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