[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPOPTRULES_PVT
Source
1 PACKAGE BODY Okl_Setupoptrules_Pvt AS
2 /* $Header: OKLRSORB.pls 115.8 2003/07/23 18:33:03 sgorantl noship $ */
3 G_COLUMN_TOKEN CONSTANT VARCHAR2(100) := 'COLUMN';
4
5 ---------------------------------------------------------------------------
6 -- PROCEDURE get_rec for: OKL_OPT_RULES_V
7 ---------------------------------------------------------------------------
8 PROCEDURE get_rec (
9 p_orlv_rec IN orlv_rec_type,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_no_data_found OUT NOCOPY BOOLEAN,
12 x_orlv_rec OUT NOCOPY orlv_rec_type
13 ) IS
14 CURSOR okl_orlv_pk_csr (p_id IN NUMBER) IS
15 SELECT
16 ID,
17 OBJECT_VERSION_NUMBER,
18 OPT_ID,
19 NVL(LRG_LSE_ID, Okl_Api.G_MISS_NUM) LRG_LSE_ID,
20 NVL(LRG_SRD_ID, Okl_Api.G_MISS_NUM) LRG_SRD_ID,
21 NVL(SRD_ID_FOR, Okl_Api.G_MISS_NUM) SRD_ID_FOR,
22 RGR_RGD_CODE,
23 RGR_RDF_CODE,
24 NVL(OVERALL_INSTRUCTIONS,Okl_Api.G_MISS_CHAR) OVERALL_INSTRUCTIONS,
25 CREATED_BY,
26 CREATION_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_DATE,
29 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
30 FROM Okl_Opt_Rules_V
31 WHERE okl_Opt_Rules_V.id = p_id;
32 l_okl_orlv_pk okl_orlv_pk_csr%ROWTYPE;
33 l_orlv_rec orlv_rec_type;
34 BEGIN
35 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
36 x_no_data_found := TRUE;
37
38 -- Get current database values
39 OPEN okl_orlv_pk_csr (p_orlv_rec.id);
40 FETCH okl_orlv_pk_csr INTO
41 l_orlv_rec.ID,
42 l_orlv_rec.OBJECT_VERSION_NUMBER,
43 l_orlv_rec.OPT_ID,
44 l_orlv_rec.LRG_LSE_ID,
45 l_orlv_rec.LRG_SRD_ID,
46 l_orlv_rec.SRD_ID_FOR,
47 l_orlv_rec.RGR_RGD_CODE,
48 l_orlv_rec.RGR_RDF_CODE,
49 l_orlv_rec.OVERALL_INSTRUCTIONS,
50 l_orlv_rec.CREATED_BY,
51 l_orlv_rec.CREATION_DATE,
52 l_orlv_rec.LAST_UPDATED_BY,
53 l_orlv_rec.LAST_UPDATE_DATE,
54 l_orlv_rec.LAST_UPDATE_LOGIN;
55 x_no_data_found := okl_orlv_pk_csr%NOTFOUND;
56 CLOSE okl_orlv_pk_csr;
57 x_orlv_rec := l_orlv_rec;
58 EXCEPTION
59 WHEN OTHERS THEN
60 -- store SQL error message on message stack
61 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
62 p_msg_name => G_UNEXPECTED_ERROR,
63 p_token1 => G_SQLCODE_TOKEN,
64 p_token1_value => SQLCODE,
65 p_token2 => G_SQLERRM_TOKEN,
66 p_token2_value => SQLERRM);
67 -- notify UNEXPECTED error for calling API.
68 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
69
70 IF (okl_orlv_pk_csr%ISOPEN) THEN
71 CLOSE okl_orlv_pk_csr;
72 END IF;
73
74 END get_rec;
75
76 ---------------------------------------------------------------------------
77 -- PROCEDURE check_constraints for: OKL_OPT_RULES_V
78 -- To verify whether the dates are valid with respect to options and
79 -- to check whether any of these selected rules are attached to option
80 -- values
81 ---------------------------------------------------------------------------
82 PROCEDURE Check_Constraints (
83 p_orlv_rec IN orlv_rec_type,
84 x_return_status OUT NOCOPY VARCHAR2,
85 x_valid OUT NOCOPY BOOLEAN
86 ) IS
87 CURSOR okl_orl_pon_fk_csr (p_opt_id IN Okl_Options_V.ID%TYPE
88 ) IS
89 SELECT '1'
90 FROM Okl_Pdt_Opts_V pon
91 WHERE pon.OPT_ID = p_opt_id;
92
93 CURSOR okl_orl_ovd_fk_csr (p_orl_id IN Okl_Opt_Rules_V.ID%TYPE
94 ) IS
95 SELECT '1'
96 FROM Okl_Opv_Rules_V ovd
97 WHERE ovd.ORL_ID = p_orl_id;
98
99 CURSOR okl_orl_opt_fk_csr (p_opt_id IN Okl_Options_V.ID%TYPE,
100 p_date IN Okl_Options_V.TO_DATE%TYPE
101 ) IS
102 SELECT '1'
103 FROM Okl_Options_V opt
104 WHERE opt.ID = p_opt_id
105 AND NVL(opt.TO_DATE, p_date) < p_date;
106
107 CURSOR okl_orl_lsr_fk_csr (p_opt_id IN Okl_Opt_Rules_V.opt_id%TYPE,
108 p_lrg_lse_id IN Okl_Opt_Rules_V.lrg_lse_id%TYPE,
109 p_lrg_srd_id IN Okl_Opt_Rules_V.lrg_srd_id%TYPE,
110 p_srd_id_for IN Okl_Opt_Rules_V.srd_id_for%TYPE,
111 p_rgr_rgd_code IN Okl_Opt_Rules_V.rgr_rgd_code%TYPE,
112 p_rgr_rdf_code IN Okl_Opt_Rules_V.rgr_rdf_code%TYPE
113 ) IS
114 SELECT '1'
115 FROM Okl_Lse_Scs_Rules_V lsr,
116 Okl_Options_V opt
117 WHERE opt.ID = p_opt_id
118 AND ((p_lrg_lse_id <> Okl_Api.G_MISS_NUM
119 AND lsr.LSE_ID = p_lrg_lse_id
120 AND lsr.SRD_ID = p_lrg_srd_id)
121 OR
122 (p_lrg_lse_id = Okl_Api.G_MISS_NUM
123 AND lsr.LSE_ID IS NULL
124 AND lsr.SRD_ID = p_srd_id_for))
125 AND lsr.RULE_GROUP = p_rgr_rgd_code
126 AND lsr.RULE = p_rgr_rdf_code
127 AND (lsr.START_DATE > opt.FROM_DATE OR
128 NVL(lsr.END_DATE, NVL(opt.TO_DATE, Okl_Api.G_MISS_DATE)) < NVL(opt.TO_DATE, Okl_Api.G_MISS_DATE));
129
130 CURSOR c1(p_opt_id okl_opt_rules_v.opt_id%TYPE,
131 p_rgr_rgd_code okl_opt_rules_v.rgr_rgd_code%TYPE,
132 p_rgr_rdf_code okl_opt_rules_v.rgr_rdf_code%TYPE,
133 p_srd_id_for okl_opt_rules_v.srd_id_for%TYPE) IS
134 SELECT '1'
135 FROM okl_opt_rules_v
136 WHERE opt_id = p_opt_id
137 AND rgr_rgd_code = p_rgr_rgd_code
138 AND rgr_rdf_code = rgr_rdf_code
139 AND srd_id_for = p_srd_id_for
140 AND id <> NVL(p_orlv_rec.id,-9999);
141
142 CURSOR c2(p_opt_id okl_opt_rules_v.opt_id%TYPE,
143 p_rgr_rgd_code okl_opt_rules_v.rgr_rgd_code%TYPE,
144 p_rgr_rdf_code okl_opt_rules_v.rgr_rdf_code%TYPE,
145 p_lrg_lse_id okl_opt_rules_v.lrg_lse_id%TYPE,
146 p_lrg_srd_id okl_opt_rules_v.lrg_srd_id%TYPE) IS
147 SELECT '1'
148 FROM okl_opt_rules_v
149 WHERE opt_id = p_opt_id
150 AND rgr_rgd_code = p_rgr_rgd_code
151 AND rgr_rdf_code = rgr_rdf_code
152 AND lrg_lse_id = p_lrg_lse_id
153 AND lrg_srd_id = p_lrg_srd_id
154 AND id <> NVL(p_orlv_rec.id,-9999);
155
156 l_orlv_rec orlv_rec_type;
157 l_check VARCHAR2(1) := '?';
158 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
159 l_row_not_found BOOLEAN := FALSE;
160 l_unq_tbl Okc_Util.unq_tbl_type;
161 l_orl_status VARCHAR2(1);
162 l_row_found BOOLEAN := FALSE;
163 l_token_1 VARCHAR2(1999);
164 l_token_2 VARCHAR2(1999);
165 l_token_3 VARCHAR2(1999);
166 l_token_4 VARCHAR2(1999);
167 BEGIN
168 x_valid := TRUE;
169 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
170
171 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_RULE_SERCH',
172 p_attribute_code => 'OKL_OPTION_RULES');
173
174 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_OPTION_SERCH',
175 p_attribute_code => 'OKL_PRODUCT_OPTIONS');
176
177 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPT_VAL_RULE_SUMRY',
178 p_attribute_code => 'OKL_OPTION_VALUE_RULES');
179
180
181 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPT_VAL_RULE_SUMRY',
182 p_attribute_code => 'OKL_RULE');
183
184 -- Check for related products being used by contracts
185 OPEN okl_orl_pon_fk_csr (p_orlv_rec.opt_id);
186 FETCH okl_orl_pon_fk_csr INTO l_check;
187 l_row_not_found := okl_orl_pon_fk_csr%NOTFOUND;
188 CLOSE okl_orl_pon_fk_csr;
189
190 IF l_row_not_found = FALSE THEN
191 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
192 p_msg_name => G_IN_USE,
193 p_token1 => G_PARENT_TABLE_TOKEN,
194 p_token1_value => l_token_1,
195 p_token2 => G_CHILD_TABLE_TOKEN,
196 p_token2_value => l_token_2);
197 x_valid := FALSE;
198 x_return_status := Okl_Api.G_RET_STS_ERROR;
199 RAISE G_EXCEPTION_HALT_PROCESSING;
200 END IF;
201
202 -- Check if the option to which the rules are added is not
203 -- in the past
204 /*OPEN okl_orl_opt_fk_csr (p_orlv_rec.opt_id,
205 l_sysdate);
206 FETCH okl_orl_opt_fk_csr INTO l_check;
207 l_row_not_found := okl_orl_opt_fk_csr%NOTFOUND;
208 CLOSE okl_orl_opt_fk_csr;
209
210 IF l_row_not_found = FALSE THEN
211 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
212 p_msg_name => G_PAST_RECORDS);
213 x_valid := FALSE;
214 x_return_status := Okl_Api.G_RET_STS_ERROR;
215 RAISE G_EXCEPTION_HALT_PROCESSING;
216 END IF;*/
217
218 -- Check for option value rules
219 IF p_orlv_rec.id <> Okl_Api.G_MISS_NUM THEN
220 OPEN okl_orl_ovd_fk_csr (p_orlv_rec.id);
221 FETCH okl_orl_ovd_fk_csr INTO l_check;
222 l_row_not_found := okl_orl_ovd_fk_csr%NOTFOUND;
223 CLOSE okl_orl_ovd_fk_csr;
224
225 IF l_row_not_found = FALSE THEN
226 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
227 p_msg_name => G_IN_USE,
228 p_token1 => G_PARENT_TABLE_TOKEN,
229 p_token1_value => l_token_1,
230 p_token2 => G_CHILD_TABLE_TOKEN,
231 p_token2_value => l_token_3);
232 x_valid := FALSE;
233 x_return_status := Okl_Api.G_RET_STS_ERROR;
234 RAISE G_EXCEPTION_HALT_PROCESSING;
235 END IF;
236 END IF;
237 IF p_orlv_rec.id = Okl_Api.G_MISS_NUM THEN
238 -- check for unique record
239 IF (p_orlv_rec.srd_id_for IS NOT NULL) THEN
240 OPEN c1(p_orlv_rec.opt_id,
241 p_orlv_rec.rgr_rgd_code,
242 p_orlv_rec.rgr_rdf_code,
243 p_orlv_rec.srd_id_for);
244 FETCH c1 INTO l_orl_status;
245 l_row_found := c1%FOUND;
246 CLOSE c1;
247 IF l_row_found THEN
248 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
249 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
250 p_token1 => G_TABLE_TOKEN,
251 p_token1_value => l_token_1,
252 p_token2 => G_COLUMN_TOKEN,
253 p_token2_value => l_token_4);
254
255 /* --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);
256
257 --Okl_Api.set_message(G_APP_NAME,'OKL_COLUMN_NOT_UNIQUE',G_TABLE_TOKEN, l_token_1,Okl_Ovd_Pvt.G_COL_NAME_TOKEN,l_token_4); ---CHG001
258 */
259 x_valid := FALSE;
260 x_return_status := Okl_Api.G_RET_STS_ERROR;
261 RAISE G_EXCEPTION_HALT_PROCESSING;
262 END IF;
263 ELSE
264 OPEN c2(p_orlv_rec.opt_id,
265 p_orlv_rec.rgr_rgd_code,
266 p_orlv_rec.rgr_rdf_code,
267 p_orlv_rec.lrg_lse_id,
268 p_orlv_rec.lrg_srd_id);
269 FETCH c2 INTO l_orl_status;
270 l_row_found := c2%FOUND;
271 CLOSE c2;
272 IF l_row_found THEN
273 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
274 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
275 p_token1 => G_TABLE_TOKEN,
276 p_token1_value => l_token_1,
277 p_token2 => G_COLUMN_TOKEN,
278 p_token2_value => l_token_4);
279 x_valid := FALSE;
280 x_return_status := Okl_Api.G_RET_STS_ERROR;
281 RAISE G_EXCEPTION_HALT_PROCESSING;
282 END IF;
283 END IF;
284 END IF;
285 -- Check for rules dates
286 IF p_orlv_rec.id = Okl_Api.G_MISS_NUM THEN
287 OPEN okl_orl_lsr_fk_csr (p_orlv_rec.opt_id,
288 p_orlv_rec.lrg_lse_id,
289 p_orlv_rec.lrg_srd_id,
290 p_orlv_rec.srd_id_for,
291 p_orlv_rec.rgr_rgd_code,
292 p_orlv_rec.rgr_rdf_code);
293 FETCH okl_orl_lsr_fk_csr INTO l_check;
294 l_row_not_found := okl_orl_lsr_fk_csr%NOTFOUND;
295 CLOSE okl_orl_lsr_fk_csr;
296
297 IF l_row_not_found = FALSE THEN
298 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
299 p_msg_name => G_DATES_MISMATCH,
300 p_token1 => G_PARENT_TABLE_TOKEN,
301 p_token1_value => l_token_1,
302 p_token2 => G_CHILD_TABLE_TOKEN,
303 p_token2_value => l_token_3);
304 x_valid := FALSE;
305 x_return_status := Okl_Api.G_RET_STS_ERROR;
306 RAISE G_EXCEPTION_HALT_PROCESSING;
307 END IF;
308 END IF;
309
310 EXCEPTION
311 WHEN G_EXCEPTION_HALT_PROCESSING THEN
312 -- no processing necessary; validation can continue
313 -- with the next column
314 NULL;
315
316 WHEN OTHERS THEN
317 -- store SQL error message on message stack
318 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
319 p_msg_name => G_UNEXPECTED_ERROR,
320 p_token1 => G_SQLCODE_TOKEN,
321 p_token1_value => SQLCODE,
322 p_token2 => G_SQLERRM_TOKEN,
323 p_token2_value => SQLERRM);
324 x_valid := FALSE;
325 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
326
327 IF (okl_orl_pon_fk_csr%ISOPEN) THEN
328 CLOSE okl_orl_pon_fk_csr;
329 END IF;
330
331 IF (okl_orl_opt_fk_csr%ISOPEN) THEN
332 CLOSE okl_orl_opt_fk_csr;
333 END IF;
334
335 IF (okl_orl_ovd_fk_csr%ISOPEN) THEN
336 CLOSE okl_orl_ovd_fk_csr;
337 END IF;
338
339 IF (okl_orl_lsr_fk_csr%ISOPEN) THEN
340 CLOSE okl_orl_lsr_fk_csr;
341 END IF;
342
343 IF (C1%ISOPEN) THEN
344 CLOSE C1;
345 END IF;
346
347 IF (C1%ISOPEN) THEN
348 CLOSE C2;
349 END IF;
350
351 END Check_Constraints;
352
353 ---------------------------------------------------------------------------
354 -- PROCEDURE Validate_Rgr_Rdf_Code
355 ---------------------------------------------------------------------------
356 -- Procedure Name : Validate_Rgr_Rdf_Code
357 -- Description :
358 -- Business Rules :
359 -- Parameters :
360 -- Version : 1.0
361 ---------------------------------------------------------------------------
362 PROCEDURE Validate_Rgr_Rdf_Code(p_orlv_rec IN orlv_rec_type
363 ,x_return_status OUT NOCOPY VARCHAR2)
364 IS
365
366 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
367 l_token_1 VARCHAR2(1999);
368
369 BEGIN
370 -- initialize return status
371 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
372
373 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPT_VAL_RULE_SUMRY',
374 p_attribute_code => 'OKL_RULE');
375
376 -- check for data before processing
377 IF (p_orlv_rec.rgr_rdf_code IS NULL) OR
378 (p_orlv_rec.rgr_rdf_code = Okl_Api.G_MISS_CHAR) THEN
379 Okl_Api.SET_MESSAGE(p_app_name => Okl_Orl_Pvt.g_app_name
380 ,p_msg_name => Okl_Orl_Pvt.g_required_value
381 ,p_token1 => Okl_Orl_Pvt.g_col_name_token
382 ,p_token1_value => l_token_1);
383 x_return_status := Okl_Api.G_RET_STS_ERROR;
384 RAISE G_EXCEPTION_HALT_PROCESSING;
385 END IF;
386
387 EXCEPTION
388 WHEN G_EXCEPTION_HALT_PROCESSING THEN
389 -- no processing necessary; validation can continue
390 -- with the next column
391 NULL;
392
393 WHEN OTHERS THEN
394 -- store SQL error message on message stack for caller
395 Okl_Api.SET_MESSAGE(p_app_name => Okl_Orl_Pvt.g_app_name,
396 p_msg_name => Okl_Orl_Pvt.g_unexpected_error,
397 p_token1 => Okl_Orl_Pvt.g_sqlcode_token,
398 p_token1_value => SQLCODE,
399 p_token2 => Okl_Orl_Pvt.g_sqlerrm_token,
400 p_token2_value => SQLERRM);
401
402 -- notify caller of an UNEXPECTED error
403 x_return_status := Okc_Api.G_RET_STS_UNEXP_ERROR;
404
405 END Validate_Rgr_Rdf_Code;
406
407 ---------------------------------------------------------------------------
408 -- FUNCTION Validate_Foreign_Keys
409 ---------------------------------------------------------------------------
410 -- Function Name : Validate_Foreign_Keys
411 -- Description :
412 -- Business Rules :
413 -- Parameters :
414 -- Version : 1.0
415 ---------------------------------------------------------------------------
416 FUNCTION Validate_Attributes (
417 p_orlv_rec IN orlv_rec_type
418 ) RETURN VARCHAR2 IS
419 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
420 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
421 BEGIN
422
423 -- Validate_Rgr_Rdf_Code
424 Validate_Rgr_Rdf_Code(p_orlv_rec, x_return_status);
425
426 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
427 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
428 -- need to leave
429 l_return_status := x_return_status;
430 RAISE G_EXCEPTION_HALT_PROCESSING;
431 ELSE
432 -- record that there was an error
433 l_return_status := x_return_status;
434 END IF;
435 END IF;
436
437 RETURN(l_return_status);
438 EXCEPTION
439 WHEN G_EXCEPTION_HALT_PROCESSING THEN
440 -- just come out with return status
441 NULL;
442 RETURN (l_return_status);
443
444 WHEN OTHERS THEN
445 -- store SQL error message on message stack for caller
446 Okl_Api.SET_MESSAGE(p_app_name => Okl_Orl_Pvt.g_app_name,
447 p_msg_name => Okl_Orl_Pvt.g_unexpected_error,
448 p_token1 => Okl_Orl_Pvt.g_sqlcode_token,
449 p_token1_value => SQLCODE,
450 p_token2 => Okl_Orl_Pvt.g_sqlerrm_token,
451 p_token2_value => SQLERRM);
452 -- notify caller of an UNEXPECTED error
453 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
454 RETURN(l_return_status);
455
456 END Validate_Attributes;
457
458 ---------------------------------------------------------------------------
459 -- PROCEDURE insert_optrules for: OKL_OPT_RULES_V
460 ---------------------------------------------------------------------------
461 PROCEDURE insert_optrules(p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
463 x_return_status OUT NOCOPY VARCHAR2,
464 x_msg_count OUT NOCOPY NUMBER,
465 x_msg_data OUT NOCOPY VARCHAR2,
466 p_optv_rec IN optv_rec_type,
467 p_orlv_rec IN orlv_rec_type,
468 x_orlv_rec OUT NOCOPY orlv_rec_type
469 ) IS
470 l_api_version CONSTANT NUMBER := 1;
471 l_api_name CONSTANT VARCHAR2(30) := 'insert_optrules';
472 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
473 l_valid BOOLEAN;
474 l_orlv_rec orlv_rec_type;
475 BEGIN
476 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
477
478 l_orlv_rec := p_orlv_rec;
479 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
480 p_pkg_name => G_PKG_NAME,
481 p_init_msg_list => p_init_msg_list,
482 l_api_version => l_api_version,
483 p_api_version => p_api_version,
484 p_api_type => '_PVT',
485 x_return_status => l_return_status);
486 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
487 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
488 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
489 RAISE Okl_Api.G_EXCEPTION_ERROR;
490 END IF;
491
492 l_return_status := Validate_Attributes(l_orlv_rec);
493 --- If any errors happen abort API
494 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
495 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
496 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
497 RAISE Okl_Api.G_EXCEPTION_ERROR;
498 END IF;
499
500 /* call check_constraints to check the validity of this relationship */
501 Check_Constraints(p_orlv_rec => l_orlv_rec,
502 x_return_status => l_return_status,
503 x_valid => l_valid);
504
505 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
506 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
507 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
508 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
509 l_valid <> TRUE) THEN
510 x_return_status := Okl_Api.G_RET_STS_ERROR;
511 RAISE Okl_Api.G_EXCEPTION_ERROR;
512 END IF;
513
514 /* public api to insert option rules */
515 Okl_Option_Rules_Pub.create_option_rules(p_api_version => p_api_version,
516 p_init_msg_list => p_init_msg_list,
517 x_return_status => l_return_status,
518 x_msg_count => x_msg_count,
519 x_msg_data => x_msg_data,
520 p_orlv_rec => l_orlv_rec,
521 x_orlv_rec => x_orlv_rec);
522
523 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
524 RAISE Okl_Api.G_EXCEPTION_ERROR;
525 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
526 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
527 END IF;
528
529 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
530 x_msg_data => x_msg_data);
531 EXCEPTION
532 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
533 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
534 p_pkg_name => G_PKG_NAME,
535 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
536 x_msg_count => x_msg_count,
537 x_msg_data => x_msg_data,
538 p_api_type => '_PVT');
539 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
540 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
541 p_pkg_name => G_PKG_NAME,
542 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
543 x_msg_count => x_msg_count,
544 x_msg_data => x_msg_data,
545 p_api_type => '_PVT');
546 WHEN OTHERS THEN
547 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
548 p_pkg_name => G_PKG_NAME,
549 p_exc_name => 'OTHERS',
550 x_msg_count => x_msg_count,
551 x_msg_data => x_msg_data,
552 p_api_type => '_PVT');
553
554 END insert_optrules;
555
556 ---------------------------------------------------------------------------
557 -- PROCEDURE delete_optrules for: OKL_OPT_RULES_V
558 -- This allows the user to delete table of records
559 ---------------------------------------------------------------------------
560 PROCEDURE delete_optrules(p_api_version IN NUMBER,
561 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
562 x_return_status OUT NOCOPY VARCHAR2,
563 x_msg_count OUT NOCOPY NUMBER,
564 x_msg_data OUT NOCOPY VARCHAR2,
565 p_optv_rec IN optv_rec_type,
566 p_orlv_tbl IN orlv_tbl_type
567 ) IS
568 l_api_version CONSTANT NUMBER := 1;
569 l_orlv_tbl orlv_tbl_type;
570 l_api_name CONSTANT VARCHAR2(30) := 'delete_optrules';
571 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
572 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
573 l_valid BOOLEAN;
574 i NUMBER;
575
576 BEGIN
577 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
578 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
579 p_pkg_name => G_PKG_NAME,
580 p_init_msg_list => p_init_msg_list,
581 l_api_version => l_api_version,
582 p_api_version => p_api_version,
583 p_api_type => '_PVT',
584 x_return_status => l_return_status);
585 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
586 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
587 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
588 RAISE Okl_Api.G_EXCEPTION_ERROR;
589 END IF;
590
591 l_orlv_tbl := p_orlv_tbl;
592 IF (l_orlv_tbl.COUNT > 0) THEN
593 i := l_orlv_tbl.FIRST;
594 LOOP
595 Check_Constraints(p_orlv_rec => l_orlv_tbl(i),
596 x_return_status => l_return_status,
597 x_valid => l_valid);
598
599 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
600 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
601 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
602 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
603 l_valid <> TRUE) THEN
604 x_return_status := Okl_Api.G_RET_STS_ERROR;
605 RAISE Okl_Api.G_EXCEPTION_ERROR;
606 END IF;
607
608 EXIT WHEN (i = l_orlv_tbl.LAST);
609
610 i := l_orlv_tbl.NEXT(i);
611
612 END LOOP;
613 END IF;
614
615 /* delete option rules */
616 Okl_Option_Rules_Pub.delete_option_rules(p_api_version => p_api_version,
617 p_init_msg_list => p_init_msg_list,
618 x_return_status => l_return_status,
619 x_msg_count => x_msg_count,
620 x_msg_data => x_msg_data,
621 p_orlv_tbl => l_orlv_tbl);
622
623 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
624 RAISE Okl_Api.G_EXCEPTION_ERROR;
625 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
626 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
627 END IF;
628
629 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
630 x_msg_data => x_msg_data);
631 EXCEPTION
632 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
633 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
634 p_pkg_name => G_PKG_NAME,
635 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
636 x_msg_count => x_msg_count,
637 x_msg_data => x_msg_data,
638 p_api_type => '_PVT');
639 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
640 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
641 p_pkg_name => G_PKG_NAME,
642 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
643 x_msg_count => x_msg_count,
644 x_msg_data => x_msg_data,
645 p_api_type => '_PVT');
646 WHEN OTHERS THEN
647 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
648 p_pkg_name => G_PKG_NAME,
649 p_exc_name => 'OTHERS',
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data,
652 p_api_type => '_PVT');
653
654 END delete_optrules;
655
656 END Okl_Setupoptrules_Pvt;