[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPOVALUES_PVT
Source
1 Package BODY Okl_Setuppovalues_Pvt AS
2 /* $Header: OKLRSDVB.pls 115.12 2003/07/23 18:32:08 sgorantl noship $ */
3 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_TABLE_NAME'; --- CHG001
4 G_UNQS CONSTANT VARCHAR2(200) := 'OKL_NOT_UNIQUE'; --- CHG001
5 G_COLUMN_TOKEN CONSTANT VARCHAR2(100) := 'COLUMN';
6 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
7
8 ---------------------------------------------------------------------------
9 -- PROCEDURE get_rec for: OKL_PDT_OPT_VALS_V
10 ---------------------------------------------------------------------------
11 PROCEDURE get_rec (
12 p_povv_rec IN povv_rec_type,
13 x_no_data_found OUT NOCOPY BOOLEAN,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_povv_rec OUT NOCOPY povv_rec_type
16 ) IS
17 CURSOR okl_povv_pk_csr (p_id IN NUMBER) IS
18 SELECT
19 ID,
20 OBJECT_VERSION_NUMBER,
21 OVE_ID,
22 PON_ID,
23 FROM_DATE,
24 CREATED_BY,
25 TO_DATE,
26 CREATION_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATE_LOGIN
30 FROM Okl_Pdt_Opt_Vals_V
31 WHERE okl_pdt_opt_vals_v.id = p_id;
32 l_okl_povv_pk okl_povv_pk_csr%ROWTYPE;
33 l_povv_rec povv_rec_type;
34 BEGIN
35 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
36 x_no_data_found := TRUE;
37 -- Get current database values
38 OPEN okl_povv_pk_csr (p_povv_rec.id);
39 FETCH okl_povv_pk_csr INTO
40 l_povv_rec.ID,
41 l_povv_rec.OBJECT_VERSION_NUMBER,
42 l_povv_rec.OVE_ID,
43 l_povv_rec.PON_ID,
44 l_povv_rec.FROM_DATE,
45 l_povv_rec.CREATED_BY,
46 l_povv_rec.TO_DATE,
47 l_povv_rec.CREATION_DATE,
48 l_povv_rec.LAST_UPDATED_BY,
49 l_povv_rec.LAST_UPDATE_DATE,
50 l_povv_rec.LAST_UPDATE_LOGIN;
51 x_no_data_found := okl_povv_pk_csr%NOTFOUND;
52
53 CLOSE okl_povv_pk_csr;
54 x_povv_rec := l_povv_rec;
55 EXCEPTION
56 WHEN OTHERS THEN
57 -- store SQL error message on message stack
58 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
59 p_msg_name => G_UNEXPECTED_ERROR,
60 p_token1 => G_SQLCODE_TOKEN,
61 p_token1_value => SQLCODE,
62 p_token2 => G_SQLERRM_TOKEN,
63 p_token2_value => SQLERRM);
64 -- notify UNEXPECTED error for calling API.
65 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
66
67 IF (okl_povv_pk_csr%ISOPEN) THEN
68 CLOSE okl_povv_pk_csr;
69 END IF;
70
71 END get_rec;
72
73 ---------------------------------------------------------------------------
74 -- PROCEDURE get_parent_dates for: OKL_PDT_OPTS_V
75 ---------------------------------------------------------------------------
76
77 PROCEDURE get_parent_dates(
78 p_povv_rec IN povv_rec_type,
79 x_no_data_found OUT NOCOPY BOOLEAN,
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_ponv_rec OUT NOCOPY ponv_rec_type
82 ) IS
83 CURSOR okl_ponv_pk_csr (p_pon_id IN NUMBER) IS
84 SELECT FROM_DATE,
85 TO_DATE
86 FROM Okl_pdt_opts_V ponv
87 WHERE ponv.id = p_pon_id;
88 l_okl_ponv_pk okl_ponv_pk_csr%ROWTYPE;
89 l_ponv_rec ponv_rec_type;
90 BEGIN
91 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
92 x_no_data_found := TRUE;
93 -- Get current database values
94 OPEN okl_ponv_pk_csr (p_povv_rec.pon_id);
95 FETCH okl_ponv_pk_csr INTO
96 l_ponv_rec.FROM_DATE,
97 l_ponv_rec.TO_DATE;
98 x_no_data_found := okl_ponv_pk_csr%NOTFOUND;
99 CLOSE okl_ponv_pk_csr;
100 x_ponv_rec := l_ponv_rec;
101 EXCEPTION
102 WHEN OTHERS THEN
103 -- store SQL error message on message stack
104 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
105 p_msg_name => G_UNEXPECTED_ERROR,
106
107 p_token1 => G_SQLCODE_TOKEN,
108 p_token1_value => SQLCODE,
109 p_token2 => G_SQLERRM_TOKEN,
110 p_token2_value => SQLERRM);
111 -- notify UNEXPECTED error for calling API.
112 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
113
114 IF (okl_ponv_pk_csr%ISOPEN) THEN
115 CLOSE okl_ponv_pk_csr;
116 END IF;
117
118 END get_parent_dates;
119
120 -----------------------------------------------------------------------------
121 -- PROCEDURE check_constraints for: OKL_PDT_OPT_VALS_V
122 -----------------------------------------------------------------------------
123
124 PROCEDURE Check_Constraints (
125 p_api_version IN NUMBER,
126 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
127 p_povv_rec IN povv_rec_type,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_msg_count OUT NOCOPY NUMBER,
130 x_msg_data OUT NOCOPY VARCHAR2,
131 x_valid OUT NOCOPY BOOLEAN
132 ) IS
133
134 CURSOR okl_povv_chk(p_pon_id NUMBER
135 ) IS
136 SELECT '1' FROM okl_pdt_opts_v ponv,
137 okl_k_headers_v khdr
138 WHERE ponv.id = p_pon_id AND
139 khdr.pdt_id = ponv.pdt_id;
140
141 CURSOR okl_pov_pdt_fk_csr(p_pon_id IN Okl_Products_V.ID%TYPE,
142 p_date IN Okl_Products_V.TO_DATE%TYPE
143 ) IS
144 SELECT '1'
145 FROM Okl_products_V pdt,
146 Okl_pdt_opts_V pon
147 WHERE pdt.id = pon.pdt_id
148 AND pon.ID = p_pon_id
149 AND NVL(pdt.TO_DATE, p_date) < p_date;
150
151 CURSOR okl_pov_constraints_csr(p_ove_id IN Okl_Pdt_Qualitys_V.ID%TYPE,
152 p_from_date IN Okl_Pdt_Qualitys_V.FROM_DATE%TYPE,
153 p_to_date IN Okl_Pdt_Qualitys_V.TO_DATE%TYPE
154 ) IS
155 SELECT '1'
156 FROM Okl_Opt_Values_V ove
157 WHERE ove.ID = p_ove_id
158 AND ((ove.FROM_DATE > p_from_date OR
159 p_from_date > NVL(ove.TO_DATE,p_from_date)) OR
160 NVL(ove.TO_DATE, p_to_date) < p_to_date);
161
162 CURSOR okl_pdt_opt_vals_unique (p_unique1 OKL_PDT_OPT_VALS_V.OVE_ID%TYPE, p_unique2 OKL_PDT_OPT_VALS_V.PON_ID%TYPE) IS
163 SELECT '1'
164 FROM OKL_PDT_OPT_VALS_V
165 WHERE OKL_PDT_OPT_VALS_V.OVE_ID = p_unique1 AND
166 OKL_PDT_OPT_VALS_V.PON_ID = p_unique2 AND
167 OKL_PDT_OPT_VALS_V.ID <> NVL(p_povv_rec.id,-9999);
168
169 l_unique_key VARCHAR2(1);
170 l_check VARCHAR2(1) := '?';
171 l_row_not_found BOOLEAN := FALSE;
172 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
173 l_token_1 VARCHAR2(1999);
174 l_token_2 VARCHAR2(1999);
175 l_token_3 VARCHAR2(1999);
176 l_token_4 VARCHAR2(1999);
177 l_token_5 VARCHAR2(1999);
178 l_token_6 VARCHAR2(1999);
179
180 BEGIN
181 x_valid := TRUE;
182 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
183
184 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_OPT_VAL_SUMRY',
185 p_attribute_code => 'OKL_PRODUCT_OPTION_VALUES');
186
187 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_SERCH',
188 p_attribute_code => 'OKL_PRODUCTS');
189 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
190 p_attribute_code => 'OKL_KDTLS_CONTRACT');
191 l_token_4 := l_token_1 ||','||l_token_2;
192
193
194 l_token_5 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_SERCH',
195 p_attribute_code => 'OKL_OPTION_VALUES');
196
197 l_token_6 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_CRUPD',
198 p_attribute_code => 'OKL_OPTION_VALUE');
199
200
201 -- Check for povv inserts and deletes
202 OPEN okl_povv_chk(p_povv_rec.pon_id);
203
204 FETCH okl_povv_chk INTO l_check;
205 l_row_not_found := okl_povv_chk%NOTFOUND;
206 CLOSE okl_povv_chk;
207
208 IF l_row_not_found = FALSE THEN
209 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
210 p_msg_name => G_IN_USE,
211 p_token1 => G_PARENT_TABLE_TOKEN,
212 p_token1_value => l_token_1,
213 p_token2 => G_CHILD_TABLE_TOKEN,
214 p_token2_value => l_token_3);
215 x_valid := FALSE;
216 x_return_status := Okl_Api.G_RET_STS_ERROR;
217 RAISE G_EXCEPTION_HALT_PROCESSING;
218 END IF;
219
220 --CHECK FOR UNIQUENESS
221 IF p_povv_rec.id = Okl_Api.G_MISS_NUM THEN
222 OPEN okl_pdt_opt_vals_unique (p_povv_rec.ove_id, p_povv_rec.pon_id);
223 FETCH okl_pdt_opt_vals_unique INTO l_unique_key;
224 IF okl_pdt_opt_vals_unique%FOUND THEN
225 --Okl_Api.set_message(G_APP_NAME,G_UNQS, G_TABLE_TOKEN,l_token_1);
226 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
227 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
228 p_token1 => G_TABLE_TOKEN,
229 p_token1_value => l_token_1,
230 p_token2 => G_COLUMN_TOKEN,
231 p_token2_value => l_token_6);
232 x_valid := FALSE;
233 x_return_status := Okl_Api.G_RET_STS_ERROR;
234 RAISE G_EXCEPTION_HALT_PROCESSING;
235 ELSE
236 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
237 END IF;
238 CLOSE okl_pdt_opt_vals_unique;
239 END IF;
240
241 -- Check if the product to which the option values are added is not
242 -- in the past
243 /* OPEN okl_pov_pdt_fk_csr (p_povv_rec.pon_id,
244 l_sysdate);
245 FETCH okl_pov_pdt_fk_csr INTO l_check;
246 l_row_not_found := okl_pov_pdt_fk_csr%NOTFOUND;
247 CLOSE okl_pov_pdt_fk_csr;
248
249 IF l_row_not_found = FALSE THEN
250 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
251 p_msg_name => G_PAST_RECORDS);
252 x_valid := FALSE;
253 x_return_status := Okl_Api.G_RET_STS_ERROR;
254 RAISE G_EXCEPTION_HALT_PROCESSING;
255 END IF;
256 */
257
258 -- Check for constraints dates
259 IF p_povv_rec.id = Okl_Api.G_MISS_NUM THEN
260 OPEN okl_pov_constraints_csr (p_povv_rec.ove_id,
261 p_povv_rec.from_date,
262 p_povv_rec.TO_DATE);
263 FETCH okl_pov_constraints_csr INTO l_check;
264 l_row_not_found := okl_pov_constraints_csr%NOTFOUND;
265 CLOSE okl_pov_constraints_csr;
266
267 IF l_row_not_found = FALSE THEN
268 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
269 p_msg_name => G_DATES_MISMATCH,
270 p_token1 => G_PARENT_TABLE_TOKEN,
271 p_token1_value => l_token_5,
272 p_token2 => G_CHILD_TABLE_TOKEN,
273 p_token2_value => l_token_4);
274 x_valid := FALSE;
275 x_return_status := Okl_Api.G_RET_STS_ERROR;
276 RAISE G_EXCEPTION_HALT_PROCESSING;
277 END IF;
278 END IF;
279
280 EXCEPTION
281 WHEN G_EXCEPTION_HALT_PROCESSING THEN
282 -- no processing necessary; validation can continue
283 -- with the next column
284 NULL;
285 WHEN OTHERS THEN
286 -- store SQL error message on message stack
287 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
288 p_msg_name => G_UNEXPECTED_ERROR,
289 p_token1 => G_SQLCODE_TOKEN,
290 p_token1_value => SQLCODE,
291 p_token2 => G_SQLERRM_TOKEN,
292 p_token2_value => SQLERRM);
293 x_valid := FALSE;
294 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
295
296 IF (okl_povv_chk%ISOPEN) THEN
297 CLOSE okl_povv_chk;
298 END IF;
299
300 IF (okl_pov_pdt_fk_csr%ISOPEN) THEN
301 CLOSE okl_pov_pdt_fk_csr;
302 END IF;
303
304 IF (okl_pov_constraints_csr%ISOPEN) THEN
305 CLOSE okl_pov_constraints_csr;
306 END IF;
307
308 IF (okl_pdt_opt_vals_unique%ISOPEN) THEN
309 CLOSE okl_pdt_opt_vals_unique;
310 END IF;
311
312 END Check_Constraints;
313
314 ---------------------------------------------------------------------------
315 -- PROCEDURE Validate _Ove_Id
316 ---------------------------------------------------------------------------
317 -- Start of comments
318 --
319 -- Procedure Name : Validate _Ove_Id
320 -- Description :
321 -- Business Rules :
322 -- Parameters :
323 -- Version : 1.0
324 -- End of comments
325 ---------------------------------------------------------------------------
326
327 PROCEDURE Validate_Ove_Id (
328 p_povv_rec IN povv_rec_type,
329 x_return_status OUT NOCOPY VARCHAR2
330 ) IS
331 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
332 CURSOR okl_pdt_opt_vals_foreign1 (p_foreign OKL_PDT_OPT_VALS.OVE_ID%TYPE) IS
333 SELECT ID
334 FROM OKL_OPT_VALUES_V
335 WHERE OKL_OPT_VALUES_V.ID = p_foreign;
336
337 l_foreign_key OKL_PDT_OPT_VALS_V.OVE_ID%TYPE;
338 l_token_1 VARCHAR2(1999);
339
340
341 BEGIN
342 -- initialize return status
343 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
344
345 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTVAL_CRUPD',
346 p_attribute_code => 'OKL_OPTION_VALUE');
347
348
349 IF p_povv_rec.ove_id = Okl_Api.G_MISS_NUM OR
350 p_povv_rec.ove_id IS NULL
351 THEN
352 Okc_Api.set_message(Okl_Pov_Pvt.G_APP_NAME, Okl_Pov_Pvt.G_REQUIRED_VALUE,Okl_Pov_Pvt.G_COL_NAME_TOKEN,l_token_1);
353 x_return_status := Okl_Api.G_RET_STS_ERROR;
354 RAISE G_ITEM_NOT_FOUND_ERROR;
355 END IF;
356
357 IF p_povv_rec.ove_id IS NOT NULL THEN
358 OPEN okl_pdt_opt_vals_foreign1 (p_povv_rec.ove_id);
359 FETCH okl_pdt_opt_vals_foreign1 INTO l_foreign_key;
360 IF okl_pdt_opt_vals_foreign1%NOTFOUND THEN
361 Okc_Api.set_message(Okl_Pov_Pvt.G_APP_NAME, Okl_Pov_Pvt.G_INVALID_KEY,Okl_Pov_Pvt.G_COL_NAME_TOKEN,l_token_1);
362 x_return_status := Okl_Api.G_RET_STS_ERROR;
363 RAISE G_ITEM_NOT_FOUND_ERROR;
364 ELSE
365 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
366 END IF;
367 CLOSE okl_pdt_opt_vals_foreign1;
368 END IF;
369 EXCEPTION
370 WHEN G_ITEM_NOT_FOUND_ERROR THEN
371 NULL;
372 WHEN OTHERS THEN
373 Okl_Api.set_message(p_app_name =>Okl_Pov_Pvt.G_APP_NAME,
374 p_msg_name =>Okl_Pov_Pvt.G_UNEXPECTED_ERROR,
375 p_token1 =>Okl_Pov_Pvt.G_SQL_SQLCODE_TOKEN,
376 p_token1_value =>SQLCODE,
377 p_token2 =>Okl_Pov_Pvt.G_SQL_SQLERRM_TOKEN,
378 p_token2_value =>SQLERRM);
379 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
380
381 END Validate_Ove_Id;
382 ------end of Validate_Ove_Id-----------------------------------
383
384 ---------------------------------------------------------------------------
385 -- FUNCTION Validate _Attribute
386 ---------------------------------------------------------------------------
387 -- Start of comments
388 --
389 -- Procedure Name : Validate _Attribute
390 -- Description :
391 -- Business Rules :
392 -- Parameters :
393 -- Version : 1.0
394 -- End of comments
395 ---------------------------------------------------------------------------
396 FUNCTION Validate_Attributes(
397 p_povv_rec IN povv_rec_type
398 ) RETURN VARCHAR IS
399 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
400 l_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
401
402 BEGIN
403 -----CHECK FOR OVE_ID----------------------------
404 Validate_Ove_Id (p_povv_rec,x_return_status);
405 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
406 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
407 -- need to leave
408 l_return_status := x_return_status;
409 RAISE G_EXCEPTION_HALT_PROCESSING;
410 ELSE
411 l_return_status := x_return_status;
412 END IF;
413 END IF;
414 RETURN(l_return_status);
415 EXCEPTION
416 WHEN G_EXCEPTION_HALT_PROCESSING THEN
417 -- just come out with return status
418 NULL;
419 RETURN (l_return_status);
420
421 WHEN OTHERS THEN
422 Okl_Api.set_message(p_app_name =>Okl_Pov_Pvt.G_APP_NAME,
423 p_msg_name =>Okl_Pov_Pvt.G_UNEXPECTED_ERROR,
424 p_token1 =>Okl_Pov_Pvt.G_SQL_SQLCODE_TOKEN,
425 p_token1_value =>SQLCODE,
426 p_token2 =>Okl_Pov_Pvt.G_SQL_SQLERRM_TOKEN,
427 p_token2_value =>SQLERRM);
428 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
429 RETURN(l_return_status);
430
431 END Validate_Attributes;
432
433 -----END OF VALIDATE ATTRIBUTES-------------------------
434
435
436 ---------------------------------------------------------------------------
437 -- PROCEDURE insert_povalues for: Okl_Pdt_opt_vals_V
438 ---------------------------------------------------------------------------
439
440 PROCEDURE insert_povalues(p_api_version IN NUMBER,
441 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2,
445 p_pdtv_rec IN pdtv_rec_type,
446 p_optv_rec IN optv_rec_type,
447 p_povv_rec IN povv_rec_type,
448 x_povv_rec OUT NOCOPY povv_rec_type
449 ) IS
450 l_api_version CONSTANT NUMBER := 1;
451 l_api_name CONSTANT VARCHAR2(30) := 'insert_povalues';
452 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
453 l_valid BOOLEAN;
454 l_povv_rec povv_rec_type;
455 l_ponv_rec ponv_rec_type;
456 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
457 l_row_notfound BOOLEAN := TRUE;
458 BEGIN
459 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
460
461 l_povv_rec := p_povv_rec;
462
463 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
464 p_pkg_name => G_PKG_NAME,
465 p_init_msg_list => p_init_msg_list,
466 l_api_version => l_api_version,
467 p_api_version => p_api_version,
468 p_api_type => '_PVT',
469 x_return_status => l_return_status);
470
471
472 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
473 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
474 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
475 RAISE Okl_Api.G_EXCEPTION_ERROR;
476 END IF;
477
478 --- Validate all non-missing attributes (Item Level Validation)
479 l_return_status := Validate_Attributes(l_povv_rec);
480 --- If any errors happen abort API
481 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
482 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
483 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
484 RAISE Okl_Api.G_EXCEPTION_ERROR;
485 END IF;
486
487
488 get_parent_dates( p_povv_rec => l_povv_rec,
489 x_no_data_found => l_row_notfound,
490 x_return_status => l_return_status,
491 x_ponv_rec => l_ponv_rec);
492
493 IF (l_row_notfound) THEN
494 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
495 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
496 RAISE Okl_Api.G_EXCEPTION_ERROR;
497 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
498 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
499 END IF;
500
501 --l_ptlv_rec := x_ptlv_rec;
502 --assign parent dates.
503
504 l_povv_rec.from_date := l_ponv_rec.from_date;
505 l_povv_rec.TO_DATE := l_ponv_rec.TO_DATE;
506
507 /* call check_constraints to check the validity of this relationship */
508 Check_Constraints(p_api_version => p_api_version,
509 p_init_msg_list => p_init_msg_list,
510 p_povv_rec => l_povv_rec,
511 x_return_status => l_return_status,
512 x_msg_count => x_msg_count,
513 x_msg_data => x_msg_data,
514 x_valid => l_valid);
515
516 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
517 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
518 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
519 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
520 l_valid <> TRUE) THEN
521 x_return_status := Okl_Api.G_RET_STS_ERROR;
522 RAISE Okl_Api.G_EXCEPTION_ERROR;
523 END IF;
524
525 /* public api to insert povalues */
526
527 Okl_Pdt_Opt_Vals_Pub.insert_pdt_opt_vals(p_api_version => p_api_version,
528 p_init_msg_list => p_init_msg_list,
529 x_return_status => l_return_status,
530 x_msg_count => x_msg_count,
531 x_msg_data => x_msg_data,
532 p_povv_rec => l_povv_rec,
533 x_povv_rec => x_povv_rec);
534
535 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
536 RAISE Okl_Api.G_EXCEPTION_ERROR;
537 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
538 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
539
540 END IF;
541
542 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
543 x_msg_data => x_msg_data);
544 EXCEPTION
545 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
546 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
547 p_pkg_name => G_PKG_NAME,
548 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data,
551 p_api_type => '_PVT');
552 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
553 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
554 p_pkg_name => G_PKG_NAME,
555 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
556 x_msg_count => x_msg_count,
557 x_msg_data => x_msg_data,
558 p_api_type => '_PVT');
559 WHEN OTHERS THEN
560 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
561 p_pkg_name => G_PKG_NAME,
562 p_exc_name => 'OTHERS',
563 x_msg_count => x_msg_count,
564 x_msg_data => x_msg_data,
565 p_api_type => '_PVT');
566
567 END insert_povalues;
568
569 ---------------------------------------------------------------------------
570 -- PROCEDURE delete_povalues for: Okl_Pdt_opt_vals_V
571 -- This allows the user to delete table of records
572 ---------------------------------------------------------------------------
573 PROCEDURE delete_povalues(
574 p_api_version IN NUMBER
575 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
576 ,x_return_status OUT NOCOPY VARCHAR2
577 ,x_msg_count OUT NOCOPY NUMBER
578 ,x_msg_data OUT NOCOPY VARCHAR2
579 ,p_pdtv_rec IN pdtv_rec_type
580 ,p_optv_rec IN optv_rec_type
581 ,p_povv_tbl IN povv_tbl_type
582 ) IS
583 l_api_version CONSTANT NUMBER := 1;
584 l_povv_tbl povv_tbl_type;
585 l_api_name CONSTANT VARCHAR2(30) := 'delete_povalues';
586 l_povv_rec povv_rec_type;
587 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
588 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
589 i NUMBER;
590 l_valid BOOLEAN;
591
592 BEGIN
593
594 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
595
596 l_povv_tbl := p_povv_tbl;
597
598 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
599 p_pkg_name => G_PKG_NAME,
600 p_init_msg_list => p_init_msg_list,
601 l_api_version => l_api_version,
602 p_api_version => p_api_version,
603 p_api_type => '_PVT',
604 x_return_status => l_return_status);
605
606 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
607 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
608 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
609 RAISE Okl_Api.G_EXCEPTION_ERROR;
610 END IF;
611
612 /* check if the product asked to delete is used by contracts if yes halt the process*/
613
614 IF (l_povv_tbl.COUNT > 0) THEN
615 i := l_povv_tbl.FIRST;
616 LOOP
617 /* call check_constraints to check the validity of this relationship */
618 Check_Constraints(p_api_version => p_api_version,
619 p_init_msg_list => p_init_msg_list,
620 p_povv_rec => l_povv_tbl(i),
621 x_return_status => l_return_status,
622 x_msg_count => x_msg_count,
623 x_msg_data => x_msg_data,
624 x_valid => l_valid);
625
626 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
627 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
628 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
629 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
630 l_valid <> TRUE) THEN
631 x_return_status := Okl_Api.G_RET_STS_ERROR;
632 RAISE Okl_Api.G_EXCEPTION_ERROR;
633 END IF;
634
635 EXIT WHEN (i = l_povv_tbl.LAST);
636
637 i := l_povv_tbl.NEXT(i);
638
639 END LOOP;
640 END IF;
641
642 /* delete product option values */
643 Okl_Pdt_Opt_Vals_Pub.delete_pdt_opt_vals(p_api_version => p_api_version,
644 p_init_msg_list => p_init_msg_list,
645 x_return_status => l_return_status,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data,
648 p_povv_tbl => l_povv_tbl);
649
650 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
651 RAISE Okl_Api.G_EXCEPTION_ERROR;
652 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
653 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
654 END IF;
655
656 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data);
658 EXCEPTION
659 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
660 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
661 p_pkg_name => G_PKG_NAME,
662 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
663 x_msg_count => x_msg_count,
664 x_msg_data => x_msg_data,
665 p_api_type => '_PVT');
666 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
667 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
668 p_pkg_name => G_PKG_NAME,
669
670 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
671 x_msg_count => x_msg_count,
672 x_msg_data => x_msg_data,
673 p_api_type => '_PVT');
674 WHEN OTHERS THEN
675 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
676 p_pkg_name => G_PKG_NAME,
677 p_exc_name => 'OTHERS',
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data,
680 p_api_type => '_PVT');
681
682 END delete_povalues;
683
684 END Okl_Setuppovalues_Pvt;