[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPOVERULES_PVT
Source
1 Package BODY Okl_Setupoverules_Pvt AS
2 /* $Header: OKLRSODB.pls 115.9 2003/07/23 18:32:50 sgorantl noship $ */
3 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
4 G_COLUMN_TOKEN CONSTANT VARCHAR2(100) := 'COLUMN';
5 ---------------------------------------------------------------------------
6 -- PROCEDURE get_rec for: OKL_OPV_RULES_V
7 ---------------------------------------------------------------------------
8 PROCEDURE get_rec (
9 p_ovdv_rec IN ovdv_rec_type,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_no_data_found OUT NOCOPY BOOLEAN,
12 x_ovdv_rec OUT NOCOPY ovdv_rec_type
13 ) IS
14 CURSOR okl_ovdv_pk_csr (p_id IN NUMBER) IS
15 SELECT
16 ID,
17 OBJECT_VERSION_NUMBER,
18 ORL_ID,
19 OVE_ID,
20 COPY_OR_ENTER_FLAG,
21 CONTEXT_INTENT,
22 CONTEXT_ORG,
23 CONTEXT_INV_ORG,
24 CONTEXT_ASSET_BOOK,
25 NVL(INDIVIDUAL_INSTRUCTIONS,Okl_Api.G_MISS_CHAR) INDIVIDUAL_INSTRUCTIONS,
26 CREATED_BY,
27 CREATION_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_DATE,
30 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
31 FROM Okl_Opv_Rules_V
32 WHERE Okl_Opv_Rules_V.id = p_id;
33 l_okl_ovdv_pk okl_ovdv_pk_csr%ROWTYPE;
34 l_ovdv_rec ovdv_rec_type;
35 BEGIN
36 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
37 x_no_data_found := TRUE;
38
39 -- Get current database values
40 OPEN okl_ovdv_pk_csr (p_ovdv_rec.id);
41 FETCH okl_ovdv_pk_csr INTO
42 l_ovdv_rec.ID,
43 l_ovdv_rec.OBJECT_VERSION_NUMBER,
44 l_ovdv_rec.ORL_ID,
45 l_ovdv_rec.OVE_ID,
46 l_ovdv_rec.COPY_OR_ENTER_FLAG,
47 l_ovdv_rec.CONTEXT_INTENT,
48 l_ovdv_rec.CONTEXT_ORG,
49 l_ovdv_rec.CONTEXT_INV_ORG,
50 l_ovdv_rec.CONTEXT_ASSET_BOOK,
51 l_ovdv_rec.INDIVIDUAL_INSTRUCTIONS,
52 l_ovdv_rec.CREATED_BY,
53 l_ovdv_rec.CREATION_DATE,
54 l_ovdv_rec.LAST_UPDATED_BY,
55 l_ovdv_rec.LAST_UPDATE_DATE,
56 l_ovdv_rec.LAST_UPDATE_LOGIN;
57 x_no_data_found := okl_ovdv_pk_csr%NOTFOUND;
58 CLOSE okl_ovdv_pk_csr;
59 x_ovdv_rec := l_ovdv_rec;
60 EXCEPTION
61 WHEN OTHERS THEN
62 -- store SQL error message on message stack
63 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
64 p_msg_name => G_UNEXPECTED_ERROR,
65 p_token1 => G_SQLCODE_TOKEN,
66 p_token1_value => SQLCODE,
67 p_token2 => G_SQLERRM_TOKEN,
68 p_token2_value => SQLERRM);
69 -- notify UNEXPECTED error for calling API.
70 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
71
72 IF (okl_ovdv_pk_csr%ISOPEN) THEN
73 CLOSE okl_ovdv_pk_csr;
74 END IF;
75
76 END get_rec;
77
78 ---------------------------------------------------------------------------
79 -- PROCEDURE check_constraints for: OKL_OPV_RULES_V
80 -- To verify whether an addition of new option value rule is ok with rest
81 -- of the product - contract relationships
82 ---------------------------------------------------------------------------
83 PROCEDURE Check_Constraints (
84 p_ovdv_rec IN ovdv_rec_type,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_valid OUT NOCOPY BOOLEAN
87 ) IS
88 CURSOR okl_ovd_orl_fk_csr (p_ove_id IN Okl_Opt_Values_V.ID%TYPE,
89 p_orl_id IN Okl_Opt_Rules_V.id%TYPE
90 ) IS
91 SELECT '1'
92 FROM Okl_Opt_Values_V ove,
93 Okl_Opt_Rules_V orl,
94 Okl_Lse_Scs_Rules_V lsr
95 WHERE orl.ID = p_orl_id
96 AND ove.ID = p_ove_id
97 AND ((orl.LRG_LSE_ID IS NOT NULL
98 AND lsr.LSE_ID = orl.LRG_LSE_ID
99 AND lsr.SRD_ID = orl.LRG_SRD_ID)
100 OR
101 (orl.LRG_LSE_ID IS NULL
102 AND lsr.LSE_ID IS NULL
103 AND lsr.SRD_ID = orl.SRD_ID_FOR))
104 AND lsr.RULE_GROUP = orl.RGR_RGD_CODE
105 AND lsr.RULE = orl.RGR_RDF_CODE
106 AND (lsr.START_DATE > ove.FROM_DATE OR
107 NVL(lsr.END_DATE, NVL(ove.TO_DATE, Okl_Api.G_MISS_DATE)) < NVL(ove.TO_DATE, Okl_Api.G_MISS_DATE));
108
109 CURSOR okl_ovd_csp_fk_csr (p_ove_id IN Okl_Opt_Values_V.ID%TYPE
110 ) IS
111 SELECT '1'
112 FROM Okl_Pdt_Opt_Vals_V pov,
113 Okl_Slctd_Optns_V csp
114 WHERE pov.OVE_ID = p_ove_id
115 AND csp.POV_ID = pov.ID;
116
117 CURSOR okl_ovd_ove_fk_csr (p_ove_id IN Okl_Products_V.ID%TYPE,
118 p_date IN Okl_Products_V.TO_DATE%TYPE
119 ) IS
120 SELECT '1'
121 FROM Okl_opt_values_V ove
122 WHERE ove.ID = p_ove_id
123 AND NVL(ove.TO_DATE, p_date) < p_date;
124
125
126 CURSOR okl_ovd_ovt_fk_csr (p_ovd_id IN Okl_Opv_Rules_V.ID%TYPE
127 ) IS
128 SELECT '1'
129 FROM Okl_Ovd_Rul_Tmls_V ovt
130 WHERE ovt.OVD_ID = p_ovd_id;
131
132 CURSOR c1(p_orl_id okl_opv_rules_v.orl_id%TYPE,
133 p_ove_id okl_opv_rules_v.ove_id%TYPE,
134 p_context_intent okl_opv_rules_v.context_intent%TYPE,
135 p_context_org okl_opv_rules_v.context_org%TYPE,
136 p_context_inv_org okl_opv_rules_v.context_inv_org%TYPE,
137 p_context_asset_book okl_opv_rules_v.context_asset_book%TYPE) IS
138 SELECT '1'
139 FROM okl_opv_rules_v
140 WHERE orl_id = p_orl_id
141 AND ove_id = p_ove_id
142 AND context_intent = p_context_intent
143 AND (context_org IS NULL OR context_org = p_context_org)
144 AND (context_inv_org IS NULL OR context_inv_org = p_context_inv_org)
145 AND (context_asset_book IS NULL OR context_asset_book = p_context_asset_book);
146
147 l_ovd_status VARCHAR2(1);
148 l_context_org NUMBER;
149 l_context_inv_org NUMBER;
150 l_context_asset_book VARCHAR2(100) := NULL;
151 l_row_found BOOLEAN := FALSE;
152 l_token_1 VARCHAR2(1999);
153 l_token_2 VARCHAR2(1999);
154 l_token_3 VARCHAR2(1999);
155 l_token_4 VARCHAR2(1999);
156 l_check VARCHAR2(1) := '?';
157 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
158 l_row_not_found BOOLEAN := FALSE;
159 BEGIN
160 x_valid := TRUE;
161 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
162
163 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPT_VAL_RULE_SUMRY',
164 p_attribute_code => 'OKL_OPTION_VALUE_RULES');
165
166 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTV_RUL_TML_SUMRY',
167 p_attribute_code => 'OKL_LP_OPT_VAL_RUL_TML');
168
169 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPT_VAL_RULE_SUMRY',
170 p_attribute_code => 'OKL_RULE');
171
172 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_RULE_CR_UPD',
173 p_attribute_code => 'OKL_RULE');
174
175
176 -- Check if the option value is already in use with a contract
177 OPEN okl_ovd_csp_fk_csr (p_ovdv_rec.ove_id);
178 FETCH okl_ovd_csp_fk_csr INTO l_check;
179 l_row_not_found := okl_ovd_csp_fk_csr%NOTFOUND;
180 CLOSE okl_ovd_csp_fk_csr;
181
182 IF l_row_not_found = FALSE THEN
183 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
184 p_msg_name => G_IN_USE,
185 p_token1 => G_PARENT_TABLE_TOKEN,
186 p_token1_value => l_token_1,
187 p_token2 => G_CHILD_TABLE_TOKEN,
188 p_token2_value => 'Okl_Slctd_Optns_V');
189 x_valid := FALSE;
190 x_return_status := Okl_Api.G_RET_STS_ERROR;
191 RAISE G_EXCEPTION_HALT_PROCESSING;
192 END IF;
193
194 -- Check for related rule templates
195 -- Only delete scenario
196 IF p_ovdv_rec.id <> Okl_Api.G_MISS_NUM THEN
197 OPEN okl_ovd_ovt_fk_csr (p_ovdv_rec.id);
198 FETCH okl_ovd_ovt_fk_csr INTO l_check;
199 l_row_not_found := okl_ovd_ovt_fk_csr%NOTFOUND;
200 CLOSE okl_ovd_ovt_fk_csr;
201
202 IF l_row_not_found = FALSE THEN
203 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
204 p_msg_name => G_IN_USE,
205 p_token1 => G_PARENT_TABLE_TOKEN,
206 p_token1_value => l_token_1,
207 p_token2 => G_CHILD_TABLE_TOKEN,
208 p_token2_value => l_token_2);
209 x_valid := FALSE;
210 x_return_status := Okl_Api.G_RET_STS_ERROR;
211 RAISE G_EXCEPTION_HALT_PROCESSING;
212 END IF;
213 END IF;
214
215 -- check uniqueness
216 IF p_ovdv_rec.id = Okl_Api.G_MISS_NUM THEN
217 IF p_ovdv_rec.context_org = Okl_Api.G_MISS_NUM THEN
218 l_context_org := NULL;
219 ELSE
220 l_context_org := p_ovdv_rec.context_org;
221 END IF;
222 IF p_ovdv_rec.context_inv_org = Okl_Api.G_MISS_NUM THEN
223 l_context_inv_org := NULL;
224 ELSE
225 l_context_inv_org := p_ovdv_rec.context_inv_org;
226 END IF;
227 IF p_ovdv_rec.context_asset_book = Okl_Api.G_MISS_CHAR THEN
228 l_context_asset_book := NULL;
229 ELSE
230 l_context_asset_book := p_ovdv_rec.context_asset_book;
231 END IF;
232
233 IF p_ovdv_rec.id = Okl_Api.G_MISS_NUM THEN
234 OPEN c1(p_ovdv_rec.orl_id,
235 p_ovdv_rec.ove_id,
236 p_ovdv_rec.context_intent,
237 l_context_org,
238 l_context_inv_org,
239 l_context_asset_book);
240 FETCH c1 INTO l_ovd_status;
241 l_row_found := c1%FOUND;
242 CLOSE c1;
243 IF l_row_found THEN
244 ---Okl_Api.set_message('OKL','OKL_COLUMN_NOT_UNIQUE', 'OKL_TABLE_NAME',l_token_1,Okl_Ovd_Pvt.G_COL_NAME_TOKEN,l_token_3);
245 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
246 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
247 p_token1 => G_TABLE_TOKEN,
248 p_token1_value => l_token_1,
249 p_token2 => G_COLUMN_TOKEN,
250 p_token2_value => l_token_3);
251
252 x_return_status := Okl_Api.G_RET_STS_ERROR;
253 RAISE G_EXCEPTION_HALT_PROCESSING;
254 END IF;
255 END IF;
256 END IF;
257 /* -- Check if the option value to which the option rules are attached is not
258 -- in the past
259 OPEN okl_ovd_ove_fk_csr (p_ovdv_rec.ove_id,
260 l_sysdate);
261 FETCH okl_ovd_ove_fk_csr INTO l_check;
262 l_row_not_found := okl_ovd_ove_fk_csr%NOTFOUND;
263 CLOSE okl_ovd_ove_fk_csr;
264
265 IF l_row_not_found = FALSE THEN
266 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
267 p_msg_name => G_PAST_RECORDS);
268 x_valid := FALSE;
269 x_return_status := OKL_API.G_RET_STS_ERROR;
270 RAISE G_EXCEPTION_HALT_PROCESSING;
271 END IF;
272
273 -- Check for related rules and contracts
274 -- Only Insert scenario
275 IF p_ovdv_rec.id = OKL_API.G_MISS_NUM THEN
276 OPEN okl_ovd_orl_fk_csr (p_ovdv_rec.ove_id,
277 p_ovdv_rec.orl_id);
278 FETCH okl_ovd_orl_fk_csr INTO l_check;
279 l_row_not_found := okl_ovd_orl_fk_csr%NOTFOUND;
280 CLOSE okl_ovd_orl_fk_csr;
281
282 IF l_row_not_found = FALSE THEN
283 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
284 p_msg_name => G_DATES_MISMATCH,
285 p_token1 => G_PARENT_TABLE_TOKEN,
286 p_token1_value => 'Okl_Opv_Rules_V',
287 p_token2 => G_CHILD_TABLE_TOKEN,
288 p_token2_value => 'Okl_Lse_Scs_Rules_V');
289 x_valid := FALSE;
290 x_return_status := OKL_API.G_RET_STS_ERROR;
291 RAISE G_EXCEPTION_HALT_PROCESSING;
292 END IF;
293 END IF;
294 */
295 EXCEPTION
296 WHEN G_EXCEPTION_HALT_PROCESSING THEN
297 -- no processing necessary; validation can continue
298 -- with the next column
299 NULL;
300
301 WHEN OTHERS THEN
302 -- store SQL error message on message stack
303 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
304 p_msg_name => G_UNEXPECTED_ERROR,
305 p_token1 => G_SQLCODE_TOKEN,
306 p_token1_value => SQLCODE,
307 p_token2 => G_SQLERRM_TOKEN,
308 p_token2_value => SQLERRM);
309 x_valid := FALSE;
310 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
311
312 IF (okl_ovd_ovt_fk_csr%ISOPEN) THEN
313 CLOSE okl_ovd_ovt_fk_csr;
314 END IF;
315
316 /*IF (okl_ovd_orl_fk_csr%ISOPEN) THEN
317 CLOSE okl_ovd_orl_fk_csr;
318 END IF;*/
319
320 IF (okl_ovd_csp_fk_csr%ISOPEN) THEN
321 CLOSE okl_ovd_csp_fk_csr;
322 END IF;
323
324 /* IF (okl_ovd_ove_fk_csr%ISOPEN) THEN
325 CLOSE okl_ovd_ove_fk_csr;
326 END IF;*/
327
328 IF (C1%ISOPEN) THEN
329 CLOSE C1;
330 END IF;
331
332 END Check_Constraints;
333
334
335 ---------------------------------------------------------------------------
336 -- PROCEDURE Validate_Orl_Id
337 ---------------------------------------------------------------------------
338 -- Start of comments
339 --
340 -- Procedure Name : Validate_Orl_Id
341 -- Description :
342 -- Business Rules :
343 -- Parameters :
344 -- Version : 1.0
345 -- End of comments
346 ---------------------------------------------------------------------------
347 PROCEDURE Validate_Orl_Id(p_ovdv_rec IN ovdv_rec_type
348 ,x_return_status OUT NOCOPY VARCHAR2)
349 IS
350 CURSOR okl_orlv_pk_csr (p_id IN NUMBER) IS
351 SELECT '1'
352 FROM okl_opt_rules_v
353 WHERE okl_opt_rules_v.id = p_id;
354
355 l_orl_status VARCHAR2(1);
356 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
357 l_row_notfound BOOLEAN := TRUE;
358 l_token_1 VARCHAR2(1999);
359
360 BEGIN
361 -- initialize return status
362 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
363
364 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTVAL_RULE_CR_UPD','OKL_RULE');
365
366 -- check for data before processing
367 IF (p_ovdv_rec.orl_id IS NULL) OR
368 (p_ovdv_rec.orl_id = Okl_Api.G_MISS_NUM) THEN
369 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ovd_Pvt.g_app_name
370 ,p_msg_name => Okl_Ovd_Pvt.g_required_value
371 ,p_token1 => Okl_Ovd_Pvt.g_col_name_token
372 ,p_token1_value => l_token_1);
373 x_return_status := Okl_Api.G_RET_STS_ERROR;
374 RAISE G_EXCEPTION_HALT_PROCESSING;
375 END IF;
376
377 IF (p_ovdv_rec.ORL_ID IS NOT NULL)
378 THEN
379 OPEN okl_orlv_pk_csr(p_ovdv_rec.ORL_ID);
380 FETCH okl_orlv_pk_csr INTO l_orl_status;
381 l_row_notfound := okl_orlv_pk_csr%NOTFOUND;
382 CLOSE okl_orlv_pk_csr;
383 IF (l_row_notfound) THEN
384 Okl_Api.set_message(Okl_Ovd_Pvt.G_APP_NAME, Okl_Ovd_Pvt.G_INVALID_VALUE,Okl_Ovd_Pvt.G_COL_NAME_TOKEN,l_token_1);
385 RAISE G_ITEM_NOT_FOUND_ERROR;
386 END IF;
387 END IF;
388
389 EXCEPTION
390 WHEN G_EXCEPTION_HALT_PROCESSING THEN
391 -- no processing necessary; validation can continue
392 -- with the next column
393 NULL;
394 WHEN G_ITEM_NOT_FOUND_ERROR THEN
395 x_return_status := Okl_Api.G_RET_STS_ERROR;
396
397 WHEN OTHERS THEN
398 -- store SQL error message on message stack for caller
399 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ovd_Pvt.g_app_name,
400 p_msg_name => Okl_Ovd_Pvt.g_unexpected_error,
401 p_token1 => Okl_Ovd_Pvt.g_sqlcode_token,
402 p_token1_value => SQLCODE,
403 p_token2 => Okl_Ovd_Pvt.g_sqlerrm_token,
404 p_token2_value => SQLERRM);
405
406 -- notify caller of an UNEXPECTED error
407 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
408
409 END Validate_Orl_Id;
410
411 ---------------------------------------------------------------------------
412 -- PROCEDURE Validate_Copy_Or_Enter_Flag
413 ---------------------------------------------------------------------------
414 -- Start of comments
415 --
416 -- Procedure Name : Validate_Copy_Or_Enter_Flag
417 -- Description :
418 -- Business Rules :
419 -- Parameters :
420 -- Version : 1.0
421 -- End of comments
422 ---------------------------------------------------------------------------
423 PROCEDURE Validate_Copy_Or_Enter_Flag(p_ovdv_rec IN ovdv_rec_type
424 ,x_return_status OUT NOCOPY VARCHAR2)
425 IS
426
427 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
428 l_token_1 VARCHAR2(1999);
429
430 BEGIN
431 -- initialize return status
432 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
433
434 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTVAL_RULE_CR_UPD','OKL_ACTION');
435 -- check for data before processing
436 l_return_status := Okl_Accounting_Util.validate_lookup_code(Okl_Ovd_Pvt.G_LOOKUP_TYPE,p_ovdv_rec.copy_or_enter_flag);
437
438 IF l_return_status = Okl_Api.G_FALSE THEN
439 l_return_status := Okl_Api.G_RET_STS_ERROR;
440 END IF;
441
442
443
444 IF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
445 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ovd_Pvt.g_app_name
446 ,p_msg_name => Okl_Ovd_Pvt.g_required_value
447 ,p_token1 => Okl_Ovd_Pvt.g_col_name_token
448 ,p_token1_value => l_token_1);
449 x_return_status := Okl_Api.G_RET_STS_ERROR;
450 RAISE G_EXCEPTION_HALT_PROCESSING;
451 END IF;
452
453 EXCEPTION
454 WHEN G_EXCEPTION_HALT_PROCESSING THEN
455 -- no processing neccessary; validation can continue
456 -- with the next column
457 NULL;
458
459 WHEN OTHERS THEN
460 -- store SQL error message on message stack for caller
461 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ovd_Pvt.g_app_name,
462 p_msg_name => Okl_Ovd_Pvt.g_unexpected_error,
463 p_token1 => Okl_Ovd_Pvt.g_sqlcode_token,
464 p_token1_value => SQLCODE,
465 p_token2 => Okl_Ovd_Pvt.g_sqlerrm_token,
466 p_token2_value => SQLERRM);
467
468 -- notify caller of an UNEXPECTED error
469 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
470
471 END Validate_Copy_Or_Enter_Flag;
472
473 ---------------------------------------------------------------------------
474 -- PROCEDURE Validate_Context_Intent
475 ---------------------------------------------------------------------------
476 -- Start of comments
477 --
478 -- Procedure Name : Validate_Context_Intent
479 -- Description :
480 -- Business Rules :
481 -- Parameters :
482 -- Version : 1.0
483 -- End of comments
484 ---------------------------------------------------------------------------
485 PROCEDURE Validate_Context_Intent(p_ovdv_rec IN ovdv_rec_type
486 ,x_return_status OUT NOCOPY VARCHAR2)
487 IS
488
489 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
490 l_token_1 VARCHAR2(999);
491
492 BEGIN
493 -- initialize return status
494 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
495
496 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_OPTVAL_RULE_CR_UPD','OKL_INTENT');
497 -- check for data before processing
498 l_return_status := Okl_Accounting_Util.validate_lookup_code(Okl_Ovd_Pvt.G_INTENT_TYPE,p_ovdv_rec.context_intent);
499 IF l_return_status = Okl_Api.G_FALSE THEN
500 l_return_status := Okl_Api.G_RET_STS_ERROR;
501 END IF;
502
503
504 IF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
505 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ovd_Pvt.g_app_name
506 ,p_msg_name => Okl_Ovd_Pvt.g_required_value
507 ,p_token1 => Okl_Ovd_Pvt.g_col_name_token
508 ,p_token1_value => l_token_1);
509 x_return_status := Okl_Api.G_RET_STS_ERROR;
510 RAISE G_EXCEPTION_HALT_PROCESSING;
511 END IF;
512
513 EXCEPTION
514 WHEN G_EXCEPTION_HALT_PROCESSING THEN
515 -- no processing neccessary; validation can continue
516 -- with the next column
517 NULL;
518
519 WHEN OTHERS THEN
520 -- store SQL error message on message stack for caller
521 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ovd_Pvt.g_app_name,
522 p_msg_name => Okl_Ovd_Pvt.g_unexpected_error,
523 p_token1 => Okl_Ovd_Pvt.g_sqlcode_token,
524 p_token1_value => SQLCODE,
525 p_token2 => Okl_Ovd_Pvt.g_sqlerrm_token,
526 p_token2_value => SQLERRM);
527
528 -- notify caller of an UNEXPECTED error
529 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
530
531 END Validate_Context_Intent;
532
533 ---------------------------------------------------------------------------
534 -- FUNCTION Validate_Attributes
535 ---------------------------------------------------------------------------
536 -- Start of comments
537 --
538 -- Function Name : Validate_Attributes
539 -- Description :
540 -- Business Rules :
541 -- Parameters :
542 -- Version : 1.0
543 -- End of comments
544 ---------------------------------------------------------------------------
545
546 FUNCTION Validate_Attributes (
547 p_ovdv_rec IN ovdv_rec_type
548 ) RETURN VARCHAR2 IS
549 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
550 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
551 BEGIN
552 -- Validate_Orl_Id
553 Validate_Orl_Id(p_ovdv_rec,x_return_status);
554 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
555 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
556 -- need to leave
557 l_return_status := x_return_status;
558 RAISE G_EXCEPTION_HALT_PROCESSING;
559 ELSE
560 -- record that there was an error
561 l_return_status := x_return_status;
562 END IF;
563 END IF;
564
565 -- Validate_Copy_Or_Enter_Flag
566 Validate_Copy_Or_Enter_Flag(p_ovdv_rec,x_return_status);
567 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
568 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
569 -- need to leave
570 l_return_status := x_return_status;
571 RAISE G_EXCEPTION_HALT_PROCESSING;
572 ELSE
573 -- record that there was an error
574 l_return_status := x_return_status;
575 END IF;
576 END IF;
577
578 -- Validate_Context_Intent
579 Validate_Context_Intent(p_ovdv_rec,x_return_status);
580 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
581 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
582 -- need to leave
583 l_return_status := x_return_status;
584 RAISE G_EXCEPTION_HALT_PROCESSING;
585 ELSE
586 -- record that there was an error
587 l_return_status := x_return_status;
588 END IF;
589 END IF;
590
591 RETURN(l_return_status);
592 EXCEPTION
593 WHEN G_EXCEPTION_HALT_PROCESSING THEN
594 -- just come out with return status
595 NULL;
596 RETURN (l_return_status);
597
598 WHEN OTHERS THEN
599 -- store SQL error message on message stack for caller
600 Okl_Api.SET_MESSAGE(p_app_name => g_app_name,
601 p_msg_name => g_unexpected_error,
602 p_token1 => g_sqlcode_token,
603 p_token1_value => SQLCODE,
604 p_token2 => g_sqlerrm_token,
605 p_token2_value => SQLERRM);
606 -- notify caller of an UNEXPECTED error
607 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
608 RETURN(l_return_status);
609
610 END Validate_Attributes;
611
612
613 ---------------------------------------------------------------------------
614 -- PROCEDURE insert_overules for: OKL_OPV_RULES_V
615 ---------------------------------------------------------------------------
616 PROCEDURE insert_overules(p_api_version IN NUMBER,
617 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
618 x_return_status OUT NOCOPY VARCHAR2,
619 x_msg_count OUT NOCOPY NUMBER,
620 x_msg_data OUT NOCOPY VARCHAR2,
621 p_optv_rec IN optv_rec_type,
622 p_ovev_rec IN ovev_rec_type,
623 p_ovdv_rec IN ovdv_rec_type,
624 x_ovdv_rec OUT NOCOPY ovdv_rec_type
625 ) IS
626 l_api_version CONSTANT NUMBER := 1;
627 l_api_name CONSTANT VARCHAR2(30) := 'insert_overules';
628 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
629 l_valid BOOLEAN;
630 l_ovdv_rec ovdv_rec_type;
631 BEGIN
632 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
633 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
634 p_pkg_name => G_PKG_NAME,
635 p_init_msg_list => p_init_msg_list,
636 l_api_version => l_api_version,
637 p_api_version => p_api_version,
638 p_api_type => '_PVT',
639 x_return_status => l_return_status);
640 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
641 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
642 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
643 RAISE Okl_Api.G_EXCEPTION_ERROR;
644 END IF;
645
646 l_ovdv_rec := p_ovdv_rec;
647
648
649 --- Validate all non-missing attributes (Item Level Validation)
650 l_return_status := Validate_Attributes(l_ovdv_rec);
651 --- If any errors happen abort API
652 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
653 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
655 RAISE Okl_Api.G_EXCEPTION_ERROR;
656 END IF;
657
658 /* call check_constraints to check the validity of this relationship */
659 Check_Constraints(p_ovdv_rec => l_ovdv_rec,
660 x_return_status => l_return_status,
661 x_valid => l_valid);
662 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
663 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
664 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
665 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
666 l_valid <> TRUE) THEN
667 x_return_status := Okl_Api.G_RET_STS_ERROR;
668 RAISE Okl_Api.G_EXCEPTION_ERROR;
669 END IF;
670
671 /* public api to insert option value rules */
672 Okl_Option_Rules_Pub.create_option_val_rules(p_api_version => p_api_version,
673 p_init_msg_list => p_init_msg_list,
674 x_return_status => l_return_status,
675 x_msg_count => x_msg_count,
676 x_msg_data => x_msg_data,
677 p_ovdv_rec => l_ovdv_rec,
678 x_ovdv_rec => x_ovdv_rec);
679
680 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
681 RAISE Okl_Api.G_EXCEPTION_ERROR;
682 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
683 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
684 END IF;
685 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
686 x_msg_data => x_msg_data);
687 EXCEPTION
688 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
689 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
690 p_pkg_name => G_PKG_NAME,
691 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
692 x_msg_count => x_msg_count,
693 x_msg_data => x_msg_data,
694 p_api_type => '_PVT');
695 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
696 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
697 p_pkg_name => G_PKG_NAME,
698 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
699 x_msg_count => x_msg_count,
700 x_msg_data => x_msg_data,
701 p_api_type => '_PVT');
702 WHEN OTHERS THEN
703 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
704 p_pkg_name => G_PKG_NAME,
705 p_exc_name => 'OTHERS',
706 x_msg_count => x_msg_count,
707 x_msg_data => x_msg_data,
708 p_api_type => '_PVT');
709
710 END insert_overules;
711
712 ---------------------------------------------------------------------------
713 -- PROCEDURE delete_overules for: OKL_OPV_RULES_V
714 -- This allows the user to delete table of records
715 ---------------------------------------------------------------------------
716 PROCEDURE delete_overules(p_api_version IN NUMBER,
717 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
718 x_return_status OUT NOCOPY VARCHAR2,
719 x_msg_count OUT NOCOPY NUMBER,
720 x_msg_data OUT NOCOPY VARCHAR2,
721 p_optv_rec IN optv_rec_type,
722 p_ovev_rec IN ovev_rec_type,
723 p_ovdv_tbl IN ovdv_tbl_type
724 ) IS
725 l_api_version CONSTANT NUMBER := 1;
726 l_ovdv_tbl ovdv_tbl_type;
727 l_api_name CONSTANT VARCHAR2(30) := 'delete_overules';
728 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
729 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
730 l_valid BOOLEAN;
731 i NUMBER;
732
733 BEGIN
734 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
735 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
736 p_pkg_name => G_PKG_NAME,
737 p_init_msg_list => p_init_msg_list,
738 l_api_version => l_api_version,
739 p_api_version => p_api_version,
740 p_api_type => '_PVT',
741 x_return_status => l_return_status);
742 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
743 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
744 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
745 RAISE Okl_Api.G_EXCEPTION_ERROR;
746 END IF;
747
748 l_ovdv_tbl := p_ovdv_tbl;
749 IF (l_ovdv_tbl.COUNT > 0) THEN
750 i := l_ovdv_tbl.FIRST;
751 LOOP
752 Check_Constraints(p_ovdv_rec => l_ovdv_tbl(i),
753 x_return_status => l_return_status,
754 x_valid => l_valid);
755
756 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
757 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
758 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
759 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
760 l_valid <> TRUE) THEN
761 x_return_status := Okl_Api.G_RET_STS_ERROR;
762 RAISE Okl_Api.G_EXCEPTION_ERROR;
763 END IF;
764
765 EXIT WHEN (i = l_ovdv_tbl.LAST);
766
767 i := l_ovdv_tbl.NEXT(i);
768
769 END LOOP;
770 END IF;
771
772 /* public api to delete option value rules */
773 Okl_Option_Rules_Pub.delete_option_val_rules(p_api_version => p_api_version,
774 p_init_msg_list => p_init_msg_list,
775 x_return_status => l_return_status,
776 x_msg_count => x_msg_count,
777 x_msg_data => x_msg_data,
778 p_ovdv_tbl => l_ovdv_tbl);
779
780 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
781 RAISE Okl_Api.G_EXCEPTION_ERROR;
782 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
783 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
784 END IF;
785
786 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
787 x_msg_data => x_msg_data);
788 EXCEPTION
789 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
790 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
791 p_pkg_name => G_PKG_NAME,
792 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
793 x_msg_count => x_msg_count,
794 x_msg_data => x_msg_data,
795 p_api_type => '_PVT');
796 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
797 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
798 p_pkg_name => G_PKG_NAME,
799 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
800 x_msg_count => x_msg_count,
801 x_msg_data => x_msg_data,
802 p_api_type => '_PVT');
803 WHEN OTHERS THEN
804 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
805 p_pkg_name => G_PKG_NAME,
806 p_exc_name => 'OTHERS',
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data,
809 p_api_type => '_PVT');
810
811 END delete_overules;
812
813 END Okl_Setupoverules_Pvt;