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