[Home] [Help]
PACKAGE BODY: APPS.OKL_SETUPTQYVALUES_PVT
Source
1 PACKAGE BODY Okl_Setuptqyvalues_Pvt AS
2 /* $Header: OKLRSEVB.pls 115.17 2003/07/23 18:32:17 sgorantl noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE get_rec for: OKL_PTQ_VALUES_V
5 ---------------------------------------------------------------------------
6 PROCEDURE get_rec (
7 p_ptvv_rec IN ptvv_rec_type,
8 x_no_data_found OUT NOCOPY BOOLEAN,
9 x_return_status OUT NOCOPY VARCHAR2,
10 x_ptvv_rec OUT NOCOPY ptvv_rec_type
11 ) IS
12 CURSOR okl_ptvv_pk_csr (p_id IN NUMBER) IS
13 SELECT
14 ID,
15 OBJECT_VERSION_NUMBER,
16 PTQ_ID,
17 VALUE,
18 DESCRIPTION,
19 FROM_DATE,
20 TO_DATE,
21 CREATED_BY,
22 CREATION_DATE,
23 LAST_UPDATED_BY,
24 LAST_UPDATE_DATE,
25 LAST_UPDATE_LOGIN
26 FROM Okl_Ptq_Values_V
27 WHERE okl_ptq_values_v.id = p_id;
28 l_okl_ptvv_pk okl_ptvv_pk_csr%ROWTYPE;
29 l_ptvv_rec ptvv_rec_type;
30 BEGIN
31 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
32 x_no_data_found := TRUE;
33 -- Get current database values
34 OPEN okl_ptvv_pk_csr (p_ptvv_rec.id);
35 FETCH okl_ptvv_pk_csr INTO
36 l_ptvv_rec.ID,
37 l_ptvv_rec.OBJECT_VERSION_NUMBER,
38 l_ptvv_rec.PTQ_ID,
39 l_ptvv_rec.VALUE,
40 l_ptvv_rec.DESCRIPTION,
41 l_ptvv_rec.FROM_DATE,
42 l_ptvv_rec.TO_DATE,
43 l_ptvv_rec.CREATED_BY,
44 l_ptvv_rec.CREATION_DATE,
45 l_ptvv_rec.LAST_UPDATED_BY,
46 l_ptvv_rec.LAST_UPDATE_DATE,
47 l_ptvv_rec.LAST_UPDATE_LOGIN;
48 x_no_data_found := okl_ptvv_pk_csr%NOTFOUND;
49 CLOSE okl_ptvv_pk_csr;
50 x_ptvv_rec := l_ptvv_rec;
51 EXCEPTION
52 WHEN OTHERS THEN
53 -- store SQL error message on message stack
54 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
55 p_msg_name => G_UNEXPECTED_ERROR,
56 p_token1 => G_SQLCODE_TOKEN,
57 p_token1_value => SQLCODE,
58 p_token2 => G_SQLERRM_TOKEN,
59 p_token2_value => SQLERRM);
60 -- notify UNEXPECTED error for calling API.
61 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
62
63 IF (okl_ptvv_pk_csr%ISOPEN) THEN
64 CLOSE okl_ptvv_pk_csr;
65 END IF;
66
67 END get_rec;
68
69
70 ---------------------------------------------------------------------------
71 -- PROCEDURE default_parent_dates for: OKL_PTQ_VALUES_V
72 ---------------------------------------------------------------------------
73
74 PROCEDURE default_parent_dates(
75 p_ptvv_rec IN ptvv_rec_type,
76 x_no_data_found OUT NOCOPY BOOLEAN,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_ptqv_rec OUT NOCOPY ptqv_rec_type
79 ) IS
80 CURSOR okl_ptqv_pk_csr (p_ptq_id IN NUMBER) IS
81 SELECT FROM_DATE,
82 TO_DATE
83 FROM Okl_ptl_qualitys_V ptq
84 WHERE ptq.id = p_ptq_id;
85 l_okl_ptqv_pk okl_ptqv_pk_csr%ROWTYPE;
86 l_ptqv_rec ptqv_rec_type;
87 BEGIN
88 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
89 x_no_data_found := TRUE;
90 -- Get current database values
91 OPEN okl_ptqv_pk_csr (p_ptvv_rec.ptq_id);
92 FETCH okl_ptqv_pk_csr INTO
93 l_ptqv_rec.FROM_DATE,
94 l_ptqv_rec.TO_DATE;
95 x_no_data_found := okl_ptqv_pk_csr%NOTFOUND;
96 CLOSE okl_ptqv_pk_csr;
97 x_ptqv_rec := l_ptqv_rec;
98 EXCEPTION
99 WHEN OTHERS THEN
100 -- store SQL error message on message stack
101 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
102 p_msg_name => G_UNEXPECTED_ERROR,
103 p_token1 => G_SQLCODE_TOKEN,
104 p_token1_value => SQLCODE,
105 p_token2 => G_SQLERRM_TOKEN,
106 p_token2_value => SQLERRM);
107 -- notify UNEXPECTED error for calling API.
108 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
109
110 IF (okl_ptqv_pk_csr%ISOPEN) THEN
111 CLOSE okl_ptqv_pk_csr;
112 END IF;
113
114 END default_parent_dates;
115
116 ---------------------------------------------------------------------------
117 -- PROCEDURE validate_Pkeys for: OKL_PTQ_VALUES_V
118 -- To verify whether the dates are valid for PRODUCT TEMPLATE QUALITY
119 ---------------------------------------------------------------------------
120
121 PROCEDURE Check_Constraints (
122 p_ptvv_rec IN OUT NOCOPY ptvv_rec_type,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_valid OUT NOCOPY BOOLEAN
125 ) IS
126
127 CURSOR okl_chk_ptq_csr(p_ptq_id NUMBER,
128 p_from_date DATE,
129 p_to_date DATE
130 ) IS
131 SELECT '1'
132 FROM okl_ptl_qualitys_v ptqv
133 WHERE ptqv.ID = p_ptq_id
134 AND ((ptqv.FROM_DATE > p_from_date OR
135 p_from_date > NVL(ptqv.TO_DATE,p_from_date) OR
136 NVL(ptqv.TO_DATE, p_to_date) < p_to_date));
137
138 CURSOR okl_chk_pmv_csr(p_ptv_id NUMBER,
139 p_from_date DATE,
140 p_to_date DATE
141 ) IS
142 SELECT '1'
143 FROM OKL_ptl_ptq_vals_v pmvv
144 WHERE pmvv.ptv_id = p_ptv_id
145 AND (pmvv.FROM_DATE < p_from_date OR
146 NVL(pmvv.TO_DATE, pmvv.FROM_DATE) > p_to_date);
147
148 CURSOR c1(p_value okl_ptq_values_v.value%TYPE,
149 p_ptq_id okl_ptq_values_v.ptq_id%TYPE
150 ) IS
151 SELECT '1'
152 FROM okl_ptq_values_v
153 WHERE value = p_value
154 AND ptq_id = p_ptq_id;
155 -- AND id <> NVL(p_ptvv_rec.id,-9999);
156
157 l_token_1 VARCHAR2(1999);
158 l_token_2 VARCHAR2(1999);
159 l_token_3 VARCHAR2(1999);
160 l_check VARCHAR2(1) := '?';
161 l_row_not_found BOOLEAN := FALSE;
162 l_unq_tbl Okc_Util.unq_tbl_type;
163 l_ptv_status VARCHAR2(1);
164 l_row_found BOOLEAN := FALSE;
165
166 BEGIN
167 x_valid := TRUE;
168 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
169
170 l_token_1 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_TMPVALS_CRUPD',
171 p_attribute_code => 'OKL_TEMPLATE_QUALITY_VALUES');
172
173 l_token_2 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_TMPQLTY_CRUPD',
174 p_attribute_code => 'OKL_TEMPLATE_QUALITIES');
175
176 l_token_3 := Okl_Accounting_Util.Get_Message_Token(p_region_code => 'OKL_LP_PDT_TMPL_QLTY_SUMRY',
177 p_attribute_code => 'OKL_PDT_QLTY_SUMRY_TITLE');
178
179
180 IF p_ptvv_rec.id = Okl_Api.G_MISS_NUM THEN
181 p_ptvv_rec.value := Okl_Accounting_Util.okl_upper(p_ptvv_rec.value);
182 OPEN c1(p_ptvv_rec.value,
183 p_ptvv_rec.ptq_id);
184 FETCH c1 INTO l_ptv_status;
185 l_row_found := c1%FOUND;
186 CLOSE c1;
187 IF l_row_found THEN
188 Okl_Api.set_message(G_APP_NAME,Okl_Ptv_Pvt.G_UNQS,Okl_Ptv_Pvt.G_TABLE_TOKEN, l_token_1); ---CHG001
189 x_valid := FALSE;
190 x_return_status := Okl_Api.G_RET_STS_ERROR;
191 RAISE G_EXCEPTION_HALT_PROCESSING;
192 END IF;
193 END IF;
194
195 IF p_ptvv_rec.id <> Okl_Api.G_MISS_NUM THEN
196 -- Check for Child dates
197 OPEN okl_chk_pmv_csr(p_ptvv_rec.id,
198 p_ptvv_rec.from_date,
199 p_ptvv_rec.TO_DATE);
200
201 FETCH okl_chk_pmv_csr INTO l_check;
202 l_row_not_found := okl_chk_pmv_csr%NOTFOUND;
203 CLOSE okl_chk_pmv_csr;
204
205 IF l_row_not_found = FALSE THEN
206 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
207 p_msg_name => G_DATES_MISMATCH,
208 p_token1 => G_PARENT_TABLE_TOKEN,
209 p_token1_value => l_token_1,
210 p_token2 => G_CHILD_TABLE_TOKEN,
211 p_token2_value => l_token_3);
212 x_valid := FALSE;
213 x_return_status := Okl_Api.G_RET_STS_ERROR;
214 RAISE G_EXCEPTION_HALT_PROCESSING;
215 END IF;
216 END IF;
217
218 -- Check for Parent dates
219 OPEN okl_chk_ptq_csr(p_ptvv_rec.ptq_id,
220 p_ptvv_rec.from_date,
221 p_ptvv_rec.TO_DATE);
222
223 FETCH okl_chk_ptq_csr INTO l_check;
224 l_row_not_found := okl_chk_ptq_csr%NOTFOUND;
225 CLOSE okl_chk_ptq_csr;
226
227 IF l_row_not_found = FALSE THEN
228 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
229 p_msg_name => G_DATES_MISMATCH,
230 p_token1 => G_PARENT_TABLE_TOKEN,
231 p_token1_value => l_token_2,
232 p_token2 => G_CHILD_TABLE_TOKEN,
233 p_token2_value => l_token_1);
234 x_valid := FALSE;
235 x_return_status := Okl_Api.G_RET_STS_ERROR;
236 RAISE G_EXCEPTION_HALT_PROCESSING;
237 END IF;
238
239 EXCEPTION
240 WHEN G_EXCEPTION_HALT_PROCESSING THEN
241 -- no processing necessary; validation can continue
242 -- with the next column
243 NULL;
244 WHEN OTHERS THEN
245 -- store SQL error message on message stack
246 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
247 p_msg_name => G_UNEXPECTED_ERROR,
248 p_token1 => G_SQLCODE_TOKEN,
249 p_token1_value => SQLCODE,
250 p_token2 => G_SQLERRM_TOKEN,
251 p_token2_value => SQLERRM);
252 x_valid := FALSE;
253 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
254
255 IF (okl_chk_pmv_csr%ISOPEN) THEN
256 CLOSE okl_chk_pmv_csr;
257 END IF;
258
259 IF (okl_chk_ptq_csr%ISOPEN) THEN
260 CLOSE okl_chk_ptq_csr;
261 END IF;
262
263 IF (c1%ISOPEN) THEN
264 CLOSE c1;
265 END IF;
266
267 END Check_Constraints;
268
269 PROCEDURE Validate_From_Date(p_ptvv_rec IN ptvv_rec_type
270 ,x_return_status OUT NOCOPY VARCHAR2)
271 IS
272
273 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
274 l_token_1 VARCHAR2(1999);
275
276 BEGIN
277 -- initialize return status
278 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
279
280 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_TMPVALS_CRUPD','OKL_EFFECTIVE_FROM');
281
282 -- check for data before processing
283 IF (p_ptvv_rec.from_date IS NULL) OR
284 (p_ptvv_rec.from_date = Okl_Api.G_MISS_DATE) THEN
285 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ptv_Pvt.g_app_name
286 ,p_msg_name => Okl_Ptv_Pvt.g_required_value
287 ,p_token1 => Okl_Ptv_Pvt.g_col_name_token
288 ,p_token1_value => l_token_1);
289 x_return_status := Okl_Api.G_RET_STS_ERROR;
290 RAISE G_EXCEPTION_HALT_PROCESSING;
291 END IF;
292
293 EXCEPTION
294 WHEN G_EXCEPTION_HALT_PROCESSING THEN
295 -- no processing necessary; validation can continue
296 -- with the next column
297 NULL;
298
299 WHEN OTHERS THEN
300 -- store SQL error message on message stack for caller
301 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ptv_Pvt.g_app_name,
302 p_msg_name => Okl_Ptv_Pvt.g_unexpected_error,
303 p_token1 => Okl_Ptv_Pvt.g_sqlcode_token,
304 p_token1_value => SQLCODE,
305 p_token2 => Okl_Ptv_Pvt.g_sqlerrm_token,
306 p_token2_value => SQLERRM);
307
308 -- notify caller of an UNEXPECTED error
309 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
310
311 END Validate_From_Date;
312
313 ---------------------------------------------------------------------------
314 -- PROCEDURE Validate_Value
315 ---------------------------------------------------------------------------
316 -- Start of comments
317 --
318 -- Procedure Name : Validate_Value
319 -- Description :
320 -- Business Rules :
321 -- Parameters :
322 -- Version : 1.0
323 -- End of comments
324 ---------------------------------------------------------------------------
325 PROCEDURE Validate_Value(p_ptvv_rec IN OUT NOCOPY ptvv_rec_type
326 ,x_return_status OUT NOCOPY VARCHAR2)
327 IS
328
329 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
330 l_token_1 VARCHAR2(1999);
331 BEGIN
332 -- initialize return status
333 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
334
335 l_token_1 := Okl_Accounting_Util.Get_Message_Token('OKL_LP_TMPVALS_CRUPD','OKL_NAME');
336
337 -- check for data before processing
338 IF (p_ptvv_rec.value IS NULL) OR
339 (p_ptvv_rec.value = Okl_Api.G_MISS_CHAR) THEN
340 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ptv_Pvt.g_app_name
341 ,p_msg_name => Okl_Ptv_Pvt.g_required_value
342 ,p_token1 => Okl_Ptv_Pvt.g_col_name_token
343 ,p_token1_value => l_token_1);
344 x_return_status := Okl_Api.G_RET_STS_ERROR;
345 RAISE G_EXCEPTION_HALT_PROCESSING;
346 END IF;
347
348 p_ptvv_rec.value := Okl_Accounting_Util.okl_upper(p_ptvv_rec.value);
349
350 EXCEPTION
351 WHEN G_EXCEPTION_HALT_PROCESSING THEN
352 -- no processing necessary; validation can continue
353 -- with the next column
354 NULL;
355
356 WHEN OTHERS THEN
357 -- store SQL error message on message stack for caller
358 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ptv_Pvt.g_app_name,
359 p_msg_name => Okl_Ptv_Pvt.g_unexpected_error,
360 p_token1 => Okl_Ptv_Pvt.g_sqlcode_token,
361 p_token1_value => SQLCODE,
362 p_token2 => Okl_Ptv_Pvt.g_sqlerrm_token,
363 p_token2_value => SQLERRM);
364
365 -- notify caller of an UNEXPECTED error
366 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
367
368 END Validate_Value;
369
370 FUNCTION Validate_Attributes (
371 p_ptvv_rec IN OUT NOCOPY ptvv_rec_type
372 ) RETURN VARCHAR2 IS
373 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
374 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
375 BEGIN
376
377 -- Validate_Value
378 Validate_Value(p_ptvv_rec,x_return_status);
379 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
380 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
381 -- need to leave
382 l_return_status := x_return_status;
383 RAISE G_EXCEPTION_HALT_PROCESSING;
384 ELSE
385 -- record that there was an error
386 l_return_status := x_return_status;
387 END IF;
388 END IF;
389
390 -- Validate_From_Date
391 Validate_From_Date(p_ptvv_rec,x_return_status);
392 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
393 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
394 -- need to leave
395 l_return_status := x_return_status;
396 RAISE G_EXCEPTION_HALT_PROCESSING;
397 ELSE
398 -- record that there was an error
399 l_return_status := x_return_status;
400 END IF;
401 END IF;
402
403 RETURN(l_return_status);
404 EXCEPTION
405 WHEN G_EXCEPTION_HALT_PROCESSING THEN
406 -- just come out with return status
407 NULL;
408 RETURN (l_return_status);
409
410 WHEN OTHERS THEN
411 -- store SQL error message on message stack for caller
412 Okl_Api.SET_MESSAGE(p_app_name => Okl_Ptv_Pvt.g_app_name,
413 p_msg_name => Okl_Ptv_Pvt.g_unexpected_error,
414 p_token1 => Okl_Ptv_Pvt.g_sqlcode_token,
415 p_token1_value => SQLCODE,
416 p_token2 => Okl_Ptv_Pvt.g_sqlerrm_token,
417 p_token2_value => SQLERRM);
418 -- notify caller of an UNEXPECTED error
419 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
420 RETURN(l_return_status);
421
422 END Validate_Attributes;
423
424 ---------------------------------------------------------------------------
425 -- FUNCTION defaults_to_actuals
426 -- This function creates an output record with changed information from the
427 -- input structure and unchanged details from the database
428 ---------------------------------------------------------------------------
429
430 FUNCTION defaults_to_actuals (
431 p_upd_ptvv_rec IN ptvv_rec_type,
432 p_db_ptvv_rec IN ptvv_rec_type
433 ) RETURN ptvv_rec_type IS
434 l_ptvv_rec ptvv_rec_type;
435 BEGIN
436
437 /* create a temporary record with all relevant details from db and upd records */
438 l_ptvv_rec := p_db_ptvv_rec;
439
440 IF p_upd_ptvv_rec.description <> Okl_Api.G_MISS_CHAR THEN
441 l_ptvv_rec.description := p_upd_ptvv_rec.description;
442 END IF;
443
444 IF p_upd_ptvv_rec.value <> Okl_Api.G_MISS_CHAR THEN
445 l_ptvv_rec.value := p_upd_ptvv_rec.value;
446 END IF;
447
448 IF p_upd_ptvv_rec.from_date <> Okl_Api.G_MISS_DATE THEN
449 l_ptvv_rec.from_date := p_upd_ptvv_rec.from_date;
450 END IF;
451
452 IF p_upd_ptvv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
453 l_ptvv_rec.TO_DATE := p_upd_ptvv_rec.TO_DATE;
454 END IF;
455
456 RETURN l_ptvv_rec;
457 END defaults_to_actuals;
458
459 ---------------------------------------------------------------------------
460 -- PROCEDURE reorganize_inputs
461 -- This procedure is to reset the attributes in the input structure based
462 -- on the data from database
463 ---------------------------------------------------------------------------
464 PROCEDURE reorganize_inputs (
465 p_upd_ptvv_rec IN OUT NOCOPY ptvv_rec_type,
466 p_db_ptvv_rec IN ptvv_rec_type
467 ) IS
468 l_upd_ptvv_rec ptvv_rec_type;
469 l_db_ptvv_rec ptvv_rec_type;
470 BEGIN
471 /* create a temporary record with all relevant details from db and upd records */
472 l_upd_ptvv_rec := p_upd_ptvv_rec;
473 l_db_ptvv_rec := p_db_ptvv_rec;
474
475 IF l_upd_ptvv_rec.description = l_db_ptvv_rec.description THEN
476 l_upd_ptvv_rec.description := Okl_Api.G_MISS_CHAR;
477 END IF;
478
479 IF to_date(to_char(l_upd_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
480 l_upd_ptvv_rec.from_date := Okl_Api.G_MISS_DATE;
481 END IF;
482
483 IF to_date(to_char(l_upd_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(l_db_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') THEN
484 l_upd_ptvv_rec.TO_DATE := Okl_Api.G_MISS_DATE;
485 END IF;
486
487 IF l_upd_ptvv_rec.value = l_db_ptvv_rec.value THEN
488 l_upd_ptvv_rec.value := Okl_Api.G_MISS_CHAR;
489 END IF;
490
491 p_upd_ptvv_rec := l_upd_ptvv_rec;
492
493 END reorganize_inputs;
494
495 ---------------------------------------------------------------------------
496 -- PROCEDURE check_updates
497 -- To verify whether the requested changes from the screen are valid or not
498 ---------------------------------------------------------------------------
499 PROCEDURE check_updates (
500 p_upd_ptvv_rec IN ptvv_rec_type,
501 p_db_ptvv_rec IN ptvv_rec_type,
502 p_ptvv_rec IN ptvv_rec_type,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_data OUT NOCOPY VARCHAR2
505 ) IS
506 l_ptvv_rec ptvv_rec_type;
507 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
508 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
509 l_valid BOOLEAN;
510 BEGIN
511
512 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
513 l_ptvv_rec := p_ptvv_rec;
514
515
516 /* check for start date greater than sysdate */
517 /*IF to_date(to_char(p_upd_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
518 to_date(to_char(p_upd_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
519 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
520 p_msg_name => G_START_DATE);
521 x_return_status := Okl_Api.G_RET_STS_ERROR;
522 RAISE G_EXCEPTION_HALT_PROCESSING;
523 END IF;*/
524
525
526 /* check for the records with from and to dates less than sysdate */
527 /* IF to_date(to_char(p_upd_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
528 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
529 p_msg_name => G_PAST_RECORDS);
530 x_return_status := Okl_Api.G_RET_STS_ERROR;
531 RAISE G_EXCEPTION_HALT_PROCESSING;
532 END IF;
533 */
534 /* if the start date is in the past, the start date cannot be
535 modified */
536 /*IF to_date(to_char(p_upd_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
537 to_date(to_char(p_db_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') <= l_sysdate THEN
538 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
539 p_msg_name => G_NOT_ALLOWED,
540 p_token1 => G_COL_NAME_TOKEN,
541 p_token1_value => 'START_DATE');
542 x_return_status := Okl_Api.G_RET_STS_ERROR;
543 RAISE G_EXCEPTION_HALT_PROCESSING;
544 END IF;
545 */
546
547 IF l_ptvv_rec.from_date <> Okl_Api.G_MISS_DATE OR
548 l_ptvv_rec.TO_DATE <> Okl_Api.G_MISS_DATE THEN
549
550 Check_Constraints(p_ptvv_rec => l_ptvv_rec,
551 x_return_status => l_return_status,
552 x_valid => l_valid);
553
554 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
555 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
556 RAISE G_EXCEPTION_HALT_PROCESSING;
557 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
558 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
559 l_valid <> TRUE) THEN
560 x_return_status := Okl_Api.G_RET_STS_ERROR;
561 RAISE G_EXCEPTION_HALT_PROCESSING;
562 END IF;
563
564 END IF;
565
566 EXCEPTION
567 WHEN G_EXCEPTION_HALT_PROCESSING THEN
568 -- no processing necessary; validation can continue
569 -- with the next column
570 NULL;
571
572 WHEN OTHERS THEN
573 -- store SQL error message on message stack for caller
574 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
575 p_msg_name => G_UNEXPECTED_ERROR,
576 p_token1 => G_SQLCODE_TOKEN,
577 p_token1_value => SQLCODE,
578 p_token2 => G_SQLERRM_TOKEN,
579 p_token2_value => SQLERRM );
580 x_msg_data := 'Unexpected DATABASE Error';
581 -- notify caller of an UNEXPECTED error
582 x_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
583
584 END check_updates;
585
586 ---------------------------------------------------------------------------
587 -- PROCEDURE determine_action for: Okl_pdt_Qualitys_v
588 -- This function helps in determining the various checks to be performed
589 -- for the new/updated record.
590 ---------------------------------------------------------------------------
591 FUNCTION determine_action (
592 p_upd_ptvv_rec IN ptvv_rec_type,
593 p_db_ptvv_rec IN ptvv_rec_type,
594 p_date IN DATE
595 ) RETURN VARCHAR2 IS
596 l_action VARCHAR2(1);
597 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
598 BEGIN
599 /* Scenario 1: Only description and/or descriptive flexfield changes */
600 IF p_upd_ptvv_rec.from_date = Okl_Api.G_MISS_DATE AND
601 p_upd_ptvv_rec.TO_DATE = Okl_Api.G_MISS_DATE THEN
602 l_action := '1';
603 /* Scenario 2: Changing the dates */
604 ELSE
605 l_action := '2';
606 END IF;
607 RETURN(l_action);
608 END determine_action;
609
610 ---------------------------------------------------------------------------
611 -- PROCEDURE insert_PQYVALUES for: okl_pdt_pqy_vals
612 ---------------------------------------------------------------------------
613 PROCEDURE insert_tqyvalues(p_api_version IN NUMBER,
614 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
615 x_return_status OUT NOCOPY VARCHAR2,
616 x_msg_count OUT NOCOPY NUMBER,
617 x_msg_data OUT NOCOPY VARCHAR2,
618 p_ptqv_rec IN ptqv_rec_type,
619 p_ptvv_rec IN ptvv_rec_type,
620 x_ptvv_rec OUT NOCOPY ptvv_rec_type
621 ) IS
622 l_api_version CONSTANT NUMBER := 1;
623 l_api_name CONSTANT VARCHAR2(30) := 'insert_tqyvalues';
624 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
625 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
626 l_valid BOOLEAN;
627 l_ptvv_rec ptvv_rec_type;
628 l_ptqv_rec ptqv_rec_type;
629 l_row_notfound BOOLEAN := TRUE;
630 BEGIN
631 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
632
633 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
634 p_pkg_name => G_PKG_NAME,
635 p_init_msg_list => p_init_msg_list,
636 l_api_version => l_api_version,
637 p_api_version => p_api_version,
638 p_api_type => '_PVT',
639 x_return_status => l_return_status);
640
641 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
642 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
643 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
644 RAISE Okl_Api.G_EXCEPTION_ERROR;
645 END IF;
646
647 l_ptvv_rec := p_ptvv_rec;
648
649 /* check for the records with from and to dates less than sysdate */
650 /*IF to_date(to_char(l_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate OR
651 to_date(to_char(l_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
652 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
653 p_msg_name => G_PAST_RECORDS);
654 RAISE Okl_Api.G_EXCEPTION_ERROR;
655 END IF;*/
656
657 --- Validate all non-missing attributes (Item Level Validation)
658 l_return_status := Validate_Attributes(l_ptvv_rec);
659 --- If any errors happen abort API
660 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
661 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
662 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
663 RAISE Okl_Api.G_EXCEPTION_ERROR;
664 END IF;
665
666
667 default_parent_dates( p_ptvv_rec => l_ptvv_rec,
668 x_no_data_found => l_row_notfound,
669 x_return_status => l_return_status,
670 x_ptqv_rec => l_ptqv_rec);
671
672 IF (l_row_notfound) THEN
673 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
674 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
675 RAISE Okl_Api.G_EXCEPTION_ERROR;
676 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
677 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
678 END IF;
679
680 --Default Child End Date With Its Parents End Date If It Is Not Entered.
681 IF to_date(to_char(l_ptqv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
682 (to_date(to_char(l_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') OR
683 to_date(to_char(l_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') IS NULL) THEN
684 l_ptvv_rec.TO_DATE := l_ptqv_rec.TO_DATE;
685 END IF;
686
687 /* call check_constraints to check the validity of this relationship */
688 Check_Constraints(p_ptvv_rec => l_ptvv_rec,
689 x_return_status => l_return_status,
690 x_valid => l_valid);
691
692 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
693 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
694 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) OR
695 (l_return_status = Okl_Api.G_RET_STS_SUCCESS AND
696 l_valid <> TRUE) THEN
697 x_return_status := Okl_Api.G_RET_STS_ERROR;
698 RAISE Okl_Api.G_EXCEPTION_ERROR;
699 END IF;
700
701 /* public api to insert PQYVALUES */
702 Okl_Ptl_Qualitys_Pub.create_ptl_qlty_values(p_api_version => p_api_version,
703 p_init_msg_list => p_init_msg_list,
704 x_return_status => l_return_status,
705 x_msg_count => x_msg_count,
706 x_msg_data => x_msg_data,
707 p_ptvv_rec => l_ptvv_rec,
708 x_ptvv_rec => x_ptvv_rec);
709
710 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
711 RAISE Okl_Api.G_EXCEPTION_ERROR;
712 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
713 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
714 END IF;
715
716 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
717 x_msg_data => x_msg_data);
718 EXCEPTION
719 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
720 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
721 p_pkg_name => G_PKG_NAME,
722 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
723 x_msg_count => x_msg_count,
724 x_msg_data => x_msg_data,
725 p_api_type => '_PVT');
726 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
727 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
728 p_pkg_name => G_PKG_NAME,
729 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
730 x_msg_count => x_msg_count,
731 x_msg_data => x_msg_data,
732 p_api_type => '_PVT');
733 WHEN OTHERS THEN
734 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
735 p_pkg_name => G_PKG_NAME,
736 p_exc_name => 'OTHERS',
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_api_type => '_PVT');
740
741 END insert_tqyvalues;
742
743 ---------------------------------------------------------------------------
744 -- PROCEDURE update_PQYVALUES for: OKL_PTQ_VALUES_V
745 ---------------------------------------------------------------------------
746 PROCEDURE update_tqyvalues(p_api_version IN NUMBER,
747 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2,
751 p_ptqv_rec IN ptqv_rec_type,
752 p_ptvv_rec IN ptvv_rec_type,
753 x_ptvv_rec OUT NOCOPY ptvv_rec_type
754 ) IS
755 l_api_version CONSTANT NUMBER := 1;
756 l_api_name CONSTANT VARCHAR2(30) := 'update_tqyvalues';
757 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
758 l_sysdate DATE := to_date(to_char(SYSDATE, 'DD/MM/YYYY'), 'DD/MM/YYYY');
759 l_valid BOOLEAN;
760 l_db_ptvv_rec ptvv_rec_type; /* database copy */
761 l_upd_ptvv_rec ptvv_rec_type; /* input copy */
762 l_ptvv_rec ptvv_rec_type; /* latest with the retained changes */
763 l_ptqv_rec ptqv_rec_type; /* Parent Record */
764 l_tmp_ptvv_rec ptvv_rec_type; /* for any other purposes */
765 l_no_data_found BOOLEAN := TRUE;
766 l_action VARCHAR2(1);
767 l_row_notfound BOOLEAN := TRUE;
768 BEGIN
769 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
770
771 l_upd_ptvv_rec := p_ptvv_rec;
772
773 l_return_status := Okl_Api.START_ACTIVITY(p_api_name => l_api_name,
774 p_pkg_name => G_PKG_NAME,
775 p_init_msg_list => p_init_msg_list,
776 l_api_version => l_api_version,
777 p_api_version => p_api_version,
778 p_api_type => '_PVT',
779 x_return_status => l_return_status);
780
781 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
782 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
783 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
784 RAISE Okl_Api.G_EXCEPTION_ERROR;
785 END IF;
786
787 /* fetch old details from the database */
788 get_rec(p_ptvv_rec => l_upd_ptvv_rec,
789 x_return_status => l_return_status,
790 x_no_data_found => l_no_data_found,
791 x_ptvv_rec => l_db_ptvv_rec);
792
793 IF l_return_status <> Okl_Api.G_RET_STS_SUCCESS OR
794 l_no_data_found = TRUE THEN
795 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
796 END IF;
797
798 default_parent_dates( p_ptvv_rec => l_db_ptvv_rec,
799 x_no_data_found => l_row_notfound,
800 x_return_status => l_return_status,
801 x_ptqv_rec => l_ptqv_rec);
802
803 IF (l_row_notfound) THEN
804 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
805 ELSIF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
806 RAISE Okl_Api.G_EXCEPTION_ERROR;
807 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
808 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
809 END IF;
810
811 --Default Child End Date With Its Parents End Date If It Is Not Entered.
812 IF to_date(to_char(l_ptqv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') AND
813 (to_date(to_char(l_upd_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') = to_date(to_char(Okl_Api.G_MISS_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') OR
814 to_date(to_char(l_upd_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') IS NULL) THEN
815 l_upd_ptvv_rec.TO_DATE := l_ptqv_rec.TO_DATE;
816 END IF;
817
818 /* to reorganize the input accordingly */
819 reorganize_inputs(p_upd_ptvv_rec => l_upd_ptvv_rec,
820 p_db_ptvv_rec => l_db_ptvv_rec);
821
822 /* check for past records */
823 /*IF to_date(to_char(l_db_ptvv_rec.from_date, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate AND
824 to_date(to_char(l_db_ptvv_rec.TO_DATE, 'DD/MM/YYYY'), 'DD/MM/YYYY') < l_sysdate THEN
825 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
826 p_msg_name => G_PAST_RECORDS);
827 x_return_status := Okl_Api.G_RET_STS_ERROR;
828 RAISE Okl_Api.G_EXCEPTION_ERROR;
829 END IF;*/
830
831
832 IF (l_upd_ptvv_rec.TO_DATE = Okl_Api.G_MISS_DATE) then
833 l_upd_ptvv_rec.TO_DATE := p_ptvv_rec.to_date;
834 end if;
835
836 IF (l_upd_ptvv_rec.from_DATE = Okl_Api.G_MISS_DATE) then
837 l_upd_ptvv_rec.from_DATE := p_ptvv_rec.from_date;
838 end if;
839
840 /* check for end date greater than start date */
841 IF (l_upd_ptvv_rec.TO_DATE IS NOT NULL) AND (l_upd_ptvv_rec.TO_DATE < l_upd_ptvv_rec.from_date) THEN
842 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
843 ,p_msg_name => Okl_Ptv_Pvt.g_to_date_error
844 ,p_token1 => Okl_Ptv_Pvt.g_col_name_token
845 ,p_token1_value => 'TO_DATE');
846 x_return_status := Okl_Api.G_RET_STS_ERROR;
847 RAISE Okl_Api.G_EXCEPTION_ERROR;
848 END IF;
849
850 /* determine how the processing to be done */
851 l_action := determine_action(p_upd_ptvv_rec => l_upd_ptvv_rec,
852 p_db_ptvv_rec => l_db_ptvv_rec,
853 p_date => l_sysdate);
854
855 /* Scenario 1: only changing description and descriptive flexfields */
856 IF l_action = '1' THEN
857 /* public api to update tqualities */
858 /* public api to update PTYVALUES */
859 Okl_Ptl_Qualitys_Pub.update_ptl_qlty_values(p_api_version => p_api_version,
860 p_init_msg_list => p_init_msg_list,
861 x_return_status => l_return_status,
862 x_msg_count => x_msg_count,
863 x_msg_data => x_msg_data,
864 p_ptvv_rec => l_upd_ptvv_rec,
865 x_ptvv_rec => x_ptvv_rec);
866 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
867 RAISE Okl_Api.G_EXCEPTION_ERROR;
868 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
869 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
870 END IF;
871 /* Scenario 2: changing the dates */
872 ELSIF l_action = '2' THEN
873 /* create a temporary record with all relevant details from db and upd records */
874 l_ptvv_rec := defaults_to_actuals(p_upd_ptvv_rec => l_upd_ptvv_rec,
875 p_db_ptvv_rec => l_db_ptvv_rec);
876
877 check_updates(p_upd_ptvv_rec => l_upd_ptvv_rec,
878 p_db_ptvv_rec => l_db_ptvv_rec,
879 p_ptvv_rec => l_ptvv_rec,
880 x_return_status => l_return_status,
881 x_msg_data => x_msg_data);
882
883 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
884 RAISE Okl_Api.G_EXCEPTION_ERROR;
885 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
886 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
887 END IF;
888
889 /* public api to update PTYVALUES */
890 Okl_Ptl_Qualitys_Pub.update_ptl_qlty_values(p_api_version => p_api_version,
891 p_init_msg_list => p_init_msg_list,
892 x_return_status => l_return_status,
893 x_msg_count => x_msg_count,
894 x_msg_data => x_msg_data,
895 p_ptvv_rec => l_upd_ptvv_rec,
896 x_ptvv_rec => x_ptvv_rec);
897 IF l_return_status = Okl_Api.G_RET_STS_ERROR THEN
898 RAISE Okl_Api.G_EXCEPTION_ERROR;
899 ELSIF l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR THEN
900 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
901 END IF;
902 END IF;
903
904 Okl_Api.END_ACTIVITY(x_msg_count => x_msg_count,
905 x_msg_data => x_msg_data);
906 EXCEPTION
907 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
908 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
909 p_pkg_name => G_PKG_NAME,
910 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
911 x_msg_count => x_msg_count,
912 x_msg_data => x_msg_data,
913 p_api_type => '_PVT');
914 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
915 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
916 p_pkg_name => G_PKG_NAME,
917 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
918 x_msg_count => x_msg_count,
919 x_msg_data => x_msg_data,
920 p_api_type => '_PVT');
921 WHEN OTHERS THEN
922 x_return_status := Okl_Api.HANDLE_EXCEPTIONS(p_api_name => l_api_name,
923 p_pkg_name => G_PKG_NAME,
924 p_exc_name => 'OTHERS',
925 x_msg_count => x_msg_count,
926 x_msg_data => x_msg_data,
927 p_api_type => '_PVT');
928
929 END update_tqyvalues;
930
931
932 END Okl_Setuptqyvalues_Pvt;