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