[Home] [Help]
PACKAGE BODY: APPS.GMD_MASS_RESULTS_GRP
Source
1 PACKAGE BODY gmd_mass_results_grp AS
2 --$Header: GMDGMRSB.pls 120.2 2006/01/19 02:53:29 rlnagara noship $
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- Start of comments
7 --+==========================================================================+
8 --| Copyright (c) 1998 Oracle Corporation |
9 --| Redwood Shores, CA, USA |
10 --| All rights reserved. |
11 --+==========================================================================+
12 --| File Name : GMDGMRSB.pls |
13 --| Package Name : GMD_MASS_RESULTS_GRP |
14 --| Type : Group |
15 --| |
16 --| Notes |
17 --| This package contains group layer APIs for Mass Results Entity |
18 --| |
19 --| HISTORY |
20 --| Chetan Nagar 17-Jul-2003 Created. |
21 --| |
22 --+==========================================================================+
23 -- End of comments
24
25 -- Global variables
26 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_MASS_RESULTS_GRP';
27
28
29 --Start of comments
30 --+========================================================================+
31 --| API Name : populate_results |
32 --| TYPE : Group |
33 --| Notes : This procedure receives as input parameters, a seq_id. |
34 --| It extracts all the results and related data for the |
35 --| sample_ids associated with the SEQ_ID passed and populates
36 --| the table - GMD_MASS_RESULTS_GT. |
37 --| |
38 --| This erything is fine then OUT parameter |
39 --| x_return_status is set to 'S' else appropriate |
40 --| error message is put on the stack and error |
41 --| is returned. |
42 --| |
43 --| HISTORY |
44 --| Chetan Nagar 17-Jul-2003 Created. |
45 --| |
46 --| Chetan Nagar 08-Jan-2004 B3358725 Do not include archive and|
47 --| reserve sample for mass results. |
48 --| RLNAGARA 22-Sep-2005 Added Parent_lot_number |
49 --| Modified test_uom to test_qty_uom |
50 --| RLNAGARA 17-Jan-2006 Bug # 4913637 Split the INSERT statement into 2 |
51 --| INSERT statements so as to reduce |
52 --| the Shared Memry Size. |
53 --+========================================================================+
54 -- End of comments
55
56
57 PROCEDURE populate_results
58 ( p_seq_id IN VARCHAR2
59 , x_return_status OUT NOCOPY VARCHAR2
60 ) IS
61
62 -- Cursors
63 -- Local Variables
64 -- Exceptions
65
66 BEGIN
67
68 IF (l_debug = 'Y') THEN
69 gmd_debug.put_line('Entering procedure POPULATE_RESULTS');
70 gmd_debug.put_line(' Input Parameters:');
71 gmd_debug.put_line(' p_seq_id : ' || p_seq_id);
72 END IF;
73
74 -- Initialize API return status to success
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 -- First clear the TEMP table.
78 DELETE FROM gmd_mass_results_gt;
79
80 -- Now populate fresh
81
82 INSERT INTO gmd_mass_results_gt
83 (
84 -- Sampling Event Info
85 SAMPLING_EVENT_ID
86 , SAMPLE_ACTIVE_CNT
87
88 -- Sample Info
89 , SAMPLE_ID
90 , SAMPLE_NO
91 , SAMPLE_DESC
92 , INVENTORY_ITEM_ID
93 , LOCATOR_ID
94 , LOT_NUMBER
95 , PARENT_LOT_NUMBER
96 , SOURCE
97 , SUBINVENTORY
98 , ORGANIZATION_ID
99 , SOURCE_SUBINVENTORY
100 , SOURCE_LOCATOR_ID
101 , RESOURCES
102 , SAMPLE_TYPE
103
104 -- Result Info
105 , UPDATE_INSTANCE_ID
106 , RESULT_ID
107 , TEST_ID
108 , TEST_METHOD_ID
109 , TEST_REPLICATE_CNT
110 , TEST_QTY
111 , TEST_QTY_UOM
112 , LAB_ORGANIZATION_ID
113 , RESULT_VALUE_NUM
114 , RESULT_DATE
115 , TESTER_ID
116 , SEQ
117 , RESULT_VALUE_CHAR
118 , LAST_UPDATE_DATE
119
120 -- Spec Result Info
121 , EVALUATION_IND
122 , ACTION_CODE
123 , IN_SPEC_IND
124 , ADDITIONAL_TEST_IND
125 , VALUE_IN_REPORT_PRECISION
126
127 -- Event Info
128 , EVENT_SPEC_DISP_ID
129 , SPEC_ID
130 , SPEC_VR_ID
131
132 -- Spec Test Info
133 , MIN_VALUE_NUM
134 , TARGET_VALUE_NUM
135 , MAX_VALUE_NUM
136 , MIN_VALUE_CHAR
137 , TARGET_VALUE_CHAR
138 , MAX_VALUE_CHAR
139 , TEST_REPLICATE
140 , OUT_OF_SPEC_ACTION
141 , EXP_ERROR_TYPE
142 , BELOW_SPEC_MIN
143 , ABOVE_SPEC_MIN
144 , BELOW_SPEC_MAX
145 , ABOVE_SPEC_MAX
146 , BELOW_MIN_ACTION_CODE
147 , ABOVE_MIN_ACTION_CODE
148 , BELOW_MAX_ACTION_CODE
149 , ABOVE_MAX_ACTION_CODE
150 , OPTIONAL_IND
151 , DISPLAY_PRECISION
152 , REPORT_PRECISION
153
154 -- Test Info
155 , TEST_CODE
156 , TEST_DESC
157 , TEST_CLASS
158 , TEST_TYPE
159 , TEST_UNIT
160 , TEST_MIN_VALUE_NUM
161 , TEST_MAX_VALUE_NUM
162 , EXPRESSION
163
164 -- Test Method Info
165 , TEST_METHOD_CODE
166 , TEST_METHOD_DESC
167
168 -- Control Columns
169 , TEST_SELECTED
170 , UPDATE_ALLOWED
171 , RECORD_UPDATED
172 )
173 SELECT
174 -- Sampling Event Info
175 se.sampling_event_id,
176 se.sample_active_cnt,
177
178 --sampleinfo
179 s.sample_id,
180 s.sample_no,
181 s.sample_desc,
182 s.inventory_item_id,
183 s.locator_id,
184 s.lot_number,
185 s.parent_lot_number,
186 s.source,
187 s.subinventory,
188 s.organization_id,
189 s.source_subinventory,
190 s.source_locator_id,
191 s.resources,
192 s.sample_type,
193
194 --resultinfo
195 r.update_instance_id,
196 r.result_id,
197 r.test_id,
198 r.test_method_id,
199 r.test_replicate_cnt,
200 r.test_qty,
201 r.test_qty_uom,
202 r.lab_organization_id,
203 r.result_value_num,
204 r.result_date,
205 nvl(r.tester_id, fnd_global.user_id),
206 r.seq,
207 r.result_value_char,
208 r.last_update_date,
209
210 --spec result tinfo
211 sr.evaluation_ind,
212 sr.action_code,
213 sr.in_spec_ind,
214 sr.additional_test_ind,
215 sr.value_in_report_precision,
216
217 --event info
218 esd.event_spec_disp_id,
219 esd.spec_id,
220 esd.spec_vr_id,
221
222 --spec test info
223 st.min_value_num,
224 st.target_value_num,
225 st.max_value_num,
226 st.min_value_char,
227 st.target_value_char,
228 st.max_value_char,
229 st.test_replicate,
230 st.out_of_spec_action,
231 st.exp_error_type,
232 st.below_spec_min,
233 st.above_spec_min,
234 st.below_spec_max,
235 st.above_spec_max,
236 st.below_min_action_code,
237 st.above_min_action_code,
238 st.below_max_action_code,
239 st.above_max_action_code,
240 st.optional_ind,
241 st.display_precision,
242 st.report_precision,
243
244 --testinfo
245 t.test_code,
246 t.test_desc,
247 t.test_class,
248 t.test_type,
249 t.test_unit,
250 t.min_value_num test_min_value_num,
251 t.max_value_num test_max_value_num,
252 t.expression,
253
254 -- Test Methid Info
255 tm.test_method_code,
256 tm.test_method_desc,
257
258 -- Control Columns
259 0 TEST_SELECTED,
260 1 UPDATE_ALLOWED,
261 0 RECORD_UPDATED
262 FROM gmd_mass_samples ms,
263 gmd_results r,
264 gmd_spec_results sr,
265 gmd_samples s,
266 gmd_sample_spec_disp ssd,
267 gmd_sampling_events se,
268 gmd_event_spec_disp esd,
269 gmd_spec_tests_b st,
270 gmd_qc_tests t,
271 gmd_test_methods tm
272 WHERE ms.seq_id = p_seq_id
273 and ms.sample_id = s.sample_id
274
275 -- standard joins
276 and se.sampling_event_id = s.sampling_event_id
277 and s.sample_id = r.sample_id
278 and se.sampling_event_id = esd.sampling_event_id
279 and esd.spec_used_for_lot_attrib_ind = 'Y'
280 and esd.event_spec_disp_id = ssd.event_spec_disp_id
281 and ssd.sample_id = s.sample_id
282 and esd.event_spec_disp_id = sr.event_spec_disp_id
283 and sr.result_id = r.result_id
284 and sr.additional_test_ind IS NULL
285 and st.spec_id = esd.spec_id
286 and st.test_id = r.test_id
287 and st.exclude_ind is null
288 and t.test_id = r.test_id
289 and r.test_method_id = tm.test_method_id
290 and r.delete_mark = 0
291 and sr.delete_mark = 0
292 and s.delete_mark = 0
293 and ssd.delete_mark = 0
294 and esd.delete_mark = 0
295 -- system built filter criteria
296 and nvl(ssd.disposition, 'xx') in ('1P', '2I', '3C')
297 and nvl(sr.evaluation_ind, 'xx') not in ('4C', '5O')
298 and nvl(s.retain_as, 'X') not in ('A', 'R') ; -- B3358725
299
300 --RLNAGARA Bug # 4913637
301
302 INSERT INTO gmd_mass_results_gt
303 (
304 -- Sampling Event Info
305 SAMPLING_EVENT_ID
306 , SAMPLE_ACTIVE_CNT
307
308 -- Sample Info
309 , SAMPLE_ID
310 , SAMPLE_NO
311 , SAMPLE_DESC
312 , INVENTORY_ITEM_ID
313 , LOCATOR_ID
314 , LOT_NUMBER
315 , PARENT_LOT_NUMBER
316 , SOURCE
317 , SUBINVENTORY
318 , ORGANIZATION_ID
319 , SOURCE_SUBINVENTORY
320 , SOURCE_LOCATOR_ID
321 , RESOURCES
322 , SAMPLE_TYPE
323
324 -- Result Info
325 , UPDATE_INSTANCE_ID
326 , RESULT_ID
327 , TEST_ID
328 , TEST_METHOD_ID
329 , TEST_REPLICATE_CNT
330 , TEST_QTY
331 , TEST_QTY_UOM
332 , LAB_ORGANIZATION_ID
333 , RESULT_VALUE_NUM
334 , RESULT_DATE
335 , TESTER_ID
336 , SEQ
337 , RESULT_VALUE_CHAR
338 , LAST_UPDATE_DATE
339
340 -- Spec Result Info
341 , EVALUATION_IND
342 , ACTION_CODE
343 , IN_SPEC_IND
344 , ADDITIONAL_TEST_IND
345 , VALUE_IN_REPORT_PRECISION
346
347 -- Event Info
348 , EVENT_SPEC_DISP_ID
349 , SPEC_ID
350 , SPEC_VR_ID
351
352 -- Spec Test Info
353 , MIN_VALUE_NUM
354 , TARGET_VALUE_NUM
355 , MAX_VALUE_NUM
356 , MIN_VALUE_CHAR
357 , TARGET_VALUE_CHAR
358 , MAX_VALUE_CHAR
359 , TEST_REPLICATE
360 , OUT_OF_SPEC_ACTION
361 , EXP_ERROR_TYPE
362 , BELOW_SPEC_MIN
363 , ABOVE_SPEC_MIN
364 , BELOW_SPEC_MAX
365 , ABOVE_SPEC_MAX
366 , BELOW_MIN_ACTION_CODE
367 , ABOVE_MIN_ACTION_CODE
368 , BELOW_MAX_ACTION_CODE
369 , ABOVE_MAX_ACTION_CODE
370 , OPTIONAL_IND
371 , DISPLAY_PRECISION
372 , REPORT_PRECISION
373
374 -- Test Info
375 , TEST_CODE
376 , TEST_DESC
377 , TEST_CLASS
378 , TEST_TYPE
379 , TEST_UNIT
380 , TEST_MIN_VALUE_NUM
381 , TEST_MAX_VALUE_NUM
382 , EXPRESSION
383
384 -- Test Method Info
385 , TEST_METHOD_CODE
386 , TEST_METHOD_DESC
387
388 -- Control Columns
389 , TEST_SELECTED
390 , UPDATE_ALLOWED
391 , RECORD_UPDATED
392 )
393 SELECT
394 -- Sampling Event Info
395 se.sampling_event_id,
396 se.sample_active_cnt,
397
398 --sampleinfo
399 s.sample_id,
400 s.sample_no,
401 s.sample_desc,
402 s.inventory_item_id,
403 s.locator_id,
404 s.lot_number,
405 s.parent_lot_number,
406 s.source,
407 s.subinventory,
408 s.organization_id,
409 s.source_subinventory,
410 s.source_locator_id,
411 s.resources,
412 s.sample_type,
413
414 --resultinfo
415 r.update_instance_id,
416 r.result_id,
417 r.test_id,
418 r.test_method_id,
419 r.test_replicate_cnt,
420 r.test_qty,
421 r.test_qty_uom,
422 r.lab_organization_id,
423 r.result_value_num,
424 r.result_date,
425 nvl(r.tester_id, fnd_global.user_id),
426 r.seq,
427 r.result_value_char,
428 r.last_update_date,
429
430 --spec result tinfo
431 sr.evaluation_ind,
432 sr.action_code,
433 sr.in_spec_ind,
434 sr.additional_test_ind,
435 sr.value_in_report_precision,
436
437 --event info
438 esd.event_spec_disp_id,
439 esd.spec_id,
440 esd.spec_vr_id,
441
442 --spec test info SINCE THIS IS additional_test READ FROM TEST TABLE
443 t.min_value_num,
444 to_number(NULL) target_value_num,
445 t.max_value_num,
446 NULL min_value_char,
447 NULL target_value_char,
448 NULL max_value_char,
449 1, -- Need to read from Test method
450 NULL out_of_spec_action,
451 t.exp_error_type,
452 t.below_spec_min,
453 t.above_spec_min,
454 t.below_spec_max,
455 t.above_spec_max,
456 t.below_min_action_code,
457 t.above_min_action_code,
458 t.below_max_action_code,
459 t.above_max_action_code,
460 NULL optional_ind,
461 t.display_precision,
462 t.report_precision,
463
464 --testinfo
465 t.test_code,
466 t.test_desc,
467 t.test_class,
468 t.test_type,
469 t.test_unit,
470 t.min_value_num test_min_value_num,
471 t.max_value_num test_max_value_num,
472 t.expression,
473
474 -- Test Methid Info
475 tm.test_method_code,
476 tm.test_method_desc,
477
478 -- Control Columns
479 0 TEST_SELECTED,
480 1 UPDATE_ALLOWED,
481 0 RECORD_UPDATED
482 FROM gmd_mass_samples ms,
483 gmd_results r,
484 gmd_spec_results sr,
485 gmd_samples s,
486 gmd_sample_spec_disp ssd,
487 gmd_sampling_events se,
488 gmd_event_spec_disp esd,
489 gmd_qc_tests t,
490 gmd_test_methods tm
491 WHERE ms.seq_id = p_seq_id
492 and ms.sample_id = s.sample_id
493
494 -- standard joins
495 and se.sampling_event_id = s.sampling_event_id
496 and s.sample_id = r.sample_id
497 and se.sampling_event_id = esd.sampling_event_id
498 and esd.spec_used_for_lot_attrib_ind = 'Y'
499 and esd.event_spec_disp_id = ssd.event_spec_disp_id
500 and ssd.sample_id = s.sample_id
501 and esd.event_spec_disp_id = sr.event_spec_disp_id
502 and sr.result_id = r.result_id
503 and sr.additional_test_ind = 'Y'
504 and t.test_id = r.test_id
505 and r.test_method_id = tm.test_method_id
506 and r.delete_mark = 0
507 and sr.delete_mark = 0
508 and s.delete_mark = 0
509 and ssd.delete_mark = 0
510 and esd.delete_mark = 0
511 -- system built filter criteria
512 and nvl(ssd.disposition, 'xx') in ('1P', '2I', '3C')
513 and nvl(sr.evaluation_ind, 'xx') not in ('4C', '5O')
514 and nvl(s.retain_as, 'X') not in ('A', 'R'); -- B3358725
515
516
517
518 IF (l_debug = 'Y') THEN
519 gmd_debug.put_line(' No. of rows inserted into GMD_MASS_RESULTS_GT : ' || SQL%ROWCOUNT);
520 END IF;
521
522 UPDATE gmd_mass_results_gt
523 SET update_allowed = 0
524 WHERE (result_date IS NOT NULL OR test_type = 'E')
525 ;
526
527 IF (l_debug = 'Y') THEN
528 gmd_debug.put_line(' No. of rows with Result or Expression Test : ' || SQL%ROWCOUNT);
529 END IF;
530
531 IF (l_debug = 'Y') THEN
532 GMD_MASS_RESULTS_GRP.dump_data_points;
533 gmd_debug.put_line('Leaving procedure POPULATE_RESULTS');
534 END IF;
535
536
537 -- All systems GO...
538
539 EXCEPTION
540 WHEN FND_API.G_EXC_ERROR THEN
541 x_return_status := FND_API.G_RET_STS_ERROR ;
542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
544 WHEN OTHERS THEN
545 GMD_API_PUB.Log_Message('GMD_API_ERROR',
546 'PACKAGE','POPULATE_RESULTS',
547 'ERROR', SUBSTR(SQLERRM,1,100));
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
549
550 END populate_results;
551
552
553
554 PROCEDURE dump_data_points
555 ( p_sample_id IN NUMBER := NULL
556 , p_result_id IN NUMBER := NULL
557 , p_test_id IN NUMBER := NULL
558 ) IS
559
560 CURSOR c1 IS
561 SELECT *
562 FROM gmd_mass_results_gt
563 WHERE sample_id = nvl(p_sample_id, sample_id)
564 AND result_id = nvl(p_result_id, result_id)
565 AND test_id = nvl(test_id, p_test_id)
566 ORDER BY sample_id, result_id;
567
568 BEGIN
569 IF (l_debug = 'Y') THEN
570 gmd_debug.put_line('');
571 gmd_debug.put_line('Data in session table - gmd_mass_results_gt');
572 gmd_debug.put_line(' Sample ID Result ID Test ID Data Num Data Char UA RU');
573 gmd_debug.put_line('---------- ---------- ---------- --------------- --------------- -- --');
574 END IF;
575
576 FOR c_rec IN c1
577 LOOP
578 IF (l_debug = 'Y') THEN
579 gmd_debug.put_line(lpad(c_rec.sample_id, 10, ' ')||' '||
580 lpad(c_rec.result_id, 10, ' ')||' '||
581 lpad(c_rec.test_id, 10, ' ')||' '||
582 lpad(nvl(c_rec.result_value_num, 0), 15, ' ')||' '||
583 lpad(nvl(c_rec.result_value_char, 'NULL'), 15, ' ')|| ' ' ||
584 lpad(c_rec.update_allowed, 2, ' ') || ' ' ||
585 lpad(c_rec.record_updated, 2, ' ')
586 );
587 END IF;
588 END LOOP;
589
590
591 END dump_data_points;
592
593 END gmd_mass_results_grp;