[Home] [Help]
PACKAGE BODY: APPS.OKL_FE_EO_TERM_OPTIONS_PVT
Source
1 PACKAGE BODY OKL_FE_EO_TERM_OPTIONS_PVT AS
2 /* $Header: OKLREOTB.pls 120.13 2010/05/10 22:35:31 sachandr ship $ */
3
4 -- exceptions used
5 G_EXCEPTION_EO_NOTFOUND exception;
6 G_EXCEPTION_VERSION_NOTFOUND exception;
7 INVALID_START_DATE exception;
8 EXCEPTION_ITEM_REPEAT exception;
9
10 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
11 rosetta_g_mistake_date2 date := to_date('01/01/-4711', 'MM/DD/SYYYY');
12 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
13
14 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
15 function rosetta_g_miss_date_in_map(d date) return date as
16 begin
17 if (d = rosetta_g_mistake_date or d=rosetta_g_mistake_date2) then return fnd_api.g_miss_date; end if;
18 return d;
19 end;
20
21
22 PROCEDURE get_item_lines(
23 p_api_version IN NUMBER,
24 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_end_of_term_id IN NUMBER,
29 p_version IN VARCHAR2,
30 x_eto_tbl OUT NOCOPY okl_eto_tbl) AS
31
32 l_end_of_term_ver_id NUMBER;
33 l_api_name CONSTANT VARCHAR2(30) := 'get_item_lines';
34 i NUMBER :=0;
35
36 CURSOR eo_term_exists IS
37 select 1 from okl_fe_eo_terms_all_b
38 where end_of_term_id= p_end_of_term_id;
39
40 CURSOR eo_term_ver_csr(p_end_of_term_id NUMBER, p_version VARCHAR2) IS
41 select end_of_term_ver_id from okl_fe_eo_term_vers
42 where end_of_term_id= p_end_of_term_id and version_number= p_version;
43
44 CURSOR eo_term_objects_csr(p_end_of_term_ver_id NUMBER) IS
45 SELECT END_OF_TERM_OBJ_ID,
46 OBJECT_VERSION_NUMBER,
47 ORGANIZATION_ID,
48 INVENTORY_ITEM_ID,
49 CATEGORY_SET_ID,
50 CATEGORY_ID,
51 RESI_CATEGORY_SET_ID,
52 END_OF_TERM_VER_ID,
53 ATTRIBUTE_CATEGORY,
54 ATTRIBUTE1,
55 ATTRIBUTE2,
56 ATTRIBUTE3,
57 ATTRIBUTE4,
58 ATTRIBUTE5,
59 ATTRIBUTE6,
60 ATTRIBUTE7,
61 ATTRIBUTE8,
62 ATTRIBUTE9,
63 ATTRIBUTE10,
64 ATTRIBUTE11,
65 ATTRIBUTE12,
66 ATTRIBUTE13,
67 ATTRIBUTE14,
68 ATTRIBUTE15,
69 CREATED_BY,
70 CREATION_DATE,
71 LAST_UPDATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATE_LOGIN
74 FROM OKL_FE_EO_TERM_OBJECTS WHERE END_OF_TERM_VER_ID = p_end_of_term_ver_id;
75
76 BEGIN
77 -- Check if the po id exists
78 OPEN eo_term_exists;
79 IF eo_term_exists%NOTFOUND THEN
80 RAISE G_EXCEPTION_EO_NOTFOUND;
81 ELSE
82 CLOSE eo_term_exists;
83 END IF;
84
85 -- Get the version id of the po for a particular version
86 OPEN eo_term_ver_csr(p_end_of_term_id, p_version) ;
87 IF eo_term_ver_csr%NOTFOUND THEN
88 RAISE G_EXCEPTION_VERSION_NOTFOUND;
89 ELSE
90 FETCH eo_term_ver_csr INTO l_end_of_term_ver_id;
91 CLOSE eo_term_ver_csr;
92 END IF;
93
94 -- fetch the lines
95 FOR eot_objects_tbl IN eo_term_objects_csr(l_end_of_term_ver_id)
96 LOOP
97 x_eto_tbl(i).END_OF_TERM_OBJ_ID := eot_objects_tbl.END_OF_TERM_OBJ_ID;
98 x_eto_tbl(i).OBJECT_VERSION_NUMBER := eot_objects_tbl.OBJECT_VERSION_NUMBER;
99 x_eto_tbl(i).ORGANIZATION_ID := eot_objects_tbl.ORGANIZATION_ID;
100 x_eto_tbl(i).INVENTORY_ITEM_ID := eot_objects_tbl.INVENTORY_ITEM_ID;
101 x_eto_tbl(i).CATEGORY_SET_ID := eot_objects_tbl.CATEGORY_SET_ID;
102 x_eto_tbl(i).CATEGORY_ID := eot_objects_tbl.CATEGORY_ID;
103 x_eto_tbl(i).RESI_CATEGORY_SET_ID := eot_objects_tbl.RESI_CATEGORY_SET_ID;
104 x_eto_tbl(i).END_OF_TERM_VER_ID := eot_objects_tbl.END_OF_TERM_VER_ID;
105 x_eto_tbl(i).ATTRIBUTE_CATEGORY := eot_objects_tbl.ATTRIBUTE_CATEGORY;
106 x_eto_tbl(i).ATTRIBUTE1 := eot_objects_tbl.ATTRIBUTE1;
107 x_eto_tbl(i).ATTRIBUTE2 := eot_objects_tbl.ATTRIBUTE2;
108 x_eto_tbl(i).ATTRIBUTE3 := eot_objects_tbl.ATTRIBUTE3;
109 x_eto_tbl(i).ATTRIBUTE4 := eot_objects_tbl.ATTRIBUTE4;
110 x_eto_tbl(i).ATTRIBUTE5 := eot_objects_tbl.ATTRIBUTE5;
111 x_eto_tbl(i).ATTRIBUTE6 := eot_objects_tbl.ATTRIBUTE6;
112 x_eto_tbl(i).ATTRIBUTE7 := eot_objects_tbl.ATTRIBUTE7;
113 x_eto_tbl(i).ATTRIBUTE8 := eot_objects_tbl.ATTRIBUTE8;
114 x_eto_tbl(i).ATTRIBUTE9 := eot_objects_tbl.ATTRIBUTE9;
115 x_eto_tbl(i).ATTRIBUTE10 := eot_objects_tbl.ATTRIBUTE10;
116 x_eto_tbl(i).ATTRIBUTE11 := eot_objects_tbl.ATTRIBUTE11;
117 x_eto_tbl(i).ATTRIBUTE12 := eot_objects_tbl.ATTRIBUTE12;
118 x_eto_tbl(i).ATTRIBUTE13 := eot_objects_tbl.ATTRIBUTE13;
119 x_eto_tbl(i).ATTRIBUTE14 := eot_objects_tbl.ATTRIBUTE14;
120 x_eto_tbl(i).ATTRIBUTE15 := eot_objects_tbl.ATTRIBUTE15;
121 x_eto_tbl(i).CREATED_BY := eot_objects_tbl.CREATED_BY;
122 x_eto_tbl(i).CREATION_DATE := eot_objects_tbl.CREATION_DATE;
123 x_eto_tbl(i).LAST_UPDATED_BY := eot_objects_tbl.LAST_UPDATED_BY;
124 x_eto_tbl(i).LAST_UPDATE_DATE := eot_objects_tbl.LAST_UPDATE_DATE;
125 x_eto_tbl(i).LAST_UPDATE_LOGIN := eot_objects_tbl.LAST_UPDATE_LOGIN;
126 i := i+1;
127 END LOOP;
128
129 exception
130 when G_EXCEPTION_EO_NOTFOUND then
131 IF eo_term_exists%ISOPEN THEN
132 CLOSE eo_term_exists;
133 END IF;
134 -- have to set that the po is not found
135 x_return_status := OKL_API.G_RET_STS_ERROR;
136 when G_EXCEPTION_VERSION_NOTFOUND THEN
137 IF eo_term_ver_csr%ISOPEN THEN
138 CLOSE eo_term_ver_csr;
139 END IF;
140 -- have to set the message that version is not found
141 x_return_status := OKL_API.G_RET_STS_ERROR;
142 when others then
143 x_return_status := OKL_API.HANDLE_EXCEPTIONS
144 (
145 l_api_name,
146 G_PKG_NAME,
147 'OTHERS',
148 x_msg_count,
149 x_msg_data,
150 '_PVT'
151 );
152 END get_item_lines;
153 -- Get the values of the Purchase Options
154 PROCEDURE get_eo_term_values(
155 p_api_version IN NUMBER,
156 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
157 x_return_status OUT NOCOPY VARCHAR2,
158 x_msg_count OUT NOCOPY NUMBER,
159 x_msg_data OUT NOCOPY VARCHAR2,
160 p_end_of_term_id IN NUMBER,
161 p_version IN VARCHAR2,
162 x_etv_tbl OUT NOCOPY okl_etv_tbl) AS
163 l_end_of_term_ver_id NUMBER;
164 l_api_name CONSTANT VARCHAR2(30) := 'get_purchase_option_values';
165 i NUMBER :=0;
166
167 CURSOR eot_exists IS
168 select 1 from okl_fe_eo_terms_all_b
169 where end_of_term_id= p_end_of_term_id;
170
171 CURSOR end_of_term_ver_csr(p_end_of_term_id NUMBER, p_po_version VARCHAR2) IS
172 select end_of_term_ver_id from okl_fe_eo_term_vers
173 where end_of_term_id= p_end_of_term_id and version_number= p_version;
174
175 CURSOR eo_term_values_csr(p_version_id NUMBER) IS
176 SELECT END_OF_TERM_VALUE_ID,
177 OBJECT_VERSION_NUMBER,
178 EOT_TERM,
179 EOT_VALUE,
180 END_OF_TERM_VER_ID,
181 ATTRIBUTE_CATEGORY,
182 ATTRIBUTE1,
183 ATTRIBUTE2,
184 ATTRIBUTE3,
185 ATTRIBUTE4,
186 ATTRIBUTE5,
187 ATTRIBUTE6,
188 ATTRIBUTE7,
189 ATTRIBUTE8,
190 ATTRIBUTE9,
191 ATTRIBUTE10,
192 ATTRIBUTE11,
193 ATTRIBUTE12,
194 ATTRIBUTE13,
195 ATTRIBUTE14,
196 ATTRIBUTE15,
197 CREATED_BY,
198 CREATION_DATE,
199 LAST_UPDATED_BY,
200 LAST_UPDATE_DATE,
201 LAST_UPDATE_LOGIN
202 FROM OKL_FE_EO_TERM_VALUES WHERE end_OF_TERM_VER_ID = p_version_id;
203
204 BEGIN
205 -- Check if the po id exists
206 OPEN eot_exists;
207 IF eot_exists%NOTFOUND THEN
208 RAISE G_EXCEPTION_EO_NOTFOUND;
209 ELSE
210 CLOSE eot_exists;
211 END IF;
212
213 -- Get the version id of the po for a particular version
214 OPEN end_of_term_ver_csr(p_end_of_term_id, p_version) ;
215 IF end_of_term_ver_csr%NOTFOUND THEN
216 RAISE G_EXCEPTION_VERSION_NOTFOUND;
217 ELSE
218 FETCH end_of_term_ver_csr INTO l_end_of_term_ver_id;
219 CLOSE end_of_term_ver_csr;
220 END IF;
221
222 -- fetch the lines
223 FOR eot_values_tbl IN eo_term_values_csr(l_end_of_term_ver_id)
224 LOOP
225 x_etv_tbl(i).END_OF_TERM_VALUE_ID := eot_values_tbl.END_OF_TERM_VALUE_ID;
226 x_etv_tbl(i).OBJECT_VERSION_NUMBER := eot_values_tbl.OBJECT_VERSION_NUMBER;
227 x_etv_tbl(i).EOT_TERM := eot_values_tbl.EOT_TERM;
228 x_etv_tbl(i).EOT_VALUE := eot_values_tbl.EOT_VALUE;
229 x_etv_tbl(i).END_OF_TERM_VER_ID := eot_values_tbl.END_OF_TERM_VER_ID;
230 x_etv_tbl(i).ATTRIBUTE_CATEGORY := eot_values_tbl.ATTRIBUTE_CATEGORY;
231 x_etv_tbl(i).ATTRIBUTE1 := eot_values_tbl.ATTRIBUTE1;
232 x_etv_tbl(i).ATTRIBUTE2 := eot_values_tbl.ATTRIBUTE2;
233 x_etv_tbl(i).ATTRIBUTE3 := eot_values_tbl.ATTRIBUTE3;
234 x_etv_tbl(i).ATTRIBUTE4 := eot_values_tbl.ATTRIBUTE4;
235 x_etv_tbl(i).ATTRIBUTE5 := eot_values_tbl.ATTRIBUTE5;
236 x_etv_tbl(i).ATTRIBUTE6 := eot_values_tbl.ATTRIBUTE6;
237 x_etv_tbl(i).ATTRIBUTE7 := eot_values_tbl.ATTRIBUTE7;
238 x_etv_tbl(i).ATTRIBUTE8 := eot_values_tbl.ATTRIBUTE8;
239 x_etv_tbl(i).ATTRIBUTE9 := eot_values_tbl.ATTRIBUTE9;
240 x_etv_tbl(i).ATTRIBUTE10 := eot_values_tbl.ATTRIBUTE10;
241 x_etv_tbl(i).ATTRIBUTE11 := eot_values_tbl.ATTRIBUTE11;
242 x_etv_tbl(i).ATTRIBUTE12 := eot_values_tbl.ATTRIBUTE12;
243 x_etv_tbl(i).ATTRIBUTE13 := eot_values_tbl.ATTRIBUTE13;
244 x_etv_tbl(i).ATTRIBUTE14 := eot_values_tbl.ATTRIBUTE14;
245 x_etv_tbl(i).ATTRIBUTE15 := eot_values_tbl.ATTRIBUTE15;
246 x_etv_tbl(i).CREATED_BY := eot_values_tbl.CREATED_BY;
247 x_etv_tbl(i).CREATION_DATE := eot_values_tbl.CREATION_DATE;
248 x_etv_tbl(i).LAST_UPDATED_BY := eot_values_tbl.LAST_UPDATED_BY;
249 x_etv_tbl(i).LAST_UPDATE_DATE := eot_values_tbl.LAST_UPDATE_DATE;
250 x_etv_tbl(i).LAST_UPDATE_LOGIN := eot_values_tbl.LAST_UPDATE_LOGIN;
251 i := i+1;
252 END LOOP;
253 EXCEPTION
254 when G_EXCEPTION_EO_NOTFOUND then
255 IF eot_exists%ISOPEN THEN
256 CLOSE eot_exists;
257 END IF;
258 -- have to set that the po is not found
259 x_return_status := OKL_API.G_RET_STS_ERROR;
260 when G_EXCEPTION_VERSION_NOTFOUND THEN
261 IF end_of_term_ver_csr%ISOPEN THEN
262 CLOSE end_of_term_ver_csr;
263 END IF;
264 -- have to set the message that version is not found
265 x_return_status := OKL_API.G_RET_STS_ERROR;
266 when others then
267 x_return_status := OKL_API.HANDLE_EXCEPTIONS
268 (
269 l_api_name,
270 G_PKG_NAME,
271 'OTHERS',
272 x_msg_count,
273 x_msg_data,
274 '_PVT'
275 );
276
277 END get_eo_term_values;
278
279 -- Get the Purchase Option Header, Version, values and Values
280 PROCEDURE get_end_of_term_option(
281 p_api_version IN NUMBER,
282 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2,
286 p_eot_id IN NUMBER,
287 p_version IN VARCHAR2,
288 x_ethv_rec OUT NOCOPY okl_ethv_rec,
289 x_eve_rec OUT NOCOPY okl_eve_rec,
290 x_eto_tbl OUT NOCOPY okl_eto_tbl,
291 x_etv_tbl OUT NOCOPY okl_etv_tbl) AS
292
293 l_record_flag BOOLEAN:= false;
294 l_api_name VARCHAR2(3):='get_end_of_term_option';
295
296 CURSOR eot_hdr_csr(p_eot_id NUMBER) IS
297 SELECT END_OF_TERM_ID,
298 OBJECT_VERSION_NUMBER,
299 END_OF_TERM_NAME,
300 END_OF_TERM_DESC,
301 ORG_ID,
302 CURRENCY_CODE,
303 EOT_TYPE_CODE,
304 PRODUCT_ID,
305 CATEGORY_TYPE_CODE,
306 ORIG_END_OF_TERM_ID,
307 STS_CODE,
308 EFFECTIVE_FROM_DATE,
309 EFFECTIVE_TO_DATE,
310 ATTRIBUTE_CATEGORY,
311 ATTRIBUTE1,
312 ATTRIBUTE2,
313 ATTRIBUTE3,
314 ATTRIBUTE4,
315 ATTRIBUTE5,
316 ATTRIBUTE6,
317 ATTRIBUTE7,
318 ATTRIBUTE8,
319 ATTRIBUTE9,
320 ATTRIBUTE10,
321 ATTRIBUTE11,
322 ATTRIBUTE12,
323 ATTRIBUTE13,
324 ATTRIBUTE14,
325 ATTRIBUTE15,
326 CREATED_BY,
327 CREATION_DATE,
328 LAST_UPDATED_BY,
329 LAST_UPDATE_DATE,
330 LAST_UPDATE_LOGIN
331 FROM OKL_FE_EO_TERMS_V WHERE END_OF_TERM_ID=p_eot_id;
332
333 CURSOR eot_version_csr(p_eot_id NUMBER, p_eot_version VARCHAR2) IS
334 SELECT END_OF_TERM_VER_ID,
335 OBJECT_VERSION_NUMBER,
336 VERSION_NUMBER,
337 EFFECTIVE_FROM_DATE,
338 EFFECTIVE_TO_DATE,
339 STS_CODE,
340 END_OF_TERM_ID,
341 ATTRIBUTE_CATEGORY,
342 ATTRIBUTE1,
343 ATTRIBUTE2,
344 ATTRIBUTE3,
345 ATTRIBUTE4,
346 ATTRIBUTE5,
347 ATTRIBUTE6,
348 ATTRIBUTE7,
349 ATTRIBUTE8,
350 ATTRIBUTE9,
351 ATTRIBUTE10,
352 ATTRIBUTE11,
353 ATTRIBUTE12,
354 ATTRIBUTE13,
355 ATTRIBUTE14,
356 ATTRIBUTE15,
357 CREATED_BY,
358 CREATION_DATE,
359 LAST_UPDATED_BY,
360 LAST_UPDATE_DATE,
361 LAST_UPDATE_LOGIN
362 FROM OKL_FE_EO_TERM_VERS WHERE END_OF_TERM_ID= p_eot_id and version_number=p_eot_version;
363 BEGIN
364
365 -- get the end of term header
366 OPEN eot_hdr_csr(p_eot_id);
367 FETCH eot_hdr_csr INTO x_ethv_rec;
368 CLOSE eot_hdr_csr;
369
370 -- get the end of term versions
371 OPEN eot_version_csr(p_eot_id, p_version);
372 FETCH eot_version_csr INTO x_eve_rec;
373 CLOSE eot_version_csr;
374
375 -- get the Purchase Option lines
376 get_item_lines( p_api_version,
377 p_init_msg_list,
378 x_return_status,
379 x_msg_count,
380 x_msg_data,
381 p_eot_id,
382 p_version,
383 x_eto_tbl);
384
385 -- get the Purchase Option Values
386 get_eo_term_values(
387 p_api_version,
388 p_init_msg_list,
389 x_return_status,
390 x_msg_count,
391 x_msg_data,
392 p_eot_id,
393 p_version,
394 x_etv_tbl);
395
396
397
398 exception
399 when others then
400 x_return_status := OKL_API.HANDLE_EXCEPTIONS
401 (
402 l_api_name,
403 G_PKG_NAME,
404 'OTHERS',
405 x_msg_count,
406 x_msg_data,
407 '_PVT'
408 );
409
410 END get_end_of_term_option;
411 --viselvar 4604059 start
412 -- Checks if the percent values lie in the range 0-100
413 PROCEDURE validate_percent_values(p_etv_tbl IN okl_etv_tbl) IS
414 BEGIN
415 FOR i IN p_etv_tbl.FIRST..p_etv_tbl.LAST
416 LOOP
417 IF p_etv_tbl(i).eot_value > 100 THEN
418 OKL_API.set_message(p_app_name => G_APP_NAME,
419 p_msg_name => 'OKL_INVALID_VALUE',
420 p_token1 => OKL_API.G_COL_NAME_TOKEN,
421 p_token1_value => 'Term ' || p_etv_tbl(i).eot_term );
422 RAISE OKL_API.G_EXCEPTION_ERROR;
423
424 END IF;
425 END LOOP;
426 END;
427 --viselvar 4604059 end
428 -- Function to validate if the chosen objects (items, item categories or residual categorie sets)
429 -- are available in item residuals
430 FUNCTION val_avail_item_residual(
431 p_version_id IN NUMBER
432 )RETURN VARCHAR2 IS
433
434 -- cursor to fetch the objects for that particular version
435 CURSOR eot_objects_csr(p_version_id IN NUMBER) IS
436 SELECT end_of_term_obj_id,
437 inventory_item_id ,
438 organization_id,
439 category_set_id,
440 category_id,
441 resi_category_set_id
442 FROM OKL_FE_EO_TERM_OBJECTS
443 WHERE end_of_term_ver_id = p_version_id;
444
445 -- cursor to fetch the residual type and the currency from the header
446 CURSOR eot_hdr_csr(p_version_id IN NUMBER) IS
447 SELECT end_of_term_id,
448 eot_type_code,
449 currency_code,
450 category_type_code
451 FROM OKL_FE_EO_TERM_VERS_V
452 WHERE end_of_term_ver_id = p_version_id;
453
454 -- cursor to see id the records are available
455 CURSOR count_csr(p_version_id IN NUMBER,l_eot_type_code IN VARCHAR2, l_currency_code IN VARCHAR2) IS
456 SELECT count(*) FROM(
457 SELECT inventory_item_id ,
458 organization_id,
459 category_set_id,
460 category_id,
461 resi_category_set_id
462 FROM OKL_FE_EO_TERM_OBJECTS
463 WHERE end_of_term_ver_id = p_version_id
464 MINUS
465 SELECT inventory_item_id ,
466 organization_id,
467 category_set_id,
468 category_id,
469 resi_category_set_id
470 FROM OKL_FE_ITEM_RESIDUAL_ALL
471 WHERE (residual_type_code= 'PERCENT' and
472 residual_type_code=l_eot_type_code ) OR (residual_type_code = 'AMOUNT' and
473 residual_type_code=l_eot_type_code and currency_code =l_currency_code)
474 );
475
476 CURSOR source_meaning_csr(source_code IN VARCHAR2) IS
477 SELECT meaning from fnd_lookups where lookup_type='OKL_SOURCE_TYPES'
478 and lookup_code=source_code;
479
480 l_eto_tbl okl_eto_tbl;
481 i NUMBER := 1;
482 l_end_of_term_id NUMBER;
483 l_end_of_type_code VARCHAR2(30);
484 l_currency_code VARCHAR2(30);
485 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
486 l_count NUMBER;
487 l_category_type_code VARCHAR2(30);
488 l_type_meaning VARCHAR2(30);
489
490 BEGIN
491
492 -- fetch the residual type and the currency from the header
493 OPEN eot_hdr_csr(p_version_id);
494 FETCH eot_hdr_csr INTO l_end_of_term_id, l_end_of_type_code, l_currency_code, l_category_type_code;
495 CLOSE eot_hdr_csr;
496
497 IF (l_end_of_type_code = 'RESIDUAL_AMOUNT' OR l_end_of_type_code = 'RESIDUAL_PERCENT') THEN
498 -- fetch the objects for the given version number
499 FOR l_objects_rec IN eot_objects_csr(p_version_id) LOOP
500 l_eto_tbl(i).end_of_term_obj_id := l_objects_rec.end_of_term_obj_id;
501 l_eto_tbl(i).inventory_item_id := l_objects_rec.inventory_item_id;
502 l_eto_tbl(i).category_set_id := l_objects_rec.category_set_id;
503 l_eto_tbl(i).category_id := l_objects_rec.category_id;
504 l_eto_tbl(i).resi_category_set_id:= l_objects_rec.resi_category_set_id;
505 i:= i+1;
506 END LOOP;
507
508 IF (l_end_of_type_code = 'RESIDUAL_AMOUNT') THEN
509 l_end_of_type_code:='AMOUNT';
510 ELSIF(l_end_of_type_code = 'RESIDUAL_PERCENT') THEN
511 l_end_of_type_code:='PERCENT';
512 END IF;
513
514 OPEN count_csr(p_version_id,l_end_of_type_code,l_currency_code);
515 FETCH count_csr INTO l_count;
516 CLOSE count_csr;
517
518 OPEN source_meaning_csr(l_category_type_code);
519 FETCH source_meaning_csr INTO l_type_meaning;
520 CLOSE source_meaning_csr;
521
522 IF (l_count <> 0) THEN
523 l_return_status := OKL_API.G_RET_STS_ERROR;
524 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
525 p_msg_name => 'OKL_FE_VALIDATE_EOT',
526 p_token1 => 'SOURCE',
527 p_token1_value => l_type_meaning);
528 END IF;
529
530 END IF;
531 return l_return_status;
532 -- have to set a message
533 END val_avail_item_residual;
534
535 -- Method to end date the referenced Lease Rate Set that uses this PO version
536 PROCEDURE calculate_start_date(
537 p_api_version IN NUMBER,
538 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2,
542 p_eve_rec IN okl_eve_rec,
543 x_cal_eff_from OUT NOCOPY DATE) IS
544
545 -- cursor to calculate the maximum end date referenced in quote
546 -- the cursor to be modified as the quotes table is not finalized
547 CURSOR qq_date_csr(p_eot_version_id IN NUMBER) IS
548 SELECT max(expected_start_date) FROM okl_quick_quotes_b WHERE end_of_term_option_id=p_eot_version_id;
549
550 CURSOR lq_date_csr(p_eot_version_id IN NUMBER) IS
551 SELECT max(expected_start_date) FROM okl_lease_quotes_b WHERE end_of_term_option_id=p_eot_version_id;
552
553 CURSOR lrs_qq_date_csr(p_eot_version_id IN NUMBER) IS
554 SELECT max(expected_start_date) FROM okl_quick_quotes_b WHERE rate_card_id in
555 (select rate_set_version_id from OKL_FE_RATE_SET_VERSIONS where end_of_term_ver_id = p_eot_version_id);
556
557 CURSOR lrs_lq_date_csr(p_eot_version_id IN NUMBER) IS
558 SELECT max(expected_start_date) FROM okl_lease_quotes_b WHERE rate_card_id in
559 (select rate_set_version_id from OKL_FE_RATE_SET_VERSIONS where end_of_term_ver_id = p_eot_version_id);
560
561 -- cursor to fetch the start date and the end of the previous version
562 CURSOR prev_ver_csr(p_eot_id IN NUMBER, p_ver_number IN VARCHAR2) IS
563 SELECT effective_from_date, effective_to_date FROM okl_fe_eo_term_vers where end_of_term_id= p_eot_id
564 AND version_number= p_ver_number-1;
565
566 TYPE l_start_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
567
568 l_eve_rec okl_eve_rec := p_eve_rec;
569 l_api_name VARCHAR2(40):= 'calculate_end_date';
570 l_api_version NUMBER := 1.0;
571 l_eff_from DATE;
572 l_eff_to DATE;
573 l_start_date l_start_date_type;
574 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
575 l_max_start_date DATE;
576 i NUMBER;
577
578 BEGIN
579 l_return_status := OKL_API.start_activity(l_api_name
580 ,g_pkg_name
581 ,p_init_msg_list
582 ,l_api_version
583 ,p_api_version
584 ,'_PVT'
585 ,x_return_status);
586 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
587 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
588 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
589 raise OKL_API.G_EXCEPTION_ERROR;
590 END IF;
591
592 OPEN prev_ver_csr(l_eve_rec.end_of_term_id, l_eve_rec.version_number);
593 FETCH prev_ver_csr INTO l_eff_from, l_eff_to;
594
595 IF (prev_ver_csr%FOUND) THEN
596 -- if the effective to date of the previous version is not null
597
598 IF (l_eff_to IS NOT NULL) THEN
599 l_max_start_date := l_eff_to + 1;
600 ELSE
601 l_max_start_date := l_eff_from + 1;
602 END IF;
603 ELSE
604 l_max_start_date:= okl_api.g_miss_date;
605 END IF;
606 CLOSE prev_ver_csr;
607
608 IF (l_eff_to is null) THEN
609 -- calculate the maximum start date
610 OPEN qq_date_csr(l_eve_rec.end_of_term_ver_id);
611 FETCH qq_date_csr INTO l_start_date(1);
612 CLOSE qq_date_csr;
613
614 OPEN lq_date_csr(l_eve_rec.end_of_term_ver_id);
615 FETCH lq_date_csr INTO l_start_date(2);
616 CLOSE lq_date_csr;
617
618 OPEN lrs_qq_date_csr(l_eve_rec.end_of_term_ver_id);
619 FETCH lrs_qq_date_csr INTO l_start_date(3);
620 CLOSE lrs_qq_date_csr;
621
622 OPEN lrs_lq_date_csr(l_eve_rec.end_of_term_ver_id);
623 FETCH lrs_lq_date_csr INTO l_start_date(3);
624 CLOSE lrs_lq_date_csr;
625
626 FOR i IN l_start_date.FIRST .. l_start_date.LAST LOOP
627 IF (l_start_date(i) IS NOT NULL AND (l_start_date(i)+1) > l_max_start_date) THEN
628 l_max_start_date:= l_start_date(i)+1;
629 END IF;
630 END LOOP;
631 END IF;
632
633
634 -- assign the max start date to the out parameter
635 x_cal_eff_from := l_max_start_date;
636
637 --end activity
638 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
639 x_return_status := l_return_status;
640
641
642 exception
643 WHEN OKL_API.G_EXCEPTION_ERROR THEN
644 x_return_status := OKL_API.HANDLE_EXCEPTIONS
645 (l_api_name,
646 G_PKG_NAME,
647 'OKL_API.G_RET_STS_ERROR',
648 x_msg_count,
649 x_msg_data,
650 '_PVT');
651 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
652 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
653 ( l_api_name,
654 G_PKG_NAME,
655 'OKL_API.G_RET_STS_UNEXP_ERROR',
656 x_msg_count,
657 x_msg_data,
658 '_PVT');
659 WHEN OTHERS THEN
660 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
661 ( l_api_name,
662 G_PKG_NAME,
663 'OTHERS',
664 x_msg_count,
665 x_msg_data,
666 '_PVT');
667 END calculate_start_date;
668
669 -- Create a End of Term option
670 PROCEDURE insert_end_of_term_option(
671 p_api_version IN NUMBER,
672 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2,
676 p_ethv_rec IN okl_ethv_rec,
677 p_eve_rec IN okl_eve_rec,
678 p_eto_tbl IN okl_eto_tbl,
679 p_etv_tbl IN okl_etv_tbl,
680 x_ethv_rec OUT NOCOPY okl_ethv_rec,
681 x_eve_rec OUT NOCOPY okl_eve_rec,
682 x_eto_tbl OUT NOCOPY okl_eto_tbl,
683 x_etv_tbl OUT NOCOPY okl_etv_tbl) AS
684
685 l_api_name CONSTANT VARCHAR2(40) := 'insert_end_of_term_option';
686 l_api_version CONSTANT NUMBER := 1.0;
687 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
688 l_ethv_rec okl_ethv_rec:= p_ethv_rec;
689 l_eve_rec okl_eve_rec := p_eve_rec;
690 l_eto_tbl okl_eto_tbl := p_eto_tbl;
691 l_etv_tbl okl_etv_tbl := p_etv_tbl;
692 i NUMBER;
693 l_dummy_var VARCHAR2(1):='?';
694
695 CURSOR eot_unique_chk(p_name IN varchar2) IS
696 SELECT 'x'
697 FROM okl_fe_eo_terms_v
698 WHERE end_of_term_name = UPPER(p_name);
699
700
701
702 BEGIN
703 l_return_status := OKL_API.start_activity(l_api_name
704 ,g_pkg_name
705 ,p_init_msg_list
706 ,l_api_version
707 ,p_api_version
708 ,'_PVT'
709 ,x_return_status);
710
711 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
712 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
713 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
714 raise OKL_API.G_EXCEPTION_ERROR;
715 END IF;
716
717 OPEN eot_unique_chk(l_ethv_rec.end_of_term_name);
718 FETCH eot_unique_chk INTO l_dummy_var ;
719 CLOSE eot_unique_chk;
720
721 -- if l_dummy_var is 'x' then name already exists
722
723 IF (l_dummy_var = 'x') THEN
724 okl_api.set_message(p_app_name => g_app_name
725 ,p_msg_name => 'OKL_DUPLICATE_NAME'
726 ,p_token1 => 'NAME'
727 ,p_token1_value => l_ethv_rec.end_of_term_name);
728 RAISE okl_api.g_exception_error;
729
730 END IF;
731
732 --Added category_type_code condition by dcshanmu for bug 6699555
733 IF ((l_ethv_rec.eot_type_code ='AMOUNT' OR l_ethv_rec.eot_type_code ='PERCENT') AND (l_ethv_rec.category_type_code <> 'NONE')) THEN
734 IF (l_etv_tbl.COUNT = 0) THEN
735 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
736 p_msg_name => 'OKL_ST_IRS_RESIDUALS_MISSING');
737 RAISE OKL_API.G_EXCEPTION_ERROR;
738 END IF;
739 END IF;
740
741 l_eve_rec.effective_to_date := rosetta_g_miss_date_in_map(l_eve_rec.effective_to_date);
742
743 -- set the header properties
744 l_ethv_rec.end_of_term_name := upper(l_ethv_rec.end_of_term_name);
745 l_ethv_rec.STS_CODE:='NEW';
746 l_ethv_rec.effective_from_date := l_eve_rec.effective_from_date;
747 l_ethv_rec.effective_to_date := l_eve_rec.effective_to_date;
748 -- viselvar 4604059 start
749 IF (l_ethv_rec.eot_type_code ='PERCENT' or l_ethv_rec.eot_type_code ='RESIDUAL_PERCENT') THEN
750 l_ethv_rec.currency_code:= null;
751 END IF;
752 -- viselvar 4604059 end
753
754 -- insert the header record
755 okl_eth_pvt.insert_row(
756 p_api_version => p_api_version,
757 p_init_msg_list => p_init_msg_list,
758 x_return_status => l_return_status,
759 x_msg_count => x_msg_count,
760 x_msg_data => x_msg_data,
761 p_ethv_rec => l_ethv_rec,
762 x_ethv_rec => x_ethv_rec
763 );
764
765 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
766 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
767 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
768 raise OKL_API.G_EXCEPTION_ERROR;
769 END IF;
770
771 -- set the version record attributes
772 l_eve_rec.VERSION_NUMBER := '1';
773 l_eve_rec.STS_CODE:= 'NEW';
774 l_eve_rec.END_OF_TERM_ID := x_ethv_rec.END_OF_TERM_ID;
775
776 -- insert the version record into the table
777 okl_eve_pvt.insert_row(
778 p_api_version => p_api_version,
779 p_init_msg_list => p_init_msg_list,
780 x_return_status => l_return_status,
781 x_msg_count => x_msg_count,
782 x_msg_data => x_msg_data,
783 p_eve_rec => l_eve_rec,
784 x_eve_rec => x_eve_rec);
785
786 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
787 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
788 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
789 raise OKL_API.G_EXCEPTION_ERROR;
790 END IF;
791
792 --Added if condition by dcshanmu for bug 6699555
793 --Objects and Term Values table will not be present for source type 'NONE'
794 IF(l_ethv_rec.category_type_code <> 'NONE') THEN
795
796 --populate the foreign key for the lines table
797 IF (l_eto_tbl.COUNT >0) then
798 i:= l_eto_tbl.FIRST;
799 LOOP
800 l_eto_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
801 EXIT WHEN (i= l_eto_tbl.LAST);
802 i := l_eto_tbl.NEXT(i);
803 END LOOP;
804 END IF;
805 -- insert the lines record into the database
806 okl_eto_pvt.insert_row(
807 p_api_version => p_api_version,
808 p_init_msg_list => p_init_msg_list,
809 x_return_status => l_return_status,
810 x_msg_count => x_msg_count,
811 x_msg_data => x_msg_data,
812 p_eto_tbl => l_eto_tbl,
813 x_eto_tbl => x_eto_tbl);
814
815 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
816 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
817 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
818 raise OKL_API.G_EXCEPTION_ERROR;
819 END IF;
820
821 -- populate the foreign key for the values table
822 IF (l_etv_tbl.COUNT > 0) then
823 i:= l_etv_tbl.FIRST;
824 LOOP
825 l_etv_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
826 EXIT WHEN (i= l_etv_tbl.LAST);
827 i:= l_etv_tbl.NEXT(i);
828 END LOOP;
829 IF (x_ethv_rec.eot_type_code ='AMOUNT' OR x_ethv_rec.eot_type_code ='PERCENT' ) THEN
830 --viselvar 4604059 start
831 -- validate the Values of terms
832 IF x_ethv_rec.eot_type_code = 'PERCENT' THEN
833 validate_percent_values(l_etv_tbl);
834 END IF;
835 --viselvar 4604059 end
836 -- insert the values record into the database
837 okl_etv_pvt.insert_row(
838 p_api_version => p_api_version,
839 p_init_msg_list => p_init_msg_list,
840 x_return_status => l_return_status,
841 x_msg_count => x_msg_count,
842 x_msg_data => x_msg_data,
843 p_etv_tbl => l_etv_tbl,
844 x_etv_tbl => x_etv_tbl);
845
846 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
847 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
848 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
849 raise OKL_API.G_EXCEPTION_ERROR;
850 END IF;
851
852 END IF;
853 END IF;
854 END IF; --Added by dcshanmu for bug 6699555
855
856 exception
857 WHEN OKL_API.G_EXCEPTION_ERROR THEN
858 x_return_status := OKL_API.HANDLE_EXCEPTIONS
859 (l_api_name,
860 G_PKG_NAME,
861 'OKL_API.G_RET_STS_ERROR',
862 x_msg_count,
863 x_msg_data,
864 '_PVT');
865 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
866 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
867 ( l_api_name,
868 G_PKG_NAME,
869 'OKL_API.G_RET_STS_UNEXP_ERROR',
870 x_msg_count,
871 x_msg_data,
872 '_PVT');
873 WHEN OTHERS THEN
874 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
875 ( l_api_name,
876 G_PKG_NAME,
877 'OTHERS',
878 x_msg_count,
879 x_msg_data,
880 '_PVT');
881
882
883 END insert_end_of_term_option;
884
885 PROCEDURE update_end_of_term_option(
886 p_api_version IN NUMBER,
887 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_msg_count OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2,
891 p_eve_rec IN okl_eve_rec,
892 p_eto_tbl IN okl_eto_tbl,
893 p_etv_tbl IN okl_etv_tbl,
894 x_eve_rec OUT NOCOPY okl_eve_rec,
895 x_eto_tbl OUT NOCOPY okl_eto_tbl,
896 x_etv_tbl OUT NOCOPY okl_etv_tbl) AS
897
898 l_eve_rec okl_eve_rec := p_eve_rec;
899 l_eto_tbl okl_eto_tbl := p_eto_tbl;
900 l_etv_tbl okl_etv_tbl := p_etv_tbl;
901 l_ethv_rec okl_ethv_rec;
902 x_ethv_rec okl_ethv_rec;
903 i NUMBER:= 0;
904 l_api_name VARCHAR2(40) := 'update_end_of_term_option';
905 l_api_version NUMBER := 1.0;
906 l_init_msg_list VARCHAR2(1):= p_init_msg_list;
907 residual_type VARCHAR2(30);
908 l_eff_from DATE;
909 l_eff_to DATE;
910 l_lat_act_ver VARCHAR2(24);
911 cal_eff_from DATE;
912 x_obj_tbl invalid_object_tbl;
913 j NUMBER;
914 lp_lrtv_tbl okl_lrs_id_tbl;
915
916 CURSOR latest_active_ver_csr(p_eot_id IN NUMBER) IS
917 SELECT max(version_number) FROM okl_fe_eo_term_vers
918 WHERE end_of_term_id=p_eot_id and sts_code='ACTIVE';
919
920 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
921 cal_end_date DATE;
922 BEGIN
923 l_return_status := OKL_API.start_activity(l_api_name
924 ,g_pkg_name
925 ,p_init_msg_list
926 ,l_api_version
927 ,p_api_version
928 ,'_PVT'
929 ,x_return_status);
930
931
932 l_eve_rec.effective_to_date := rosetta_g_miss_date_in_map(l_eve_rec.effective_to_date);
933
934
935 -- assign the in records to the out records
936 x_eve_rec := p_eve_rec;
937 x_eto_tbl := p_eto_tbl;
938 x_etv_tbl := p_etv_tbl;
939
940 IF (l_eve_rec.version_number >1 and l_eve_rec.STS_CODE ='NEW') THEN
941 calculate_start_date(
942 l_api_version
943 ,l_init_msg_list
944 ,l_return_status
945 ,x_msg_count
946 ,x_msg_data
947 ,l_eve_rec
948 ,cal_eff_from);
949
950
951 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
952 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
953 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
954 raise OKL_API.G_EXCEPTION_ERROR;
955 END IF;
956
957 IF ( l_eve_rec.effective_from_date < cal_eff_from ) THEN
958 RAISE INVALID_START_DATE;
959 END IF;
960 END IF;
961
962 -- if the status of the version is new, then the version record can be modified
963 -- objects can be modified or added
964 -- term value pairs can be added or modified
965 IF (l_eve_rec.STS_CODE='NEW') THEN
966 IF (l_eve_rec.VERSION_NUMBER = 1) THEN
967
968
969 l_ethv_rec.end_of_term_id := l_eve_rec.end_of_term_id;
970 l_ethv_rec.effective_from_date := l_eve_rec.effective_from_date;
971 okl_eth_pvt.update_row(
972 p_api_version => p_api_version,
973 p_init_msg_list => p_init_msg_list,
974 x_return_status => l_return_status,
975 x_msg_count => x_msg_count,
976 x_msg_data => x_msg_data,
977 p_ethv_rec => l_ethv_rec,
978 x_ethv_rec => x_ethv_rec
979 );
980
981 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
982 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
983 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
984 raise OKL_API.G_EXCEPTION_ERROR;
985 END IF;
986
987 END IF;
988 -- update the version record
989
990 okl_eve_pvt.update_row(
991 p_api_version => p_api_version,
992 p_init_msg_list => p_init_msg_list,
993 x_return_status => l_return_status,
994 x_msg_count => x_msg_count,
995 x_msg_data => x_msg_data,
996 p_eve_rec => l_eve_rec,
997 x_eve_rec => x_eve_rec);
998
999
1000 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1001 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1002 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1003 raise OKL_API.G_EXCEPTION_ERROR;
1004 END IF;
1005
1006 --Added if condition by dcshanmu for bug 6699555
1007 --Objects and Term Values table will not be present for source type 'NONE'
1008 --Commenting out the check for 'NONE' condition as UI is not passing the value
1009 --This check is already done UI layer. Fix for bug#6892431
1010 -- IF(l_ethv_rec.category_type_code <> 'NONE') THEN
1011
1012 -- update the lines table
1013
1014 IF (l_eto_tbl.COUNT >0) then
1015 i := l_eto_tbl.FIRST;
1016 LOOP
1017 IF (l_eto_tbl(i).END_OF_TERM_OBJ_ID is not null) THEN
1018 okl_eto_pvt.update_row(
1019 p_api_version => p_api_version,
1020 p_init_msg_list => p_init_msg_list,
1021 x_return_status => l_return_status,
1022 x_msg_count => x_msg_count,
1023 x_msg_data => x_msg_data,
1024 p_eto_rec => l_eto_tbl(i),
1025 x_eto_rec => x_eto_tbl(i));
1026
1027 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1028 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1029 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1030 raise OKL_API.G_EXCEPTION_ERROR;
1031 END IF;
1032
1033 ELSIF (l_eto_tbl(i).END_OF_TERM_OBJ_ID is null ) THEN
1034
1035 l_eto_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1036
1037 okl_eto_pvt.insert_row(
1038 p_api_version => p_api_version,
1039 p_init_msg_list => p_init_msg_list,
1040 x_return_status => l_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_eto_rec => l_eto_tbl(i),
1044 x_eto_rec => x_eto_tbl(i));
1045
1046
1047 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1048 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1049 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1050 raise OKL_API.G_EXCEPTION_ERROR;
1051 END IF;
1052
1053 END IF;
1054 EXIT WHEN (i= l_eto_tbl.LAST);
1055 i := l_eto_tbl.NEXT(i);
1056 END LOOP;
1057 END IF;
1058
1059 -- update the values table
1060 IF (l_etv_tbl.COUNT >0) then
1061
1062 i := l_etv_tbl.FIRST;
1063 --viselvar 4604059 start
1064 -- validate the Values of terms
1065 IF x_ethv_rec.eot_type_code = 'PERCENT' THEN
1066 validate_percent_values(l_etv_tbl);
1067 END IF;
1068 --viselvar 4604059 end
1069 LOOP
1070 IF (l_etv_tbl(i).END_OF_TERM_VALUE_ID is not null) THEN
1071
1072 okl_etv_pvt.update_row(
1073 p_api_version => p_api_version,
1074 p_init_msg_list => p_init_msg_list,
1075 x_return_status => l_return_status,
1076 x_msg_count => x_msg_count,
1077 x_msg_data => x_msg_data,
1078 p_etv_rec => l_etv_tbl(i),
1079 x_etv_rec => x_etv_tbl(i));
1080 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1081 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1082 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1083 raise OKL_API.G_EXCEPTION_ERROR;
1084 END IF;
1085
1086 ELSIF (l_etv_tbl(i).END_OF_TERM_VALUE_ID is null) THEN
1087 l_etv_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1088
1089 okl_etv_pvt.insert_row(
1090 p_api_version => p_api_version,
1091 p_init_msg_list => p_init_msg_list,
1092 x_return_status => l_return_status,
1093 x_msg_count => x_msg_count,
1094 x_msg_data => x_msg_data,
1095 p_etv_rec => l_etv_tbl(i),
1096 x_etv_rec => x_etv_tbl(i));
1097
1098 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1099 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1100 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1101 raise OKL_API.G_EXCEPTION_ERROR;
1102 END IF;
1103
1104 END IF;
1105 EXIT WHEN (i= l_etv_tbl.LAST);
1106 i := l_etv_tbl.NEXT(i);
1107 END LOOP;
1108
1109 END IF;
1110 --END IF; --Added by dcshanmu for bug 6699555. Commenting out the END IF.
1111
1112 ELSIF (l_eve_rec.STS_CODE = 'ACTIVE') THEN
1113
1114 OPEN latest_active_ver_csr(l_eve_rec.END_OF_TERM_ID);
1115 FETCH latest_active_ver_csr INTO l_lat_act_ver;
1116 CLOSE latest_active_ver_csr;
1117
1118
1119 IF (l_eve_rec.effective_to_date IS NOT NULL) THEN
1120
1121 -- end date the lease rate set versions
1122 INVALID_OBJECTS(
1123 p_api_version => p_api_version,
1124 p_init_msg_list => p_init_msg_list,
1125 x_return_status => l_return_status,
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_version_id => l_eve_rec.end_of_term_ver_id,
1129 x_obj_tbl => x_obj_tbl
1130 );
1131
1132
1133 IF (x_obj_tbl.COUNT >0) THEN
1134 FOR j IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1135 lp_lrtv_tbl(j) := x_obj_tbl(j).obj_id;
1136 END LOOP;
1137
1138
1139 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(
1140 p_api_version
1141 ,p_init_msg_list
1142 ,l_return_status
1143 ,x_msg_count
1144 ,x_msg_data
1145 ,lp_lrtv_tbl
1146 ,l_eve_rec.effective_to_date
1147 );
1148 END IF;
1149 END IF;
1150 IF (l_eve_rec.version_number = l_lat_act_ver) THEN
1151 -- update the header record
1152 -- viselvar 4604059 start
1153 l_ethv_rec.EFFECTIVE_TO_DATE := l_eve_rec.EFFECTIVE_TO_DATE;
1154 -- viselvar 4604059 end
1155 l_ethv_rec.END_OF_TERM_ID:= l_eve_rec.end_of_term_id;
1156 okl_eth_pvt.update_row(
1157 p_api_version => p_api_version,
1158 p_init_msg_list => p_init_msg_list,
1159 x_return_status => l_return_status,
1160 x_msg_count => x_msg_count,
1161 x_msg_data => x_msg_data,
1162 p_ethv_rec => l_ethv_rec,
1163 x_ethv_rec => x_ethv_rec
1164 );
1165 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1166 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1167 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1168 raise OKL_API.G_EXCEPTION_ERROR;
1169 END IF;
1170
1171 -- update the version record
1172 okl_eve_pvt.update_row(
1173 p_api_version => p_api_version,
1174 p_init_msg_list => p_init_msg_list,
1175 x_return_status => l_return_status,
1176 x_msg_count => x_msg_count,
1177 x_msg_data => x_msg_data,
1178 p_eve_rec => l_eve_rec,
1179 x_eve_rec => x_eve_rec);
1180
1181 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1182 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1183 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1184 raise OKL_API.G_EXCEPTION_ERROR;
1185 END IF;
1186 END IF;
1187
1188 --Added if condition by dcshanmu for bug 6699555
1189 --Objects table will not be present for source type 'NONE'
1190 --Commenting out the check for 'NONE' condition as UI is not passing the value
1191 --This check is already done UI layer. Fix for bug#6892431
1192 --IF(l_ethv_rec.category_type_code <> 'NONE') THEN
1193
1194 -- add only objects if the status is active
1195 IF (l_eto_tbl.COUNT >0) then
1196 FOR i IN l_eto_tbl.FIRST .. l_eto_tbl.LAST LOOP
1197 IF (l_eto_tbl(i).END_OF_TERM_OBJ_ID IS NULL) THEN
1198 l_eto_tbl(i).END_OF_TERM_VER_ID := l_eve_rec.END_OF_TERM_VER_ID;
1199 okl_eto_pvt.insert_row(
1200 p_api_version => p_api_version,
1201 p_init_msg_list => p_init_msg_list,
1202 x_return_status => l_return_status,
1203 x_msg_count => x_msg_count,
1204 x_msg_data => x_msg_data,
1205 p_eto_rec => l_eto_tbl(i),
1206 x_eto_rec => x_eto_tbl(i));
1207
1208 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1209 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1210 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1211 raise OKL_API.G_EXCEPTION_ERROR;
1212 END IF;
1213
1214 END IF;
1215 END LOOP;
1216 END IF;
1217 --END IF; --Added by dcshanmu for bug 6699555 Commenting out END IF.
1218 END IF;
1219
1220 exception
1221 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1222 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1223 ( l_api_name,
1224 G_PKG_NAME,
1225 'OKL_API.G_RET_STS_ERROR',
1226 x_msg_count,
1227 x_msg_data,
1228 '_PVT');
1229 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1230 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1231 ( l_api_name,
1232 G_PKG_NAME,
1233 'OKL_API.G_RET_STS_UNEXP_ERROR',
1234 x_msg_count,
1235 x_msg_data,
1236 '_PVT');
1237
1238 WHEN OTHERS THEN
1239 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1240 ( l_api_name,
1241 G_PKG_NAME,
1242 'OTHERS',
1243 x_msg_count,
1244 x_msg_data,
1245 '_PVT');
1246
1247 END update_end_of_term_option;
1248
1249 -- create version for the end of term option
1250 PROCEDURE create_version(
1251 p_api_version IN NUMBER,
1252 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2,
1256 p_eve_rec IN okl_eve_rec,
1257 p_eto_tbl IN okl_eto_tbl,
1258 p_etv_tbl IN okl_etv_tbl,
1259 x_eve_rec OUT NOCOPY okl_eve_rec,
1260 x_eto_tbl OUT NOCOPY okl_eto_tbl,
1261 x_etv_tbl OUT NOCOPY okl_etv_tbl) AS
1262
1263 l_eve_rec okl_eve_rec := p_eve_rec;
1264 l_eto_tbl okl_eto_tbl := p_eto_tbl;
1265 l_etv_tbl okl_etv_tbl := p_etv_tbl;
1266 l_ethv_rec okl_ethv_rec;
1267 x_ethv_rec okl_ethv_rec;
1268 l_prev_ver_rec okl_eve_rec;
1269 x_prev_ver_rec okl_eve_rec;
1270 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1271 residual_type VARCHAR2(30);
1272 i NUMBER := 0;
1273 l_api_name VARCHAR2(40) := 'create_version';
1274 l_api_version NUMBER := 1.0;
1275 l_cal_eff_from DATE;
1276
1277 BEGIN
1278 l_return_status := OKL_API.start_activity(l_api_name
1279 ,g_pkg_name
1280 ,p_init_msg_list
1281 ,l_api_version
1282 ,p_api_version
1283 ,'_PVT'
1284 ,x_return_status);
1285
1286 l_eve_rec.effective_to_date := rosetta_g_miss_date_in_map(l_eve_rec.effective_to_date);
1287 calculate_start_date(
1288 p_api_version ,
1289 p_init_msg_list ,
1290 l_return_status ,
1291 x_msg_count ,
1292 x_msg_data ,
1293 l_eve_rec ,
1294 l_cal_eff_from );
1295
1296 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1297 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1298 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1299 raise OKL_API.G_EXCEPTION_ERROR;
1300 END IF;
1301
1302 IF (l_eve_rec.effective_from_date < l_cal_eff_from ) THEN
1303 RAISE INVALID_START_DATE;
1304 END IF;
1305
1306 l_ethv_rec.sts_code:= 'UNDER_REVISION';
1307 l_ethv_rec.end_of_term_id := l_eve_rec.end_of_term_id;
1308
1309 okl_eth_pvt.update_row(
1310 p_api_version => p_api_version,
1311 p_init_msg_list => p_init_msg_list,
1312 x_return_status => l_return_status,
1313 x_msg_count => x_msg_count,
1314 x_msg_data => x_msg_data,
1315 p_ethv_rec => l_ethv_rec,
1316 x_ethv_rec => x_ethv_rec
1317 );
1318
1319 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1320 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1321 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1322 raise OKL_API.G_EXCEPTION_ERROR;
1323 END IF;
1324
1325 -- insert the version record into the table
1326 okl_eve_pvt.insert_row(
1327 p_api_version => p_api_version,
1328 p_init_msg_list => p_init_msg_list,
1329 x_return_status => l_return_status,
1330 x_msg_count => x_msg_count,
1331 x_msg_data => x_msg_data,
1332 p_eve_rec => l_eve_rec,
1333 x_eve_rec => x_eve_rec);
1334
1335 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1336 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1337 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1338 raise OKL_API.G_EXCEPTION_ERROR;
1339 END IF;
1340
1341 --Added if condition by dcshanmu for bug 6699555
1342 --Objects and Term Values table will not be present for source type 'NONE'
1343 -- asahoo l_ethv_rec.category_code is not passed from UI. Hence getting from x_ethv_rec for bug#6892431
1344 IF(x_ethv_rec.category_type_code <> 'NONE') THEN
1345
1346 --populate the foreign key for the lines table
1347 IF (l_eto_tbl.COUNT >0) then
1348 i:= l_eto_tbl.FIRST;
1349 LOOP
1350 l_eto_tbl(i) := p_eto_tbl(i);
1351 l_eto_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1352 EXIT WHEN (i= l_eto_tbl.LAST);
1353 i := l_eto_tbl.NEXT(i);
1354 END LOOP;
1355 END IF;
1356 -- insert the lines record into the database
1357 okl_eto_pvt.insert_row(
1358 p_api_version => p_api_version,
1359 p_init_msg_list => p_init_msg_list,
1360 x_return_status => l_return_status,
1361 x_msg_count => x_msg_count,
1362 x_msg_data => x_msg_data,
1363 p_eto_tbl => l_eto_tbl,
1364 x_eto_tbl => x_eto_tbl);
1365 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1366 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1367 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1368 raise OKL_API.G_EXCEPTION_ERROR;
1369 END IF;
1370
1371 -- populate the foreign key for the values table
1372 IF (l_etv_tbl.COUNT > 0) then
1373 i:= l_etv_tbl.FIRST;
1374 LOOP
1375 l_etv_tbl(i) := p_etv_tbl(i);
1376 l_etv_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1377 EXIT WHEN (i= l_etv_tbl.LAST);
1378 i:= l_etv_tbl.NEXT(i);
1379 END LOOP;
1380 END IF;
1381 --viselvar 4604059 start
1382 -- validate the Values of terms
1383 IF x_ethv_rec.eot_type_code = 'PERCENT' THEN
1384 validate_percent_values(l_etv_tbl);
1385 END IF;
1386 --viselvar 4604059 end
1387 -- insert the values record into the database
1388 okl_etv_pvt.insert_row(
1389 p_api_version => p_api_version,
1390 p_init_msg_list => p_init_msg_list,
1391 x_return_status => l_return_status,
1392 x_msg_count => x_msg_count,
1393 x_msg_data => x_msg_data,
1394 p_etv_tbl => l_etv_tbl,
1395 x_etv_tbl => x_etv_tbl);
1396
1397 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1398 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1399 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1400 raise OKL_API.G_EXCEPTION_ERROR;
1401 END IF;
1402
1403 END IF; --Added by dcshanmu for bug 6699555
1404
1405 exception
1406 WHEN INVALID_START_DATE THEN
1407 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1408 p_msg_name => 'OKL_INVALID_EFF_FROM',
1409 p_token1 => 'DATE',
1410 p_token1_value => l_cal_eff_from);
1411 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1412 ( l_api_name,
1413 G_PKG_NAME,
1414 'OKL_API.G_RET_STS_ERROR',
1415 x_msg_count,
1416 x_msg_data,
1417 '_PVT');
1418
1419 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1420 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1421 ( l_api_name,
1422 G_PKG_NAME,
1423 'OKL_API.G_RET_STS_ERROR',
1424 x_msg_count,
1425 x_msg_data,
1426 '_PVT');
1427 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1428 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1429 ( l_api_name,
1430 G_PKG_NAME,
1431 'OKL_API.G_RET_STS_UNEXP_ERROR',
1432 x_msg_count,
1433 x_msg_data,
1434 '_PVT');
1435 WHEN OTHERS THEN
1436 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1437 ( l_api_name,
1438 G_PKG_NAME,
1439 'OTHERS',
1440 x_msg_count,
1441 x_msg_data,
1442 '_PVT');
1443 END create_version;
1444
1445 PROCEDURE validate_end_of_term_option(
1446 p_api_version IN NUMBER,
1447 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1448 x_return_status OUT NOCOPY VARCHAR2,
1449 x_msg_count OUT NOCOPY NUMBER,
1450 x_msg_data OUT NOCOPY VARCHAR2,
1451 p_end_of_ver_id IN NUMBER
1452 ) AS
1453
1454
1455 l_eto_tbl okl_eto_tbl;
1456 l_api_name VARCHAR2(40) := 'validate_end_of_term_option';
1457 l_eot_type VARCHAR2(30);
1458 l_cat_code VARCHAR2(30);
1459 i NUMBER := 0;
1460 j NUMBER;
1461 int_count NUMBER;
1462
1463 CURSOR cat_type_code_csr(p_end_of_term_ver_id IN NUMBER) IS
1464 SELECT category_type_code,eot_type_code FROM OKL_FE_EO_TERMS_V hdr, OKL_FE_EO_TERM_VERS ver
1465 WHERE hdr.end_of_term_id = ver.end_of_term_id AND ver.end_of_term_ver_id = p_end_of_term_ver_id;
1466
1467 CURSOR objects_csr(p_end_of_term_ver_id IN NUMBER) IS
1468 SELECT inventory_item_id, organization_id, category_id, category_set_id, resi_category_set_id
1469 FROM OKL_FE_EO_TERM_OBJECTS
1470 WHERE end_of_term_ver_id=p_end_of_term_ver_id;
1471
1472 cursor repeat_csr(id1 NUMBER, id2 NUMBER) is
1473 select count(*) from
1474 (
1475 select organization_id, inventory_item_id, category_id, category_set_id
1476 from OKL_FE_RESI_CAT_OBJECTS where resi_category_set_id = id1
1477 intersect
1478 select organization_id, inventory_item_id, category_id, category_set_id
1479 from OKL_FE_RESI_CAT_OBJECTS where resi_category_set_id = id2
1480 );
1481
1482 BEGIN
1483
1484 -- If the residual type is 'residual category set', then we need to check for items repeating
1485 -- in that residual amount
1486 OPEN cat_type_code_csr(p_end_of_ver_id);
1487 FETCH cat_type_code_csr INTO l_cat_code,l_eot_type;
1488 CLOSE cat_type_code_csr;
1489
1490 FOR l_objects_rec IN objects_csr(p_end_of_ver_id) LOOP
1491 l_eto_tbl(i).inventory_item_id:=l_objects_rec.inventory_item_id;
1492 l_eto_tbl(i).organization_id :=l_objects_rec.organization_id;
1493 l_eto_tbl(i).category_id :=l_objects_rec.category_id;
1494 l_eto_tbl(i).category_set_id :=l_objects_rec.category_set_id;
1495 l_eto_tbl(i).resi_category_set_id :=l_objects_rec.resi_category_set_id;
1496 END LOOP;
1497
1498 IF( l_eto_tbl.COUNT> 0) then
1499 i := l_eto_tbl.FIRST;
1500 IF (l_cat_code= 'RESIDUAL_CAT_SET') and
1501 (l_eot_type='RESIDUAL_AMOUNT' or l_eot_type='RESIDUAL_PERCENT') then
1502 FOR i IN l_eto_tbl.FIRST..l_eto_tbl.LAST-1 LOOP
1503 FOR j IN i+1..l_eto_tbl.LAST LOOP
1504 OPEN repeat_csr(l_eto_tbl(i).resi_category_set_id, l_eto_tbl(j).resi_category_set_id);
1505 FETCH repeat_csr into int_count;
1506 CLOSE repeat_csr;
1507 IF (int_count > 0) THEN
1508 RAISE EXCEPTION_ITEM_REPEAT;
1509 END IF;
1510 END LOOP;
1511 END LOOP;
1512 END IF;
1513 END IF;
1514 exception
1515 WHEN EXCEPTION_ITEM_REPEAT THEN
1516 null;
1517 WHEN OTHERS THEN
1518 IF (repeat_csr%ISOPEN) then
1519 CLOSE repeat_csr;
1520 END IF;
1521 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1522 ( l_api_name,
1523 G_PKG_NAME,
1524 'OTHERS',
1525 x_msg_count,
1526 x_msg_data,
1527 '_PVT');
1528 END validate_end_of_term_option;
1529
1530 PROCEDURE handle_approval(
1531 p_api_version IN NUMBER,
1532 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1533 x_return_status OUT NOCOPY VARCHAR2,
1534 x_msg_count OUT NOCOPY NUMBER,
1535 x_msg_data OUT NOCOPY VARCHAR2,
1536 p_end_of_term_ver_id IN NUMBER) AS
1537 cal_end_date DATE;
1538 l_eve_rec okl_eve_rec ;
1539 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_ERROR;
1540 x_eve_rec okl_eve_rec;
1541 l_ethv_rec okl_ethv_rec;
1542 x_ethv_rec okl_ethv_rec;
1543 l_eff_from DATE;
1544 l_eff_to DATE;
1545 l_ver_number VARCHAR2(24);
1546 l_eot_id NUMBER;
1547 l_api_name VARCHAR2(40) := 'handle_approval';
1548 l_api_version NUMBER := 1.0;
1549 l_cal_eff_from DATE;
1550 l_prev_ver_id NUMBER;
1551 l_prev_ver_eff_to DATE;
1552 l_prev_eve_rec okl_eve_rec;
1553 x_prev_eve_rec okl_eve_rec;
1554 x_obj_tbl invalid_object_tbl;
1555 i NUMBER;
1556 lp_lrtv_tbl okl_lrs_id_tbl;
1557
1558 -- cursor to get the data of the versions record
1559 CURSOR eot_versions_csr(p_ver_id IN NUMBER) IS
1560 SELECT END_OF_TERM_ID,
1561 VERSION_NUMBER,
1562 EFFECTIVE_FROM_DATE,
1563 EFFECTIVE_TO_DATE
1564 FROM OKL_FE_EO_TERM_VERS WHERE END_OF_TERM_VER_ID=p_ver_id;
1565
1566 CURSOR ver_eff_to_csr(p_eot_id IN NUMBER, p_version_number IN NUMBER)IS
1567 SELECT END_OF_TERM_VER_ID,
1568 EFFECTIVE_TO_DATE FROM okl_fe_eo_term_vers
1569 WHERE END_OF_TERM_ID=p_eot_id and VERSION_NUMBER = p_version_number;
1570
1571 BEGIN
1572 l_return_status := OKL_API.start_activity(l_api_name
1573 ,g_pkg_name
1574 ,p_init_msg_list
1575 ,l_api_version
1576 ,p_api_version
1577 ,'_PVT'
1578 ,x_return_status);
1579
1580 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1581 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1582 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1583 raise OKL_API.G_EXCEPTION_ERROR;
1584 END IF;
1585
1586 l_return_status := val_avail_item_residual(p_end_of_term_ver_id);
1587 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1588 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1589 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1590 raise OKL_API.G_EXCEPTION_ERROR;
1591 END IF;
1592
1593 -- fetch thte details of the versions record and populate them into versions record
1594 OPEN eot_versions_csr(p_end_of_term_ver_id);
1595 FETCH eot_versions_csr INTO l_eot_id,l_ver_number,l_eff_from, l_eff_to;
1596 CLOSE eot_versions_csr;
1597
1598 -- set the properties of the versions record
1599 l_eve_rec.STS_CODE:='ACTIVE';
1600 l_eve_rec.END_OF_TERM_VER_ID:= p_end_of_term_ver_id;
1601 l_eve_rec.END_OF_TERM_ID:= l_eot_id;
1602 l_eve_rec.VERSION_NUMBER:= l_ver_number;
1603 l_eve_rec.EFFECTIVE_FROM_DATE:=l_eff_from;
1604
1605 IF (l_eff_to is not null) THEN
1606 l_eve_rec.EFFECTIVE_TO_DATE:=l_eff_to;
1607 ELSE
1608 l_eve_rec.EFFECTIVE_TO_DATE:= OKL_API.G_MISS_DATE;
1609 END IF;
1610
1611 IF (l_eve_rec.VERSION_NUMBER >1 ) THEN
1612 calculate_start_date(
1613 p_api_version ,
1614 p_init_msg_list ,
1615 l_return_status ,
1616 x_msg_count ,
1617 x_msg_data ,
1618 l_eve_rec ,
1619 l_cal_eff_from );
1620
1621 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1622 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1623 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1624 raise OKL_API.G_EXCEPTION_ERROR;
1625 END IF;
1626
1627 IF (l_eve_rec.effective_from_date < l_cal_eff_from ) THEN
1628 RAISE INVALID_START_DATE;
1629 END IF;
1630
1631 OPEN ver_eff_to_csr(l_eot_id,l_ver_number-1);
1632 FETCH ver_eff_to_csr INTO l_prev_ver_id,l_prev_ver_eff_to;
1633 CLOSE ver_eff_to_csr;
1634
1635 l_prev_eve_rec.end_of_term_ver_id:=l_prev_ver_id;
1636 l_prev_eve_rec.effective_to_date:=l_eve_rec.effective_from_date-1;
1637
1638 -- end date the lease rate set versions
1639 INVALID_OBJECTS(
1640 p_api_version => p_api_version,
1641 p_init_msg_list => p_init_msg_list,
1642 x_return_status => l_return_status,
1643 x_msg_count => x_msg_count,
1644 x_msg_data => x_msg_data,
1645 p_version_id => l_prev_ver_id,
1646 x_obj_tbl => x_obj_tbl
1647 );
1648 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1649 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1650 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1651 raise OKL_API.G_EXCEPTION_ERROR;
1652 END IF;
1653
1654 IF (x_obj_tbl.COUNT >0) THEN
1655 FOR i IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1656 lp_lrtv_tbl(i) := x_obj_tbl(i).obj_id;
1657 END LOOP;
1658
1659 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(
1660 p_api_version
1661 ,p_init_msg_list
1662 ,l_return_status
1663 ,x_msg_count
1664 ,x_msg_data
1665 ,lp_lrtv_tbl
1666 ,l_prev_eve_rec.effective_to_date
1667 );
1668 END IF;
1669
1670 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1671 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1672 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1673 raise OKL_API.G_EXCEPTION_ERROR;
1674 END IF;
1675
1676 -- update the previous version record
1677 okl_eve_pvt.update_row(
1678 p_api_version => p_api_version,
1679 p_init_msg_list => p_init_msg_list,
1680 x_return_status => l_return_status,
1681 x_msg_count => x_msg_count,
1682 x_msg_data => x_msg_data,
1683 p_eve_rec => l_prev_eve_rec,
1684 x_eve_rec => x_prev_eve_rec);
1685
1686 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1687 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1688 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1689 raise OKL_API.G_EXCEPTION_ERROR;
1690 END IF;
1691
1692 END IF;
1693
1694 -- update the version record
1695 okl_eve_pvt.update_row(
1696 p_api_version => p_api_version,
1697 p_init_msg_list => p_init_msg_list,
1698 x_return_status => l_return_status,
1699 x_msg_count => x_msg_count,
1700 x_msg_data => x_msg_data,
1701 p_eve_rec => l_eve_rec,
1702 x_eve_rec => x_eve_rec);
1703
1704 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1705 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1706 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1707 raise OKL_API.G_EXCEPTION_ERROR;
1708 END IF;
1709 -- change the status in the header record also as active for search purcpose only
1710 l_ethv_rec.sts_code:= 'ACTIVE';
1711 l_ethv_rec.end_of_term_id := x_eve_rec.end_of_term_id;
1712
1713 IF (l_eve_rec.EFFECTIVE_TO_DATE is not null) THEN
1714 l_ethv_rec.EFFECTIVE_TO_DATE :=x_eve_rec.EFFECTIVE_TO_DATE;
1715 ELSE
1716 l_ethv_rec.EFFECTIVE_TO_DATE :=OKL_API.G_MISS_DATE;
1717 END IF;
1718 okl_eth_pvt.update_row(
1719 p_api_version => p_api_version,
1720 p_init_msg_list => p_init_msg_list,
1721 x_return_status => l_return_status,
1722 x_msg_count => x_msg_count,
1723 x_msg_data => x_msg_data,
1724 p_ethv_rec => l_ethv_rec,
1725 x_ethv_rec => x_ethv_rec
1726 );
1727 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1728 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1729 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1730 raise OKL_API.G_EXCEPTION_ERROR;
1731 END IF;
1732
1733 -- make the change to the previous 0.
1734
1735 exception
1736 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1737 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1738 ( l_api_name,
1739 G_PKG_NAME,
1740 'OKL_API.G_RET_STS_ERROR',
1741 x_msg_count,
1742 x_msg_data,
1743 '_PVT');
1744 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1745 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1746 ( l_api_name,
1747 G_PKG_NAME,
1748 'OKL_API.G_RET_STS_UNEXP_ERROR',
1749 x_msg_count,
1750 x_msg_data,
1751 '_PVT');
1752
1753 WHEN OTHERS THEN
1754 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1755 ( l_api_name,
1756 G_PKG_NAME,
1757 'OTHERS',
1758 x_msg_count,
1759 x_msg_data,
1760 '_PVT');
1761
1762
1763 END handle_approval;
1764 PROCEDURE INVALID_OBJECTS(
1765 p_api_version IN NUMBER,
1766 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1767 x_return_status OUT NOCOPY VARCHAR2,
1768 x_msg_count OUT NOCOPY NUMBER,
1769 x_msg_data OUT NOCOPY VARCHAR2,
1770 p_version_id IN NUMBER,
1771 x_obj_tbl OUT NOCOPY invalid_object_tbl
1772 )AS
1773
1774 -- cursor to calculate the LRS objects which are referncing this adjustment matrix
1775 CURSOR lrs_invalids_csr(p_version_id IN NUMBER) IS
1776 SELECT vers.RATE_SET_VERSION_ID ID,hdr.name NAME,vers.version_number VERSION_NUMBER
1777 FROM OKL_FE_RATE_SET_VERSIONS vers, OKL_LS_RT_FCTR_SETS_V hdr
1778 WHERE vers.rate_set_id = hdr.id AND vers.end_of_term_ver_id=p_version_id
1779 AND vers.STS_CODE='ACTIVE';
1780
1781 l_version_id NUMBER :=p_version_id;
1782 i NUMBER:=1;
1783 l_api_version NUMBER := 1.0;
1784 l_api_name VARCHAR2(40):='invalid_objects';
1785 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
1786
1787 BEGIN
1788
1789 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1790 G_PKG_NAME,
1791 p_init_msg_list,
1792 l_api_version,
1793 p_api_version,
1794 '_PVT',
1795 x_return_status);
1796
1797 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1798 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1799 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1800 RAISE OKL_API.G_EXCEPTION_ERROR;
1801 END IF;
1802
1803 FOR lrs_invalid_record IN lrs_invalids_csr(p_version_id) LOOP
1804 x_obj_tbl(i).obj_id :=lrs_invalid_record.id;
1805 x_obj_tbl(i).obj_name:=lrs_invalid_record.NAME;
1806 x_obj_tbl(i).obj_version :=lrs_invalid_record.VERSION_NUMBER;
1807 x_obj_tbl(i).obj_type:='LRS';
1808 i:=i+1;
1809 END LOOP;
1810
1811 --end activity
1812 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1813 x_return_status := l_return_status;
1814
1815
1816 EXCEPTION
1817 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1818 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1819 (
1820 l_api_name,
1821 G_PKG_NAME,
1822 'OKL_API.G_RET_STS_ERROR',
1823 x_msg_count,
1824 x_msg_data,
1825 '_PVT'
1826 );
1827
1828 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1829 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1830 (
1831 l_api_name,
1832 G_PKG_NAME,
1833 'OKL_API.G_RET_STS_UNEXP_ERROR',
1834 x_msg_count,
1835 x_msg_data,
1836 '_PVT'
1837 );
1838
1839 WHEN OTHERS THEN
1840 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1841 (
1842 l_api_name,
1843 G_PKG_NAME,
1844 'OTHERS',
1845 x_msg_count,
1846 x_msg_data,
1847 '_PVT'
1848 );
1849
1850 END INVALID_OBJECTS;
1851
1852 PROCEDURE submit_end_of_term(
1853 p_api_version IN NUMBER,
1854 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1855 x_return_status OUT NOCOPY VARCHAR2,
1856 x_msg_count OUT NOCOPY NUMBER,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 p_end_of_term_ver_id IN NUMBER) AS
1859
1860 l_eve_rec okl_eve_rec;
1861 x_eve_rec okl_eve_rec;
1862 l_api_version NUMBER := 1.0;
1863 l_api_name VARCHAR2(40):='submit_end_of_term';
1864 l_init_msg_list VARCHAR2(1):=p_init_msg_list;
1865 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1866 l_parameter_list wf_parameter_list_t;
1867 p_event_name varchar2(240):='oracle.apps.okl.fe.eotapproval';
1868 l_profile_value varchar2(30);
1869
1870 -- Cursor to check if the residual category sets are active before Activating the Item Residual
1871 -- Pass the Item Residual Identifier and the Status as ACTIVE to check for Inactive Residual Category Sets
1872 CURSOR check_active_resi_cat_sets(p_itm_rsdl_id NUMBER, p_rcs_sts_code VARCHAR2) IS
1873 SELECT
1874 RCSV.RESI_CATEGORY_SET_ID ID
1875 , RCSV.RESI_CAT_NAME NAME
1876 FROM
1877 OKL_FE_RESI_CAT_V RCSV
1878 , OKL_FE_ITEM_RESIDUAL IRESDV
1879 WHERE
1880 IRESDV.CATEGORY_TYPE_CODE = 'RESCAT'
1881 AND IRESDV.RESI_CATEGORY_SET_ID = RCSV.RESI_CATEGORY_SET_ID
1882 AND RCSV.STS_CODE <> p_rcs_sts_code
1883 AND IRESDV.item_residual_id = p_itm_rsdl_id;
1884
1885 l_eot_id NUMBER;
1886 l_source_type OKL_FE_ITEM_RESIDUAL_ALL.CATEGORY_TYPE_CODE%TYPE;
1887 l_rcs_rec check_active_resi_cat_sets%ROWTYPE;
1888
1889 CURSOR get_eot_id(p_ver_id IN NUMBER) IS
1890 SELECT END_OF_TERM_ID
1891 FROM OKL_FE_EO_TERM_VERS WHERE END_OF_TERM_VER_ID=p_ver_id;
1892
1893 CURSOR get_source_type(p_eot_id IN NUMBER)IS
1894 SELECT EOT_TYPE_CODE
1895 FROM OKL_FE_EO_TERMS_V where end_of_term_id= p_eot_id;
1896 BEGIN
1897 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1898 G_PKG_NAME,
1899 l_init_msg_list,
1900 l_api_version,
1901 p_api_version,
1902 '_PVT',
1903 x_return_status);
1904
1905 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1906 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1907 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1908 RAISE OKL_API.G_EXCEPTION_ERROR;
1909 END IF;
1910
1911 l_return_status := val_avail_item_residual(p_end_of_term_ver_id);
1912 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1913 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1914 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1915 raise OKL_API.G_EXCEPTION_ERROR;
1916 END IF;
1917 OPEN get_eot_id(p_end_of_term_ver_id);
1918 FETCH get_eot_id INTO l_eot_id;
1919 CLOSE get_eot_id;
1920
1921 OPEN get_source_type (l_eot_id);
1922 FETCH get_source_type INTO l_source_type;
1923 CLOSE get_source_type;
1924
1925 IF l_source_type = 'RESCAT' THEN
1926 OPEN check_active_resi_cat_sets(l_eot_id,'ACTIVE');
1927 FETCH check_active_resi_cat_sets INTO l_rcs_rec;
1928 IF check_active_resi_cat_sets%FOUND THEN
1929 LOOP
1930 OKL_API.set_message(p_app_name => G_APP_NAME,
1931 p_msg_name => 'OKL_RCS_STS_INACTIVE',
1932 p_token1 => OKL_API.G_COL_NAME_TOKEN,
1933 p_token1_value => l_rcs_rec.name);
1934 FETCH check_active_resi_cat_sets INTO l_rcs_rec;
1935 EXIT WHEN check_active_resi_cat_sets%NOTFOUND;
1936 END LOOP;
1937 RAISE OKL_API.G_EXCEPTION_ERROR;
1938 END IF;
1939 CLOSE check_active_resi_cat_sets;
1940 END IF;
1941
1942 l_eve_rec.end_of_term_ver_id := p_end_of_term_ver_id;
1943 l_eve_rec.STS_CODE := 'SUBMITTED';
1944
1945 okl_eve_pvt.update_row( l_api_version
1946 ,p_init_msg_list
1947 ,l_return_status
1948 ,x_msg_count
1949 ,x_msg_data
1950 ,l_eve_rec
1951 ,x_eve_rec);
1952
1953 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1954 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1955 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1956 RAISE OKL_API.G_EXCEPTION_ERROR;
1957 END IF;
1958 fnd_profile.get('OKL_PE_APPROVAL_PROCESS',l_profile_value);
1959
1960 IF (nvl(l_profile_value,'NONE') = 'NONE') THEN
1961
1962 HANDLE_APPROVAL(
1963 p_api_version => l_api_version,
1964 p_init_msg_list => p_init_msg_list,
1965 x_return_status => l_return_status,
1966 x_msg_count => x_msg_count,
1967 x_msg_data => x_msg_data,
1968 p_end_of_term_ver_id => p_end_of_term_ver_id
1969 );
1970
1971 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1972 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1973 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1974 RAISE OKL_API.G_EXCEPTION_ERROR;
1975 END IF;
1976 ELSE
1977 -- raise the business event passing the version id added to the parameter list
1978 wf_event.AddParameterToList('VERSION_ID',p_end_of_term_ver_id,l_parameter_list);
1979 --added by akrangan
1980 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
1981 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
1982 p_init_msg_list => p_init_msg_list,
1983 x_return_status => x_return_status,
1984 x_msg_count => x_msg_count,
1985 x_msg_data => x_msg_data,
1986 p_event_name => p_event_name,
1987 p_parameters => l_parameter_list);
1988
1989
1990
1991 END IF;
1992
1993
1994 --end activity
1995 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1996 x_return_status := l_return_status;
1997
1998 EXCEPTION
1999 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2000 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2001 (
2002 l_api_name,
2003 G_PKG_NAME,
2004 'OKL_API.G_RET_STS_ERROR',
2005 x_msg_count,
2006 x_msg_data,
2007 '_PVT'
2008 );
2009
2010 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2011 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2012 (
2013 l_api_name,
2014 G_PKG_NAME,
2015 'OKL_API.G_RET_STS_UNEXP_ERROR',
2016 x_msg_count,
2017 x_msg_data,
2018 '_PVT'
2019 );
2020
2021 WHEN OTHERS THEN
2022 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2023 (
2024 l_api_name,
2025 G_PKG_NAME,
2026 'OTHERS',
2027 x_msg_count,
2028 x_msg_data,
2029 '_PVT'
2030 );
2031 end submit_end_of_term;
2032 END OKL_FE_EO_TERM_OPTIONS_PVT;