1 package body GMD_TEST_VALUES_PKG as
2 /* $Header: GMDGIAVB.pls 115.0 2002/03/12 12:54:34 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_QCASSY_VAL_ID in NUMBER,
6 X_QCASSY_TYP_ID in NUMBER,
7 X_ORGN_CODE in VARCHAR2,
8 X_ASSAY_CODE in VARCHAR2,
9 X_ASSAY_VALUE in VARCHAR2,
10 X_TEXT_CODE in NUMBER,
11 X_VALUE_NUM_MIN in NUMBER,
12 X_VALUE_NUM_MAX in NUMBER,
13 X_ASSAY_VALUE_RANGE_ORDER in NUMBER,
14 X_VALUE_DESC in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from GMD_TEST_VALUES_B
22 where QCASSY_VAL_ID = X_QCASSY_VAL_ID
23 ;
24 begin
25 insert into GMD_TEST_VALUES_B (
26 QCASSY_VAL_ID,
27 QCASSY_TYP_ID,
28 ORGN_CODE,
29 ASSAY_CODE,
30 ASSAY_VALUE,
31 TEXT_CODE,
32 VALUE_NUM_MIN,
33 VALUE_NUM_MAX,
34 ASSAY_VALUE_RANGE_ORDER,
35 CREATION_DATE,
36 CREATED_BY,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_LOGIN
40 ) values (
41 X_QCASSY_VAL_ID,
42 X_QCASSY_TYP_ID,
43 X_ORGN_CODE,
44 X_ASSAY_CODE,
45 X_ASSAY_VALUE,
46 X_TEXT_CODE,
47 X_VALUE_NUM_MIN,
48 X_VALUE_NUM_MAX,
49 X_ASSAY_VALUE_RANGE_ORDER,
50 X_CREATION_DATE,
51 X_CREATED_BY,
52 X_LAST_UPDATE_DATE,
53 X_LAST_UPDATED_BY,
54 X_LAST_UPDATE_LOGIN
55 );
56
57 insert into GMD_TEST_VALUES_TL (
58 QCASSY_VAL_ID,
59 QCASSY_TYP_ID,
60 VALUE_DESC,
61 CREATION_DATE,
62 LAST_UPDATE_LOGIN,
63 CREATED_BY,
64 LAST_UPDATE_DATE,
65 LAST_UPDATED_BY,
66 LANGUAGE,
67 SOURCE_LANG
68 ) select
69 X_QCASSY_VAL_ID,
70 X_QCASSY_TYP_ID,
71 X_VALUE_DESC,
72 X_CREATION_DATE,
73 X_LAST_UPDATE_LOGIN,
74 X_CREATED_BY,
75 X_LAST_UPDATE_DATE,
76 X_LAST_UPDATED_BY,
77 L.LANGUAGE_CODE,
78 userenv('LANG')
79 from FND_LANGUAGES L
80 where L.INSTALLED_FLAG in ('I', 'B')
81 and not exists
82 (select NULL
83 from GMD_TEST_VALUES_TL T
84 where T.QCASSY_VAL_ID = X_QCASSY_VAL_ID
85 and T.LANGUAGE = L.LANGUAGE_CODE);
86
87 open c;
88 fetch c into X_ROWID;
89 if (c%notfound) then
90 close c;
91 raise no_data_found;
92 end if;
93 close c;
94
95 end INSERT_ROW;
96
97 procedure LOCK_ROW (
98 X_QCASSY_VAL_ID in NUMBER,
99 X_QCASSY_TYP_ID in NUMBER,
100 X_ORGN_CODE in VARCHAR2,
101 X_ASSAY_CODE in VARCHAR2,
102 X_ASSAY_VALUE in VARCHAR2,
103 X_TEXT_CODE in NUMBER,
104 X_VALUE_NUM_MIN in NUMBER,
105 X_VALUE_NUM_MAX in NUMBER,
106 X_ASSAY_VALUE_RANGE_ORDER in NUMBER,
107 X_VALUE_DESC in VARCHAR2
108 ) is
109 cursor c is select
110 QCASSY_TYP_ID,
111 ORGN_CODE,
112 ASSAY_CODE,
113 ASSAY_VALUE,
114 TEXT_CODE,
115 VALUE_NUM_MIN,
116 VALUE_NUM_MAX,
117 ASSAY_VALUE_RANGE_ORDER
118 from GMD_TEST_VALUES_B
119 where QCASSY_VAL_ID = X_QCASSY_VAL_ID
120 for update of QCASSY_VAL_ID nowait;
121 recinfo c%rowtype;
122
123 cursor c1 is select
124 VALUE_DESC,
125 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
126 from GMD_TEST_VALUES_TL
127 where QCASSY_VAL_ID = X_QCASSY_VAL_ID
128 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
129 for update of QCASSY_VAL_ID nowait;
130 begin
131 open c;
132 fetch c into recinfo;
133 if (c%notfound) then
134 close c;
135 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
136 app_exception.raise_exception;
137 end if;
138 close c;
139 if ( (recinfo.QCASSY_TYP_ID = X_QCASSY_TYP_ID)
140 AND ((recinfo.ORGN_CODE = X_ORGN_CODE)
141 OR ((recinfo.ORGN_CODE is null) AND (X_ORGN_CODE is null)))
142 AND (recinfo.ASSAY_CODE = X_ASSAY_CODE)
143 AND (recinfo.ASSAY_VALUE = X_ASSAY_VALUE)
144 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
145 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
146 AND ((recinfo.VALUE_NUM_MIN = X_VALUE_NUM_MIN)
147 OR ((recinfo.VALUE_NUM_MIN is null) AND (X_VALUE_NUM_MIN is null)))
148 AND ((recinfo.VALUE_NUM_MAX = X_VALUE_NUM_MAX)
149 OR ((recinfo.VALUE_NUM_MAX is null) AND (X_VALUE_NUM_MAX is null)))
150 AND ((recinfo.ASSAY_VALUE_RANGE_ORDER = X_ASSAY_VALUE_RANGE_ORDER)
151 OR ((recinfo.ASSAY_VALUE_RANGE_ORDER is null) AND (X_ASSAY_VALUE_RANGE_ORDER is null)))
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158
159 for tlinfo in c1 loop
160 if (tlinfo.BASELANG = 'Y') then
161 if ( ((tlinfo.VALUE_DESC = X_VALUE_DESC)
162 OR ((tlinfo.VALUE_DESC is null) AND (X_VALUE_DESC is null)))
163 ) then
164 null;
165 else
166 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
167 app_exception.raise_exception;
168 end if;
169 end if;
170 end loop;
171 return;
172 end LOCK_ROW;
173
174 procedure UPDATE_ROW (
175 X_QCASSY_VAL_ID in NUMBER,
176 X_QCASSY_TYP_ID in NUMBER,
177 X_ORGN_CODE in VARCHAR2,
178 X_ASSAY_CODE in VARCHAR2,
179 X_ASSAY_VALUE in VARCHAR2,
180 X_TEXT_CODE in NUMBER,
181 X_VALUE_NUM_MIN in NUMBER,
182 X_VALUE_NUM_MAX in NUMBER,
183 X_ASSAY_VALUE_RANGE_ORDER in NUMBER,
184 X_VALUE_DESC in VARCHAR2,
185 X_LAST_UPDATE_DATE in DATE,
186 X_LAST_UPDATED_BY in NUMBER,
187 X_LAST_UPDATE_LOGIN in NUMBER
188 ) is
189 begin
190 update GMD_TEST_VALUES_B set
191 ORGN_CODE = X_ORGN_CODE,
192 ASSAY_CODE = X_ASSAY_CODE,
193 ASSAY_VALUE = X_ASSAY_VALUE,
194 TEXT_CODE = X_TEXT_CODE,
195 VALUE_NUM_MIN = X_VALUE_NUM_MIN,
196 VALUE_NUM_MAX = X_VALUE_NUM_MAX,
197 ASSAY_VALUE_RANGE_ORDER = X_ASSAY_VALUE_RANGE_ORDER,
198 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
199 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
200 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
201 where QCASSY_VAL_ID = X_QCASSY_VAL_ID
202 and QCASSY_TYP_ID = X_QCASSY_TYP_ID;
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207
208 update GMD_TEST_VALUES_TL set
209 VALUE_DESC = X_VALUE_DESC,
210 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
211 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
212 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
213 SOURCE_LANG = userenv('LANG')
214 where QCASSY_VAL_ID = X_QCASSY_VAL_ID
215 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220 end UPDATE_ROW;
221
222 procedure DELETE_ROW (
223 X_QCASSY_VAL_ID in NUMBER
224 ) is
225 begin
226 delete from GMD_TEST_VALUES_TL
227 where QCASSY_VAL_ID = X_QCASSY_VAL_ID;
228
229 if (sql%notfound) then
230 raise no_data_found;
231 end if;
232
233 delete from GMD_TEST_VALUES_B
234 where QCASSY_VAL_ID = X_QCASSY_VAL_ID;
235
236 if (sql%notfound) then
237 raise no_data_found;
238 end if;
239 end DELETE_ROW;
240
241 procedure ADD_LANGUAGE
242 is
243 begin
244 delete from GMD_TEST_VALUES_TL T
245 where not exists
246 (select NULL
247 from GMD_TEST_VALUES_B B
248 where B.QCASSY_VAL_ID = T.QCASSY_VAL_ID
249 );
250
251 update GMD_TEST_VALUES_TL T set (
252 VALUE_DESC
253 ) = (select
254 B.VALUE_DESC
255 from GMD_TEST_VALUES_TL B
256 where B.QCASSY_VAL_ID = T.QCASSY_VAL_ID
257 and B.LANGUAGE = T.SOURCE_LANG)
258 where (
259 T.QCASSY_VAL_ID,
260 T.LANGUAGE
261 ) in (select
262 SUBT.QCASSY_VAL_ID,
263 SUBT.LANGUAGE
264 from GMD_TEST_VALUES_TL SUBB, GMD_TEST_VALUES_TL SUBT
265 where SUBB.QCASSY_VAL_ID = SUBT.QCASSY_VAL_ID
266 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
267 and (SUBB.VALUE_DESC <> SUBT.VALUE_DESC
268 or (SUBB.VALUE_DESC is null and SUBT.VALUE_DESC is not null)
269 or (SUBB.VALUE_DESC is not null and SUBT.VALUE_DESC is null)
270 ));
271
272 insert into GMD_TEST_VALUES_TL (
273 QCASSY_VAL_ID,
274 QCASSY_TYP_ID,
275 VALUE_DESC,
276 CREATION_DATE,
277 LAST_UPDATE_LOGIN,
278 CREATED_BY,
279 LAST_UPDATE_DATE,
280 LAST_UPDATED_BY,
281 LANGUAGE,
282 SOURCE_LANG
283 ) select
284 B.QCASSY_VAL_ID,
285 B.QCASSY_TYP_ID,
286 B.VALUE_DESC,
287 B.CREATION_DATE,
288 B.LAST_UPDATE_LOGIN,
289 B.CREATED_BY,
290 B.LAST_UPDATE_DATE,
291 B.LAST_UPDATED_BY,
292 L.LANGUAGE_CODE,
293 B.SOURCE_LANG
294 from GMD_TEST_VALUES_TL B, FND_LANGUAGES L
295 where L.INSTALLED_FLAG in ('I', 'B')
296 and B.LANGUAGE = userenv('LANG')
297 and not exists
298 (select NULL
299 from GMD_TEST_VALUES_TL T
300 where T.QCASSY_VAL_ID = B.QCASSY_VAL_ID
301 and T.LANGUAGE = L.LANGUAGE_CODE);
302 end ADD_LANGUAGE;
303
304 end GMD_TEST_VALUES_PKG;
[Home] [Help]