[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPOVDTEMPLATES_PVT
Source
1 PACKAGE BODY Okl_Setupovdtemplates_Pvt AS
2 /* $Header: OKLRSVTB.pls 115.8 2003/10/15 23:26:30 sgorantl noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE get_rec for: OKL_OVD_RUL_TMLS_V
6 ---------------------------------------------------------------------------
7 PROCEDURE get_rec (
8 p_ovtv_rec IN ovtv_rec_type,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_no_data_found OUT NOCOPY BOOLEAN,
11 x_ovtv_rec OUT NOCOPY ovtv_rec_type
12 ) IS
13 CURSOR okl_ovtv_pk_csr (p_id IN NUMBER) IS
14 SELECT
15 ID,
16 OBJECT_VERSION_NUMBER,
17 OVD_ID,
18 RUL_ID,
19 SEQUENCE_NUMBER,
20 CREATED_BY,
21 CREATION_DATE,
22 LAST_UPDATED_BY,
23 LAST_UPDATE_DATE,
24 NVL(LAST_UPDATE_LOGIN, Okl_Api.G_MISS_NUM) LAST_UPDATE_LOGIN
25 FROM Okl_Ovd_Rul_Tmls_V
26 WHERE Okl_Ovd_Rul_Tmls_V.id = p_id;
27 l_okl_ovtv_pk okl_ovtv_pk_csr%ROWTYPE;
28 l_ovtv_rec ovtv_rec_type;
29 BEGIN
30 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
31 x_no_data_found := TRUE;
32
33 -- Get current database values
34 OPEN okl_ovtv_pk_csr (p_ovtv_rec.id);
35 FETCH okl_ovtv_pk_csr INTO
36 l_ovtv_rec.ID,
37 l_ovtv_rec.OBJECT_VERSION_NUMBER,
38 l_ovtv_rec.OVD_ID,
39 l_ovtv_rec.RUL_ID,
40 l_ovtv_rec.SEQUENCE_NUMBER,
41 l_ovtv_rec.CREATED_BY,
42 l_ovtv_rec.CREATION_DATE,
43 l_ovtv_rec.LAST_UPDATED_BY,
44 l_ovtv_rec.LAST_UPDATE_DATE,
45 l_ovtv_rec.LAST_UPDATE_LOGIN;
46 x_no_data_found := okl_ovtv_pk_csr%NOTFOUND;
47 CLOSE okl_ovtv_pk_csr;
48 x_ovtv_rec := l_ovtv_rec;
49 EXCEPTION
50 WHEN OTHERS THEN
51 -- store SQL error message on message stack
52 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
53 p_msg_name => G_UNEXPECTED_ERROR,
54 p_token1 => G_SQLCODE_TOKEN,
55 p_token1_value => SQLCODE,
56 p_token2 => G_SQLERRM_TOKEN,
57 p_token2_value => SQLERRM);
58 -- notify UNEXPECTED error for calling API.
59 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
60
61 IF (okl_ovtv_pk_csr%ISOPEN) THEN
62 CLOSE okl_ovtv_pk_csr;
63 END IF;
64
65 END get_rec;
66
67 ---------------------------------------------------------------------------
68 -- PROCEDURE verify_context for: OKL_OVD_RUL_TMLS_V
69 ---------------------------------------------------------------------------
70 FUNCTION verify_context (
71 p_org_id IN NUMBER,
72 p_inv_org_id IN NUMBER,
73 p_book_type_code IN VARCHAR2,
74 p_context_org IN NUMBER,
75 p_context_inv_org IN NUMBER,
76 p_context_asset_book IN VARCHAR2
77 ) RETURN VARCHAR2 IS
78 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
79 BEGIN
80 IF p_context_org <> Okl_Api.G_MISS_NUM AND
81 p_org_id <> p_context_org THEN
82 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
83 p_msg_name => G_CONTEXT_MISMATCH,
84 p_token1 => G_CONTEXT_TOKEN,
85 p_token1_value => 'Context_Org_Id');
86 l_return_status := Okl_Api.G_RET_STS_ERROR;
87 RETURN(l_return_status);
88 END IF;
89 IF p_context_inv_org <> Okl_Api.G_MISS_NUM AND
90 p_inv_org_id <> p_context_inv_org THEN
91 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
92 p_msg_name => G_CONTEXT_MISMATCH,
93 p_token1 => G_CONTEXT_TOKEN,
94 p_token1_value => 'Context_Inv_Org_Id');
95 l_return_status := Okl_Api.G_RET_STS_ERROR;
96 RETURN(l_return_status);
97 END IF;
98 IF p_context_asset_book <> Okl_Api.G_MISS_CHAR AND
99 p_book_type_code <> p_context_asset_book THEN
100 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
101 p_msg_name => G_CONTEXT_MISMATCH,
102 p_token1 => G_CONTEXT_TOKEN,
103 p_token1_value => 'Context_Book_Type_Code');
104 l_return_status := Okl_Api.G_RET_STS_ERROR;
105 RETURN(l_return_status);
106 END IF;
107 RETURN(l_return_status);
108 END verify_context;
109
110 ---------------------------------------------------------------------------
111 -- PROCEDURE check_constraints for: OKL_OVD_RUL_TMLS_V
112 -- To verify whether an addition of new option value rule template
113 -- is ok with rest of the product - contract relationships
114 ---------------------------------------------------------------------------
115 PROCEDURE check_constraints (
116 p_api_version IN NUMBER,
117 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE, p_ovtv_rec IN ovtv_rec_type,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2,
121 x_valid OUT NOCOPY BOOLEAN
122 ) IS
123 CURSOR okl_ovt_csp_fk_csr (p_ovd_id IN Okl_Opv_Rules_V.ID%TYPE
124 ) IS
125 SELECT '1'
126 FROM Okl_Opv_Rules_V ovd,
127 Okl_Pdt_Opt_Vals_V pov,
128 Okl_Slctd_Optns_V csp
129 WHERE ovd.ID = p_ovd_id
130 AND pov.OVE_ID = ovd.OVE_ID
131 AND csp.POV_ID = pov.ID;
132
133 CURSOR okl_ovt_lsr_fk_csr (p_ovd_id IN Okl_Opv_Rules_V.ID%TYPE
134 ) IS
135 SELECT orl.RGR_RGD_CODE RGR_RGD_CODE,
136 orl.RGR_RDF_CODE RGR_RDF_CODE,
137 NVL(ovd.CONTEXT_INTENT, Okl_Api.G_MISS_CHAR) CONTEXT_INTENT,
138 NVL(ovd.CONTEXT_ORG, Okl_Api.G_MISS_NUM) CONTEXT_ORG,
139 NVL(ovd.CONTEXT_INV_ORG, Okl_Api.G_MISS_NUM) CONTEXT_INV_ORG,
140 NVL(ovd.CONTEXT_ASSET_BOOK, Okl_Api.G_MISS_CHAR) CONTEXT_ASSET_BOOK,
141 ove.FROM_DATE FROM_DATE,
142 NVL(ove.TO_DATE, Okl_Api.G_MISS_DATE) TO_DATE
143 FROM Okl_Opv_Rules_V ovd,
144 Okl_Opt_Rules_V orl,
145 Okl_Opt_Values_V ove
146 WHERE ovd.ID = p_ovd_id
147 AND orl.ID = ovd.ORL_ID
148 AND ove.ID = ovd.OVE_ID;
149
150 CURSOR okl_ovt_rds_fk_csr (p_rgd_code IN Okc_Rule_Def_Sources_V.rgr_rgd_code%TYPE,
151 p_rdf_code IN Okc_Rule_Def_Sources_V.rgr_rdf_code%TYPE,
152 p_buy_or_sell IN Okc_Rule_Def_Sources_V.buy_or_sell%TYPE,
153 p_jtot_object_code IN Okc_Rule_Def_Sources_V.jtot_object_code%TYPE,
154 p_object_id_number IN Okc_Rule_Def_Sources_V.object_id_number%TYPE,
155 p_from_date IN Okl_Opt_Values_V.from_date%TYPE,
156 p_to_date IN Okl_Opt_Values_V.TO_DATE%TYPE
157 ) IS
158 SELECT '1'
159 FROM Okc_Rule_Def_Sources_V rds
160 WHERE rds.RGR_RGD_CODE = p_rgd_code
161 AND rds.RGR_RDF_CODE = p_rdf_code
162 AND rds.OBJECT_ID_NUMBER = p_object_id_number
163 AND rds.JTOT_OBJECT_CODE = p_jtot_object_code
164 AND rds.BUY_OR_SELL = p_buy_or_sell
165 AND (rds.START_DATE > p_from_date OR
166 NVL(rds.END_DATE, Okl_Api.G_MISS_DATE) < p_to_date);
167
168 l_check VARCHAR2(1) := '?';
169 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY') , 'DD/MM/YYYY');
170 l_row_not_found BOOLEAN := FALSE;
171 l_rulv_rec rulv_rec_type;
172 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
173 l_no_data_found BOOLEAN := FALSE;
174 l_object_id_number NUMBER := 0;
175 l_rule VARCHAR2(30);
176 l_rulegroup VARCHAR2(30);
177 l_context_org NUMBER := 0;
178 l_context_inv_org NUMBER := 0;
179 l_context_asset_book VARCHAR2(10);
180 l_context_intent VARCHAR2(30);
181 l_from_date DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
182 l_to_date DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
183 l_jtot_object_code VARCHAR2(30);
184 l_okx_start_date DATE;
185 l_okx_end_date DATE;
186 l_rulv_disp_rec rulv_disp_rec_type;
187 BEGIN
188 x_valid := TRUE;
189 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
190
191 -- Check if the option value is already in use with a contract
192 OPEN okl_ovt_csp_fk_csr (p_ovtv_rec.ovd_id);
193 FETCH okl_ovt_csp_fk_csr INTO l_check;
194 l_row_not_found := okl_ovt_csp_fk_csr%NOTFOUND;
195 CLOSE okl_ovt_csp_fk_csr;
196
197 IF l_row_not_found = FALSE THEN
198 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
199 p_msg_name => G_IN_USE,
200 p_token1 => G_PARENT_TABLE_TOKEN,
201 p_token1_value => 'Okl_Ovd_Rul_Tmls_V',
202 p_token2 => G_CHILD_TABLE_TOKEN,
203 p_token2_value => 'Okl_Slctd_Optns_V');
204 x_valid := FALSE;
205 x_return_status := Okl_Api.G_RET_STS_ERROR;
206 RAISE G_EXCEPTION_HALT_PROCESSING;
207 END IF;
208
209 IF p_ovtv_rec.id = Okl_Api.G_MISS_NUM THEN
210 l_rulv_rec.id := p_ovtv_rec.rul_id;
211 Okl_Setupoptvalues_Pvt.get_rul_rec (p_rulv_rec => l_rulv_rec,
212 x_return_status => l_return_status,
213 x_no_data_found => l_no_data_found,
214 x_rulv_rec => l_rulv_rec);
215 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
216 l_no_data_found = TRUE THEN
217 x_valid := FALSE;
218 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
219 RAISE G_EXCEPTION_HALT_PROCESSING;
220 END IF;
221
222 Okl_Rule_Apis_Pvt.get_rule_disp_value(p_api_version => p_api_version,
223 p_init_msg_list => p_init_msg_list,
224 p_rulv_rec => l_rulv_rec,
225 x_return_status => l_return_status,
226 x_msg_count => x_msg_count,
227 x_msg_data => x_msg_data,
228 x_rulv_disp_rec => l_rulv_disp_rec);
229 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
230 x_valid := FALSE;
231 x_return_status := Okl_Api.G_RET_STS_ERROR;
232 RAISE G_EXCEPTION_HALT_PROCESSING;
233 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
234 x_valid := FALSE;
235 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
236 RAISE G_EXCEPTION_HALT_PROCESSING;
237 END IF;
238
239 -- Fetch all the details for the option value rule for which this
240 -- template is being attached
241 OPEN okl_ovt_lsr_fk_csr (p_ovtv_rec.ovd_id);
242 FETCH okl_ovt_lsr_fk_csr
243 INTO l_rulegroup,
244 l_rule,
245 l_context_intent,
246 l_context_org,
247 l_context_inv_org,
248 l_context_asset_book,
249 l_from_date,
250 l_to_date;
251 l_row_not_found := okl_ovt_lsr_fk_csr%NOTFOUND;
252 CLOSE okl_ovt_lsr_fk_csr;
253
254 IF l_row_not_found = TRUE THEN
255 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
256 p_msg_name => G_MISS_DATA);
257 x_valid := FALSE;
258 x_return_status := Okl_Api.G_RET_STS_ERROR;
259 RAISE G_EXCEPTION_HALT_PROCESSING;
260 END IF;
261
262 --IF l_rulv_rec.rule_information_category <> l_rule OR
263 --l_rulv_rec.template_yn <> 'Y' THEN
264
265 IF l_rulv_rec.rule_information_category <> l_rule THEN
266 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
267 p_msg_name => G_RULE_MISMATCH);
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 --END IF;
274
275 FOR l_object_id_number IN 1..3
276 LOOP
277 l_jtot_object_code := Okl_Api.G_MISS_CHAR;
278 l_okx_start_date := Okl_Api.G_MISS_DATE;
279 l_okx_end_date := Okl_Api.G_MISS_DATE;
280 IF l_object_id_number = 1 AND l_rulv_rec.jtot_object1_code <> Okl_Api.G_MISS_CHAR THEN
281 l_jtot_object_code := l_rulv_rec.jtot_object1_code;
282 l_okx_start_date := l_rulv_disp_rec.obj1_start_date;
283 l_okx_end_date := l_rulv_disp_rec.obj1_end_date;
284 l_return_status := verify_context(p_org_id => l_rulv_disp_rec.obj1_org_id,
285 p_inv_org_id => l_rulv_disp_rec.obj1_inv_org_id,
286 p_book_type_code => l_rulv_disp_rec.obj1_book_type_code,
287 p_context_org => l_context_org,
288 p_context_inv_org => l_context_inv_org,
289 p_context_asset_book => l_context_asset_book);
290 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
291 x_valid := FALSE;
292 x_return_status := Okl_Api.G_RET_STS_ERROR;
293 RAISE G_EXCEPTION_HALT_PROCESSING;
294 END IF;
295 ELSIF l_object_id_number = 2 AND l_rulv_rec.jtot_object2_code <> Okl_Api.G_MISS_CHAR THEN
296 l_jtot_object_code := l_rulv_rec.jtot_object2_code;
297 l_okx_start_date := l_rulv_disp_rec.obj2_start_date;
298 l_okx_end_date := l_rulv_disp_rec.obj2_end_date;
299 l_return_status := verify_context(p_org_id => l_rulv_disp_rec.obj2_org_id,
300 p_inv_org_id => l_rulv_disp_rec.obj2_inv_org_id,
301 p_book_type_code => l_rulv_disp_rec.obj2_book_type_code,
302 p_context_org => l_context_org,
303 p_context_inv_org => l_context_inv_org,
304 p_context_asset_book => l_context_asset_book);
305 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
306 x_valid := FALSE;
307 x_return_status := Okl_Api.G_RET_STS_ERROR;
308 RAISE G_EXCEPTION_HALT_PROCESSING;
309 END IF;
310 ELSIF l_object_id_number = 3 AND l_rulv_rec.jtot_object3_code <> Okl_Api.G_MISS_CHAR THEN
311 l_jtot_object_code := l_rulv_rec.jtot_object3_code;
312 l_okx_start_date := l_rulv_disp_rec.obj3_start_date;
313 l_okx_end_date := l_rulv_disp_rec.obj3_end_date;
314 l_return_status := verify_context(p_org_id => l_rulv_disp_rec.obj3_org_id,
315 p_inv_org_id => l_rulv_disp_rec.obj3_inv_org_id,
316 p_book_type_code => l_rulv_disp_rec.obj3_book_type_code,
317 p_context_org => l_context_org,
318 p_context_inv_org => l_context_inv_org,
319 p_context_asset_book => l_context_asset_book);
320 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
321 x_valid := FALSE;
322 x_return_status := Okl_Api.G_RET_STS_ERROR;
323 RAISE G_EXCEPTION_HALT_PROCESSING;
324 END IF;
325 END IF;
326
327 IF l_jtot_object_code <> Okl_Api.G_MISS_CHAR AND
328 (l_okx_start_date > l_from_date OR
329 NVL(l_okx_end_date, Okl_Api.G_MISS_DATE) < l_to_date OR
330 l_from_date > l_to_date) THEN
331 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
332 p_msg_name => G_DATES_MISMATCH,
333 p_token1 => G_PARENT_TABLE_TOKEN,
334 p_token1_value => 'Okl_Opt_Values_V',
335 p_token2 => G_CHILD_TABLE_TOKEN,
336 p_token2_value => 'Okc_Rules_b');
337 x_valid := FALSE;
338 x_return_status := Okl_Api.G_RET_STS_ERROR;
339 RAISE G_EXCEPTION_HALT_PROCESSING;
340 END IF;
341
342 IF l_jtot_object_code <> Okl_Api.G_MISS_CHAR THEN
343 -- Check for dates in source, okx and option value
344 OPEN okl_ovt_rds_fk_csr (p_rgd_code => l_rulegroup,
345 p_rdf_code => l_rule,
346 p_buy_or_sell => l_context_intent,
347 p_jtot_object_code => l_jtot_object_code,
348 p_object_id_number => l_object_id_number,
349 p_from_date => l_from_date,
350 p_to_date => l_to_date);
351 FETCH okl_ovt_rds_fk_csr INTO l_check;
352 l_no_data_found := okl_ovt_rds_fk_csr%NOTFOUND;
353 CLOSE okl_ovt_rds_fk_csr;
354
355 IF l_no_data_found = FALSE THEN
356 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
357 p_msg_name => G_DATES_MISMATCH,
358 p_token1 => G_PARENT_TABLE_TOKEN,
359 p_token1_value => 'Okl_Ovd_Rul_Tmls_V',
360 p_token2 => G_CHILD_TABLE_TOKEN,
361 p_token2_value => 'Okc_Rule_Def_Sources_V');
362 x_valid := FALSE;
363 x_return_status := Okl_Api.G_RET_STS_ERROR;
364 RAISE G_EXCEPTION_HALT_PROCESSING;
365 END IF;
366 END IF;
367
368 END LOOP;
369
370 END IF;
371 EXCEPTION
372 WHEN G_EXCEPTION_HALT_PROCESSING THEN
373 -- no processing necessary; validation can continue
374 -- with the next column
375 NULL;
376
377 WHEN OTHERS THEN
378 -- store SQL error message on message stack
379 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
380 p_msg_name => G_UNEXPECTED_ERROR,
381 p_token1 => G_SQLCODE_TOKEN,
382 p_token1_value => SQLCODE,
383 p_token2 => G_SQLERRM_TOKEN,
384 p_token2_value => SQLERRM);
385 x_valid := FALSE;
386 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
387
388 IF (okl_ovt_csp_fk_csr%ISOPEN) THEN
389 CLOSE okl_ovt_csp_fk_csr;
390 END IF;
391
392 IF (okl_ovt_lsr_fk_csr%ISOPEN) THEN
393 CLOSE okl_ovt_lsr_fk_csr;
394 END IF;
395
396 IF (okl_ovt_rds_fk_csr%ISOPEN) THEN
397 CLOSE okl_ovt_rds_fk_csr;
398 END IF;
399
400 END check_constraints;
401
402 ---------------------------------------------------------------------------
403 -- PROCEDURE insert_ovdtemplates for: OKL_OVD_RUL_TMLS_V
404 ---------------------------------------------------------------------------
405 PROCEDURE insert_ovdtemplates(p_api_version IN NUMBER,
406 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
407 x_return_status OUT NOCOPY VARCHAR2,
408 x_msg_count OUT NOCOPY NUMBER,
409 x_msg_data OUT NOCOPY VARCHAR2,
410 p_optv_rec IN optv_rec_type,
411 p_ovev_rec IN ovev_rec_type,
412 p_ovdv_rec IN ovdv_rec_type,
413 p_ovtv_rec IN ovtv_rec_type,
414 x_ovtv_rec OUT NOCOPY ovtv_rec_type
415 ) IS
416 l_api_version CONSTANT NUMBER := 1;
417 l_api_name CONSTANT VARCHAR2(30) := 'insert_ovdtemplates';
418 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
419 l_valid BOOLEAN;
420 l_ovtv_rec ovtv_rec_type;
421 BEGIN
422 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
423 l_return_status := Okc_Api.START_ACTIVITY(p_api_name => l_api_name,
424 p_pkg_name => G_PKG_NAME,
425 p_init_msg_list => p_init_msg_list,
426 l_api_version => l_api_version,
427 p_api_version => p_api_version,
428 p_api_type => '_PVT',
429 x_return_status => l_return_status);
430 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
431 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
432 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
433 RAISE Okc_Api.G_EXCEPTION_ERROR;
434 END IF;
435
436 l_ovtv_rec := p_ovtv_rec;
437
438 /* call check_constraints to check the validity of this relationship */
439 check_constraints(p_api_version => p_api_version,
440 p_init_msg_list => p_init_msg_list,
441 p_ovtv_rec => l_ovtv_rec,
442 x_return_status => l_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data,
445 x_valid => l_valid);
446
447 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
448 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
449 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
450 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
451 l_valid <> TRUE) THEN
452 x_return_status := Okl_Api.G_RET_STS_ERROR;
453 RAISE Okc_Api.G_EXCEPTION_ERROR;
454 END IF;
455
456 /* public api to insert option value rule templates */
457 Okl_Ovd_Rul_Tmls_Pub.insert_ovd_rul_tmls(p_api_version => p_api_version,
458 p_init_msg_list => p_init_msg_list,
459 x_return_status => l_return_status,
460 x_msg_count => x_msg_count,
461 x_msg_data => x_msg_data,
462 p_ovtv_rec => l_ovtv_rec,
463 x_ovtv_rec => x_ovtv_rec);
464
465 IF l_return_status = Okc_Api.G_RET_STS_ERROR THEN
466 RAISE Okc_Api.G_EXCEPTION_ERROR;
467 ELSIF l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
468 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
469 END IF;
470
471 Okc_Api.END_ACTIVITY(x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data);
473 EXCEPTION
474 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
475 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
476 p_pkg_name => G_PKG_NAME,
477 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
478 x_msg_count => x_msg_count,
479 x_msg_data => x_msg_data,
480 p_api_type => '_PVT');
481 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
482 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
483 p_pkg_name => G_PKG_NAME,
484 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_api_type => '_PVT');
488 WHEN OTHERS THEN
489 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
490 p_pkg_name => G_PKG_NAME,
491 p_exc_name => 'OTHERS',
492 x_msg_count => x_msg_count,
493 x_msg_data => x_msg_data,
494 p_api_type => '_PVT');
495
496 END insert_ovdtemplates;
497
498 ---------------------------------------------------------------------------
499 -- PROCEDURE delete_ovdtemplates for: OKL_OVD_RUL_TMLS_V
500 -- This allows the user to delete table of records
501 ---------------------------------------------------------------------------
502 PROCEDURE delete_ovdtemplates(p_api_version IN NUMBER,
503 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 p_optv_rec IN optv_rec_type,
508 p_ovev_rec IN ovev_rec_type,
509 p_ovdv_rec IN ovdv_rec_type,
510 p_ovtv_tbl IN ovtv_tbl_type
511 ) IS
512 l_api_version CONSTANT NUMBER := 1;
513 l_ovtv_tbl ovtv_tbl_type;
514 l_db_ovtv_rec ovtv_rec_type;
515 l_rulv_tbl Okl_Rule_Pub.rulv_tbl_type;
516 l_no_data_found BOOLEAN := TRUE;
517 l_api_name CONSTANT VARCHAR2(30) := 'delete_ovdtemplates';
518 l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
519 l_overall_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
520 l_valid BOOLEAN;
521 i NUMBER;
522
523
524 BEGIN
525 x_return_status := Okc_Api.G_RET_STS_SUCCESS;
526 l_return_status := Okc_Api.START_ACTIVITY(p_api_name => l_api_name,
527 p_pkg_name => G_PKG_NAME,
528 p_init_msg_list => p_init_msg_list,
529 l_api_version => l_api_version,
530 p_api_version => p_api_version,
531 p_api_type => '_PVT',
532 x_return_status => l_return_status);
533 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
534 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
535 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
536 RAISE Okc_Api.G_EXCEPTION_ERROR;
537 END IF;
538
539 l_ovtv_tbl := p_ovtv_tbl;
540 IF (l_ovtv_tbl.COUNT > 0) THEN
541 i := l_ovtv_tbl.FIRST;
542 LOOP
543 check_constraints(p_api_version => p_api_version,
544 p_init_msg_list => p_init_msg_list,
545 p_ovtv_rec => l_ovtv_tbl(i),
546 x_return_status => l_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 x_valid => l_valid);
550 -- store the highest degree of error
551 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
552 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
553 l_overall_status := l_return_status;
554 END IF;
555 END IF;
556
557
558 /* fetch old details from the database */
559 get_rec(p_ovtv_rec => l_ovtv_tbl(i),
560 x_return_status => l_return_status,
561 x_no_data_found => l_no_data_found,
562 x_ovtv_rec => l_db_ovtv_rec);
563
564 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
565 l_no_data_found = TRUE THEN
566 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
567 END IF;
568
569 l_rulv_tbl(i).id := l_db_ovtv_rec.rul_id;
570
571 Okl_Rule_Pub.delete_rule(p_api_version => p_api_version
572 ,p_init_msg_list => p_init_msg_list
573 ,x_return_status => x_return_status
574 ,x_msg_count => x_msg_count
575 ,x_msg_data => x_msg_data
576 ,p_rulv_rec => l_rulv_tbl(i)
577 );
578
579 -- TCHGS: Store the highest degree of error
580 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
581 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
582 l_overall_status := x_return_status;
583 END IF;
584 END IF;
585
586 EXIT WHEN (i = l_ovtv_tbl.LAST);
587
588 i := l_ovtv_tbl.NEXT(i);
589
590 END LOOP;
591 -- return overall status
592 l_return_status := l_overall_status;
593 END IF;
594 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
595 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
596 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
597 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
598 l_valid <> TRUE) THEN
599 x_return_status := Okl_Api.G_RET_STS_ERROR;
600 RAISE Okc_Api.G_EXCEPTION_ERROR;
601 END IF;
602
603 /* public api to delete option value rules */
604 Okl_Ovd_Rul_Tmls_Pub.delete_ovd_rul_tmls(p_api_version => p_api_version,
605 p_init_msg_list => p_init_msg_list,
606 x_return_status => l_return_status,
607 x_msg_count => x_msg_count,
608 x_msg_data => x_msg_data,
609 p_ovtv_tbl => l_ovtv_tbl);
610
611 IF l_return_status = Okc_Api.G_RET_STS_ERROR THEN
612 RAISE Okc_Api.G_EXCEPTION_ERROR;
613 ELSIF l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR THEN
614 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
615 END IF;
616
617 Okc_Api.END_ACTIVITY(x_msg_count => x_msg_count,
618 x_msg_data => x_msg_data);
619 EXCEPTION
620 WHEN Okc_Api.G_EXCEPTION_ERROR THEN
621 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
622 p_pkg_name => G_PKG_NAME,
623 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
624 x_msg_count => x_msg_count,
625 x_msg_data => x_msg_data,
626 p_api_type => '_PVT');
627 WHEN Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
628 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
629 p_pkg_name => G_PKG_NAME,
630 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data,
633 p_api_type => '_PVT');
634 WHEN OTHERS THEN
635 x_return_status := Okc_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
636 p_pkg_name => G_PKG_NAME,
637 p_exc_name => 'OTHERS',
638 x_msg_count => x_msg_count,
639 x_msg_data => x_msg_data,
640 p_api_type => '_PVT');
641
642 END delete_ovdtemplates;
643
644 END Okl_Setupovdtemplates_Pvt;