[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPOPTVALUES_PVT
Source
1 PACKAGE BODY Okl_Setupoptvalues_Pvt AS
2 /* $Header: OKLRSOVB.pls 115.15 2003/10/15 23:26:21 sgorantl noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE get_rec for: OKL_OPT_VALUES_V
5 ---------------------------------------------------------------------------
6 PROCEDURE get_rec (
7 p_ovev_rec IN ovev_rec_type,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_no_data_found OUT NOCOPY BOOLEAN,
10 x_ovev_rec OUT NOCOPY ovev_rec_type
11 ) IS
12 CURSOR okl_ovev_pk_csr (p_id IN NUMBER) IS
13 SELECT
14 ID,
15 OBJECT_VERSION_NUMBER,
16 OPT_ID,
17 VALUE,
18 NVL(DESCRIPTION,Okl_Api.G_MISS_CHAR) DESCRIPTION,
19 FROM_DATE,
20 NVL(TO_DATE,Okl_Api.G_MISS_DATE) TO_DATE,
21 CREATED_BY,
22 CREATION_DATE,
23 LAST_UPDATED_BY,
24 LAST_UPDATE_DATE,
25 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
26 FROM Okl_Opt_Values_V
27 WHERE okl_opt_values_v.id = p_id;
28 l_okl_ovev_pk okl_ovev_pk_csr%ROWTYPE;
29 l_ovev_rec ovev_rec_type;
30 BEGIN
31 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
32 x_no_data_found := TRUE;
33
34 -- Get current database values
35 OPEN okl_ovev_pk_csr (p_ovev_rec.id);
36 FETCH okl_ovev_pk_csr INTO
37 l_ovev_rec.ID,
38 l_ovev_rec.OBJECT_VERSION_NUMBER,
39 l_ovev_rec.OPT_ID,
40 l_ovev_rec.VALUE,
41 l_ovev_rec.DESCRIPTION,
42 l_ovev_rec.FROM_DATE,
43 l_ovev_rec.TO_DATE,
44 l_ovev_rec.CREATED_BY,
45 l_ovev_rec.CREATION_DATE,
46 l_ovev_rec.LAST_UPDATED_BY,
47 l_ovev_rec.LAST_UPDATE_DATE,
48 l_ovev_rec.LAST_UPDATE_LOGIN;
49 x_no_data_found := okl_ovev_pk_csr%NOTFOUND;
50 CLOSE okl_ovev_pk_csr;
51 x_ovev_rec := l_ovev_rec;
52 EXCEPTION
53 WHEN OTHERS THEN
54 -- store SQL error message on message stack
55 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
56 p_msg_name => G_UNEXPECTED_ERROR,
57 p_token1 => G_SQLCODE_TOKEN,
58 p_token1_value => SQLCODE,
59 p_token2 => G_SQLERRM_TOKEN,
60 p_token2_value => SQLERRM);
61 -- notify UNEXPECTED error for calling API.
62 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
63
64 IF (okl_ovev_pk_csr%ISOPEN) THEN
65 CLOSE okl_ovev_pk_csr;
66 END IF;
67
68 END get_rec;
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE get_rul_rec for: OKC_RuleS_V
72 ---------------------------------------------------------------------------
73 PROCEDURE get_rul_rec (
74 p_rulv_rec IN rulv_rec_type,
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_no_data_found OUT NOCOPY BOOLEAN,
77 x_rulv_rec OUT NOCOPY rulv_rec_type
78 ) IS
79 CURSOR okl_rulv_pk_csr (p_id IN NUMBER) IS
80 SELECT
81 ID,
82 RGP_ID,
83 NVL(OBJECT1_ID1, Okl_Api.G_MISS_CHAR) OBJECT1_ID1,
84 NVL(OBJECT2_ID1, Okl_Api.G_MISS_CHAR) OBJECT2_ID1,
85 NVL(OBJECT3_ID1, Okl_Api.G_MISS_CHAR) OBJECT3_ID1,
86 NVL(OBJECT1_ID2, Okl_Api.G_MISS_CHAR) OBJECT1_ID2,
87 NVL(OBJECT2_ID2, Okl_Api.G_MISS_CHAR) OBJECT2_ID2,
88 NVL(OBJECT3_ID2, Okl_Api.G_MISS_CHAR) OBJECT3_ID2,
89 NVL(JTOT_OBJECT1_CODE, Okl_Api.G_MISS_CHAR) JTOT_OBJECT1_CODE,
90 NVL(JTOT_OBJECT2_CODE, Okl_Api.G_MISS_CHAR) JTOT_OBJECT2_CODE,
91 NVL(JTOT_OBJECT3_CODE, Okl_Api.G_MISS_CHAR) JTOT_OBJECT3_CODE,
92 NVL(DNZ_CHR_ID, Okl_Api.G_MISS_NUM) DNZ_CHR_ID,
93 STD_TEMPLATE_YN,
94 --TEMPLATE_YN,
95 -- removing dependincies from okc_rules_tl
96 --COMMENTS,
97 WARN_YN,
98 NVL(PRIORITY, Okl_Api.G_MISS_NUM) PRIORITY,
99 OBJECT_VERSION_NUMBER,
100 NVL(ATTRIBUTE_CATEGORY, Okl_Api.G_MISS_CHAR) ATTRIBUTE_CATEGORY,
101 NVL(ATTRIBUTE1, Okl_Api.G_MISS_CHAR) ATTRIBUTE1,
102 NVL(ATTRIBUTE2, Okl_Api.G_MISS_CHAR) ATTRIBUTE2,
103 NVL(ATTRIBUTE3, Okl_Api.G_MISS_CHAR) ATTRIBUTE3,
104 NVL(ATTRIBUTE4, Okl_Api.G_MISS_CHAR) ATTRIBUTE4,
105 NVL(ATTRIBUTE5, Okl_Api.G_MISS_CHAR) ATTRIBUTE5,
106 NVL(ATTRIBUTE6, Okl_Api.G_MISS_CHAR) ATTRIBUTE6,
107 NVL(ATTRIBUTE7, Okl_Api.G_MISS_CHAR) ATTRIBUTE7,
108 NVL(ATTRIBUTE8, Okl_Api.G_MISS_CHAR) ATTRIBUTE8,
109 NVL(ATTRIBUTE9, Okl_Api.G_MISS_CHAR) ATTRIBUTE9,
110 NVL(ATTRIBUTE10, Okl_Api.G_MISS_CHAR) ATTRIBUTE10,
111 NVL(ATTRIBUTE11, Okl_Api.G_MISS_CHAR) ATTRIBUTE11,
112 NVL(ATTRIBUTE12, Okl_Api.G_MISS_CHAR) ATTRIBUTE12,
113 NVL(ATTRIBUTE13, Okl_Api.G_MISS_CHAR) ATTRIBUTE13,
114 NVL(ATTRIBUTE14, Okl_Api.G_MISS_CHAR) ATTRIBUTE14,
115 NVL(ATTRIBUTE15, Okl_Api.G_MISS_CHAR) ATTRIBUTE15,
116 RULE_INFORMATION_CATEGORY,
117 NVL(RULE_INFORMATION1, Okl_Api.G_MISS_CHAR) RULE_INFORMATION1,
118 NVL(RULE_INFORMATION2, Okl_Api.G_MISS_CHAR) RULE_INFORMATION2,
119 NVL(RULE_INFORMATION3, Okl_Api.G_MISS_CHAR) RULE_INFORMATION3,
120 NVL(RULE_INFORMATION4, Okl_Api.G_MISS_CHAR) RULE_INFORMATION4,
121 NVL(RULE_INFORMATION5, Okl_Api.G_MISS_CHAR) RULE_INFORMATION5,
122 NVL(RULE_INFORMATION6, Okl_Api.G_MISS_CHAR) RULE_INFORMATION6,
123 NVL(RULE_INFORMATION7, Okl_Api.G_MISS_CHAR) RULE_INFORMATION7,
124 NVL(RULE_INFORMATION8, Okl_Api.G_MISS_CHAR) RULE_INFORMATION8,
125 NVL(RULE_INFORMATION9, Okl_Api.G_MISS_CHAR) RULE_INFORMATION9,
126 NVL(RULE_INFORMATION10, Okl_Api.G_MISS_CHAR) RULE_INFORMATION10,
127 NVL(RULE_INFORMATION11, Okl_Api.G_MISS_CHAR) RULE_INFORMATION11,
128 NVL(RULE_INFORMATION12, Okl_Api.G_MISS_CHAR) RULE_INFORMATION12,
129 NVL(RULE_INFORMATION13, Okl_Api.G_MISS_CHAR) RULE_INFORMATION13,
130 NVL(RULE_INFORMATION14, Okl_Api.G_MISS_CHAR) RULE_INFORMATION14,
131 NVL(RULE_INFORMATION15, Okl_Api.G_MISS_CHAR) RULE_INFORMATION15,
132 CREATED_BY,
133 CREATION_DATE,
134 LAST_UPDATED_BY,
135 LAST_UPDATE_DATE,
136 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
137 -- removed references to okc_rules_tl
138 FROM Okc_Rules_b
139 WHERE okc_rules_b.id = p_id;
140 l_okl_rulv_pk okl_rulv_pk_csr%ROWTYPE;
141 l_rulv_rec rulv_rec_type;
142 BEGIN
143 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
144 x_no_data_found := TRUE;
145
146 -- Get current database values
147 OPEN okl_rulv_pk_csr (p_rulv_rec.id);
148 FETCH okl_rulv_pk_csr INTO
149 l_rulv_rec.ID,
150 l_rulv_rec.RGP_ID,
151 l_rulv_rec.OBJECT1_ID1,
152 l_rulv_rec.OBJECT2_ID1,
153 l_rulv_rec.OBJECT3_ID1,
154 l_rulv_rec.OBJECT1_ID2,
155 l_rulv_rec.OBJECT2_ID2,
156 l_rulv_rec.OBJECT3_ID2,
157 l_rulv_rec.JTOT_OBJECT1_CODE,
158 l_rulv_rec.JTOT_OBJECT2_CODE,
159 l_rulv_rec.JTOT_OBJECT3_CODE,
160 l_rulv_rec.DNZ_CHR_ID,
161 l_rulv_rec.STD_TEMPLATE_YN,
162 --l_rulv_rec.TEMPLATE_YN,
163 -- removing dependincies from okc_rules_tl
164 --l_rulv_rec.COMMENTS,
165 l_rulv_rec.WARN_YN,
166 l_rulv_rec.PRIORITY,
167 l_rulv_rec.OBJECT_VERSION_NUMBER,
168 l_rulv_rec.ATTRIBUTE_CATEGORY,
169 l_rulv_rec.ATTRIBUTE1,
170 l_rulv_rec.ATTRIBUTE2,
171 l_rulv_rec.ATTRIBUTE3,
172 l_rulv_rec.ATTRIBUTE4,
173 l_rulv_rec.ATTRIBUTE5,
174 l_rulv_rec.ATTRIBUTE6,
175 l_rulv_rec.ATTRIBUTE7,
176 l_rulv_rec.ATTRIBUTE8,
177 l_rulv_rec.ATTRIBUTE9,
178 l_rulv_rec.ATTRIBUTE10,
179 l_rulv_rec.ATTRIBUTE11,
180 l_rulv_rec.ATTRIBUTE12,
181 l_rulv_rec.ATTRIBUTE13,
182 l_rulv_rec.ATTRIBUTE14,
183 l_rulv_rec.ATTRIBUTE15,
184 l_rulv_rec.RULE_INFORMATION_CATEGORY,
185 l_rulv_rec.RULE_INFORMATION1,
186 l_rulv_rec.RULE_INFORMATION2,
187 l_rulv_rec.RULE_INFORMATION3,
188 l_rulv_rec.RULE_INFORMATION4,
189 l_rulv_rec.RULE_INFORMATION5,
190 l_rulv_rec.RULE_INFORMATION6,
191 l_rulv_rec.RULE_INFORMATION7,
192 l_rulv_rec.RULE_INFORMATION8,
193 l_rulv_rec.RULE_INFORMATION9,
194 l_rulv_rec.RULE_INFORMATION10,
195 l_rulv_rec.RULE_INFORMATION11,
196 l_rulv_rec.RULE_INFORMATION12,
197 l_rulv_rec.RULE_INFORMATION13,
198 l_rulv_rec.RULE_INFORMATION14,
199 l_rulv_rec.RULE_INFORMATION15,
200 l_rulv_rec.CREATED_BY,
201 l_rulv_rec.CREATION_DATE,
202 l_rulv_rec.LAST_UPDATED_BY,
203 l_rulv_rec.LAST_UPDATE_DATE,
204 l_rulv_rec.LAST_UPDATE_LOGIN;
205 x_no_data_found := okl_rulv_pk_csr%NOTFOUND;
206 CLOSE okl_rulv_pk_csr;
207 x_rulv_rec := l_rulv_rec;
208 EXCEPTION
209 WHEN OTHERS THEN
210 -- store SQL error message on message stack
211 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
212 p_msg_name => G_UNEXPECTED_ERROR,
213 p_token1 => G_SQLCODE_TOKEN,
214 p_token1_value => SQLCODE,
215 p_token2 => G_SQLERRM_TOKEN,
216 p_token2_value => SQLERRM);
217 -- notify UNEXPECTED error for calling API.
218 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
219
220 IF (okl_rulv_pk_csr%ISOPEN) THEN
221 CLOSE okl_rulv_pk_csr;
222 END IF;
223
224 END get_rul_rec;
225
226
227 ---------------------------------------------------------------------------
228 -- PROCEDURE default_parent_dates for: OKL_OPT_VALUES_V
229 ---------------------------------------------------------------------------
230
231 PROCEDURE default_parent_dates(
232 p_ovev_rec IN ovev_rec_type,
233 x_no_data_found OUT NOCOPY BOOLEAN,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_optv_rec OUT NOCOPY optv_rec_type
236 ) IS
237 CURSOR okl_optv_pk_csr (p_opt_id IN NUMBER) IS
238 SELECT FROM_DATE,
239 TO_DATE
240 FROM Okl_Options_V opt
241 WHERE opt.id = p_opt_id;
242 l_okl_optv_pk okl_optv_pk_csr%ROWTYPE;
243 l_optv_rec optv_rec_type;
244 BEGIN
245 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
246 x_no_data_found := TRUE;
247 -- Get current database values
248 OPEN okl_optv_pk_csr (p_ovev_rec.opt_id);
249 FETCH okl_optv_pk_csr INTO
250 l_optv_rec.FROM_DATE,
251 l_optv_rec.TO_DATE;
252 x_no_data_found := okl_optv_pk_csr%NOTFOUND;
253 CLOSE okl_optv_pk_csr;
254 x_optv_rec := l_optv_rec;
255 EXCEPTION
256 WHEN OTHERS THEN
257 -- store SQL error message on message stack
258 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
259 p_msg_name => G_UNEXPECTED_ERROR,
260
261 p_token1 => G_SQLCODE_TOKEN,
262 p_token1_value => SQLCODE,
263 p_token2 => G_SQLERRM_TOKEN,
264 p_token2_value => SQLERRM);
265 -- notify UNEXPECTED error for calling API.
266 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
267
268 IF (okl_optv_pk_csr%ISOPEN) THEN
269 CLOSE okl_optv_pk_csr;
270 END IF;
271
272 END default_parent_dates;
273
274 ---------------------------------------------------------------------------
275 -- PROCEDURE check_constraints for: OKL_OPT_VALUES_V
276 -- To verify whether the dates modification is valid in relation with
277 -- the attached Option Value Rules, Options and Product
278 ---------------------------------------------------------------------------
279 PROCEDURE Check_Constraints (
280 p_ovev_rec IN OUT NOCOPY ovev_rec_type,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_valid OUT NOCOPY BOOLEAN
283 ) IS
284 CURSOR okl_ove_opt_fk_csr (p_opt_id IN Okl_Options_V.id%TYPE,
285 p_from_date IN Okl_Opt_Values_V.from_date%TYPE,
286 p_to_date IN Okl_Opt_Values_V.TO_DATE%TYPE
287 ) IS
288 SELECT '1'
289 FROM Okl_Options_V opt
290 WHERE opt.ID = p_opt_id
291 AND ((opt.FROM_DATE > p_from_date OR
292 p_from_date > NVL(opt.TO_DATE,p_from_date)) OR
293 NVL(opt.TO_DATE, p_to_date) < p_to_date);
294
295 CURSOR okl_ove_ovd_fk_csr (p_ove_id IN Okl_Opt_Values_V.id%TYPE,
296 p_from_date IN Okl_Opt_Values_V.from_date%TYPE,
297 p_to_date IN Okl_Opt_Values_V.TO_DATE%TYPE
298 ) IS
299 SELECT '1'
300 FROM Okl_Opv_Rules_V ovd,
301 Okl_Opt_Rules_V orl,
302 Okl_Lse_Scs_Rules_V lsr
303 WHERE ovd.OVE_ID = p_ove_id
304 AND orl.ID = ovd.ORL_ID
305 AND ((orl.LRG_LSE_ID IS NOT NULL
306 AND lsr.LSE_ID = orl.LRG_LSE_ID
307 AND lsr.SRD_ID = orl.LRG_SRD_ID)
308 OR
309 (orl.LRG_LSE_ID IS NULL
310 AND lsr.LSE_ID IS NULL
311 AND lsr.SRD_ID = orl.SRD_ID_FOR))
312 AND lsr.RULE_GROUP = orl.RGR_RGD_CODE
313 AND lsr.RULE = orl.RGR_RDF_CODE
314 AND ((lsr.START_DATE > p_from_date OR
315 P_from_date > NVL(lsr.END_DATE,p_from_date)) OR
316 NVL(lsr.END_DATE, p_to_date) < p_to_date);
317
318 CURSOR okl_ove_pov_fk_csr (p_ove_id IN Okl_Opt_Values_V.ID%TYPE,
319 p_from_date IN Okl_Opt_Values_V.from_date%TYPE,
320 p_to_date IN Okl_Opt_Values_V.TO_DATE%TYPE
321 ) IS
322 SELECT '1'
323 FROM Okl_Pdt_Opt_Vals_V pov
324 WHERE pov.OVE_ID = p_ove_id
325 AND (pov.FROM_DATE < p_from_date OR
326 NVL(pov.TO_DATE, pov.FROM_DATE) > p_to_date);
327
328 CURSOR okl_ove_values_unique (p_unique1 OKL_OPT_VALUES.OPT_ID%TYPE,
329 p_unique2 OKL_OPT_VALUES.VALUE%TYPE
330 ) IS
331 SELECT '1'
332 FROM OKL_OPT_VALUES_V
333 WHERE OKL_OPT_VALUES_V.OPT_ID = p_unique1 AND
334 OKL_OPT_VALUES_V.VALUE = p_unique2;
335
336 l_unique_key OKL_OPT_VALUES_V.OPT_ID%TYPE;
337 l_ovev_rec ovev_rec_type;
338 l_token_1 VARCHAR2(1999);
339 l_token_2 VARCHAR2(1999);
340 l_token_3 VARCHAR2(1999);
341 l_token_4 VARCHAR2(1999);
342 l_check VARCHAR2(1) := '?';
343 l_row_not_found BOOLEAN := FALSE;
344 l_to_date okl_opt_values_v.TO_DATE%TYPE;
345 BEGIN
346 x_valid := TRUE;
347 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
348
349 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_SERCH',
350 p_attribute_code => 'OKL_OPTION_VALUES');
351
352 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_SERCH',
353 p_attribute_code => 'OKL_OPTIONS');
354
355 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_RULE_SERCH',
356 p_attribute_code => 'OKL_OPTION_RULES');
357
358 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_OPT_VAL_SUMRY',
359 p_attribute_code => 'OKL_PRODUCT_OPTION_VALUES');
360
361
362 -- Fix for g_miss_date
363 IF p_ovev_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
364 l_to_date := NULL;
365 ELSE
366 l_to_date := p_ovev_rec.TO_DATE;
367 END IF;
368
369 IF p_ovev_rec.id = Okl_Api.G_MISS_NUM THEN
370 p_ovev_rec.value := Okl_Accounting_Util.okl_upper(p_ovev_rec.value);
371 OPEN okl_ove_values_unique (p_ovev_rec.opt_id, p_ovev_rec.value);
372 FETCH okl_ove_values_unique INTO l_unique_key;
373 IF okl_ove_values_unique%FOUND THEN
374 Okl_Api.set_message(G_APP_NAME,'OKL_NOT_UNIQUE', 'OKL_TABLE_NAME',l_token_1);
375 x_valid := FALSE;
376 x_return_status := Okl_Api.G_RET_STS_ERROR;
377 RAISE G_EXCEPTION_HALT_PROCESSING;
378 END IF;
379 CLOSE okl_ove_values_unique;
380 END IF;
381
382
383 -- Check for option values dates
384 OPEN okl_ove_opt_fk_csr (p_ovev_rec.opt_id,
385 p_ovev_rec.from_date,
386 l_to_date);
387 FETCH okl_ove_opt_fk_csr INTO l_check;
388 l_row_not_found := okl_ove_opt_fk_csr%NOTFOUND;
389 CLOSE okl_ove_opt_fk_csr;
390
391 IF l_row_not_found = FALSE THEN
392 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
393 p_msg_name => G_DATES_MISMATCH,
394 p_token1 => G_PARENT_TABLE_TOKEN,
395 p_token1_value => l_token_2,
396 p_token2 => G_CHILD_TABLE_TOKEN,
397 p_token2_value => l_token_1);
398 x_valid := FALSE;
399 x_return_status := Okl_Api.G_RET_STS_ERROR;
400 RAISE G_EXCEPTION_HALT_PROCESSING;
401 END IF;
402
403 IF p_ovev_rec.id <> Okl_Api.G_MISS_NUM THEN
404 -- Check for option rules dates
405 OPEN okl_ove_ovd_fk_csr (p_ovev_rec.id,
406 p_ovev_rec.from_date,
407 l_to_date);
408 FETCH okl_ove_ovd_fk_csr INTO l_check;
409 l_row_not_found := okl_ove_ovd_fk_csr%NOTFOUND;
410 CLOSE okl_ove_ovd_fk_csr;
411
412 IF l_row_not_found = FALSE THEN
413 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
414 p_msg_name => G_DATES_MISMATCH,
415 p_token1 => G_PARENT_TABLE_TOKEN,
416 p_token1_value => l_token_1,
417 p_token2 => G_CHILD_TABLE_TOKEN,
418 p_token2_value => l_token_3);
419 x_valid := FALSE;
420 x_return_status := Okl_Api.G_RET_STS_ERROR;
421 RAISE G_EXCEPTION_HALT_PROCESSING;
422 END IF;
423
424 -- Check for product dates
425 OPEN okl_ove_pov_fk_csr (p_ovev_rec.id,
426 p_ovev_rec.from_date,
427 l_to_date);
428 FETCH okl_ove_pov_fk_csr INTO l_check;
429 l_row_not_found := okl_ove_pov_fk_csr%NOTFOUND;
430 CLOSE okl_ove_pov_fk_csr;
431
432 IF l_row_not_found = FALSE THEN
433 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
434 p_msg_name => G_DATES_MISMATCH,
435 p_token1 => G_PARENT_TABLE_TOKEN,
436 p_token1_value => l_token_1,
437 p_token2 => G_CHILD_TABLE_TOKEN,
438 p_token2_value => l_token_4);
439 x_valid := FALSE;
440 x_return_status := Okl_Api.G_RET_STS_ERROR;
441 RAISE G_EXCEPTION_HALT_PROCESSING;
442 END IF;
443 END IF;
444
445 EXCEPTION
446 WHEN G_EXCEPTION_HALT_PROCESSING THEN
447 -- no processing necessary; validation can continue
448 -- with the next column
449 NULL;
450
451 WHEN OTHERS THEN
452 -- store SQL error message on message stack
453 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
454 p_msg_name => G_UNEXPECTED_ERROR,
455 p_token1 => G_SQLCODE_TOKEN,
456 p_token1_value => SQLCODE,
457 p_token2 => G_SQLERRM_TOKEN,
458 p_token2_value => SQLERRM);
459 x_valid := FALSE;
460 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
461
462 IF (okl_ove_ovd_fk_csr%ISOPEN) THEN
463 CLOSE okl_ove_ovd_fk_csr;
464 END IF;
465
466 IF (okl_ove_opt_fk_csr%ISOPEN) THEN
467 CLOSE okl_ove_opt_fk_csr;
468 END IF;
469
470 IF (okl_ove_pov_fk_csr%ISOPEN) THEN
471 CLOSE okl_ove_pov_fk_csr;
472 END IF;
473
474 IF (okl_ove_values_unique%ISOPEN) THEN
475 CLOSE okl_ove_values_unique;
476 END IF;
477 END Check_Constraints;
478
479 ---------------------------------------------------------------------------
480 -- PROCEDURE Validate _Value
481 ---------------------------------------------------------------------------
482 -- Start of comments
483 --
484 -- Function Name : Validate _Value
485 -- Description :
486 -- Business Rules :
487 -- Parameters :
488 -- Version : 1.0
489 -- End of comments
490 ---------------------------------------------------------------------------
491
492 PROCEDURE Validate_Value (
493 p_ovev_rec IN OUT NOCOPY ovev_rec_type,
494 x_return_status OUT NOCOPY VARCHAR2
495 ) IS
496 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
497 l_token_1 VARCHAR2(1999);
498 BEGIN
499 -- initialize return status
500 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
501
502 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTVAL_CRUPD','OKL_NAME');
503 IF p_ovev_rec.value = Okc_Api.G_MISS_CHAR OR
504 p_ovev_rec.value IS NULL
505 THEN
506 Okc_Api.set_message(Okl_Ove_Pvt.G_APP_NAME, Okl_Ove_Pvt.G_REQUIRED_VALUE,Okl_Ove_Pvt.G_COL_NAME_TOKEN,l_token_1);
507 x_return_status := Okc_Api.G_RET_STS_ERROR;
508 END IF;
509 p_ovev_rec.value := Okl_Accounting_Util.okl_upper(p_ovev_rec.value);
510 EXCEPTION
511 WHEN OTHERS THEN
512 Okc_Api.set_message(p_app_name =>Okl_Ove_Pvt.G_APP_NAME,
513 p_msg_name =>Okl_Ove_Pvt.G_UNEXPECTED_ERROR,
514 p_token1 =>Okl_Ove_Pvt.G_SQL_SQLCODE_TOKEN,
515 p_token1_value =>SQLCODE,
516 p_token2 =>Okl_Ove_Pvt.G_SQL_SQLERRM_TOKEN,
517 p_token2_value =>SQLERRM);
518 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
519
520 END Validate_Value;
521 ------end of Validate_Value-----------------------------------
522
523 ---------------------------------------------------------------------------
524 -- PROCEDURE Validate _From_Date
525 ---------------------------------------------------------------------------
526 -- Start of comments
527 --
528 -- Function Name : Validate _From_Date
529 -- Description :
530 -- Business Rules :
531 -- Parameters :
532 -- Version : 1.0
533 -- End of comments
534 ---------------------------------------------------------------------------
535
536 PROCEDURE Validate_From_Date(
537 p_ovev_rec IN ovev_rec_type,
538 x_return_status OUT NOCOPY VARCHAR2
539 ) IS
540 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
541 l_token_1 VARCHAR2(999);
542 BEGIN
543 -- initialize return status
544 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
545 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTVAL_CRUPD','OKL_EFFECTIVE_FROM');
546 IF p_ovev_rec.from_date IS NULL OR p_ovev_rec.from_date = Okl_Api.G_MISS_DATE
547 THEN
548 Okl_Api.set_message(Okl_Ove_Pvt.G_APP_NAME, Okl_Ove_Pvt.G_REQUIRED_VALUE,Okl_Ove_Pvt.G_COL_NAME_TOKEN,l_token_1);
549 x_return_status := Okl_Api.G_RET_STS_ERROR;
550 END IF;
551 EXCEPTION
552 WHEN OTHERS THEN
553 Okl_Api.set_message(p_app_name =>Okl_Ove_Pvt.G_APP_NAME,
554 p_msg_name =>Okl_Ove_Pvt.G_UNEXPECTED_ERROR,
555 p_token1 =>Okl_Ove_Pvt.G_SQL_SQLCODE_TOKEN,
556 p_token1_value =>SQLCODE,
557 p_token2 =>Okl_Ove_Pvt.G_SQL_SQLERRM_TOKEN,
558 p_token2_value =>SQLERRM);
559 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
560
561 END Validate_From_Date;
562 ------end of Validate_From_Date-----------------------------------
563
564
565 ---------------------------------------------------------------------------
566 -- FUNCTION Validate _Attribute
567 ---------------------------------------------------------------------------
568 -- Start of comments
569 --
570 -- Function Name : Validate _Attribute
571 -- Description :
572 -- Business Rules :
573 -- Parameters :
574 -- Version : 1.0
575 -- End of comments
576 ---------------------------------------------------------------------------
577
578 FUNCTION Validate_Attributes(
579 p_ovev_rec IN OUT NOCOPY ovev_rec_type
580 ) RETURN VARCHAR IS
581 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
582 l_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
583
584
585 BEGIN
586
587 -----CHECK FOR VALUE----------------------------
588 Validate_Value (p_ovev_rec,x_return_status);
589 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
590 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
591 -- need to leave
592 l_return_status := x_return_status;
593 RAISE G_EXCEPTION_HALT_PROCESSING;
594 ELSE
595 l_return_status := x_return_status;
596 END IF;
597
598 END IF;
599
600 -----CHECK FOR FROM_DATE----------------------------
601 Validate_From_Date (p_ovev_rec,x_return_status);
602 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
603 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
604 -- need to leave
605 l_return_status := x_return_status;
606 RAISE G_EXCEPTION_HALT_PROCESSING;
607 ELSE
608 l_return_status := x_return_status;
609 END IF;
610 END IF;
611
612
613 RETURN(l_return_status);
614 EXCEPTION
615 WHEN G_EXCEPTION_HALT_PROCESSING THEN
616 -- just come out with return status
617 NULL;
618 RETURN (l_return_status);
619
620 WHEN OTHERS THEN
621 Okl_Api.set_message(p_app_name =>Okl_Ove_Pvt.G_APP_NAME,
622 p_msg_name =>Okl_Ove_Pvt.G_UNEXPECTED_ERROR,
623 p_token1 =>Okl_Ove_Pvt.G_SQL_SQLCODE_TOKEN,
624 p_token1_value =>SQLCODE,
625 p_token2 =>Okl_Ove_Pvt.G_SQL_SQLERRM_TOKEN,
626 p_token2_value =>SQLERRM);
627 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
628 RETURN(l_return_status);
629
630 END Validate_Attributes;
631
632 -----END OF VALIDATE ATTRIBUTES-------------------------
633
634 ---------------------------------------------------------------------------
635 -- PROCEDURE check_rule_templates for: OKL_OPT_VALUES_V
636 -- To verify whether the dates modification is valid in relation with
637 -- the attached Option Value Rule Templates
638 ---------------------------------------------------------------------------
639 PROCEDURE check_rule_templates (
640 p_api_version IN NUMBER,
641 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
642 p_ovev_rec IN ovev_rec_type,
643 x_return_status OUT NOCOPY VARCHAR2,
644 x_msg_count OUT NOCOPY NUMBER,
645 x_msg_data OUT NOCOPY VARCHAR2,
646 x_valid OUT NOCOPY BOOLEAN
647 ) IS
648 CURSOR okl_ove_ovt_fk_csr (p_ove_id IN Okl_Opt_Values_V.id%TYPE
649 ) IS
650 SELECT ovt.RUL_ID RUL_ID,
651 ovd.CONTEXT_INTENT CONTEXT_INTENT,
652 orl.RGR_RGD_CODE RGR_RGD_CODE,
653 orl.RGR_RDF_CODE RGR_RDF_CODE,
654 ovt.SEQUENCE_NUMBER SEQUENCE_NUMBER
655 FROM Okl_Opt_Rules_V orl,
656 Okl_Opv_Rules_V ovd,
657 Okl_Ovd_Rul_Tmls_V ovt
658 WHERE ovd.OVE_ID = p_ove_id
659 AND orl.ID = ovd.ORL_ID
660 AND ovt.OVD_ID = ovd.ID;
661
662 CURSOR okl_ove_rds_fk_csr (p_rgd_code IN OKC_Rule_Def_Sources_V.rgr_rgd_code%TYPE,
663 p_rdf_code IN OKC_Rule_Def_Sources_V.rgr_rdf_code%TYPE,
664 p_buy_or_sell IN OKC_Rule_Def_Sources_V.buy_or_sell%TYPE,
665 p_jtot_object_code IN OKC_Rule_Def_Sources_V.jtot_object_code%TYPE,
666 p_object_id_number IN OKC_Rule_Def_Sources_V.object_id_number%TYPE,
667 p_from_date IN Okl_Opt_Values_V.from_date%TYPE,
668 p_to_date IN Okl_Opt_Values_V.TO_DATE%TYPE
669 ) IS
670 SELECT '1'
671 FROM OKC_Rule_Def_Sources_V rds
672 WHERE rds.RGR_RGD_CODE = p_rgd_code
673 AND rds.RGR_RDF_CODE = p_rdf_code
674 AND rds.OBJECT_ID_NUMBER = p_object_id_number
675 AND rds.JTOT_OBJECT_CODE = p_jtot_object_code
676 AND rds.BUY_OR_SELL = p_buy_or_sell
677 AND (rds.START_DATE > p_from_date OR
678 NVL(rds.END_DATE, p_to_date) < p_to_date);
679
680 l_rulv_disp_rec rulv_disp_rec_type;
681 l_no_data_found BOOLEAN := TRUE;
682 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
683 l_count NUMBER := 0;
684 l_jtot_object_code VARCHAR2(30);
685 l_okx_start_date DATE;
686 l_okx_end_date DATE;
687 l_rulv_rec rulv_rec_type;
688 l_check VARCHAR2(1) := '?';
689 BEGIN
690 x_valid := TRUE;
691 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
692
693 -- Get current database values
694 FOR okl_ove_ovt_rec IN okl_ove_ovt_fk_csr(p_ovev_rec.id)
695 LOOP
696 l_rulv_rec.id := okl_ove_ovt_rec.rul_id;
697 get_rul_rec (p_rulv_rec => l_rulv_rec,
698 x_return_status => l_return_status,
699 x_no_data_found => l_no_data_found,
700 x_rulv_rec => l_rulv_rec);
701 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
702 l_no_data_found = TRUE THEN
703 x_valid := FALSE;
704 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
705 RAISE G_EXCEPTION_HALT_PROCESSING;
706 END IF;
707 Okl_Rule_Apis_Pvt.get_rule_disp_value(p_api_version => p_api_version,
708 p_init_msg_list => p_init_msg_list,
709 p_rulv_rec => l_rulv_rec,
710 x_return_status => l_return_status,
711 x_msg_count => x_msg_count,
712 x_msg_data => x_msg_data,
713 x_rulv_disp_rec => l_rulv_disp_rec);
714 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
715 x_valid := FALSE;
716 x_return_status := Okl_Api.G_RET_STS_ERROR;
717 RAISE G_EXCEPTION_HALT_PROCESSING;
718 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
719 x_valid := FALSE;
720 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
721 RAISE G_EXCEPTION_HALT_PROCESSING;
722 END IF;
723
724 FOR l_object_id_number IN 1..3
725 LOOP
726 IF l_object_id_number = 1 THEN
727 l_jtot_object_code := l_rulv_rec.jtot_object1_code;
728 l_okx_start_date := l_rulv_disp_rec.obj1_start_date;
729 l_okx_end_date := l_rulv_disp_rec.obj1_end_date;
730 ELSIF l_object_id_number = 2 THEN
731 l_jtot_object_code := l_rulv_rec.jtot_object2_code;
732 l_okx_start_date := l_rulv_disp_rec.obj2_start_date;
733 l_okx_end_date := l_rulv_disp_rec.obj2_end_date;
734 ELSE
735 l_jtot_object_code := l_rulv_rec.jtot_object3_code;
736 l_okx_start_date := l_rulv_disp_rec.obj3_start_date;
737 l_okx_end_date := l_rulv_disp_rec.obj3_end_date;
738 END IF;
739
740 IF l_jtot_object_code <> Okl_Api.G_MISS_CHAR AND
741 (l_okx_start_date > p_ovev_rec.from_date OR
742 NVL(l_okx_end_date, p_ovev_rec.TO_DATE) < p_ovev_rec.TO_DATE OR
743 p_ovev_rec.from_date > p_ovev_rec.TO_DATE) THEN
744 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
745 p_msg_name => G_DATES_MISMATCH,
746 p_token1 => G_PARENT_TABLE_TOKEN,
747 p_token1_value => 'Okl_Opt_Values_V',
748 p_token2 => G_CHILD_TABLE_TOKEN,
749 p_token2_value => 'OKC_Rules_V');
750 x_valid := FALSE;
751 x_return_status := Okl_Api.G_RET_STS_ERROR;
752 RAISE G_EXCEPTION_HALT_PROCESSING;
753 END IF;
754
755 IF l_jtot_object_code <> Okl_Api.G_MISS_CHAR THEN
756 -- Check for dates in source, okx and option value
757 OPEN okl_ove_rds_fk_csr (p_rgd_code => okl_ove_ovt_rec.rgr_rgd_code,
758 p_rdf_code => okl_ove_ovt_rec.rgr_rdf_code,
759 p_buy_or_sell => okl_ove_ovt_rec.context_intent,
760 p_jtot_object_code => l_jtot_object_code,
761 p_object_id_number => l_object_id_number,
762 p_from_date => p_ovev_rec.from_date,
763 p_to_date => p_ovev_rec.TO_DATE);
764 FETCH okl_ove_rds_fk_csr INTO l_check;
765 l_no_data_found := okl_ove_rds_fk_csr%NOTFOUND;
766 CLOSE okl_ove_rds_fk_csr;
767
768 IF l_no_data_found = FALSE THEN
769 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
770 p_msg_name => G_DATES_MISMATCH,
771 p_token1 => G_PARENT_TABLE_TOKEN,
772 p_token1_value => 'Okl_Opt_Values_V',
773 p_token2 => G_CHILD_TABLE_TOKEN,
774 p_token2_value => 'OKC_Rule_Def_Sources_V');
775 x_valid := FALSE;
776 x_return_status := Okl_Api.G_RET_STS_ERROR;
777 RAISE G_EXCEPTION_HALT_PROCESSING;
778 END IF;
779 END IF;
780 END LOOP;
781
782 END LOOP;
783
784 EXCEPTION
785 WHEN G_EXCEPTION_HALT_PROCESSING THEN
786 -- no processing necessary; validation can continue
787 -- with the next column
788 NULL;
789
790 WHEN OTHERS THEN
791 -- store SQL error message on message stack
792 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
793 p_msg_name => G_UNEXPECTED_ERROR,
794 p_token1 => G_SQLCODE_TOKEN,
795 p_token1_value => SQLCODE,
796 p_token2 => G_SQLERRM_TOKEN,
797 p_token2_value => SQLERRM);
798 x_valid := FALSE;
799 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
800
801 IF (okl_ove_ovt_fk_csr%ISOPEN) THEN
802 CLOSE okl_ove_ovt_fk_csr;
803 END IF;
804
805 IF (okl_ove_rds_fk_csr%ISOPEN) THEN
806 CLOSE okl_ove_rds_fk_csr;
807 END IF;
808
809 END check_rule_templates;
810
811 ---------------------------------------------------------------------------
812 -- PROCEDURE reorganize_inputs
813 -- This procedure is to reset the attributes in the input structure based
814 -- on the data from database
815 ---------------------------------------------------------------------------
816 PROCEDURE reorganize_inputs (
817 p_upd_ovev_rec IN OUT NOCOPY ovev_rec_type,
818 p_db_ovev_rec IN ovev_rec_type
819 ) IS
820 l_upd_ovev_rec ovev_rec_type;
821 l_db_ovev_rec ovev_rec_type;
822 BEGIN
823 /* create a temporary record with all relevant details from db and upd records */
824 l_upd_ovev_rec := p_upd_ovev_rec;
825 l_db_ovev_rec := p_db_ovev_rec;
826
827 IF l_upd_ovev_rec.description = l_db_ovev_rec.description THEN
828 l_upd_ovev_rec.description := Okl_Api.G_MISS_CHAR;
829 END IF;
830
831 IF to_date(to_char(l_upd_ovev_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_ovev_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
832 l_upd_ovev_rec.from_date := Okl_Api.G_MISS_DATE;
833 END IF;
834
835 IF to_date(to_char(l_upd_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
836 l_upd_ovev_rec.TO_DATE := Okl_Api.G_MISS_DATE;
837 END IF;
838
839 p_upd_ovev_rec := l_upd_ovev_rec;
840
841 END reorganize_inputs;
842
843 ---------------------------------------------------------------------------
844 -- FUNCTION defaults_to_actuals
845 -- This function creates an output record with changed information from the
846 -- input structure and unchanged details from the database
847 ---------------------------------------------------------------------------
848 FUNCTION defaults_to_actuals (
849 p_upd_ovev_rec IN ovev_rec_type,
850 p_db_ovev_rec IN ovev_rec_type
851 ) RETURN ovev_rec_type IS
852 l_ovev_rec ovev_rec_type;
853 BEGIN
854 /* create a temporary record with all relevant details from db and upd records */
855 l_ovev_rec := p_db_ovev_rec;
856
857 IF p_upd_ovev_rec.description <> Okl_Api.G_MISS_CHAR THEN
858 l_ovev_rec.description := p_upd_ovev_rec.description;
859 END IF;
860
861 IF p_upd_ovev_rec.from_date <> Okl_Api.G_MISS_DATE THEN
862 l_ovev_rec.from_date := p_upd_ovev_rec.from_date;
863 END IF;
864
865 IF p_upd_ovev_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
866 l_ovev_rec.TO_DATE := p_upd_ovev_rec.TO_DATE;
867 END IF;
868
869 RETURN l_ovev_rec;
870 END defaults_to_actuals;
871
872 ---------------------------------------------------------------------------
873 -- PROCEDURE check_updates
874 -- To verify whether the requested changes from the screen are valid or not
875 ---------------------------------------------------------------------------
876 PROCEDURE check_updates (
877 p_api_version IN NUMBER,
878 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
879 p_upd_ovev_rec IN ovev_rec_type,
880 p_db_ovev_rec IN ovev_rec_type,
881 p_ovev_rec IN ovev_rec_type,
882 x_return_status OUT NOCOPY VARCHAR2,
883 x_msg_count OUT NOCOPY NUMBER,
884 x_msg_data OUT NOCOPY VARCHAR2
885 ) IS
886 l_ovev_rec ovev_rec_type;
887 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
888 l_valid BOOLEAN;
889 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
890 BEGIN
891 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
892 l_ovev_rec := p_ovev_rec;
893
894 /* check for start date greater than sysdate */
895 /*IF to_date(to_char(p_upd_ovev_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
896 to_date(to_char(p_upd_ovev_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
897 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
898 p_msg_name => G_START_DATE);
899 x_return_status := Okl_Api.G_RET_STS_ERROR;
900 RAISE G_EXCEPTION_HALT_PROCESSING;
901 END IF;
902 */
903 /* check for the records with from and to dates less than sysdate */
904 /*IF to_date(to_char(p_upd_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
905 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
906 p_msg_name => G_PAST_RECORDS);
907 x_return_status := Okl_Api.G_RET_STS_ERROR;
908 RAISE G_EXCEPTION_HALT_PROCESSING;
909 END IF;
910 */
911 /* if the start date is in the past, the start date cannot be
912 modified */
913 /*IF to_date(to_char(p_upd_ovev_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
914 to_date(to_char(p_db_ovev_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate THEN
915 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
916 p_msg_name => G_NOT_ALLOWED,
917 p_token1 => G_COL_NAME_TOKEN,
918 p_token1_value => 'START_DATE');
919 x_return_status := Okl_Api.G_RET_STS_ERROR;
920 RAISE G_EXCEPTION_HALT_PROCESSING;
921 END IF;
922 */
923
924 IF p_upd_ovev_rec.from_date <> Okl_Api.G_MISS_DATE OR
925 p_upd_ovev_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
926 Check_Constraints(p_ovev_rec => l_ovev_rec,
927 x_return_status => l_return_status,
928 x_valid => l_valid);
929 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
930 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
931 RAISE G_EXCEPTION_HALT_PROCESSING;
932 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
933 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
934 l_valid <> TRUE) THEN
935 x_return_status := Okl_Api.G_RET_STS_ERROR;
936 RAISE G_EXCEPTION_HALT_PROCESSING;
937 END IF;
938
939 check_rule_templates(p_api_version => p_api_version,
940 p_init_msg_list => p_init_msg_list,
941 p_ovev_rec => l_ovev_rec,
942 x_return_status => l_return_status,
943 x_msg_count => x_msg_count,
944 x_msg_data => x_msg_data,
945 x_valid => l_valid);
946 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
947 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
948 RAISE G_EXCEPTION_HALT_PROCESSING;
949 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
950 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
951 l_valid <> TRUE) THEN
952 x_return_status := Okl_Api.G_RET_STS_ERROR;
953 RAISE G_EXCEPTION_HALT_PROCESSING;
954 END IF;
955
956 END IF;
957
958 EXCEPTION
959 WHEN G_EXCEPTION_HALT_PROCESSING THEN
960 -- no processing necessary; validation can continue
961 -- with the next column
962 NULL;
963
964 WHEN OTHERS THEN
965 -- store SQL error message on message stack for caller
966 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
967 p_msg_name => G_UNEXPECTED_ERROR,
968 p_token1 => G_SQLCODE_TOKEN,
969 p_token1_value => SQLCODE,
970 p_token2 => G_SQLERRM_TOKEN,
971 p_token2_value => SQLERRM );
972 -- notify caller of an UNEXPECTED error
973 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
974
975 END check_updates;
976
977 ---------------------------------------------------------------------------
978 -- PROCEDURE determine_action for: OKL_OPT_VALUES_V
979 -- This function helps in determining the various checks to be performed
980 -- for the new/updated record and also helps in determining whether a new
981 -- version is required or not
982 ---------------------------------------------------------------------------
983 FUNCTION determine_action (
984 p_upd_ovev_rec IN ovev_rec_type,
985 p_db_ovev_rec IN ovev_rec_type,
986 p_date IN DATE
987 ) RETURN VARCHAR2 IS
988 l_action VARCHAR2(1);
989 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
990 BEGIN
991 /* Scenario 1: Only description changes */
992 IF p_upd_ovev_rec.from_date = Okl_Api.G_MISS_DATE AND
993 p_upd_ovev_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
994 l_action := '1';
995 /* Scenario 2: Changing the dates */
996 ELSE
997 l_action := '2';
998 END IF;
999 RETURN(l_action);
1000 END determine_action;
1001
1002 ---------------------------------------------------------------------------
1003 -- PROCEDURE insert_optvalues for: OKL_OPT_VALUES_V
1004 ---------------------------------------------------------------------------
1005 PROCEDURE insert_optvalues(p_api_version IN NUMBER,
1006 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1007 x_return_status OUT NOCOPY VARCHAR2,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2,
1010 p_optv_rec IN optv_rec_type,
1011 p_ovev_rec IN ovev_rec_type,
1012 x_ovev_rec OUT NOCOPY ovev_rec_type
1013 ) IS
1014 l_api_version CONSTANT NUMBER := 1;
1015 l_api_name CONSTANT VARCHAR2(30) := 'insert_optvalues';
1016 l_valid BOOLEAN := TRUE;
1017 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1018 l_optv_rec optv_rec_type;
1019 l_ovev_rec ovev_rec_type;
1020 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
1021 l_row_notfound BOOLEAN := TRUE;
1022 BEGIN
1023 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1024 l_optv_rec := p_optv_rec;
1025 l_ovev_rec := p_ovev_rec;
1026
1027 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1028 p_pkg_name => G_PKG_NAME,
1029 p_init_msg_list => p_init_msg_list,
1030 l_api_version => l_api_version,
1031 p_api_version => p_api_version,
1032 p_api_type => '_PVT',
1033 x_return_status => l_return_status);
1034 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1035 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1036 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1037 RAISE Okl_Api.G_EXCEPTION_ERROR;
1038 END IF;
1039
1040 --- Validate all non-missing attributes (Item Level Validation)
1041 l_return_status := Validate_Attributes(l_ovev_rec);
1042 --- If any errors happen abort API
1043 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1044 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1045 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1046 RAISE Okl_Api.G_EXCEPTION_ERROR;
1047 END IF;
1048
1049 /* check for the records with start or end dates less than sysdate */
1050 /*IF to_date(to_char(l_ovev_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
1051 to_date(to_char(l_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
1052 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1053 p_msg_name => G_PAST_RECORDS);
1054 RAISE Okl_Api.G_EXCEPTION_ERROR;
1055 END IF;
1056 */
1057 default_parent_dates( p_ovev_rec => l_ovev_rec,
1058 x_no_data_found => l_row_notfound,
1059 x_return_status => l_return_status,
1060 x_optv_rec => l_optv_rec);
1061
1062 IF (l_row_notfound) THEN
1063 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1064 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1065 RAISE Okl_Api.G_EXCEPTION_ERROR;
1066 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1067 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1068 END IF;
1069
1070 --Default Child End Date With Its Parents End Date If It Is Not Entered.
1071 IF to_date(to_char(l_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
1072 to_date(to_char(l_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
1073 l_ovev_rec.TO_DATE := l_optv_rec.TO_DATE;
1074 END IF;
1075
1076 /* call check_constraints to check the validity of this relationship */
1077 Check_Constraints(p_ovev_rec => l_ovev_rec,
1078 x_return_status => l_return_status,
1079 x_valid => l_valid);
1080
1081 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
1084 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
1085 l_valid <> TRUE) THEN
1086 x_return_status := Okl_Api.G_RET_STS_ERROR;
1087 RAISE Okl_Api.G_EXCEPTION_ERROR;
1088 END IF;
1089
1090 /* public api to create option values */
1091 Okl_Options_Pub.create_option_values(p_api_version => p_api_version,
1092 p_init_msg_list => p_init_msg_list,
1093 x_return_status => l_return_status,
1094 x_msg_count => x_msg_count,
1095 x_msg_data => x_msg_data,
1096 p_ovev_rec => l_ovev_rec,
1097 x_ovev_rec => x_ovev_rec);
1098
1099 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1100 RAISE Okl_Api.G_EXCEPTION_ERROR;
1101 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1102 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1103 END IF;
1104 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1105 x_msg_data => x_msg_data);
1106 EXCEPTION
1107 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1108 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1109 p_pkg_name => G_PKG_NAME,
1110 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1111 x_msg_count => x_msg_count,
1112 x_msg_data => x_msg_data,
1113 p_api_type => '_PVT');
1114 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1115 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1116 p_pkg_name => G_PKG_NAME,
1117 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1118 x_msg_count => x_msg_count,
1119 x_msg_data => x_msg_data,
1120 p_api_type => '_PVT');
1121 WHEN OTHERS THEN
1122 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1123 p_pkg_name => G_PKG_NAME,
1124 p_exc_name => 'OTHERS',
1125 x_msg_count => x_msg_count,
1126 x_msg_data => x_msg_data,
1127 p_api_type => '_PVT');
1128
1129 END insert_optvalues;
1130
1131 ---------------------------------------------------------------------------
1132 -- PROCEDURE update_optvalues for: OKL_OPT_VALUES_V
1133 ---------------------------------------------------------------------------
1134 PROCEDURE update_optvalues(p_api_version IN NUMBER,
1135 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 x_msg_count OUT NOCOPY NUMBER,
1138 x_msg_data OUT NOCOPY VARCHAR2,
1139 p_optv_rec IN optv_rec_type,
1140 p_ovev_rec IN ovev_rec_type,
1141 x_ovev_rec OUT NOCOPY ovev_rec_type
1142 ) IS
1143 l_api_version CONSTANT NUMBER := 1;
1144 l_api_name CONSTANT VARCHAR2(30) := 'update_optvalues';
1145 l_no_data_found BOOLEAN := TRUE;
1146 l_valid BOOLEAN := TRUE;
1147 l_optv_rec optv_rec_type; /* for master record */
1148 l_db_ovev_rec ovev_rec_type; /* database copy */
1149 l_upd_ovev_rec ovev_rec_type; /* input copy */
1150 l_ovev_rec ovev_rec_type; /* latest with the retained changes */
1151 l_tmp_ovev_rec ovev_rec_type; /* for any other purposes */
1152 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
1153 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1154 l_action VARCHAR2(1);
1155 l_row_notfound BOOLEAN := TRUE;
1156 BEGIN
1157 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1158
1159 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
1160 p_pkg_name => G_PKG_NAME,
1161 p_init_msg_list => p_init_msg_list,
1162 l_api_version => l_api_version,
1163 p_api_version => p_api_version,
1164 p_api_type => '_PVT',
1165 x_return_status => l_return_status);
1166 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1167 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1168 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1169 RAISE Okl_Api.G_EXCEPTION_ERROR;
1170 END IF;
1171
1172 l_upd_ovev_rec := p_ovev_rec;
1173
1174 /* fetch old details from the database */
1175 get_rec(p_ovev_rec => l_upd_ovev_rec,
1176 x_return_status => l_return_status,
1177 x_no_data_found => l_no_data_found,
1178 x_ovev_rec => l_db_ovev_rec);
1179 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
1180 l_no_data_found = TRUE THEN
1181 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1182 END IF;
1183
1184 default_parent_dates( p_ovev_rec => l_db_ovev_rec,
1185 x_no_data_found => l_row_notfound,
1186 x_return_status => l_return_status,
1187 x_optv_rec => l_optv_rec);
1188
1189 IF (l_row_notfound) THEN
1190 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
1191 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1192 RAISE Okl_Api.G_EXCEPTION_ERROR;
1193 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1194 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1195 END IF;
1196
1197 --Default Child End Date With Its Parents End Date If It Is Not Entered.
1198 IF to_date(to_char(l_optv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
1199 (to_date(to_char(l_upd_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') OR
1200 to_date(to_char(l_upd_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') IS NULL) THEN
1201 l_upd_ovev_rec.TO_DATE := l_optv_rec.TO_DATE;
1202 END IF;
1203
1204 /* to reorganize the input accordingly */
1205 reorganize_inputs(p_upd_ovev_rec => l_upd_ovev_rec,
1206 p_db_ovev_rec => l_db_ovev_rec);
1207
1208 /* check for past records */
1209 /*IF to_date(to_char(l_db_ovev_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate AND
1210 to_date(to_char(l_db_ovev_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
1211 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
1212 p_msg_name => G_PAST_RECORDS);
1213 x_return_status := Okl_Api.G_RET_STS_ERROR;
1214 RAISE Okl_Api.G_EXCEPTION_ERROR;
1215 END IF;
1216 */
1217
1218 IF (l_upd_ovev_rec.TO_DATE = Okl_Api.G_MISS_DATE) then
1219 l_upd_ovev_rec.TO_DATE := p_ovev_rec.to_date;
1220 end if;
1221
1222 IF (l_upd_ovev_rec.from_DATE = Okl_Api.G_MISS_DATE) then
1223 l_upd_ovev_rec.from_DATE := p_ovev_rec.from_date;
1224 end if;
1225
1226
1227 /* To Check end date is > start date*/
1228 IF (l_upd_ovev_rec.TO_DATE IS NOT NULL) AND (l_upd_ovev_rec.TO_DATE < l_upd_ovev_rec.from_date) THEN
1229 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
1230 ,p_msg_name => Okl_Ove_Pvt.g_to_date_error
1231 ,p_token1 => Okl_Ove_Pvt.g_col_name_token
1232 ,p_token1_value => 'TO_DATE');
1233 x_return_status := Okl_Api.G_RET_STS_ERROR;
1234 RAISE Okl_Api.G_EXCEPTION_ERROR;
1235 END IF;
1236
1237 /* determine how the processing to be done */
1238 l_action := determine_action(p_upd_ovev_rec => l_upd_ovev_rec,
1239 p_db_ovev_rec => l_db_ovev_rec,
1240 p_date => l_sysdate);
1241
1242 /* Scenario 1: only changing description and descriptive flexfields */
1243 IF l_action = '1' THEN
1244 /* public api to update options */
1245 Okl_Options_Pub.update_option_values(p_api_version => p_api_version,
1246 p_init_msg_list => p_init_msg_list,
1247 x_return_status => l_return_status,
1248 x_msg_count => x_msg_count,
1249 x_msg_data => x_msg_data,
1250 p_ovev_rec => l_upd_ovev_rec,
1251 x_ovev_rec => x_ovev_rec);
1252 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1253 RAISE Okl_Api.G_EXCEPTION_ERROR;
1254 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1255 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1256 END IF;
1257
1258 /* Scenario 2: changing the dates */
1259 ELSIF l_action = '2' THEN
1260 /* create a temporary record with all relevant details from db and upd records */
1261 l_ovev_rec := defaults_to_actuals(p_upd_ovev_rec => l_upd_ovev_rec,
1262 p_db_ovev_rec => l_db_ovev_rec);
1263
1264 check_updates(p_api_version => p_api_version,
1265 p_init_msg_list => p_init_msg_list,
1266 p_upd_ovev_rec => l_upd_ovev_rec,
1267 p_db_ovev_rec => l_db_ovev_rec,
1268 p_ovev_rec => l_ovev_rec,
1269 x_return_status => l_return_status,
1270 x_msg_count => x_msg_count,
1271 x_msg_data => x_msg_data);
1272 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1273 RAISE Okl_Api.G_EXCEPTION_ERROR;
1274 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1275 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1276 END IF;
1277
1278 /* public api to update options */
1279 Okl_Options_Pub.update_option_values(p_api_version => p_api_version,
1280 p_init_msg_list => p_init_msg_list,
1281 x_return_status => l_return_status,
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_ovev_rec => l_upd_ovev_rec,
1285 x_ovev_rec => x_ovev_rec);
1286 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
1287 RAISE Okl_Api.G_EXCEPTION_ERROR;
1288 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
1289 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1290 END IF;
1291
1292 END IF;
1293
1294 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data);
1296 EXCEPTION
1297 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1298 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1299 p_pkg_name => G_PKG_NAME,
1300 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1301 x_msg_count => x_msg_count,
1302 x_msg_data => x_msg_data,
1303 p_api_type => '_PVT');
1304 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1305 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1306 p_pkg_name => G_PKG_NAME,
1307 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1308 x_msg_count => x_msg_count,
1309 x_msg_data => x_msg_data,
1310 p_api_type => '_PVT');
1311 WHEN OTHERS THEN
1312 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
1313 p_pkg_name => G_PKG_NAME,
1314 p_exc_name => 'OTHERS',
1315 x_msg_count => x_msg_count,
1316 x_msg_data => x_msg_data,
1317 p_api_type => '_PVT');
1318
1319 END update_optvalues;
1320
1321 END Okl_Setupoptvalues_Pvt;