1 PACKAGE BODY GMD_QUALITY_GRP AS
2 /* $Header: GMDGQCMB.pls 120.1 2005/06/21 04:09:04 appldev ship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_QUALITY_GRP';
6
7
8 /* ************************************************************************ */
9 /* API name : get_display_precision */
10 /* Type : Private */
11 /* Function : */
12 /* Pre-reqs : None. */
13 /* Parameters : */
14 /* IN : p_number IN NUMBER (Required) */
15 /* : p_display_precision IN NUMBER (Required) */
16 /* Notes : This function returns the number in character format with */
17 /* the specified precision */
18 /* HISTORY */
19 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
20 /* 13-JUN-2005 Saikiran Vankadari Convergence Changes */
21 /* ************************************************************************ */
22 FUNCTION get_display_precision(p_number NUMBER,
23 p_display_precision NUMBER) RETURN VARCHAR2 IS
24 l_format_mask VARCHAR2(100);
25 l_prefix VARCHAR2(100);
26 BEGIN
27 IF (instr(p_number,'.') <> 0) THEN
28 l_prefix := POWER(10, (instr(p_number,'.') - 1)) - 1;
29 ELSE
30 l_prefix := POWER(10, length(p_number)) - 1;
31 END IF;
32 l_format_mask := POWER(10, p_display_precision) - 1;
33 RETURN TO_CHAR(p_number, l_prefix||'D'||l_format_mask);
34 END get_display_precision;
35
36
37 /* ************************************************************************ */
38 /* API name : get_inv_test_value */
39 /* Type : Private */
40 /* Function : */
41 /* Pre-reqs : None. */
42 /* Parameters : */
43 /* IN : P_inv_test_inp_rec IN inv_inp_rec_type (Required) */
44 /* */
45 /* P_inv_test_inp_rec.organization_id (Required) */
46 /* P_inv_test_inp_rec.inventory_item_Id (Required) */
47 /* P_inv_test_inp_rec.grade_code (Optional) */
48 /* P_inv_test_inp_rec.parent_lot_number (Optional) */
49 /* P_inv_test_inp_rec.lot_number (Optional) */
50 /* P_inv_test_inp_rec.subinventory (Optional) */
51 /* P_inv_test_inp_rec.locator_id (Optional) */
52 /* P_inv_test_inp_rec.Test_Id (Required) */
53 /* */
54 /* OUT : x_return_status OUT VARCHAR2(1) */
55 /* : x_inv_test_out_rec OUT inv_val_out_rec_type */
56 /* */
57 /* x_inv_test_out_rec.Entity_Id (Result/Spec Id) */
58 /* x_inv_test_out_rec.Entity_Value (Result/Spec test value) */
59 /* x_inv_test_out_rec.Entity_min_value */
60 /* x_inv_test_out_rec.Entity_max_value */
61 /* x_inv_test_out_rec.Level (Result/Spec test source/type */
62 /* */
63 /* Notes : This is a wrapper on Procedures GET_INV_RESULT_TEST_VALUE */
64 /* (returns results) or GET_INV_SPEC_TEST_VALUE (returns spec */
65 /* tests). Given the item, organization_id, parent_lot, */
66 /* lot, subinventory, locator_id and */
67 /* grade information it gets the result value for a test. If */
68 /* the result is not found it gets the spec details for this */
69 /* test. */
70 /* HISTORY */
71 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
72 /* 09-FEB-2004 Thomas Daniel Bug#3412075. Passed a local variable to */
73 /* get_inv_spec_test_value to avoid the */
74 /* overwrite of the global specification */
75 /* 10-JUN-2005 Saikiran Vankadari Convergence Changes. Replaced all */
76 /* opm-inventory references with that of */
77 /* discrete inventory */
78 /* ************************************************************************ */
79 PROCEDURE get_inv_test_value
80 ( P_inv_test_inp_rec IN inv_inp_rec_type
81 , x_inv_test_out_rec OUT NOCOPY inv_val_out_rec_type
82 , x_return_status OUT NOCOPY VARCHAR2
83 )
84 IS
85 l_api_name VARCHAR2(100) := 'GET_INV_TEST_VALUE';
86 x_results GMD_QUALITY_GRP.inv_rslt_out_rec_type;
87 x_spec_tests GMD_QUALITY_GRP.inv_spec_out_rec_type;
88
89 /* Bug#3412075 - Define the local variable */
90 l_spec_tests GMD_QUALITY_GRP.inv_spec_out_rec_type;
91
92 l_inv_test_inp_rec GMD_QUALITY_GRP.INV_INP_REC_TYPE;
93
94 BEGIN
95 -- Initialize API return status to success
96 x_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 -- Intialize the local variable rec
99 l_inv_test_inp_rec := P_inv_test_inp_rec;
100
101 -- Check if Plant_id value is passed.
102 -- If it is passed then we obtain the test samples/results/specs
103 -- for this plant organization.
104 -- Of course if there is no result we would like to derive Results/Specs
105 -- for the lab organization_id that is passed in (mandatory field for the record
106 -- type p_inv_test_inp_rec.
107
108 -- Call the fetch Results API
109 IF p_inv_test_inp_rec.plant_id IS NOT NULL THEN
110 l_inv_test_inp_rec.organization_id := p_inv_test_inp_rec.plant_id;
111
112 GMD_QUALITY_GRP.get_inv_result_test_value
113 ( p_inv_rslt_inp_rec => l_inv_test_inp_rec
114 , x_inv_rslt_out_rec => x_results
115 , x_return_status => x_return_status);
116
117 IF x_results.result_value IS NULL THEN
118 -- check if you get results using lab_id or organization_id
119 l_inv_test_inp_rec.organization_id := p_inv_test_inp_rec.organization_id;
120
121 GMD_QUALITY_GRP.get_inv_result_test_value
122 ( p_inv_rslt_inp_rec => l_inv_test_inp_rec
123 , x_inv_rslt_out_rec => x_results
124 , x_return_status => x_return_status);
125 END IF;
126 ELSE -- no plant_id provided, so use the lab or organization id
127 l_inv_test_inp_rec.organization_id := p_inv_test_inp_rec.organization_id;
128
129 GMD_QUALITY_GRP.get_inv_result_test_value
130 ( p_inv_rslt_inp_rec => l_inv_test_inp_rec
131 , x_inv_rslt_out_rec => x_results
132 , x_return_status => x_return_status
133 );
134 END IF; -- When plant_id is not null
135
136 IF x_results.result_value IS NULL THEN
137 -- Call the fetch Spec Test API
138
139 IF p_inv_test_inp_rec.plant_id IS NOT NULL THEN
140 l_inv_test_inp_rec.organization_id := p_inv_test_inp_rec.plant_id;
141
142 GMD_QUALITY_GRP.get_inv_spec_test_value
143 ( p_inv_spec_inp_rec => l_inv_test_inp_rec
144 , x_inv_spec_out_rec => x_spec_tests
145 , x_return_status => x_return_status
146 );
147
148 -- Level=41 or 51 would indicate a Global Spec
149 -- in this case we would want to retrieve lab_id or organization_id
150 -- based specification first
151 IF x_spec_tests.level IN (41,51) THEN
152 -- check if you get results using lab_id or organization_id
153 l_inv_test_inp_rec.organization_id := p_inv_test_inp_rec.organization_id;
154
155 GMD_QUALITY_GRP.get_inv_spec_test_value
156 ( p_inv_spec_inp_rec => l_inv_test_inp_rec
157 /* Bug#3412075 - Changed the parameter from x_spec_tests to l_spec_tests */
158 /* to avoid the overwriting of the data from the global sepc */
159 , x_inv_spec_out_rec => l_spec_tests
160 , x_return_status => x_return_status
161 );
162 /* Bug#3412075 - If a local spec is found then we have to assign its data */
163 IF l_spec_tests.spec_id IS NOT NULL THEN
164 x_spec_tests := l_spec_tests;
165 END IF;
166 END IF;
167 ELSE -- no plant_id provided, so use the lab or orgn ocde
168 l_inv_test_inp_rec.organization_id := p_inv_test_inp_rec.organization_id;
169
170 GMD_QUALITY_GRP.get_inv_spec_test_value
171 ( p_inv_spec_inp_rec => l_inv_test_inp_rec
172 , x_inv_spec_out_rec => x_spec_tests
173 , x_return_status => x_return_status
174 );
175 END IF; -- When plant_id is not null
176
177 x_inv_test_out_rec.entity_id := x_spec_tests.spec_id;
178 x_inv_test_out_rec.spec_id := x_spec_tests.spec_id;
179 x_inv_test_out_rec.entity_value := x_spec_tests.target_value;
180 x_inv_test_out_rec.entity_min_value := x_spec_tests.min_value;
181 x_inv_test_out_rec.entity_max_value := x_spec_tests.max_value;
182 x_inv_test_out_rec.level := x_spec_tests.level;
183 ELSE -- Assign value from results
184 x_inv_test_out_rec.entity_id := x_results.result_id;
185 x_inv_test_out_rec.spec_id := x_results.spec_id;
186 x_inv_test_out_rec.entity_value := x_results.result_value;
187 x_inv_test_out_rec.entity_min_value := x_results.min_value;
188 x_inv_test_out_rec.entity_max_value := x_results.max_value;
189 x_inv_test_out_rec.level := x_results.level;
190 x_inv_test_out_rec.composite_ind := x_results.composite_ind;
191 END IF;
192
193 EXCEPTION
194 WHEN FND_API.G_EXC_ERROR THEN
195 x_return_status := FND_API.G_RET_STS_ERROR ;
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
198 WHEN OTHERS THEN
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
200 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
201 END get_inv_test_value;
202
203 /* ************************************************************************ */
204 /* API name : get_inv_result_test_value */
205 /* Type : Private */
206 /* Function : */
207 /* Pre-reqs : None. */
208 /* Parameters : */
209 /* IN : P_inv_rslt_inp_rec IN inv_inp_rec_type (Required) */
210 /* */
211 /* OUT : x_return_status OUT VARCHAR2(1) */
212 /* : x_inv_rslt_out_rec OUT inv_rslt_out_rec_type */
213 /* */
214 /* x_inv_rslt_out_rec.Result_Id (Simple/Composite Result Id) */
215 /* x_inv_rslt_out_rec.result_value (Numeric or Char Results) */
216 /* x_inv_rslt_out_rec.Min_Value (Result value lower limit) */
217 /* x_inv_rslt_out_rec.Max_Value (Result value upper limit) */
218 /* x_inv_rslt_out_rec.Level (value representing inventory/lot */
219 /* specific test result) */
220 /* */
221 /* Notes : Given the item, organization_id, parent_lot, lot, */
222 /* subinventory, locator_id and */
223 /* grade information this API gets the result value for a test*/
224 /* This API can be called independently to return the test */
225 /* results for a sample that has an approved disposition. */
226 /* The results could return either simple or composite based */
227 /* on the number of approved samples (or sample acive count) */
228 /* for a given item. */
229 /* */
230 /* HISTORY */
231 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
232 /* 10-JUN-2005 Saikiran Vankadari Convergence Changes. Replaced all */
233 /* opm-inventory references with that of */
234 /* discrete inventory */
235 /* ************************************************************************ */
236 PROCEDURE get_inv_result_test_value
237 ( P_inv_rslt_inp_rec IN inv_inp_rec_type
238 , x_inv_rslt_out_rec OUT NOCOPY inv_rslt_out_rec_type
239 , x_return_status OUT NOCOPY VARCHAR2
240 )
241 IS
242 l_api_name VARCHAR2(100) := 'GET_INV_RESULT_TEST_VALUE';
243 x_sampling_events GMD_QUALITY_GRP.sampling_events_tbl_type;
244 x_results GMD_RESULTS_GRP.gmd_results_rec_tbl;
245 l_level_rec GMD_QUALITY_GRP.inv_inp_rec_type;
246 l_return_status VARCHAR2(1);
247 l_composite_spec_disp_id NUMBER;
248 i NUMBER := 0;
249 l_row_num NUMBER := 0;
250 l_test_type gmd_qc_tests_b.test_type%TYPE;
251 l_min_value_num gmd_spec_tests_b.min_value_num%TYPE;
252 l_max_value_num gmd_spec_tests_b.max_value_num%TYPE;
253 l_min_value_char gmd_spec_tests_b.min_value_char%TYPE;
254 l_max_value_char gmd_spec_tests_b.max_value_char%TYPE;
255 l_display_precision gmd_spec_tests_b.display_precision%TYPE;
256
257 l_result_id gmd_results.result_id%TYPE;
258 l_result_value_num gmd_results.result_value_num%TYPE;
259 l_result_value_char gmd_results.result_value_char%TYPE;
260
261 -- Cursor defn
262 -- Get simple results
263 Cursor get_simple_results(vSample_id NUMBER,
264 vEvent_Spec_disp_id NUMBER,
265 vTest_id NUMBER) IS
266 SELECT sr.result_id, sr.result_value_num, sr.result_value_char
267 FROM gmd_results sr,
268 gmd_spec_results spr
269 WHERE sr.sample_id = vSample_id
270 AND sr.test_id = vTest_id
271 AND spr.result_id = sr.result_id
272 AND spr.Event_spec_disp_id = vEvent_Spec_disp_id
273 AND (spr.evaluation_ind <> '4C'
274 OR spr.evaluation_ind <> '5O')
275 -- B3698232 The manager must evaluate the result for it to be considered for the simulator
276 AND spr.evaluation_ind IS NOT NULL
277 ;
278
279 Cursor get_qc_tests(vTest_id NUMBER) IS
280 SELECT test_type, min_value_num, max_value_num, display_precision
281 FROM gmd_qc_tests_b
282 WHERE test_id = vTest_id;
283
287 min_value_char, max_value_char, display_precision
284 Cursor get_spec_tests(vSpec_id NUMBER,
285 vTest_id NUMBER) IS
286 SELECT min_value_num, max_value_num,
288 FROM gmd_spec_tests_b
289 WHERE spec_id = vSpec_id
290 AND test_id = vTest_id;
291
292 -- Get composite results
293 Cursor get_composite_results(vComposite_spec_disp_id NUMBER) IS
294 SELECT rst.mean, rst.mode_char, rst.high_num, rst.low_num,
295 rst.high_char, rst.low_char, rst.composite_result_id,
296 rst.test_id
297 FROM gmd_composite_results rst, gmd_composite_spec_disp csd
298 WHERE rst.test_id = p_inv_rslt_inp_rec.test_id
299 AND rst.delete_mark = 0
300 AND rst.composite_spec_disp_id = csd.composite_spec_disp_id
301 AND csd.composite_spec_disp_id = vComposite_spec_disp_id
302 AND csd.latest_ind ='Y';
303
304 Cursor get_composite_disp(vEvent_spec_disp_id NUMBER) IS
305 SELECT composite_spec_disp_id
306 FROM gmd_composite_spec_disp
307 WHERE event_spec_disp_id = vEvent_spec_disp_id
308 -- B3698232 Added disposition 'Complete' for the composite sample
309 -- AND disposition IN ('4A','5AV');
310 AND disposition IN ('3C', '4A','5AV');
311
312 -- get display precision for composite results
313 CURSOR get_tst_display_precision(vTest_id NUMBER) IS
314 SELECT display_precision
315 FROM gmd_qc_tests_b
316 WHERE test_id = vTest_id;
317
318 l_test_found BOOLEAN := FALSE;
319 l_disp_precision NUMBER;
320
321 -- Exception Definition
322 No_test_results_exp EXCEPTION;
323
324 BEGIN
325 -- Initialize API return status to success
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327
328 -- Get the list of approved sampling events
329 GMD_QUALITY_GRP.get_appr_sampling_events
330 ( p_inv_rslt_inp_rec => p_inv_rslt_inp_rec
331 , x_sampling_events_tbl => x_sampling_events
332 , x_return_status => x_return_status
333 );
334
335 -- For each approved sampling event, based on the sample active count
336 -- we return either the composite or simple result.
337 FOR i IN 1 .. x_sampling_events.count LOOP
338 IF (l_debug = 'Y') THEN
339 gmd_debug.put_line('Sample active cnt for row # '
340 ||i||' = '||x_sampling_events(i).sample_active_cnt);
341 END IF;
342
343 IF (x_sampling_events(i).sample_active_cnt > 1) THEN -- composite results
344 IF (l_debug = 'Y') THEN
345 gmd_debug.put_line('Sample id, spec id, event_spec_id row # '
346 ||i||' = '||X_sampling_events(i).sample_id
347 ||' - '||X_sampling_events(i).spec_id
348 ||' - '||X_sampling_events(i).event_spec_disp_id);
349 END IF;
350
351 OPEN get_composite_disp(X_sampling_events(i).event_spec_disp_id);
352 FETCH get_composite_disp INTO l_composite_spec_disp_id;
353 IF get_composite_disp%FOUND THEN
354 IF (l_debug = 'Y') THEN
355 gmd_debug.put_line('Comp_spec_disp_id row # '
356 ||i||' = '||l_Composite_spec_disp_id);
357 END IF;
358
359 FOR get_composite_rec IN get_composite_results(l_Composite_spec_disp_id)
360 LOOP
361 IF get_composite_rec.test_id = p_inv_rslt_inp_rec.test_id THEN
362 x_inv_rslt_out_rec.Result_Id
363 := get_composite_rec.Composite_result_Id ;
364 IF (get_composite_rec.mean IS NOT NULL) THEN
365 -- Get the display precision
366 OPEN get_tst_display_precision(get_composite_rec.test_id);
367 FETCH get_tst_display_precision INTO l_disp_precision;
368 CLOSE get_tst_display_precision;
369
370 IF (l_debug = 'Y') THEN
371 gmd_debug.put_line('Display Precision and mean row # '
372 ||i||' = '||get_composite_rec.mean
373 ||' - '||l_disp_precision);
374 END IF;
375
376 IF (l_disp_precision IS NOT NULL) THEN
377 x_inv_rslt_out_rec.result_value :=
378 get_display_precision(get_composite_rec.mean,
379 l_disp_precision);
380 ELSE
381 x_inv_rslt_out_rec.result_value := get_composite_rec.mean;
382 END IF;
383 x_inv_rslt_out_rec.Spec_id := X_sampling_events(i).spec_id;
384 x_inv_rslt_out_rec.Min_Value := get_composite_rec.low_num;
385 x_inv_rslt_out_rec.Max_Value := get_composite_rec.high_num;
386 x_inv_rslt_out_rec.composite_ind
387 := x_sampling_events(i).sample_active_cnt;
388 l_row_num := i;
389 l_test_found := TRUE;
390 Exit; -- break away from the outer loops
391 ELSIF (get_composite_rec.mode_char IS NOT NULL) THEN
392 -- we use mode for result value is char
393 x_inv_rslt_out_rec.result_value := get_composite_rec.mode_char;
394 x_inv_rslt_out_rec.Min_Value := get_composite_rec.low_char;
398 x_inv_rslt_out_rec.Spec_id := X_sampling_events(i).spec_id;
395 x_inv_rslt_out_rec.Max_Value := get_composite_rec.high_char;
396 x_inv_rslt_out_rec.composite_ind
397 := x_sampling_events(i).sample_active_cnt;
399 l_row_num := i;
400 l_test_found := TRUE;
401 Exit; -- break away from the outer loops
402 END IF;-- If mean or mode composite result exists
403 END IF; -- test ids match
404 END LOOP; -- Loop thro all composite results
405 ELSE -- No composite results found
406 FND_MESSAGE.SET_NAME('GMD','GMD_RESULT_NOT_FOUND');
407 FND_MESSAGE.SET_TOKEN('SAMPLE_ID', X_sampling_events(i).sample_id);
408 FND_MESSAGE.SET_TOKEN('TEST_ID', p_inv_rslt_inp_rec.test_id);
409 FND_MSG_PUB.ADD;
410 END IF; -- if the composite disposition id is found
411 CLOSE get_composite_disp;
412 ELSE -- for simple results
413
414 IF (l_debug = 'Y') THEN
415 gmd_debug.put_line('Sample id, spec id, event_spec_id row # '
416 ||i||' = '||X_sampling_events(i).sample_id
417 ||' - '||X_sampling_events(i).spec_id
418 ||' - '||X_sampling_events(i).event_spec_disp_id);
419 END IF;
420
421 -- cursor below should return only onw row
422 OPEN get_simple_results(X_sampling_events(i).sample_id,
423 X_sampling_events(i).event_spec_disp_id,
424 p_inv_rslt_inp_rec.test_id);
425 FETCH get_simple_results INTO l_result_id,
426 l_result_value_num,
427 l_result_value_char;
428
429 IF get_simple_results%FOUND THEN
430 -- Get details from gmd_qc_tests
431 OPEN get_qc_tests(p_inv_rslt_inp_rec.test_id);
432 FETCH get_qc_tests INTO l_test_type, l_min_value_num, l_max_value_num,
433 l_display_precision;
434 IF get_qc_tests%FOUND THEN
435 -- Override certain qc tests values with spec test values
436 OPEN get_spec_tests(X_sampling_events(i).spec_id ,
437 p_inv_rslt_inp_rec.test_id);
438 FETCH get_spec_tests INTO l_min_value_num, l_max_value_num,
439 l_min_value_char,l_max_value_char,
440 l_display_precision;
441 CLOSE get_spec_tests;
442 END IF;
443
444 IF (l_test_type IN ('T','U','V')) THEN -- char test types
445 x_inv_rslt_out_rec.result_value := l_result_value_char;
446 x_inv_rslt_out_rec.Min_Value := l_min_value_char;
447 x_inv_rslt_out_rec.Max_Value := l_max_value_char;
448 ELSIF (l_test_type IN ('E','L','N')) THEN -- numeric test type
449 x_inv_rslt_out_rec.Min_Value := l_min_value_num;
450 x_inv_rslt_out_rec.Max_Value := l_max_value_num;
451
452 -- Setting the display precision
453 IF (l_Display_Precision IS NOT NULL) THEN
454 x_inv_rslt_out_rec.Display_precision := l_Display_Precision;
455 x_inv_rslt_out_rec.result_value :=
456 get_display_precision(l_result_value_num,
457 l_Display_Precision);
458 ELSE
459 x_inv_rslt_out_rec.result_value := l_result_value_num;
460 END IF;
461 END IF;
462
463 x_inv_rslt_out_rec.result_id := l_result_id;
464 x_inv_rslt_out_rec.Spec_id := X_sampling_events(i).spec_id;
465 x_inv_rslt_out_rec.composite_ind := 1;
466 l_row_num := i;
467 l_test_found := TRUE;
468 ELSE -- No Results were found
469 FND_MESSAGE.SET_NAME('GMD','GMD_RESULT_NOT_FOUND');
470 FND_MESSAGE.SET_TOKEN('SAMPLE_ID', X_sampling_events(i).sample_id);
471 FND_MESSAGE.SET_TOKEN('TEST_ID', p_inv_rslt_inp_rec.test_id);
472 FND_MSG_PUB.ADD;
473 END IF; -- geting simple results
474
475 CLOSE get_simple_results;
476 END IF; -- condition to check if simple or composite results
477 IF l_test_found THEN -- Either a simple or composite result was found
478 EXIT ;
479 END IF;
480 END LOOP; -- goes thro all sampling events
481
482 -- get the level
483 IF (l_row_num > 0) THEN
484 l_level_rec.organization_id := X_sampling_events(l_row_num).organization_id;
485 l_level_rec.inventory_item_id := X_sampling_events(l_row_num).inventory_item_id ;
486 l_level_rec.parent_lot_number := X_sampling_events(l_row_num).parent_lot_number ;
487 l_level_rec.lot_number := X_sampling_events(l_row_num).lot_number ;
488 l_level_rec.subinventory := X_sampling_events(l_row_num).subinventory;
489 l_level_rec.locator_id := X_sampling_events(l_row_num).locator_id ;
490
491 IF (l_debug = 'Y') THEN
492 gmd_debug.put_line('Getting the level for inventory item_id '
493 ||X_sampling_events(l_row_num).organization_id);
494 END IF;
495
496 GMD_QUALITY_GRP.get_level
497 ( p_inv_inp_rec => l_level_rec
498 , p_called_from => 'RESULT'
502
499 , x_level => x_inv_rslt_out_rec.level
500 , x_return_status => x_return_status
501 );
503 IF (l_debug = 'Y') THEN
504 gmd_debug.put_line('The return status from get_level '||x_return_status);
505 END IF;
506 END IF; -- when l_row_num > 0
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
511 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
512 END get_inv_result_test_value;
513
514
515 /* ************************************************************************ */
516 /* API name : get_appr_sampling_events */
517 /* Type : Private */
518 /* Function : */
519 /* Pre-reqs : None. */
520 /* Parameters : */
521 /* IN : p_inv_rslt_inp_rec IN inv_inp_rec_type (Required) */
522 /* */
523 /* OUT : x_return_status OUT VARCHAR2(1) */
524 /* : x_sampling_events_tbl OUT sampling_events_tbl_type */
525 /* */
526 /* Notes : Given the item, organization_id, parent_lot, lot, */
527 /* subinventory, locator_id and */
528 /* grade information this API gets the list of the sampling */
529 /* that have approved disposition. */
530 /* */
531 /* HISTORY */
532 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
533 /* 10-JUN-2005 Saikiran Vankadari Convergence Changes. Replaced all */
534 /* opm-inventory references with that of */
535 /* discrete inventory */
536 /* ************************************************************************ */
537 PROCEDURE get_appr_sampling_events
538 ( p_inv_rslt_inp_rec IN inv_inp_rec_type
539 , x_sampling_events_tbl OUT NOCOPY sampling_events_tbl_type
540 , x_return_status OUT NOCOPY VARCHAR2
541 )
542 IS
543 l_api_name VARCHAR2(100) := 'GET_APPR_SAMPLING_EVENTS';
544 i NUMBER := 0;
545
546 -- Cursor for selecting approved sampling events
547 CURSOR get_sample_events IS
548 SELECT se.sampling_event_id, se.sample_active_cnt, se.inventory_item_id,
549 se.parent_lot_number, se.lot_number, se.subinventory, se.locator_id,
550 sd.event_spec_disp_id, sd.spec_id
551 FROM gmd_sampling_events se,
552 gmd_event_spec_disp sd
553 WHERE se.sampling_event_id = sd.sampling_event_id
554 AND se.inventory_item_id = p_inv_rslt_inp_rec.inventory_item_id
555 AND ((se.parent_lot_number = p_inv_rslt_inp_rec.parent_lot_number)
556 OR (p_inv_rslt_inp_rec.parent_lot_number IS NULL AND se.parent_lot_number IS NULL)
557 OR (p_inv_rslt_inp_rec.parent_lot_number IS NOT NULL AND se.parent_lot_number IS NULL))
558 AND ((se.lot_number = p_inv_rslt_inp_rec.lot_number)
559 OR (p_inv_rslt_inp_rec.lot_number IS NULL AND se.lot_number IS NULL)
560 OR (p_inv_rslt_inp_rec.lot_number IS NOT NULL AND se.lot_number IS NULL))
561 AND ((se.subinventory = p_inv_rslt_inp_rec.subinventory)
562 OR (p_inv_rslt_inp_rec.subinventory IS NULL AND se.subinventory IS NULL)
563 OR (p_inv_rslt_inp_rec.subinventory IS NOT NULL AND se.subinventory IS NULL))
564 AND ((se.locator_id = p_inv_rslt_inp_rec.locator_id)
565 OR (p_inv_rslt_inp_rec.locator_id IS NULL AND se.locator_id IS NULL)
566 OR (p_inv_rslt_inp_rec.locator_id IS NOT NULL AND se.locator_id IS NULL))
567 AND sd.spec_used_for_lot_attrib_ind = 'Y'
568 -- B3698232 Added dispostion 'In-Progress' and 'Complete' for single sample
569 -- AND sd.disposition IN ('4A','5AV')
570 AND sd.disposition IN ('2I', '3C', '4A','5AV')
571 AND EXISTS (Select 1
572 From gmd_samples s, gmd_results r
573 Where s.sample_id = r.sample_id
574 AND r.test_id = p_inv_rslt_inp_rec.test_id
575 AND s.organization_id = p_inv_rslt_inp_rec.organization_id
576 AND s.delete_mark = 0
577 AND r.delete_mark = 0
578 AND s.sampling_event_id = se.sampling_event_id)
579 ORDER BY se.lot_number, se.parent_lot_number, se.subinventory, se.locator_id,
580 se.last_update_date desc;
581
582 CURSOR get_samples(vSampling_Event_Id NUMBER,
583 vEvent_spec_disp_id NUMBER) IS
584 SELECT smp.sample_id
585 FROM gmd_samples smp, gmd_sample_spec_disp sd
586 WHERE smp.sampling_event_id = vSampling_Event_Id
587 AND smp.organization_id = p_inv_rslt_inp_rec.organization_id
588 AND smp.sample_id = sd.sample_id
589 AND sd.event_spec_disp_id = vEvent_spec_disp_id
590 AND smp.delete_mark = 0
591 AND sd.disposition NOT IN ('0RT','7CN')
592 ORDER BY smp.last_update_date desc;
596
593
594 -- Exception Definition
595 No_Appr_Sample_evt_exp EXCEPTION;
597 BEGIN
598 -- Initialize API return status to success
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600 IF (l_debug = 'Y') THEN
601 gmd_debug.put_line('Begining get_appr_sampling_evnt proc, '
602 ||' inventory_item_id = '||p_inv_rslt_inp_rec.inventory_item_id
603 ||' organization_id = '||p_inv_rslt_inp_rec.organization_id
604 ||' test_id = '||p_inv_rslt_inp_rec.test_id
605 ||' parent_lot_number = '||p_inv_rslt_inp_rec.parent_lot_number
606 ||' lot_number = '||p_inv_rslt_inp_rec.lot_number);
607 END IF;
608 FOR sampling_rec IN get_sample_events LOOP
609 i := i + 1;
610 IF (l_debug = 'Y') THEN
611 gmd_debug.put_line('Before assigning values , i value = '||i);
612 END IF;
613 x_sampling_events_tbl(i).sampling_event_id := sampling_rec.sampling_event_id;
614 x_sampling_events_tbl(i).event_spec_disp_id := sampling_rec.event_spec_disp_id;
615 x_sampling_events_tbl(i).sample_active_cnt := sampling_rec.sample_active_cnt;
616 x_sampling_events_tbl(i).spec_id := sampling_rec.spec_id;
617 x_sampling_events_tbl(i).organization_id := p_inv_rslt_inp_rec.organization_id;
618 x_sampling_events_tbl(i).inventory_item_id := sampling_rec.inventory_item_id ;
619 x_sampling_events_tbl(i).parent_lot_number := sampling_rec.parent_lot_number ;
620 x_sampling_events_tbl(i).lot_number := sampling_rec.lot_number ;
621 x_sampling_events_tbl(i).subinventory := sampling_rec.subinventory ;
622 x_sampling_events_tbl(i).locator_id := sampling_rec.locator_id ;
623
624 -- Get the sample id
625 OPEN get_samples(x_sampling_events_tbl(i).sampling_event_id
626 ,x_sampling_events_tbl(i).event_spec_disp_id);
627 FETCH get_samples INTO x_sampling_events_tbl(i).sample_id;
628 CLOSE get_samples;
629 IF (l_debug = 'Y') THEN
630 gmd_debug.put_line('i value = '||i);
631 gmd_debug.put_line('The sample id = '||x_sampling_events_tbl(i).sample_id);
632 END IF;
633 END LOOP;
634
635 IF (i = 0) THEN
636 RAISE No_Appr_Sample_evt_exp;
637 END IF;
638
639 EXCEPTION
640 WHEN No_Appr_Sample_evt_exp THEN
641 FND_MESSAGE.SET_NAME('GMD','GMD_SAMPLING_EVENT_NOT_FOUND');
642 FND_MSG_PUB.ADD;
643 WHEN FND_API.G_EXC_ERROR THEN
644 x_return_status := FND_API.G_RET_STS_ERROR ;
645 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
647 WHEN OTHERS THEN
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
649 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
650 END get_appr_sampling_events;
651
652
653 /* ************************************************************************ */
654 /* API name : get_inv_spec_test_value */
655 /* Type : Private */
656 /* Function : */
657 /* Pre-reqs : None. */
658 /* Parameters : */
659 /* IN : p_inv_spec_inp_rec IN inv_inp_rec_type (Required) */
660 /* */
661 /* OUT : x_return_status OUT VARCHAR2(1) */
662 /* : x_inv_spec_out_rec OUT inv_rslt_out_rec_type */
663 /* */
664 /* x_inv_spec_out_rec.Spec_Id (Specification id) */
665 /* x_inv_spec_out_rec.Target_Value (Numeric or char target) */
666 /* x_inv_spec_out_rec.Min_Value (Spec test lower limit) */
667 /* x_inv_spec_out_rec.Max_Value (Spec test upper limit) */
668 /* x_inv_spec_out_rec.level (value representing inventory/lot */
669 /* specification tests */
670 /* */
671 /* Notes : Given the item, organization_id, parent_lot, lot, */
672 /* subinventory, locator_id and */
673 /* grade information this API gets the inventory spec value */
674 /* for a test. This API can be called independently to return*/
675 /* the inventory specs */
676 /* HISTORY */
677 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
678 /* 10-JUN-2005 Saikiran Vankadari Convergence Changes. Replaced all */
679 /* opm-inventory references with that of */
680 /* discrete inventory */
681 /* ************************************************************************ */
682 PROCEDURE get_inv_spec_test_value
683 ( p_inv_spec_inp_rec IN inv_inp_rec_type
684 , x_inv_spec_out_rec OUT NOCOPY inv_spec_out_rec_type
685 , x_return_status OUT NOCOPY VARCHAR2
686 )
687 IS
688 l_api_name VARCHAR2(100) := 'GET_INV_SPEC_TEST_VALUE';
689 l_inventory_spec_rec GMD_SPEC_MATCH_GRP.inventory_spec_rec_type;
693 x_msg_data VARCHAR2(2000);
690 l_level_rec GMD_QUALITY_GRP.inv_inp_rec_type;
691 x_spec_id NUMBER;
692 x_spec_vr_id NUMBER;
694
695 -- get the test_type from gmd_qc_tests to check if it is numeric/char type
696 CURSOR get_spec_test(vSpec_id NUMBER, vTest_id NUMBER) IS
697 SELECT st.target_value_char, st.target_value_num ,
698 st.min_value_char, st.min_value_num ,
699 st.max_value_char, st.max_value_num ,
700 qt.test_type, st.spec_id,
701 NVL(st.display_precision, qt.display_precision) display_precision
702 FROM gmd_spec_tests_b st, gmd_qc_tests_b qt
703 WHERE st.spec_id = vSpec_id
704 AND st.test_id = vTest_id
705 AND st.test_id = qt.test_id;
706
707 CURSOR get_spec_vr(vSpec_vr_id NUMBER) IS
708 SELECT organization_id, parent_lot_number, lot_number,
709 subinventory, locator_id
710 FROM gmd_inventory_spec_vrs
711 WHERE spec_vr_id = vSpec_vr_id;
712
713 BEGIN
714 -- Initialize API return status to success
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716
717 l_inventory_spec_rec.inventory_item_id := p_inv_spec_inp_rec.inventory_item_id;
718 l_inventory_spec_rec.grade_code := p_inv_spec_inp_rec.grade_code;
719 l_inventory_spec_rec.organization_id := p_inv_spec_inp_rec.organization_id;
720 l_inventory_spec_rec.parent_lot_number := p_inv_spec_inp_rec.parent_lot_number;
721 l_inventory_spec_rec.lot_number := p_inv_spec_inp_rec.lot_number;
722 l_inventory_spec_rec.subinventory := p_inv_spec_inp_rec.subinventory;
723 l_inventory_spec_rec.locator_id := p_inv_spec_inp_rec.locator_id ;
724 l_inventory_spec_rec.date_effective := SYSDATE;
725 l_inventory_spec_rec.exact_match := 'N' ;
726
727 -- get the inventory spec match
728 IF GMD_SPEC_MATCH_GRP.find_inventory_spec
729 ( p_inventory_spec_rec => l_inventory_spec_rec
730 , x_spec_id => x_spec_id
731 , x_spec_vr_id => x_spec_vr_id
732 , x_return_status => x_return_status
733 , x_message_data => x_msg_data) THEN
734
735 IF (l_debug = 'Y') THEN
736 gmd_debug.put_line('The spec and spec_vr id = '||x_spec_id||' x '||x_spec_vr_id);
737 END IF;
738
739 IF x_spec_id IS NOT NULL THEN
740 -- Spec test match is found
741 FOR get_spec_rec IN get_spec_test(x_Spec_id, p_inv_spec_inp_rec.test_id)
742 LOOP
743 x_inv_spec_out_rec.Spec_Id := get_spec_rec.spec_id;
744 IF (l_debug = 'Y') THEN
745 gmd_debug.put_line('The test type = '||get_spec_rec.test_type);
746 END IF;
747 IF get_spec_rec.test_type IN ('E','L','N') THEN -- numeric test types
748 IF (l_debug = 'Y') THEN
749 gmd_debug.put_line('The target_val_num = '||get_spec_rec.target_value_num);
750 END IF;
751 IF get_spec_rec.target_value_num IS NOT NULL THEN
752 x_inv_spec_out_rec.target_value := get_spec_rec.target_value_num;
753 ELSIF (get_spec_rec.max_value_num IS NOT NULL
754 AND get_spec_rec.min_value_num IS NOT NULL) THEN
755 x_inv_spec_out_rec.target_value
756 := (get_spec_rec.min_value_num + get_spec_rec.max_value_num)/2;
757 ELSIF get_spec_rec.min_value_num IS NOT NULL THEN
758 x_inv_spec_out_rec.target_value := get_spec_rec.min_value_num;
759 ELSIF get_spec_rec.max_value_num IS NOT NULL THEN
760 x_inv_spec_out_rec.target_value := get_spec_rec.max_value_num;
761 END IF;
762
763 -- setting the spec target value with the display precision
764 IF (get_spec_rec.display_precision IS NOT NULL) THEN
765 x_inv_spec_out_rec.display_precision := get_spec_rec.display_precision;
766 x_inv_spec_out_rec.target_value
767 := get_display_precision(
768 to_number(x_inv_spec_out_rec.target_value),
769 get_spec_rec.display_precision);
770 END IF;
771 x_inv_spec_out_rec.Min_Value := get_spec_rec.min_value_num;
772 x_inv_spec_out_rec.Max_Value := get_spec_rec.max_value_num;
773
774 ELSE -- character test types
775 IF (l_debug = 'Y') THEN
776 gmd_debug.put_line('The target_val_char = '||get_spec_rec.target_value_char);
777 END IF;
778 x_inv_spec_out_rec.target_value := get_spec_rec.target_value_char;
779 x_inv_spec_out_rec.Min_Value := get_spec_rec.min_value_char;
780 x_inv_spec_out_rec.Max_Value := get_spec_rec.max_value_char;
781 END IF;
782 END LOOP;
783
784 IF x_spec_vr_id IS NOT NULL THEN
785 FOR get_spec_vr_rec IN get_spec_vr(x_Spec_vr_id) LOOP
786 l_level_rec.organization_id := get_spec_vr_rec.organization_id;
787 l_level_rec.parent_lot_number := get_spec_vr_rec.parent_lot_number ;
788 l_level_rec.lot_number := get_spec_vr_rec.lot_number ;
789 l_level_rec.subinventory := get_spec_vr_rec.subinventory;
790 l_level_rec.locator_id := get_spec_vr_rec.locator_id ;
791 END LOOP;
792 END IF;
793
794 l_level_rec.inventory_item_Id := p_inv_spec_inp_rec.inventory_item_Id ;
795 l_level_rec.grade_code := p_inv_spec_inp_rec.grade_code ;
799 ( p_inv_inp_rec => l_level_rec
796
797 -- get the level
798 GMD_QUALITY_GRP.get_level
800 , p_called_from => 'TEST'
801 , x_level => x_inv_spec_out_rec.level
802 , x_return_status => x_return_status
803 );
804
805 END IF; -- If spec id is not null
806 ELSE
807 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_NOT_FOUND');
808 FND_MSG_PUB.ADD;
809 END IF; -- If the inventory spec match exists
810
811 EXCEPTION
812 WHEN FND_API.G_EXC_ERROR THEN
813 x_return_status := FND_API.G_RET_STS_ERROR ;
814 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
816 WHEN OTHERS THEN
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
818 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
819 END get_inv_spec_test_value;
820
821 /* ************************************************************************ */
822 /* API name : get_level */
823 /* Type : Private */
824 /* Function : */
825 /* Pre-reqs : None. */
826 /* Parameters : */
827 /* IN : p_inv_spec_inp_rec IN inv_inp_rec_type (Required) */
828 /* p_called_from IN VARCHAR2 (Required - either */
829 /* 'TEST' / 'RESULT') */
830 /* */
831 /* OUT : x_return_status OUT VARCHAR2(1) */
832 /* : x_level OUT NUMBER */
833 /* */
834 /* */
835 /* Notes : Given the item, organization_id, parent lot, lot, */
836 /* subinventory, locator_id and */
837 /* grade information this API gets the level i.e. it based */
838 /* based on the number returned (x_level) users can identify */
839 /* if the test value was based on results or specs and if it */
840 /* was inventory or lot or grade specific. More details on */
841 /* implecation of the value returned please refer to the QM */
842 /* Fetch API Detailed Design doc */
843 /* */
844 /* HISTORY */
845 /* 20-Feb-2003 Shyam Sitaraman Initial Implementation */
846 /* 10-JUN-2005 Saikiran Vankadari Convergence Changes. Replaced all */
847 /* opm-inventory references with that of */
848 /* discrete inventory */
849 /* ************************************************************************ */
850 PROCEDURE get_level
851 ( p_inv_inp_rec IN inv_inp_rec_type
852 , p_called_from IN VARCHAR2
853 , x_level OUT NOCOPY NUMBER
854 , x_return_status OUT NOCOPY VARCHAR2
855 )
856 IS
857 l_api_name VARCHAR2(100) := 'GET_LEVEL';
858 BEGIN
859 -- Initialize API return status to success
860 x_return_status := FND_API.G_RET_STS_SUCCESS;
861
862 IF (p_called_from = 'RESULT') THEN
863 -- When there is an exact match on Organization , inventory_item_id, lot_number, subinventory
864 -- and locator_id
865 IF(( p_inv_inp_rec.organization_id IS NOT NULL) AND
866 ( p_inv_inp_rec.lot_number IS NOT NULL) AND
867 ( p_inv_inp_rec.subinventory IS NOT NULL) AND
868 ( p_inv_inp_rec.locator_id IS NOT NULL)) THEN
869
870 x_Level := 1;
871
872 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
873 ( p_inv_inp_rec.parent_lot_number IS NOT NULL) AND
874 ( p_inv_inp_rec.subinventory IS NOT NULL) AND
875 ( p_inv_inp_rec.locator_id IS NOT NULL)) THEN
876
877 x_Level := 2;
878
879 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
880 ( p_inv_inp_rec.lot_number IS NOT NULL) AND
881 ( p_inv_inp_rec.subinventory IS NOT NULL)) THEN
882
883 x_Level := 3;
884
885 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
886 ( p_inv_inp_rec.parent_lot_number IS NOT NULL) AND
887 ( p_inv_inp_rec.subinventory IS NOT NULL)) THEN
888
889 x_Level := 4;
890
891 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
892 ( p_inv_inp_rec.lot_number IS NOT NULL)) THEN
893
894 x_Level := 5;
895
896 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
897 ( p_inv_inp_rec.parent_lot_number IS NOT NULL)) THEN
898
899 x_Level := 6;
900
901 ELSE
902 x_Level := 11;
903
904 END IF;
905 ELSIF (p_called_from = 'TEST') THEN
906 IF (p_inv_inp_rec.grade_code IS NOT NULL) THEN
907
908 IF(( p_inv_inp_rec.organization_id IS NOT NULL) AND
909 ( p_inv_inp_rec.lot_number IS NOT NULL) AND
910 ( p_inv_inp_rec.subinventory IS NOT NULL) AND
911 ( p_inv_inp_rec.locator_id IS NOT NULL)) THEN
912
913 x_Level := 21;
914
915 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
916 ( p_inv_inp_rec.parent_lot_number IS NOT NULL) AND
917 ( p_inv_inp_rec.subinventory IS NOT NULL) AND
918 ( p_inv_inp_rec.locator_id IS NOT NULL)) THEN
919
920 x_Level := 22;
921
922 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
923 ( p_inv_inp_rec.lot_number IS NOT NULL) AND
924 ( p_inv_inp_rec.subinventory IS NOT NULL)) THEN
925
926 x_Level := 23;
927
928 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
929 ( p_inv_inp_rec.parent_lot_number IS NOT NULL) AND
930 ( p_inv_inp_rec.subinventory IS NOT NULL)) THEN
931
932 x_Level := 24;
933
934 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
935 ( p_inv_inp_rec.lot_number IS NOT NULL)) THEN
936
937 x_Level := 25;
938
939 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
940 ( p_inv_inp_rec.parent_lot_number IS NOT NULL)) THEN
941
942 x_Level := 26;
943
944 ELSE
945 x_Level := 41;
946
947 END IF;
948
949 ELSE -- if p_inv_inp_rec.grade_code is null
950
951 IF(( p_inv_inp_rec.organization_id IS NOT NULL) AND
952 ( p_inv_inp_rec.lot_number IS NOT NULL) AND
953 ( p_inv_inp_rec.subinventory IS NOT NULL) AND
954 ( p_inv_inp_rec.locator_id IS NOT NULL)) THEN
955
956 x_Level := 31;
957
958 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
959 ( p_inv_inp_rec.parent_lot_number IS NOT NULL) AND
960 ( p_inv_inp_rec.subinventory IS NOT NULL) AND
961 ( p_inv_inp_rec.locator_id IS NOT NULL)) THEN
962
963 x_Level := 32;
964
965 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
966 ( p_inv_inp_rec.lot_number IS NOT NULL) AND
967 ( p_inv_inp_rec.subinventory IS NOT NULL)) THEN
968
969 x_Level := 33;
970
971 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
972 ( p_inv_inp_rec.parent_lot_number IS NOT NULL) AND
973 ( p_inv_inp_rec.subinventory IS NOT NULL)) THEN
974
975 x_Level := 34;
976
977 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
978 ( p_inv_inp_rec.lot_number IS NOT NULL)) THEN
979
980 x_Level := 35;
981
982 ELSIF (( p_inv_inp_rec.organization_id IS NOT NULL) AND
983 ( p_inv_inp_rec.parent_lot_number IS NOT NULL)) THEN
984
985 x_Level := 36;
986
987 ELSE
988 x_Level := 51;
989
990 END IF;
991 END IF; -- check if grade_code is null
992 END IF;
993 EXCEPTION
994 WHEN FND_API.G_EXC_ERROR THEN
995 x_return_status := FND_API.G_RET_STS_ERROR ;
996 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
998 WHEN OTHERS THEN
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1000 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,l_api_name);
1001 END get_level;
1002
1003 END GMD_QUALITY_GRP;
[Home] [Help]