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