1 PACKAGE BODY GR_TECHNICAL_PARAMETERS AS
2 /*$Header: GRPTECHB.pls 120.3 2005/11/16 12:09:10 pbamb noship $*/
3
4 PROCEDURE Get_Tech_Parm_Data
5 (p_commit IN VARCHAR2,
6 p_api_version IN NUMBER,
7 p_organization_id IN NUMBER,
8 p_property_id IN VARCHAR2,
9 p_inventory_item_id IN NUMBER,
10 p_label_code IN VARCHAR2,
11 x_value OUT NOCOPY VARCHAR2,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_data OUT NOCOPY VARCHAR2)
14 IS
15 /* Alpha Variables */
16 L_API_NAME CONSTANT VARCHAR2(30) := 'Get Tech Parm Data';
17 L_TEXT_DATA VARCHAR2(80);
18 L_UNIT_CODE VARCHAR2(4);
19 L_RETURN_STATUS VARCHAR2(1) := 'S';
20 L_MSG_DATA VARCHAR2(2000);
21 L_VALUE VARCHAR2(240);
22 L_CHECK_FOR_VALUE VARCHAR2(3);
23 L_TECH_PARM VARCHAR2(1);
24 L_ORGN_CODE VARCHAR2(4) := FND_PROFILE.Value('GEMMS_DEFAULT_ORGN');
25
26 /* Numeric Variables */
27 L_API_VERSION CONSTANT NUMBER := 1.0;
28 L_ORACLE_ERROR NUMBER;
29 L_BOOLEAN_DATA NUMBER(5);
30 L_NUM_DATA NUMBER;
31 L_QCASSY_TYP_ID NUMBER(15);
32 L_MSG_COUNT NUMBER;
33 L_ITEM_ID NUMBER;
34
35 /* Exceptions */
36 NO_TECH_PARM_FOUND EXCEPTION;
37 INCOMPATIBLE_API_VERSION_ERROR EXCEPTION;
38
39 /* Declare cursors */
40
41 CURSOR Cur_get_description IS
42 SELECT label_description
43 FROM gr_labels_tl
44 WHERE label_code = p_label_code
45 AND language = userenv('LANG');
46 X_desc Cur_get_description%ROWTYPE;
47
48 CURSOR c_get_lab_techparm IS
49 SELECT data_type, qcassy_typ_id, lm_unit_code, lowerbound_num,
50 upperbound_num, lowerbound_char, upperbound_char, organization_id, tech_parm_id
51 FROM lm_tech_hdr
52 WHERE organization_id = p_organization_id
53 AND tech_parm_name = X_desc.label_description;
54 LocalLabRecord c_get_lab_techparm%ROWTYPE;
55
56 --<Bug 4724991 consider global technical parameters>
57 CURSOR c_get_lab_techparm_global IS
58 SELECT data_type, qcassy_typ_id, lm_unit_code, lowerbound_num,
59 upperbound_num, lowerbound_char, upperbound_char, organization_id, tech_parm_id
60 FROM lm_tech_hdr
61 WHERE organization_id IS NULL
62 AND tech_parm_name = X_desc.label_description;
63
64 /* B2564522 Changed the cursor to incorporatethe QM changes*/
65 CURSOR c_get_specs IS
66 SELECT b.min_value_num, b.max_value_num, b.target_value_num, b.target_value_char, q.test_unit
67 FROM gmd_spec_tests_b b, gmd_inventory_spec_vrs v, gmd_specifications_b s, gmd_qc_tests_b q
68 WHERE b.test_id = LocalLabRecord.qcassy_typ_id
69 AND s.inventory_item_id = p_inventory_item_id
70 AND b.spec_id = v.spec_id
71 AND s.spec_id = b.spec_id
72 AND q.test_id = b.test_id
73 AND (v.organization_id = p_organization_id OR v.organization_id IS NULL)
74 AND v.lot_number IS NULL
75 AND v.subinventory IS NULL;
76 LocalSpecsRecord c_get_specs%ROWTYPE;
77
78
79 CURSOR Cur_get_num(p_TECH_PARM_ID NUMBER) IS
80 SELECT num_data
81 FROM lm_item_dat
82 WHERE organization_id = p_organization_id
83 AND tech_parm_name = X_desc.label_description
84 AND inventory_item_id = p_inventory_item_id
85 AND tech_parm_id = p_TECH_PARM_ID;
86 X_num_data Cur_get_num%ROWTYPE;
87
88 CURSOR Cur_get_text(p_TECH_PARM_ID NUMBER) IS
89 SELECT text_data
90 FROM lm_item_dat
91 WHERE organization_id = p_organization_id
92 AND tech_parm_name = X_desc.label_description
93 AND inventory_item_id = p_inventory_item_id
94 AND tech_parm_id = p_TECH_PARM_ID;
95 X_text_data Cur_get_text%ROWTYPE;
96
97 CURSOR Cur_get_boolean(p_TECH_PARM_ID NUMBER) IS
98 SELECT boolean_data
99 FROM lm_item_dat
100 WHERE organization_id = p_organization_id
101 AND tech_parm_name = X_desc.label_description
102 AND inventory_item_id = p_inventory_item_id
103 AND tech_parm_id = p_TECH_PARM_ID;
104 X_boolean_data Cur_get_boolean%ROWTYPE;
105
106 CURSOR Cur_get_techparm IS
107 SELECT tech_parm
108 FROM gr_labels_b
109 WHERE label_code = p_label_code;
110 LocalTechParmRecord Cur_get_techparm%ROWTYPE;
111
112 BEGIN
113
114 /* Initialization Routine */
115
116 SAVEPOINT Get_Tech_Parm_Data;
117 x_msg_data := NULL;
118
119 /* Now call the check API versions procedure */
120
121 IF NOT FND_API.Compatible_API_Call
122 (l_api_version,
123 p_api_version,
124 l_api_name,
125 g_pkg_name) THEN
126 RAISE Incompatible_API_version_error;
127 END IF;
128
129 /*
130 ** Set return status to successful
131 */
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 l_check_for_value := 'YES';
135
136 OPEN Cur_get_description;
137 FETCH Cur_get_description INTO X_desc;
138
139 IF Cur_get_description%NOTFOUND THEN
140 RAISE No_Tech_Parm_Found;
141 END IF;
142
143 OPEN c_get_lab_techparm;
144 FETCH c_get_lab_techparm INTO LocalLabRecord;
145 IF c_get_lab_techparm%FOUND THEN
146 WHILE c_get_lab_techparm%FOUND LOOP
147
148 /* If we are looking at a numeric value */
149 IF (c_get_lab_techparm%FOUND and LocalLabRecord.data_type NOT IN (0,2,3,4)) THEN
150 /* If it is not a QC assay */
151 IF LocalLabRecord.qcassy_typ_id IS NULL THEN
152 IF p_property_id = 'LOW' THEN
153 l_value := TO_CHAR(LocalLabRecord.lowerbound_num);
154 ELSIF p_property_id = 'HIGH' THEN
155 l_value := TO_CHAR(LocalLabRecord.upperbound_num);
156 ELSIF p_property_id = 'VALUE' and l_check_for_value = 'YES' THEN
157 OPEN Cur_get_num(LocalLabRecord.tech_parm_id);
158 FETCH Cur_get_num INTO X_num_data;
159 IF Cur_get_num%FOUND THEN
160 l_value := TO_CHAR(X_num_data.num_data);
161 END IF;
162 CLOSE Cur_get_num;
163 ELSIF p_property_id = 'UNIT' THEN
164 l_value := LocalLabRecord.lm_unit_code;
165 END IF;
166 /* This is a QC assay B2564522 - changed the values for the correct columns from new table*/
167 ELSE
168 OPEN c_get_specs;
169 FETCH c_get_specs INTO LocalSpecsRecord;
170 WHILE c_get_specs%FOUND LOOP
171 IF p_property_id = 'LOW' THEN
172 l_value := TO_CHAR(LocalSpecsRecord.min_value_num);
173 ELSIF p_property_id = 'HIGH' THEN
174 l_value := TO_CHAR(LocalSpecsRecord.max_value_num);
175 ELSIF p_property_id = 'VALUE' THEN
176 l_value := TO_CHAR(LocalSpecsRecord.target_value_num);
177 ELSIF p_property_id = 'UNIT' THEN
178 l_value := LocalSpecsRecord.test_unit;
179 END IF;
180 FETCH c_get_specs INTO LocalSpecsRecord;
181 END LOOP;
182 CLOSE c_get_specs;
183 END IF;
184
185 /* If it is a character value */
186 ELSIF LocalLabRecord.data_type = 0 THEN
187 IF p_property_id = 'LW_CHR' THEN
188 l_value := LocalLabRecord.lowerbound_char;
189 END IF;
190 IF p_property_id = 'HI_CHR' THEN
191 l_value := LocalLabRecord.upperbound_char;
192 END IF;
193 IF p_property_id = 'VALUE' THEN
194 /* If it is not a QC assay and we have an item id */
195 IF LocalLabRecord.qcassy_typ_id IS NULL and l_check_for_value = 'YES' THEN
196 OPEN Cur_get_text(LocalLabRecord.tech_parm_id);
197 FETCH Cur_get_text INTO X_text_data;
198 IF Cur_get_text%FOUND THEN
199 l_value := X_text_data.text_data;
200 END IF;
201 CLOSE Cur_get_text;
202 /* If it is a QC assay B2564522 - changed the text to target_value_char*/
203 ELSIF LocalLabRecord.qcassy_typ_id IS NOT NULL THEN
204 OPEN c_get_specs;
205 FETCH c_get_specs INTO LocalSpecsRecord;
206 IF c_get_specs%FOUND THEN
207 l_value := LocalSpecsRecord.target_value_char;
208 END IF;
209 CLOSE c_get_specs;
210 END IF;
211 ELSIF p_property_id = 'UNIT' THEN
212 l_value := LocalLabRecord.lm_unit_code;
213
214 END IF;
215 /* If this is a list and we are looking for the item technical data */
216 ELSIF LocalLabRecord.data_type = 2 and l_check_for_value = 'YES' THEN
217 /* Not a qc assay */
218 IF p_property_id = 'VALUE' THEN
219 IF LocalLabRecord.qcassy_typ_id IS NULL THEN
220 OPEN Cur_get_text(LocalLabRecord.tech_parm_id);
221 FETCH Cur_get_text INTO X_text_data;
222 IF Cur_get_text%FOUND THEN
223 l_value := X_text_data.text_data;
224 END IF;
225 CLOSE Cur_get_text;
226 /* A qc assay B2564522 - changed text_spec to target_value_char*/
227 ELSIF LocalLabRecord.qcassy_typ_id IS NOT NULL THEN
228 OPEN c_get_specs;
229 FETCH c_get_specs INTO LocalSpecsRecord;
230 IF c_get_specs%FOUND THEN
231 l_value := LocalSpecsRecord.target_value_char;
232 END IF;
233 CLOSE c_get_specs;
234 END IF;
235 /* QM B2564522 - changed qcunit_code to test_unit*/
236 ELSIF p_property_id = 'UNIT' THEN
237 OPEN c_get_specs;
238 FETCH c_get_specs INTO LocalSpecsRecord;
239 IF c_get_specs%FOUND THEN
240 l_value := LocalSpecsRecord.test_unit;
241 END IF;
242 CLOSE c_get_specs;
243 END IF;
244 /*If this is a boolean and we are looking for the item technical data */
245 ELSIF LocalLabRecord.data_type = 3 and p_property_id = 'VALUE' and l_check_for_value = 'YES' THEN
246 OPEN Cur_get_boolean(LocalLabRecord.tech_parm_id);
247 FETCH Cur_get_boolean INTO X_boolean_data;
248 IF Cur_get_boolean%FOUND THEN
249 l_value := TO_CHAR(X_boolean_data.boolean_data);
250 END IF;
251 CLOSE Cur_get_boolean;
252 END IF;
253 FETCH c_get_lab_techparm INTO LocalLabRecord;
254 END LOOP;
255 ELSE
256
257 OPEN c_get_lab_techparm_global;
258 FETCH c_get_lab_techparm_global INTO LocalLabRecord;
259 WHILE c_get_lab_techparm_global%FOUND LOOP
260 /* If we are looking at a numeric value */
261 IF (c_get_lab_techparm_global%FOUND and LocalLabRecord.data_type NOT IN (0,2,3,4)) THEN
262 /* If it is not a QC assay */
263 IF LocalLabRecord.qcassy_typ_id IS NULL THEN
264 IF p_property_id = 'LOW' THEN
265 l_value := TO_CHAR(LocalLabRecord.lowerbound_num);
266 ELSIF p_property_id = 'HIGH' THEN
267 l_value := TO_CHAR(LocalLabRecord.upperbound_num);
268 ELSIF p_property_id = 'VALUE' and l_check_for_value = 'YES' THEN
269 OPEN Cur_get_num(LocalLabRecord.tech_parm_id);
270 FETCH Cur_get_num INTO X_num_data;
271 IF Cur_get_num%FOUND THEN
272 l_value := TO_CHAR(X_num_data.num_data);
273 END IF;
274 CLOSE Cur_get_num;
275 ELSIF p_property_id = 'UNIT' THEN
276 l_value := LocalLabRecord.lm_unit_code;
277 END IF;
278 /* This is a QC assay B2564522 - changed the values for the correct columns from new table*/
279 ELSE
280 OPEN c_get_specs;
281 FETCH c_get_specs INTO LocalSpecsRecord;
282 WHILE c_get_specs%FOUND LOOP
283 IF p_property_id = 'LOW' THEN
284 l_value := TO_CHAR(LocalSpecsRecord.min_value_num);
285 ELSIF p_property_id = 'HIGH' THEN
286 l_value := TO_CHAR(LocalSpecsRecord.max_value_num);
287 ELSIF p_property_id = 'VALUE' THEN
288 l_value := TO_CHAR(LocalSpecsRecord.target_value_num);
289 ELSIF p_property_id = 'UNIT' THEN
290 l_value := LocalSpecsRecord.test_unit;
291 END IF;
292 FETCH c_get_specs INTO LocalSpecsRecord;
293 END LOOP;
294 CLOSE c_get_specs;
295 END IF;
296
297 /* If it is a character value */
298 ELSIF LocalLabRecord.data_type = 0 THEN
299 IF p_property_id = 'LW_CHR' THEN
300 l_value := LocalLabRecord.lowerbound_char;
301 END IF;
302 IF p_property_id = 'HI_CHR' THEN
303 l_value := LocalLabRecord.upperbound_char;
304 END IF;
305 IF p_property_id = 'VALUE' THEN
306 /* If it is not a QC assay and we have an item id */
307 IF LocalLabRecord.qcassy_typ_id IS NULL and l_check_for_value = 'YES' THEN
308 OPEN Cur_get_text(LocalLabRecord.tech_parm_id);
309 FETCH Cur_get_text INTO X_text_data;
310 IF Cur_get_text%FOUND THEN
311 l_value := X_text_data.text_data;
312 END IF;
313 CLOSE Cur_get_text;
314 /* If it is a QC assay B2564522 - changed the text to target_value_char*/
315 ELSIF LocalLabRecord.qcassy_typ_id IS NOT NULL THEN
316 OPEN c_get_specs;
317 FETCH c_get_specs INTO LocalSpecsRecord;
318 IF c_get_specs%FOUND THEN
319 l_value := LocalSpecsRecord.target_value_char;
320 END IF;
321 CLOSE c_get_specs;
322 END IF;
323 ELSIF p_property_id = 'UNIT' THEN
324 l_value := LocalLabRecord.lm_unit_code;
325
326 END IF;
327 /* If this is a list and we are looking for the item technical data */
328 ELSIF LocalLabRecord.data_type = 2 and l_check_for_value = 'YES' THEN
329 /* Not a qc assay */
330 IF p_property_id = 'VALUE' THEN
331 IF LocalLabRecord.qcassy_typ_id IS NULL THEN
332 OPEN Cur_get_text(LocalLabRecord.tech_parm_id);
333 FETCH Cur_get_text INTO X_text_data;
334 IF Cur_get_text%FOUND THEN
335 l_value := X_text_data.text_data;
336 END IF;
337 CLOSE Cur_get_text;
338 /* A qc assay B2564522 - changed text_spec to target_value_char*/
339 ELSIF LocalLabRecord.qcassy_typ_id IS NOT NULL THEN
340 OPEN c_get_specs;
341 FETCH c_get_specs INTO LocalSpecsRecord;
342 IF c_get_specs%FOUND THEN
343 l_value := LocalSpecsRecord.target_value_char;
344 END IF;
345 CLOSE c_get_specs;
346 END IF;
347 /* QM B2564522 - changed qcunit_code to test_unit*/
348 ELSIF p_property_id = 'UNIT' THEN
349 OPEN c_get_specs;
350 FETCH c_get_specs INTO LocalSpecsRecord;
351 IF c_get_specs%FOUND THEN
352 l_value := LocalSpecsRecord.test_unit;
353 END IF;
354 CLOSE c_get_specs;
355 END IF;
356 /*If this is a boolean and we are looking for the item technical data */
357 ELSIF LocalLabRecord.data_type = 3 and p_property_id = 'VALUE' and l_check_for_value = 'YES' THEN
358 OPEN Cur_get_boolean(LocalLabRecord.tech_parm_id);
359 FETCH Cur_get_boolean INTO X_boolean_data;
360 IF Cur_get_boolean%FOUND THEN
361 l_value := TO_CHAR(X_boolean_data.boolean_data);
362 END IF;
363 CLOSE Cur_get_boolean;
364 END IF;
365 FETCH c_get_lab_techparm_global INTO LocalLabRecord;
366 END LOOP;
367 END IF;
368 IF c_get_lab_techparm%ISOPEN THEN
369 CLOSE c_get_lab_techparm;
370 END IF;
371
372 CLOSE Cur_get_description;
373
374 IF c_get_lab_techparm_global%ISOPEN THEN
375 CLOSE c_get_lab_techparm_global;
376 END IF;
377
378
379 X_value := l_value;
380
381 EXCEPTION
382 WHEN No_Tech_Parm_Found THEN
383 ROLLBACK TO SAVEPOINT Get_Tech_Parm_Data;
384 FND_MESSAGE.SET_NAME('GR', 'GR_VALID_TECHPARM');
385 APP_EXCEPTION.RAISE_EXCEPTION;
386
387 WHEN Incompatible_API_version_error THEN
388 ROLLBACK TO SAVEPOINT Get_Tech_Parm_Data;
389 FND_MESSAGE.SET_NAME('GR', 'GR_API_VERSION_ERROR');
390 FND_MESSAGE.SET_TOKEN('VERSION', p_api_version,FALSE);
391 APP_EXCEPTION.RAISE_EXCEPTION;
392
393 WHEN OTHERS THEN
394 ROLLBACK TO SAVEPOINT Get_Tech_Parm_Data;
395 l_oracle_error := SQLCODE;
396 l_msg_data := SUBSTR(SQLERRM, 1, 200);
397 FND_MESSAGE.SET_NAME('GR', 'GR_UNEXPECTED_ERROR');
398 FND_MESSAGE.SET_TOKEN('TEXT', l_msg_data, FALSE);
399 APP_EXCEPTION.RAISE_EXCEPTION;
400
401 END Get_Tech_Parm_Data;
402
403 END GR_TECHNICAL_PARAMETERS;
[Home] [Help]