[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPOPTIONS_PVT
Source
1 PACKAGE BODY Okl_Setupoptions_Pvt AS
2 /* $Header: OKLRSOTB.pls 115.13 2003/07/23 18:33:51 sgorantl noship $ */
3
4 SUBTYPE ovev_rec_type IS Okl_Options_Pub.ovev_rec_type;
5 SUBTYPE ovev_tbl_type IS Okl_Options_Pub.ovev_tbl_type;
6
7 ---------------------------------------------------------------------------
8 -- PROCEDURE get_rec for: OKL_OPTIONS_V
9 ---------------------------------------------------------------------------
10 PROCEDURE get_rec (
11 p_optv_rec IN optv_rec_type,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_no_data_found OUT NOCOPY BOOLEAN,
14 x_optv_rec OUT NOCOPY optv_rec_type
15 ) IS
16 CURSOR okl_optv_pk_csr (p_id IN NUMBER) IS
17 SELECT
18 ID,
19 OBJECT_VERSION_NUMBER,
20 NAME,
21 NVL(DESCRIPTION,Okl_Api.G_MISS_CHAR) DESCRIPTION,
22 FROM_DATE,
23 NVL(TO_DATE,Okl_Api.G_MISS_DATE) TO_DATE,
24 NVL(ATTRIBUTE_CATEGORY, Okl_Api.G_MISS_CHAR) ATTRIBUTE_CATEGORY,
25 NVL(ATTRIBUTE1, Okl_Api.G_MISS_CHAR) ATTRIBUTE1,
26 NVL(ATTRIBUTE2, Okl_Api.G_MISS_CHAR) ATTRIBUTE2,
27 NVL(ATTRIBUTE3, Okl_Api.G_MISS_CHAR) ATTRIBUTE3,
28 NVL(ATTRIBUTE4, Okl_Api.G_MISS_CHAR) ATTRIBUTE4,
29 NVL(ATTRIBUTE5, Okl_Api.G_MISS_CHAR) ATTRIBUTE5,
30 NVL(ATTRIBUTE6, Okl_Api.G_MISS_CHAR) ATTRIBUTE6,
31 NVL(ATTRIBUTE7, Okl_Api.G_MISS_CHAR) ATTRIBUTE7,
32 NVL(ATTRIBUTE8, Okl_Api.G_MISS_CHAR) ATTRIBUTE8,
33 NVL(ATTRIBUTE9, Okl_Api.G_MISS_CHAR) ATTRIBUTE9,
34 NVL(ATTRIBUTE10, Okl_Api.G_MISS_CHAR) ATTRIBUTE10,
35 NVL(ATTRIBUTE11, Okl_Api.G_MISS_CHAR) ATTRIBUTE11,
36 NVL(ATTRIBUTE12, Okl_Api.G_MISS_CHAR) ATTRIBUTE12,
37 NVL(ATTRIBUTE13, Okl_Api.G_MISS_CHAR) ATTRIBUTE13,
38 NVL(ATTRIBUTE14, Okl_Api.G_MISS_CHAR) ATTRIBUTE14,
39 NVL(ATTRIBUTE15, Okl_Api.G_MISS_CHAR) ATTRIBUTE15,
40 CREATED_BY,
41 CREATION_DATE,
42 LAST_UPDATED_BY,
43 LAST_UPDATE_DATE,
44 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
45 FROM Okl_Options_V
46 WHERE okl_options_v.id = p_id;
47 l_okl_optv_pk okl_optv_pk_csr%ROWTYPE;
48 l_optv_rec optv_rec_type;
49 BEGIN
50 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
51 x_no_data_found := TRUE;
52
53 -- Get current database values
54 OPEN okl_optv_pk_csr (p_optv_rec.id);
55 FETCH okl_optv_pk_csr INTO
56 l_optv_rec.ID,
57 l_optv_rec.OBJECT_VERSION_NUMBER,
58 l_optv_rec.NAME,
59 l_optv_rec.DESCRIPTION,
60 l_optv_rec.FROM_DATE,
61 l_optv_rec.TO_DATE,
62 l_optv_rec.ATTRIBUTE_CATEGORY,
63 l_optv_rec.ATTRIBUTE1,
64 l_optv_rec.ATTRIBUTE2,
65 l_optv_rec.ATTRIBUTE3,
66 l_optv_rec.ATTRIBUTE4,
67 l_optv_rec.ATTRIBUTE5,
68 l_optv_rec.ATTRIBUTE6,
69 l_optv_rec.ATTRIBUTE7,
70 l_optv_rec.ATTRIBUTE8,
71 l_optv_rec.ATTRIBUTE9,
72 l_optv_rec.ATTRIBUTE10,
73 l_optv_rec.ATTRIBUTE11,
74 l_optv_rec.ATTRIBUTE12,
75 l_optv_rec.ATTRIBUTE13,
76 l_optv_rec.ATTRIBUTE14,
77 l_optv_rec.ATTRIBUTE15,
78 l_optv_rec.CREATED_BY,
79 l_optv_rec.CREATION_DATE,
80 l_optv_rec.LAST_UPDATED_BY,
81 l_optv_rec.LAST_UPDATE_DATE,
82 l_optv_rec.LAST_UPDATE_LOGIN;
83 x_no_data_found := okl_optv_pk_csr%NOTFOUND;
84 CLOSE okl_optv_pk_csr;
85 x_optv_rec := l_optv_rec;
86 EXCEPTION
87 WHEN OTHERS THEN
88 -- store SQL error message on message stack
89 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
90 p_msg_name => G_UNEXPECTED_ERROR,
91 p_token1 => G_SQLCODE_TOKEN,
92 p_token1_value => SQLCODE,
93 p_token2 => G_SQLERRM_TOKEN,
94 p_token2_value => SQLERRM);
95 -- notify UNEXPECTED error for calling API.
96 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
97
98 IF (okl_optv_pk_csr%ISOPEN) THEN
99 CLOSE okl_optv_pk_csr;
100 END IF;
101
102 END get_rec;
103
104 ---------------------------------------------------------------------------
105 -- PROCEDURE get_opt_values for: OKL_OPTIONS_V
106 -- To fetch the valid values for the OPTIONS.
107 ---------------------------------------------------------------------------
108 PROCEDURE get_opt_values (p_upd_optv_rec IN optv_rec_type,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_count OUT NOCOPY NUMBER,
111 x_ovev_tbl OUT NOCOPY ovev_tbl_type
112 ) IS
113 CURSOR okl_ovev_fk_csr (p_opt_id IN Okl_opt_values_V.id%TYPE) IS
114 SELECT ove.ID ID,
115 ove.FROM_DATE FROM_DATE,
116 ove.TO_DATE TO_DATE
117 FROM Okl_opt_Values_V ove
118 WHERE ove.opt_id = p_opt_id
119 AND ove.TO_DATE IS NULL;
120
121 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
122 l_count NUMBER := 0;
123 l_ovev_tbl ovev_tbl_type;
124 i NUMBER := 0;
125
126 BEGIN
127 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
128
129 -- Get current database values
130 FOR okl_ove_rec IN okl_ovev_fk_csr(p_upd_optv_rec.id)
131 LOOP
132 l_ovev_tbl(l_count).ID := okl_ove_rec.ID;
133 l_ovev_tbl(l_count).TO_DATE := p_upd_optv_rec.TO_DATE;
134 l_count := l_count + 1;
135 END LOOP;
136
137 x_count := l_count;
138 x_ovev_tbl := l_ovev_tbl;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 -- store SQL error message on message stack
143 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
144 p_msg_name => G_UNEXPECTED_ERROR,
145 p_token1 => G_SQLCODE_TOKEN,
146 p_token1_value => SQLCODE,
147 p_token2 => G_SQLERRM_TOKEN,
148 p_token2_value => SQLERRM );
149 -- notify UNEXPECTED error for calling API.
150 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
151
152 IF (okl_ovev_fk_csr%ISOPEN) THEN
153 CLOSE okl_ovev_fk_csr;
154 END IF;
155
156 END get_opt_values;
157
158 ---------------------------------------------------------------------------
159 -- PROCEDURE check_constraints for: OKL_OPTIONS_V
160 -- To verify whether the dates modification is valid in relation with
161 -- the attached Option Rules, Option Values and Product
162 ---------------------------------------------------------------------------
163 PROCEDURE Check_Constraints (
164 p_optv_rec IN optv_rec_type,
165 x_return_status OUT NOCOPY VARCHAR2,
166 x_valid OUT NOCOPY BOOLEAN
167 ) IS
168 CURSOR okl_opt_orl_fk_csr (p_opt_id IN Okl_Options_V.id%TYPE,
169 p_from_date IN Okl_Options_V.from_date%TYPE,
170 p_to_date IN Okl_Options_V.TO_DATE%TYPE
171
172 ) IS
173 SELECT '1'
174 FROM Okl_Opt_Rules_V orl,
175 Okl_Lse_Scs_Rules_V lsr
176 WHERE orl.OPT_ID = p_opt_id
177 AND ((orl.LRG_LSE_ID IS NOT NULL
178 AND lsr.LSE_ID = orl.LRG_LSE_ID
179 AND lsr.SRD_ID = orl.LRG_SRD_ID)
180 OR
181 (orl.LRG_LSE_ID IS NULL
182 AND lsr.LSE_ID IS NULL
183 AND lsr.SRD_ID = orl.SRD_ID_FOR))
184 AND lsr.RULE_GROUP = orl.RGR_RGD_CODE
185 AND lsr.RULE = orl.RGR_RDF_CODE
186 AND ((lsr.START_DATE > p_from_date OR
187 P_from_date > NVL(lsr.END_DATE,p_from_date)) OR
188 NVL(lsr.END_DATE, p_to_date) < p_to_date);
189
190 CURSOR okl_opt_ove_fk_csr (p_opt_id IN Okl_Options_V.id%TYPE,
191 p_from_date IN Okl_Options_V.from_date%TYPE,
192 p_to_date IN Okl_Options_V.TO_DATE%TYPE
193
194 ) IS
195 SELECT '1'
196 FROM Okl_Opt_Values_V ove
197 WHERE ove.OPT_ID = p_opt_id
198 AND (ove.FROM_DATE < p_from_date OR
199 NVL(ove.TO_DATE, ove.FROM_DATE) > p_to_date);
200
201 CURSOR okl_opt_pon_fk_csr (p_opt_id IN Okl_Options_V.ID%TYPE,
202 p_from_date IN Okl_Options_V.from_date%TYPE,
203 p_to_date IN Okl_Options_V.TO_DATE%TYPE
204 ) IS
205 SELECT '1'
206 FROM Okl_Pdt_Opts_V pon
207 WHERE pon.OPT_ID = p_opt_id
208 AND (pon.FROM_DATE < p_from_date OR
209 NVL(pon.TO_DATE, pon.FROM_DATE) > p_to_date);
210
211 l_token_1 VARCHAR2(1999);
212 l_token_2 VARCHAR2(1999);
213 l_token_3 VARCHAR2(1999);
214 l_token_4 VARCHAR2(1999);
215 l_optv_rec optv_rec_type;
216 l_check VARCHAR2(1) := '?';
217 l_row_not_found BOOLEAN := FALSE;
218 l_to_date okl_options_v.TO_DATE%TYPE;
219 BEGIN
220 x_valid := TRUE;
221 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
222
223 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_SERCH',
224 p_attribute_code => 'OKL_OPTIONS');
225
226 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_RULE_SERCH',
227 p_attribute_code => 'OKL_OPTION_RULES');
228
229 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_SERCH',
230 p_attribute_code => 'OKL_OPTION_VALUES');
231
232 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_OPTION_SERCH',
233 p_attribute_code => 'OKL_PRODUCT_OPTIONS');
234
235
236 -- Fix for g_miss_date
237 IF p_optv_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
238 l_to_date := NULL;
239 ELSE
240 l_to_date := p_optv_rec.TO_DATE;
241 END IF;
242
243
244 -- Check for option rules dates
245 OPEN okl_opt_orl_fk_csr (p_optv_rec.id,
246 p_optv_rec.from_date,
247 l_to_date);
248 FETCH okl_opt_orl_fk_csr INTO l_check;
249 l_row_not_found := okl_opt_orl_fk_csr%NOTFOUND;
250 CLOSE okl_opt_orl_fk_csr;
251
252 IF l_row_not_found = FALSE THEN
253 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
254 p_msg_name => G_DATES_MISMATCH,
255 p_token1 => G_PARENT_TABLE_TOKEN,
256 p_token1_value => l_token_1,
257 p_token2 => G_CHILD_TABLE_TOKEN,
258 p_token2_value => l_token_2);
259 x_valid := FALSE;
260 x_return_status := Okl_Api.G_RET_STS_ERROR;
261 RAISE G_EXCEPTION_HALT_PROCESSING;
262 END IF;
263
264 -- Check for option values dates
265 OPEN okl_opt_ove_fk_csr (p_optv_rec.id,
266 p_optv_rec.from_date,
267 l_to_date);
268 FETCH okl_opt_ove_fk_csr INTO l_check;
269 l_row_not_found := okl_opt_ove_fk_csr%NOTFOUND;
270 CLOSE okl_opt_ove_fk_csr;
271
272 IF l_row_not_found = FALSE THEN
273 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
274 p_msg_name => G_DATES_MISMATCH,
275 p_token1 => G_PARENT_TABLE_TOKEN,
276 p_token1_value => l_token_1,
277 p_token2 => G_CHILD_TABLE_TOKEN,
278 p_token2_value => l_token_3);
279 x_valid := FALSE;
280 x_return_status := Okl_Api.G_RET_STS_ERROR;
281 RAISE G_EXCEPTION_HALT_PROCESSING;
282 END IF;
283
284 -- Check for product dates
285 OPEN okl_opt_pon_fk_csr (p_optv_rec.id,
286 p_optv_rec.from_date,
287 l_to_date);
288 FETCH okl_opt_pon_fk_csr INTO l_check;
289 l_row_not_found := okl_opt_pon_fk_csr%NOTFOUND;
290 CLOSE okl_opt_pon_fk_csr;
291
292 IF l_row_not_found = FALSE THEN
293 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
294 p_msg_name => G_DATES_MISMATCH,
295 p_token1 => G_PARENT_TABLE_TOKEN,
296 p_token1_value => l_token_1,
297 p_token2 => G_CHILD_TABLE_TOKEN,
298 p_token2_value => l_token_4);
299 x_valid := FALSE;
300 x_return_status := Okl_Api.G_RET_STS_ERROR;
301 RAISE G_EXCEPTION_HALT_PROCESSING;
302 END IF;
303
304 EXCEPTION
305 WHEN G_EXCEPTION_HALT_PROCESSING THEN
306 -- no processing necessary; validation can continue
307 -- with the next column
308 NULL;
309
310 WHEN OTHERS THEN
311 -- store SQL error message on message stack
312 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
313 p_msg_name => G_UNEXPECTED_ERROR,
314 p_token1 => G_SQLCODE_TOKEN,
315 p_token1_value => SQLCODE,
316 p_token2 => G_SQLERRM_TOKEN,
317 p_token2_value => SQLERRM);
318 x_valid := FALSE;
319 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
320
321 IF (okl_opt_orl_fk_csr%ISOPEN) THEN
322 CLOSE okl_opt_orl_fk_csr;
323 END IF;
324
325 IF (okl_opt_ove_fk_csr%ISOPEN) THEN
326 CLOSE okl_opt_ove_fk_csr;
327 END IF;
328
329 IF (okl_opt_pon_fk_csr%ISOPEN) THEN
330 CLOSE okl_opt_pon_fk_csr;
331 END IF;
332
333 END Check_Constraints;
334
335
336 ---------------------------------------------------------------------------
337 -- PROCEDURE Validate _Name
338 ---------------------------------------------------------------------------
339 -- Start of comments
340 --
341 -- Procedure Name : Validate _Name
342 -- Description :
343 -- Business Rules :
344 -- Parameters :
345 -- Version : 1.0
346 -- End of comments
347 ---------------------------------------------------------------------------
348
349 PROCEDURE Validate_Name(
350 p_optv_rec IN OUT NOCOPY optv_rec_type,
351 x_return_status OUT NOCOPY VARCHAR2
352 ) IS
353 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
354 l_token_1 VARCHAR2(999);
355 BEGIN
356
357 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTION_CRUPD','OKL_NAME');
358 IF p_optv_rec.name = Okl_Api.G_MISS_CHAR OR
359 p_optv_rec.name IS NULL
360 THEN
361 Okl_Api.set_message(Okl_Opt_Pvt.G_APP_NAME, Okl_Opt_Pvt.G_REQUIRED_VALUE,Okl_Opt_Pvt.G_COL_NAME_TOKEN,l_token_1);
362 x_return_status := Okl_Api.G_RET_STS_ERROR;
363 END IF;
364 p_optv_rec.name := Okl_Accounting_Util.okl_upper(p_optv_rec.name);
365 EXCEPTION
366 WHEN OTHERS THEN
367 Okl_Api.set_message(p_app_name =>Okl_Opt_Pvt.G_APP_NAME,
368 p_msg_name =>Okl_Opt_Pvt.G_UNEXPECTED_ERROR,
369 p_token1 =>Okl_Opt_Pvt.G_SQL_SQLCODE_TOKEN,
370 p_token1_value =>SQLCODE,
371 p_token2 =>Okl_Opt_Pvt.G_SQL_SQLERRM_TOKEN,
372 p_token2_value =>SQLERRM);
373 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
374
375 END Validate_Name;
376 ------end of Validate_Name-----------------------------------
377
378 ---------------------------------------------------------------------------
379 -- PROCEDURE Validate _From_Date
380 ---------------------------------------------------------------------------
381 -- Start of comments
382 --
383 -- Procedure Name : Validate _From_Date
384 -- Description :
385 -- Business Rules :
386 -- Parameters :
387 -- Version : 1.0
388 -- End of comments
389 ---------------------------------------------------------------------------
390 PROCEDURE Validate_From_Date(
391 p_optv_rec IN optv_rec_type,
392 x_return_status OUT NOCOPY VARCHAR2
393 ) IS
394 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
395 l_token_1 VARCHAR2(999);
396 BEGIN
397 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTION_CRUPD','OKL_EFFECTIVE_FROM');
398 IF p_optv_rec.from_date IS NULL OR p_optv_rec.from_date = Okl_Api.G_MISS_DATE
399 THEN
400 Okl_Api.set_message(Okl_Opt_Pvt.G_APP_NAME, Okl_Opt_Pvt.G_REQUIRED_VALUE,Okl_Opt_Pvt.G_COL_NAME_TOKEN,l_token_1);
401 x_return_status := Okl_Api.G_RET_STS_ERROR;
402 END IF;
403 EXCEPTION
404 WHEN OTHERS THEN
405 Okl_Api.set_message(p_app_name =>Okl_Opt_Pvt.G_APP_NAME,
406 p_msg_name =>Okl_Opt_Pvt.G_UNEXPECTED_ERROR,
407 p_token1 =>Okl_Opt_Pvt.G_SQL_SQLCODE_TOKEN,
408 p_token1_value =>SQLCODE,
409 p_token2 =>Okl_Opt_Pvt.G_SQL_SQLERRM_TOKEN,
410 p_token2_value =>SQLERRM);
411 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
412
413 END Validate_From_Date;
414 ------end of Validate_From_Date-----------------------------------
415
416
417 ---------------------------------------------------------------------------
418 -- FUNCTION Validate_Attributes
419 ---------------------------------------------------------------------------
420 -- Start of comments
421 --
422 -- Function Name : Validate_Attributes
423 -- Description :
424 -- Business Rules :
425 -- Parameters :
426 -- Version : 1.0
427 -- End of comments
428 ---------------------------------------------------------------------------
429
430 FUNCTION Validate_Attributes(
431 p_optv_rec IN OUT NOCOPY optv_rec_type
432 ) RETURN VARCHAR IS
433 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
434 l_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
435
436
437 BEGIN
438 -------CHECK FOR NAME------------------
439 Validate_Name (p_optv_rec, x_return_status);
440 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
441 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
442 -- need to leave
443 l_return_status := x_return_status;
444 RAISE G_EXCEPTION_HALT_PROCESSING;
445 ELSE
446 l_return_status := x_return_status;
447 END IF;
448 END IF;
449
450 -----CHECK FOR FROM_DATE----------------------------
451 Validate_From_Date (p_optv_rec, x_return_status);
452 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
453 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
454 -- need to leave
455 l_return_status := x_return_status;
456 RAISE G_EXCEPTION_HALT_PROCESSING;
457 ELSE
458 l_return_status := x_return_status;
459 END IF;
460 END IF;
461
462
463 RETURN(l_return_status);
464 EXCEPTION
465 WHEN G_EXCEPTION_HALT_PROCESSING THEN
466 -- just come out with return status
467 NULL;
468 RETURN (l_return_status);
469
470 WHEN OTHERS THEN
471 Okl_Api.set_message(p_app_name =>G_APP_NAME,
472 p_msg_name =>Okl_Opt_Pvt.G_UNEXPECTED_ERROR,
473 p_token1 =>Okl_Opt_Pvt.G_SQL_SQLCODE_TOKEN,
474 p_token1_value =>SQLCODE,
475 p_token2 =>Okl_Opt_Pvt.G_SQL_SQLERRM_TOKEN,
476 p_token2_value =>SQLERRM);
477 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
478 RETURN(l_return_status);
479
480 END Validate_Attributes;
481
482 -----END OF VALIDATE ATTRIBUTES-------------------------
483
484 ---------------------------------------------------------------------------
485 -- PROCEDURE reorganize_inputs
486 -- This procedure is to reset the attributes in the input structure based
487 -- on the data from database
488 ---------------------------------------------------------------------------
489 PROCEDURE reorganize_inputs (
490 p_upd_optv_rec IN OUT NOCOPY optv_rec_type,
491 p_db_optv_rec IN optv_rec_type
492 ) IS
493 l_upd_optv_rec optv_rec_type;
494 l_db_optv_rec optv_rec_type;
495 BEGIN
496 /* create a temporary record with all relevant details from db and upd records */
497 l_upd_optv_rec := p_upd_optv_rec;
498 l_db_optv_rec := p_db_optv_rec;
499
500 IF l_upd_optv_rec.description = l_db_optv_rec.description THEN
501 l_upd_optv_rec.description := Okl_Api.G_MISS_CHAR;
502 END IF;
503
504 IF to_date(to_char(l_upd_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
505 l_upd_optv_rec.from_date := Okl_Api.G_MISS_DATE;
506 END IF;
507
508 IF to_date(to_char(l_upd_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
509 l_upd_optv_rec.TO_DATE := Okl_Api.G_MISS_DATE;
510 END IF;
511
512 IF l_upd_optv_rec.attribute_category = l_db_optv_rec.attribute_category THEN
513 l_upd_optv_rec.attribute_category := Okl_Api.G_MISS_CHAR;
514 END IF;
515
516 IF l_upd_optv_rec.attribute1 = l_db_optv_rec.attribute1 THEN
517 l_upd_optv_rec.attribute1 := Okl_Api.G_MISS_CHAR;
518 END IF;
519
520 IF l_upd_optv_rec.attribute2 = l_db_optv_rec.attribute2 THEN
521 l_upd_optv_rec.attribute2 := Okl_Api.G_MISS_CHAR;
522 END IF;
523
524 IF l_upd_optv_rec.attribute3 = l_db_optv_rec.attribute3 THEN
525 l_upd_optv_rec.attribute3 := Okl_Api.G_MISS_CHAR;
526 END IF;
527
528 IF l_upd_optv_rec.attribute4 = l_db_optv_rec.attribute4 THEN
529 l_upd_optv_rec.attribute4 := Okl_Api.G_MISS_CHAR;
530 END IF;
531
532 IF l_upd_optv_rec.attribute5 = l_db_optv_rec.attribute5 THEN
533 l_upd_optv_rec.attribute5 := Okl_Api.G_MISS_CHAR;
534 END IF;
535
536 IF l_upd_optv_rec.attribute6 = l_db_optv_rec.attribute6 THEN
537 l_upd_optv_rec.attribute6 := Okl_Api.G_MISS_CHAR;
538 END IF;
539
540 IF l_upd_optv_rec.attribute7 = l_db_optv_rec.attribute7 THEN
541 l_upd_optv_rec.attribute7 := Okl_Api.G_MISS_CHAR;
542 END IF;
543
544 IF l_upd_optv_rec.attribute8 = l_db_optv_rec.attribute8 THEN
545 l_upd_optv_rec.attribute8 := Okl_Api.G_MISS_CHAR;
546 END IF;
547
548 IF l_upd_optv_rec.attribute9 = l_db_optv_rec.attribute9 THEN
549 l_upd_optv_rec.attribute9 := Okl_Api.G_MISS_CHAR;
550 END IF;
551
552 IF l_upd_optv_rec.attribute10 = l_db_optv_rec.attribute10 THEN
553 l_upd_optv_rec.attribute10 := Okl_Api.G_MISS_CHAR;
554 END IF;
555
556 IF l_upd_optv_rec.attribute11 = l_db_optv_rec.attribute11 THEN
557 l_upd_optv_rec.attribute11 := Okl_Api.G_MISS_CHAR;
558 END IF;
559
560 IF l_upd_optv_rec.attribute12 = l_db_optv_rec.attribute12 THEN
561 l_upd_optv_rec.attribute12 := Okl_Api.G_MISS_CHAR;
562 END IF;
563
564 IF l_upd_optv_rec.attribute13 = l_db_optv_rec.attribute13 THEN
565 l_upd_optv_rec.attribute13 := Okl_Api.G_MISS_CHAR;
566 END IF;
567
568 IF l_upd_optv_rec.attribute14 = l_db_optv_rec.attribute14 THEN
569 l_upd_optv_rec.attribute14 := Okl_Api.G_MISS_CHAR;
570 END IF;
571
572 IF l_upd_optv_rec.attribute15 = l_db_optv_rec.attribute15 THEN
573 l_upd_optv_rec.attribute15 := Okl_Api.G_MISS_CHAR;
574 END IF;
575
576 p_upd_optv_rec := l_upd_optv_rec;
577
578 END reorganize_inputs;
579
580 ---------------------------------------------------------------------------
581 -- FUNCTION defaults_to_actuals
582 -- This function creates an output record with changed information from the
583 -- input structure and unchanged details from the database
584 ---------------------------------------------------------------------------
585 FUNCTION defaults_to_actuals (
586 p_upd_optv_rec IN optv_rec_type,
587 p_db_optv_rec IN optv_rec_type
588 ) RETURN optv_rec_type IS
589 l_optv_rec optv_rec_type;
590 BEGIN
591 /* create a temporary record with all relevant details from db and upd records */
592 l_optv_rec := p_db_optv_rec;
593
594 IF p_upd_optv_rec.description <> Okl_Api.G_MISS_CHAR THEN
595 l_optv_rec.description := p_upd_optv_rec.description;
596 END IF;
597
598 IF p_upd_optv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
599 l_optv_rec.from_date := p_upd_optv_rec.from_date;
600 END IF;
601
602 IF p_upd_optv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
603 l_optv_rec.TO_DATE := p_upd_optv_rec.TO_DATE;
604 END IF;
605
606 IF p_upd_optv_rec.attribute_category <> Okl_Api.G_MISS_CHAR THEN
607 l_optv_rec.attribute_category := p_upd_optv_rec.attribute_category;
608 END IF;
609
610 IF p_upd_optv_rec.attribute1 <> Okl_Api.G_MISS_CHAR THEN
611 l_optv_rec.attribute1 := p_upd_optv_rec.attribute1;
612 END IF;
613
614 IF p_upd_optv_rec.attribute2 <> Okl_Api.G_MISS_CHAR THEN
615 l_optv_rec.attribute2 := p_upd_optv_rec.attribute2;
616 END IF;
617
618 IF p_upd_optv_rec.attribute3 <> Okl_Api.G_MISS_CHAR THEN
619 l_optv_rec.attribute3 := p_upd_optv_rec.attribute3;
620 END IF;
621
622 IF p_upd_optv_rec.attribute4 <> Okl_Api.G_MISS_CHAR THEN
623 l_optv_rec.attribute4 := p_upd_optv_rec.attribute4;
624 END IF;
625
626 IF p_upd_optv_rec.attribute5 <> Okl_Api.G_MISS_CHAR THEN
627 l_optv_rec.attribute5 := p_upd_optv_rec.attribute5;
628 END IF;
629
630 IF p_upd_optv_rec.attribute6 <> Okl_Api.G_MISS_CHAR THEN
631 l_optv_rec.attribute6 := p_upd_optv_rec.attribute6;
632 END IF;
633
634 IF p_upd_optv_rec.attribute7 <> Okl_Api.G_MISS_CHAR THEN
635 l_optv_rec.attribute7 := p_upd_optv_rec.attribute7;
636 END IF;
637
638 IF p_upd_optv_rec.attribute8 <> Okl_Api.G_MISS_CHAR THEN
639 l_optv_rec.attribute8 := p_upd_optv_rec.attribute8;
640 END IF;
641
642 IF p_upd_optv_rec.attribute9 <> Okl_Api.G_MISS_CHAR THEN
643 l_optv_rec.attribute9 := p_upd_optv_rec.attribute9;
644 END IF;
645
646 IF p_upd_optv_rec.attribute10 <> Okl_Api.G_MISS_CHAR THEN
647 l_optv_rec.attribute10 := p_upd_optv_rec.attribute10;
648 END IF;
649
650 IF p_upd_optv_rec.attribute11 <> Okl_Api.G_MISS_CHAR THEN
651 l_optv_rec.attribute11 := p_upd_optv_rec.attribute11;
652 END IF;
653
654 IF p_upd_optv_rec.attribute12 <> Okl_Api.G_MISS_CHAR THEN
655 l_optv_rec.attribute12 := p_upd_optv_rec.attribute12;
656 END IF;
657
658 IF p_upd_optv_rec.attribute13 <> Okl_Api.G_MISS_CHAR THEN
659 l_optv_rec.attribute13 := p_upd_optv_rec.attribute13;
660 END IF;
661
662 IF p_upd_optv_rec.attribute14 <> Okl_Api.G_MISS_CHAR THEN
663 l_optv_rec.attribute14 := p_upd_optv_rec.attribute14;
664 END IF;
665
666 IF p_upd_optv_rec.attribute15 <> Okl_Api.G_MISS_CHAR THEN
667 l_optv_rec.attribute15 := p_upd_optv_rec.attribute15;
668 END IF;
669
670 RETURN l_optv_rec;
671 END defaults_to_actuals;
672
673 ---------------------------------------------------------------------------
674 -- PROCEDURE check_updates
675 -- To verify whether the requested changes from the screen are valid or not
676 ---------------------------------------------------------------------------
677 PROCEDURE check_updates (
678 p_upd_optv_rec IN optv_rec_type,
679 p_db_optv_rec IN optv_rec_type,
680 p_optv_rec IN optv_rec_type,
681 x_return_status OUT NOCOPY VARCHAR2,
682 x_msg_data OUT NOCOPY VARCHAR2
683 ) IS
684 l_optv_rec optv_rec_type;
685 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
686 l_valid BOOLEAN;
687 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
688 BEGIN
689 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
690 l_optv_rec := p_optv_rec;
691
692 /* check for start date greater than sysdate */
693 /*IF to_date(to_char(p_upd_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
694 to_date(to_char(p_upd_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
695 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
696 p_msg_name => G_START_DATE);
697 x_return_status := Okl_Api.G_RET_STS_ERROR;
698 RAISE G_EXCEPTION_HALT_PROCESSING;
699 END IF;
700 */
701 /* check for the records with from and to dates less than sysdate */
702 /*IF to_date(to_char(p_upd_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
703 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
704 p_msg_name => G_PAST_RECORDS);
705 x_return_status := Okl_Api.G_RET_STS_ERROR;
706 RAISE G_EXCEPTION_HALT_PROCESSING;
707 END IF;
708 */
709 /* if the start date is in the past, the start date cannot be
710 modified */
711 /*IF to_date(to_char(p_upd_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
712 to_date(to_char(p_db_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate THEN
713 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
714 p_msg_name => G_NOT_ALLOWED,
715 p_token1 => G_COL_NAME_TOKEN,
716 p_token1_value => 'START_DATE');
717 x_return_status := Okl_Api.G_RET_STS_ERROR;
718 RAISE G_EXCEPTION_HALT_PROCESSING;
719 END IF;
720 */
721 IF p_upd_optv_rec.from_date <> Okl_Api.G_MISS_DATE OR
722 p_upd_optv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
723 Check_Constraints(p_optv_rec => l_optv_rec,
724 x_return_status => l_return_status,
725 x_valid => l_valid);
726 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
727 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
728 RAISE G_EXCEPTION_HALT_PROCESSING;
729 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
730 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
731 l_valid <> TRUE) THEN
732 x_return_status := Okl_Api.G_RET_STS_ERROR;
733 RAISE G_EXCEPTION_HALT_PROCESSING;
734 END IF;
735 END IF;
736
737 EXCEPTION
738 WHEN G_EXCEPTION_HALT_PROCESSING THEN
739 -- no processing necessary; validation can continue
740 -- with the next column
741 NULL;
742
743 WHEN OTHERS THEN
744 -- store SQL error message on message stack for caller
745 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
746 p_msg_name => G_UNEXPECTED_ERROR,
747 p_token1 => G_SQLCODE_TOKEN,
748 p_token1_value => SQLCODE,
749 p_token2 => G_SQLERRM_TOKEN,
750 p_token2_value => SQLERRM );
751 -- notify caller of an UNEXPECTED error
752 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
753
754 END check_updates;
755
756 ---------------------------------------------------------------------------
757 -- PROCEDURE determine_action for: OKL_OPTIONS_V
758 -- This function helps in determining the various checks to be performed
759 -- for the new/updated record and also helps in determining whether a new
760 -- version is required or not
761 ---------------------------------------------------------------------------
762 FUNCTION determine_action (
763 p_upd_optv_rec IN optv_rec_type,
764 p_db_optv_rec IN optv_rec_type,
765 p_date IN DATE
766 ) RETURN VARCHAR2 IS
767 l_action VARCHAR2(1);
768 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
769 BEGIN
770 /* Scenario 1: Only description and/or descriptive flexfield changes */
771 IF p_upd_optv_rec.from_date = Okl_Api.G_MISS_DATE AND
772 p_upd_optv_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
773 l_action := '1';
774 /* Scenario 2: Changing the dates */
775 ELSE
776 l_action := '2';
777 END IF;
778 RETURN(l_action);
779 END determine_action;
780
781 ---------------------------------------------------------------------------
782 -- PROCEDURE copy_update_constraints for: OKL_OPTIONS_V
783 ---------------------------------------------------------------------------
784 PROCEDURE copy_update_constraints (p_api_version IN NUMBER,
785 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
786 p_upd_optv_rec IN optv_rec_type,
787 x_return_status OUT NOCOPY VARCHAR2,
788 x_msg_count OUT NOCOPY NUMBER,
789 x_msg_data OUT NOCOPY VARCHAR2
790 ) IS
791 l_upd_optv_rec optv_rec_type; /* input copy */
792 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
793 l_ove_count NUMBER := 0;
794 l_ovev_tbl ovev_tbl_type;
795 l_out_ovev_tbl ovev_tbl_type;
796
797 BEGIN
798 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
799 l_upd_optv_rec := p_upd_optv_rec;
800
801 /* Get Option Values */
802 get_opt_values(p_upd_optv_rec => l_upd_optv_rec,
803 x_return_status => l_return_status,
804 x_count => l_ove_count,
805 x_ovev_tbl => l_ovev_tbl);
806
807 IF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
808 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
809 RAISE G_EXCEPTION_HALT_PROCESSING;
810 END IF;
811
812 IF l_ove_count > 0 THEN
813 Okl_Options_Pub.update_option_values(p_api_version => p_api_version,
814 p_init_msg_list => p_init_msg_list,
815 x_return_status => l_return_status,
816 x_msg_count => x_msg_count,
817 x_msg_data => x_msg_data,
818 p_ovev_tbl => l_ovev_tbl,
819 x_ovev_tbl => l_out_ovev_tbl);
820 END IF;
821
822 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
823 x_return_status := Okl_Api.G_RET_STS_ERROR;
824 RAISE G_EXCEPTION_HALT_PROCESSING;
825 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
826 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
827 RAISE G_EXCEPTION_HALT_PROCESSING;
828 END IF;
829
830 EXCEPTION
831 WHEN G_EXCEPTION_HALT_PROCESSING THEN
832 -- no processing necessary; validation can continue
833 -- with the next column
834 NULL;
835
836 WHEN OTHERS THEN
837 -- store SQL error message on message stack
838 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
839 p_msg_name => G_UNEXPECTED_ERROR,
840 p_token1 => G_SQLCODE_TOKEN,
841 p_token1_value => SQLCODE,
842 p_token2 => G_SQLERRM_TOKEN,
843 p_token2_value => SQLERRM );
844 -- notify UNEXPECTED error for calling API.
845 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
846
847 END copy_update_constraints;
848
849 ---------------------------------------------------------------------------
850 -- PROCEDURE insert_options for: OKL_OPTIONS_V
851 ---------------------------------------------------------------------------
852 PROCEDURE insert_options(p_api_version IN NUMBER,
853 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
854 x_return_status OUT NOCOPY VARCHAR2,
855 x_msg_count OUT NOCOPY NUMBER,
856 x_msg_data OUT NOCOPY VARCHAR2,
857 p_optv_rec IN optv_rec_type,
858 x_optv_rec OUT NOCOPY optv_rec_type
859 ) IS
860 CURSOR okl_options_unique (p_unique OKL_OPTIONS_V.NAME%TYPE) IS
861 SELECT '1'
862 FROM OKL_OPTIONS_V
863 WHERE OKL_OPTIONS_V.NAME = p_unique;
864
865 l_unique_key OKL_OPTIONS_V.NAME%TYPE;
866 l_token_1 VARCHAR2(1999);
867 l_api_version CONSTANT NUMBER := 1;
868 l_api_name CONSTANT VARCHAR2(30) := 'insert_options';
869 l_valid BOOLEAN := TRUE;
870 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
871 l_optv_rec optv_rec_type;
872 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
873 BEGIN
874 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
875 l_optv_rec := p_optv_rec;
876
877 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
878 p_pkg_name => G_PKG_NAME,
879 p_init_msg_list => p_init_msg_list,
880 l_api_version => l_api_version,
881 p_api_version => p_api_version,
882 p_api_type => '_PVT',
883 x_return_status => l_return_status);
884 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
885 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
886 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
887 RAISE Okl_Api.G_EXCEPTION_ERROR;
888 END IF;
889
890 --- Validate all non-missing attributes (Item Level Validation)
891 l_return_status := Validate_Attributes(l_optv_rec);
892 --- If any errors happen abort API
893 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
894 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
895 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
896 RAISE Okl_Api.G_EXCEPTION_ERROR;
897 END IF;
898
899 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_SERCH',
900 p_attribute_code => 'OKL_OPTIONS');
901
902 --moved from simle api to fix error messages
903 OPEN okl_options_unique (Okl_Accounting_Util.okl_upper(p_optv_rec.name));
904 FETCH okl_options_unique INTO l_unique_key;
905 IF okl_options_unique%FOUND THEN
906 Okl_Api.set_message('OKL','OKL_NOT_UNIQUE', 'OKL_TABLE_NAME',l_token_1);
907 RAISE Okl_Api.G_EXCEPTION_ERROR;
908 ELSE
909 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
910 END IF;
911 CLOSE okl_options_unique;
912
913 /* check for the records with start or end dates less than sysdate */
914 /*IF to_date(to_char(l_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
915 to_date(to_char(l_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
916 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
917 p_msg_name => G_PAST_RECORDS);
918 RAISE Okl_Api.G_EXCEPTION_ERROR;
919 END IF;
920 */
921 /* public api to create options */
922 Okl_Options_Pub.create_options(p_api_version => p_api_version,
923 p_init_msg_list => p_init_msg_list,
924 x_return_status => l_return_status,
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data,
927 p_optv_rec => l_optv_rec,
928 x_optv_rec => x_optv_rec);
929
930 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
931 RAISE Okl_Api.G_EXCEPTION_ERROR;
932 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
933 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
934 END IF;
935
936 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
937 x_msg_data => x_msg_data);
938 EXCEPTION
939 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
940 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
941 p_pkg_name => G_PKG_NAME,
942 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
943 x_msg_count => x_msg_count,
944 x_msg_data => x_msg_data,
945 p_api_type => '_PVT');
946 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
947 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
948 p_pkg_name => G_PKG_NAME,
949 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
950 x_msg_count => x_msg_count,
951 x_msg_data => x_msg_data,
952 p_api_type => '_PVT');
953 WHEN OTHERS THEN
954 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
955 p_pkg_name => G_PKG_NAME,
956 p_exc_name => 'OTHERS',
957 x_msg_count => x_msg_count,
958 x_msg_data => x_msg_data,
959 p_api_type => '_PVT');
960 IF (okl_options_unique%ISOPEN) THEN
961 CLOSE okl_options_unique;
962 END IF;
963
964 END insert_options;
965
966 ---------------------------------------------------------------------------
967 -- PROCEDURE update_options for: OKL_OPTIONS_V
968 ---------------------------------------------------------------------------
969 PROCEDURE update_options(p_api_version IN NUMBER,
970 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
971 x_return_status OUT NOCOPY VARCHAR2,
972 x_msg_count OUT NOCOPY NUMBER,
973 x_msg_data OUT NOCOPY VARCHAR2,
974 p_optv_rec IN optv_rec_type,
975 x_optv_rec OUT NOCOPY optv_rec_type
976 ) IS
977 l_api_version CONSTANT NUMBER := 1;
978 l_api_name CONSTANT VARCHAR2(30) := 'update_options';
979 l_no_data_found BOOLEAN := TRUE;
980 l_valid BOOLEAN := TRUE;
981 l_db_optv_rec optv_rec_type; /* database copy */
982 l_upd_optv_rec optv_rec_type; /* input copy */
983 l_optv_rec optv_rec_type; /* latest with the retained changes */
984 l_tmp_optv_rec optv_rec_type; /* for any other purposes */
985 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
986 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
987 l_action VARCHAR2(1);
988 BEGIN
989 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
990 l_upd_optv_rec := p_optv_rec;
991
992 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
993 p_pkg_name => G_PKG_NAME,
994 p_init_msg_list => p_init_msg_list,
995 l_api_version => l_api_version,
996 p_api_version => p_api_version,
997 p_api_type => '_PVT',
998 x_return_status => l_return_status);
999 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1000 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1001 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1002 RAISE Okl_Api.G_EXCEPTION_ERROR;
1003 END IF;
1004
1005 /* fetch old details from the database */
1006 get_rec(p_optv_rec => l_upd_optv_rec,
1007 x_return_status => l_return_status,
1008 x_no_data_found => l_no_data_found,
1009 x_optv_rec => l_db_optv_rec);
1010 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
1011 l_no_data_found = TRUE THEN
1012 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1013 END IF;
1014
1015 IF l_upd_optv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
1016 /* update constraints */
1017 copy_update_constraints(p_api_version => p_api_version,
1018 p_init_msg_list => p_init_msg_list,
1019 p_upd_optv_rec => l_upd_optv_rec,
1020 x_return_status => l_return_status,
1021 x_msg_count => x_msg_count,
1022 x_msg_data => x_msg_data);
1023
1024 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1025 RAISE Okl_Api.G_EXCEPTION_ERROR;
1026 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1027 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1028 END IF;
1029
1030 END IF;
1031
1032 /* to reorganize the input accordingly */
1033 reorganize_inputs(p_upd_optv_rec => l_upd_optv_rec,
1034 p_db_optv_rec => l_db_optv_rec);
1035
1036 /* check for past records */
1037 /*IF to_date(to_char(l_db_optv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate AND
1038 to_date(to_char(l_db_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
1039 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1040 p_msg_name => G_PAST_RECORDS);
1041 x_return_status := Okl_Api.G_RET_STS_ERROR;
1042 RAISE Okl_Api.G_EXCEPTION_ERROR;
1043 END IF;
1044 */
1045
1046
1047
1048 IF (l_upd_optv_rec.TO_DATE = Okl_Api.G_MISS_DATE) then
1049 l_upd_optv_rec.TO_DATE := p_optv_rec.to_date;
1050 end if;
1051
1052 IF (l_upd_optv_rec.from_DATE = Okl_Api.G_MISS_DATE) then
1053 l_upd_optv_rec.from_DATE := p_optv_rec.from_date;
1054 end if;
1055
1056 /* To Check end date is > from_date */
1057 IF (l_upd_optv_rec.TO_DATE IS NOT NULL) AND (l_upd_optv_rec.TO_DATE < l_upd_optv_rec.from_date) THEN
1058 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
1059 ,p_msg_name => Okl_Opt_Pvt.g_to_date_error
1060 ,p_token1 => Okl_Opt_Pvt.g_col_name_token
1061 ,p_token1_value => 'to_date');
1062 x_return_status := Okl_Api.G_RET_STS_ERROR;
1063 RAISE Okl_Api.G_EXCEPTION_ERROR;
1064 END IF;
1065
1066 /* determine how the processing to be done */
1067 l_action := determine_action(p_upd_optv_rec => l_upd_optv_rec,
1068 p_db_optv_rec => l_db_optv_rec,
1069 p_date => l_sysdate);
1070
1071 /* Scenario 1: only changing description and descriptive flexfields */
1072 IF l_action = '1' THEN
1073 /* public api to update options */
1074 Okl_Options_Pub.update_options(p_api_version => p_api_version,
1075 p_init_msg_list => p_init_msg_list,
1076 x_return_status => l_return_status,
1077 x_msg_count => x_msg_count,
1078 x_msg_data => x_msg_data,
1079 p_optv_rec => l_upd_optv_rec,
1080 x_optv_rec => x_optv_rec);
1081 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1082 RAISE Okl_Api.G_EXCEPTION_ERROR;
1083 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1084 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1085 END IF;
1086
1087 /* Scenario 2: changing the dates */
1088 ELSIF l_action = '2' THEN
1089 /* create a temporary record with all relevant details from db and upd records */
1090 l_optv_rec := defaults_to_actuals(p_upd_optv_rec => l_upd_optv_rec,
1091 p_db_optv_rec => l_db_optv_rec);
1092
1093 check_updates(p_upd_optv_rec => l_upd_optv_rec,
1094 p_db_optv_rec => l_db_optv_rec,
1095 p_optv_rec => l_optv_rec,
1096 x_return_status => l_return_status,
1097 x_msg_data => x_msg_data);
1098 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1099 RAISE Okl_Api.G_EXCEPTION_ERROR;
1100 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1101 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1102 END IF;
1103
1104 /* public api to update options */
1105 Okl_Options_Pub.update_options(p_api_version => p_api_version,
1106 p_init_msg_list => p_init_msg_list,
1107 x_return_status => l_return_status,
1108 x_msg_count => x_msg_count,
1109 x_msg_data => x_msg_data,
1110 p_optv_rec => l_upd_optv_rec,
1111 x_optv_rec => x_optv_rec);
1112 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1113 RAISE Okl_Api.G_EXCEPTION_ERROR;
1114 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1115 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1116 END IF;
1117 END IF;
1118
1119 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1120 x_msg_data => x_msg_data);
1121 EXCEPTION
1122 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1123 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1124 p_pkg_name => G_PKG_NAME,
1125 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1126 x_msg_count => x_msg_count,
1127 x_msg_data => x_msg_data,
1128 p_api_type => '_PVT');
1129 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1130 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1131 p_pkg_name => G_PKG_NAME,
1132 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1133 x_msg_count => x_msg_count,
1134 x_msg_data => x_msg_data,
1135 p_api_type => '_PVT');
1136 WHEN OTHERS THEN
1137 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1138 p_pkg_name => G_PKG_NAME,
1139 p_exc_name => 'OTHERS',
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data,
1142 p_api_type => '_PVT');
1143
1144 END update_options;
1145
1146 END Okl_Setupoptions_Pvt;