[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPOPTIONS_PVT
Source
1 Package BODY Okl_Setuppoptions_Pvt AS
2 /* $Header: OKLRSPOB.pls 115.14 2003/07/23 18:36:47 sgorantl noship $ */
3
4 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_TABLE_NAME'; --- CHG001
5 G_UNQS CONSTANT VARCHAR2(200) := 'OKL_NOT_UNIQUE'; --- CHG001
6 G_COLUMN_TOKEN CONSTANT VARCHAR2(100) := 'COLUMN';
7 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
8
9 ---------------------------------------------------------------------------
10 -- PROCEDURE get_rec for: OKL_PDT_OPTS_V
11 ---------------------------------------------------------------------------
12 PROCEDURE get_rec (
13 p_ponv_rec IN ponv_rec_type,
14 x_no_data_found OUT NOCOPY BOOLEAN,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_ponv_rec OUT NOCOPY ponv_rec_type
17 ) IS
18 CURSOR okl_ponv_pk_csr (p_id IN NUMBER) IS
19 SELECT
20 ID,
21 OBJECT_VERSION_NUMBER,
22 OPT_ID,
23 PDT_ID,
24 FROM_DATE,
25 TO_DATE,
26 OPTIONAL_YN,
27 CREATED_BY,
28 CREATION_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATE_LOGIN
32 FROM Okl_Pdt_Opts_V
33 WHERE okl_pdt_opts_v.id = p_id;
34 l_okl_ponv_pk okl_ponv_pk_csr%ROWTYPE;
35 l_ponv_rec ponv_rec_type;
36 BEGIN
37 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
38 x_no_data_found := TRUE;
39 -- Get current database values
40 OPEN okl_ponv_pk_csr (p_ponv_rec.id);
41 FETCH okl_ponv_pk_csr INTO
42 l_ponv_rec.ID,
43 l_ponv_rec.OBJECT_VERSION_NUMBER,
44 l_ponv_rec.OPT_ID,
45 l_ponv_rec.PDT_ID,
46 l_ponv_rec.FROM_DATE,
47 l_ponv_rec.TO_DATE,
48 l_ponv_rec.OPTIONAL_YN,
49 l_ponv_rec.CREATED_BY,
50 l_ponv_rec.CREATION_DATE,
51 l_ponv_rec.LAST_UPDATED_BY,
52 l_ponv_rec.LAST_UPDATE_DATE,
53 l_ponv_rec.LAST_UPDATE_LOGIN;
54 x_no_data_found := okl_ponv_pk_csr%NOTFOUND;
55 CLOSE okl_ponv_pk_csr;
56 x_ponv_rec := l_ponv_rec;
57 EXCEPTION
58 WHEN OTHERS THEN
59 -- store SQL error message on message stack
60 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
61 p_msg_name => G_UNEXPECTED_ERROR,
62 p_token1 => G_SQLCODE_TOKEN,
63 p_token1_value => SQLCODE,
64 p_token2 => G_SQLERRM_TOKEN,
65 p_token2_value => SQLERRM);
66 -- notify UNEXPECTED error for calling API.
67 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
68
69 IF (okl_ponv_pk_csr%ISOPEN) THEN
70 CLOSE okl_ponv_pk_csr;
71 END IF;
72
73 END get_rec;
74
75
76 ---------------------------------------------------------------------------
77 -- PROCEDURE get_parent_dates for: OKL_PDT_OPTS_V
78 ---------------------------------------------------------------------------
79
80 PROCEDURE get_parent_dates(
81 p_ponv_rec IN ponv_rec_type,
82 x_no_data_found OUT NOCOPY BOOLEAN,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_pdtv_rec OUT NOCOPY pdtv_rec_type
85 ) IS
86 CURSOR okl_pdt_pk_csr (p_pdt_id IN NUMBER) IS
87 SELECT FROM_DATE,
88 TO_DATE
89 FROM Okl_products_V pdtv
90 WHERE pdtv.id = p_pdt_id;
91 l_okl_pdtv_pk okl_pdt_pk_csr%ROWTYPE;
92 l_pdtv_rec pdtv_rec_type;
93 BEGIN
94 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
95 x_no_data_found := TRUE;
96 -- Get current database values
97 OPEN okl_pdt_pk_csr (p_ponv_rec.pdt_id);
98 FETCH okl_pdt_pk_csr INTO
99 l_pdtv_rec.FROM_DATE,
100 l_pdtv_rec.TO_DATE;
101 x_no_data_found := okl_pdt_pk_csr%NOTFOUND;
102 CLOSE okl_pdt_pk_csr;
103 x_pdtv_rec := l_pdtv_rec;
104 EXCEPTION
105 WHEN OTHERS THEN
106 -- store SQL error message on message stack
107 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
108 p_msg_name => G_UNEXPECTED_ERROR,
109 p_token1 => G_SQLCODE_TOKEN,
110 p_token1_value => SQLCODE,
111 p_token2 => G_SQLERRM_TOKEN,
112 p_token2_value => SQLERRM);
113 -- notify UNEXPECTED error for calling API.
114 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
115
116 IF (okl_pdt_pk_csr%ISOPEN) THEN
117 CLOSE okl_pdt_pk_csr;
118 END IF;
119
120 END get_parent_dates;
121
122 -----------------------------------------------------------------------------
123 -- PROCEDURE check_constraints for: OKL_PDT_OPTS_V
124 -----------------------------------------------------------------------------
125
126 PROCEDURE Check_Constraints (
127 p_api_version IN NUMBER,
128 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
129 p_ponv_rec IN ponv_rec_type,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_msg_count OUT NOCOPY NUMBER,
132 x_msg_data OUT NOCOPY VARCHAR2,
133 x_valid OUT NOCOPY BOOLEAN
134 ) IS
135 CURSOR okl_ponv_chk_upd(p_pdt_id NUMBER
136 ) IS
137 SELECT '1' FROM okl_k_headers_v khdr
138 WHERE khdr.pdt_id = p_pdt_id;
139
140 CURSOR okl_pon_pdt_fk_csr (p_pdt_id IN Okl_Products_V.ID%TYPE,
141 p_date IN Okl_Products_V.TO_DATE%TYPE
142 ) IS
143 SELECT '1'
144 FROM Okl_products_V pdt
145 WHERE pdt.ID = p_pdt_id
146 AND NVL(pdt.TO_DATE, p_date) < p_date;
147
148 CURSOR okl_pon_constraints_csr(p_opt_id IN Okl_Options_V.ID%TYPE,
149 p_from_date IN Okl_Options_V.FROM_DATE%TYPE,
150 p_to_date IN Okl_Options_V.TO_DATE%TYPE
151 ) IS
152 SELECT '1'
153 FROM Okl_options_V opt
154 WHERE opt.ID = p_opt_id
155 AND ((opt.FROM_DATE > p_from_date OR
156 p_from_date > NVL(opt.TO_DATE,p_from_date)) OR
157 NVL(opt.TO_DATE, p_to_date) < p_to_date);
158
159 CURSOR get_opt_ruls(p_opt_id NUMBER
160 ) IS
161 SELECT opt_id,rgr_rdf_code,
162 lrg_lse_id,rgr_rgd_code,
163 srd_id_for,lrg_srd_id
164 FROM okl_opt_rules
165 WHERE opt_id = p_opt_id;
166
167 CURSOR get_pdt_ruls(p_pdt_id NUMBER
168 ) IS
169 SELECT pon.pdt_id,pon.opt_id,
170 orl.rgr_rdf_code,orl.lrg_lse_id,
171 orl.rgr_rgd_code,orl.srd_id_for,
172 orl.lrg_srd_id
173 FROM okl_pdt_opts pon,
174 okl_opt_rules orl
175 WHERE pon.opt_id = orl.opt_id
176 AND pon.pdt_id = p_pdt_id;
177
178 CURSOR okl_pdt_opts_unique (p_unique1 OKL_PDT_OPTS.OPT_ID%TYPE, p_unique2 OKL_PDT_OPTS.PDT_ID%TYPE) IS
179 SELECT '1'
180 FROM OKL_PDT_OPTS_V
181 WHERE OKL_PDT_OPTS_V.OPT_ID = p_unique1 AND
182 OKL_PDT_OPTS_V.PDT_ID = p_unique2 AND
183 OKL_PDT_OPTS_V.ID <> NVL(p_ponv_rec.id,-9999);
184
185 l_unique_key OKL_PDT_OPTS_V.OPT_ID%TYPE;
186 l_check VARCHAR2(1) := '?';
187 l_row_not_found BOOLEAN := FALSE;
188 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/RRRR'), 'DD/MM/RRRR');
189 l_token_1 VARCHAR2(9999);
190 l_token_2 VARCHAR2(9999);
191 l_token_3 VARCHAR2(9999);
192 l_token_4 VARCHAR2(9999);
193 l_token_5 VARCHAR2(9999);
194 l_token_6 VARCHAR2(9999);
195
196 BEGIN
197 x_valid := TRUE;
198 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
199
200 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_OPTION_SERCH',
201 p_attribute_code => 'OKL_PRODUCT_OPTIONS');
202
203
204 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_SERCH',
205 p_attribute_code => 'OKL_PRODUCTS');
206
207 l_token_3 := l_token_1 ||','||l_token_2;
208
209
210 l_token_4 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_SERCH',
211 p_attribute_code => 'OKL_OPTIONS');
212
213
214 l_token_5 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
215 p_attribute_code => 'OKL_KDTLS_CONTRACT');
216
217 l_token_6 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_CRUPD',
218 p_attribute_code => 'OKL_OPTION');
219
220
221 -- Check for pqvv valid dates
222 OPEN okl_ponv_chk_upd(p_ponv_rec.pdt_id);
223
224 FETCH okl_ponv_chk_upd INTO l_check;
225 l_row_not_found := okl_ponv_chk_upd%NOTFOUND;
226 CLOSE okl_ponv_chk_upd;
227
228
229 IF l_row_not_found = FALSE THEN
230 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
231 p_msg_name => G_IN_USE,
232 p_token1 => G_PARENT_TABLE_TOKEN,
233 p_token1_value => l_token_1,
234 p_token2 => G_CHILD_TABLE_TOKEN,
235 p_token2_value => l_token_5);
236 x_valid := FALSE;
237 x_return_status := Okl_Api.G_RET_STS_ERROR;
238 RAISE G_EXCEPTION_HALT_PROCESSING;
239 END IF;
240
241 -- Check if the product to which the option are added is not
242 -- in the past
243 /*OPEN okl_pon_pdt_fk_csr (p_ponv_rec.pdt_id,
244 l_sysdate);
245 FETCH okl_pon_pdt_fk_csr INTO l_check;
246 l_row_not_found := okl_pon_pdt_fk_csr%NOTFOUND;
247 CLOSE okl_pon_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 --CHECK FOR UNIQUENESS
258 IF p_ponv_rec.id = Okl_Api.G_MISS_NUM THEN
259 OPEN okl_pdt_opts_unique (p_ponv_rec.opt_id, p_ponv_rec.pdt_id);
260 FETCH okl_pdt_opts_unique INTO l_unique_key;
261 IF okl_pdt_opts_unique%FOUND THEN
262 --Okl_Api.set_message(G_APP_NAME,G_UNQS, G_TABLE_TOKEN,l_token_6);
263 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
264 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
265 p_token1 => G_TABLE_TOKEN,
266 p_token1_value => l_token_1,
267 p_token2 => G_COLUMN_TOKEN,
268 p_token2_value => l_token_6);
269 x_valid := FALSE;
270 x_return_status := Okl_Api.G_RET_STS_ERROR;
271 RAISE G_EXCEPTION_HALT_PROCESSING;
272 ELSE
273 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
274 END IF;
275 CLOSE okl_pdt_opts_unique;
276 END IF;
277
278 -- Check for constraints dates
279 IF p_ponv_rec.id = Okl_Api.G_MISS_NUM THEN
280 OPEN okl_pon_constraints_csr(p_ponv_rec.opt_id,
281 p_ponv_rec.from_date,
282 p_ponv_rec.TO_DATE);
283 FETCH okl_pon_constraints_csr INTO l_check;
284 l_row_not_found := okl_pon_constraints_csr%NOTFOUND;
285 CLOSE okl_pon_constraints_csr;
286
287 IF l_row_not_found = FALSE THEN
288 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
289 p_msg_name => G_DATES_MISMATCH,
290 p_token1 => G_PARENT_TABLE_TOKEN,
291 p_token1_value => l_token_4,
292 p_token2 => G_CHILD_TABLE_TOKEN,
293 p_token2_value => l_token_3);
294 x_valid := FALSE;
295 x_return_status := Okl_Api.G_RET_STS_ERROR;
296 RAISE G_EXCEPTION_HALT_PROCESSING;
297 END IF;
298 END IF;
299
300 -- Check for rule Overlap
301 IF p_ponv_rec.id = Okl_Api.G_MISS_NUM THEN
302 FOR i IN get_opt_ruls(p_ponv_rec.opt_id)
303 LOOP
304 FOR j IN get_pdt_ruls(p_ponv_rec.pdt_id)
305 LOOP
306 IF i.lrg_lse_id IS NULL THEN
307 IF j.lrg_lse_id IS NULL THEN
308 IF (i.srd_id_for = j.srd_id_for AND
309 i.rgr_rgd_code = j.rgr_rgd_code AND
310 i.rgr_rdf_code = j.rgr_rdf_code) AND
311 i.opt_id <> j.opt_id THEN
312 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
313 p_msg_name => G_OPTION_DUPLICATE_RULE);
314 x_valid := FALSE;
315 x_return_status := Okl_Api.G_RET_STS_ERROR;
316 RAISE G_EXCEPTION_HALT_PROCESSING;
317 END IF;
318 END IF;
319 END IF;
320
321 IF i.lrg_lse_id IS NOT NULL THEN
322 IF j.lrg_lse_id IS NOT NULL THEN
323 IF (i.lrg_lse_id = j.lrg_lse_id AND
324 i.lrg_srd_id = j.lrg_srd_id AND
325 i.rgr_rgd_code = j.rgr_rgd_code AND
326 i.rgr_rdf_code = j.rgr_rdf_code) AND
327 i.opt_id <> j.opt_id THEN
328 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
329 p_msg_name => G_OPTION_DUPLICATE_RULE);
330 x_valid := FALSE;
331 x_return_status := Okl_Api.G_RET_STS_ERROR;
332 RAISE G_EXCEPTION_HALT_PROCESSING;
333 END IF;
334 END IF;
335 END IF;
336 END LOOP;
337 END LOOP;
338 END IF;
339
340 EXCEPTION
341 WHEN G_EXCEPTION_HALT_PROCESSING THEN
342 -- no processing necessary; validation can continue
343 -- with the next column
344 NULL;
345 WHEN OTHERS THEN
346 -- store SQL error message on message stack
347 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
348 p_msg_name => G_UNEXPECTED_ERROR,
349 p_token1 => G_SQLCODE_TOKEN,
350 p_token1_value => SQLCODE,
351 p_token2 => G_SQLERRM_TOKEN,
352 p_token2_value => SQLERRM);
353 x_valid := FALSE;
354 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
355
356 IF (okl_ponv_chk_upd%ISOPEN) THEN
357 CLOSE okl_ponv_chk_upd;
358 END IF;
359
360 IF (okl_pon_pdt_fk_csr%ISOPEN) THEN
361 CLOSE okl_pon_pdt_fk_csr;
362 END IF;
363
364 IF (okl_pon_constraints_csr%ISOPEN) THEN
365 CLOSE okl_pon_constraints_csr;
366 END IF;
367
368 IF (okl_pdt_opts_unique%ISOPEN) THEN
369 CLOSE okl_pdt_opts_unique;
370 END IF;
371
372 END Check_Constraints;
373
374 ---------------------------------------------------------------------------
375 -- PROCEDURE Validate _Opt_Id
376 ---------------------------------------------------------------------------
377 -- Start of comments
378 --
379 -- Procedure Name : Validate _Opt_Id
380 -- Description :
381 -- Business Rules :
382 -- Parameters :
383 -- Version : 1.0
384 -- End of comments
385 ---------------------------------------------------------------------------
386
387 PROCEDURE Validate_Opt_Id (
388 p_ponv_rec IN ponv_rec_type,
389 x_return_status OUT NOCOPY VARCHAR2
390 ) IS
391 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
392 CURSOR okl_pdt_opts_foreign1 (p_foreign OKL_PDT_OPTS.OPT_ID%TYPE) IS
393 SELECT ID
394 FROM OKL_OPTIONS_V
395 WHERE OKL_OPTIONS_V.ID = p_foreign;
396
397 l_foreign_key OKL_PDT_OPTS_V.OPT_ID%TYPE;
398 l_token_1 VARCHAR2(999);
399
400 BEGIN
401 -- initialize return status
402 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
403
404 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_OPTION_CRUPD',
405 p_attribute_code => 'OKL_OPTION');
406
407 IF p_ponv_rec.opt_id = Okl_Api.G_MISS_NUM OR
408 p_ponv_rec.opt_id IS NULL
409 THEN
410 Okl_Api.set_message(Okl_Pon_Pvt.G_APP_NAME, Okl_Pon_Pvt.G_REQUIRED_VALUE,Okl_Pon_Pvt.G_COL_NAME_TOKEN,l_token_1);
411 x_return_status := Okl_Api.G_RET_STS_ERROR;
412 RAISE G_ITEM_NOT_FOUND_ERROR;
413 END IF;
414
415 IF p_ponv_rec.opt_id IS NOT NULL THEN
416 OPEN okl_pdt_opts_foreign1 (p_ponv_rec.opt_id);
417 FETCH okl_pdt_opts_foreign1 INTO l_foreign_key;
418 IF okl_pdt_opts_foreign1%NOTFOUND THEN
419 Okl_Api.set_message(Okl_Pon_Pvt.G_APP_NAME, Okl_Pon_Pvt.G_INVALID_VALUE,Okl_Pon_Pvt.G_COL_NAME_TOKEN,l_token_1);
420 x_return_status := Okl_Api.G_RET_STS_ERROR;
421 RAISE G_ITEM_NOT_FOUND_ERROR;
422 ELSE
423 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
424 END IF;
425 CLOSE okl_pdt_opts_foreign1;
426 END IF;
427
428 EXCEPTION
429 WHEN G_ITEM_NOT_FOUND_ERROR THEN
430 NULL;
431 WHEN OTHERS THEN
432 Okl_Api.set_message(p_app_name =>Okl_Pon_Pvt.G_APP_NAME,
433 p_msg_name =>Okl_Pon_Pvt.G_UNEXPECTED_ERROR,
434 p_token1 =>Okl_Pon_Pvt.G_SQL_SQLCODE_TOKEN,
435 p_token1_value =>SQLCODE,
436 p_token2 =>Okl_Pon_Pvt.G_SQL_SQLERRM_TOKEN,
437 p_token2_value =>SQLERRM);
438 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
439
440 END Validate_Opt_Id;
441 ------end of Validate_Opt_Id-----------------------------------
442
443 ---------------------------------------------------------------------------
444 -- FUNCTION Validate _Attribute
445 ---------------------------------------------------------------------------
446 -- Start of comments
447 --
448 -- Procedure Name : Validate _Attribute
449 -- Description :
450 -- Business Rules :
451 -- Parameters :
452 -- Version : 1.0
453 -- End of comments
454 ---------------------------------------------------------------------------
455 FUNCTION Validate_Attributes(
456 p_ponv_rec IN ponv_rec_type
457 ) RETURN VARCHAR IS
458 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
459 l_return_status VARCHAR2(1):= Okl_Api.G_RET_STS_SUCCESS;
460
461
462 BEGIN
463 -----CHECK FOR OPT_ID----------------------------
464 Validate_Opt_Id (p_ponv_rec,x_return_status);
465 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
466 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
467 -- need to leave
468 l_return_status := x_return_status;
469 RAISE G_EXCEPTION_HALT_PROCESSING;
470 ELSE
471 l_return_status := x_return_status;
472 END IF;
473 END IF;
474
475 RETURN(l_return_status);
476
477 EXCEPTION
478 WHEN G_EXCEPTION_HALT_PROCESSING THEN
479 -- just come out with return status
480 NULL;
481 RETURN (l_return_status);
482
483 WHEN OTHERS THEN
484 Okl_Api.set_message(p_app_name =>Okl_Pon_Pvt.G_APP_NAME,
485 p_msg_name =>Okl_Pon_Pvt.G_UNEXPECTED_ERROR,
486 p_token1 =>Okl_Pon_Pvt.G_SQL_SQLCODE_TOKEN,
487 p_token1_value =>SQLCODE,
488 p_token2 =>Okl_Pon_Pvt.G_SQL_SQLERRM_TOKEN,
489 p_token2_value =>SQLERRM);
490 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
491 RETURN(l_return_status);
492
493 END Validate_Attributes;
494
495
496
497 ---------------------------------------------------------------------------
498 -- PROCEDURE insert_poptions for: OKL_PDT_OPTS_V
499 ---------------------------------------------------------------------------
500
501 PROCEDURE insert_poptions(p_api_version IN NUMBER,
502 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_count OUT NOCOPY NUMBER,
505 x_msg_data OUT NOCOPY VARCHAR2,
506 p_pdtv_rec IN pdtv_rec_type,
507 p_ponv_rec IN ponv_rec_type,
508 x_ponv_rec OUT NOCOPY ponv_rec_type
509 ) IS
510
511 l_api_version CONSTANT NUMBER := 1;
512 l_api_name CONSTANT VARCHAR2(30) := 'insert_poptions';
513 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
514 l_valid BOOLEAN;
515 l_ponv_rec ponv_rec_type;
516 l_pdtv_rec pdtv_rec_type;
517 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/RRRR'), 'DD/MM/RRRR');
518 l_row_notfound BOOLEAN := TRUE;
519 BEGIN
520 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
521
522 l_ponv_rec := p_ponv_rec;
523
524 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
525 p_pkg_name => G_PKG_NAME,
526 p_init_msg_list => p_init_msg_list,
527 l_api_version => l_api_version,
528 p_api_version => p_api_version,
529 p_api_type => '_PVT',
530 x_return_status => l_return_status);
531
532 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
533 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
534 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
535 RAISE Okl_Api.G_EXCEPTION_ERROR;
536 END IF;
537
538 --- Validate all non-missing attributes (Item Level Validation)
539 l_return_status := Validate_Attributes(l_ponv_rec);
540 --- If any errors happen abort API
541 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
542 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
543 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
544 RAISE Okl_Api.G_EXCEPTION_ERROR;
545 END IF;
546
547 get_parent_dates(p_ponv_rec => l_ponv_rec,
548 x_no_data_found => l_row_notfound,
549 x_return_status => l_return_status,
550 x_pdtv_rec => l_pdtv_rec);
551
552 IF (l_row_notfound) THEN
553 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
554 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
555 RAISE Okl_Api.G_EXCEPTION_ERROR;
556 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
557 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
558 END IF;
559
560 --assign parent dates.
561
562 l_ponv_rec.from_date := l_pdtv_rec.from_date;
563 l_ponv_rec.TO_DATE := l_pdtv_rec.TO_DATE;
564
565 /* check if the products is already used by contracts if yes halt the process*/
566
567 Check_Constraints(p_api_version => p_api_version,
568 p_init_msg_list => p_init_msg_list,
569 p_ponv_rec => l_ponv_rec,
570 x_return_status => l_return_status,
571 x_msg_count => x_msg_count,
572 x_msg_data => x_msg_data,
573 x_valid => l_valid);
574
575 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
576 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
577 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
578 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
579 l_valid <> TRUE) THEN
580 x_return_status := Okl_Api.G_RET_STS_ERROR;
581 RAISE Okl_Api.G_EXCEPTION_ERROR;
582 END IF;
583
584 /* public api to insert poptions */
585 Okl_Product_Options_Pub.insert_product_options(p_api_version => p_api_version,
586 p_init_msg_list => p_init_msg_list,
587 x_return_status => l_return_status,
588 x_msg_count => x_msg_count,
589 x_msg_data => x_msg_data,
590 p_ponv_rec => l_ponv_rec,
591 x_ponv_rec => x_ponv_rec);
592
593 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
594 RAISE Okl_Api.G_EXCEPTION_ERROR;
595 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
596 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
597 END IF;
598
599 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
600 x_msg_data => x_msg_data);
601 EXCEPTION
602 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
603 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
604 p_pkg_name => G_PKG_NAME,
605 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
606 x_msg_count => x_msg_count,
607 x_msg_data => x_msg_data,
608 p_api_type => '_PVT');
609 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
610 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
611 p_pkg_name => G_PKG_NAME,
612 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
613 x_msg_count => x_msg_count,
614 x_msg_data => x_msg_data,
615 p_api_type => '_PVT');
616 WHEN OTHERS THEN
617 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
618 p_pkg_name => G_PKG_NAME,
619 p_exc_name => 'OTHERS',
620 x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data,
622 p_api_type => '_PVT');
623
624 END insert_poptions;
625
626 ---------------------------------------------------------------------------
627 -- PROCEDURE delete_pdt_pqys for: OKL_PDT_OPTS_V
628 -- Private procedure called from delete_poptions.
629 ---------------------------------------------------------------------------
630
631 PROCEDURE delete_pdt_opt_vals(
632 p_api_version IN NUMBER
633 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
634 ,x_return_status OUT NOCOPY VARCHAR2
635 ,x_msg_count OUT NOCOPY NUMBER
636 ,x_msg_data OUT NOCOPY VARCHAR2
637 ,p_pdtv_rec IN pdtv_rec_type
638 ,p_ponv_rec IN ponv_rec_type) IS
639
640 i PLS_INTEGER :=0;
641 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
642 l_del_povv_tbl Okl_Pdt_Opt_Vals_Pub.povv_tbl_type;
643
644 CURSOR pov_csr IS
645 SELECT povv.id
646 FROM okl_pdt_opt_vals_v povv
647 WHERE povv.pon_id = p_ponv_rec.id;
648
649 BEGIN
650
651 FOR pov_rec IN pov_csr
652 LOOP
653 i := i + 1;
654 l_del_povv_tbl(i).id := pov_rec.id;
655 END LOOP;
656 IF l_del_povv_tbl.COUNT > 0 THEN
657 /* public api to delete product option values */
658 Okl_Pdt_Opt_Vals_Pub.delete_pdt_opt_vals(p_api_version => p_api_version,
659 p_init_msg_list => p_init_msg_list,
660 x_return_status => l_return_status,
661 x_msg_count => x_msg_count,
662 x_msg_data => x_msg_data,
663 p_povv_tbl => l_del_povv_tbl);
664
665 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
666 RAISE G_EXCEPTION_HALT_PROCESSING;
667 ELSE
668 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
669 l_return_status := x_return_status;
670 END IF;
671 END IF;
672 END IF;
673 --Delete the Master
674 Okl_Product_Options_Pub.delete_product_options(p_api_version => p_api_version,
675 p_init_msg_list => p_init_msg_list,
676 x_return_status => l_return_status,
677 x_msg_count => x_msg_count,
678 x_msg_data => x_msg_data,
679 p_ponv_rec => p_ponv_rec);
680
681 IF x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
682 RAISE G_EXCEPTION_HALT_PROCESSING;
683 ELSE
684 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
685 l_return_status := x_return_status;
686 END IF;
687 END IF;
688 EXCEPTION
689 WHEN G_EXCEPTION_HALT_PROCESSING THEN
690 NULL;
691 WHEN OTHERS THEN
692 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
693 ,p_msg_name => g_unexpected_error
694 ,p_token1 => g_sqlcode_token
695 ,p_token1_value => SQLCODE
696 ,p_token2 => g_sqlerrm_token
697 ,p_token2_value => SQLERRM);
698
699 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
700 END delete_pdt_opt_vals;
701
702
703 ----------------------------------------------------------------------------
704 -- PROCEDURE delete_poptions for: OKL_PDT_OPTS_V
705 -- This allows the user to delete table of records
706 ----------------------------------------------------------------------------
707
708 PROCEDURE delete_poptions(
709 p_api_version IN NUMBER
710 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
711 ,x_return_status OUT NOCOPY VARCHAR2
712 ,x_msg_count OUT NOCOPY NUMBER
713 ,x_msg_data OUT NOCOPY VARCHAR2
714 ,p_pdtv_rec IN pdtv_rec_type
715 ,p_ponv_tbl IN ponv_tbl_type
716 ) IS
717
718 l_del_povv_tbl Okl_Pdt_Opt_Vals_Pub.povv_tbl_type;
719 l_loop_ctr NUMBER := 1;
720 l_api_version CONSTANT NUMBER := 1;
721 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS; --TCHGS
722 i PLS_INTEGER :=0;
723 l_ponv_tbl ponv_tbl_type;
724 l_pdtv_rec pdtv_rec_type;
725 l_api_name CONSTANT VARCHAR2(30) := 'delete_poptions';
726 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
727 l_valid BOOLEAN;
728
729 BEGIN
730 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
731
732 l_ponv_tbl := p_ponv_tbl;
733 l_pdtv_rec := p_pdtv_rec;
734
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
743 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
744 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
745 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
746 RAISE Okl_Api.G_EXCEPTION_ERROR;
747 END IF;
748
749 IF (l_ponv_tbl.COUNT > 0) THEN
750 i := p_ponv_tbl.FIRST;
751 LOOP
752 /* check if the product asked to delete is used by contracts if yes halt the process*/
753
754 Check_Constraints(p_api_version => p_api_version,
755 p_init_msg_list => p_init_msg_list,
756 p_ponv_rec => l_ponv_tbl(i),
757 x_return_status => l_return_status,
758 x_msg_count => x_msg_count,
759 x_msg_data => x_msg_data,
760 x_valid => l_valid);
761
762 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
763 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
764 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
765 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
766 l_valid <> TRUE) THEN
767 x_return_status := Okl_Api.G_RET_STS_ERROR;
768 RAISE Okl_Api.G_EXCEPTION_ERROR;
769 END IF;
770
771 delete_pdt_opt_vals(p_api_version => p_api_version
772 ,p_init_msg_list => p_init_msg_list
773 ,x_return_status => x_return_status
774 ,x_msg_count => x_msg_count
775 ,x_msg_data => x_msg_data
776 ,p_pdtv_rec => l_pdtv_rec
777 ,p_ponv_rec => l_ponv_tbl(i)
778 );
779 -- TCHGS: Store the highest degree of error
780 IF x_return_status <> Okl_Api.G_RET_STS_SUCCESS THEN
781 IF l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR THEN
782 l_overall_status := x_return_status;
783 END IF;
784 END IF;
785 EXIT WHEN (i = p_ponv_tbl.LAST);
786 i := p_ponv_tbl.NEXT(i);
787 END LOOP;
788 --TCHGS: return overall status
789 x_return_status := l_overall_status;
790 END IF;
791
792 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
793 RAISE Okl_Api.G_EXCEPTION_ERROR;
794 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
795 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
796 END IF;
797
798 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
799 x_msg_data => x_msg_data);
800 EXCEPTION
801 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
802 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
803 p_pkg_name => G_PKG_NAME,
804 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
805 x_msg_count => x_msg_count,
806 x_msg_data => x_msg_data,
807 p_api_type => '_PVT');
808 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
809 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
810 p_pkg_name => G_PKG_NAME,
811 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
812 x_msg_count => x_msg_count,
813 x_msg_data => x_msg_data,
814 p_api_type => '_PVT');
815 WHEN OTHERS THEN
816 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
817 p_pkg_name => G_PKG_NAME,
818 p_exc_name => 'OTHERS',
819 x_msg_count => x_msg_count,
820 x_msg_data => x_msg_data,
821 p_api_type => '_PVT');
822
823 END delete_poptions;
824
825 END Okl_Setuppoptions_Pvt;