[Home] [Help]
PACKAGE BODY: APPS.OKL_PTQ_PVT
Source
1 PACKAGE BODY OKL_PTQ_PVT AS
2 /* $Header: OKLSPTQB.pls 115.13 2002/12/18 13:05:43 kjinger noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 NULL;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 NULL;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 NULL;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKL_PTL_QUALITYS
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_ptq_rec IN ptq_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN ptq_rec_type IS
42 CURSOR okl_ptl_qualitys_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 NAME,
46 OBJECT_VERSION_NUMBER,
47 DESCRIPTION,
48 FROM_DATE,
49 TO_DATE,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN
55 FROM Okl_Ptl_Qualitys
56 WHERE okl_ptl_qualitys.id = p_id;
57 l_okl_ptl_qualitys_pk okl_ptl_qualitys_pk_csr%ROWTYPE;
58 l_ptq_rec ptq_rec_type;
59 BEGIN
60 x_no_data_found := TRUE;
61 -- Get current database values
62 OPEN okl_ptl_qualitys_pk_csr (p_ptq_rec.id);
63 FETCH okl_ptl_qualitys_pk_csr INTO
64 l_ptq_rec.ID,
65 l_ptq_rec.NAME,
66 l_ptq_rec.OBJECT_VERSION_NUMBER,
67 l_ptq_rec.DESCRIPTION,
68 l_ptq_rec.FROM_DATE,
69 l_ptq_rec.TO_DATE,
70 l_ptq_rec.CREATED_BY,
71 l_ptq_rec.CREATION_DATE,
72 l_ptq_rec.LAST_UPDATED_BY,
73 l_ptq_rec.LAST_UPDATE_DATE,
74 l_ptq_rec.LAST_UPDATE_LOGIN;
75 x_no_data_found := okl_ptl_qualitys_pk_csr%NOTFOUND;
76 CLOSE okl_ptl_qualitys_pk_csr;
77 RETURN(l_ptq_rec);
78 END get_rec;
79
80 FUNCTION get_rec (
81 p_ptq_rec IN ptq_rec_type
82 ) RETURN ptq_rec_type IS
83 l_row_notfound BOOLEAN := TRUE;
84 BEGIN
85 RETURN(get_rec(p_ptq_rec, l_row_notfound));
86 END get_rec;
87 ---------------------------------------------------------------------------
88 -- FUNCTION get_rec for: OKL_PTL_QUALITYS_V
89 ---------------------------------------------------------------------------
90 FUNCTION get_rec (
91 p_ptqv_rec IN ptqv_rec_type,
92 x_no_data_found OUT NOCOPY BOOLEAN
93 ) RETURN ptqv_rec_type IS
94 CURSOR okl_ptqv_pk_csr (p_id IN NUMBER) IS
95 SELECT
96 ID,
97 OBJECT_VERSION_NUMBER,
98 NAME,
99 DESCRIPTION,
100 FROM_DATE,
101 TO_DATE,
102 CREATED_BY,
103 CREATION_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 LAST_UPDATE_LOGIN
107 FROM Okl_Ptl_Qualitys_V
108 WHERE okl_ptl_qualitys_v.id = p_id;
109 l_okl_ptqv_pk okl_ptqv_pk_csr%ROWTYPE;
110 l_ptqv_rec ptqv_rec_type;
111 BEGIN
112 x_no_data_found := TRUE;
113 -- Get current database values
114 OPEN okl_ptqv_pk_csr (p_ptqv_rec.id);
115 FETCH okl_ptqv_pk_csr INTO
116 l_ptqv_rec.ID,
117 l_ptqv_rec.OBJECT_VERSION_NUMBER,
118 l_ptqv_rec.NAME,
119 l_ptqv_rec.DESCRIPTION,
120 l_ptqv_rec.FROM_DATE,
121 l_ptqv_rec.TO_DATE,
122 l_ptqv_rec.CREATED_BY,
123 l_ptqv_rec.CREATION_DATE,
124 l_ptqv_rec.LAST_UPDATED_BY,
125 l_ptqv_rec.LAST_UPDATE_DATE,
126 l_ptqv_rec.LAST_UPDATE_LOGIN;
127 x_no_data_found := okl_ptqv_pk_csr%NOTFOUND;
128 CLOSE okl_ptqv_pk_csr;
129 RETURN(l_ptqv_rec);
130 END get_rec;
131
132 FUNCTION get_rec (
133 p_ptqv_rec IN ptqv_rec_type
134 ) RETURN ptqv_rec_type IS
135 l_row_notfound BOOLEAN := TRUE;
136 BEGIN
137 RETURN(get_rec(p_ptqv_rec, l_row_notfound));
138 END get_rec;
139
140 --------------------------------------------------------
141 -- FUNCTION null_out_defaults for: OKL_PTL_QUALITYS_V --
142 --------------------------------------------------------
143 FUNCTION null_out_defaults (
144 p_ptqv_rec IN ptqv_rec_type
145 ) RETURN ptqv_rec_type IS
146 l_ptqv_rec ptqv_rec_type := p_ptqv_rec;
147 BEGIN
148 IF (l_ptqv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
149 l_ptqv_rec.object_version_number := NULL;
150 END IF;
151 IF (l_ptqv_rec.name = OKC_API.G_MISS_CHAR) THEN
152 l_ptqv_rec.name := NULL;
153 END IF;
154 IF (l_ptqv_rec.description = OKC_API.G_MISS_CHAR) THEN
155 l_ptqv_rec.description := NULL;
156 END IF;
157 IF (l_ptqv_rec.from_date = OKC_API.G_MISS_DATE) THEN
158 l_ptqv_rec.from_date := NULL;
159 END IF;
160 IF (l_ptqv_rec.TO_DATE = OKC_API.G_MISS_DATE) THEN
161 l_ptqv_rec.TO_DATE := NULL;
162 END IF;
163 IF (l_ptqv_rec.created_by = OKC_API.G_MISS_NUM) THEN
164 l_ptqv_rec.created_by := NULL;
165 END IF;
166 IF (l_ptqv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
167 l_ptqv_rec.creation_date := NULL;
168 END IF;
169 IF (l_ptqv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
170 l_ptqv_rec.last_updated_by := NULL;
171 END IF;
172 IF (l_ptqv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
173 l_ptqv_rec.last_update_date := NULL;
174 END IF;
175 IF (l_ptqv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
176 l_ptqv_rec.last_update_login := NULL;
177 END IF;
178 RETURN(l_ptqv_rec);
179 END null_out_defaults;
180
181 /**********************TCHGS: Commenting Old Code ******************************
182 ---------------------------------------------------------------------------
183 -- PROCEDURE Validate_Attributes
184 ---------------------------------------------------------------------------
185 ------------------------------------------------
186 -- Validate_Attributes for:OKL_PTL_QUALITYS_V --
187 ------------------------------------------------
188 FUNCTION Validate_Attributes (
189 p_ptqv_rec IN ptqv_rec_type
190 ) RETURN VARCHAR2 IS
191 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
192 BEGIN
193 IF p_ptqv_rec.id = OKC_API.G_MISS_NUM OR
194 p_ptqv_rec.id IS NULL
195 THEN
196 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
197 l_return_status := OKC_API.G_RET_STS_ERROR;
198 ELSIF p_ptqv_rec.object_version_number = OKC_API.G_MISS_NUM OR
199 p_ptqv_rec.object_version_number IS NULL
200 THEN
201 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
202 l_return_status := OKC_API.G_RET_STS_ERROR;
203 ELSIF p_ptqv_rec.name = OKC_API.G_MISS_CHAR OR
204 p_ptqv_rec.name IS NULL
205 THEN
206 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'name');
207 l_return_status := OKC_API.G_RET_STS_ERROR;
208 END IF;
209 RETURN(l_return_status);
210 END Validate_Attributes;
211
212 ---------------------------------------------------------------------------
213 -- PROCEDURE Validate_Record
214 ---------------------------------------------------------------------------
215 --------------------------------------------
216 -- Validate_Record for:OKL_PTL_QUALITYS_V --
217 --------------------------------------------
218 FUNCTION Validate_Record (
219 p_ptqv_rec IN ptqv_rec_type
220 ) RETURN VARCHAR2 IS
221 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
222 BEGIN
223 RETURN (l_return_status);
224 END Validate_Record;
225
226 **********************TCHGS: Commenting Old Code ******************************/
227
228 /************************** TCHGS: Start New Code *****************************/
229 ---------------------------------------------------------------------------
230 -- PROCEDURE Validate_Id
231 ---------------------------------------------------------------------------
232 -- Start of comments
233 --
234 -- Procedure Name : Validate_Id
235 -- Description :
236 -- Business Rules :
237 -- Parameters :
238 -- Version : 1.0
239 -- End of comments
240 ---------------------------------------------------------------------------
241 PROCEDURE Validate_Id(p_ptqv_rec IN ptqv_rec_type
242 ,x_return_status OUT NOCOPY VARCHAR2)
243 IS
244
245 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
246
247 BEGIN
248 -- initialize return status
249 x_return_status := OKC_API.G_RET_STS_SUCCESS;
250
251 -- check for data before processing
252 IF (p_ptqv_rec.id IS NULL) OR
253 (p_ptqv_rec.id = OKC_API.G_MISS_NUM) THEN
254 OKC_API.SET_MESSAGE(p_app_name => g_app_name
255 ,p_msg_name => g_required_value
256 ,p_token1 => g_col_name_token
257 ,p_token1_value => 'id');
258 x_return_status := OKC_API.G_RET_STS_ERROR;
259 RAISE G_EXCEPTION_HALT_VALIDATION;
260 END IF;
261
262 EXCEPTION
263 WHEN G_EXCEPTION_HALT_VALIDATION THEN
264 -- no processing necessary; validation can continue
265 -- with the next column
266 NULL;
267
268 WHEN OTHERS THEN
269 -- store SQL error message on message stack for caller
270 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
271 p_msg_name => g_unexpected_error,
272 p_token1 => g_sqlcode_token,
273 p_token1_value => SQLCODE,
274 p_token2 => g_sqlerrm_token,
275 p_token2_value => SQLERRM);
276
277 -- notify caller of an UNEXPECTED error
278 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
279
280 END Validate_Id;
281
282 ---------------------------------------------------------------------------
283 -- PROCEDURE Validate_Name
284 ---------------------------------------------------------------------------
285 -- Start of comments
286 --
287 -- Procedure Name : Validate_Name
288 -- Description :
289 -- Business Rules :
290 -- Parameters :
291 -- Version : 1.0
292 -- End of comments
293 ---------------------------------------------------------------------------
294 PROCEDURE Validate_Name(p_ptqv_rec IN OUT NOCOPY ptqv_rec_type
295 ,x_return_status OUT NOCOPY VARCHAR2)
296 IS
297
298 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
299
300 BEGIN
301 -- initialize return status
302 x_return_status := OKC_API.G_RET_STS_SUCCESS;
303
304 -- check for data before processing
305 IF (p_ptqv_rec.name IS NULL) OR
306 (p_ptqv_rec.name = OKC_API.G_MISS_CHAR) THEN
307 OKC_API.SET_MESSAGE(p_app_name => g_app_name
308 ,p_msg_name => g_required_value
309 ,p_token1 => g_col_name_token
310 ,p_token1_value => 'name');
311 x_return_status := OKC_API.G_RET_STS_ERROR;
312 RAISE G_EXCEPTION_HALT_VALIDATION;
313 END IF;
314 p_ptqv_rec.name := Okl_Accounting_Util.okl_upper(p_ptqv_rec.name);
315 EXCEPTION
316 WHEN G_EXCEPTION_HALT_VALIDATION THEN
317 -- no processing necessary; validation can continue
318 -- with the next column
319 NULL;
320
321 WHEN OTHERS THEN
322 -- store SQL error message on message stack for caller
323 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
324 p_msg_name => g_unexpected_error,
325 p_token1 => g_sqlcode_token,
326 p_token1_value => SQLCODE,
327 p_token2 => g_sqlerrm_token,
328 p_token2_value => SQLERRM);
329
330 -- notify caller of an UNEXPECTED error
331 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
332
333 END Validate_Name;
334
335 ---------------------------------------------------------------------------
336 -- PROCEDURE Validate_Object_Version_Number
337 ---------------------------------------------------------------------------
338 -- Start of comments
339 --
340 -- Procedure Name : Validate_Object_Version_Number
341 -- Description :
342 -- Business Rules :
343 -- Parameters :
344 -- Version : 1.0
345 -- End of comments
346 ---------------------------------------------------------------------------
347 PROCEDURE Validate_Object_Version_Number(p_ptqv_rec IN ptqv_rec_type
348 ,x_return_status OUT NOCOPY VARCHAR2)
349 IS
350
351 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
352
353 BEGIN
354 -- initialize return status
355 x_return_status := OKC_API.G_RET_STS_SUCCESS;
356
357 -- check for data before processing
358 IF (p_ptqv_rec.object_version_number IS NULL) OR
359 (p_ptqv_rec.object_version_Number = OKC_API.G_MISS_NUM) THEN
360 OKC_API.SET_MESSAGE(p_app_name => g_app_name
361 ,p_msg_name => g_required_value
362 ,p_token1 => g_col_name_token
363 ,p_token1_value => 'object_version_number');
364 x_return_status := OKC_API.G_RET_STS_ERROR;
365 RAISE G_EXCEPTION_HALT_VALIDATION;
366 END IF;
367
368 EXCEPTION
369 WHEN G_EXCEPTION_HALT_VALIDATION THEN
370 -- no processing necessary; validation can continue
371 -- with the next column
372 NULL;
373
374 WHEN OTHERS THEN
375 -- store SQL error message on message stack for caller
376 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
377 p_msg_name => g_unexpected_error,
378 p_token1 => g_sqlcode_token,
379 p_token1_value => SQLCODE,
380 p_token2 => g_sqlerrm_token,
381 p_token2_value => SQLERRM);
382
383 -- notify caller of an UNEXPECTED error
384 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
385
386 END Validate_Object_Version_Number;
387
388 ---------------------------------------------------------------------------
389 -- PROCEDURE Validate_From_Date
390 ---------------------------------------------------------------------------
391 -- Start of comments
392 --
393 -- Procedure Name : Validate_From_Date
394 -- Description :
395 -- Business Rules :
396 -- Parameters :
397 -- Version : 1.0
398 -- End of comments
399 ---------------------------------------------------------------------------
400 PROCEDURE Validate_From_Date(p_ptqv_rec IN ptqv_rec_type
401 ,x_return_status OUT NOCOPY VARCHAR2)
402 IS
403
404 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
405
406 BEGIN
407 -- initialize return status
408 x_return_status := OKC_API.G_RET_STS_SUCCESS;
409
410 -- check for data before processing
411 IF (p_ptqv_rec.from_date IS NULL) OR
412 (p_ptqv_rec.from_date = OKC_API.G_MISS_DATE) THEN
413 OKC_API.SET_MESSAGE(p_app_name => g_app_name
414 ,p_msg_name => g_required_value
415 ,p_token1 => g_col_name_token
416 ,p_token1_value => 'from_date');
417 x_return_status := OKC_API.G_RET_STS_ERROR;
418 RAISE G_EXCEPTION_HALT_VALIDATION;
419 END IF;
420
421 EXCEPTION
422 WHEN G_EXCEPTION_HALT_VALIDATION THEN
423 -- no processing necessary; validation can continue
424 -- with the next column
425 NULL;
426
427 WHEN OTHERS THEN
428 -- store SQL error message on message stack for caller
429 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
430 p_msg_name => g_unexpected_error,
431 p_token1 => g_sqlcode_token,
432 p_token1_value => SQLCODE,
433 p_token2 => g_sqlerrm_token,
434 p_token2_value => SQLERRM);
435
436 -- notify caller of an UNEXPECTED error
437 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
438
439 END Validate_From_Date;
440
441 ---------------------------------------------------------------------------
442 -- PROCEDURE Validate_To_Date
443 ---------------------------------------------------------------------------
444 -- Start of comments
445 --
446 -- Procedure Name : Validate_To_Date
447 -- Description :
448 -- Business Rules :
449 -- Parameters :
450 -- Version : 1.0
451 -- End of comments
452 ---------------------------------------------------------------------------
453 PROCEDURE Validate_To_Date(p_ptqv_rec IN ptqv_rec_type
454 ,x_return_status OUT NOCOPY VARCHAR2)
455 IS
456
457 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
458
459 BEGIN
460 -- initialize return status
461 x_return_status := OKC_API.G_RET_STS_SUCCESS;
462
463 -- check for data before processing
464 IF (p_ptqv_rec.TO_DATE IS NOT NULL) AND
465 (p_ptqv_rec.TO_DATE < p_ptqv_rec.from_date) THEN
466 OKC_API.SET_MESSAGE(p_app_name => 'OKL'
467 ,p_msg_name => g_to_date_error
468 ,p_token1 => g_col_name_token
469 ,p_token1_value => 'TO_DATE');
470 x_return_status := OKC_API.G_RET_STS_ERROR;
471 RAISE G_EXCEPTION_HALT_VALIDATION;
472 END IF;
473
474 EXCEPTION
475 WHEN G_EXCEPTION_HALT_VALIDATION THEN
476 -- no processing necessary; validation can continue
477 -- with the next column
478 NULL;
479
480 WHEN OTHERS THEN
481 -- store SQL error message on message stack for caller
482 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
483 p_msg_name => g_unexpected_error,
484 p_token1 => g_sqlcode_token,
485 p_token1_value => SQLCODE,
486 p_token2 => g_sqlerrm_token,
487 p_token2_value => SQLERRM);
488
489 -- notify caller of an UNEXPECTED error
490 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
491
492 END Validate_To_Date;
493
494 ---------------------------------------------------------------------------
495 -- FUNCTION Validate_Attributes
496 ---------------------------------------------------------------------------
497 -- Start of comments
498 --
499 -- Function Name : Validate_Attributes
500 -- Description :
501 -- Business Rules :
502 -- Parameters :
503 -- Version : 1.0
504 -- End of comments
505 ---------------------------------------------------------------------------
506
507 FUNCTION Validate_Attributes (
508 p_ptqv_rec IN OUT NOCOPY ptqv_rec_type
509 ) RETURN VARCHAR2 IS
510 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
511 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
512 BEGIN
513 -- Validate_Id
514 Validate_Id(p_ptqv_rec,x_return_status);
515 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
516 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
517 -- need to leave
518 l_return_status := x_return_status;
519 RAISE G_EXCEPTION_HALT_VALIDATION;
520 ELSE
521 -- record that there was an error
522 l_return_status := x_return_status;
523 END IF;
524 END IF;
525
526 -- Validate_Name
527 Validate_Name(p_ptqv_rec,x_return_status);
528 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
529 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
530 -- need to leave
531 l_return_status := x_return_status;
532 RAISE G_EXCEPTION_HALT_VALIDATION;
533 ELSE
534 -- record that there was an error
535 l_return_status := x_return_status;
536 END IF;
537 END IF;
538
539 -- Validate_Object_Version_Number
540 Validate_Object_Version_Number(p_ptqv_rec,x_return_status);
541 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
542 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
543 -- need to leave
544 l_return_status := x_return_status;
545 RAISE G_EXCEPTION_HALT_VALIDATION;
546 ELSE
547 -- record that there was an error
548 l_return_status := x_return_status;
549 END IF;
550 END IF;
551
552 -- Validate_From_Date
553 Validate_From_Date(p_ptqv_rec,x_return_status);
554 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
555 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
556 -- need to leave
557 l_return_status := x_return_status;
558 RAISE G_EXCEPTION_HALT_VALIDATION;
559 ELSE
560 -- record that there was an error
561 l_return_status := x_return_status;
562 END IF;
563 END IF;
564
565 RETURN(l_return_status);
566 EXCEPTION
567 WHEN G_EXCEPTION_HALT_VALIDATION THEN
568 -- just come out with return status
569 NULL;
570 RETURN (l_return_status);
571
572 WHEN OTHERS THEN
573 -- store SQL error message on message stack for caller
574 OKC_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 -- notify caller of an UNEXPECTED error
581 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
582 RETURN(l_return_status);
583
584 END Validate_Attributes;
585
586 ---------------------------------------------------------------------------
587 -- PROCEDURE Validate_Unique_Ptq_Record
588 ---------------------------------------------------------------------------
589 -- Start of comments
590 --
591 -- Procedure Name : Validate_Unique_Ptq_Record
592 -- Description :
593 -- Business Rules :
594 -- Parameters :
595 -- Version : 1.0
596 -- End of comments
597 ---------------------------------------------------------------------------
598 PROCEDURE Validate_Unique_Ptq_Record(p_ptqv_rec IN ptqv_rec_type
599 ,x_return_status OUT NOCOPY VARCHAR2)
600 IS
601
602 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
603 l_unq_tbl OKC_UTIL.unq_tbl_type;
604 l_ptq_status VARCHAR2(1);
605 l_row_found BOOLEAN := FALSE;
606 CURSOR c1(p_name okl_ptl_qualitys_v.name%TYPE) IS
607 SELECT '1'
608 FROM okl_ptl_qualitys_v
609 WHERE name = p_name
610 AND id <> NVL(p_ptqv_rec.id,-9999);
611
612 BEGIN
613
614 -- initialize return status
615 x_return_status := OKC_API.G_RET_STS_SUCCESS;
616 OPEN c1(p_ptqv_rec.name);
617 FETCH c1 INTO l_ptq_status;
618 l_row_found := c1%FOUND;
619 CLOSE c1;
620 IF l_row_found THEN
621 OKC_API.set_message('OKL',G_UNQS,G_TABLE_TOKEN, 'Okl_Ptl_Qualitys_V'); ---CHG001
622 x_return_status := OKC_API.G_RET_STS_ERROR;
623 END IF;
624 EXCEPTION
625 WHEN G_EXCEPTION_HALT_VALIDATION THEN
626 -- no processing necessary; validation can continue
627 -- with the next column
628 NULL;
629
630 WHEN OTHERS THEN
631 -- store SQL error message on message stack for caller
632 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
633 p_msg_name => g_unexpected_error,
634 p_token1 => g_sqlcode_token,
635 p_token1_value => SQLCODE,
636 p_token2 => g_sqlerrm_token,
637 p_token2_value => SQLERRM);
638
639 -- notify caller of an UNEXPECTED error
640 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
641
642 END Validate_Unique_Ptq_Record;
643
644 ---------------------------------------------------------------------------
645 -- PROCEDURE Validate_Record
646 ---------------------------------------------------------------------------
647 -- Start of comments
648 --
649 -- Procedure Name : Validate_Record
650 -- Description :
651 -- Business Rules :
652 -- Parameters :
653 -- Version : 1.0
654 -- End of comments
655 ---------------------------------------------------------------------------
656
657 FUNCTION Validate_Record (
658 p_ptqv_rec IN ptqv_rec_type
659 ) RETURN VARCHAR2 IS
660 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
661 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
662 BEGIN
663 -- Validate_Unique_Ptq_Record
664 Validate_Unique_Ptq_Record(p_ptqv_rec,x_return_status);
665 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
666 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
667 -- need to leave
668 l_return_status := x_return_status;
669 RAISE G_EXCEPTION_HALT_VALIDATION;
670 ELSE
671 -- record that there was an error
672 l_return_status := x_return_status;
673 END IF;
674 END IF;
675
676 -- Validate_To_Date
677 Validate_To_Date(p_ptqv_rec,x_return_status);
678 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
679 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
680 -- need to leave
681 l_return_status := x_return_status;
682 RAISE G_EXCEPTION_HALT_VALIDATION;
683 ELSE
684 -- record that there was an error
685 l_return_status := x_return_status;
686 END IF;
687 END IF;
688
689 RETURN(l_return_status);
690 EXCEPTION
691 WHEN G_EXCEPTION_HALT_VALIDATION THEN
692 -- just come out with return status
693 NULL;
694 RETURN (l_return_status);
695
696 WHEN OTHERS THEN
697 -- store SQL error message on message stack for caller
698 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
699 p_msg_name => g_unexpected_error,
700 p_token1 => g_sqlcode_token,
701 p_token1_value => SQLCODE,
702 p_token2 => g_sqlerrm_token,
703 p_token2_value => SQLERRM);
704 -- notify caller of an UNEXPECTED error
705 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
706 RETURN(l_return_status);
707 END Validate_Record;
708
709 /************************** TCHGS: Start New Code *****************************/
710
711 ---------------------------------------------------------------------------
712 -- PROCEDURE Migrate
713 ---------------------------------------------------------------------------
714 PROCEDURE migrate (
715 p_from IN ptqv_rec_type,
716 p_to IN OUT NOCOPY ptq_rec_type
717 ) IS
718 BEGIN
719 p_to.id := p_from.id;
720 p_to.name := p_from.name;
721 p_to.object_version_number := p_from.object_version_number;
722 p_to.description := p_from.description;
723 p_to.from_date := p_from.from_date;
724 p_to.TO_DATE := p_from.TO_DATE;
725 p_to.created_by := p_from.created_by;
726 p_to.creation_date := p_from.creation_date;
727 p_to.last_updated_by := p_from.last_updated_by;
728 p_to.last_update_date := p_from.last_update_date;
729 p_to.last_update_login := p_from.last_update_login;
730 END migrate;
731 PROCEDURE migrate (
732 p_from IN ptq_rec_type,
733 p_to IN OUT NOCOPY ptqv_rec_type
734 ) IS
735 BEGIN
736 p_to.id := p_from.id;
737 p_to.name := p_from.name;
738 p_to.object_version_number := p_from.object_version_number;
739 p_to.description := p_from.description;
740 p_to.from_date := p_from.from_date;
741 p_to.TO_DATE := p_from.TO_DATE;
742 p_to.created_by := p_from.created_by;
743 p_to.creation_date := p_from.creation_date;
744 p_to.last_updated_by := p_from.last_updated_by;
745 p_to.last_update_date := p_from.last_update_date;
746 p_to.last_update_login := p_from.last_update_login;
747 END migrate;
748
749 ---------------------------------------------------------------------------
750 -- PROCEDURE validate_row
751 ---------------------------------------------------------------------------
752 -----------------------------------------
753 -- validate_row for:OKL_PTL_QUALITYS_V --
754 -----------------------------------------
755 PROCEDURE validate_row(
756 p_api_version IN NUMBER,
757 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
758 x_return_status OUT NOCOPY VARCHAR2,
759 x_msg_count OUT NOCOPY NUMBER,
760 x_msg_data OUT NOCOPY VARCHAR2,
761 p_ptqv_rec IN ptqv_rec_type) IS
762
763 l_api_version CONSTANT NUMBER := 1;
764 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
765 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
766 l_ptqv_rec ptqv_rec_type := p_ptqv_rec;
767 l_ptq_rec ptq_rec_type;
768 BEGIN
769 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
770 G_PKG_NAME,
771 p_init_msg_list,
772 l_api_version,
773 p_api_version,
774 '_PVT',
775 x_return_status);
776 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
777 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
778 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
779 RAISE OKC_API.G_EXCEPTION_ERROR;
780 END IF;
781 --- Validate all non-missing attributes (Item Level Validation)
782 l_return_status := Validate_Attributes(l_ptqv_rec);
783 --- If any errors happen abort API
784 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
785 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
786 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
787 RAISE OKC_API.G_EXCEPTION_ERROR;
788 END IF;
789 l_return_status := Validate_Record(l_ptqv_rec);
790 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
791 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
792 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
793 RAISE OKC_API.G_EXCEPTION_ERROR;
794 END IF;
795 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
796 EXCEPTION
797 WHEN OKC_API.G_EXCEPTION_ERROR THEN
798 x_return_status := OKC_API.HANDLE_EXCEPTIONS
799 (
800 l_api_name,
801 G_PKG_NAME,
802 'OKC_API.G_RET_STS_ERROR',
803 x_msg_count,
804 x_msg_data,
805 '_PVT'
806 );
807 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
808 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
809 (
810 l_api_name,
811 G_PKG_NAME,
812 'OKC_API.G_RET_STS_UNEXP_ERROR',
813 x_msg_count,
814 x_msg_data,
815 '_PVT'
816 );
817 WHEN OTHERS THEN
818 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
819 (
820 l_api_name,
821 G_PKG_NAME,
822 'OTHERS',
823 x_msg_count,
824 x_msg_data,
825 '_PVT'
826 );
827 END validate_row;
828 ------------------------------------------
829 -- PL/SQL TBL validate_row for:PTQV_TBL --
830 ------------------------------------------
831 PROCEDURE validate_row(
832 p_api_version IN NUMBER,
833 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
834 x_return_status OUT NOCOPY VARCHAR2,
835 x_msg_count OUT NOCOPY NUMBER,
836 x_msg_data OUT NOCOPY VARCHAR2,
837 p_ptqv_tbl IN ptqv_tbl_type) IS
838
839 l_api_version CONSTANT NUMBER := 1;
840 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
841 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
842 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
843 i NUMBER := 0;
844 BEGIN
845 OKC_API.init_msg_list(p_init_msg_list);
846 -- Make sure PL/SQL table has records in it before passing
847 IF (p_ptqv_tbl.COUNT > 0) THEN
848 i := p_ptqv_tbl.FIRST;
849 LOOP
850 validate_row (
851 p_api_version => p_api_version,
852 p_init_msg_list => OKC_API.G_FALSE,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data,
856 p_ptqv_rec => p_ptqv_tbl(i));
857 -- TCHGS: Store the highest degree of error
858 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
859 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
860 l_overall_status := x_return_status;
861 END IF;
862 END IF;
863 EXIT WHEN (i = p_ptqv_tbl.LAST);
864 i := p_ptqv_tbl.NEXT(i);
865 END LOOP;
866 --TCHGS: return overall status
867 x_return_status := l_overall_status;
868 END IF;
869 EXCEPTION
870 WHEN OKC_API.G_EXCEPTION_ERROR THEN
871 x_return_status := OKC_API.HANDLE_EXCEPTIONS
872 (
873 l_api_name,
874 G_PKG_NAME,
875 'OKC_API.G_RET_STS_ERROR',
876 x_msg_count,
877 x_msg_data,
878 '_PVT'
879 );
880 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
881 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
882 (
883 l_api_name,
884 G_PKG_NAME,
885 'OKC_API.G_RET_STS_UNEXP_ERROR',
886 x_msg_count,
887 x_msg_data,
888 '_PVT'
889 );
890 WHEN OTHERS THEN
891 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
892 (
893 l_api_name,
894 G_PKG_NAME,
895 'OTHERS',
896 x_msg_count,
897 x_msg_data,
898 '_PVT'
899 );
900 END validate_row;
901
902 ---------------------------------------------------------------------------
903 -- PROCEDURE insert_row
904 ---------------------------------------------------------------------------
905 -------------------------------------
906 -- insert_row for:OKL_PTL_QUALITYS --
907 -------------------------------------
908 PROCEDURE insert_row(
909 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
910 x_return_status OUT NOCOPY VARCHAR2,
911 x_msg_count OUT NOCOPY NUMBER,
912 x_msg_data OUT NOCOPY VARCHAR2,
913 p_ptq_rec IN ptq_rec_type,
914 x_ptq_rec OUT NOCOPY ptq_rec_type) IS
915
916 l_api_version CONSTANT NUMBER := 1;
917 l_api_name CONSTANT VARCHAR2(30) := 'QUALITYS_insert_row';
918 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
919 l_ptq_rec ptq_rec_type := p_ptq_rec;
920 l_def_ptq_rec ptq_rec_type;
921 -----------------------------------------
922 -- Set_Attributes for:OKL_PTL_QUALITYS --
923 -----------------------------------------
924 FUNCTION Set_Attributes (
925 p_ptq_rec IN ptq_rec_type,
926 x_ptq_rec OUT NOCOPY ptq_rec_type
927 ) RETURN VARCHAR2 IS
928 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
929 BEGIN
930 x_ptq_rec := p_ptq_rec;
931 RETURN(l_return_status);
932 END Set_Attributes;
933 BEGIN
934 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
935 p_init_msg_list,
936 '_PVT',
937 x_return_status);
938 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
939 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
940 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
941 RAISE OKC_API.G_EXCEPTION_ERROR;
942 END IF;
943 --- Setting item attributes
944 l_return_status := Set_Attributes(
945 p_ptq_rec, -- IN
946 l_ptq_rec); -- OUT
947 --- If any errors happen abort API
948 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
949 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
950 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
951 RAISE OKC_API.G_EXCEPTION_ERROR;
952 END IF;
953 INSERT INTO OKL_PTL_QUALITYS(
954 id,
955 name,
956 object_version_number,
957 description,
958 from_date,
959 TO_DATE,
960 created_by,
961 creation_date,
962 last_updated_by,
963 last_update_date,
964 last_update_login)
965 VALUES (
966 l_ptq_rec.id,
967 l_ptq_rec.name,
968 l_ptq_rec.object_version_number,
969 l_ptq_rec.description,
970 l_ptq_rec.from_date,
971 l_ptq_rec.TO_DATE,
972 l_ptq_rec.created_by,
973 l_ptq_rec.creation_date,
974 l_ptq_rec.last_updated_by,
975 l_ptq_rec.last_update_date,
976 l_ptq_rec.last_update_login);
977 -- Set OUT values
978 x_ptq_rec := l_ptq_rec;
979 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
980 EXCEPTION
981 WHEN OKC_API.G_EXCEPTION_ERROR THEN
982 x_return_status := OKC_API.HANDLE_EXCEPTIONS
983 (
984 l_api_name,
985 G_PKG_NAME,
986 'OKC_API.G_RET_STS_ERROR',
987 x_msg_count,
988 x_msg_data,
989 '_PVT'
990 );
991 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
992 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
993 (
994 l_api_name,
995 G_PKG_NAME,
996 'OKC_API.G_RET_STS_UNEXP_ERROR',
997 x_msg_count,
998 x_msg_data,
999 '_PVT'
1000 );
1001 WHEN OTHERS THEN
1002 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1003 (
1004 l_api_name,
1005 G_PKG_NAME,
1006 'OTHERS',
1007 x_msg_count,
1008 x_msg_data,
1009 '_PVT'
1010 );
1011 END insert_row;
1012 ---------------------------------------
1013 -- insert_row for:OKL_PTL_QUALITYS_V --
1014 ---------------------------------------
1015 PROCEDURE insert_row(
1016 p_api_version IN NUMBER,
1017 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1018 x_return_status OUT NOCOPY VARCHAR2,
1019 x_msg_count OUT NOCOPY NUMBER,
1020 x_msg_data OUT NOCOPY VARCHAR2,
1021 p_ptqv_rec IN ptqv_rec_type,
1022 x_ptqv_rec OUT NOCOPY ptqv_rec_type) IS
1023
1024 l_api_version CONSTANT NUMBER := 1;
1025 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1026 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1027 l_ptqv_rec ptqv_rec_type;
1028 l_def_ptqv_rec ptqv_rec_type;
1029 l_ptq_rec ptq_rec_type;
1030 lx_ptq_rec ptq_rec_type;
1031 -------------------------------
1032 -- FUNCTION fill_who_columns --
1033 -------------------------------
1034 FUNCTION fill_who_columns (
1035 p_ptqv_rec IN ptqv_rec_type
1036 ) RETURN ptqv_rec_type IS
1037 l_ptqv_rec ptqv_rec_type := p_ptqv_rec;
1038 BEGIN
1039 l_ptqv_rec.CREATION_DATE := SYSDATE;
1040 l_ptqv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1041 l_ptqv_rec.LAST_UPDATE_DATE := l_ptqv_rec.CREATION_DATE;
1042 l_ptqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1043 l_ptqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1044 RETURN(l_ptqv_rec);
1045 END fill_who_columns;
1046 -------------------------------------------
1047 -- Set_Attributes for:OKL_PTL_QUALITYS_V --
1048 -------------------------------------------
1049 FUNCTION Set_Attributes (
1050 p_ptqv_rec IN ptqv_rec_type,
1051 x_ptqv_rec OUT NOCOPY ptqv_rec_type
1052 ) RETURN VARCHAR2 IS
1053 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1054 BEGIN
1055 x_ptqv_rec := p_ptqv_rec;
1056 x_ptqv_rec.OBJECT_VERSION_NUMBER := 1;
1057 RETURN(l_return_status);
1058 END Set_Attributes;
1059 BEGIN
1060 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1061 G_PKG_NAME,
1062 p_init_msg_list,
1063 l_api_version,
1064 p_api_version,
1065 '_PVT',
1066 x_return_status);
1067 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1068 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1069 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1070 RAISE OKC_API.G_EXCEPTION_ERROR;
1071 END IF;
1072 l_ptqv_rec := null_out_defaults(p_ptqv_rec);
1073 -- Set primary key value
1074 l_ptqv_rec.ID := get_seq_id;
1075 --- Setting item attributes
1076 l_return_status := Set_Attributes(
1077 l_ptqv_rec, -- IN
1078 l_def_ptqv_rec); -- OUT
1079 --- If any errors happen abort API
1080 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1081 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1082 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1083 RAISE OKC_API.G_EXCEPTION_ERROR;
1084 END IF;
1085 l_def_ptqv_rec := fill_who_columns(l_def_ptqv_rec);
1086 --- Validate all non-missing attributes (Item Level Validation)
1087 l_return_status := Validate_Attributes(l_def_ptqv_rec);
1088 --- If any errors happen abort API
1089 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1090 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1091 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1092 RAISE OKC_API.G_EXCEPTION_ERROR;
1093 END IF;
1094 l_return_status := Validate_Record(l_def_ptqv_rec);
1095 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1096 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1097 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1098 RAISE OKC_API.G_EXCEPTION_ERROR;
1099 END IF;
1100 --------------------------------------
1101 -- Move VIEW record to "Child" records
1102 --------------------------------------
1103 migrate(l_def_ptqv_rec, l_ptq_rec);
1104 --------------------------------------------
1105 -- Call the INSERT_ROW for each child record
1106 --------------------------------------------
1107 insert_row(
1108 p_init_msg_list,
1109 x_return_status,
1110 x_msg_count,
1111 x_msg_data,
1112 l_ptq_rec,
1113 lx_ptq_rec
1114 );
1115 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1116 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1117 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1118 RAISE OKC_API.G_EXCEPTION_ERROR;
1119 END IF;
1120 migrate(lx_ptq_rec, l_def_ptqv_rec);
1121 -- Set OUT values
1122 x_ptqv_rec := l_def_ptqv_rec;
1123 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1124 EXCEPTION
1125 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1126 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1127 (
1128 l_api_name,
1129 G_PKG_NAME,
1130 'OKC_API.G_RET_STS_ERROR',
1131 x_msg_count,
1132 x_msg_data,
1133 '_PVT'
1134 );
1135 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1136 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1137 (
1138 l_api_name,
1139 G_PKG_NAME,
1140 'OKC_API.G_RET_STS_UNEXP_ERROR',
1141 x_msg_count,
1142 x_msg_data,
1143 '_PVT'
1144 );
1145 WHEN OTHERS THEN
1146 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1147 (
1148 l_api_name,
1149 G_PKG_NAME,
1150 'OTHERS',
1151 x_msg_count,
1152 x_msg_data,
1153 '_PVT'
1154 );
1155 END insert_row;
1156 ----------------------------------------
1157 -- PL/SQL TBL insert_row for:PTQV_TBL --
1158 ----------------------------------------
1159 PROCEDURE insert_row(
1160 p_api_version IN NUMBER,
1161 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1162 x_return_status OUT NOCOPY VARCHAR2,
1163 x_msg_count OUT NOCOPY NUMBER,
1164 x_msg_data OUT NOCOPY VARCHAR2,
1165 p_ptqv_tbl IN ptqv_tbl_type,
1166 x_ptqv_tbl OUT NOCOPY ptqv_tbl_type) IS
1167
1168 l_api_version CONSTANT NUMBER := 1;
1169 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1170 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1171 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1172 i NUMBER := 0;
1173 BEGIN
1174 OKC_API.init_msg_list(p_init_msg_list);
1175 -- Make sure PL/SQL table has records in it before passing
1176 IF (p_ptqv_tbl.COUNT > 0) THEN
1177 i := p_ptqv_tbl.FIRST;
1178 LOOP
1179 insert_row (
1180 p_api_version => p_api_version,
1181 p_init_msg_list => OKC_API.G_FALSE,
1182 x_return_status => x_return_status,
1183 x_msg_count => x_msg_count,
1184 x_msg_data => x_msg_data,
1185 p_ptqv_rec => p_ptqv_tbl(i),
1186 x_ptqv_rec => x_ptqv_tbl(i));
1187 -- TCHGS: Store the highest degree of error
1188 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1189 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1190 l_overall_status := x_return_status;
1191 END IF;
1192 END IF;
1193 EXIT WHEN (i = p_ptqv_tbl.LAST);
1194 i := p_ptqv_tbl.NEXT(i);
1195 END LOOP;
1196 --TCHGS: return overall status
1197 x_return_status := l_overall_status;
1198 END IF;
1199 EXCEPTION
1200 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1201 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1202 (
1203 l_api_name,
1204 G_PKG_NAME,
1205 'OKC_API.G_RET_STS_ERROR',
1206 x_msg_count,
1207 x_msg_data,
1208 '_PVT'
1209 );
1210 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1211 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1212 (
1213 l_api_name,
1214 G_PKG_NAME,
1215 'OKC_API.G_RET_STS_UNEXP_ERROR',
1216 x_msg_count,
1217 x_msg_data,
1218 '_PVT'
1219 );
1220 WHEN OTHERS THEN
1221 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1222 (
1223 l_api_name,
1224 G_PKG_NAME,
1225 'OTHERS',
1226 x_msg_count,
1227 x_msg_data,
1228 '_PVT'
1229 );
1230 END insert_row;
1231
1232 ---------------------------------------------------------------------------
1233 -- PROCEDURE lock_row
1234 ---------------------------------------------------------------------------
1235 -----------------------------------
1236 -- lock_row for:OKL_PTL_QUALITYS --
1237 -----------------------------------
1238 PROCEDURE lock_row(
1239 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1240 x_return_status OUT NOCOPY VARCHAR2,
1241 x_msg_count OUT NOCOPY NUMBER,
1242 x_msg_data OUT NOCOPY VARCHAR2,
1243 p_ptq_rec IN ptq_rec_type) IS
1244
1245 E_Resource_Busy EXCEPTION;
1246 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1247 CURSOR lock_csr (p_ptq_rec IN ptq_rec_type) IS
1248 SELECT OBJECT_VERSION_NUMBER
1249 FROM OKL_PTL_QUALITYS
1250 WHERE ID = p_ptq_rec.id
1251 AND OBJECT_VERSION_NUMBER = p_ptq_rec.object_version_number
1252 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1253
1254 CURSOR lchk_csr (p_ptq_rec IN ptq_rec_type) IS
1255 SELECT OBJECT_VERSION_NUMBER
1256 FROM OKL_PTL_QUALITYS
1257 WHERE ID = p_ptq_rec.id;
1258 l_api_version CONSTANT NUMBER := 1;
1259 l_api_name CONSTANT VARCHAR2(30) := 'QUALITYS_lock_row';
1260 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1261 l_object_version_number OKL_PTL_QUALITYS.OBJECT_VERSION_NUMBER%TYPE;
1262 lc_object_version_number OKL_PTL_QUALITYS.OBJECT_VERSION_NUMBER%TYPE;
1263 l_row_notfound BOOLEAN := FALSE;
1264 lc_row_notfound BOOLEAN := FALSE;
1265 BEGIN
1266 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1267 p_init_msg_list,
1268 '_PVT',
1269 x_return_status);
1270 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1271 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1272 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1273 RAISE OKC_API.G_EXCEPTION_ERROR;
1274 END IF;
1275 BEGIN
1276 OPEN lock_csr(p_ptq_rec);
1277 FETCH lock_csr INTO l_object_version_number;
1278 l_row_notfound := lock_csr%NOTFOUND;
1279 CLOSE lock_csr;
1280 EXCEPTION
1281 WHEN E_Resource_Busy THEN
1282 IF (lock_csr%ISOPEN) THEN
1283 CLOSE lock_csr;
1284 END IF;
1285 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1286 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1287 END;
1288
1289 IF ( l_row_notfound ) THEN
1290 OPEN lchk_csr(p_ptq_rec);
1291 FETCH lchk_csr INTO lc_object_version_number;
1292 lc_row_notfound := lchk_csr%NOTFOUND;
1293 CLOSE lchk_csr;
1294 END IF;
1295 IF (lc_row_notfound) THEN
1296 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1297 RAISE OKC_API.G_EXCEPTION_ERROR;
1298 ELSIF lc_object_version_number > p_ptq_rec.object_version_number THEN
1299 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1300 RAISE OKC_API.G_EXCEPTION_ERROR;
1301 ELSIF lc_object_version_number <> p_ptq_rec.object_version_number THEN
1302 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1303 RAISE OKC_API.G_EXCEPTION_ERROR;
1304 ELSIF lc_object_version_number = -1 THEN
1305 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1306 RAISE OKC_API.G_EXCEPTION_ERROR;
1307 END IF;
1308 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1309 EXCEPTION
1310 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1311 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1312 (
1313 l_api_name,
1314 G_PKG_NAME,
1315 'OKC_API.G_RET_STS_ERROR',
1316 x_msg_count,
1317 x_msg_data,
1318 '_PVT'
1319 );
1320 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1321 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1322 (
1323 l_api_name,
1324 G_PKG_NAME,
1325 'OKC_API.G_RET_STS_UNEXP_ERROR',
1326 x_msg_count,
1327 x_msg_data,
1328 '_PVT'
1329 );
1330 WHEN OTHERS THEN
1331 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1332 (
1333 l_api_name,
1334 G_PKG_NAME,
1335 'OTHERS',
1336 x_msg_count,
1337 x_msg_data,
1338 '_PVT'
1339 );
1340 END lock_row;
1341 -------------------------------------
1342 -- lock_row for:OKL_PTL_QUALITYS_V --
1343 -------------------------------------
1344 PROCEDURE lock_row(
1345 p_api_version IN NUMBER,
1346 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1347 x_return_status OUT NOCOPY VARCHAR2,
1348 x_msg_count OUT NOCOPY NUMBER,
1349 x_msg_data OUT NOCOPY VARCHAR2,
1350 p_ptqv_rec IN ptqv_rec_type) IS
1351
1352 l_api_version CONSTANT NUMBER := 1;
1353 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1354 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1355 l_ptq_rec ptq_rec_type;
1356 BEGIN
1357 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1358 G_PKG_NAME,
1359 p_init_msg_list,
1360 l_api_version,
1361 p_api_version,
1362 '_PVT',
1363 x_return_status);
1364 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1365 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1366 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1367 RAISE OKC_API.G_EXCEPTION_ERROR;
1368 END IF;
1369 --------------------------------------
1370 -- Move VIEW record to "Child" records
1371 --------------------------------------
1372 migrate(p_ptqv_rec, l_ptq_rec);
1373 --------------------------------------------
1374 -- Call the LOCK_ROW for each child record
1375 --------------------------------------------
1376 lock_row(
1377 p_init_msg_list,
1378 x_return_status,
1379 x_msg_count,
1380 x_msg_data,
1381 l_ptq_rec
1382 );
1383 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1384 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1385 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1386 RAISE OKC_API.G_EXCEPTION_ERROR;
1387 END IF;
1388 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1389 EXCEPTION
1390 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1391 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1392 (
1393 l_api_name,
1394 G_PKG_NAME,
1395 'OKC_API.G_RET_STS_ERROR',
1396 x_msg_count,
1397 x_msg_data,
1398 '_PVT'
1399 );
1400 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1401 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1402 (
1403 l_api_name,
1404 G_PKG_NAME,
1405 'OKC_API.G_RET_STS_UNEXP_ERROR',
1406 x_msg_count,
1407 x_msg_data,
1408 '_PVT'
1409 );
1410 WHEN OTHERS THEN
1411 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1412 (
1413 l_api_name,
1414 G_PKG_NAME,
1415 'OTHERS',
1416 x_msg_count,
1417 x_msg_data,
1418 '_PVT'
1419 );
1420 END lock_row;
1421 --------------------------------------
1422 -- PL/SQL TBL lock_row for:PTQV_TBL --
1423 --------------------------------------
1424 PROCEDURE lock_row(
1425 p_api_version IN NUMBER,
1426 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1427 x_return_status OUT NOCOPY VARCHAR2,
1428 x_msg_count OUT NOCOPY NUMBER,
1429 x_msg_data OUT NOCOPY VARCHAR2,
1430 p_ptqv_tbl IN ptqv_tbl_type) IS
1431
1432 l_api_version CONSTANT NUMBER := 1;
1433 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1434 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1435 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1436 i NUMBER := 0;
1437 BEGIN
1438 OKC_API.init_msg_list(p_init_msg_list);
1439 -- Make sure PL/SQL table has records in it before passing
1440 IF (p_ptqv_tbl.COUNT > 0) THEN
1441 i := p_ptqv_tbl.FIRST;
1442 LOOP
1443 lock_row (
1444 p_api_version => p_api_version,
1445 p_init_msg_list => OKC_API.G_FALSE,
1446 x_return_status => x_return_status,
1447 x_msg_count => x_msg_count,
1448 x_msg_data => x_msg_data,
1449 p_ptqv_rec => p_ptqv_tbl(i));
1450 -- TCHGS: Store the highest degree of error
1451 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1452 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1453 l_overall_status := x_return_status;
1454 END IF;
1455 END IF;
1456 EXIT WHEN (i = p_ptqv_tbl.LAST);
1457 i := p_ptqv_tbl.NEXT(i);
1458 END LOOP;
1459 --TCHGS: return overall status
1460 x_return_status := l_overall_status;
1461 END IF;
1462 EXCEPTION
1463 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1464 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1465 (
1466 l_api_name,
1467 G_PKG_NAME,
1468 'OKC_API.G_RET_STS_ERROR',
1469 x_msg_count,
1470 x_msg_data,
1471 '_PVT'
1472 );
1473 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1474 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1475 (
1476 l_api_name,
1477 G_PKG_NAME,
1478 'OKC_API.G_RET_STS_UNEXP_ERROR',
1479 x_msg_count,
1480 x_msg_data,
1481 '_PVT'
1482 );
1483 WHEN OTHERS THEN
1484 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1485 (
1486 l_api_name,
1487 G_PKG_NAME,
1488 'OTHERS',
1489 x_msg_count,
1490 x_msg_data,
1491 '_PVT'
1492 );
1493 END lock_row;
1494
1495 ---------------------------------------------------------------------------
1496 -- PROCEDURE update_row
1497 ---------------------------------------------------------------------------
1498 -------------------------------------
1499 -- update_row for:OKL_PTL_QUALITYS --
1500 -------------------------------------
1501 PROCEDURE update_row(
1502 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1503 x_return_status OUT NOCOPY VARCHAR2,
1504 x_msg_count OUT NOCOPY NUMBER,
1505 x_msg_data OUT NOCOPY VARCHAR2,
1506 p_ptq_rec IN ptq_rec_type,
1507 x_ptq_rec OUT NOCOPY ptq_rec_type) IS
1508
1509 l_api_version CONSTANT NUMBER := 1;
1510 l_api_name CONSTANT VARCHAR2(30) := 'QUALITYS_update_row';
1511 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1512 l_ptq_rec ptq_rec_type := p_ptq_rec;
1513 l_def_ptq_rec ptq_rec_type;
1514 l_row_notfound BOOLEAN := TRUE;
1515 ----------------------------------
1516 -- FUNCTION populate_new_record --
1517 ----------------------------------
1518 FUNCTION populate_new_record (
1519 p_ptq_rec IN ptq_rec_type,
1520 x_ptq_rec OUT NOCOPY ptq_rec_type
1521 ) RETURN VARCHAR2 IS
1522 l_ptq_rec ptq_rec_type;
1523 l_row_notfound BOOLEAN := TRUE;
1524 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1525 BEGIN
1526 x_ptq_rec := p_ptq_rec;
1527 -- Get current database values
1528 l_ptq_rec := get_rec(p_ptq_rec, l_row_notfound);
1529 IF (l_row_notfound) THEN
1530 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1531 END IF;
1532 IF (x_ptq_rec.id = OKC_API.G_MISS_NUM)
1533 THEN
1534 x_ptq_rec.id := l_ptq_rec.id;
1535 END IF;
1536 IF (x_ptq_rec.name = OKC_API.G_MISS_CHAR)
1537 THEN
1538 x_ptq_rec.name := l_ptq_rec.name;
1539 END IF;
1540 IF (x_ptq_rec.object_version_number = OKC_API.G_MISS_NUM)
1541 THEN
1542 x_ptq_rec.object_version_number := l_ptq_rec.object_version_number;
1543 END IF;
1544 IF (x_ptq_rec.description = OKC_API.G_MISS_CHAR)
1545 THEN
1546 x_ptq_rec.description := l_ptq_rec.description;
1547 END IF;
1548 IF (x_ptq_rec.from_date = OKC_API.G_MISS_DATE)
1549 THEN
1550 x_ptq_rec.from_date := l_ptq_rec.from_date;
1551 END IF;
1552 IF (x_ptq_rec.TO_DATE = OKC_API.G_MISS_DATE)
1553 THEN
1554 x_ptq_rec.TO_DATE := l_ptq_rec.TO_DATE;
1555 END IF;
1556 IF (x_ptq_rec.created_by = OKC_API.G_MISS_NUM)
1557 THEN
1558 x_ptq_rec.created_by := l_ptq_rec.created_by;
1559 END IF;
1560 IF (x_ptq_rec.creation_date = OKC_API.G_MISS_DATE)
1561 THEN
1562 x_ptq_rec.creation_date := l_ptq_rec.creation_date;
1563 END IF;
1564 IF (x_ptq_rec.last_updated_by = OKC_API.G_MISS_NUM)
1565 THEN
1566 x_ptq_rec.last_updated_by := l_ptq_rec.last_updated_by;
1567 END IF;
1568 IF (x_ptq_rec.last_update_date = OKC_API.G_MISS_DATE)
1569 THEN
1570 x_ptq_rec.last_update_date := l_ptq_rec.last_update_date;
1571 END IF;
1572 IF (x_ptq_rec.last_update_login = OKC_API.G_MISS_NUM)
1573 THEN
1574 x_ptq_rec.last_update_login := l_ptq_rec.last_update_login;
1575 END IF;
1576 RETURN(l_return_status);
1577 END populate_new_record;
1578 -----------------------------------------
1579 -- Set_Attributes for:OKL_PTL_QUALITYS --
1580 -----------------------------------------
1581 FUNCTION Set_Attributes (
1582 p_ptq_rec IN ptq_rec_type,
1583 x_ptq_rec OUT NOCOPY ptq_rec_type
1584 ) RETURN VARCHAR2 IS
1585 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1586 BEGIN
1587 x_ptq_rec := p_ptq_rec;
1588 RETURN(l_return_status);
1589 END Set_Attributes;
1590 BEGIN
1591 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1592 p_init_msg_list,
1593 '_PVT',
1594 x_return_status);
1595 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1596 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1597 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1598 RAISE OKC_API.G_EXCEPTION_ERROR;
1599 END IF;
1600 --- Setting item attributes
1601 l_return_status := Set_Attributes(
1602 p_ptq_rec, -- IN
1603 l_ptq_rec); -- OUT
1604 --- If any errors happen abort API
1605 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1606 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1607 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1608 RAISE OKC_API.G_EXCEPTION_ERROR;
1609 END IF;
1610 l_return_status := populate_new_record(l_ptq_rec, l_def_ptq_rec);
1611 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1614 RAISE OKC_API.G_EXCEPTION_ERROR;
1615 END IF;
1616 UPDATE OKL_PTL_QUALITYS
1617 SET NAME = l_def_ptq_rec.name,
1618 OBJECT_VERSION_NUMBER = l_def_ptq_rec.object_version_number,
1619 DESCRIPTION = l_def_ptq_rec.description,
1620 FROM_DATE = l_def_ptq_rec.from_date,
1621 TO_DATE = l_def_ptq_rec.TO_DATE,
1622 CREATED_BY = l_def_ptq_rec.created_by,
1623 CREATION_DATE = l_def_ptq_rec.creation_date,
1624 LAST_UPDATED_BY = l_def_ptq_rec.last_updated_by,
1625 LAST_UPDATE_DATE = l_def_ptq_rec.last_update_date,
1626 LAST_UPDATE_LOGIN = l_def_ptq_rec.last_update_login
1627 WHERE ID = l_def_ptq_rec.id;
1628
1629 x_ptq_rec := l_def_ptq_rec;
1630 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1631 EXCEPTION
1632 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1633 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1634 (
1635 l_api_name,
1636 G_PKG_NAME,
1637 'OKC_API.G_RET_STS_ERROR',
1638 x_msg_count,
1639 x_msg_data,
1640 '_PVT'
1641 );
1642 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1643 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1644 (
1645 l_api_name,
1646 G_PKG_NAME,
1647 'OKC_API.G_RET_STS_UNEXP_ERROR',
1648 x_msg_count,
1649 x_msg_data,
1650 '_PVT'
1651 );
1652 WHEN OTHERS THEN
1653 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1654 (
1655 l_api_name,
1656 G_PKG_NAME,
1657 'OTHERS',
1658 x_msg_count,
1659 x_msg_data,
1660 '_PVT'
1661 );
1662 END update_row;
1663 ---------------------------------------
1664 -- update_row for:OKL_PTL_QUALITYS_V --
1665 ---------------------------------------
1666 PROCEDURE update_row(
1667 p_api_version IN NUMBER,
1668 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1669 x_return_status OUT NOCOPY VARCHAR2,
1670 x_msg_count OUT NOCOPY NUMBER,
1671 x_msg_data OUT NOCOPY VARCHAR2,
1672 p_ptqv_rec IN ptqv_rec_type,
1673 x_ptqv_rec OUT NOCOPY ptqv_rec_type) IS
1674
1675 l_api_version CONSTANT NUMBER := 1;
1676 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1677 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1678 l_ptqv_rec ptqv_rec_type := p_ptqv_rec;
1679 l_def_ptqv_rec ptqv_rec_type;
1680 l_ptq_rec ptq_rec_type;
1681 lx_ptq_rec ptq_rec_type;
1682 -------------------------------
1683 -- FUNCTION fill_who_columns --
1684 -------------------------------
1685 FUNCTION fill_who_columns (
1686 p_ptqv_rec IN ptqv_rec_type
1687 ) RETURN ptqv_rec_type IS
1688 l_ptqv_rec ptqv_rec_type := p_ptqv_rec;
1689 BEGIN
1690 l_ptqv_rec.LAST_UPDATE_DATE := SYSDATE;
1691 l_ptqv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1692 l_ptqv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1693 RETURN(l_ptqv_rec);
1694 END fill_who_columns;
1695 ----------------------------------
1696 -- FUNCTION populate_new_record --
1697 ----------------------------------
1698 FUNCTION populate_new_record (
1699 p_ptqv_rec IN ptqv_rec_type,
1700 x_ptqv_rec OUT NOCOPY ptqv_rec_type
1701 ) RETURN VARCHAR2 IS
1702 l_ptqv_rec ptqv_rec_type;
1703 l_row_notfound BOOLEAN := TRUE;
1704 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1705 BEGIN
1706 x_ptqv_rec := p_ptqv_rec;
1707 -- Get current database values
1708 l_ptqv_rec := get_rec(p_ptqv_rec, l_row_notfound);
1709 IF (l_row_notfound) THEN
1710 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1711 END IF;
1712 IF (x_ptqv_rec.id = OKC_API.G_MISS_NUM)
1713 THEN
1714 x_ptqv_rec.id := l_ptqv_rec.id;
1715 END IF;
1716 IF (x_ptqv_rec.object_version_number = OKC_API.G_MISS_NUM)
1717 THEN
1718 x_ptqv_rec.object_version_number := l_ptqv_rec.object_version_number;
1719 END IF;
1720 IF (x_ptqv_rec.name = OKC_API.G_MISS_CHAR)
1721 THEN
1722 x_ptqv_rec.name := l_ptqv_rec.name;
1723 END IF;
1724 IF (x_ptqv_rec.description = OKC_API.G_MISS_CHAR)
1725 THEN
1726 x_ptqv_rec.description := l_ptqv_rec.description;
1727 END IF;
1728 IF (x_ptqv_rec.from_date = OKC_API.G_MISS_DATE)
1729 THEN
1730 x_ptqv_rec.from_date := l_ptqv_rec.from_date;
1731 END IF;
1732 IF (x_ptqv_rec.TO_DATE = OKC_API.G_MISS_DATE)
1733 THEN
1734 x_ptqv_rec.TO_DATE := l_ptqv_rec.TO_DATE;
1735 END IF;
1736 IF (x_ptqv_rec.created_by = OKC_API.G_MISS_NUM)
1737 THEN
1738 x_ptqv_rec.created_by := l_ptqv_rec.created_by;
1739 END IF;
1740 IF (x_ptqv_rec.creation_date = OKC_API.G_MISS_DATE)
1741 THEN
1742 x_ptqv_rec.creation_date := l_ptqv_rec.creation_date;
1743 END IF;
1744 IF (x_ptqv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1745 THEN
1746 x_ptqv_rec.last_updated_by := l_ptqv_rec.last_updated_by;
1747 END IF;
1748 IF (x_ptqv_rec.last_update_date = OKC_API.G_MISS_DATE)
1749 THEN
1750 x_ptqv_rec.last_update_date := l_ptqv_rec.last_update_date;
1751 END IF;
1752 IF (x_ptqv_rec.last_update_login = OKC_API.G_MISS_NUM)
1753 THEN
1754 x_ptqv_rec.last_update_login := l_ptqv_rec.last_update_login;
1755 END IF;
1756 RETURN(l_return_status);
1757 END populate_new_record;
1758 -------------------------------------------
1759 -- Set_Attributes for:OKL_PTL_QUALITYS_V --
1760 -------------------------------------------
1761 FUNCTION Set_Attributes (
1762 p_ptqv_rec IN ptqv_rec_type,
1763 x_ptqv_rec OUT NOCOPY ptqv_rec_type
1764 ) RETURN VARCHAR2 IS
1765 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1766 BEGIN
1767 x_ptqv_rec := p_ptqv_rec;
1768 x_ptqv_rec.OBJECT_VERSION_NUMBER := NVL(x_ptqv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1769 RETURN(l_return_status);
1770 END Set_Attributes;
1771 BEGIN
1772 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1773 G_PKG_NAME,
1774 p_init_msg_list,
1775 l_api_version,
1776 p_api_version,
1777 '_PVT',
1778 x_return_status);
1779 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1780 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1781 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1782 RAISE OKC_API.G_EXCEPTION_ERROR;
1783 END IF;
1784 --- Setting item attributes
1785 l_return_status := Set_Attributes(
1786 p_ptqv_rec, -- IN
1787 l_ptqv_rec); -- OUT
1788 --- If any errors happen abort API
1789 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1791 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1792 RAISE OKC_API.G_EXCEPTION_ERROR;
1793 END IF;
1794 l_return_status := populate_new_record(l_ptqv_rec, l_def_ptqv_rec);
1795 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1796 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1797 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1798 RAISE OKC_API.G_EXCEPTION_ERROR;
1799 END IF;
1800 l_def_ptqv_rec := fill_who_columns(l_def_ptqv_rec);
1801 --- Validate all non-missing attributes (Item Level Validation)
1802 l_return_status := Validate_Attributes(l_def_ptqv_rec);
1803 --- If any errors happen abort API
1804 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1805 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1806 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1807 RAISE OKC_API.G_EXCEPTION_ERROR;
1808 END IF;
1809 l_return_status := Validate_Record(l_def_ptqv_rec);
1810 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1811 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1812 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1813 RAISE OKC_API.G_EXCEPTION_ERROR;
1814 END IF;
1815
1816 --------------------------------------
1817 -- Move VIEW record to "Child" records
1818 --------------------------------------
1819 migrate(l_def_ptqv_rec, l_ptq_rec);
1820 --------------------------------------------
1821 -- Call the UPDATE_ROW for each child record
1822 --------------------------------------------
1823 update_row(
1824 p_init_msg_list,
1825 x_return_status,
1826 x_msg_count,
1827 x_msg_data,
1828 l_ptq_rec,
1829 lx_ptq_rec
1830 );
1831 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1832 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1833 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1834 RAISE OKC_API.G_EXCEPTION_ERROR;
1835 END IF;
1836 migrate(lx_ptq_rec, l_def_ptqv_rec);
1837 x_ptqv_rec := l_def_ptqv_rec;
1838 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1839 EXCEPTION
1840 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1841 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1842 (
1843 l_api_name,
1844 G_PKG_NAME,
1845 'OKC_API.G_RET_STS_ERROR',
1846 x_msg_count,
1847 x_msg_data,
1848 '_PVT'
1849 );
1850 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1851 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1852 (
1853 l_api_name,
1854 G_PKG_NAME,
1855 'OKC_API.G_RET_STS_UNEXP_ERROR',
1856 x_msg_count,
1857 x_msg_data,
1858 '_PVT'
1859 );
1860 WHEN OTHERS THEN
1861 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1862 (
1863 l_api_name,
1864 G_PKG_NAME,
1865 'OTHERS',
1866 x_msg_count,
1867 x_msg_data,
1868 '_PVT'
1869 );
1870 END update_row;
1871 ----------------------------------------
1872 -- PL/SQL TBL update_row for:PTQV_TBL --
1873 ----------------------------------------
1874 PROCEDURE update_row(
1875 p_api_version IN NUMBER,
1876 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1877 x_return_status OUT NOCOPY VARCHAR2,
1878 x_msg_count OUT NOCOPY NUMBER,
1879 x_msg_data OUT NOCOPY VARCHAR2,
1880 p_ptqv_tbl IN ptqv_tbl_type,
1881 x_ptqv_tbl OUT NOCOPY ptqv_tbl_type) IS
1882
1883 l_api_version CONSTANT NUMBER := 1;
1884 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1885 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1886 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1887 i NUMBER := 0;
1888 BEGIN
1889 OKC_API.init_msg_list(p_init_msg_list);
1890 -- Make sure PL/SQL table has records in it before passing
1891 IF (p_ptqv_tbl.COUNT > 0) THEN
1892 i := p_ptqv_tbl.FIRST;
1893 LOOP
1894 update_row (
1895 p_api_version => p_api_version,
1896 p_init_msg_list => OKC_API.G_FALSE,
1897 x_return_status => x_return_status,
1898 x_msg_count => x_msg_count,
1899 x_msg_data => x_msg_data,
1900 p_ptqv_rec => p_ptqv_tbl(i),
1901 x_ptqv_rec => x_ptqv_tbl(i));
1902 -- TCHGS: Store the highest degree of error
1903 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1904 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1905 l_overall_status := x_return_status;
1906 END IF;
1907 END IF;
1908 EXIT WHEN (i = p_ptqv_tbl.LAST);
1909 i := p_ptqv_tbl.NEXT(i);
1910 END LOOP;
1911 --TCHGS: return overall status
1912 x_return_status := l_overall_status;
1913 END IF;
1914 EXCEPTION
1915 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1916 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1917 (
1918 l_api_name,
1919 G_PKG_NAME,
1920 'OKC_API.G_RET_STS_ERROR',
1921 x_msg_count,
1922 x_msg_data,
1923 '_PVT'
1924 );
1925 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1926 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1927 (
1928 l_api_name,
1929 G_PKG_NAME,
1930 'OKC_API.G_RET_STS_UNEXP_ERROR',
1931 x_msg_count,
1932 x_msg_data,
1933 '_PVT'
1934 );
1935 WHEN OTHERS THEN
1936 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1937 (
1938 l_api_name,
1939 G_PKG_NAME,
1940 'OTHERS',
1941 x_msg_count,
1942 x_msg_data,
1943 '_PVT'
1944 );
1945 END update_row;
1946
1947 ---------------------------------------------------------------------------
1948 -- PROCEDURE delete_row
1949 ---------------------------------------------------------------------------
1950 -------------------------------------
1951 -- delete_row for:OKL_PTL_QUALITYS --
1952 -------------------------------------
1953 PROCEDURE delete_row(
1954 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1955 x_return_status OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER,
1957 x_msg_data OUT NOCOPY VARCHAR2,
1958 p_ptq_rec IN ptq_rec_type) IS
1959
1960 l_api_version CONSTANT NUMBER := 1;
1961 l_api_name CONSTANT VARCHAR2(30) := 'QUALITYS_delete_row';
1962 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1963 l_ptq_rec ptq_rec_type:= p_ptq_rec;
1964 l_row_notfound BOOLEAN := TRUE;
1965 BEGIN
1966 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1967 p_init_msg_list,
1968 '_PVT',
1969 x_return_status);
1970 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1971 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1972 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1973 RAISE OKC_API.G_EXCEPTION_ERROR;
1974 END IF;
1975 DELETE FROM OKL_PTL_QUALITYS
1976 WHERE ID = l_ptq_rec.id;
1977
1978 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1979 EXCEPTION
1980 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1981 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1982 (
1983 l_api_name,
1984 G_PKG_NAME,
1985 'OKC_API.G_RET_STS_ERROR',
1986 x_msg_count,
1987 x_msg_data,
1988 '_PVT'
1989 );
1990 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1991 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1992 (
1993 l_api_name,
1994 G_PKG_NAME,
1995 'OKC_API.G_RET_STS_UNEXP_ERROR',
1996 x_msg_count,
1997 x_msg_data,
1998 '_PVT'
1999 );
2000 WHEN OTHERS THEN
2001 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2002 (
2003 l_api_name,
2004 G_PKG_NAME,
2005 'OTHERS',
2006 x_msg_count,
2007 x_msg_data,
2008 '_PVT'
2009 );
2010 END delete_row;
2011 ---------------------------------------
2012 -- delete_row for:OKL_PTL_QUALITYS_V --
2013 ---------------------------------------
2014 PROCEDURE delete_row(
2015 p_api_version IN NUMBER,
2016 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2017 x_return_status OUT NOCOPY VARCHAR2,
2018 x_msg_count OUT NOCOPY NUMBER,
2019 x_msg_data OUT NOCOPY VARCHAR2,
2020 p_ptqv_rec IN ptqv_rec_type) IS
2021
2022 l_api_version CONSTANT NUMBER := 1;
2023 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2024 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2025 l_ptqv_rec ptqv_rec_type := p_ptqv_rec;
2026 l_ptq_rec ptq_rec_type;
2027 BEGIN
2028 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2029 G_PKG_NAME,
2030 p_init_msg_list,
2031 l_api_version,
2032 p_api_version,
2033 '_PVT',
2034 x_return_status);
2035 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2036 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2037 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2038 RAISE OKC_API.G_EXCEPTION_ERROR;
2039 END IF;
2040 --------------------------------------
2041 -- Move VIEW record to "Child" records
2042 --------------------------------------
2043 migrate(l_ptqv_rec, l_ptq_rec);
2044 --------------------------------------------
2045 -- Call the DELETE_ROW for each child record
2046 --------------------------------------------
2047 delete_row(
2048 p_init_msg_list,
2049 x_return_status,
2050 x_msg_count,
2051 x_msg_data,
2052 l_ptq_rec
2053 );
2054 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2056 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2057 RAISE OKC_API.G_EXCEPTION_ERROR;
2058 END IF;
2059 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2060 EXCEPTION
2061 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2062 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2063 (
2064 l_api_name,
2065 G_PKG_NAME,
2066 'OKC_API.G_RET_STS_ERROR',
2067 x_msg_count,
2068 x_msg_data,
2069 '_PVT'
2070 );
2071 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2072 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2073 (
2074 l_api_name,
2075 G_PKG_NAME,
2076 'OKC_API.G_RET_STS_UNEXP_ERROR',
2077 x_msg_count,
2078 x_msg_data,
2079 '_PVT'
2080 );
2081 WHEN OTHERS THEN
2082 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2083 (
2084 l_api_name,
2085 G_PKG_NAME,
2086 'OTHERS',
2087 x_msg_count,
2088 x_msg_data,
2089 '_PVT'
2090 );
2091 END delete_row;
2092 ----------------------------------------
2093 -- PL/SQL TBL delete_row for:PTQV_TBL --
2094 ----------------------------------------
2095 PROCEDURE delete_row(
2096 p_api_version IN NUMBER,
2097 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2098 x_return_status OUT NOCOPY VARCHAR2,
2099 x_msg_count OUT NOCOPY NUMBER,
2100 x_msg_data OUT NOCOPY VARCHAR2,
2101 p_ptqv_tbl IN ptqv_tbl_type) IS
2102
2103 l_api_version CONSTANT NUMBER := 1;
2104 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2105 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2106 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
2107 i NUMBER := 0;
2108 BEGIN
2109 OKC_API.init_msg_list(p_init_msg_list);
2110 -- Make sure PL/SQL table has records in it before passing
2111 IF (p_ptqv_tbl.COUNT > 0) THEN
2112 i := p_ptqv_tbl.FIRST;
2113 LOOP
2114 delete_row (
2115 p_api_version => p_api_version,
2116 p_init_msg_list => OKC_API.G_FALSE,
2117 x_return_status => x_return_status,
2118 x_msg_count => x_msg_count,
2119 x_msg_data => x_msg_data,
2120 p_ptqv_rec => p_ptqv_tbl(i));
2121 -- TCHGS: Store the highest degree of error
2122 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2123 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2124 l_overall_status := x_return_status;
2125 END IF;
2126 END IF;
2127 EXIT WHEN (i = p_ptqv_tbl.LAST);
2128 i := p_ptqv_tbl.NEXT(i);
2129 END LOOP;
2130 --TCHGS: return overall status
2131 x_return_status := l_overall_status;
2132 END IF;
2133 EXCEPTION
2134 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2135 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2136 (
2137 l_api_name,
2138 G_PKG_NAME,
2139 'OKC_API.G_RET_STS_ERROR',
2140 x_msg_count,
2141 x_msg_data,
2142 '_PVT'
2143 );
2144 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2145 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2146 (
2147 l_api_name,
2148 G_PKG_NAME,
2149 'OKC_API.G_RET_STS_UNEXP_ERROR',
2150 x_msg_count,
2151 x_msg_data,
2152 '_PVT'
2153 );
2154 WHEN OTHERS THEN
2155 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2156 (
2157 l_api_name,
2158 G_PKG_NAME,
2159 'OTHERS',
2160 x_msg_count,
2161 x_msg_data,
2162 '_PVT'
2163 );
2164 END delete_row;
2165 END OKL_PTQ_PVT;