[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.12 2008/03/28 22:07:26 asahoo noship $ */
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 l_eve_rec.effective_to_date := rosetta_g_miss_date_in_map(l_eve_rec.effective_to_date);
932
933 -- assign the in records to the out records
934 x_eve_rec := p_eve_rec;
935 x_eto_tbl := p_eto_tbl;
936 x_etv_tbl := p_etv_tbl;
937
938 IF (l_eve_rec.version_number >1 and l_eve_rec.STS_CODE ='NEW') THEN
939 calculate_start_date(
940 l_api_version
941 ,l_init_msg_list
942 ,l_return_status
943 ,x_msg_count
944 ,x_msg_data
945 ,l_eve_rec
946 ,cal_eff_from);
947
948 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
949 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
950 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
951 raise OKL_API.G_EXCEPTION_ERROR;
952 END IF;
953
954 IF ( l_eve_rec.effective_from_date < cal_eff_from ) THEN
955 RAISE INVALID_START_DATE;
956 END IF;
957 END IF;
958
959 -- if the status of the version is new, then the version record can be modified
960 -- objects can be modified or added
961 -- term value pairs can be added or modified
962 IF (l_eve_rec.STS_CODE='NEW') THEN
963 IF (l_eve_rec.VERSION_NUMBER = 1) THEN
964 l_ethv_rec.end_of_term_id := l_eve_rec.end_of_term_id;
965 l_ethv_rec.effective_from_date := l_eve_rec.effective_from_date;
966 okl_eth_pvt.update_row(
967 p_api_version => p_api_version,
968 p_init_msg_list => p_init_msg_list,
969 x_return_status => l_return_status,
970 x_msg_count => x_msg_count,
971 x_msg_data => x_msg_data,
972 p_ethv_rec => l_ethv_rec,
973 x_ethv_rec => x_ethv_rec
974 );
975 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
976 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
977 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
978 raise OKL_API.G_EXCEPTION_ERROR;
979 END IF;
980
981 END IF;
982 -- update the version record
983 okl_eve_pvt.update_row(
984 p_api_version => p_api_version,
985 p_init_msg_list => p_init_msg_list,
986 x_return_status => l_return_status,
987 x_msg_count => x_msg_count,
988 x_msg_data => x_msg_data,
989 p_eve_rec => l_eve_rec,
990 x_eve_rec => x_eve_rec);
991
992 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
993 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
994 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
995 raise OKL_API.G_EXCEPTION_ERROR;
996 END IF;
997
998 --Added if condition by dcshanmu for bug 6699555
999 --Objects and Term Values table will not be present for source type 'NONE'
1000 --Commenting out the check for 'NONE' condition as UI is not passing the value
1001 --This check is already done UI layer. Fix for bug#6892431
1002 -- IF(l_ethv_rec.category_type_code <> 'NONE') THEN
1003
1004 -- update the lines table
1005 IF (l_eto_tbl.COUNT >0) then
1006 i := l_eto_tbl.FIRST;
1007 LOOP
1008 IF (l_eto_tbl(i).END_OF_TERM_OBJ_ID is not null) THEN
1009 okl_eto_pvt.update_row(
1010 p_api_version => p_api_version,
1011 p_init_msg_list => p_init_msg_list,
1012 x_return_status => l_return_status,
1013 x_msg_count => x_msg_count,
1014 x_msg_data => x_msg_data,
1015 p_eto_rec => l_eto_tbl(i),
1016 x_eto_rec => x_eto_tbl(i));
1017
1018 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1019 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1020 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1021 raise OKL_API.G_EXCEPTION_ERROR;
1022 END IF;
1023
1024 ELSIF (l_eto_tbl(i).END_OF_TERM_OBJ_ID is null ) THEN
1025
1026 l_eto_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1027
1028 okl_eto_pvt.insert_row(
1029 p_api_version => p_api_version,
1030 p_init_msg_list => p_init_msg_list,
1031 x_return_status => l_return_status,
1032 x_msg_count => x_msg_count,
1033 x_msg_data => x_msg_data,
1034 p_eto_rec => l_eto_tbl(i),
1035 x_eto_rec => x_eto_tbl(i));
1036
1037 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1038 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1039 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1040 raise OKL_API.G_EXCEPTION_ERROR;
1041 END IF;
1042
1043 END IF;
1044 EXIT WHEN (i= l_eto_tbl.LAST);
1045 i := l_eto_tbl.NEXT(i);
1046 END LOOP;
1047 END IF;
1048 -- update the values table
1049 IF (l_etv_tbl.COUNT >0) then
1050 i := l_etv_tbl.FIRST;
1051 --viselvar 4604059 start
1052 -- validate the Values of terms
1053 IF x_ethv_rec.eot_type_code = 'PERCENT' THEN
1054 validate_percent_values(l_etv_tbl);
1055 END IF;
1056 --viselvar 4604059 end
1057 LOOP
1058 IF (l_etv_tbl(i).END_OF_TERM_VALUE_ID is not null) THEN
1059 okl_etv_pvt.update_row(
1060 p_api_version => p_api_version,
1061 p_init_msg_list => p_init_msg_list,
1062 x_return_status => l_return_status,
1063 x_msg_count => x_msg_count,
1064 x_msg_data => x_msg_data,
1065 p_etv_rec => l_etv_tbl(i),
1066 x_etv_rec => x_etv_tbl(i));
1067 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1068 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1069 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1070 raise OKL_API.G_EXCEPTION_ERROR;
1071 END IF;
1072
1073 ELSIF (l_etv_tbl(i).END_OF_TERM_VALUE_ID is null) THEN
1074 l_etv_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1075
1076 okl_etv_pvt.insert_row(
1077 p_api_version => p_api_version,
1078 p_init_msg_list => p_init_msg_list,
1079 x_return_status => l_return_status,
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data,
1082 p_etv_rec => l_etv_tbl(i),
1083 x_etv_rec => x_etv_tbl(i));
1084
1085 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1086 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1087 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1088 raise OKL_API.G_EXCEPTION_ERROR;
1089 END IF;
1090
1091 END IF;
1092 EXIT WHEN (i= l_etv_tbl.LAST);
1093 i := l_etv_tbl.NEXT(i);
1094 END LOOP;
1095
1096 END IF;
1097 --END IF; --Added by dcshanmu for bug 6699555. Commenting out the END IF.
1098
1099 ELSIF (l_eve_rec.STS_CODE = 'ACTIVE') THEN
1100 OPEN latest_active_ver_csr(l_eve_rec.END_OF_TERM_ID);
1101 FETCH latest_active_ver_csr INTO l_lat_act_ver;
1102 CLOSE latest_active_ver_csr;
1103
1104 IF (l_eve_rec.effective_to_date IS NOT NULL) THEN
1105 -- end date the lease rate set versions
1106 INVALID_OBJECTS(
1107 p_api_version => p_api_version,
1108 p_init_msg_list => p_init_msg_list,
1109 x_return_status => l_return_status,
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data,
1112 p_version_id => l_eve_rec.end_of_term_ver_id,
1113 x_obj_tbl => x_obj_tbl
1114 );
1115
1116 IF (x_obj_tbl.COUNT >0) THEN
1117 FOR j IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1118 lp_lrtv_tbl(j) := x_obj_tbl(j).obj_id;
1119 END LOOP;
1120
1121 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(
1122 p_api_version
1123 ,p_init_msg_list
1124 ,l_return_status
1125 ,x_msg_count
1126 ,x_msg_data
1127 ,lp_lrtv_tbl
1128 ,l_eve_rec.effective_to_date
1129 );
1130 END IF;
1131 END IF;
1132 IF (l_eve_rec.version_number = l_lat_act_ver) THEN
1133 -- update the header record
1134 -- viselvar 4604059 start
1135 l_ethv_rec.EFFECTIVE_TO_DATE := l_eve_rec.EFFECTIVE_TO_DATE;
1136 -- viselvar 4604059 end
1137 l_ethv_rec.END_OF_TERM_ID:= l_eve_rec.end_of_term_id;
1138 okl_eth_pvt.update_row(
1139 p_api_version => p_api_version,
1140 p_init_msg_list => p_init_msg_list,
1141 x_return_status => l_return_status,
1142 x_msg_count => x_msg_count,
1143 x_msg_data => x_msg_data,
1144 p_ethv_rec => l_ethv_rec,
1145 x_ethv_rec => x_ethv_rec
1146 );
1147 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1148 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1149 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1150 raise OKL_API.G_EXCEPTION_ERROR;
1151 END IF;
1152
1153 -- update the version record
1154 okl_eve_pvt.update_row(
1155 p_api_version => p_api_version,
1156 p_init_msg_list => p_init_msg_list,
1157 x_return_status => l_return_status,
1158 x_msg_count => x_msg_count,
1159 x_msg_data => x_msg_data,
1160 p_eve_rec => l_eve_rec,
1161 x_eve_rec => x_eve_rec);
1162
1163 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1164 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1165 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1166 raise OKL_API.G_EXCEPTION_ERROR;
1167 END IF;
1168 END IF;
1169
1170 --Added if condition by dcshanmu for bug 6699555
1171 --Objects table will not be present for source type 'NONE'
1172 --Commenting out the check for 'NONE' condition as UI is not passing the value
1173 --This check is already done UI layer. Fix for bug#6892431
1174 --IF(l_ethv_rec.category_type_code <> 'NONE') THEN
1175
1176 -- add only objects if the status is active
1177 FOR i IN l_eto_tbl.FIRST .. l_eto_tbl.LAST LOOP
1178 IF (l_eto_tbl(i).END_OF_TERM_OBJ_ID IS NULL) THEN
1179 l_eto_tbl(i).END_OF_TERM_VER_ID := l_eve_rec.END_OF_TERM_VER_ID;
1180 okl_eto_pvt.insert_row(
1181 p_api_version => p_api_version,
1182 p_init_msg_list => p_init_msg_list,
1183 x_return_status => l_return_status,
1184 x_msg_count => x_msg_count,
1185 x_msg_data => x_msg_data,
1186 p_eto_rec => l_eto_tbl(i),
1187 x_eto_rec => x_eto_tbl(i));
1188
1189 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1190 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1191 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1192 raise OKL_API.G_EXCEPTION_ERROR;
1193 END IF;
1194
1195 END IF;
1196 END LOOP;
1197 --END IF; --Added by dcshanmu for bug 6699555 Commenting out END IF.
1198 END IF;
1199
1200 exception
1201 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1202 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1203 ( l_api_name,
1204 G_PKG_NAME,
1205 'OKL_API.G_RET_STS_ERROR',
1206 x_msg_count,
1207 x_msg_data,
1208 '_PVT');
1209 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1210 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1211 ( l_api_name,
1212 G_PKG_NAME,
1213 'OKL_API.G_RET_STS_UNEXP_ERROR',
1214 x_msg_count,
1215 x_msg_data,
1216 '_PVT');
1217
1218 WHEN OTHERS THEN
1219 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1220 ( l_api_name,
1221 G_PKG_NAME,
1222 'OTHERS',
1223 x_msg_count,
1224 x_msg_data,
1225 '_PVT');
1226
1227 END update_end_of_term_option;
1228
1229 -- create version for the end of term option
1230 PROCEDURE create_version(
1231 p_api_version IN NUMBER,
1232 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1233 x_return_status OUT NOCOPY VARCHAR2,
1234 x_msg_count OUT NOCOPY NUMBER,
1235 x_msg_data OUT NOCOPY VARCHAR2,
1236 p_eve_rec IN okl_eve_rec,
1237 p_eto_tbl IN okl_eto_tbl,
1238 p_etv_tbl IN okl_etv_tbl,
1239 x_eve_rec OUT NOCOPY okl_eve_rec,
1240 x_eto_tbl OUT NOCOPY okl_eto_tbl,
1241 x_etv_tbl OUT NOCOPY okl_etv_tbl) AS
1242
1243 l_eve_rec okl_eve_rec := p_eve_rec;
1244 l_eto_tbl okl_eto_tbl := p_eto_tbl;
1245 l_etv_tbl okl_etv_tbl := p_etv_tbl;
1246 l_ethv_rec okl_ethv_rec;
1247 x_ethv_rec okl_ethv_rec;
1248 l_prev_ver_rec okl_eve_rec;
1249 x_prev_ver_rec okl_eve_rec;
1250 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1251 residual_type VARCHAR2(30);
1252 i NUMBER := 0;
1253 l_api_name VARCHAR2(40) := 'create_version';
1254 l_api_version NUMBER := 1.0;
1255 l_cal_eff_from DATE;
1256
1257 BEGIN
1258 l_return_status := OKL_API.start_activity(l_api_name
1259 ,g_pkg_name
1260 ,p_init_msg_list
1261 ,l_api_version
1262 ,p_api_version
1263 ,'_PVT'
1264 ,x_return_status);
1265
1266 l_eve_rec.effective_to_date := rosetta_g_miss_date_in_map(l_eve_rec.effective_to_date);
1267 calculate_start_date(
1268 p_api_version ,
1269 p_init_msg_list ,
1270 l_return_status ,
1271 x_msg_count ,
1272 x_msg_data ,
1273 l_eve_rec ,
1274 l_cal_eff_from );
1275
1276 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1277 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1278 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1279 raise OKL_API.G_EXCEPTION_ERROR;
1280 END IF;
1281
1282 IF (l_eve_rec.effective_from_date < l_cal_eff_from ) THEN
1283 RAISE INVALID_START_DATE;
1284 END IF;
1285
1286 l_ethv_rec.sts_code:= 'UNDER_REVISION';
1287 l_ethv_rec.end_of_term_id := l_eve_rec.end_of_term_id;
1288
1289 okl_eth_pvt.update_row(
1290 p_api_version => p_api_version,
1291 p_init_msg_list => p_init_msg_list,
1292 x_return_status => l_return_status,
1293 x_msg_count => x_msg_count,
1294 x_msg_data => x_msg_data,
1295 p_ethv_rec => l_ethv_rec,
1296 x_ethv_rec => x_ethv_rec
1297 );
1298
1299 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1300 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1301 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1302 raise OKL_API.G_EXCEPTION_ERROR;
1303 END IF;
1304
1305 -- insert the version record into the table
1306 okl_eve_pvt.insert_row(
1307 p_api_version => p_api_version,
1308 p_init_msg_list => p_init_msg_list,
1309 x_return_status => l_return_status,
1310 x_msg_count => x_msg_count,
1311 x_msg_data => x_msg_data,
1312 p_eve_rec => l_eve_rec,
1313 x_eve_rec => x_eve_rec);
1314
1315 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1316 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1317 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1318 raise OKL_API.G_EXCEPTION_ERROR;
1319 END IF;
1320
1321 --Added if condition by dcshanmu for bug 6699555
1322 --Objects and Term Values table will not be present for source type 'NONE'
1323 -- asahoo l_ethv_rec.category_code is not passed from UI. Hence getting from x_ethv_rec for bug#6892431
1324 IF(x_ethv_rec.category_type_code <> 'NONE') THEN
1325
1326 --populate the foreign key for the lines table
1327 IF (l_eto_tbl.COUNT >0) then
1328 i:= l_eto_tbl.FIRST;
1329 LOOP
1330 l_eto_tbl(i) := p_eto_tbl(i);
1331 l_eto_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1332 EXIT WHEN (i= l_eto_tbl.LAST);
1333 i := l_eto_tbl.NEXT(i);
1334 END LOOP;
1335 END IF;
1336 -- insert the lines record into the database
1337 okl_eto_pvt.insert_row(
1338 p_api_version => p_api_version,
1339 p_init_msg_list => p_init_msg_list,
1340 x_return_status => l_return_status,
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data,
1343 p_eto_tbl => l_eto_tbl,
1344 x_eto_tbl => x_eto_tbl);
1345 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1346 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1347 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1348 raise OKL_API.G_EXCEPTION_ERROR;
1349 END IF;
1350
1351 -- populate the foreign key for the values table
1352 IF (l_etv_tbl.COUNT > 0) then
1353 i:= l_etv_tbl.FIRST;
1354 LOOP
1355 l_etv_tbl(i) := p_etv_tbl(i);
1356 l_etv_tbl(i).END_OF_TERM_VER_ID := x_eve_rec.END_OF_TERM_VER_ID;
1357 EXIT WHEN (i= l_etv_tbl.LAST);
1358 i:= l_etv_tbl.NEXT(i);
1359 END LOOP;
1360 END IF;
1361 --viselvar 4604059 start
1362 -- validate the Values of terms
1363 IF x_ethv_rec.eot_type_code = 'PERCENT' THEN
1364 validate_percent_values(l_etv_tbl);
1365 END IF;
1366 --viselvar 4604059 end
1367 -- insert the values record into the database
1368 okl_etv_pvt.insert_row(
1369 p_api_version => p_api_version,
1370 p_init_msg_list => p_init_msg_list,
1371 x_return_status => l_return_status,
1372 x_msg_count => x_msg_count,
1373 x_msg_data => x_msg_data,
1374 p_etv_tbl => l_etv_tbl,
1375 x_etv_tbl => x_etv_tbl);
1376
1377 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1378 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1379 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1380 raise OKL_API.G_EXCEPTION_ERROR;
1381 END IF;
1382
1383 END IF; --Added by dcshanmu for bug 6699555
1384
1385 exception
1386 WHEN INVALID_START_DATE THEN
1387 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1388 p_msg_name => 'OKL_INVALID_EFF_FROM',
1389 p_token1 => 'DATE',
1390 p_token1_value => l_cal_eff_from);
1391 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1392 ( l_api_name,
1393 G_PKG_NAME,
1394 'OKL_API.G_RET_STS_ERROR',
1395 x_msg_count,
1396 x_msg_data,
1397 '_PVT');
1398
1399 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1400 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1401 ( l_api_name,
1402 G_PKG_NAME,
1403 'OKL_API.G_RET_STS_ERROR',
1404 x_msg_count,
1405 x_msg_data,
1406 '_PVT');
1407 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1408 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1409 ( l_api_name,
1410 G_PKG_NAME,
1411 'OKL_API.G_RET_STS_UNEXP_ERROR',
1412 x_msg_count,
1413 x_msg_data,
1414 '_PVT');
1415 WHEN OTHERS THEN
1416 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1417 ( l_api_name,
1418 G_PKG_NAME,
1419 'OTHERS',
1420 x_msg_count,
1421 x_msg_data,
1422 '_PVT');
1423 END create_version;
1424
1425 PROCEDURE validate_end_of_term_option(
1426 p_api_version IN NUMBER,
1427 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1428 x_return_status OUT NOCOPY VARCHAR2,
1429 x_msg_count OUT NOCOPY NUMBER,
1430 x_msg_data OUT NOCOPY VARCHAR2,
1431 p_end_of_ver_id IN NUMBER
1432 ) AS
1433
1434
1435 l_eto_tbl okl_eto_tbl;
1436 l_api_name VARCHAR2(40) := 'validate_end_of_term_option';
1437 l_eot_type VARCHAR2(30);
1438 l_cat_code VARCHAR2(30);
1439 i NUMBER := 0;
1440 j NUMBER;
1441 int_count NUMBER;
1442
1443 CURSOR cat_type_code_csr(p_end_of_term_ver_id IN NUMBER) IS
1444 SELECT category_type_code,eot_type_code FROM OKL_FE_EO_TERMS_V hdr, OKL_FE_EO_TERM_VERS ver
1445 WHERE hdr.end_of_term_id = ver.end_of_term_id AND ver.end_of_term_ver_id = p_end_of_term_ver_id;
1446
1447 CURSOR objects_csr(p_end_of_term_ver_id IN NUMBER) IS
1448 SELECT inventory_item_id, organization_id, category_id, category_set_id, resi_category_set_id
1449 FROM OKL_FE_EO_TERM_OBJECTS
1450 WHERE end_of_term_ver_id=p_end_of_term_ver_id;
1451
1452 cursor repeat_csr(id1 NUMBER, id2 NUMBER) is
1453 select count(*) from
1454 (
1455 select organization_id, inventory_item_id, category_id, category_set_id
1456 from OKL_FE_RESI_CAT_OBJECTS where resi_category_set_id = id1
1457 intersect
1458 select organization_id, inventory_item_id, category_id, category_set_id
1459 from OKL_FE_RESI_CAT_OBJECTS where resi_category_set_id = id2
1460 );
1461
1462 BEGIN
1463
1464 -- If the residual type is 'residual category set', then we need to check for items repeating
1465 -- in that residual amount
1466 OPEN cat_type_code_csr(p_end_of_ver_id);
1467 FETCH cat_type_code_csr INTO l_cat_code,l_eot_type;
1468 CLOSE cat_type_code_csr;
1469
1470 FOR l_objects_rec IN objects_csr(p_end_of_ver_id) LOOP
1471 l_eto_tbl(i).inventory_item_id:=l_objects_rec.inventory_item_id;
1472 l_eto_tbl(i).organization_id :=l_objects_rec.organization_id;
1473 l_eto_tbl(i).category_id :=l_objects_rec.category_id;
1474 l_eto_tbl(i).category_set_id :=l_objects_rec.category_set_id;
1475 l_eto_tbl(i).resi_category_set_id :=l_objects_rec.resi_category_set_id;
1476 END LOOP;
1477
1478 IF( l_eto_tbl.COUNT> 0) then
1479 i := l_eto_tbl.FIRST;
1480 IF (l_cat_code= 'RESIDUAL_CAT_SET') and
1481 (l_eot_type='RESIDUAL_AMOUNT' or l_eot_type='RESIDUAL_PERCENT') then
1482 FOR i IN l_eto_tbl.FIRST..l_eto_tbl.LAST-1 LOOP
1483 FOR j IN i+1..l_eto_tbl.LAST LOOP
1484 OPEN repeat_csr(l_eto_tbl(i).resi_category_set_id, l_eto_tbl(j).resi_category_set_id);
1485 FETCH repeat_csr into int_count;
1486 CLOSE repeat_csr;
1487 IF (int_count > 0) THEN
1488 RAISE EXCEPTION_ITEM_REPEAT;
1489 END IF;
1490 END LOOP;
1491 END LOOP;
1492 END IF;
1493 END IF;
1494 exception
1495 WHEN EXCEPTION_ITEM_REPEAT THEN
1496 null;
1497 WHEN OTHERS THEN
1498 IF (repeat_csr%ISOPEN) then
1499 CLOSE repeat_csr;
1500 END IF;
1501 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1502 ( l_api_name,
1503 G_PKG_NAME,
1504 'OTHERS',
1505 x_msg_count,
1506 x_msg_data,
1507 '_PVT');
1508 END validate_end_of_term_option;
1509
1510 PROCEDURE handle_approval(
1511 p_api_version IN NUMBER,
1512 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1513 x_return_status OUT NOCOPY VARCHAR2,
1514 x_msg_count OUT NOCOPY NUMBER,
1515 x_msg_data OUT NOCOPY VARCHAR2,
1516 p_end_of_term_ver_id IN NUMBER) AS
1517 cal_end_date DATE;
1518 l_eve_rec okl_eve_rec ;
1519 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_ERROR;
1520 x_eve_rec okl_eve_rec;
1521 l_ethv_rec okl_ethv_rec;
1522 x_ethv_rec okl_ethv_rec;
1523 l_eff_from DATE;
1524 l_eff_to DATE;
1525 l_ver_number VARCHAR2(24);
1526 l_eot_id NUMBER;
1527 l_api_name VARCHAR2(40) := 'handle_approval';
1528 l_api_version NUMBER := 1.0;
1529 l_cal_eff_from DATE;
1530 l_prev_ver_id NUMBER;
1531 l_prev_ver_eff_to DATE;
1532 l_prev_eve_rec okl_eve_rec;
1533 x_prev_eve_rec okl_eve_rec;
1534 x_obj_tbl invalid_object_tbl;
1535 i NUMBER;
1536 lp_lrtv_tbl okl_lrs_id_tbl;
1537
1538 -- cursor to get the data of the versions record
1539 CURSOR eot_versions_csr(p_ver_id IN NUMBER) IS
1540 SELECT END_OF_TERM_ID,
1541 VERSION_NUMBER,
1542 EFFECTIVE_FROM_DATE,
1543 EFFECTIVE_TO_DATE
1544 FROM OKL_FE_EO_TERM_VERS WHERE END_OF_TERM_VER_ID=p_ver_id;
1545
1546 CURSOR ver_eff_to_csr(p_eot_id IN NUMBER, p_version_number IN NUMBER)IS
1547 SELECT END_OF_TERM_VER_ID,
1548 EFFECTIVE_TO_DATE FROM okl_fe_eo_term_vers
1549 WHERE END_OF_TERM_ID=p_eot_id and VERSION_NUMBER = p_version_number;
1550
1551 BEGIN
1552 l_return_status := OKL_API.start_activity(l_api_name
1553 ,g_pkg_name
1554 ,p_init_msg_list
1555 ,l_api_version
1556 ,p_api_version
1557 ,'_PVT'
1558 ,x_return_status);
1559
1560 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1561 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1562 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1563 raise OKL_API.G_EXCEPTION_ERROR;
1564 END IF;
1565
1566 l_return_status := val_avail_item_residual(p_end_of_term_ver_id);
1567 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1568 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1569 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1570 raise OKL_API.G_EXCEPTION_ERROR;
1571 END IF;
1572
1573 -- fetch thte details of the versions record and populate them into versions record
1574 OPEN eot_versions_csr(p_end_of_term_ver_id);
1575 FETCH eot_versions_csr INTO l_eot_id,l_ver_number,l_eff_from, l_eff_to;
1576 CLOSE eot_versions_csr;
1577
1578 -- set the properties of the versions record
1579 l_eve_rec.STS_CODE:='ACTIVE';
1580 l_eve_rec.END_OF_TERM_VER_ID:= p_end_of_term_ver_id;
1581 l_eve_rec.END_OF_TERM_ID:= l_eot_id;
1582 l_eve_rec.VERSION_NUMBER:= l_ver_number;
1583 l_eve_rec.EFFECTIVE_FROM_DATE:=l_eff_from;
1584
1585 IF (l_eff_to is not null) THEN
1586 l_eve_rec.EFFECTIVE_TO_DATE:=l_eff_to;
1587 ELSE
1588 l_eve_rec.EFFECTIVE_TO_DATE:= OKL_API.G_MISS_DATE;
1589 END IF;
1590
1591 IF (l_eve_rec.VERSION_NUMBER >1 ) THEN
1592 calculate_start_date(
1593 p_api_version ,
1594 p_init_msg_list ,
1595 l_return_status ,
1596 x_msg_count ,
1597 x_msg_data ,
1598 l_eve_rec ,
1599 l_cal_eff_from );
1600
1601 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1602 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1603 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1604 raise OKL_API.G_EXCEPTION_ERROR;
1605 END IF;
1606
1607 IF (l_eve_rec.effective_from_date < l_cal_eff_from ) THEN
1608 RAISE INVALID_START_DATE;
1609 END IF;
1610
1611 OPEN ver_eff_to_csr(l_eot_id,l_ver_number-1);
1612 FETCH ver_eff_to_csr INTO l_prev_ver_id,l_prev_ver_eff_to;
1613 CLOSE ver_eff_to_csr;
1614
1615 l_prev_eve_rec.end_of_term_ver_id:=l_prev_ver_id;
1616 l_prev_eve_rec.effective_to_date:=l_eve_rec.effective_from_date-1;
1617
1618 -- end date the lease rate set versions
1619 INVALID_OBJECTS(
1620 p_api_version => p_api_version,
1621 p_init_msg_list => p_init_msg_list,
1622 x_return_status => l_return_status,
1623 x_msg_count => x_msg_count,
1624 x_msg_data => x_msg_data,
1625 p_version_id => l_prev_ver_id,
1626 x_obj_tbl => x_obj_tbl
1627 );
1628 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1629 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1630 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1631 raise OKL_API.G_EXCEPTION_ERROR;
1632 END IF;
1633
1634 IF (x_obj_tbl.COUNT >0) THEN
1635 FOR i IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1636 lp_lrtv_tbl(i) := x_obj_tbl(i).obj_id;
1637 END LOOP;
1638
1639 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(
1640 p_api_version
1641 ,p_init_msg_list
1642 ,l_return_status
1643 ,x_msg_count
1644 ,x_msg_data
1645 ,lp_lrtv_tbl
1646 ,l_prev_eve_rec.effective_to_date
1647 );
1648 END IF;
1649
1650 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1651 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1652 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1653 raise OKL_API.G_EXCEPTION_ERROR;
1654 END IF;
1655
1656 -- update the previous version record
1657 okl_eve_pvt.update_row(
1658 p_api_version => p_api_version,
1659 p_init_msg_list => p_init_msg_list,
1660 x_return_status => l_return_status,
1661 x_msg_count => x_msg_count,
1662 x_msg_data => x_msg_data,
1663 p_eve_rec => l_prev_eve_rec,
1664 x_eve_rec => x_prev_eve_rec);
1665
1666 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1667 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1668 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1669 raise OKL_API.G_EXCEPTION_ERROR;
1670 END IF;
1671
1672 END IF;
1673
1674 -- update the version record
1675 okl_eve_pvt.update_row(
1676 p_api_version => p_api_version,
1677 p_init_msg_list => p_init_msg_list,
1678 x_return_status => l_return_status,
1679 x_msg_count => x_msg_count,
1680 x_msg_data => x_msg_data,
1681 p_eve_rec => l_eve_rec,
1682 x_eve_rec => x_eve_rec);
1683
1684 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1685 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1686 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1687 raise OKL_API.G_EXCEPTION_ERROR;
1688 END IF;
1689 -- change the status in the header record also as active for search purcpose only
1690 l_ethv_rec.sts_code:= 'ACTIVE';
1691 l_ethv_rec.end_of_term_id := x_eve_rec.end_of_term_id;
1692
1693 IF (l_eve_rec.EFFECTIVE_TO_DATE is not null) THEN
1694 l_ethv_rec.EFFECTIVE_TO_DATE :=x_eve_rec.EFFECTIVE_TO_DATE;
1695 ELSE
1696 l_ethv_rec.EFFECTIVE_TO_DATE :=OKL_API.G_MISS_DATE;
1697 END IF;
1698 okl_eth_pvt.update_row(
1699 p_api_version => p_api_version,
1700 p_init_msg_list => p_init_msg_list,
1701 x_return_status => l_return_status,
1702 x_msg_count => x_msg_count,
1703 x_msg_data => x_msg_data,
1704 p_ethv_rec => l_ethv_rec,
1705 x_ethv_rec => x_ethv_rec
1706 );
1707 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1708 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1709 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1710 raise OKL_API.G_EXCEPTION_ERROR;
1711 END IF;
1712
1713 -- make the change to the previous 0.
1714
1715 exception
1716 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1717 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1718 ( l_api_name,
1719 G_PKG_NAME,
1720 'OKL_API.G_RET_STS_ERROR',
1721 x_msg_count,
1722 x_msg_data,
1723 '_PVT');
1724 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1725 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1726 ( l_api_name,
1727 G_PKG_NAME,
1728 'OKL_API.G_RET_STS_UNEXP_ERROR',
1729 x_msg_count,
1730 x_msg_data,
1731 '_PVT');
1732
1733 WHEN OTHERS THEN
1734 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1735 ( l_api_name,
1736 G_PKG_NAME,
1737 'OTHERS',
1738 x_msg_count,
1739 x_msg_data,
1740 '_PVT');
1741
1742
1743 END handle_approval;
1744 PROCEDURE INVALID_OBJECTS(
1745 p_api_version IN NUMBER,
1746 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1747 x_return_status OUT NOCOPY VARCHAR2,
1748 x_msg_count OUT NOCOPY NUMBER,
1749 x_msg_data OUT NOCOPY VARCHAR2,
1750 p_version_id IN NUMBER,
1751 x_obj_tbl OUT NOCOPY invalid_object_tbl
1752 )AS
1753
1754 -- cursor to calculate the LRS objects which are referncing this adjustment matrix
1755 CURSOR lrs_invalids_csr(p_version_id IN NUMBER) IS
1756 SELECT vers.RATE_SET_VERSION_ID ID,hdr.name NAME,vers.version_number VERSION_NUMBER
1757 FROM OKL_FE_RATE_SET_VERSIONS vers, OKL_LS_RT_FCTR_SETS_V hdr
1758 WHERE vers.rate_set_id = hdr.id AND vers.end_of_term_ver_id=p_version_id
1759 AND vers.STS_CODE='ACTIVE';
1760
1761 l_version_id NUMBER :=p_version_id;
1762 i NUMBER:=1;
1763 l_api_version NUMBER := 1.0;
1764 l_api_name VARCHAR2(40):='invalid_objects';
1765 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
1766
1767 BEGIN
1768
1769 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1770 G_PKG_NAME,
1771 p_init_msg_list,
1772 l_api_version,
1773 p_api_version,
1774 '_PVT',
1775 x_return_status);
1776
1777 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1778 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1779 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1780 RAISE OKL_API.G_EXCEPTION_ERROR;
1781 END IF;
1782
1783 FOR lrs_invalid_record IN lrs_invalids_csr(p_version_id) LOOP
1784 x_obj_tbl(i).obj_id :=lrs_invalid_record.id;
1785 x_obj_tbl(i).obj_name:=lrs_invalid_record.NAME;
1786 x_obj_tbl(i).obj_version :=lrs_invalid_record.VERSION_NUMBER;
1787 x_obj_tbl(i).obj_type:='LRS';
1788 i:=i+1;
1789 END LOOP;
1790
1791 --end activity
1792 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1793 x_return_status := l_return_status;
1794
1795
1796 EXCEPTION
1797 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1798 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1799 (
1800 l_api_name,
1801 G_PKG_NAME,
1802 'OKL_API.G_RET_STS_ERROR',
1803 x_msg_count,
1804 x_msg_data,
1805 '_PVT'
1806 );
1807
1808 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1809 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1810 (
1811 l_api_name,
1812 G_PKG_NAME,
1813 'OKL_API.G_RET_STS_UNEXP_ERROR',
1814 x_msg_count,
1815 x_msg_data,
1816 '_PVT'
1817 );
1818
1819 WHEN OTHERS THEN
1820 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1821 (
1822 l_api_name,
1823 G_PKG_NAME,
1824 'OTHERS',
1825 x_msg_count,
1826 x_msg_data,
1827 '_PVT'
1828 );
1829
1830 END INVALID_OBJECTS;
1831
1832 PROCEDURE submit_end_of_term(
1833 p_api_version IN NUMBER,
1834 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1835 x_return_status OUT NOCOPY VARCHAR2,
1836 x_msg_count OUT NOCOPY NUMBER,
1837 x_msg_data OUT NOCOPY VARCHAR2,
1838 p_end_of_term_ver_id IN NUMBER) AS
1839
1840 l_eve_rec okl_eve_rec;
1841 x_eve_rec okl_eve_rec;
1842 l_api_version NUMBER := 1.0;
1843 l_api_name VARCHAR2(40):='submit_end_of_term';
1844 l_init_msg_list VARCHAR2(1):=p_init_msg_list;
1845 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1846 l_parameter_list wf_parameter_list_t;
1847 p_event_name varchar2(240):='oracle.apps.okl.fe.eotapproval';
1848 l_profile_value varchar2(30);
1849
1850 -- Cursor to check if the residual category sets are active before Activating the Item Residual
1851 -- Pass the Item Residual Identifier and the Status as ACTIVE to check for Inactive Residual Category Sets
1852 CURSOR check_active_resi_cat_sets(p_itm_rsdl_id NUMBER, p_rcs_sts_code VARCHAR2) IS
1853 SELECT
1854 RCSV.RESI_CATEGORY_SET_ID ID
1855 , RCSV.RESI_CAT_NAME NAME
1856 FROM
1857 OKL_FE_RESI_CAT_V RCSV
1858 , OKL_FE_ITEM_RESIDUAL IRESDV
1859 WHERE
1860 IRESDV.CATEGORY_TYPE_CODE = 'RESCAT'
1861 AND IRESDV.RESI_CATEGORY_SET_ID = RCSV.RESI_CATEGORY_SET_ID
1862 AND RCSV.STS_CODE <> p_rcs_sts_code
1863 AND IRESDV.item_residual_id = p_itm_rsdl_id;
1864
1865 l_eot_id NUMBER;
1866 l_source_type OKL_FE_ITEM_RESIDUAL_ALL.CATEGORY_TYPE_CODE%TYPE;
1867 l_rcs_rec check_active_resi_cat_sets%ROWTYPE;
1868
1869 CURSOR get_eot_id(p_ver_id IN NUMBER) IS
1870 SELECT END_OF_TERM_ID
1871 FROM OKL_FE_EO_TERM_VERS WHERE END_OF_TERM_VER_ID=p_ver_id;
1872
1873 CURSOR get_source_type(p_eot_id IN NUMBER)IS
1874 SELECT EOT_TYPE_CODE
1875 FROM OKL_FE_EO_TERMS_V where end_of_term_id= p_eot_id;
1876 BEGIN
1877 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1878 G_PKG_NAME,
1879 l_init_msg_list,
1880 l_api_version,
1881 p_api_version,
1882 '_PVT',
1883 x_return_status);
1884
1885 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1886 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1887 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1888 RAISE OKL_API.G_EXCEPTION_ERROR;
1889 END IF;
1890
1891 l_return_status := val_avail_item_residual(p_end_of_term_ver_id);
1892 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1893 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1894 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) then
1895 raise OKL_API.G_EXCEPTION_ERROR;
1896 END IF;
1897 OPEN get_eot_id(p_end_of_term_ver_id);
1898 FETCH get_eot_id INTO l_eot_id;
1899 CLOSE get_eot_id;
1900
1901 OPEN get_source_type (l_eot_id);
1902 FETCH get_source_type INTO l_source_type;
1903 CLOSE get_source_type;
1904
1905 IF l_source_type = 'RESCAT' THEN
1906 OPEN check_active_resi_cat_sets(l_eot_id,'ACTIVE');
1907 FETCH check_active_resi_cat_sets INTO l_rcs_rec;
1908 IF check_active_resi_cat_sets%FOUND THEN
1909 LOOP
1910 OKL_API.set_message(p_app_name => G_APP_NAME,
1911 p_msg_name => 'OKL_RCS_STS_INACTIVE',
1912 p_token1 => OKL_API.G_COL_NAME_TOKEN,
1913 p_token1_value => l_rcs_rec.name);
1914 FETCH check_active_resi_cat_sets INTO l_rcs_rec;
1915 EXIT WHEN check_active_resi_cat_sets%NOTFOUND;
1916 END LOOP;
1917 RAISE OKL_API.G_EXCEPTION_ERROR;
1918 END IF;
1919 CLOSE check_active_resi_cat_sets;
1920 END IF;
1921
1922 l_eve_rec.end_of_term_ver_id := p_end_of_term_ver_id;
1923 l_eve_rec.STS_CODE := 'SUBMITTED';
1924
1925 okl_eve_pvt.update_row( l_api_version
1926 ,p_init_msg_list
1927 ,l_return_status
1928 ,x_msg_count
1929 ,x_msg_data
1930 ,l_eve_rec
1931 ,x_eve_rec);
1932
1933 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1934 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1935 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1936 RAISE OKL_API.G_EXCEPTION_ERROR;
1937 END IF;
1938 fnd_profile.get('OKL_PE_APPROVAL_PROCESS',l_profile_value);
1939
1940 IF (nvl(l_profile_value,'NONE') = 'NONE') THEN
1941
1942 HANDLE_APPROVAL(
1943 p_api_version => l_api_version,
1944 p_init_msg_list => p_init_msg_list,
1945 x_return_status => l_return_status,
1946 x_msg_count => x_msg_count,
1947 x_msg_data => x_msg_data,
1948 p_end_of_term_ver_id => p_end_of_term_ver_id
1949 );
1950
1951 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1952 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1953 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1954 RAISE OKL_API.G_EXCEPTION_ERROR;
1955 END IF;
1956 ELSE
1957 -- raise the business event passing the version id added to the parameter list
1958 wf_event.AddParameterToList('VERSION_ID',p_end_of_term_ver_id,l_parameter_list);
1959 --added by akrangan
1960 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
1961 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
1962 p_init_msg_list => p_init_msg_list,
1963 x_return_status => x_return_status,
1964 x_msg_count => x_msg_count,
1965 x_msg_data => x_msg_data,
1966 p_event_name => p_event_name,
1967 p_parameters => l_parameter_list);
1968
1969
1970
1971 END IF;
1972
1973
1974 --end activity
1975 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1976 x_return_status := l_return_status;
1977
1978 EXCEPTION
1979 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1980 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1981 (
1982 l_api_name,
1983 G_PKG_NAME,
1984 'OKL_API.G_RET_STS_ERROR',
1985 x_msg_count,
1986 x_msg_data,
1987 '_PVT'
1988 );
1989
1990 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1991 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1992 (
1993 l_api_name,
1994 G_PKG_NAME,
1995 'OKL_API.G_RET_STS_UNEXP_ERROR',
1996 x_msg_count,
1997 x_msg_data,
1998 '_PVT'
1999 );
2000
2001 WHEN OTHERS THEN
2002 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2003 (
2004 l_api_name,
2005 G_PKG_NAME,
2006 'OTHERS',
2007 x_msg_count,
2008 x_msg_data,
2009 '_PVT'
2010 );
2011 end submit_end_of_term;
2012 END OKL_FE_EO_TERM_OPTIONS_PVT;