[Home] [Help]
PACKAGE BODY: APPS.OKL_FE_ADJ_MATRIX_PVT
Source
1 PACKAGE BODY OKL_FE_ADJ_MATRIX_PVT AS
2 /* $Header: OKLRPAMB.pls 120.9 2006/07/21 13:12:59 akrangan noship $ */
3
4 --------------------------------------------------------------------------------
5 --PACKAGE CONSTANTS
6 --------------------------------------------------------------------------------
7 G_DB_ERROR CONSTANT VARCHAR2(12) := 'OKL_DB_ERROR';
8 G_PROG_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROG_NAME';
9 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) :='OKC_NO_PARENT_RECORD';
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) :='OKC_CONTRACTS_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13
14 G_EXCEPTION_HALT_VALIDATION exception;
15 G_INVALID_ADJ_CAT_DATES exception;
16 G_EXCEPTION_CANNOT_UPDATE exception;
17 G_INVALID_START_DATE exception;
18
19 rosetta_g_mistake_date date := to_date('01/01/+4713', 'MM/DD/SYYYY');
20 rosetta_g_mistake_date2 date := to_date('01/01/-4711', 'MM/DD/SYYYY');
21 rosetta_g_miss_date date := to_date('01/01/-4712', 'MM/DD/SYYYY');
22
23 -- this is to workaround the JDBC bug regarding IN DATE of value GMiss
24 function rosetta_g_miss_date_in_map(d date) return date as
25 begin
26 if (d = rosetta_g_mistake_date or d=rosetta_g_mistake_date2) then return fnd_api.g_miss_date; end if;
27 return d;
28 end;
29
30 -- to calculate the start date of the new version
31 PROCEDURE calc_start_date(
32 p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2,
37 p_pal_rec IN okl_pal_rec,
38 x_cal_eff_from OUT NOCOPY DATE) AS
39
40 TYPE l_start_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
41
42 l_api_name VARCHAR2(40):='calc_start_date';
43 l_api_version NUMBER := 1.0;
44 l_pal_rec okl_pal_rec := p_pal_rec;
45 l_eff_from DATE;
46 l_eff_to DATE;
47 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
48 l_start_date l_start_date_type;
49 l_max_start_date DATE;
50 i NUMBER;
51
52 -- cursor to fetch the maximum start date of lease quotes referencing Standard Rate Template
53 CURSOR srt_lq_csr(p_version_id IN NUMBER) IS
54 SELECT max(expected_start_date) start_date FROM okl_lease_quotes_b
55 WHERE rate_template_id IN
56 (SELECT std_rate_tmpl_ver_id FROM okl_fe_std_rt_tmp_vers WHERE adj_mat_version_id=p_version_id);
57
58 -- cursor to fetch the maximum start date of quick quotes referencing Standard Rate Template
59 CURSOR srt_qq_csr(p_version_id IN NUMBER) IS
60 SELECT max(expected_start_date) start_date FROM okl_quick_quotes_b
61 WHERE rate_template_id in
62 (SELECT std_rate_tmpl_ver_id from okl_fe_std_rt_tmp_vers where adj_mat_version_id=p_version_id);
63
64 -- cursor to fetch the maximum start date of lease quotes referencing Lease Rate Sets
65 CURSOR lrs_lq_csr(p_version_id IN NUMBER) IS
66 SELECT max(expected_start_date) start_date FROM okl_lease_quotes_b
67 WHERE rate_card_id IN
68 (SELECT rate_set_version_id FROM okl_fe_rate_set_versions WHERE adj_mat_version_id=p_version_id);
69
70 -- cursor to fetch the maximum start date of quick quotes referencing Lease Rate Sets
71 CURSOR lrs_qq_csr(p_version_id IN NUMBER) IS
72 SELECT max(expected_start_date) start_date FROM okl_quick_quotes_b
73 WHERE rate_card_id IN
74 (SELECT rate_set_version_id FROM okl_fe_rate_set_versions WHERE adj_mat_version_id=p_version_id);
75
76 -- cursor to fetch the start date and the end of the previous version
77 CURSOR prev_ver_csr(p_adj_mat_id IN NUMBER, p_ver_number IN VARCHAR2) IS
78 SELECT effective_from_date, effective_to_date FROM okl_fe_adj_mat_versions where adj_mat_id= p_adj_mat_id
79 and version_number= p_ver_number-1;
80
81 CURSOR get_elig_crit_start_date(p_version_id IN NUMBER) IS
82 SELECT max(effective_from_date)
83 FROM okl_fe_criteria_set ech
84 ,okl_fe_criteria ecl
85 WHERE ecl.criteria_set_id = ech.criteria_set_id
86 AND ech.source_id = p_version_id AND source_object_code = 'PAM';
87
88 BEGIN
89 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
90 G_PKG_NAME,
91 p_init_msg_list,
92 l_api_version,
93 p_api_version,
94 '_PVT',
95 x_return_status);
96
97 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
98 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
99 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
100 RAISE OKL_API.G_EXCEPTION_ERROR;
101 END IF;
102
103 OPEN prev_ver_csr(l_pal_rec.adj_mat_id, l_pal_rec.version_number);
104 FETCH prev_ver_csr INTO l_eff_from, l_eff_to;
105
106 IF (prev_ver_csr%FOUND) THEN
107 -- if the effective to date of the previous version is not null
108
109 IF (l_eff_to IS NOT NULL) THEN
110 l_max_start_date := l_eff_to + 1;
111 ELSE
112 l_max_start_date := l_eff_from + 1;
113 END IF;
114 ELSE
115 l_max_start_date:= okl_api.g_miss_date;
116 END IF;
117 CLOSE prev_ver_csr;
118
119 IF (l_eff_to IS NULL) THEN
120
121
122 OPEN srt_lq_csr(l_pal_rec.adj_mat_version_id);
123 FETCH srt_lq_csr INTO l_start_date(0);
124 CLOSE srt_lq_csr;
125
126 OPEN srt_qq_csr(l_pal_rec.adj_mat_version_id);
127 FETCH srt_qq_csr INTO l_start_date(1);
128 CLOSE srt_qq_csr;
129
130 OPEN lrs_lq_csr(l_pal_rec.adj_mat_version_id);
131 FETCH lrs_lq_csr INTO l_start_date(2);
132 CLOSE lrs_lq_csr;
133
134 OPEN lrs_qq_csr(l_pal_rec.adj_mat_version_id);
135 FETCH lrs_qq_csr INTO l_start_date(3);
136 CLOSE lrs_qq_csr;
137
138 OPEN get_elig_crit_start_date(l_pal_rec.adj_mat_version_id);
139 FETCH get_elig_crit_start_date INTO l_start_date(4);
140 CLOSE get_elig_crit_start_date;
141 -- calculate the maximum start date
142 FOR i IN l_start_date.FIRST .. l_start_date.LAST LOOP
143 IF (l_start_date(i) IS NOT NULL AND (l_start_date(i)+1) > l_max_start_date) THEN
144 l_max_start_date:= l_start_date(i)+1;
145 END IF;
146 END LOOP;
147
148 END IF;
149
150 -- assign the max start date to the out parameter
151 x_cal_eff_from := l_max_start_date;
152
153 --end activity
154 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
155 x_return_status := l_return_status;
156
157 EXCEPTION
158 WHEN OKL_API.G_EXCEPTION_ERROR THEN
159 x_return_status := OKL_API.HANDLE_EXCEPTIONS
160 (
161 l_api_name,
162 G_PKG_NAME,
163 'OKL_API.G_RET_STS_ERROR',
164 x_msg_count,
165 x_msg_data,
166 '_PVT'
167 );
168
169 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
170 x_return_status := OKL_API.HANDLE_EXCEPTIONS
171 (
172 l_api_name,
173 G_PKG_NAME,
174 'OKL_API.G_RET_STS_UNEXP_ERROR',
175 x_msg_count,
176 x_msg_data,
177 '_PVT'
178 );
179
180 WHEN OTHERS THEN
181 x_return_status := OKL_API.HANDLE_EXCEPTIONS
182 (
183 l_api_name,
184 G_PKG_NAME,
185 'OTHERS',
186 x_msg_count,
187 x_msg_data,
188 '_PVT'
189 );
190 END calc_start_date;
191
192 -- procedure to validate the pricing adjustment matrix
193 PROCEDURE VALIDATE_ADJ_MAT(
194 p_api_version IN NUMBER,
195 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2,
199 p_pal_rec IN okl_pal_rec,
200 p_ech_rec IN okl_ech_rec,
201 p_ecl_tbl IN okl_ecl_tbl,
202 p_ecv_tbl IN okl_ecv_tbl) IS
203
204 l_api_name VARCHAR2(40):='VALIDATE_ADJ_MAT';
205 l_api_version NUMBER := 1.0;
206 l_pal_rec okl_pal_rec := p_pal_rec;
207 l_ecl_tbl okl_ecl_tbl := p_ecl_tbl;
208 i NUMBER;
209 l_crit_cat VARCHAR2(40) := 'Adjustment Categories';
210 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
211
212 BEGIN
213 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
214 G_PKG_NAME,
215 p_init_msg_list,
216 l_api_version,
217 p_api_version,
218 '_PVT',
219 x_return_status);
220
221 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
222 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
223 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
224 RAISE OKL_API.G_EXCEPTION_ERROR;
225 END IF;
226
227
228 -- The Effective Dates of the Adjusment Categories should be in the range of the Adjustment matrix
229
230 FOR i in l_ecl_tbl.FIRST..l_ecl_tbl.LAST LOOP
231 IF (l_ecl_tbl(i).EFFECTIVE_FROM_DATE < l_pal_rec.EFFECTIVE_FROM_DATE) THEN
232 RAISE G_INVALID_ADJ_CAT_DATES;
233 END IF;
234 IF (l_ecl_tbl(i).EFFECTIVE_TO_DATE is null or l_ecl_tbl(i).EFFECTIVE_TO_DATE = OKL_API.G_MISS_DATE) THEN
235 IF (l_pal_rec.EFFECTIVE_TO_DATE is not null and l_pal_rec.EFFECTIVE_TO_DATE <> OKL_API.G_MISS_DATE) THEN
236 RAISE G_INVALID_ADJ_CAT_DATES;
237 END IF;
238 ELSIF (l_pal_rec.EFFECTIVE_TO_DATE is not null and l_pal_rec.EFFECTIVE_TO_DATE <> OKL_API.G_MISS_DATE) THEN
239 IF (l_ecl_tbl(i).EFFECTIVE_TO_DATE > l_pal_rec.EFFECTIVE_TO_DATE) THEN
240 RAISE G_INVALID_ADJ_CAT_DATES;
241 END IF;
242 END IF;
243 END LOOP;
244
245 --end activity
246 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
247 x_return_status := l_return_status;
248
249 EXCEPTION
250 WHEN G_INVALID_ADJ_CAT_DATES THEN
251 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
252 p_msg_name => 'OKL_CAT_INVALID_DATE_RANGE',
253 p_token1 => 'CRIT_CAT',
254 p_token1_value => l_crit_cat
255 );
256 x_return_status := OKL_API.HANDLE_EXCEPTIONS
257 (
258 l_api_name,
259 G_PKG_NAME,
260 'OKL_API.G_RET_STS_ERROR',
261 x_msg_count,
262 x_msg_data,
263 '_PVT'
264 );
265
266
267 WHEN OKL_API.G_EXCEPTION_ERROR THEN
268 x_return_status := OKL_API.HANDLE_EXCEPTIONS
269 (
270 l_api_name,
271 G_PKG_NAME,
272 'OKL_API.G_RET_STS_ERROR',
273 x_msg_count,
274 x_msg_data,
275 '_PVT'
276 );
277
278 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
279 x_return_status := OKL_API.HANDLE_EXCEPTIONS
280 (
281 l_api_name,
282 G_PKG_NAME,
283 'OKL_API.G_RET_STS_UNEXP_ERROR',
284 x_msg_count,
285 x_msg_data,
286 '_PVT'
287 );
288
289 WHEN OTHERS THEN
290 x_return_status := OKL_API.HANDLE_EXCEPTIONS
291 (
292 l_api_name,
293 G_PKG_NAME,
294 'OTHERS',
295 x_msg_count,
296 x_msg_data,
297 '_PVT'
298 );
299
300
301 END VALIDATE_ADJ_MAT;
302
303 PROCEDURE GET_ADJUSTMENT_CATEGORIES( p_api_version IN NUMBER,
304 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
305 x_return_status OUT NOCOPY VARCHAR2,
306 x_msg_count OUT NOCOPY NUMBER,
307 x_msg_data OUT NOCOPY VARCHAR2,
308 source_id IN NUMBER,
309 x_ech_rec OUT NOCOPY okl_ech_rec,
310 x_ecl_tbl OUT NOCOPY okl_ecl_tbl,
311 x_ecv_tbl OUT NOCOPY okl_ecv_tbl)IS
312
313 -- cursor to fetch the adjustment categories header record
314 CURSOR adj_cat_hdr(p_source_id IN NUMBER, p_source_object IN VARCHAR2) IS
315 SELECT CRITERIA_SET_ID,
316 OBJECT_VERSION_NUMBER,
317 SOURCE_ID,
318 SOURCE_OBJECT_CODE,
319 MATCH_CRITERIA_CODE,
320 VALIDATION_CODE,
321 CREATED_BY,
322 CREATION_DATE,
323 LAST_UPDATED_BY,
324 LAST_UPDATE_DATE,
325 LAST_UPDATE_LOGIN
326 FROM OKL_FE_CRITERIA_SET WHERE SOURCE_ID= p_source_id AND SOURCE_OBJECT_CODE= p_source_object;
327
328 -- cursor to fetch the adjustment categories lines record
329 CURSOR adj_cat_lines(p_criteria_set_id IN NUMBER) IS
330 SELECT CRITERIA_ID,
331 OBJECT_VERSION_NUMBER,
332 MATCH_CRITERIA_CODE,
333 CRITERIA_SET_ID,
334 CRIT_CAT_DEF_ID,
335 Effective_From_DATE,
336 Effective_To_DATE,
337 CREATED_BY,
338 CREATION_DATE,
339 LAST_UPDATED_BY,
340 LAST_UPDATE_DATE,
341 LAST_UPDATE_LOGIN
342 FROM OKL_FE_CRITERIA WHERE CRITERIA_SET_ID= p_criteria_set_id;
343
344 -- cursor to fetch the adjustment categories values record
345 CURSOR adj_cat_values(p_criteria_id IN NUMBER) IS
346 SELECT CRITERION_VALUE_ID,
347 OBJECT_VERSION_NUMBER,
348 CRITERIA_ID,
349 OPERATOR_CODE,
350 CRIT_CAT_VALUE1,
351 CRIT_CAT_VALUE2,
352 ADJUSTMENT_FACTOR,
353 ATTRIBUTE_CATEGORY,
354 ATTRIBUTE1,
355 ATTRIBUTE2,
356 ATTRIBUTE3,
357 ATTRIBUTE4,
358 ATTRIBUTE5,
359 ATTRIBUTE6,
360 ATTRIBUTE7,
361 ATTRIBUTE8,
362 ATTRIBUTE9,
363 ATTRIBUTE10,
364 ATTRIBUTE11,
365 ATTRIBUTE12,
366 ATTRIBUTE13,
367 ATTRIBUTE14,
368 ATTRIBUTE15,
369 CREATED_BY,
370 CREATION_DATE,
371 LAST_UPDATED_BY,
372 LAST_UPDATE_DATE,
373 LAST_UPDATE_LOGIN
374 FROM OKL_FE_CRITERION_VALUES WHERE CRITERIA_ID = p_criteria_id;
375
376 -- cursor to get the data type and the value type of a category
377 CURSOR crit_def(criteria_def_id IN NUMBER) IS
378 SELECT DATA_TYPE_CODE,
379 VALUE_TYPE_CODE
380 FROM OKL_FE_CRIT_CAT_DEF_V where CRIT_CAT_DEF_ID = criteria_def_id;
381
382 l_api_name VARCHAR2(40):= 'POPULATE_ADJUSTMENT_CATEGORIES';
383 l_api_version NUMBER:=1.0;
384 i NUMBER :=1;
385 j NUMBER :=1;
386 data_type VARCHAR2(30);
387 BEGIN
388 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
389 G_PKG_NAME,
390 p_init_msg_list,
391 l_api_version,
392 p_api_version,
393 '_PVT',
394 x_return_status);
395
396
397 FOR cat_hdr_rec IN adj_cat_hdr(source_id, 'PAM')
398 LOOP
399 x_ech_rec.CRITERIA_SET_ID := cat_hdr_rec.CRITERIA_SET_ID;
400 x_ech_rec.OBJECT_VERSION_NUMBER := cat_hdr_rec.OBJECT_VERSION_NUMBER;
401 x_ech_rec.SOURCE_ID := cat_hdr_rec.SOURCE_ID;
402 x_ech_rec.SOURCE_OBJECT_CODE := cat_hdr_rec.SOURCE_OBJECT_CODE;
403 x_ech_rec.MATCH_CRITERIA_CODE := cat_hdr_rec.MATCH_CRITERIA_CODE;
404 x_ech_rec.VALIDATION_CODE := cat_hdr_rec.VALIDATION_CODE;
405 x_ech_rec.CREATED_BY := cat_hdr_rec.CREATED_BY;
406 x_ech_rec.CREATION_DATE := cat_hdr_rec.CREATION_DATE;
407 x_ech_rec.LAST_UPDATED_BY := cat_hdr_rec.LAST_UPDATED_BY;
408 x_ech_rec.LAST_UPDATE_DATE := cat_hdr_rec.LAST_UPDATE_DATE;
409 x_ech_rec.LAST_UPDATE_LOGIN := cat_hdr_rec.LAST_UPDATE_LOGIN;
410 END LOOP;
411
412 FOR cat_lines_rec IN adj_cat_lines(x_ech_rec.CRITERIA_SET_ID)
413 LOOP
414 x_ecl_tbl(i).CRITERIA_ID := cat_lines_rec.CRITERIA_ID;
415 x_ecl_tbl(i).CRIT_CAT_DEF_ID := cat_lines_rec.CRIT_CAT_DEF_ID;
416 FOR type_code IN crit_def(cat_lines_rec.CRIT_CAT_DEF_ID) LOOP
417 data_type := type_code.DATA_TYPE_CODE;
418 END LOOP;
419 FOR cat_values_rec IN adj_cat_values(cat_lines_rec.CRITERIA_ID) LOOP
420 x_ecv_tbl(j).CRITERION_VALUE_ID:= cat_values_rec.CRITERION_VALUE_ID;
421 x_ecv_tbl(j).OBJECT_VERSION_NUMBER:= cat_values_rec.OBJECT_VERSION_NUMBER;
422 x_ecv_tbl(j).CRITERIA_ID:= cat_values_rec.CRITERIA_ID;
423 x_ecv_tbl(j).OPERATOR_CODE:= cat_values_rec.OPERATOR_CODE;
424 IF (data_type = 'VARCHAR2') THEN
425 x_ecv_tbl(j).CRIT_CAT_VALUE1:= cat_values_rec.CRIT_CAT_VALUE1;
426 x_ecv_tbl(j).CRIT_CAT_VALUE2:= cat_values_rec.CRIT_CAT_VALUE2;
427 ELSIF (data_type = 'NUMBER') THEN
428 x_ecv_tbl(j).CRIT_CAT_NUMVAL1 :=to_number(cat_values_rec.CRIT_CAT_VALUE1);
429 x_ecv_tbl(j).CRIT_CAT_NUMVAL2 :=to_number(cat_values_rec.CRIT_CAT_VALUE2);
430 ELSIF (data_type = 'DATE') THEN
431 x_ecv_tbl(j).CRIT_CAT_DATEVAL1 :=FND_DATE.canonical_to_date(cat_values_rec.CRIT_CAT_VALUE1);
432 x_ecv_tbl(j).CRIT_CAT_DATEVAL2 :=FND_DATE.canonical_to_date(cat_values_rec.CRIT_CAT_VALUE2);
433 END IF;
434 x_ecv_tbl(j).ADJUSTMENT_FACTOR:= cat_values_rec.ADJUSTMENT_FACTOR;
435 x_ecv_tbl(j).ATTRIBUTE_CATEGORY:= cat_values_rec.ATTRIBUTE_CATEGORY;
436 x_ecv_tbl(j).ATTRIBUTE1:= cat_values_rec.ATTRIBUTE1;
437 x_ecv_tbl(j).ATTRIBUTE2:= cat_values_rec.ATTRIBUTE2;
438 x_ecv_tbl(j).ATTRIBUTE3:= cat_values_rec.ATTRIBUTE3;
439 x_ecv_tbl(j).ATTRIBUTE4:= cat_values_rec.ATTRIBUTE4;
440 x_ecv_tbl(j).ATTRIBUTE5:= cat_values_rec.ATTRIBUTE5;
441 x_ecv_tbl(j).ATTRIBUTE6:= cat_values_rec.ATTRIBUTE6;
442 x_ecv_tbl(j).ATTRIBUTE7:= cat_values_rec.ATTRIBUTE7;
443 x_ecv_tbl(j).ATTRIBUTE8:= cat_values_rec.ATTRIBUTE8;
444 x_ecv_tbl(j).ATTRIBUTE9:= cat_values_rec.ATTRIBUTE9;
445 x_ecv_tbl(j).ATTRIBUTE10:= cat_values_rec.ATTRIBUTE10;
446 x_ecv_tbl(j).ATTRIBUTE11:= cat_values_rec.ATTRIBUTE11;
447 x_ecv_tbl(j).ATTRIBUTE12:= cat_values_rec.ATTRIBUTE12;
448 x_ecv_tbl(j).ATTRIBUTE13:= cat_values_rec.ATTRIBUTE13;
449 x_ecv_tbl(j).ATTRIBUTE14:= cat_values_rec.ATTRIBUTE14;
450 x_ecv_tbl(j).ATTRIBUTE15:= cat_values_rec.ATTRIBUTE15;
451 x_ecv_tbl(j).CREATED_BY:= cat_values_rec.CREATED_BY;
452 x_ecv_tbl(j).CREATION_DATE:= cat_values_rec.CREATION_DATE;
453 x_ecv_tbl(j).LAST_UPDATED_BY:= cat_values_rec.LAST_UPDATED_BY;
454 x_ecv_tbl(j).LAST_UPDATE_DATE:= cat_values_rec.LAST_UPDATE_DATE;
455 x_ecv_tbl(j).LAST_UPDATE_LOGIN:= cat_values_rec.LAST_UPDATE_LOGIN;
456
457 j:=j+1;
458 END LOOP;
459 x_ecl_tbl(i).OBJECT_VERSION_NUMBER := cat_lines_rec.OBJECT_VERSION_NUMBER;
460 x_ecl_tbl(i).MATCH_CRITERIA_CODE := cat_lines_rec.MATCH_CRITERIA_CODE;
461 x_ecl_tbl(i).CRITERIA_SET_ID := cat_lines_rec.CRITERIA_SET_ID;
462 x_ecl_tbl(i).Effective_From_DATE := cat_lines_rec.Effective_From_DATE;
463 x_ecl_tbl(i).Effective_To_DATE := cat_lines_rec.Effective_To_DATE;
464 x_ecl_tbl(i).CREATED_BY := cat_lines_rec.CREATED_BY;
465 x_ecl_tbl(i).CREATION_DATE := cat_lines_rec.CREATION_DATE;
466 x_ecl_tbl(i).LAST_UPDATED_BY := cat_lines_rec.LAST_UPDATED_BY;
467 x_ecl_tbl(i).LAST_UPDATE_DATE := cat_lines_rec.LAST_UPDATE_DATE;
468 x_ecl_tbl(i).LAST_UPDATE_LOGIN := cat_lines_rec.LAST_UPDATE_LOGIN;
469 x_ecl_tbl(i).IS_NEW_FLAG := 'N';
470 i:=i+1;
471 END LOOP;
472 --end activity
473 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
474
475 EXCEPTION
476
477 WHEN others THEN
478 x_return_status := OKL_API.HANDLE_EXCEPTIONS
479 (
480 l_api_name,
481 G_PKG_NAME,
482 'OTHERS',
483 x_msg_count,
484 x_msg_data,
485 '_PVT'
486 );
487
488 END GET_ADJUSTMENT_CATEGORIES;
489
490 PROCEDURE GET_ADJ_MATRIX(p_api_version IN NUMBER,
491 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_msg_data OUT NOCOPY VARCHAR2,
495 p_adj_mat_id IN NUMBER,
496 p_version_number IN NUMBER,
497 x_pamv_rec OUT NOCOPY okl_pamv_rec,
498 x_pal_rec OUT NOCOPY okl_pal_rec
499 )IS
500 -- cursor to fetch the header record
501 CURSOR adj_mat_hdr(p_adj_mat_id IN NUMBER) IS
502 SELECT ADJ_MAT_ID,
503 ADJ_MAT_NAME,
504 ADJ_MAT_DESC,
505 OBJECT_VERSION_NUMBER,
506 ORG_ID,
507 CURRENCY_CODE,
508 ADJ_MAT_TYPE_CODE,
509 ORIG_ADJ_MAT_ID,
510 STS_CODE,
511 EFFECTIVE_FROM_DATE,
512 EFFECTIVE_TO_DATE,
513 ATTRIBUTE_CATEGORY,
514 ATTRIBUTE1,
515 ATTRIBUTE2,
516 ATTRIBUTE3,
517 ATTRIBUTE4,
518 ATTRIBUTE5,
519 ATTRIBUTE6,
520 ATTRIBUTE7,
521 ATTRIBUTE8,
522 ATTRIBUTE9,
523 ATTRIBUTE10,
524 ATTRIBUTE11,
525 ATTRIBUTE12,
526 ATTRIBUTE13,
527 ATTRIBUTE14,
528 ATTRIBUTE15,
529 CREATED_BY,
530 CREATION_DATE,
531 LAST_UPDATED_BY,
532 LAST_UPDATE_DATE,
533 LAST_UPDATE_LOGIN
534 FROM OKL_FE_ADJ_MAT_V WHERE ADJ_MAT_ID= p_adj_mat_id;
535
536 -- cursor to fetch the versions record
537 CURSOR adj_mat_version(p_adj_mat_id IN NUMBER, p_version_number IN VARCHAR2) IS
538 SELECT ADJ_MAT_VERSION_ID,
539 VERSION_NUMBER,
540 OBJECT_VERSION_NUMBER,
541 ADJ_MAT_ID,
542 STS_CODE,
543 EFFECTIVE_FROM_DATE,
544 EFFECTIVE_TO_DATE,
545 ATTRIBUTE_CATEGORY,
546 ATTRIBUTE1,
547 ATTRIBUTE2,
548 ATTRIBUTE3,
549 ATTRIBUTE4,
550 ATTRIBUTE5,
551 ATTRIBUTE6,
552 ATTRIBUTE7,
553 ATTRIBUTE8,
554 ATTRIBUTE9,
555 ATTRIBUTE10,
556 ATTRIBUTE11,
557 ATTRIBUTE12,
558 ATTRIBUTE13,
559 ATTRIBUTE14,
560 ATTRIBUTE15,
561 CREATED_BY,
562 CREATION_DATE,
563 LAST_UPDATED_BY,
564 LAST_UPDATE_DATE,
565 LAST_UPDATE_LOGIN FROM OKL_FE_ADJ_MAT_VERSIONS
566 WHERE ADJ_MAT_ID=p_adj_mat_id and VERSION_NUMBER= p_version_number;
567
568 l_api_name VARCHAR2(40) := 'populate_adj_matrix';
569 l_api_version NUMBER := 1.0;
570 BEGIN
571 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
572 G_PKG_NAME,
573 p_init_msg_list,
574 l_api_version,
575 p_api_version,
576 '_PVT',
577 x_return_status);
578
579 -- populate the header record
580 FOR cat_hdr_rec IN adj_mat_hdr(p_adj_mat_id) LOOP
581 x_pamv_rec.ADJ_MAT_ID := cat_hdr_rec.ADJ_MAT_ID;
582 x_pamv_rec.ADJ_MAT_NAME := cat_hdr_rec.ADJ_MAT_NAME;
583 x_pamv_rec.ADJ_MAT_DESC := cat_hdr_rec.ADJ_MAT_DESC;
584 x_pamv_rec.OBJECT_VERSION_NUMBER := cat_hdr_rec.OBJECT_VERSION_NUMBER;
585 x_pamv_rec.ORG_ID := cat_hdr_rec.ORG_ID;
586 x_pamv_rec.CURRENCY_CODE := cat_hdr_rec.CURRENCY_CODE;
587 x_pamv_rec.ADJ_MAT_TYPE_CODE := cat_hdr_rec.ADJ_MAT_TYPE_CODE;
588 x_pamv_rec.ORIG_ADJ_MAT_ID := cat_hdr_rec.ORIG_ADJ_MAT_ID;
589 x_pamv_rec.STS_CODE := cat_hdr_rec.STS_CODE;
590 x_pamv_rec.EFFECTIVE_FROM_DATE := cat_hdr_rec.EFFECTIVE_FROM_DATE;
591 x_pamv_rec.EFFECTIVE_TO_DATE := cat_hdr_rec.EFFECTIVE_TO_DATE;
592 x_pamv_rec.ATTRIBUTE_CATEGORY := cat_hdr_rec.ATTRIBUTE_CATEGORY;
593 x_pamv_rec.ATTRIBUTE1 := cat_hdr_rec.ATTRIBUTE1;
594 x_pamv_rec.ATTRIBUTE2 := cat_hdr_rec.ATTRIBUTE2;
595 x_pamv_rec.ATTRIBUTE3 := cat_hdr_rec.ATTRIBUTE3;
596 x_pamv_rec.ATTRIBUTE4 := cat_hdr_rec.ATTRIBUTE4;
597 x_pamv_rec.ATTRIBUTE5 := cat_hdr_rec.ATTRIBUTE5;
598 x_pamv_rec.ATTRIBUTE6 := cat_hdr_rec.ATTRIBUTE6;
599 x_pamv_rec.ATTRIBUTE7 := cat_hdr_rec.ATTRIBUTE7;
600 x_pamv_rec.ATTRIBUTE8 := cat_hdr_rec.ATTRIBUTE8;
601 x_pamv_rec.ATTRIBUTE9 := cat_hdr_rec.ATTRIBUTE9;
602 x_pamv_rec.ATTRIBUTE10 := cat_hdr_rec.ATTRIBUTE10;
603 x_pamv_rec.ATTRIBUTE11 := cat_hdr_rec.ATTRIBUTE11;
604 x_pamv_rec.ATTRIBUTE12 := cat_hdr_rec.ATTRIBUTE12;
605 x_pamv_rec.ATTRIBUTE13 := cat_hdr_rec.ATTRIBUTE13;
606 x_pamv_rec.ATTRIBUTE14 := cat_hdr_rec.ATTRIBUTE14;
607 x_pamv_rec.ATTRIBUTE15 := cat_hdr_rec.ATTRIBUTE15;
608 x_pamv_rec.CREATED_BY := cat_hdr_rec.CREATED_BY;
609 x_pamv_rec.CREATION_DATE := cat_hdr_rec.CREATION_DATE;
610 x_pamv_rec.LAST_UPDATED_BY := cat_hdr_rec.LAST_UPDATED_BY;
611 x_pamv_rec.LAST_UPDATE_DATE := cat_hdr_rec.LAST_UPDATE_DATE;
612 x_pamv_rec.LAST_UPDATE_LOGIN := cat_hdr_rec.LAST_UPDATE_LOGIN;
613 END LOOP;
614
615 -- populate the versions record
616 FOR cat_version_rec IN adj_mat_version(p_adj_mat_id, p_version_number) LOOP
617 x_pal_rec.ADJ_MAT_VERSION_ID := cat_version_rec.ADJ_MAT_VERSION_ID;
618 x_pal_rec.VERSION_NUMBER := cat_version_rec.VERSION_NUMBER;
619 x_pal_rec.OBJECT_VERSION_NUMBER := cat_version_rec.OBJECT_VERSION_NUMBER;
620 x_pal_rec.ADJ_MAT_ID := cat_version_rec.ADJ_MAT_ID;
621 x_pal_rec.STS_CODE := cat_version_rec.STS_CODE;
622 x_pal_rec.EFFECTIVE_FROM_DATE := cat_version_rec.EFFECTIVE_FROM_DATE;
623 x_pal_rec.EFFECTIVE_TO_DATE := cat_version_rec.EFFECTIVE_TO_DATE;
624 x_pal_rec.ATTRIBUTE_CATEGORY := cat_version_rec.ATTRIBUTE_CATEGORY;
625 x_pal_rec.ATTRIBUTE1 := cat_version_rec.ATTRIBUTE1;
626 x_pal_rec.ATTRIBUTE2 := cat_version_rec.ATTRIBUTE2;
627 x_pal_rec.ATTRIBUTE3 := cat_version_rec.ATTRIBUTE3;
628 x_pal_rec.ATTRIBUTE4 := cat_version_rec.ATTRIBUTE4;
629 x_pal_rec.ATTRIBUTE5 := cat_version_rec.ATTRIBUTE5;
630 x_pal_rec.ATTRIBUTE6 := cat_version_rec.ATTRIBUTE6;
631 x_pal_rec.ATTRIBUTE7 := cat_version_rec.ATTRIBUTE7;
632 x_pal_rec.ATTRIBUTE8 := cat_version_rec.ATTRIBUTE8;
633 x_pal_rec.ATTRIBUTE9 := cat_version_rec.ATTRIBUTE9;
634 x_pal_rec.ATTRIBUTE10 := cat_version_rec.ATTRIBUTE10;
635 x_pal_rec.ATTRIBUTE11 := cat_version_rec.ATTRIBUTE11;
636 x_pal_rec.ATTRIBUTE12 := cat_version_rec.ATTRIBUTE12;
637 x_pal_rec.ATTRIBUTE13 := cat_version_rec.ATTRIBUTE13;
638 x_pal_rec.ATTRIBUTE14 := cat_version_rec.ATTRIBUTE14;
639 x_pal_rec.ATTRIBUTE15 := cat_version_rec.ATTRIBUTE15;
640 x_pal_rec.CREATED_BY := cat_version_rec.CREATED_BY;
641 x_pal_rec.CREATION_DATE := cat_version_rec.CREATION_DATE;
642 x_pal_rec.LAST_UPDATED_BY := cat_version_rec.LAST_UPDATED_BY;
643 x_pal_rec.LAST_UPDATE_DATE := cat_version_rec.LAST_UPDATE_DATE;
644 x_pal_rec.LAST_UPDATE_LOGIN := cat_version_rec.LAST_UPDATE_LOGIN;
645 END LOOP;
646
647 --end activity
648 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
649
650 EXCEPTION
651
652 WHEN others THEN
653 x_return_status := OKL_API.HANDLE_EXCEPTIONS
654 (
655 l_api_name,
656 G_PKG_NAME,
657 'OTHERS',
658 x_msg_count,
659 x_msg_data,
660 '_PVT'
661 );
662
663 END GET_ADJ_MATRIX;
664 -- procedure to give the details of the adjustment matrix given the Adjustment
665 -- matrix id and the version number
666 PROCEDURE GET_VERSION(
667 p_api_version IN NUMBER,
668 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
669 x_return_status OUT NOCOPY VARCHAR2,
670 x_msg_count OUT NOCOPY NUMBER,
671 x_msg_data OUT NOCOPY VARCHAR2,
672 p_adj_mat_id IN NUMBER,
673 p_version_number IN NUMBER,
674 x_pamv_rec OUT NOCOPY okl_pamv_rec,
675 x_pal_rec OUT NOCOPY okl_pal_rec,
676 x_ech_rec OUT NOCOPY okl_ech_rec,
677 x_ecl_tbl OUT NOCOPY okl_ecl_tbl,
678 x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
679 l_api_name VARCHAR2(40):='get_version';
680 l_api_version NUMBER:=1.0;
681 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
682 BEGIN
683 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
684 G_PKG_NAME,
685 p_init_msg_list,
686 l_api_version,
687 p_api_version,
688 '_PVT',
689 x_return_status);
690
691 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
692 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
693 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
694 RAISE OKL_API.G_EXCEPTION_ERROR;
695 END IF;
696
697 GET_ADJ_MATRIX(p_api_version ,
698 p_init_msg_list,
699 x_return_status,
700 x_msg_count,
701 x_msg_data,
702 p_adj_mat_id,
703 p_version_number,
704 x_pamv_rec,
705 x_pal_rec);
706
707 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
708 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
709 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
710 RAISE OKL_API.G_EXCEPTION_ERROR;
711 END IF;
712
713 GET_ADJUSTMENT_CATEGORIES( p_api_version,
714 p_init_msg_list,
715 x_return_status,
716 x_msg_count,
717 x_msg_data,
718 x_pal_rec.ADJ_MAT_VERSION_ID,
719 x_ech_rec,
720 x_ecl_tbl,
721 x_ecv_tbl);
722
723 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
724 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
725 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
726 RAISE OKL_API.G_EXCEPTION_ERROR;
727 END IF;
728
729 --end activity
730 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
731 x_return_status := l_return_status;
732
733 EXCEPTION
734 WHEN OKL_API.G_EXCEPTION_ERROR THEN
735 x_return_status := OKL_API.HANDLE_EXCEPTIONS
736 (
737 l_api_name,
738 G_PKG_NAME,
739 'OKL_API.G_RET_STS_ERROR',
740 x_msg_count,
741 x_msg_data,
742 '_PVT'
743 );
744
745 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
746 x_return_status := OKL_API.HANDLE_EXCEPTIONS
747 (
748 l_api_name,
749 G_PKG_NAME,
750 'OKL_API.G_RET_STS_UNEXP_ERROR',
751 x_msg_count,
752 x_msg_data,
753 '_PVT'
754 );
755
756 WHEN OTHERS THEN
757 x_return_status := OKL_API.HANDLE_EXCEPTIONS
758 (
759 l_api_name,
760 G_PKG_NAME,
761 'OTHERS',
762 x_msg_count,
763 x_msg_data,
764 '_PVT'
765 );
766
767 END GET_VERSION;
768 -- procedure to give the details of the latest versionadjustment matrix given the Adjustment
769 -- matrix id
770 PROCEDURE GET_VERSION(
771 p_api_version IN NUMBER,
772 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
773 x_return_status OUT NOCOPY VARCHAR2,
774 x_msg_count OUT NOCOPY NUMBER,
775 x_msg_data OUT NOCOPY VARCHAR2,
776 p_adj_mat_id IN NUMBER,
777 x_pamv_rec OUT NOCOPY okl_pamv_rec,
778 x_pal_rec OUT NOCOPY okl_pal_rec,
779 x_ech_rec OUT NOCOPY okl_ech_rec,
780 x_ecl_tbl OUT NOCOPY okl_ecl_tbl,
781 x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
782
783 CURSOR get_version_number(p_adj_mat_id IN NUMBER) IS
784 SELECT max(version_number) FROM
785 OKL_FE_ADJ_MAT_VERSIONS WHERE ADJ_MAT_ID=p_adj_mat_id;
786
787 l_version_number VARCHAR2(24);
788 l_api_name VARCHAR2(40):='get_version';
789 l_api_version NUMBER:=1.0;
790 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
791 BEGIN
792 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
793 G_PKG_NAME,
794 p_init_msg_list,
795 l_api_version,
796 p_api_version,
797 '_PVT',
798 x_return_status);
799
800 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
801 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
802 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
803 RAISE OKL_API.G_EXCEPTION_ERROR;
804 END IF;
805
806 OPEN get_version_number(p_adj_mat_id);
807 FETCH get_version_number into l_version_number;
808 CLOSE get_version_number;
809
810 GET_ADJ_MATRIX(p_api_version ,
811 p_init_msg_list,
812 x_return_status,
813 x_msg_count,
814 x_msg_data,
815 p_adj_mat_id,
816 l_version_number,
817 x_pamv_rec,
818 x_pal_rec);
819
820 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
821 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
822 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
823 RAISE OKL_API.G_EXCEPTION_ERROR;
824 END IF;
825
826 GET_ADJUSTMENT_CATEGORIES( p_api_version,
827 p_init_msg_list,
828 x_return_status,
829 x_msg_count,
830 x_msg_data,
831 x_pal_rec.ADJ_MAT_VERSION_ID,
832 x_ech_rec,
833 x_ecl_tbl,
834 x_ecv_tbl);
835
836 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
837 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
838 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
839 RAISE OKL_API.G_EXCEPTION_ERROR;
840 END IF;
841
842 --end activity
843 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
844 x_return_status := l_return_status;
845
846 EXCEPTION
847 WHEN OKL_API.G_EXCEPTION_ERROR THEN
848 x_return_status := OKL_API.HANDLE_EXCEPTIONS
849 (
850 l_api_name,
851 G_PKG_NAME,
852 'OKL_API.G_RET_STS_ERROR',
853 x_msg_count,
854 x_msg_data,
855 '_PVT'
856 );
857
858 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
859 x_return_status := OKL_API.HANDLE_EXCEPTIONS
860 (
861 l_api_name,
862 G_PKG_NAME,
863 'OKL_API.G_RET_STS_UNEXP_ERROR',
864 x_msg_count,
865 x_msg_data,
866 '_PVT'
867 );
868
869 WHEN OTHERS THEN
870 x_return_status := OKL_API.HANDLE_EXCEPTIONS
871 (
872 l_api_name,
873 G_PKG_NAME,
874 'OTHERS',
875 x_msg_count,
876 x_msg_data,
877 '_PVT'
878 );
879
880 END GET_VERSION;
881
882
883 --procedure to create a Pricing Adjusment Matrix with the associated adjustment categories
884 PROCEDURE INSERT_ADJ_MAT(
885 p_api_version IN NUMBER,
886 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
887 x_return_status OUT NOCOPY VARCHAR2,
888 x_msg_count OUT NOCOPY NUMBER,
889 x_msg_data OUT NOCOPY VARCHAR2,
890 p_pamv_rec IN okl_pamv_rec,
891 p_pal_rec IN okl_pal_rec,
892 x_pamv_rec OUT NOCOPY okl_pamv_rec,
893 x_pal_rec OUT NOCOPY okl_pal_rec
894 ) IS
895
896 l_pamv_rec okl_pamv_rec := p_pamv_rec;
897 l_pal_rec okl_pal_rec := p_pal_rec;
898 l_api_version NUMBER := 1.0;
899 l_api_name VARCHAR2(40):='INSERT_ADJ_MAT';
900 l_init_msg_list VARCHAR2(1):=p_init_msg_list;
901 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
902 l_dummy_var VARCHAR2(1):='?';
903
904 CURSOR pam_unique_chk(p_name IN varchar2) IS
905 SELECT 'x'
906 FROM okl_fe_adj_mat_v
907 WHERE adj_mat_name = p_name;
908
909 BEGIN
910 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
911 G_PKG_NAME,
912 p_init_msg_list,
913 l_api_version,
914 p_api_version,
915 '_PVT',
916 x_return_status);
917
918 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
919 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
920 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
921 RAISE OKL_API.G_EXCEPTION_ERROR;
922 END IF;
923
924 OPEN pam_unique_chk(l_pamv_rec.adj_mat_name);
925 FETCH pam_unique_chk INTO l_dummy_var ;
926 CLOSE pam_unique_chk;
927
928 -- if l_dummy_var is 'x' then name already exists
929
930 IF (l_dummy_var = 'x') THEN
931 okl_api.set_message(p_app_name => g_app_name
932 ,p_msg_name => 'OKL_DUPLICATE_NAME'
933 ,p_token1 => 'NAME'
934 ,p_token1_value => l_pamv_rec.adj_mat_name);
935 RAISE okl_api.g_exception_error;
936 END IF;
937 -- fix for gmiss date
938 l_pal_rec.effective_to_date := rosetta_g_miss_date_in_map(l_pal_rec.effective_to_date);
939 -- setting the header attributes
940 l_pamv_rec.STS_CODE := 'NEW';
941 l_pamv_rec.EFFECTIVE_FROM_DATE := l_pal_rec.EFFECTIVE_FROM_DATE;
942 l_pamv_rec.EFFECTIVE_TO_DATE := l_pal_rec.EFFECTIVE_TO_DATE;
943
944 -- insert the header record into the table
945 okl_pam_pvt.insert_row( l_api_version
946 ,l_init_msg_list
947 ,l_return_status
948 ,x_msg_count
949 ,x_msg_data
950 ,l_pamv_rec
951 ,x_pamv_rec);
952
953 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
954 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
955 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
956 RAISE OKL_API.G_EXCEPTION_ERROR;
957 END IF;
958
959 -- setting the version attributes
960 l_pal_rec.STS_CODE := 'NEW';
961 l_pal_rec.VERSION_NUMBER:=1.0;
962 l_pal_rec.ADJ_MAT_ID := x_pamv_rec.ADJ_MAT_ID;
963
964 -- insert the version record into the table
965 okl_pal_pvt.insert_row( l_api_version
966 ,l_init_msg_list
967 ,l_return_status
968 ,x_msg_count
969 ,x_msg_data
970 ,l_pal_rec
971 ,x_pal_rec);
972
973 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
974 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
975 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
976 RAISE OKL_API.G_EXCEPTION_ERROR;
977 END IF;
978
979 --end activity
980 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
981 x_return_status := l_return_status;
982
983 EXCEPTION
984 WHEN OKL_API.G_EXCEPTION_ERROR THEN
985 x_return_status := OKL_API.HANDLE_EXCEPTIONS
986 (
987 l_api_name,
988 G_PKG_NAME,
989 'OKL_API.G_RET_STS_ERROR',
990 x_msg_count,
991 x_msg_data,
992 '_PVT'
993 );
994
995 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
996 x_return_status := OKL_API.HANDLE_EXCEPTIONS
997 (
998 l_api_name,
999 G_PKG_NAME,
1000 'OKL_API.G_RET_STS_UNEXP_ERROR',
1001 x_msg_count,
1002 x_msg_data,
1003 '_PVT'
1004 );
1005
1006 WHEN OTHERS THEN
1007 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1008 (
1009 l_api_name,
1010 G_PKG_NAME,
1011 'OTHERS',
1012 x_msg_count,
1013 x_msg_data,
1014 '_PVT'
1015 );
1016 END INSERT_ADJ_MAT;
1017
1018 -- procedure to update a particular version of the Pricing Adjustment matrix
1019 PROCEDURE UPDATE_ADJ_MAT(
1020 p_api_version IN NUMBER,
1021 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1022 x_return_status OUT NOCOPY VARCHAR2,
1023 x_msg_count OUT NOCOPY NUMBER,
1024 x_msg_data OUT NOCOPY VARCHAR2,
1025 p_pal_rec IN okl_pal_rec,
1026 x_pal_rec OUT NOCOPY okl_pal_rec
1027 ) IS
1028
1029 l_pamv_rec okl_pamv_rec;
1030 x_pamv_rec okl_pamv_rec;
1031 l_pal_rec okl_pal_rec := p_pal_rec;
1032 l_api_version NUMBER := 1.0;
1033 l_api_name VARCHAR2(40):='UPDATE_ADJ_MAT';
1034 l_init_msg_list VARCHAR2(1):=p_init_msg_list;
1035 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1036 l_eff_from DATE;
1037 l_eff_to DATE;
1038 l_max_date DATE;
1039 k NUMBER :=1;
1040 l NUMBER :=1;
1041 lp_lrtv_tbl okl_lrs_id_tbl;
1042 lp_srtv_tbl okl_srt_id_tbl;
1043 x_obj_tbl invalid_object_tbl;
1044 l_cal_end_date DATE;
1045
1046 -- cursor to fetch the previous version effective from and the previous version effective to
1047 CURSOR prev_ver_csr(l_adj_mat_id IN NUMBER, l_version_number IN VARCHAR2) IS
1048 SELECT effective_from_date, effective_to_date
1049 FROM okl_fe_adj_mat_versions
1050 WHERE adj_mat_id= l_adj_mat_id AND version_number=l_version_number -1;
1051
1052 CURSOR get_elig_crit_start_date(p_version_id IN NUMBER) IS
1053 SELECT max(effective_from_date)
1054 FROM okl_fe_criteria_set ech
1055 ,okl_fe_criteria ecl
1056 WHERE ecl.criteria_set_id = ech.criteria_set_id
1057 AND ech.source_id = p_version_id AND source_object_code = 'PAM';
1058 BEGIN
1059 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1060 G_PKG_NAME,
1061 l_init_msg_list,
1062 l_api_version,
1063 p_api_version,
1064 '_PVT',
1065 x_return_status);
1066
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 -- fix for gmiss date
1074 l_pal_rec.effective_to_date := rosetta_g_miss_date_in_map(l_pal_rec.effective_to_date);
1075
1076 OPEN prev_ver_csr(l_pal_rec.adj_mat_id, l_pal_rec.version_number);
1077 FETCH prev_ver_csr INTO l_eff_from, l_eff_to;
1078 CLOSE prev_ver_csr;
1079
1080 IF (l_eff_to is not null AND l_pal_rec.effective_from_date < l_eff_to) THEN
1081 RAISE G_INVALID_ADJ_CAT_DATES;
1082 END IF;
1083 IF (l_pal_rec.effective_from_date<= l_eff_from) THEN
1084 RAISE G_INVALID_ADJ_CAT_DATES;
1085 END IF;
1086
1087 -- If the status is active only the effective date can be updated.
1088 IF (l_pal_rec.STS_CODE = 'ACTIVE') THEN
1089
1090 l_pamv_rec.ADJ_MAT_ID := l_pal_rec.ADJ_MAT_ID;
1091 IF (l_pal_rec.EFFECTIVE_TO_DATE is not null) THEN
1092 l_pamv_rec.EFFECTIVE_TO_DATE := l_pal_rec.EFFECTIVE_TO_DATE;
1093 ELSE
1094 l_pamv_rec.EFFECTIVE_TO_DATE :=OKL_API.G_MISS_DATE;
1095 END IF;
1096 -- have to check if this effective to date > referenced end dates.
1097 -- update the header record
1098 okl_pam_pvt.update_row(l_api_version
1099 ,l_init_msg_list
1100 ,l_return_status
1101 ,x_msg_count
1102 ,x_msg_data
1103 ,l_pamv_rec
1104 ,x_pamv_rec);
1105 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1106 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1107 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1108 RAISE OKL_API.G_EXCEPTION_ERROR;
1109 END IF;
1110 IF (l_pal_rec.effective_to_date IS NOT NULL) THEN
1111 -- check whether the effective to date is greater than the maximum effective from that has been calculated.
1112 calc_start_date(
1113 p_api_version ,
1114 p_init_msg_list ,
1115 x_return_status ,
1116 x_msg_count ,
1117 x_msg_data ,
1118 l_pal_rec ,
1119 l_cal_end_date );
1120
1121 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1122 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1123 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1124 RAISE OKL_API.G_EXCEPTION_ERROR;
1125 END IF;
1126
1127 OPEN get_elig_crit_start_date(l_pal_rec.adj_mat_version_id);
1128 FETCH get_elig_crit_start_date INTO l_max_date;
1129 CLOSE get_elig_crit_start_date;
1130
1131 IF(l_max_date > (l_cal_end_date-1)) THEN
1132 l_cal_end_date:= l_max_date +1;
1133 END IF;
1134 IF (l_pal_rec.effective_to_date < (l_cal_end_date-1) ) THEN
1135 okl_api.set_message(
1136 p_app_name => g_app_name
1137 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO_DATE'
1138 ,p_token1 => 'DATE'
1139 ,p_token1_value => l_cal_end_date-1);
1140 RAISE okl_api.g_exception_error;
1141 END IF;
1142
1143 -- put an end date to the previous version of the eligibility criteria
1144 okl_ecc_values_pvt.end_date_eligibility_criteria(
1145 p_api_version => l_api_version,
1146 p_init_msg_list => p_init_msg_list,
1147 x_return_status => x_return_status,
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 p_source_id => l_pal_rec.adj_mat_version_id,
1151 p_source_type => 'PAM',
1152 p_end_date => l_pal_rec.effective_to_date
1153 );
1154 -- end date the lease rate set versions
1155 INVALID_OBJECTS(
1156 p_api_version ,
1157 p_init_msg_list ,
1158 x_return_status ,
1159 x_msg_count ,
1160 x_msg_data ,
1161 l_pal_rec.adj_mat_version_id,
1162 x_obj_tbl
1163 );
1164 if (x_obj_tbl.count>0) then
1165
1166 FOR j IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1167 IF (x_obj_tbl(j).OBJ_TYPE = 'LRS') THEN
1168 lp_lrtv_tbl(k) := x_obj_tbl(j).obj_id;
1169 k:=k+1;
1170 ELSIF (x_obj_tbl(j).OBJ_TYPE = 'SRT') THEN
1171 lp_srtv_tbl(l) := x_obj_tbl(j).obj_id;
1172 l:=l+1;
1173 END IF;
1174 END LOOP;
1175
1176 IF (k>1) THEN
1177 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(
1178 p_api_version
1179 ,p_init_msg_list
1180 ,x_return_status
1181 ,x_msg_count
1182 ,x_msg_data
1183 ,lp_lrtv_tbl
1184 ,l_pal_rec.effective_to_date
1185 );
1186 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1187 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1188 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1189 RAISE OKL_API.G_EXCEPTION_ERROR;
1190 END IF;
1191 END IF;
1192 IF (l>1) THEN
1193 OKL_FE_STD_RATE_TMPL_PVT.enddate_std_rate_tmpl(
1194 p_api_version
1195 ,p_init_msg_list
1196 ,x_return_status
1197 ,x_msg_count
1198 ,x_msg_data
1199 ,lp_srtv_tbl
1200 ,l_pal_rec.effective_to_date
1201 );
1202 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1203 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1204 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1205 RAISE OKL_API.G_EXCEPTION_ERROR;
1206 END IF;
1207 END IF;
1208 END IF;
1209 END IF;
1210 -- update the version record
1211 okl_pal_pvt.update_row( l_api_version
1212 ,l_init_msg_list
1213 ,l_return_status
1214 ,x_msg_count
1215 ,x_msg_data
1216 ,l_pal_rec
1217 ,x_pal_rec);
1218
1219 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1220 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1221 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1222 RAISE OKL_API.G_EXCEPTION_ERROR;
1223 END IF;
1224
1225 ELSE
1226
1227 -- update the version record
1228 okl_pal_pvt.update_row( l_api_version
1229 ,l_init_msg_list
1230 ,l_return_status
1231 ,x_msg_count
1232 ,x_msg_data
1233 ,l_pal_rec
1234 ,x_pal_rec);
1235
1236 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1237 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1238 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1239 RAISE OKL_API.G_EXCEPTION_ERROR;
1240 END IF;
1241
1242
1243 END IF;
1244
1245
1246
1247 --end activity
1248 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1249 x_return_status := l_return_status;
1250
1251 EXCEPTION
1252 WHEN G_INVALID_ADJ_CAT_DATES THEN
1253 OKL_API.SET_MESSAGE( p_app_name => g_app_name,
1254 p_msg_name => g_invalid_value,
1255 p_token1 => g_col_name_token,
1256 p_token1_value => 'effective_from ');
1257 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1258 (
1259 l_api_name,
1260 G_PKG_NAME,
1261 'OKL_API.G_RET_STS_ERROR',
1262 x_msg_count,
1263 x_msg_data,
1264 '_PVT'
1265 );
1266 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1267 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1268 (
1269 l_api_name,
1270 G_PKG_NAME,
1271 'OKL_API.G_RET_STS_ERROR',
1272 x_msg_count,
1273 x_msg_data,
1274 '_PVT'
1275 );
1276
1277 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1278 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1279 (
1280 l_api_name,
1281 G_PKG_NAME,
1282 'OKL_API.G_RET_STS_UNEXP_ERROR',
1283 x_msg_count,
1284 x_msg_data,
1285 '_PVT'
1286 );
1287
1288 WHEN OTHERS THEN
1289 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1290 (
1291 l_api_name,
1292 G_PKG_NAME,
1293 'OTHERS',
1294 x_msg_count,
1295 x_msg_data,
1296 '_PVT'
1297 );
1298
1299 END UPDATE_ADJ_MAT;
1300
1301 -- procedure to create a new version of the Pricing Adjustment Matrix
1302 PROCEDURE CREATE_VERSION(
1303 p_api_version IN NUMBER,
1304 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1305 x_return_status OUT NOCOPY VARCHAR2,
1306 x_msg_count OUT NOCOPY NUMBER,
1307 x_msg_data OUT NOCOPY VARCHAR2,
1308 p_pal_rec IN okl_pal_rec,
1309 x_pal_rec OUT NOCOPY okl_pal_rec
1310 ) IS
1311
1312 l_pamv_rec okl_pamv_rec;
1313 x_pamv_rec okl_pamv_rec;
1314 l_pal_rec okl_pal_rec := p_pal_rec;
1315 l_api_version NUMBER := 1.0;
1316 l_api_name VARCHAR2(40):='UPDATE_ADJ_MAT';
1317 l_init_msg_list VARCHAR2(1):=p_init_msg_list;
1318 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1319 cal_eff_from DATE;
1320
1321 BEGIN
1322 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1323 G_PKG_NAME,
1324 l_init_msg_list,
1325 l_api_version,
1326 p_api_version,
1327 '_PVT',
1328 x_return_status);
1329
1330 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1331 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1332 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1333 RAISE OKL_API.G_EXCEPTION_ERROR;
1334 END IF;
1335
1336 -- fix for gmiss date
1337 l_pal_rec.effective_to_date := rosetta_g_miss_date_in_map(l_pal_rec.effective_to_date);
1338
1339 -- change the status of the header as under revision
1340 l_pamv_rec.ADJ_MAT_ID := l_pal_rec.ADJ_MAT_ID;
1341 l_pamv_rec.STS_CODE := 'UNDER_REVISION';
1342
1343 -- update the header record
1344 okl_pam_pvt.update_row(l_api_version
1345 ,l_init_msg_list
1346 ,l_return_status
1347 ,x_msg_count
1348 ,x_msg_data
1349 ,l_pamv_rec
1350 ,x_pamv_rec);
1351
1352 -- logic to be added. Check if user entered start date > the calculated start date
1353 -- else raise an exception
1354 calc_start_date( l_api_version
1355 ,l_init_msg_list
1356 ,l_return_status
1357 ,x_msg_count
1358 ,x_msg_data
1359 ,l_pal_rec
1360 ,cal_eff_from);
1361
1362 IF ( l_pal_rec.effective_from_date < cal_eff_from ) THEN
1363 RAISE G_INVALID_ADJ_CAT_DATES;
1364 END IF;
1365 -- insert the version record into the table
1366 okl_pal_pvt.insert_row( l_api_version
1367 ,l_init_msg_list
1368 ,l_return_status
1369 ,x_msg_count
1370 ,x_msg_data
1371 ,l_pal_rec
1372 ,x_pal_rec);
1373 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1374 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1375 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1376 RAISE OKL_API.G_EXCEPTION_ERROR;
1377 END IF;
1378
1379
1380 --end activity
1381 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1382 x_return_status := l_return_status;
1383
1384 EXCEPTION
1385 WHEN G_INVALID_ADJ_CAT_DATES THEN
1386 OKL_API.SET_MESSAGE( p_app_name => g_app_name,
1387 p_msg_name => 'OKL_INVALID_EFF_FROM',
1388 p_token1 => 'DATE',
1389 p_token1_value => cal_eff_from);
1390 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1391 (
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 (
1402 l_api_name,
1403 G_PKG_NAME,
1404 'OKL_API.G_RET_STS_ERROR',
1405 x_msg_count,
1406 x_msg_data,
1407 '_PVT'
1408 );
1409
1410 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1411 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1412 (
1413 l_api_name,
1414 G_PKG_NAME,
1415 'OKL_API.G_RET_STS_UNEXP_ERROR',
1416 x_msg_count,
1417 x_msg_data,
1418 '_PVT'
1419 );
1420
1421 WHEN OTHERS THEN
1422 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1423 (
1424 l_api_name,
1425 G_PKG_NAME,
1426 'OTHERS',
1427 x_msg_count,
1428 x_msg_data,
1429 '_PVT'
1430 );
1431
1432 END CREATE_VERSION;
1433
1434 -- procedure to raise the workflow which submits the record and changes the status.
1435 PROCEDURE SUBMIT_ADJ_MAT(
1436 p_api_version IN NUMBER,
1437 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1438 x_return_status OUT NOCOPY VARCHAR2,
1439 x_msg_count OUT NOCOPY NUMBER,
1440 x_msg_data OUT NOCOPY VARCHAR2,
1441 p_version_id IN NUMBER
1442 ) IS
1443
1444 l_pal_rec okl_pal_rec;
1445 x_pal_rec okl_pal_rec;
1446 l_api_version NUMBER := 1.0;
1447 l_api_name VARCHAR2(40):='UPDATE_ADJ_MAT';
1448 l_init_msg_list VARCHAR2(1):=p_init_msg_list;
1449 l_return_status VARCHAR2(1):=OKL_API.G_RET_STS_SUCCESS;
1450 l_parameter_list wf_parameter_list_t;
1451 p_event_name varchar2(240):='oracle.apps.okl.fe.pamapproval';
1452 l_profile_value VARCHAR2(30);
1453
1454 BEGIN
1455 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1456 G_PKG_NAME,
1457 l_init_msg_list,
1458 l_api_version,
1459 p_api_version,
1460 '_PVT',
1461 x_return_status);
1462
1463 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1464 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1465 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1466 RAISE OKL_API.G_EXCEPTION_ERROR;
1467 END IF;
1468
1469 l_pal_rec.ADJ_MAT_VERSION_ID := p_version_id;
1470 l_pal_rec.STS_CODE := 'SUBMITTED';
1471
1472 okl_pal_pvt.update_row( l_api_version
1473 ,p_init_msg_list
1474 ,l_return_status
1475 ,x_msg_count
1476 ,x_msg_data
1477 ,l_pal_rec
1478 ,x_pal_rec);
1479
1480 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1481 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1482 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1483 RAISE OKL_API.G_EXCEPTION_ERROR;
1484 END IF;
1485 fnd_profile.get('OKL_PE_APPROVAL_PROCESS',l_profile_value);
1486
1487 IF (nvl(l_profile_value,'NONE') = 'NONE') THEN
1488
1489 HANDLE_APPROVAL(
1490 p_api_version => l_api_version,
1491 p_init_msg_list => p_init_msg_list,
1492 x_return_status => l_return_status,
1493 x_msg_count => x_msg_count,
1494 x_msg_data => x_msg_data,
1495 p_version_id => p_version_id
1496 );
1497
1498 ELSE
1499 -- raise the business event passing the version id added to the parameter list
1500 wf_event.AddParameterToList('VERSION_ID',p_version_id,l_parameter_list);
1501 --added by akrangan
1502 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
1503
1504 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
1505 p_init_msg_list => p_init_msg_list,
1506 x_return_status => x_return_status,
1507 x_msg_count => x_msg_count,
1508 x_msg_data => x_msg_data,
1509 p_event_name => p_event_name,
1510 p_parameters => l_parameter_list);
1511
1512
1513 END IF;
1514
1515 --end activity
1516 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1517 x_return_status := l_return_status;
1518
1519 EXCEPTION
1520 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1521 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1522 (
1523 l_api_name,
1524 G_PKG_NAME,
1525 'OKL_API.G_RET_STS_ERROR',
1526 x_msg_count,
1527 x_msg_data,
1528 '_PVT'
1529 );
1530
1531 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1532 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1533 (
1534 l_api_name,
1535 G_PKG_NAME,
1536 'OKL_API.G_RET_STS_UNEXP_ERROR',
1537 x_msg_count,
1538 x_msg_data,
1539 '_PVT'
1540 );
1541
1542 WHEN OTHERS THEN
1543 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1544 (
1545 l_api_name,
1546 G_PKG_NAME,
1547 'OTHERS',
1548 x_msg_count,
1549 x_msg_data,
1550 '_PVT'
1551 );
1552 END SUBMIT_ADJ_MAT ;
1553
1554
1555 -- procedure to handle when the process is going through the process of approval
1556 PROCEDURE HANDLE_APPROVAL(
1557 p_api_version IN NUMBER,
1558 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1559 x_return_status OUT NOCOPY VARCHAR2,
1560 x_msg_count OUT NOCOPY NUMBER,
1561 x_msg_data OUT NOCOPY VARCHAR2,
1562 p_version_id IN NUMBER
1563 ) IS
1564 CURSOR adj_mat_version_csr(p_version_id IN NUMBER)IS
1565 SELECT ADJ_MAT_ID,
1566 VERSION_NUMBER,
1567 EFFECTIVE_FROM_DATE,
1568 EFFECTIVE_TO_DATE
1569 FROM okl_fe_adj_mat_versions
1570 WHERE ADJ_MAT_VERSION_ID = p_version_id;
1571
1572 CURSOR ver_eff_to_csr(p_adj_mat_id IN NUMBER, p_version_number IN NUMBER)IS
1573 SELECT ADJ_MAT_VERSION_ID,
1574 EFFECTIVE_TO_DATE FROM okl_fe_adj_mat_versions
1575 WHERE ADJ_MAT_ID=p_adj_mat_id and VERSION_NUMBER = p_version_number;
1576
1577 CURSOR max_version_csr(p_adj_mat_id IN NUMBER) IS
1578 SELECT max(VERSION_NUMBER) FROM OKL_FE_ADJ_MAT_VERSIONS
1579 WHERE ADJ_MAT_ID = p_adj_mat_id;
1580
1581
1582 CURSOR cal_end_date(p_version_id IN NUMBER) IS
1583 select max(effective_from_date) from okl_fe_std_rt_tmp_vers
1584 where adj_mat_version_id=p_version_id;
1585
1586 l_adj_mat_id NUMBER;
1587 l_adj_mat_ver_id NUMBER;
1588 l_version_number NUMBER;
1589 l_effective_from DATE;
1590 l_effective_to DATE;
1591 l_eff_prev_ver DATE;
1592 l_pamv_rec okl_pamv_rec;
1593 x_pamv_rec okl_pamv_rec;
1594 l_pal_rec okl_pal_rec;
1595 lp_pal_rec okl_pal_rec;
1596 x_pal_rec okl_pal_rec;
1597 l_max_version VARCHAR2(24);
1598 l_cal_end_date DATE;
1599 l_end_date DATE;
1600 l_api_version NUMBER := 1.0;
1601 l_api_name VARCHAR2(40):='handle_approval';
1602 k NUMBER :=1;
1603 l NUMBER :=1;
1604 lp_lrtv_tbl okl_lrs_id_tbl;
1605 lp_srtv_tbl okl_srt_id_tbl;
1606 x_obj_tbl invalid_object_tbl;
1607 BEGIN
1608 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1609 G_PKG_NAME,
1610 p_init_msg_list,
1611 l_api_version,
1612 p_api_version,
1613 '_PVT',
1614 x_return_status);
1615
1616 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1618 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1619 RAISE OKL_API.G_EXCEPTION_ERROR;
1620 END IF;
1621 -- if it is the first version,
1622 -- change the header status and the end date of the header as the version end date
1623 -- change the version status to active
1624 -- if it has already some versions,
1625 -- then end_date the previous versions
1626 -- then end date the reference of the previous version
1627 OPEN adj_mat_version_csr(p_version_id);
1628 FETCH adj_mat_version_csr INTO l_adj_mat_id,l_version_number,l_effective_from,l_effective_to;
1629 CLOSE adj_mat_version_csr;
1630
1631 OPEN max_version_csr(l_adj_mat_id);
1632 FETCH max_version_csr INTO l_max_version;
1633 CLOSE max_version_csr;
1634
1635 -- set the properties of the versions record
1636 lp_pal_rec.STS_CODE:='ACTIVE';
1637 lp_pal_rec.ADJ_MAT_VERSION_ID:= p_version_id;
1638 lp_pal_rec.ADJ_MAT_ID:= l_adj_mat_id;
1639 lp_pal_rec.VERSION_NUMBER:= l_version_number;
1640 lp_pal_rec.EFFECTIVE_FROM_DATE:=l_effective_from;
1641
1642 IF (l_version_number = 1) THEN
1643 l_pamv_rec.ADJ_MAT_ID:= l_adj_mat_id;
1644 l_pamv_rec.STS_CODE := 'ACTIVE';
1645 IF (l_effective_to IS NOT NULL) THEN
1646 l_pamv_rec.EFFECTIVE_TO_DATE :=l_effective_to;
1647 ELSE
1648 l_pamv_rec.EFFECTIVE_TO_DATE:= OKL_API.G_MISS_DATE;
1649 END IF;
1650 ELSIF (l_version_number < l_max_version) THEN
1651 -- only the last but one version can be updated in the case of Adjustment Matrix
1652
1653 l_pamv_rec.ADJ_MAT_ID := l_adj_mat_id;
1654 IF (l_effective_to IS NOT NULL) THEN
1655 l_pamv_rec.EFFECTIVE_TO_DATE := l_effective_to;
1656 ELSE
1657 l_pamv_rec.EFFECTIVE_TO_DATE:= OKL_API.G_MISS_DATE;
1658 END IF;
1659 l_end_date:= l_effective_to;
1660 ELSIF (l_version_number = l_max_version) THEN
1661
1662 -- get the previous version Effective To
1663 OPEN ver_eff_to_csr(l_adj_mat_id, l_version_number-1);
1664 FETCH ver_eff_to_csr INTO l_adj_mat_ver_id,l_eff_prev_ver;
1665 CLOSE ver_eff_to_csr;
1666
1667 lp_pal_rec.ADJ_MAT_VERSION_ID:= l_adj_mat_ver_id;
1668 calc_start_date(
1669 p_api_version ,
1670 p_init_msg_list ,
1671 x_return_status ,
1672 x_msg_count ,
1673 x_msg_data ,
1674 lp_pal_rec ,
1675 l_cal_end_date );
1676
1677 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
1678 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1679 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
1680 raise OKL_API.G_EXCEPTION_ERROR;
1681 END IF;
1682
1683 IF (lp_pal_rec.effective_from_date < l_cal_end_date ) THEN
1684 RAISE okl_api.g_exception_error;
1685 END IF;
1686
1687 l_cal_end_date:= lp_pal_rec.effective_from_date -1;
1688
1689 l_pamv_rec.ADJ_MAT_ID := l_adj_mat_id;
1690 l_pamv_rec.STS_CODE:= 'ACTIVE';
1691 IF (l_effective_to IS NOT NULL) THEN
1692 l_pamv_rec.EFFECTIVE_TO_DATE := l_effective_to;
1693 ELSE
1694 l_pamv_rec.EFFECTIVE_TO_DATE:= OKL_API.G_MISS_DATE;
1695 END IF;
1696
1697 -- update the previous version effective to
1698 IF (nvl(l_eff_prev_ver,okl_api.g_miss_date) <> l_cal_end_date) THEN
1699
1700 l_pal_rec.ADJ_MAT_VERSION_ID :=l_adj_mat_ver_id;
1701 l_pal_rec.EFFECTIVE_TO_DATE := l_cal_end_date;
1702 okl_pal_pvt.update_row( l_api_version
1703 ,p_init_msg_list
1704 ,x_return_status
1705 ,x_msg_count
1706 ,x_msg_data
1707 ,l_pal_rec
1708 ,x_pal_rec);
1709
1710 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1711 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1712 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1713 RAISE OKL_API.G_EXCEPTION_ERROR;
1714 END IF;
1715 -- put an end date to the previous version of the eligibility criteria
1716 okl_ecc_values_pvt.end_date_eligibility_criteria(
1717 p_api_version => l_api_version,
1718 p_init_msg_list => p_init_msg_list,
1719 x_return_status => x_return_status,
1720 x_msg_count => x_msg_count,
1721 x_msg_data => x_msg_data,
1722 p_source_id => l_adj_mat_ver_id,
1723 p_source_type => 'PAM',
1724 p_end_date => l_cal_end_date
1725 );
1726
1727 END IF;
1728 IF (l_cal_end_date IS NOT NULL) THEN
1729 -- end date the lease rate set versions
1730 INVALID_OBJECTS(
1731 p_api_version ,
1732 p_init_msg_list ,
1733 x_return_status ,
1734 x_msg_count ,
1735 x_msg_data ,
1736 l_adj_mat_ver_id,
1737 x_obj_tbl
1738 );
1739
1740 IF (x_obj_tbl.COUNT > 0) THEN
1741 -- populate the ids
1742 FOR j IN x_obj_tbl.FIRST..x_obj_tbl.LAST LOOP
1743 IF (x_obj_tbl(j).OBJ_TYPE = 'LRS') THEN
1744 lp_lrtv_tbl(k) := x_obj_tbl(j).obj_id;
1745 k:=k+1;
1746 ELSIF (x_obj_tbl(j).OBJ_TYPE = 'SRT') THEN
1747 lp_srtv_tbl(l) := x_obj_tbl(j).obj_id;
1748 l:=l+1;
1749 END IF;
1750 END LOOP;
1751 IF (k>1) THEN
1752 -- end date the referenced lease rate set
1753 okl_lease_rate_Sets_pvt.enddate_lease_rate_set(
1754 p_api_version
1755 ,p_init_msg_list
1756 ,x_return_status
1757 ,x_msg_count
1758 ,x_msg_data
1759 ,lp_lrtv_tbl
1760 ,l_cal_end_date
1761 );
1762 ENd IF;
1763 IF (l>1) THEN
1764 -- end date the referenced Standard Rate Template
1765 OKL_FE_STD_RATE_TMPL_PVT.enddate_std_rate_tmpl(
1766 p_api_version
1767 ,p_init_msg_list
1768 ,x_return_status
1769 ,x_msg_count
1770 ,x_msg_data
1771 ,lp_srtv_tbl
1772 ,l_cal_end_date
1773 );
1774 END IF;
1775 END IF;
1776 END IF;
1777
1778 END IF;
1779 --make the version status as active
1780 l_pal_rec.ADJ_MAT_VERSION_ID := p_version_id;
1781 l_pal_rec.STS_CODE := 'ACTIVE';
1782 l_pal_rec.EFFECTIVE_TO_DATE:=null;
1783 okl_pal_pvt.update_row( l_api_version
1784 ,p_init_msg_list
1785 ,x_return_status
1786 ,x_msg_count
1787 ,x_msg_data
1788 ,l_pal_rec
1789 ,x_pal_rec);
1790
1791
1792 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1793 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1794 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1795 RAISE OKL_API.G_EXCEPTION_ERROR;
1796 END IF;
1797 okl_pam_pvt.update_row( l_api_version
1798 ,p_init_msg_list
1799 ,x_return_status
1800 ,x_msg_count
1801 ,x_msg_data
1802 ,l_pamv_rec
1803 ,x_pamv_rec);
1804
1805 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1806 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1807 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1808 RAISE OKL_API.G_EXCEPTION_ERROR;
1809 END IF;
1810
1811
1812 --end activity
1813 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1814
1815 EXCEPTION
1816 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1817 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1818 (
1819 l_api_name,
1820 G_PKG_NAME,
1821 'OKL_API.G_RET_STS_ERROR',
1822 x_msg_count,
1823 x_msg_data,
1824 '_PVT'
1825 );
1826
1827 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1828 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1829 (
1830 l_api_name,
1831 G_PKG_NAME,
1832 'OKL_API.G_RET_STS_UNEXP_ERROR',
1833 x_msg_count,
1834 x_msg_data,
1835 '_PVT'
1836 );
1837
1838 WHEN OTHERS THEN
1839 IF adj_mat_version_csr%ISOPEN THEN
1840 CLOSE adj_mat_version_csr;
1841 END IF;
1842 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1843 (
1844 l_api_name,
1845 G_PKG_NAME,
1846 'OTHERS',
1847 x_msg_count,
1848 x_msg_data,
1849 '_PVT'
1850 );
1851 END HANDLE_APPROVAL;
1852
1853
1854 PROCEDURE INVALID_OBJECTS(
1855 p_api_version IN NUMBER,
1856 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false,
1857 x_return_status OUT NOCOPY VARCHAR2,
1858 x_msg_count OUT NOCOPY NUMBER,
1859 x_msg_data OUT NOCOPY VARCHAR2,
1860 p_version_id IN NUMBER,
1861 x_obj_tbl OUT NOCOPY invalid_object_tbl
1862 ) AS
1863 l_version_id NUMBER :=p_version_id;
1864 i NUMBER:=1;
1865 l_api_version NUMBER := 1.0;
1866 l_api_name VARCHAR2(40):='invalid_objects';
1867 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
1868
1869 -- cursor to calculate the SRT objects which are referncing this adjustment matrix
1870
1871 CURSOR srt_invalids_csr(p_version_id IN NUMBER) IS
1872 SELECT vers.std_rate_tmpl_ver_id ID,hdr.template_name NAME ,vers.version_number VERSION_NUMBER
1873 FROM okl_fe_std_rt_tmp_vers vers, okl_fe_std_rt_tmp_v hdr
1874 WHERE vers.std_rate_tmpl_id = hdr.std_rate_tmpl_id AND vers.adj_mat_version_id=p_version_id
1875 AND vers.STS_CODE='ACTIVE';
1876
1877 -- cursor to calculate the LRS objects which are referncing this adjustment matrix
1878
1879 CURSOR lrs_invalids_csr(p_version_id IN NUMBER) IS
1880 SELECT vers.RATE_SET_VERSION_ID ID,hdr.name NAME,vers.version_number VERSION_NUMBER
1881 FROM OKL_FE_RATE_SET_VERSIONS vers, OKL_LS_RT_FCTR_SETS_V hdr
1882 WHERE vers.rate_set_id = hdr.id AND vers.adj_mat_version_id=p_version_id
1883 AND vers.STS_CODE='ACTIVE';
1884
1885 -- cursor to calculate the LRS invalid for the invalid SRTs
1886 CURSOR lrs_srt_invalids_csr(p_version_id IN NUMBER) IS
1887 SELECT vers.rate_set_version_id id
1888 ,hdr.name name
1889 ,vers.version_number version_number
1890 FROM okl_fe_rate_set_versions vers
1891 ,okl_ls_rt_fctr_sets_v hdr
1892 WHERE vers.rate_set_id = hdr.id
1893 AND vers.std_rate_tmpl_ver_id = p_version_id
1894 AND vers.sts_code = 'ACTIVE';
1895
1896 BEGIN
1897 x_return_status := OKL_API.START_ACTIVITY(l_api_name,
1898 G_PKG_NAME,
1899 p_init_msg_list,
1900 l_api_version,
1901 p_api_version,
1902 '_PVT',
1903 x_return_status);
1904
1905 FOR srt_invalid_record IN srt_invalids_csr(p_version_id) LOOP
1906 x_obj_tbl(i).obj_id:=srt_invalid_record.ID;
1907 x_obj_tbl(i).obj_name:=srt_invalid_record.NAME;
1908 x_obj_tbl(i).obj_version :=srt_invalid_record.VERSION_NUMBER;
1909 x_obj_tbl(i).obj_type:='SRT';
1910 i:=i+1;
1911 -- invalid LRS for this SRT
1912 FOR lrs_srt_invalid_record IN lrs_srt_invalids_csr(srt_invalid_record.ID) LOOP
1913 x_obj_tbl(i).obj_id:=lrs_srt_invalid_record.ID;
1914 x_obj_tbl(i).obj_name:=lrs_srt_invalid_record.NAME;
1915 x_obj_tbl(i).obj_version :=lrs_srt_invalid_record.VERSION_NUMBER;
1916 x_obj_tbl(i).obj_type:='LRS';
1917 i:=i+1;
1918 END LOOP;
1919 END LOOP;
1920
1921 FOR lrs_invalid_record IN lrs_invalids_csr(p_version_id) LOOP
1922 x_obj_tbl(i).obj_id:=lrs_invalid_record.ID;
1923 x_obj_tbl(i).obj_name:=lrs_invalid_record.NAME;
1924 x_obj_tbl(i).obj_version :=lrs_invalid_record.VERSION_NUMBER;
1925 x_obj_tbl(i).obj_type:='LRS';
1926 i:=i+1;
1927 END LOOP;
1928
1929 --end activity
1930 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1931 x_return_status := l_return_status;
1932
1933
1934 EXCEPTION
1935 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1936 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1937 (
1938 l_api_name,
1939 G_PKG_NAME,
1940 'OKL_API.G_RET_STS_ERROR',
1941 x_msg_count,
1942 x_msg_data,
1943 '_PVT'
1944 );
1945
1946 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1947 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1948 (
1949 l_api_name,
1950 G_PKG_NAME,
1951 'OKL_API.G_RET_STS_UNEXP_ERROR',
1952 x_msg_count,
1953 x_msg_data,
1954 '_PVT'
1955 );
1956
1957 WHEN OTHERS THEN
1958 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1959 (
1960 l_api_name,
1961 G_PKG_NAME,
1962 'OTHERS',
1963 x_msg_count,
1964 x_msg_data,
1965 '_PVT'
1966 );
1967 END INVALID_OBJECTS;
1968
1969 END OKL_FE_ADJ_MATRIX_PVT;