1 package body GMD_QC_TEST_VALUES_PVT as
2 /* $Header: GMDVTVLB.pls 115.4 2002/12/03 17:13:24 cnagarba noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy ROWID,
5 X_TEST_VALUE_ID in out nocopy NUMBER,
6 X_TEST_ID in NUMBER,
7 X_MIN_NUM in NUMBER ,
8 X_MAX_NUM in NUMBER ,
9 X_VALUE_CHAR in VARCHAR2 ,
10 X_TEXT_RANGE_SEQ in NUMBER ,
11 X_EXPRESSION_REF_TEST_ID in NUMBER ,
12 X_TEXT_CODE in NUMBER ,
13 X_ATTRIBUTE_CATEGORY in VARCHAR2 ,
14 X_ATTRIBUTE1 in VARCHAR2 ,
15 X_ATTRIBUTE2 in VARCHAR2 ,
16 X_ATTRIBUTE3 in VARCHAR2 ,
17 X_ATTRIBUTE4 in VARCHAR2 ,
18 X_ATTRIBUTE5 in VARCHAR2 ,
19 X_ATTRIBUTE6 in VARCHAR2 ,
20 X_ATTRIBUTE7 in VARCHAR2 ,
21 X_ATTRIBUTE8 in VARCHAR2 ,
22 X_ATTRIBUTE9 in VARCHAR2 ,
23 X_ATTRIBUTE10 in VARCHAR2 ,
24 X_ATTRIBUTE11 in VARCHAR2 ,
25 X_ATTRIBUTE12 in VARCHAR2 ,
26 X_ATTRIBUTE13 in VARCHAR2 ,
27 X_ATTRIBUTE14 in VARCHAR2 ,
28 X_ATTRIBUTE15 in VARCHAR2 ,
29 X_ATTRIBUTE16 in VARCHAR2 ,
30 X_ATTRIBUTE17 in VARCHAR2 ,
31 X_ATTRIBUTE18 in VARCHAR2 ,
32 X_ATTRIBUTE19 in VARCHAR2 ,
33 X_ATTRIBUTE20 in VARCHAR2 ,
34 X_ATTRIBUTE21 in VARCHAR2 ,
35 X_ATTRIBUTE22 in VARCHAR2 ,
36 X_ATTRIBUTE23 in VARCHAR2 ,
37 X_ATTRIBUTE24 in VARCHAR2 ,
38 X_ATTRIBUTE25 in VARCHAR2 ,
39 X_ATTRIBUTE26 in VARCHAR2 ,
40 X_ATTRIBUTE27 in VARCHAR2 ,
41 X_ATTRIBUTE28 in VARCHAR2 ,
42 X_ATTRIBUTE29 in VARCHAR2 ,
43 X_ATTRIBUTE30 in VARCHAR2 ,
44 X_DISPLAY_LABEL_NUMERIC_RANGE in VARCHAR2 ,
45 X_TEST_VALUE_DESC in VARCHAR2 ,
46 X_CREATION_DATE in DATE ,
47 X_CREATED_BY in NUMBER ,
48 X_LAST_UPDATE_DATE in DATE ,
49 X_LAST_UPDATED_BY in NUMBER ,
50 X_LAST_UPDATE_LOGIN in NUMBER
51 ) is
52 cursor C is select ROWID from GMD_QC_TEST_VALUES_B
53 where TEST_VALUE_ID = X_TEST_VALUE_ID
54 ;
55 begin
56
57 IF X_TEST_VALUE_ID IS NULL THEN
58 SELECT GMD_QC_TEST_VALUE_ID_S.NEXTVAL INTO X_TEST_VALUE_ID FROM DUAL;
59 END IF;
60
61 insert into GMD_QC_TEST_VALUES_B (
62 TEST_VALUE_ID,
63 TEST_ID,
64 MIN_NUM,
65 MAX_NUM,
66 VALUE_CHAR,
67 TEXT_RANGE_SEQ,
68 EXPRESSION_REF_TEST_ID,
69 TEXT_CODE,
70 ATTRIBUTE_CATEGORY,
71 ATTRIBUTE1,
72 ATTRIBUTE2,
73 ATTRIBUTE3,
74 ATTRIBUTE4,
75 ATTRIBUTE5,
76 ATTRIBUTE6,
77 ATTRIBUTE7,
78 ATTRIBUTE8,
79 ATTRIBUTE9,
80 ATTRIBUTE10,
81 ATTRIBUTE11,
82 ATTRIBUTE12,
83 ATTRIBUTE13,
84 ATTRIBUTE14,
85 ATTRIBUTE15,
86 ATTRIBUTE16,
87 ATTRIBUTE17,
88 ATTRIBUTE18,
89 ATTRIBUTE19,
90 ATTRIBUTE20,
91 ATTRIBUTE21,
92 ATTRIBUTE22,
93 ATTRIBUTE23,
94 ATTRIBUTE24,
95 ATTRIBUTE25,
96 ATTRIBUTE26,
97 ATTRIBUTE27,
98 ATTRIBUTE28,
99 ATTRIBUTE29,
100 ATTRIBUTE30,
101 CREATION_DATE,
102 CREATED_BY,
103 LAST_UPDATE_DATE,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_LOGIN
106 ) values (
107 X_TEST_VALUE_ID,
108 X_TEST_ID,
109 X_MIN_NUM,
110 X_MAX_NUM,
111 X_VALUE_CHAR,
112 X_TEXT_RANGE_SEQ,
113 X_EXPRESSION_REF_TEST_ID,
114 X_TEXT_CODE,
115 X_ATTRIBUTE_CATEGORY,
116 X_ATTRIBUTE1,
117 X_ATTRIBUTE2,
118 X_ATTRIBUTE3,
119 X_ATTRIBUTE4,
120 X_ATTRIBUTE5,
121 X_ATTRIBUTE6,
122 X_ATTRIBUTE7,
123 X_ATTRIBUTE8,
124 X_ATTRIBUTE9,
125 X_ATTRIBUTE10,
126 X_ATTRIBUTE11,
127 X_ATTRIBUTE12,
128 X_ATTRIBUTE13,
129 X_ATTRIBUTE14,
130 X_ATTRIBUTE15,
131 X_ATTRIBUTE16,
132 X_ATTRIBUTE17,
133 X_ATTRIBUTE18,
134 X_ATTRIBUTE19,
135 X_ATTRIBUTE20,
136 X_ATTRIBUTE21,
137 X_ATTRIBUTE22,
138 X_ATTRIBUTE23,
139 X_ATTRIBUTE24,
140 X_ATTRIBUTE25,
141 X_ATTRIBUTE26,
142 X_ATTRIBUTE27,
143 X_ATTRIBUTE28,
144 X_ATTRIBUTE29,
145 X_ATTRIBUTE30,
146 NVL(X_CREATION_DATE,SYSDATE),
147 NVL(X_CREATED_BY,FND_GLOBAL.USER_ID),
148 NVL(X_LAST_UPDATE_DATE,SYSDATE),
149 NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
150 NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
151 );
152
153 insert into GMD_QC_TEST_VALUES_TL (
154 TEST_VALUE_ID,
155 DISPLAY_LABEL_NUMERIC_RANGE,
156 TEST_VALUE_DESC,
157 CREATION_DATE,
158 CREATED_BY,
159 LAST_UPDATED_BY,
160 LAST_UPDATE_DATE,
161 LAST_UPDATE_LOGIN,
162 LANGUAGE,
163 SOURCE_LANG
164 ) select
165 X_TEST_VALUE_ID,
166 X_DISPLAY_LABEL_NUMERIC_RANGE,
167 X_TEST_VALUE_DESC,
168 NVL(X_CREATION_DATE,SYSDATE),
169 NVL(X_CREATED_BY,FND_GLOBAL.USER_ID),
170 NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
171 NVL(X_LAST_UPDATE_DATE,SYSDATE),
172 NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID),
173 L.LANGUAGE_CODE,
174 userenv('LANG')
175 from FND_LANGUAGES L
176 where L.INSTALLED_FLAG in ('I', 'B')
177 and not exists
178 (select NULL
179 from GMD_QC_TEST_VALUES_TL T
180 where T.TEST_VALUE_ID = X_TEST_VALUE_ID
181 and T.LANGUAGE = L.LANGUAGE_CODE);
182
183 open c;
184 fetch c into X_ROWID;
185 if (c%notfound) then
186 close c;
187 raise no_data_found;
188 end if;
189 close c;
190
191 end INSERT_ROW;
192
193 FUNCTION INSERT_ROW(p_qc_test_values_rec IN OUT NOCOPY GMD_QC_TEST_VALUES%ROWTYPE) RETURN BOOLEAN IS
194 l_rowid ROWID;
195 BEGIN
196 GMD_QC_TEST_VALUES_PVT.INSERT_ROW(
197 X_ROWID => l_rowid,
198 X_TEST_VALUE_ID => p_qc_test_values_rec.TEST_VALUE_ID,
199 X_TEST_ID => p_qc_test_values_rec.TEST_ID,
200 X_MIN_NUM => p_qc_test_values_rec.MIN_NUM,
201 X_MAX_NUM => p_qc_test_values_rec.MAX_NUM,
202 X_VALUE_CHAR => p_qc_test_values_rec.VALUE_CHAR,
203 X_TEXT_RANGE_SEQ => p_qc_test_values_rec.TEXT_RANGE_SEQ,
204 X_EXPRESSION_REF_TEST_ID => p_qc_test_values_rec.EXPRESSION_REF_TEST_ID,
205 X_TEXT_CODE => p_qc_test_values_rec.TEXT_CODE,
206 X_ATTRIBUTE_CATEGORY => p_qc_test_values_rec.ATTRIBUTE_CATEGORY,
207 X_ATTRIBUTE1 => p_qc_test_values_rec.ATTRIBUTE1,
208 X_ATTRIBUTE2 => p_qc_test_values_rec.ATTRIBUTE2,
209 X_ATTRIBUTE3 => p_qc_test_values_rec.ATTRIBUTE3,
210 X_ATTRIBUTE4 => p_qc_test_values_rec.ATTRIBUTE4,
211 X_ATTRIBUTE5 => p_qc_test_values_rec.ATTRIBUTE5,
212 X_ATTRIBUTE6 => p_qc_test_values_rec.ATTRIBUTE6,
213 X_ATTRIBUTE7 => p_qc_test_values_rec.ATTRIBUTE7,
214 X_ATTRIBUTE8 => p_qc_test_values_rec.ATTRIBUTE8,
215 X_ATTRIBUTE9 => p_qc_test_values_rec.ATTRIBUTE9,
216 X_ATTRIBUTE10 => p_qc_test_values_rec.ATTRIBUTE10,
217 X_ATTRIBUTE11 => p_qc_test_values_rec.ATTRIBUTE11,
218 X_ATTRIBUTE12 => p_qc_test_values_rec.ATTRIBUTE12,
219 X_ATTRIBUTE13 => p_qc_test_values_rec.ATTRIBUTE13,
220 X_ATTRIBUTE14 => p_qc_test_values_rec.ATTRIBUTE14,
221 X_ATTRIBUTE15 => p_qc_test_values_rec.ATTRIBUTE15,
222 X_ATTRIBUTE16 => p_qc_test_values_rec.ATTRIBUTE16,
223 X_ATTRIBUTE17 => p_qc_test_values_rec.ATTRIBUTE17,
224 X_ATTRIBUTE18 => p_qc_test_values_rec.ATTRIBUTE18,
225 X_ATTRIBUTE19 => p_qc_test_values_rec.ATTRIBUTE19,
226 X_ATTRIBUTE20 => p_qc_test_values_rec.ATTRIBUTE20,
227 X_ATTRIBUTE21 => p_qc_test_values_rec.ATTRIBUTE21,
228 X_ATTRIBUTE22 => p_qc_test_values_rec.ATTRIBUTE22,
229 X_ATTRIBUTE23 => p_qc_test_values_rec.ATTRIBUTE23,
230 X_ATTRIBUTE24 => p_qc_test_values_rec.ATTRIBUTE24,
231 X_ATTRIBUTE25 => p_qc_test_values_rec.ATTRIBUTE25,
232 X_ATTRIBUTE26 => p_qc_test_values_rec.ATTRIBUTE26,
233 X_ATTRIBUTE27 => p_qc_test_values_rec.ATTRIBUTE27,
234 X_ATTRIBUTE28 => p_qc_test_values_rec.ATTRIBUTE28,
235 X_ATTRIBUTE29 => p_qc_test_values_rec.ATTRIBUTE29,
236 X_ATTRIBUTE30 => p_qc_test_values_rec.ATTRIBUTE30,
237 X_DISPLAY_LABEL_NUMERIC_RANGE => p_qc_test_values_rec.DISPLAY_LABEL_NUMERIC_RANGE,
238 X_TEST_VALUE_DESC => p_qc_test_values_rec.TEST_VALUE_DESC,
239 X_CREATION_DATE => p_qc_test_values_rec.CREATION_DATE,
240 X_CREATED_BY => p_qc_test_values_rec.CREATED_BY,
241 X_LAST_UPDATE_DATE => p_qc_test_values_rec.LAST_UPDATE_DATE,
242 X_LAST_UPDATED_BY => p_qc_test_values_rec.LAST_UPDATED_BY,
243 X_LAST_UPDATE_LOGIN => p_qc_test_values_rec.LAST_UPDATE_LOGIN);
244
245 RETURN TRUE;
246
247 EXCEPTION WHEN OTHERS THEN
248 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QC_TEST_VALUES_PVT.INSERT_ROW','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
249 RETURN FALSE;
250 end INSERT_ROW;
251
252
253 procedure LOCK_ROW (
254 X_TEST_VALUE_ID in NUMBER,
255 X_TEST_ID in NUMBER,
256 X_MIN_NUM in NUMBER,
257 X_MAX_NUM in NUMBER,
258 X_VALUE_CHAR in VARCHAR2,
259 X_TEXT_RANGE_SEQ in NUMBER,
260 X_EXPRESSION_REF_TEST_ID in NUMBER,
261 X_TEXT_CODE in NUMBER,
262 X_ATTRIBUTE_CATEGORY in VARCHAR2,
263 X_ATTRIBUTE1 in VARCHAR2,
264 X_ATTRIBUTE2 in VARCHAR2,
265 X_ATTRIBUTE3 in VARCHAR2,
266 X_ATTRIBUTE4 in VARCHAR2,
267 X_ATTRIBUTE5 in VARCHAR2,
268 X_ATTRIBUTE6 in VARCHAR2,
269 X_ATTRIBUTE7 in VARCHAR2,
270 X_ATTRIBUTE8 in VARCHAR2,
271 X_ATTRIBUTE9 in VARCHAR2,
272 X_ATTRIBUTE10 in VARCHAR2,
273 X_ATTRIBUTE11 in VARCHAR2,
274 X_ATTRIBUTE12 in VARCHAR2,
275 X_ATTRIBUTE13 in VARCHAR2,
276 X_ATTRIBUTE14 in VARCHAR2,
277 X_ATTRIBUTE15 in VARCHAR2,
278 X_ATTRIBUTE16 in VARCHAR2,
279 X_ATTRIBUTE17 in VARCHAR2,
280 X_ATTRIBUTE18 in VARCHAR2,
281 X_ATTRIBUTE19 in VARCHAR2,
282 X_ATTRIBUTE20 in VARCHAR2,
283 X_ATTRIBUTE21 in VARCHAR2,
284 X_ATTRIBUTE22 in VARCHAR2,
285 X_ATTRIBUTE23 in VARCHAR2,
286 X_ATTRIBUTE24 in VARCHAR2,
287 X_ATTRIBUTE25 in VARCHAR2,
288 X_ATTRIBUTE26 in VARCHAR2,
289 X_ATTRIBUTE27 in VARCHAR2,
290 X_ATTRIBUTE28 in VARCHAR2,
291 X_ATTRIBUTE29 in VARCHAR2,
292 X_ATTRIBUTE30 in VARCHAR2,
293 X_DISPLAY_LABEL_NUMERIC_RANGE in VARCHAR2,
294 X_TEST_VALUE_DESC in VARCHAR2
295 ) is
296 cursor c is select
297 TEST_ID,
298 MIN_NUM,
299 MAX_NUM,
300 VALUE_CHAR,
301 TEXT_RANGE_SEQ,
302 EXPRESSION_REF_TEST_ID,
303 TEXT_CODE,
304 ATTRIBUTE_CATEGORY,
305 ATTRIBUTE1,
306 ATTRIBUTE2,
307 ATTRIBUTE3,
308 ATTRIBUTE4,
309 ATTRIBUTE5,
310 ATTRIBUTE6,
311 ATTRIBUTE7,
312 ATTRIBUTE8,
313 ATTRIBUTE9,
314 ATTRIBUTE10,
315 ATTRIBUTE11,
316 ATTRIBUTE12,
317 ATTRIBUTE13,
318 ATTRIBUTE14,
319 ATTRIBUTE15,
320 ATTRIBUTE16,
321 ATTRIBUTE17,
322 ATTRIBUTE18,
323 ATTRIBUTE19,
324 ATTRIBUTE20,
325 ATTRIBUTE21,
326 ATTRIBUTE22,
327 ATTRIBUTE23,
328 ATTRIBUTE24,
329 ATTRIBUTE25,
330 ATTRIBUTE26,
331 ATTRIBUTE27,
332 ATTRIBUTE28,
333 ATTRIBUTE29,
334 ATTRIBUTE30
335 from GMD_QC_TEST_VALUES_B
336 where TEST_VALUE_ID = X_TEST_VALUE_ID
337 for update of TEST_VALUE_ID nowait;
338 recinfo c%rowtype;
339
340 cursor c1 is select
341 DISPLAY_LABEL_NUMERIC_RANGE,
342 TEST_VALUE_DESC,
343 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
344 from GMD_QC_TEST_VALUES_TL
345 where TEST_VALUE_ID = X_TEST_VALUE_ID
346 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
347 for update of TEST_VALUE_ID nowait;
348 begin
349 open c;
350 fetch c into recinfo;
351 if (c%notfound) then
352 close c;
353 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
354 app_exception.raise_exception;
355 end if;
356 close c;
357 if ( (recinfo.TEST_ID = X_TEST_ID)
358 AND ((recinfo.MIN_NUM = X_MIN_NUM)
359 OR ((recinfo.MIN_NUM is null) AND (X_MIN_NUM is null)))
360 AND ((recinfo.MAX_NUM = X_MAX_NUM)
361 OR ((recinfo.MAX_NUM is null) AND (X_MAX_NUM is null)))
362 AND ((recinfo.VALUE_CHAR = X_VALUE_CHAR)
363 OR ((recinfo.VALUE_CHAR is null) AND (X_VALUE_CHAR is null)))
364 AND ((recinfo.TEXT_RANGE_SEQ = X_TEXT_RANGE_SEQ)
365 OR ((recinfo.TEXT_RANGE_SEQ is null) AND (X_TEXT_RANGE_SEQ is null)))
366 AND ((recinfo.EXPRESSION_REF_TEST_ID = X_EXPRESSION_REF_TEST_ID)
367 OR ((recinfo.EXPRESSION_REF_TEST_ID is null) AND (X_EXPRESSION_REF_TEST_ID is null)))
368 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
369 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
370 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
371 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
372 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
373 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
374 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
375 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
376 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
377 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
378 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
379 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
380 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
381 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
382 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
383 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
384 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
385 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
386 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
387 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
388 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
389 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
390 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
391 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
392 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
393 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
394 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
395 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
396 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
397 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
398 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
399 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
400 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
401 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
402 AND ((recinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
403 OR ((recinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
404 AND ((recinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
405 OR ((recinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
406 AND ((recinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
407 OR ((recinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
408 AND ((recinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
409 OR ((recinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
410 AND ((recinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
411 OR ((recinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
412 AND ((recinfo.ATTRIBUTE21 = X_ATTRIBUTE21)
413 OR ((recinfo.ATTRIBUTE21 is null) AND (X_ATTRIBUTE21 is null)))
414 AND ((recinfo.ATTRIBUTE22 = X_ATTRIBUTE22)
415 OR ((recinfo.ATTRIBUTE22 is null) AND (X_ATTRIBUTE22 is null)))
416 AND ((recinfo.ATTRIBUTE23 = X_ATTRIBUTE23)
417 OR ((recinfo.ATTRIBUTE23 is null) AND (X_ATTRIBUTE23 is null)))
418 AND ((recinfo.ATTRIBUTE24 = X_ATTRIBUTE24)
419 OR ((recinfo.ATTRIBUTE24 is null) AND (X_ATTRIBUTE24 is null)))
420 AND ((recinfo.ATTRIBUTE25 = X_ATTRIBUTE25)
421 OR ((recinfo.ATTRIBUTE25 is null) AND (X_ATTRIBUTE25 is null)))
422 AND ((recinfo.ATTRIBUTE26 = X_ATTRIBUTE26)
423 OR ((recinfo.ATTRIBUTE26 is null) AND (X_ATTRIBUTE26 is null)))
424 AND ((recinfo.ATTRIBUTE27 = X_ATTRIBUTE27)
425 OR ((recinfo.ATTRIBUTE27 is null) AND (X_ATTRIBUTE27 is null)))
426 AND ((recinfo.ATTRIBUTE28 = X_ATTRIBUTE28)
427 OR ((recinfo.ATTRIBUTE28 is null) AND (X_ATTRIBUTE28 is null)))
428 AND ((recinfo.ATTRIBUTE29 = X_ATTRIBUTE29)
429 OR ((recinfo.ATTRIBUTE29 is null) AND (X_ATTRIBUTE29 is null)))
430 AND ((recinfo.ATTRIBUTE30 = X_ATTRIBUTE30)
431 OR ((recinfo.ATTRIBUTE30 is null) AND (X_ATTRIBUTE30 is null)))
432 ) then
433 null;
434 else
435 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
436 app_exception.raise_exception;
437 end if;
438
439 for tlinfo in c1 loop
440 if (tlinfo.BASELANG = 'Y') then
441 if ( ((tlinfo.DISPLAY_LABEL_NUMERIC_RANGE = X_DISPLAY_LABEL_NUMERIC_RANGE)
442 OR ((tlinfo.DISPLAY_LABEL_NUMERIC_RANGE is null) AND (X_DISPLAY_LABEL_NUMERIC_RANGE is null)))
443 AND ((tlinfo.TEST_VALUE_DESC = X_TEST_VALUE_DESC)
444 OR ((tlinfo.TEST_VALUE_DESC is null) AND (X_TEST_VALUE_DESC is null)))
445 ) then
446 null;
447 else
448 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
449 app_exception.raise_exception;
450 end if;
451 end if;
452 end loop;
453 return;
454 end LOCK_ROW;
455
456
457 procedure UPDATE_ROW (
458 X_TEST_VALUE_ID in NUMBER,
459 X_TEST_ID in NUMBER,
460 X_MIN_NUM in NUMBER,
461 X_MAX_NUM in NUMBER,
462 X_VALUE_CHAR in VARCHAR2,
463 X_TEXT_RANGE_SEQ in NUMBER,
464 X_EXPRESSION_REF_TEST_ID in NUMBER,
465 X_TEXT_CODE in NUMBER,
466 X_ATTRIBUTE_CATEGORY in VARCHAR2,
467 X_ATTRIBUTE1 in VARCHAR2,
468 X_ATTRIBUTE2 in VARCHAR2,
469 X_ATTRIBUTE3 in VARCHAR2,
470 X_ATTRIBUTE4 in VARCHAR2,
471 X_ATTRIBUTE5 in VARCHAR2,
472 X_ATTRIBUTE6 in VARCHAR2,
473 X_ATTRIBUTE7 in VARCHAR2,
474 X_ATTRIBUTE8 in VARCHAR2,
475 X_ATTRIBUTE9 in VARCHAR2,
476 X_ATTRIBUTE10 in VARCHAR2,
477 X_ATTRIBUTE11 in VARCHAR2,
478 X_ATTRIBUTE12 in VARCHAR2,
479 X_ATTRIBUTE13 in VARCHAR2,
480 X_ATTRIBUTE14 in VARCHAR2,
481 X_ATTRIBUTE15 in VARCHAR2,
482 X_ATTRIBUTE16 in VARCHAR2,
483 X_ATTRIBUTE17 in VARCHAR2,
484 X_ATTRIBUTE18 in VARCHAR2,
485 X_ATTRIBUTE19 in VARCHAR2,
486 X_ATTRIBUTE20 in VARCHAR2,
487 X_ATTRIBUTE21 in VARCHAR2,
488 X_ATTRIBUTE22 in VARCHAR2,
489 X_ATTRIBUTE23 in VARCHAR2,
490 X_ATTRIBUTE24 in VARCHAR2,
491 X_ATTRIBUTE25 in VARCHAR2,
492 X_ATTRIBUTE26 in VARCHAR2,
493 X_ATTRIBUTE27 in VARCHAR2,
494 X_ATTRIBUTE28 in VARCHAR2,
495 X_ATTRIBUTE29 in VARCHAR2,
496 X_ATTRIBUTE30 in VARCHAR2,
497 X_DISPLAY_LABEL_NUMERIC_RANGE in VARCHAR2,
498 X_TEST_VALUE_DESC in VARCHAR2,
499 X_LAST_UPDATE_DATE in DATE,
500 X_LAST_UPDATED_BY in NUMBER,
501 X_LAST_UPDATE_LOGIN in NUMBER
502 ) is
503 begin
504 update GMD_QC_TEST_VALUES_B set
505 TEST_ID = X_TEST_ID,
506 MIN_NUM = X_MIN_NUM,
507 MAX_NUM = X_MAX_NUM,
508 VALUE_CHAR = X_VALUE_CHAR,
509 TEXT_RANGE_SEQ = X_TEXT_RANGE_SEQ,
510 EXPRESSION_REF_TEST_ID = X_EXPRESSION_REF_TEST_ID,
511 TEXT_CODE = X_TEXT_CODE,
512 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
513 ATTRIBUTE1 = X_ATTRIBUTE1,
514 ATTRIBUTE2 = X_ATTRIBUTE2,
515 ATTRIBUTE3 = X_ATTRIBUTE3,
516 ATTRIBUTE4 = X_ATTRIBUTE4,
517 ATTRIBUTE5 = X_ATTRIBUTE5,
518 ATTRIBUTE6 = X_ATTRIBUTE6,
519 ATTRIBUTE7 = X_ATTRIBUTE7,
520 ATTRIBUTE8 = X_ATTRIBUTE8,
521 ATTRIBUTE9 = X_ATTRIBUTE9,
522 ATTRIBUTE10 = X_ATTRIBUTE10,
523 ATTRIBUTE11 = X_ATTRIBUTE11,
524 ATTRIBUTE12 = X_ATTRIBUTE12,
525 ATTRIBUTE13 = X_ATTRIBUTE13,
526 ATTRIBUTE14 = X_ATTRIBUTE14,
527 ATTRIBUTE15 = X_ATTRIBUTE15,
528 ATTRIBUTE16 = X_ATTRIBUTE16,
529 ATTRIBUTE17 = X_ATTRIBUTE17,
530 ATTRIBUTE18 = X_ATTRIBUTE18,
531 ATTRIBUTE19 = X_ATTRIBUTE19,
532 ATTRIBUTE20 = X_ATTRIBUTE20,
533 ATTRIBUTE21 = X_ATTRIBUTE21,
534 ATTRIBUTE22 = X_ATTRIBUTE22,
535 ATTRIBUTE23 = X_ATTRIBUTE23,
536 ATTRIBUTE24 = X_ATTRIBUTE24,
537 ATTRIBUTE25 = X_ATTRIBUTE25,
538 ATTRIBUTE26 = X_ATTRIBUTE26,
539 ATTRIBUTE27 = X_ATTRIBUTE27,
540 ATTRIBUTE28 = X_ATTRIBUTE28,
541 ATTRIBUTE29 = X_ATTRIBUTE29,
542 ATTRIBUTE30 = X_ATTRIBUTE30,
543 LAST_UPDATE_DATE = NVL(X_LAST_UPDATE_DATE,SYSDATE),
544 LAST_UPDATED_BY = NVL(X_LAST_UPDATED_BY,FND_GLOBAL.USER_ID),
545 LAST_UPDATE_LOGIN = NVL(X_LAST_UPDATE_LOGIN,FND_GLOBAL.LOGIN_ID)
546 where TEST_VALUE_ID = X_TEST_VALUE_ID;
547
548 if (sql%notfound) then
549 raise no_data_found;
550 end if;
551
552 update GMD_QC_TEST_VALUES_TL set
553 DISPLAY_LABEL_NUMERIC_RANGE = X_DISPLAY_LABEL_NUMERIC_RANGE,
554 TEST_VALUE_DESC = X_TEST_VALUE_DESC,
555 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
556 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
557 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
558 SOURCE_LANG = userenv('LANG')
559 where TEST_VALUE_ID = X_TEST_VALUE_ID
560 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
561
562 if (sql%notfound) then
563 raise no_data_found;
564 end if;
565 end UPDATE_ROW;
566
567 FUNCTION DELETE_ROW (p_test_value_id in NUMBER) RETURN BOOLEAN IS
568 begin
569 IF p_test_value_id IS NOT NULL THEN
570 DELETE from GMD_QC_TEST_VALUES_TL
571 where TEST_VALUE_ID = p_test_value_id ;
572
573 if (sql%notfound) then
574 raise no_data_found;
575 end if;
576
577 delete from GMD_QC_TEST_VALUES_B
578 where TEST_VALUE_ID = p_test_value_id;
579
580 if (sql%notfound) then
581 raise no_data_found;
582 end if;
583 ELSE
584 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_QC_TEST_VALUES');
585 RETURN FALSE;
586 END IF;
587
588 RETURN TRUE;
589
590 EXCEPTION
591 WHEN NO_DATA_FOUND
592 THEN
593 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_QC_TEST_VALUES');
594 RETURN FALSE;
595 WHEN OTHERS
596 THEN
597 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QC_TEST_VALUES_PVT.DELETE_ROW','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
598 RETURN FALSE;
599 end DELETE_ROW;
600
601 procedure ADD_LANGUAGE
602 is
603 begin
604 delete from GMD_QC_TEST_VALUES_TL T
605 where not exists
606 (select NULL
607 from GMD_QC_TEST_VALUES_B B
608 where B.TEST_VALUE_ID = T.TEST_VALUE_ID
609 );
610
611 update GMD_QC_TEST_VALUES_TL T set (
612 DISPLAY_LABEL_NUMERIC_RANGE,
613 TEST_VALUE_DESC
614 ) = (select
615 B.DISPLAY_LABEL_NUMERIC_RANGE,
616 B.TEST_VALUE_DESC
617 from GMD_QC_TEST_VALUES_TL B
618 where B.TEST_VALUE_ID = T.TEST_VALUE_ID
619 and B.LANGUAGE = T.SOURCE_LANG)
620 where (
621 T.TEST_VALUE_ID,
622 T.LANGUAGE
623 ) in (select
624 SUBT.TEST_VALUE_ID,
625 SUBT.LANGUAGE
626 from GMD_QC_TEST_VALUES_TL SUBB, GMD_QC_TEST_VALUES_TL SUBT
627 where SUBB.TEST_VALUE_ID = SUBT.TEST_VALUE_ID
628 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
629 and (SUBB.DISPLAY_LABEL_NUMERIC_RANGE <> SUBT.DISPLAY_LABEL_NUMERIC_RANGE
630 or (SUBB.DISPLAY_LABEL_NUMERIC_RANGE is null and SUBT.DISPLAY_LABEL_NUMERIC_RANGE is not null)
631 or (SUBB.DISPLAY_LABEL_NUMERIC_RANGE is not null and SUBT.DISPLAY_LABEL_NUMERIC_RANGE is null)
632 or SUBB.TEST_VALUE_DESC <> SUBT.TEST_VALUE_DESC
633 or (SUBB.TEST_VALUE_DESC is null and SUBT.TEST_VALUE_DESC is not null)
634 or (SUBB.TEST_VALUE_DESC is not null and SUBT.TEST_VALUE_DESC is null)
635 ));
636
637 insert into GMD_QC_TEST_VALUES_TL (
638 TEST_VALUE_ID,
639 DISPLAY_LABEL_NUMERIC_RANGE,
640 TEST_VALUE_DESC,
641 CREATION_DATE,
642 CREATED_BY,
643 LAST_UPDATED_BY,
644 LAST_UPDATE_DATE,
645 LAST_UPDATE_LOGIN,
646 LANGUAGE,
647 SOURCE_LANG
648 ) select
649 B.TEST_VALUE_ID,
650 B.DISPLAY_LABEL_NUMERIC_RANGE,
651 B.TEST_VALUE_DESC,
652 B.CREATION_DATE,
653 B.CREATED_BY,
654 B.LAST_UPDATED_BY,
655 B.LAST_UPDATE_DATE,
656 B.LAST_UPDATE_LOGIN,
657 L.LANGUAGE_CODE,
658 B.SOURCE_LANG
659 from GMD_QC_TEST_VALUES_TL B, FND_LANGUAGES L
660 where L.INSTALLED_FLAG in ('I', 'B')
661 and B.LANGUAGE = userenv('LANG')
662 and not exists
663 (select NULL
664 from GMD_QC_TEST_VALUES_TL T
665 where T.TEST_VALUE_ID = B.TEST_VALUE_ID
666 and T.LANGUAGE = L.LANGUAGE_CODE);
667 end ADD_LANGUAGE;
668
669 FUNCTION lock_row (
670 p_test_value_id IN NUMBER)
671 RETURN BOOLEAN
672 IS
673 dummy NUMBER;
674 BEGIN
675
676 IF (p_test_value_id IS NOT NULL) THEN
677 SELECT test_value_id
678 INTO dummy
679 FROM gmd_qc_test_values_b
680 WHERE test_value_id = p_test_value_id
681 FOR UPDATE OF test_value_id NOWAIT ;
682 ELSE
683 gmd_api_pub.log_message('GMD_NO_KEYS','TABLE_NAME', 'GMD_QC_TEST_VALUES');
684 RETURN FALSE;
685 END IF;
686
687 RETURN TRUE;
688
689 EXCEPTION
690 WHEN NO_DATA_FOUND
691 THEN
692 gmd_api_pub.log_message('GMD_NO_DATA_FOUND','TABLE_NAME', 'GMD_QC_TEST_VALUES');
693 RETURN FALSE;
694 WHEN OTHERS
695 THEN
696 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_QC_TEST_VALUES_PVT.LOCK_ROW','ERROR',SUBSTR(SQLERRM,1,100),'POSITION','010');
697 RETURN FALSE;
698 END lock_row;
699
700
701 end GMD_QC_TEST_VALUES_PVT;
[Home] [Help]