[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPPMVALUES_PVT
Source
1 Package BODY Okl_Setuppmvalues_Pvt AS
2 /* $Header: OKLRSMVB.pls 120.2 2007/03/04 09:53:56 dcshanmu ship $ */
3
4 G_ITEM_NOT_FOUND_ERROR EXCEPTION;
5 G_COLUMN_TOKEN CONSTANT VARCHAR2(100) := 'COLUMN';
6 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_TABLE_NAME'; --- CHG001
7 ---------------------------------------------------------------------------
8 -- PROCEDURE get_rec for: OKL_PTL_PTQ_VALS_V
9 ---------------------------------------------------------------------------
10
11 PROCEDURE get_rec (
12 p_pmvv_rec IN pmvv_rec_type,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_no_data_found OUT NOCOPY BOOLEAN,
15 x_pmvv_rec OUT NOCOPY pmvv_rec_type
16 ) IS
17 CURSOR okl_pmvv_pk_csr (p_id IN NUMBER) IS
18 SELECT
19 ID,
20 OBJECT_VERSION_NUMBER,
21 PTV_ID,
22 PTL_ID,
23 PTQ_ID,
24 FROM_DATE,
25 TO_DATE,
26 CREATED_BY,
27 CREATION_DATE,
28 LAST_UPDATED_BY,
29 LAST_UPDATE_DATE,
30 LAST_UPDATE_LOGIN
31 FROM Okl_Ptl_Ptq_Vals_V
32 WHERE okl_ptl_ptq_vals_v.id = p_id;
33 l_okl_pmvv_pk okl_pmvv_pk_csr%ROWTYPE;
34 l_pmvv_rec pmvv_rec_type;
35 BEGIN
36 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
37 x_no_data_found := TRUE;
38 -- Get current database values
39 OPEN okl_pmvv_pk_csr (p_pmvv_rec.id);
40 FETCH okl_pmvv_pk_csr INTO
41 l_pmvv_rec.ID,
42 l_pmvv_rec.OBJECT_VERSION_NUMBER,
43 l_pmvv_rec.PTV_ID,
44 l_pmvv_rec.PTL_ID,
45 l_pmvv_rec.PTQ_ID,
46 l_pmvv_rec.FROM_DATE,
47 l_pmvv_rec.TO_DATE,
48 l_pmvv_rec.CREATED_BY,
49 l_pmvv_rec.CREATION_DATE,
50 l_pmvv_rec.LAST_UPDATED_BY,
51 l_pmvv_rec.LAST_UPDATE_DATE,
52 l_pmvv_rec.LAST_UPDATE_LOGIN;
53 x_no_data_found := okl_pmvv_pk_csr%NOTFOUND;
54 CLOSE okl_pmvv_pk_csr;
55 x_pmvv_rec := l_pmvv_rec;
56 EXCEPTION
57 WHEN OTHERS THEN
58 -- store SQL error message on message stack
59 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
60 p_msg_name => G_UNEXPECTED_ERROR,
61 p_token1 => G_SQLCODE_TOKEN,
62 p_token1_value => SQLCODE,
63 p_token2 => G_SQLERRM_TOKEN,
64 p_token2_value => SQLERRM);
65 -- notify UNEXPECTED error for calling API.
66 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
67
68 IF (okl_pmvv_pk_csr%ISOPEN) THEN
69 CLOSE okl_pmvv_pk_csr;
70 END IF;
71
72 END get_rec;
73
74 ---------------------------------------------------------------------------
75 -- PROCEDURE get_parent_dates for: OKL_PDT_TEMPLATES_V
76 ---------------------------------------------------------------------------
77
78 PROCEDURE get_parent_dates(
79 p_pmvv_rec IN pmvv_rec_type,
80 x_no_data_found OUT NOCOPY BOOLEAN,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_ptlv_rec OUT NOCOPY ptlv_rec_type
83 ) IS
84 CURSOR okl_ptl_pk_csr (p_ptl_id IN NUMBER) IS
85 SELECT FROM_DATE,
86 TO_DATE
87 FROM Okl_pdt_templates_V ptlv
88 WHERE ptlv.id = p_ptl_id;
89 l_okl_ptlv_pk okl_ptl_pk_csr%ROWTYPE;
90 l_ptlv_rec ptlv_rec_type;
91 BEGIN
92 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
93 x_no_data_found := TRUE;
94 -- Get current database values
95 OPEN okl_ptl_pk_csr (p_pmvv_rec.ptl_id);
96 FETCH okl_ptl_pk_csr INTO
97 l_ptlv_rec.FROM_DATE,
98 l_ptlv_rec.TO_DATE;
99 x_no_data_found := okl_ptl_pk_csr%NOTFOUND;
100 CLOSE okl_ptl_pk_csr;
101 x_ptlv_rec := l_ptlv_rec;
102 EXCEPTION
103 WHEN OTHERS THEN
104 -- store SQL error message on message stack
105 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
106 p_msg_name => G_UNEXPECTED_ERROR,
107 p_token1 => G_SQLCODE_TOKEN,
108 p_token1_value => SQLCODE,
109 p_token2 => G_SQLERRM_TOKEN,
110 p_token2_value => SQLERRM);
111
112 -- notify UNEXPECTED error for calling API.
113 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
114
115 IF (okl_ptl_pk_csr%ISOPEN) THEN
116 CLOSE okl_ptl_pk_csr;
117
118 END IF;
119
120 END get_parent_dates;
121
122 -----------------------------------------------------------------------------
123 -- PROCEDURE check_in_use for: Okl_Ptl_Ptq_Vals_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_pmvv_rec IN pmvv_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
136 CURSOR okl_pmvv_chk_csr(p_ptl_id NUMBER
137 ) IS
138 SELECT '1' FROM okl_pdt_templates_v ptvv,
139 okl_products pdtv,
140 okl_k_headers_v khdr
141 WHERE ptvv.id = p_ptl_id
142 AND ptvv.id = pdtv.ptl_id
143 AND pdtv.id = khdr.pdt_id;
144
145 CURSOR okl_pmv_ptl_fk_csr (p_ptl_id IN Okl_Products_V.ID%TYPE,
146 p_date IN Okl_Products_V.TO_DATE%TYPE
147 ) IS
148 SELECT '1'
149 FROM Okl_pdt_templates_V ptl
150 WHERE ptl.ID = p_ptl_id
151 AND NVL(ptl.TO_DATE, p_date) < p_date;
152
153 CURSOR okl_pmv_constraints_csr (p_ptv_id IN Okl_Ptq_Values_V.ID%TYPE,
154 p_from_date IN Okl_Ptq_Values_V.FROM_DATE%TYPE,
155 p_to_date IN Okl_Ptq_Values_V.TO_DATE%TYPE
156 ) IS
157 SELECT '1'
158 FROM Okl_Ptq_Values_V ptv
159 WHERE ptv.ID = p_ptv_id
160 AND ((ptv.FROM_DATE > p_from_date OR
161 p_from_date > NVL(ptv.TO_DATE,p_from_date)) OR
162 NVL(ptv.TO_DATE, p_to_date) < p_to_date);
163
164 CURSOR c1(p_ptl_id okl_ptl_ptq_vals_v.ptl_id%TYPE,
165 p_ptq_id okl_ptl_ptq_vals_v.ptq_id%TYPE) IS
166 SELECT '1'
167 FROM okl_ptl_ptq_vals_v
168 WHERE ptl_id = p_ptl_id
169 AND ptq_id = p_ptq_id
170 AND id <> NVL(p_pmvv_rec.id,-9999);
171
172 l_unq_tbl Okc_Util.unq_tbl_type;
173 l_pmv_status VARCHAR2(1);
174 l_row_found BOOLEAN := FALSE;
175 l_check VARCHAR2(1) := '?';
176 l_row_not_found BOOLEAN := FALSE;
177 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
178 l_token_1 VARCHAR2(1999);
179 l_token_2 VARCHAR2(1999);
180 l_token_3 VARCHAR2(1999);
181 l_token_4 VARCHAR2(1999);
182 l_token_5 VARCHAR2(1999);
183 l_token_6 VARCHAR2(1999);
184
185 BEGIN
186 x_valid := TRUE;
187 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
188
189 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_TMPL_QLTY_CREATE',
190 p_attribute_code => 'OKL_PDT_TMPL_QLTY_CREATE_TITLE');
191
192 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_CONTRACT_DTLS',
193 p_attribute_code => 'OKL_KDTLS_CONTRACT');
194
195 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PRODUCT_TEMPLATE_SERCH',
196 p_attribute_code => 'OKL_PRODUCT_TEMPLATES');
197
198 l_token_4 := l_token_1 ||','||l_token_3;
199
200 l_token_5 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_TMPVALS_CRUPD',
201 p_attribute_code => 'OKL_TEMPLATE_QUALITY_VALUES');
202
203 l_token_6 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_TMPL_QLTY_CREATE',
204 p_attribute_code => 'OKL_TEMPLATE_QUALITY');
205
206
207
208 OPEN okl_pmvv_chk_csr(p_pmvv_rec.ptl_id);
209
210 FETCH okl_pmvv_chk_csr INTO l_check;
211 l_row_not_found := okl_pmvv_chk_csr%NOTFOUND;
212 CLOSE okl_pmvv_chk_csr;
213
214 IF l_row_not_found = FALSE THEN
215 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
216 p_msg_name => G_IN_USE,
217 p_token1 => G_PARENT_TABLE_TOKEN,
218 p_token1_value => l_token_1,
219 p_token2 => G_CHILD_TABLE_TOKEN,
220 p_token2_value => l_token_2);
221 x_valid := FALSE;
222 x_return_status := Okl_Api.G_RET_STS_ERROR;
223 RAISE G_EXCEPTION_HALT_PROCESSING;
224 END IF;
225
226
227
228 IF p_pmvv_rec.id = Okl_Api.G_MISS_NUM THEN
229 OPEN c1(p_pmvv_rec.ptl_id,
230 p_pmvv_rec.ptq_id);
231 FETCH c1 INTO l_pmv_status;
232 l_row_found := c1%FOUND;
233 CLOSE c1;
234 IF l_row_found THEN
235 --Okl_Api.set_message(Okl_Pmv_Pvt.G_APP_NAME,Okl_Pmv_Pvt.G_UNQS,Okl_Pmv_Pvt.G_TABLE_TOKEN, l_token_1); ---CHG001
236 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
237 p_msg_name => 'OKL_COLUMN_NOT_UNIQUE',
238 p_token1 => G_TABLE_TOKEN,
239 p_token1_value => l_token_1,
240 p_token2 => G_COLUMN_TOKEN,
241 p_token2_value => l_token_6);
242 x_valid := FALSE;
243 x_return_status := Okl_Api.G_RET_STS_ERROR;
244 RAISE G_EXCEPTION_HALT_PROCESSING;
245 END IF;
246 END IF;
247
248 -- Check if the product template to which the template qualities are added is not
249 -- in the past
250 /*OPEN okl_pmv_ptl_fk_csr (p_pmvv_rec.ptl_id,
251 l_sysdate);
252 FETCH okl_pmv_ptl_fk_csr INTO l_check;
253 l_row_not_found := okl_pmv_ptl_fk_csr%NOTFOUND;
254 CLOSE okl_pmv_ptl_fk_csr;
255
256 IF l_row_not_found = FALSE THEN
257 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
258 p_msg_name => G_PAST_RECORDS);
259 x_valid := FALSE;
260 x_return_status := Okl_Api.G_RET_STS_ERROR;
261 RAISE G_EXCEPTION_HALT_PROCESSING;
262 END IF;
263 */
264
265 -- Check for constraints dates
266 IF p_pmvv_rec.id = Okl_Api.G_MISS_NUM THEN
267 OPEN okl_pmv_constraints_csr (p_pmvv_rec.ptv_id,
268 p_pmvv_rec.from_date,
269 p_pmvv_rec.TO_DATE);
270 FETCH okl_pmv_constraints_csr INTO l_check;
271 l_row_not_found := okl_pmv_constraints_csr%NOTFOUND;
272 CLOSE okl_pmv_constraints_csr;
273
274 IF l_row_not_found = FALSE THEN
275 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
276 p_msg_name => G_DATES_MISMATCH,
277 p_token1 => G_PARENT_TABLE_TOKEN,
278 p_token1_value => l_token_5,
279 p_token2 => G_CHILD_TABLE_TOKEN,
280 p_token2_value => l_token_4);
281 x_valid := FALSE;
282 x_return_status := Okl_Api.G_RET_STS_ERROR;
283 RAISE G_EXCEPTION_HALT_PROCESSING;
284 END IF;
285 END IF;
286
287 EXCEPTION
288 WHEN G_EXCEPTION_HALT_PROCESSING THEN
289 -- no processing necessary; validation can continue
290 -- with the next column
291 NULL;
292 WHEN OTHERS THEN
293 -- store SQL error message on message stack
294 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
295 p_msg_name => G_UNEXPECTED_ERROR,
296 p_token1 => G_SQLCODE_TOKEN,
297 p_token1_value => SQLCODE,
298 p_token2 => G_SQLERRM_TOKEN,
299 p_token2_value => SQLERRM);
300 x_valid := FALSE;
301 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
302
303 IF (okl_pmvv_chk_csr%ISOPEN) THEN
304 CLOSE okl_pmvv_chk_csr;
305 END IF;
306
307 IF (okl_pmv_ptl_fk_csr%ISOPEN) THEN
308 CLOSE okl_pmv_ptl_fk_csr;
309 END IF;
310
311 IF (okl_pmv_constraints_csr%ISOPEN) THEN
312 CLOSE okl_pmv_constraints_csr;
313 END IF;
314
315 IF (c1%ISOPEN) THEN
316 CLOSE c1;
317 END IF;
318
319 END Check_Constraints;
320
321 ---------------------------------------------------------------------------
322 -- PROCEDURE Validate_Ptq_Id
323 ---------------------------------------------------------------------------
324 -- Start of comments
325 --
326 -- Procedure Name : Validate_Ptq_Id
327 -- Description :
328 -- Business Rules :
329 -- Parameters :
330 -- Version : 1.0
331 -- End of comments
332 ---------------------------------------------------------------------------
333 PROCEDURE Validate_Ptq_Id(p_pmvv_rec IN pmvv_rec_type
334 ,x_return_status OUT NOCOPY VARCHAR2)
335 IS
336
337 CURSOR okl_ptqv_pk_csr (p_id IN NUMBER) IS
338 SELECT '1'
339 FROM okl_ptl_qualitys_v
340 WHERE okl_ptl_qualitys_v.id = p_id;
341
342 l_ptq_status VARCHAR2(1);
343 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
344 l_row_notfound BOOLEAN := TRUE;
345 l_token_1 VARCHAR2(1999);
346
347 BEGIN
348 -- initialize return status
349 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
350
351 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_TMPL_QLTY_CREATE',
352 p_attribute_code => 'OKL_TEMPLATE_QUALITY');
353
354 -- check for data before processing
355 IF (p_pmvv_rec.ptq_id IS NULL) OR
356 (p_pmvv_rec.ptq_id = Okl_Api.G_MISS_NUM) THEN
357 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pmv_Pvt.g_app_name
358 ,p_msg_name => Okl_Pmv_Pvt.g_required_value
359 ,p_token1 => Okl_Pmv_Pvt.g_col_name_token
360 ,p_token1_value => l_token_1);
361 x_return_status := Okl_Api.G_RET_STS_ERROR;
362 RAISE G_EXCEPTION_HALT_PROCESSING;
363 END IF;
364
365 IF (p_pmvv_rec.ptq_ID IS NOT NULL)
366 THEN
367 OPEN okl_ptqv_pk_csr(p_pmvv_rec.PTQ_ID);
368 FETCH okl_ptqv_pk_csr INTO l_ptq_status;
369 l_row_notfound := okl_ptqv_pk_csr%NOTFOUND;
370 CLOSE okl_ptqv_pk_csr;
371 IF (l_row_notfound) THEN
372 Okl_Api.set_message(Okl_Pmv_Pvt.G_APP_NAME, Okl_Pmv_Pvt.G_INVALID_VALUE,Okl_Pmv_Pvt.G_COL_NAME_TOKEN,l_token_1);
373 RAISE G_ITEM_NOT_FOUND_ERROR;
374 END IF;
375 END IF;
376
377
378 EXCEPTION
379 WHEN G_EXCEPTION_HALT_PROCESSING THEN
380 -- no processing necessary; validation can continue
381 -- with the next column
382 NULL;
383 WHEN G_ITEM_NOT_FOUND_ERROR THEN
384 x_return_status := Okl_Api.G_RET_STS_ERROR;
385
386 WHEN OTHERS THEN
387 -- store SQL error message on message stack for caller
388 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pmv_Pvt.g_app_name,
389 p_msg_name => Okl_Pmv_Pvt.g_unexpected_error,
390 p_token1 => Okl_Pmv_Pvt.g_sqlcode_token,
391 p_token1_value => SQLCODE,
392 p_token2 => Okl_Pmv_Pvt.g_sqlerrm_token,
393 p_token2_value => SQLERRM);
394
395 -- notify caller of an UNEXPECTED error
396 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
397
398 END Validate_Ptq_Id;
399
400 ---------------------------------------------------------------------------
401 -- FUNCTION Validate_Attributes
402 ---------------------------------------------------------------------------
403 -- Start of comments
404 --
405 -- Function Name : Validate_Attributes
406 -- Description :
407 -- Business Rules :
408 -- Parameters :
409 -- Version : 1.0
410 -- End of comments
411 ---------------------------------------------------------------------------
412
413 FUNCTION Validate_Attributes (
414 p_pmvv_rec IN pmvv_rec_type
415 ) RETURN VARCHAR2 IS
416 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
417 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
418 BEGIN
419 -- Validate_Ptq_Id
420 Validate_Ptq_Id(p_pmvv_rec, x_return_status);
421 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
422 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
423 -- need to leave
424 l_return_status := x_return_status;
425 RAISE G_EXCEPTION_HALT_PROCESSING;
426 ELSE
427 -- record that there was an error
428 l_return_status := x_return_status;
429 END IF;
430 END IF;
431
432 RETURN(l_return_status);
433 EXCEPTION
434 WHEN G_EXCEPTION_HALT_PROCESSING THEN
435 -- just come out with return status
436 NULL;
437 RETURN (l_return_status);
438
439 WHEN OTHERS THEN
440 -- store SQL error message on message stack for caller
441 Okl_Api.SET_MESSAGE(p_app_name => Okl_Pmv_Pvt.g_app_name,
442 p_msg_name => Okl_Pmv_Pvt.g_unexpected_error,
443 p_token1 => Okl_Pmv_Pvt.g_sqlcode_token,
444 p_token1_value => SQLCODE,
445 p_token2 => Okl_Pmv_Pvt.g_sqlerrm_token,
446 p_token2_value => SQLERRM);
447 -- notify caller of an UNEXPECTED error
448 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
449 RETURN(l_return_status);
450
451 END Validate_Attributes;
452
453 ---------------------------------------------------------------------------
454 -- PROCEDURE insert_pmvalues for: OKL_PTL_PTQ_VALS_V
455 ---------------------------------------------------------------------------
456
457 PROCEDURE insert_pmvalues(p_api_version IN NUMBER,
458 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 p_ptlv_rec IN ptlv_rec_type,
463 p_pmvv_rec IN pmvv_rec_type,
464 x_pmvv_rec OUT NOCOPY pmvv_rec_type
465 ) IS
466 l_api_version CONSTANT NUMBER := 1;
467 l_api_name CONSTANT VARCHAR2(30) := 'insert_pmvalues';
468 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
469 l_valid BOOLEAN;
470 l_pmvv_rec pmvv_rec_type;
471 l_ptlv_rec ptlv_rec_type;
472 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
473 l_row_notfound BOOLEAN := TRUE;
474 BEGIN
475 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
476
477 l_pmvv_rec := p_pmvv_rec;
478
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
487 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
488 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
489 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
490 RAISE Okl_Api.G_EXCEPTION_ERROR;
491 END IF;
492
493 l_return_status := Validate_Attributes(l_pmvv_rec);
494 --- If any errors happen abort API
495 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
496 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
497 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
498 RAISE Okl_Api.G_EXCEPTION_ERROR;
499 END IF;
500
501 get_parent_dates(p_pmvv_rec => l_pmvv_rec,
502 x_no_data_found => l_row_notfound,
503 x_return_status => l_return_status,
504 x_ptlv_rec => l_ptlv_rec);
505
506 IF (l_row_notfound) THEN
507 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
508 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
509 RAISE Okl_Api.G_EXCEPTION_ERROR;
510 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
511 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
512 END IF;
513
514 l_pmvv_rec.from_date := l_ptlv_rec.from_date;
515 l_pmvv_rec.TO_DATE := l_ptlv_rec.TO_DATE;
516
517 /* call check_constraints to check the validity of this relationship */
518
519 Check_Constraints(p_api_version => p_api_version,
520 p_init_msg_list => p_init_msg_list,
521 p_pmvv_rec => l_pmvv_rec,
522 x_return_status => l_return_status,
523 x_msg_count => x_msg_count,
524 x_msg_data => x_msg_data,
525 x_valid => l_valid);
526
527 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
528 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
529 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
530 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
531 l_valid <> TRUE) THEN
532 x_return_status := Okl_Api.G_RET_STS_ERROR;
533 RAISE Okl_Api.G_EXCEPTION_ERROR;
534 END IF;
535
536 /* public api to insert pmvalues */
537
538 Okl_Ptq_Values_Pub.insert_ptq_values(p_api_version => p_api_version,
539 p_init_msg_list => p_init_msg_list,
540 x_return_status => l_return_status,
541 x_msg_count => x_msg_count,
542 x_msg_data => x_msg_data,
543 p_pmvv_rec => l_pmvv_rec,
544 x_pmvv_rec => x_pmvv_rec);
545
546 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
547 RAISE Okl_Api.G_EXCEPTION_ERROR;
548 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
549 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
550 END IF;
551
552 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data);
554 EXCEPTION
555 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
556 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
557 p_pkg_name => G_PKG_NAME,
558 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
559 x_msg_count => x_msg_count,
560 x_msg_data => x_msg_data,
561 p_api_type => '_PVT');
562 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
563 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
564 p_pkg_name => G_PKG_NAME,
565 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
566 x_msg_count => x_msg_count,
567 x_msg_data => x_msg_data,
568 p_api_type => '_PVT');
569 WHEN OTHERS THEN
570 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
571 p_pkg_name => G_PKG_NAME,
572 p_exc_name => 'OTHERS',
573 x_msg_count => x_msg_count,
574 x_msg_data => x_msg_data,
575 p_api_type => '_PVT');
576
577 END insert_pmvalues;
578
579 PROCEDURE insert_pmvalues(p_api_version IN NUMBER,
580 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2,
584 p_ptlv_rec IN ptlv_rec_type,
585 p_pmvv_tbl IN pmvv_tbl_type,
586 x_pmvv_tbl OUT NOCOPY pmvv_tbl_type
587 ) IS
588 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
589 i NUMBER := 0;
590
591 BEGIN
592
593 -- Make sure PL/SQL table has records in it before passing
594 IF (p_pmvv_tbl.COUNT > 0) THEN
595 i := p_pmvv_tbl.FIRST;
596 LOOP
597 insert_pmvalues(
598 p_api_version => p_api_version,
599 p_init_msg_list => OKL_API.G_FALSE,
600 x_return_status => x_return_status,
601 x_msg_count => x_msg_count,
602 x_msg_data => x_msg_data,
603 p_ptlv_rec => p_ptlv_rec,
604 p_pmvv_rec => p_pmvv_tbl(i),
605 x_pmvv_rec => x_pmvv_tbl(i));
606
607 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
608 IF (l_overall_status <> Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
609 l_overall_status := x_return_status;
610 END IF;
611 END IF;
612
613 EXIT WHEN (i = p_pmvv_tbl.LAST);
614 i := p_pmvv_tbl.NEXT(i);
615 END LOOP;
616 END IF;
617
618 x_return_status := l_overall_status;
619 END insert_pmvalues;
620
621 ---------------------------------------------------------------------------
622 -- PROCEDURE delete_pmvalues for: okl_ptl_ptq_vals_v
623 -- This allows the user to delete table of records
624 ---------------------------------------------------------------------------
625 PROCEDURE delete_pmvalues(
626 p_api_version IN NUMBER
627 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
628 ,x_return_status OUT NOCOPY VARCHAR2
629 ,x_msg_count OUT NOCOPY NUMBER
630 ,x_msg_data OUT NOCOPY VARCHAR2
631 ,p_ptlv_rec IN ptlv_rec_type
632 ,p_pmvv_tbl IN pmvv_tbl_type
633 ) IS
634 l_api_version CONSTANT NUMBER := 1;
635 l_pmvv_tbl pmvv_tbl_type;
636 l_api_name CONSTANT VARCHAR2(30) := 'delete_pmvalues';
637 l_pmvv_rec pmvv_rec_type;
638 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
639 l_overall_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
640 i NUMBER;
641 l_valid BOOLEAN;
642
643
644 BEGIN
645
646 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
647
648 l_pmvv_tbl := p_pmvv_tbl;
649
650 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
651 p_pkg_name => G_PKG_NAME,
652 p_init_msg_list => p_init_msg_list,
653 l_api_version => l_api_version,
654 p_api_version => p_api_version,
655 p_api_type => '_PVT',
656 x_return_status => l_return_status);
657
658 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
659 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
660 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
661 RAISE Okl_Api.G_EXCEPTION_ERROR;
662 END IF;
663
664 /* check if the product asked to delete is used by contracts if yes halt the process*/
665
666
667 IF (l_pmvv_tbl.COUNT > 0) THEN
668 i := l_pmvv_tbl.FIRST;
669 LOOP
670
671 /* check if the product template value asked to delete is used by contracts if yes halt the process*/
672 Check_Constraints(p_api_version => p_api_version,
673 p_init_msg_list => p_init_msg_list,
674 p_pmvv_rec => l_pmvv_tbl(i),
675 x_return_status => l_return_status,
676 x_msg_count => x_msg_count,
677 x_msg_data => x_msg_data,
678 x_valid => l_valid);
679
680 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
681 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
682 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
683 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
684 l_valid <> TRUE) THEN
685 x_return_status := Okl_Api.G_RET_STS_ERROR;
686 RAISE Okl_Api.G_EXCEPTION_ERROR;
687 END IF;
688
689 EXIT WHEN (i = l_pmvv_tbl.LAST);
690
691 i := l_pmvv_tbl.NEXT(i);
692
693 END LOOP;
694 END IF;
695
696 /* delete pmvalues */
697 Okl_Ptq_Values_Pub.delete_ptq_values(p_api_version => p_api_version,
698 p_init_msg_list => p_init_msg_list,
699 x_return_status => l_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_pmvv_tbl => l_pmvv_tbl);
703
704 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
705 RAISE Okl_Api.G_EXCEPTION_ERROR;
706 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
707 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
708 END IF;
709
710 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
711 x_msg_data => x_msg_data);
712 EXCEPTION
713 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
714 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
715 p_pkg_name => G_PKG_NAME,
716 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data,
719 p_api_type => '_PVT');
720 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
721 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
722 p_pkg_name => G_PKG_NAME,
723 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_api_type => '_PVT');
727 WHEN OTHERS THEN
728 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
729 p_pkg_name => G_PKG_NAME,
730 p_exc_name => 'OTHERS',
731 x_msg_count => x_msg_count,
732 x_msg_data => x_msg_data,
733 p_api_type => '_PVT');
734
735 END delete_pmvalues;
736
737 END Okl_Setuppmvalues_Pvt;