1 PACKAGE BODY GMD_DISP_PUB AS
2 /* $Header: GMDPDISB.pls 120.1 2009/06/08 17:10:20 plowe noship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDPDISB.pls |
11 --| Package Name : GMD_DISP_PUB |
12 --| Type : Public |
13 --| |
14 --| Notes |
15 --| This package contains public layer APIs for Changing the disposition |
16 --| of a Sample/Group |
17 --| |
18 --| HISTORY |
19 --| Ravi Lingappa Nagaraja 16-Jun-2008 Created |
20 --+==========================================================================+
21 -- End of comments
22
23 FUNCTION set_debug_flag RETURN VARCHAR2;
24
25 l_debug VARCHAR2(1) := set_debug_flag;
26
27 FUNCTION set_debug_flag RETURN VARCHAR2 IS
28 l_debug VARCHAR2(1):= 'N';
29 BEGIN
30 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
31 l_debug := 'Y';
32 END IF;
33 RETURN l_debug;
34 END set_debug_flag;
35
36 /*=============================================================================
37 Start Forward Declaration
38 =============================================================================*/
39
40 FUNCTION get_current_event_spec_disp_id
41 (
42 p_id IN NUMBER
43 ,p_is_sample_id IN VARCHAR2
44 ) RETURN NUMBER;
45
46 PROCEDURE validate_parentlot_lot (
47 p_inventory_item_id IN NUMBER
48 ,p_organization_id IN NUMBER
49 ,p_parent_lot_number IN VARCHAR2
50 ,p_lot_number IN VARCHAR2
51 ,x_return_status OUT NOCOPY VARCHAR2
52 ,x_msg_count OUT NOCOPY NUMBER
53 ,x_msg_data OUT NOCOPY VARCHAR2);
54
55 PROCEDURE validate_lpn (
56 p_inventory_item_id IN NUMBER
57 ,p_organization_id IN NUMBER
58 ,p_lot_number IN VARCHAR2
59 ,p_lpn_id IN NUMBER
60 ,p_lpn IN VARCHAR2
61 ,x_lpn_id OUT NOCOPY NUMBER
62 ,x_lpn OUT NOCOPY VARCHAR2
63 ,x_return_status OUT NOCOPY VARCHAR2
64 ,x_msg_count OUT NOCOPY NUMBER
65 ,x_msg_data OUT NOCOPY VARCHAR2);
66
67 PROCEDURE validate_reason_code (
68 p_reason_code IN VARCHAR2
69 ,x_reason_id OUT NOCOPY NUMBER
70 ,x_return_status OUT NOCOPY VARCHAR2
71 ,x_msg_count OUT NOCOPY NUMBER
72 ,x_msg_data OUT NOCOPY VARCHAR2
73 );
74
75 PROCEDURE populate_hold_date (
76 p_parent_lot_number IN VARCHAR2
77 ,p_lot_number IN VARCHAR2
78 ,p_inventory_item_id IN NUMBER
79 ,p_organization_id IN NUMBER
80 ,x_lot_created OUT NOCOPY DATE
81 ,x_hold_date IN OUT NOCOPY DATE);
82
83 PROCEDURE validate_lot_grade_status (
84 p_is_lot IN VARCHAR2
85 ,p_status IN VARCHAR2
86 ,x_return_status OUT NOCOPY VARCHAR2
87 ,x_msg_count OUT NOCOPY NUMBER
88 ,x_msg_data OUT NOCOPY VARCHAR2
89 );
90
91 PROCEDURE Validate_disp (
92 p_update_disp_rec IN GMD_SAMPLES_GRP.update_disp_rec
93 ,p_to_disposition IN VARCHAR2
94 ,x_return_status OUT NOCOPY VARCHAR2
95 ,x_msg_count OUT NOCOPY NUMBER
96 ,x_msg_data OUT NOCOPY VARCHAR2
97 );
98
99 /*=============================================================================
100 End Forward Declaration
101 =============================================================================*/
102
103
104 /*=============================================================================
105 Function to get the event_spec_disp_id
106 =============================================================================*/
107 FUNCTION get_current_event_spec_disp_id
108 (
109 p_id IN NUMBER
110 ,p_is_sample_id IN VARCHAR2
111 ) RETURN NUMBER IS
112
113 -- Cursors
114 CURSOR c_event_disp_sm(p_sample_id NUMBER) IS
115 SELECT event_spec_disp_id
116 FROM gmd_samples gs, gmd_event_spec_disp ge
117 WHERE gs.sampling_event_id = ge.sampling_event_id
118 AND ge.spec_used_for_lot_attrib_ind = 'Y'
119 AND gs.sample_id = p_id
120 AND ge.delete_mark = 0
121 ;
122
123 CURSOR c_event_disp_se(p_sample_id NUMBER) IS
124 SELECT DISTINCT event_spec_disp_id
125 FROM gmd_samples gs, gmd_event_spec_disp ge
126 WHERE gs.sampling_event_id = ge.sampling_event_id
127 AND gs.sampling_event_id = p_id
128 AND ge.spec_used_for_lot_attrib_ind = 'Y'
129 AND ge.delete_mark = 0
130 ;
131 -- Local Variables
132 l_dummy NUMBER(15);
133
134 BEGIN
135
136 IF p_is_sample_id = 'Y' THEN
137 OPEN c_event_disp_sm(p_id);
138 FETCH c_event_disp_sm INTO l_dummy;
139 CLOSE c_event_disp_sm;
140 ELSE
141 OPEN c_event_disp_se(p_id);
142 FETCH c_event_disp_se INTO l_dummy;
143 CLOSE c_event_disp_se;
144 END IF;
145
146 RETURN l_dummy;
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 RETURN NULL;
151
152 END get_current_event_spec_disp_id;
153
154 /*=============================================================================
155 Procedure to validate the parent lot and lot.
156 =============================================================================*/
157 PROCEDURE validate_parentlot_lot (
158 p_inventory_item_id IN NUMBER
159 ,p_organization_id IN NUMBER
160 ,p_parent_lot_number IN VARCHAR2
161 ,p_lot_number IN VARCHAR2
162 ,x_return_status OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164 ,x_msg_data OUT NOCOPY VARCHAR2
165 ) IS
166
167 ITEM_PARENTLOT_NOT_FOUND EXCEPTION;
168 ITEM_LOT_NOT_FOUND EXCEPTION;
169
170 CURSOR c_item_parentlot IS
171 SELECT 1
172 FROM mtl_lot_numbers
173 WHERE inventory_item_id = p_inventory_item_id
174 AND organization_id = p_organization_id
175 AND parent_lot_number = p_parent_lot_number;
176
177 CURSOR c_item_lot IS
178 SELECT 1
179 FROM mtl_lot_numbers
180 WHERE inventory_item_id = p_inventory_item_id
181 AND organization_id = p_organization_id
182 AND parent_lot_number = p_parent_lot_number
183 AND lot_number = p_lot_number;
184
185 l_dummy NUMBER(15);
186
187 BEGIN
188
189 -- Initialize API return status to success
190 x_return_status := FND_API.G_RET_STS_SUCCESS;
191
192 IF (p_parent_lot_number IS NOT NULL) THEN
193 OPEN c_item_parentlot;
194 FETCH c_item_parentlot INTO l_dummy;
195 IF c_item_parentlot%NOTFOUND THEN
196 CLOSE c_item_parentlot;
197 RAISE ITEM_PARENTLOT_NOT_FOUND;
198 END IF;
199 CLOSE c_item_parentlot;
200 END IF;
201
202 IF (p_lot_number IS NOT NULL) THEN
203 OPEN c_item_lot;
204 FETCH c_item_lot INTO l_dummy;
205 IF c_item_lot%NOTFOUND THEN
206 CLOSE c_item_lot;
207 RAISE ITEM_LOT_NOT_FOUND;
208 END IF;
209 CLOSE c_item_lot;
210 END IF;
211
212 EXCEPTION
213
214 WHEN ITEM_PARENTLOT_NOT_FOUND THEN
215 gmd_api_pub.log_message('GMD_ITEM_PARENTLOT_NOT_FOUND');
216 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
217 x_return_status := FND_API.G_RET_STS_ERROR ;
218 WHEN ITEM_LOT_NOT_FOUND THEN
219 gmd_api_pub.log_message('GMD_ITEM_LOT_NOT_FOUND');
220 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
221 x_return_status := FND_API.G_RET_STS_ERROR ;
222 WHEN OTHERS THEN
223 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_DISP_PUB.validate_parentlot_lot','ERROR', SUBSTR(SQLERRM,1,100));
224 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
226 END validate_parentlot_lot;
227
228 /*=============================================================================
229 Procedure to validate the lpn value.
230 =============================================================================*/
231 PROCEDURE validate_lpn (
232 p_inventory_item_id IN NUMBER
233 ,p_organization_id IN NUMBER
234 ,p_lot_number IN VARCHAR2
235 ,p_lpn_id IN NUMBER
236 ,p_lpn IN VARCHAR2
237 ,x_lpn_id OUT NOCOPY NUMBER
238 ,x_lpn OUT NOCOPY VARCHAR2
239 ,x_return_status OUT NOCOPY VARCHAR2
240 ,x_msg_count OUT NOCOPY NUMBER
241 ,x_msg_data OUT NOCOPY VARCHAR2
242 ) IS
243
244 ITEM_LPN_NOT_FOUND EXCEPTION;
245
246 CURSOR c_item_lpn_id IS
247 SELECT distinct wlpn.lpn_id, wlpn.license_plate_number
248 FROM wms_license_plate_numbers wlpn,
249 wms_lpn_contents wlc
250 WHERE wlpn.lpn_id = wlc.parent_lpn_id
251 AND wlpn.organization_id = p_organization_id
252 AND wlpn.parent_lpn_id is null
253 AND wlc.inventory_item_id = p_inventory_item_id
254 AND (wlc.lot_number = p_lot_number or p_lot_number is null)
255 AND wlpn.lpn_id = x_lpn_id;
256
257 CURSOR c_item_lpn IS
258 SELECT distinct wlpn.lpn_id, wlpn.license_plate_number
259 FROM wms_license_plate_numbers wlpn,
260 wms_lpn_contents wlc
261 WHERE wlpn.lpn_id = wlc.parent_lpn_id
262 AND wlpn.organization_id = p_organization_id
263 AND wlpn.parent_lpn_id is null
264 AND wlc.inventory_item_id = p_inventory_item_id
265 AND (wlc.lot_number = p_lot_number or p_lot_number is null)
266 AND wlpn.license_plate_number = x_lpn;
267
268 l_dummy NUMBER(15);
269
270 BEGIN
271
272 -- Initialize API return status to success
273 x_return_status := FND_API.G_RET_STS_SUCCESS;
274
275 IF (x_lpn_id IS NOT NULL) THEN
276 OPEN c_item_lpn_id;
277 FETCH c_item_lpn_id INTO x_lpn_id, x_lpn;
278 IF c_item_lpn_id%NOTFOUND THEN
279 CLOSE c_item_lpn_id;
280 RAISE ITEM_LPN_NOT_FOUND;
281 END IF;
282 CLOSE c_item_lpn_id;
283 ELSIF (x_lpn IS NOT NULL) THEN
284 OPEN c_item_lpn;
285 FETCH c_item_lpn INTO x_lpn_id, x_lpn;
286 IF c_item_lpn%NOTFOUND THEN
287 CLOSE c_item_lpn;
288 RAISE ITEM_LPN_NOT_FOUND;
289 END IF;
290 CLOSE c_item_lpn;
291 END IF;
292
293 EXCEPTION
294
295 WHEN ITEM_LPN_NOT_FOUND THEN
296 gmd_api_pub.log_message('GMD_ITEM_LPN_NOT_FOUND');
297 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
298 x_return_status := FND_API.G_RET_STS_ERROR ;
299 WHEN OTHERS THEN
300 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_DISP_PUB.validate_lpn','ERROR', SUBSTR(SQLERRM,1,100));
301 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
303 END validate_lpn;
304
305 /*=============================================================================
306 Procedure to validate the reason_code
307 =============================================================================*/
308 PROCEDURE validate_reason_code (
309 p_reason_code IN VARCHAR2
310 ,x_reason_id OUT NOCOPY NUMBER
311 ,x_return_status OUT NOCOPY VARCHAR2
312 ,x_msg_count OUT NOCOPY NUMBER
313 ,x_msg_data OUT NOCOPY VARCHAR2
314 ) IS
315
316 CURSOR c_reason_code IS
317 SELECT reason_id
318 FROM mtl_transaction_reasons
319 WHERE NVL(disable_date, SYSDATE) >= SYSDATE
320 AND reason_name = p_reason_code;
321
322 l_dummy VARCHAR2(4);
323 INVALID_REASON_CODE EXCEPTION;
324
325 BEGIN
326
327 -- Initialize API return status to success
328 x_return_status := FND_API.G_RET_STS_SUCCESS; --Bug#5752786
329 IF p_reason_code IS NOT NULL THEN
330 OPEN c_reason_code;
331 FETCH c_reason_code INTO x_reason_id;
332 IF c_reason_code%NOTFOUND THEN
333 CLOSE c_reason_code;
334 RAISE INVALID_REASON_CODE;
335 END IF;
336 CLOSE c_reason_code;
337 END IF;
338 EXCEPTION
339
340 WHEN INVALID_REASON_CODE THEN
341 gmd_api_pub.log_message('GMD_INVALID_REASON_CODE');
342 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
344 WHEN OTHERS THEN
345 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_DISP_PUB.validate_reason_code','ERROR', SUBSTR(SQLERRM,1,100));
346 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
348
349 END validate_reason_code;
350
351 /*=============================================================================
352 Procedure to populate the hold date
353 =============================================================================*/
354 PROCEDURE populate_hold_date (
355 p_parent_lot_number IN VARCHAR2
356 ,p_lot_number IN VARCHAR2
357 ,p_inventory_item_id IN NUMBER
358 ,p_organization_id IN NUMBER
359 ,x_lot_created OUT NOCOPY DATE
360 ,x_hold_date IN OUT NOCOPY DATE)
361 IS
362
363 CURSOR Cur_lot_data IS
364 SELECT creation_date, hold_date
365 FROM mtl_lot_numbers
366 WHERE inventory_item_id = p_inventory_item_id
367 AND organization_id = p_organization_id
368 AND lot_number = p_lot_number
369 AND ((p_parent_lot_number IS NULL)
370 OR (parent_lot_number = p_parent_lot_number));
371
372 BEGIN
373
374 IF (p_lot_number IS NOT NULL AND p_parent_lot_number IS NOT NULL) THEN
375 OPEN Cur_lot_data;
376 FETCH Cur_lot_data INTO x_lot_created, x_hold_date;
377 CLOSE Cur_lot_data;
378 ELSIF (p_lot_number IS NOT NULL) THEN
379 SELECT max(creation_date)
380 INTO x_lot_created
381 FROM mtl_lot_numbers
382 WHERE lot_number = p_lot_number
383 AND organization_id = p_organization_id
384 AND inventory_item_id = p_inventory_item_id;
385
386 SELECT max(hold_date)
387 INTO x_hold_date
388 FROM mtl_lot_numbers
389 WHERE lot_number = p_lot_number
390 AND organization_id = p_organization_id
391 AND inventory_item_id = p_inventory_item_id;
392 END IF;
393
394 EXCEPTION
395 --No data found should not occur
396 WHEN NO_DATA_FOUND THEN
397 NULL;
398 END populate_hold_date;
399
400 /*=============================================================================
401 Procedure to validate lot_status and grade
402 =============================================================================*/
403 PROCEDURE validate_lot_grade_status (
404 p_is_lot IN VARCHAR2
405 ,p_status IN VARCHAR2
406 ,x_return_status OUT NOCOPY VARCHAR2
407 ,x_msg_count OUT NOCOPY NUMBER
408 ,x_msg_data OUT NOCOPY VARCHAR2
409 ) IS
410
411 CURSOR cr_lot_status IS
412 SELECT status_id, status_code
413 FROM mtl_material_statuses
414 WHERE status_code = p_status
415 AND enabled_flag = 1;
416
417 CURSOR cr_grade IS
418 SELECT grade_code
419 FROM mtl_grades
420 WHERE grade_code = p_status
421 AND disable_flag = 'N';
422
423 l_dummy VARCHAR2(4);
424 l_dummy1 NUMBER;
425
426 INVALID_LOT_STATUS EXCEPTION;
427 INVALID_GRADE EXCEPTION;
428
429 BEGIN
430
431 -- Initialize API return status to success
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433
434 IF p_is_lot = 'Y' THEN
435 OPEN cr_lot_status;
436 FETCH cr_lot_status INTO l_dummy1,l_dummy;
437 IF cr_lot_status%NOTFOUND THEN
438 CLOSE cr_lot_status;
439 RAISE INVALID_LOT_STATUS;
440 END IF;
441 CLOSE cr_lot_status;
442 ELSE
443 OPEN cr_grade;
444 FETCH cr_grade INTO l_dummy;
445 IF cr_grade%NOTFOUND THEN
446 CLOSE cr_grade;
447 RAISE INVALID_GRADE;
448 END IF;
449 CLOSE cr_grade;
450 END IF;
451
452 EXCEPTION
453 WHEN INVALID_LOT_STATUS THEN
454 gmd_api_pub.log_message('GMD_INVALID_LOT_STATUS');
455 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 WHEN INVALID_GRADE THEN
458 gmd_api_pub.log_message('GMD_INVALID_GRADE');
459 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
461 WHEN OTHERS THEN
462 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_DISP_PUB.validate_lot_grade_status','ERROR', SUBSTR(SQLERRM,1,100));
463 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
465
466 END validate_lot_grade_status;
467
468 /*=============================================================================
469 Procedure to validate the to_disposition of the sample
470 =============================================================================*/
471 PROCEDURE Validate_disp (
472 p_update_disp_rec IN GMD_SAMPLES_GRP.update_disp_rec
473 ,p_to_disposition IN VARCHAR2
474 ,x_return_status OUT NOCOPY VARCHAR2
475 ,x_msg_count OUT NOCOPY NUMBER
476 ,x_msg_data OUT NOCOPY VARCHAR2
477 ) IS
478
479 l_msg_count NUMBER :=0;
480 l_message_data VARCHAR2(2000);
481 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
482
483 quality_config GMD_QUALITY_CONFIG%ROWTYPE;
484
485 l_non_accept_w_spec NUMBER(5);
486 l_non_accept_wo_spec NUMBER(5);
487 l_non_reject_w_spec NUMBER(5);
488 l_non_reject_wo_spec NUMBER(5);
489 l_reject_w_spec_no_av NUMBER(5);
490 l_reject_wo_spec_no_av NUMBER(5);
491
492 l_def_reject NUMBER(1) := 0;
493 l_include_optional VARCHAR2(1);
494 l_organization_id NUMBER;
495 org_found BOOLEAN;
496
497 l_test_count NUMBER(4) := 0;
498 l_disp_count NUMBER(2) := 1;
499 l_in_spec_count NUMBER(4) := 0;
500 l_out_spec_count NUMBER(4) := 0;
501
502 INVALID_ORG_PARAM EXCEPTION;
503 INVALID_DISPOSITION EXCEPTION;
504 NO_TESTS_CMP EXCEPTION;
505 INVALID_PARAMETERS EXCEPTION;
506 l_pos VARCHAR2(10);
507
508 CURSOR c_non_accept_w_spec IS
509 SELECT count(1)
510 FROM gmd_event_spec_disp esd,
511 gmd_results r,
512 gmd_spec_results sr,
513 gmd_spec_tests_b st
514 WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
515 AND esd.event_spec_disp_id = sr.event_spec_disp_id
516 AND sr.result_id = r.result_id
517 AND r.sample_id = p_update_disp_rec.sample_id
518 AND (sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('0A', '4C', '5O'))
519 AND r.delete_mark = 0
520 AND sr.delete_mark = 0
521 AND esd.spec_id = st.spec_id
522 AND st.test_id = r.test_id
523 AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR (l_include_optional = 'Y'));
524
525 CURSOR c_non_accept_wo_spec IS
526 SELECT count(1)
527 FROM gmd_results r, gmd_spec_results sr
528 WHERE sr.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
529 AND sr.result_id = r.result_id
530 AND r.sample_id = p_update_disp_rec.sample_id
531 AND sr.additional_test_ind = 'Y'
532 AND (sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('0A', '4C', '5O'))
533 AND r.delete_mark = 0
534 AND sr.delete_mark = 0;
535
536 CURSOR cr_check_comp_results_in_spec IS
537 SELECT COUNT(1)
538 FROM gmd_composite_results
539 WHERE composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
540 AND delete_mark = 0 ;
541
542 CURSOR c_non_accept_w_spec_cr IS
543 SELECT count(1)
544 FROM gmd_event_spec_disp esd, gmd_composite_spec_disp csd,
545 gmd_composite_results cr, gmd_spec_tests_b st
546 WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
547 AND csd.event_spec_disp_id = esd.event_spec_disp_id
548 AND csd.latest_ind = 'Y'
549 AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
550 AND cr.in_spec_ind IS NULL
551 AND st.spec_id = esd.spec_id
552 AND st.test_id = cr.test_id
553 AND (st.optional_ind IS NULL OR (l_include_optional = 'Y' and st.optional_ind = 'Y' and (cr.mean IS NOT NULL or cr.mode_char IS NOT NULL)));
554
555 CURSOR c_non_accept_wo_spec_cr IS
556 SELECT count(1)
557 FROM gmd_event_spec_disp esd, gmd_composite_spec_disp csd,
558 gmd_composite_results cr
559 WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
560 AND csd.event_spec_disp_id = esd.event_spec_disp_id
561 AND csd.latest_ind = 'Y'
562 AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
563 AND cr.in_spec_ind IS NULL
564 AND cr.test_id NOT IN (SELECT st.test_id FROM gmd_spec_tests_b st WHERE st.spec_id = esd.spec_id);
565
566 CURSOR c_non_reject_w_spec IS
567 SELECT count(1)
568 FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
569 gmd_spec_tests_b st
570 WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
571 AND esd.event_spec_disp_id = sr.event_spec_disp_id
572 AND sr.result_id = r.result_id
573 AND r.sample_id = p_update_disp_rec.sample_id
574 AND ((sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('2R')) OR sr.in_spec_ind = 'Y' )
575 AND r.delete_mark = 0
576 AND sr.delete_mark = 0
577 AND esd.spec_id = st.spec_id
578 AND st.test_id = r.test_id ;
579
580 CURSOR c_non_reject_wo_spec IS
581 SELECT count(1)
582 FROM gmd_results r, gmd_spec_results sr
583 WHERE sr.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
584 AND sr.result_id = r.result_id
585 AND r.sample_id = p_update_disp_rec.sample_id
586 AND sr.additional_test_ind = 'Y'
587 AND (sr.evaluation_ind IS NULL OR sr.evaluation_ind NOT IN ('2R'))
588 AND r.delete_mark = 0
589 AND sr.delete_mark = 0;
590
591 CURSOR c_reject_w_spec_no_av IS
592 SELECT count(1)
593 FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
594 gmd_spec_tests_b st
595 WHERE esd.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
596 AND esd.event_spec_disp_id = sr.event_spec_disp_id
597 AND sr.result_id = r.result_id
598 AND r.sample_id = p_update_disp_rec.sample_id
599 AND (sr.in_spec_ind IS NULL AND (sr.evaluation_ind IS NULL))
600 AND r.delete_mark = 0
601 AND sr.delete_mark = 0
602 AND esd.spec_id = st.spec_id
603 AND st.test_id = r.test_id
604 AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR (l_include_optional = 'Y'));
605
606 CURSOR c_reject_wo_spec_no_av IS
607 SELECT count(1)
608 FROM gmd_results r, gmd_spec_results sr
609 WHERE sr.event_spec_disp_id = p_update_disp_rec.event_spec_disp_id
610 AND sr.result_id = r.result_id
611 AND r.sample_id = p_update_disp_rec.sample_id
612 AND sr.additional_test_ind = 'Y'
613 AND (sr.evaluation_ind IS NULL)
614 AND r.delete_mark = 0
615 AND sr.delete_mark = 0;
616
617 CURSOR c_get_smpl_org_id IS
618 SELECT organization_id
619 FROM gmd_samples
620 WHERE sample_id = p_update_disp_rec.sample_id;
621
622 CURSOR c_get_evt_org_id IS
623 SELECT organization_id
624 FROM gmd_sampling_events gse,
625 gmd_event_spec_disp gesd,
626 gmd_composite_spec_disp gcsd
627 WHERE gse.sampling_event_id = gesd.sampling_event_id
628 AND gesd.event_spec_disp_id = gcsd.event_spec_disp_id
629 AND gcsd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id;
630
631 BEGIN
632 l_pos :='000';
633
634 -- Initialize API return status to success
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636
637 IF p_update_disp_rec.sample_id IS NOT NULL THEN
638 OPEN c_get_smpl_org_id;
639 FETCH c_get_smpl_org_id INTO l_organization_id;
640 CLOSE c_get_smpl_org_id;
641 ELSE
642 OPEN c_get_evt_org_id;
643 FETCH c_get_evt_org_id INTO l_organization_id;
644 CLOSE c_get_evt_org_id;
645 END IF;
646
647 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
648 p_organization_id => l_organization_id
649 , x_quality_parameters => quality_config
650 , x_return_status => l_return_status
651 , x_orgn_found => org_found );
652 IF (l_return_status <> 'S') THEN
653 RAISE INVALID_ORG_PARAM;
654 END IF;
655
656 l_include_optional :=nvl(quality_config.include_optional_test_rslt_ind,'N');
657
658 IF (p_update_disp_rec.sample_id IS NOT NULL AND p_update_disp_rec.composite_spec_disp_id IS NOT NULL)
659 OR (p_update_disp_rec.sample_id IS NULL AND p_update_disp_rec.composite_spec_disp_id IS NULL)
660 THEN
661 RAISE INVALID_PARAMETERS; --'GMD_INVALID_PARAMETERS'
662 END IF;
663
664 l_pos :='001';
665
666 IF p_update_disp_rec.sample_id IS NOT NULL THEN
667 OPEN c_non_accept_w_spec ;
668 FETCH c_non_accept_w_spec INTO l_non_accept_w_spec;
669 CLOSE c_non_accept_w_spec ;
670
671 OPEN c_non_accept_wo_spec ;
672 FETCH c_non_accept_wo_spec INTO l_non_accept_wo_spec;
673 CLOSE c_non_accept_wo_spec ;
674
675 OPEN c_non_reject_w_spec ;
676 FETCH c_non_reject_w_spec INTO l_non_reject_w_spec;
677 CLOSE c_non_reject_w_spec ;
678
679 OPEN c_non_reject_wo_spec ;
680 FETCH c_non_reject_wo_spec INTO l_non_reject_wo_spec;
681 CLOSE c_non_reject_wo_spec ;
682
683 OPEN c_reject_w_spec_no_av ;
684 FETCH c_reject_w_spec_no_av INTO l_reject_w_spec_no_av;
685 CLOSE c_reject_w_spec_no_av ;
686
687 OPEN c_reject_wo_spec_no_av ;
688 FETCH c_reject_wo_spec_no_av INTO l_reject_wo_spec_no_av;
689 CLOSE c_reject_wo_spec_no_av ;
690
691 IF p_update_disp_rec.curr_disposition = '1P' THEN
692 IF p_to_disposition NOT IN ('7CN') THEN
693 RAISE INVALID_DISPOSITION ;
694 END IF;
695 ELSIF p_update_disp_rec.curr_disposition = '2I' THEN
696 IF l_reject_w_spec_no_av + l_reject_wo_spec_no_av > 0 THEN
697 IF p_to_disposition NOT IN ('7CN','6RJ') THEN
698 RAISE INVALID_DISPOSITION ;
699 END IF;
700 ELSE
701 IF p_to_disposition NOT IN ('7CN','5AV','6RJ') THEN
702 RAISE INVALID_DISPOSITION ;
703 END IF;
704 END IF;
705 ELSIF p_update_disp_rec.curr_disposition = '3C' THEN
706 IF l_non_accept_w_spec + l_non_accept_wo_spec = 0 THEN
707 IF p_to_disposition NOT IN ('4A','6RJ') THEN
708 RAISE INVALID_DISPOSITION ;
709 END IF;
710 ELSE
711 IF l_reject_w_spec_no_av + l_reject_wo_spec_no_av > 0 THEN
712 IF p_to_disposition NOT IN ('6RJ') THEN
713 RAISE INVALID_DISPOSITION ;
714 END IF;
715 ELSE
716 IF p_to_disposition NOT IN ('5AV','6RJ') THEN
717 RAISE INVALID_DISPOSITION ;
718 END IF;
719 END IF;
720 END IF;
721 IF l_non_reject_w_spec + l_non_reject_wo_spec = 0 THEN
722 l_def_reject := 1;
723 END IF;
724 ELSIF p_update_disp_rec.curr_disposition = '4A' AND p_update_disp_rec.no_of_samples_for_event = 1 THEN
725 IF p_to_disposition NOT IN ('7CN','6RJ') THEN
726 RAISE INVALID_DISPOSITION ;
727 END IF;
728 ELSIF p_update_disp_rec.curr_disposition = '5AV' AND p_update_disp_rec.no_of_samples_for_event = 1 THEN
729 IF p_to_disposition NOT IN ('7CN','6RJ') THEN
730 RAISE INVALID_DISPOSITION ;
731 END IF;
732 ELSIF p_update_disp_rec.curr_disposition = '6RJ' AND p_update_disp_rec.no_of_samples_for_event = 1 THEN
733 IF p_to_disposition NOT IN ('5AV','7CN') THEN
734 RAISE INVALID_DISPOSITION ;
735 END IF;
736 END IF;
737
738 IF l_def_reject = 1 THEN
739 IF p_to_disposition NOT IN ('6RJ') THEN
740 RAISE INVALID_DISPOSITION;
741 END IF;
742 END IF;
743
744 l_pos :='002';
745
746 ELSIF p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
747 OPEN cr_check_comp_results_in_spec ;
748 FETCH cr_check_comp_results_in_spec INTO l_test_count;
749 CLOSE cr_check_comp_results_in_spec ;
750
751 OPEN c_non_accept_w_spec_cr ;
752 FETCH c_non_accept_w_spec_cr INTO l_non_accept_w_spec;
753 CLOSE c_non_accept_w_spec_cr ;
754
755 OPEN c_non_accept_wo_spec_cr ;
756 FETCH c_non_accept_wo_spec_cr INTO l_non_accept_wo_spec;
757 CLOSE c_non_accept_wo_spec_cr ;
758 l_pos :='003';
759 IF NVL(l_test_count,0) = 0 AND p_update_disp_rec.curr_disposition = '3C' THEN
760 RAISE NO_TESTS_CMP;
761 END IF;
762 l_pos :='004';
763 IF p_update_disp_rec.curr_disposition = '3C' THEN
764 IF l_non_accept_w_spec + l_non_accept_wo_spec = 0 THEN
765 IF p_to_disposition NOT IN ('4A','6RJ') THEN
766 RAISE INVALID_DISPOSITION;
767 END IF;
768 ELSE
769 IF p_to_disposition NOT IN ('5AV','6RJ') THEN
770 RAISE INVALID_DISPOSITION;
771 END IF;
772 END IF;
773 ELSIF p_update_disp_rec.curr_disposition = '2I' THEN
774 IF p_to_disposition NOT IN ('5AV','6RJ') THEN
775 RAISE INVALID_DISPOSITION;
776 END IF;
777 END IF;
778 END IF; -- p_update_disp_rec.sample_id IS NOT NULL
779
780 EXCEPTION
781 WHEN INVALID_ORG_PARAM THEN
782 gmd_api_pub.log_message('GMD_QM_ORG_PARAMETER');
783 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
784 x_return_status := FND_API.G_RET_STS_ERROR ;
785 WHEN INVALID_DISPOSITION THEN
786 gmd_api_pub.log_message('GMD_SAMPLE_DISPOSITION_INVALID');
787 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
788 x_return_status := FND_API.G_RET_STS_ERROR ;
789 WHEN NO_TESTS_CMP THEN
790 gmd_api_pub.log_message('GMD_QM_NO_CMPS_RSLT');
791 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
792 x_return_status := FND_API.G_RET_STS_ERROR ;
793 WHEN INVALID_PARAMETERS THEN
794 gmd_api_pub.log_message('GMD_INVALID_PARAMETERS');
795 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
796 x_return_status := FND_API.G_RET_STS_ERROR ;
797 WHEN FND_API.G_EXC_ERROR THEN
798 x_return_status := FND_API.G_RET_STS_ERROR ;
799 WHEN OTHERS THEN
800 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_DISP_PUB.Validate_disp','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_pos);
801 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
803
804 END Validate_disp;
805
806
807 /*=============================================================================
808 Procedure change_disposition
809 This is the public procedure which should be called to change the
810 disposition of the sample/group.
811 =============================================================================*/
812 PROCEDURE change_disposition (
813 p_api_version IN NUMBER
814 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
815 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
816 ,p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
817 ,p_change_disp_rec IN CHANGE_DISP_REC
818 ,x_return_status OUT NOCOPY VARCHAR2
819 ,x_msg_count OUT NOCOPY NUMBER
820 ,x_msg_data OUT NOCOPY VARCHAR2
821 ) IS
822
823 l_api_name CONSTANT VARCHAR2 (30) := 'CHANGE_DISPOSITION';
824 l_api_version CONSTANT NUMBER := 1.0;
825 l_msg_count NUMBER :=0;
826 l_message_data VARCHAR2(2000);
827 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
828 l_pos VARCHAR2(5);
829 l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
830 l_change_rec GMD_SAMPLES_GRP.update_change_disp_rec;
831 l_sample_req_cnt NUMBER;
832 l_sample_active_cnt NUMBER;
833 l_sample_taken_cnt NUMBER;
834
835 l_sample_spec_disp VARCHAR2(3);
836 l_event_spec_disp_id NUMBER(15);
837 l_comp_spec_disp VARCHAR2(3);
838 l_sampling_event_id NUMBER(15) ;
839
840 l_organization_id NUMBER;
841 l_source_subinventory VARCHAR2(10);
842 l_source_locator VARCHAR2(16);
843 l_source_locator_id NUMBER;
844 l_subinventory VARCHAR2(10);
845 l_locator VARCHAR2(16);
846 l_locator_id NUMBER;
847 l_inventory_item_id NUMBER;
848 l_parent_lot_number VARCHAR2(80);
849 l_lot_number VARCHAR2(80);
850 l_lpn_id NUMBER;
851 l_lpn VARCHAR2(32);
852
853 l_wms_enabled_flag VARCHAR2(1);
854 l_lot_control_code NUMBER;
855 l_grade_control_flag VARCHAR2(1);
856 l_child_lot_flag VARCHAR2(1);
857 l_lot_status_enabled VARCHAR2(1);
858
859 l_sample_type VARCHAR2(1);
860 l_source VARCHAR2(1);
861
862 l_spec_id NUMBER;
863 l_spec_vr_id NUMBER;
864 l_spec_name VARCHAR2(30);
865 l_spec_vers NUMBER;
866 l_ctrl_lot_attrib_ind VARCHAR2(1) ;
867 l_in_spec_lot_sts_id VARCHAR2(4) ;
868 l_out_spec_lot_sts_id VARCHAR2(4) ;
869 l_ctrl_batch_step_ind VARCHAR2(1) ;
870 l_to_qc_status NUMBER;
871 l_to_lot_status_id NUMBER;
872 l_to_grade_code VARCHAR2(4);
873 l_lot_created DATE;
874 l_hold_date DATE := SYSDATE;
875 l_sampling_event_date DATE;
876 l_from_lot_status_id NUMBER;
877 l_from_grade_code VARCHAR2(4);
878 l_reason_id NUMBER;
879 l_delayed_lot_entry VARCHAR2(1);
880 l_delayed_lpn_entry VARCHAR2(1);
881
882 INVALID_SAMPLE EXCEPTION;
883 GMD_QC_LESS_LOT_DATE EXCEPTION;
884 INVALID_PARAMETERS EXCEPTION;
885 LOT_MUST_BE_SPECIFIED EXCEPTION;
886 INVALID_DISPOSITION EXCEPTION;
887
888 CURSOR samples_for_event IS
889 SELECT sample_req_cnt, sample_active_cnt, sample_taken_cnt
890 FROM gmd_sampling_events
891 WHERE sampling_event_id = p_change_disp_rec.sampling_event_id;
892
893 --Get sample id for single sample group if sampling event is passed
894 CURSOR get_sample_id IS
895 SELECT sample_id
896 FROM gmd_samples
897 WHERE sampling_event_id = p_change_disp_rec.sampling_event_id;
898
899 CURSOR composite_spec_disp(l_event_spec_disp_id NUMBER) IS
900 SELECT composite_spec_disp_id
901 FROM gmd_composite_spec_disp
902 WHERE event_spec_disp_id = l_event_spec_disp_id;
903
904 CURSOR cr_get_lot_status IS
905 SELECT control_lot_attrib_ind,
906 in_spec_lot_status_id,
907 out_of_spec_lot_status_id,
908 null
909 FROM gmd_inventory_spec_vrs
910 WHERE spec_vr_id = l_spec_vr_id
911 UNION ALL
912 SELECT control_lot_attrib_ind,
913 in_spec_lot_status_id,
914 out_of_spec_lot_status_id,
915 control_batch_step_ind
916 FROM gmd_wip_spec_vrs
917 WHERE spec_vr_id = l_spec_vr_id
918 UNION ALL
919 SELECT control_lot_attrib_ind,
920 in_spec_lot_status_id,
921 out_of_spec_lot_status_id,
922 null
923 FROM gmd_supplier_spec_vrs
924 WHERE spec_vr_id = l_spec_vr_id ;
925
926 CURSOR cr_get_delayed_lot_entry IS
927 SELECT delayed_lot_entry
928 FROM GMD_INVENTORY_SPEC_VRS
929 WHERE spec_vr_id = l_spec_vr_id
930 UNION ALL
931 SELECT delayed_lot_entry
932 FROM GMD_WIP_SPEC_VRS
933 WHERE spec_vr_id = l_spec_vr_id
934 UNION ALL
935 SELECT delayed_lot_entry
936 FROM GMD_SUPPLIER_SPEC_VRS
937 WHERE spec_vr_id = l_spec_vr_id ;
938
939 CURSOR cr_get_delayed_lpn_entry IS
940 SELECT delayed_lpn_entry
941 FROM GMD_INVENTORY_SPEC_VRS
942 WHERE spec_vr_id = l_spec_vr_id
943 UNION ALL
944 SELECT delayed_lpn_entry
945 FROM GMD_WIP_SPEC_VRS
946 WHERE spec_vr_id = l_spec_vr_id
947 UNION ALL
948 SELECT delayed_lpn_entry
949 FROM GMD_SUPPLIER_SPEC_VRS
950 WHERE spec_vr_id = l_spec_vr_id ;
951
952 BEGIN
953 IF (l_debug = 'Y') THEN
954 gmd_debug.put_line('Entered Procedure GMD_DISP_PUB.change_disposition');
955 END IF;
956
957 -- Initialize API return status to success
958 x_return_status := FND_API.G_RET_STS_SUCCESS;
959
960 l_pos := '000';
961 l_update_disp_rec.sample_id := p_change_disp_rec.sample_id;
962 l_update_disp_rec.sampling_event_id := p_change_disp_rec.sampling_event_id;
963
964 --Either one of Sample_id or Sampling_event_id should be entered
965 IF (p_change_disp_rec.sample_id IS NOT NULL AND p_change_disp_rec.sampling_event_id IS NOT NULL )
966 OR (p_change_disp_rec.sample_id IS NULL AND p_change_disp_rec.sampling_event_id IS NULL ) THEN
967 IF (l_debug = 'Y') THEN
968 gmd_debug.put_line('Either one of Sample_id or Sampling_event_id should be entered');
969 END IF;
970 RAISE INVALID_PARAMETERS;
971 END IF;
972
973 --Either one of LPN or LPN_ID should be entered and will be considered for delayed_lpn_entry
974 IF (p_change_disp_rec.sample_id IS NOT NULL AND p_change_disp_rec.sampling_event_id IS NOT NULL ) THEN
975 IF (l_debug = 'Y') THEN
976 gmd_debug.put_line('Either one of LPN or LPN_ID should be entered');
977 END IF;
978 RAISE INVALID_PARAMETERS;
979 END IF;
980
981 IF l_update_disp_rec.sample_id IS NOT NULL THEN
982 l_update_disp_rec.event_spec_disp_id := get_current_event_spec_disp_id(l_update_disp_rec.sample_id,'Y');
983 ELSE
984 l_update_disp_rec.event_spec_disp_id := get_current_event_spec_disp_id(l_update_disp_rec.sampling_event_id,'N');
985 IF l_update_disp_rec.event_spec_disp_id IS NOT NULL THEN
986 OPEN composite_spec_disp(l_update_disp_rec.event_spec_disp_id);
987 FETCH composite_spec_disp INTO l_update_disp_rec.composite_spec_disp_id;
988 CLOSE composite_spec_disp;
989 END IF;
990 END IF;
991 l_pos := '001';
992 l_update_disp_rec.called_from_results :='N';
993
994 OPEN samples_for_event;
995 FETCH samples_for_event INTO l_sample_req_cnt, l_sample_active_cnt,l_sample_taken_cnt;
996 CLOSE samples_for_event;
997
998 l_pos := '002';
999
1000 IF NVL(l_sample_req_cnt,1) = 1
1001 AND NVL(l_sample_req_cnt,1) = NVL(l_sample_active_cnt ,1) THEN
1002 l_update_disp_rec.no_of_samples_for_event := 1;
1003 IF l_update_disp_rec.sample_id IS NULL THEN
1004 OPEN get_sample_id;
1005 FETCH get_sample_id INTO l_update_disp_rec.sample_id;
1006 CLOSE get_sample_id;
1007 END IF;
1008 ELSE
1009 l_update_disp_rec.no_of_samples_for_event := 2;
1010 END IF;
1011
1012 IF p_change_disp_rec.to_disposition NOT IN ('4A','5AV','6RJ') THEN --correct error should be initialized
1013 IF (l_debug = 'Y') THEN
1014 gmd_debug.put_line('Not a Valid To Disposition');
1015 END IF;
1016 RAISE FND_API.G_EXC_ERROR;
1017 END IF;
1018
1019 IF l_update_disp_rec.sample_id IS NOT NULL THEN
1020
1021 SELECT a.organization_id,b.lot_control_code,b.grade_control_flag,b.child_lot_flag,b.lot_status_enabled,a.sample_type,a.source,
1022 a.inventory_item_id,a.parent_lot_number,a.lot_number,a.lpn_id,a.subinventory,a.locator_id,c.concatenated_segments,a.lpn_id,d.license_plate_number,e.wms_enabled_flag
1023 INTO l_organization_id,l_lot_control_code,l_grade_control_flag,l_child_lot_flag,l_lot_status_enabled,l_sample_type,l_source,
1024 l_inventory_item_id,l_parent_lot_number,l_lot_number,l_lpn_id,l_subinventory,l_locator_id,l_locator,l_lpn_id,l_lpn,l_wms_enabled_flag
1025 FROM gmd_samples a,
1026 mtl_system_items_b b,
1027 mtl_item_locations_kfv c,
1028 wms_license_plate_numbers d,
1029 mtl_parameters e
1030 WHERE a.sample_id = l_update_disp_rec.sample_id
1031 AND a.delete_mark = 0
1032 AND a.organization_id = b.organization_id(+)
1033 AND a.inventory_item_id = b.inventory_item_id(+)
1034 AND a.organization_id = c.organization_id(+)
1035 AND a.locator_id = c.inventory_location_id(+)
1036 AND a.lpn_id = d.lpn_id(+)
1037 AND a.organization_id = e.organization_id;
1038
1039 l_pos := '003';
1040
1041 -- Get current disposition of Sample from gmd_sample_spec_disp
1042 SELECT a.DISPOSITION,
1043 b.EVENT_SPEC_DISP_ID,
1044 b.spec_id,b.spec_vr_id,
1045 c.spec_name,
1046 c.spec_vers
1047 INTO l_update_disp_rec.curr_disposition,l_update_disp_rec.event_spec_disp_id,
1048 l_spec_id,l_spec_vr_id,
1049 l_spec_name,l_spec_vers
1050 FROM gmd_sample_spec_disp a,
1051 gmd_event_spec_disp b,
1052 gmd_specifications_b c
1053 WHERE a.sample_id = l_update_disp_rec.sample_id
1054 AND a.event_spec_disp_id = b.event_spec_disp_id
1055 AND a.delete_mark = 0
1056 AND b.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
1057 AND b.delete_mark = 0
1058 AND b.spec_id = c.spec_id(+);
1059
1060 l_pos := '004';
1061
1062 IF (l_update_disp_rec.no_of_samples_for_event = 1)
1063 AND (l_sample_type <> 'M') AND (l_source <> 'T') --Not a Monitoring Sample and Stability Study Sample
1064 AND (l_spec_vr_id IS NOT NULL)
1065 AND (l_lot_control_code = 2) -- Lot controlled item
1066 AND (l_lot_number IS NULL)
1067 AND (l_update_disp_rec.curr_disposition = '3C') THEN
1068
1069 OPEN cr_get_delayed_lot_entry;
1070 FETCH cr_get_delayed_lot_entry INTO l_delayed_lot_entry;
1071 CLOSE cr_get_delayed_lot_entry;
1072
1073 IF l_delayed_lot_entry = 'Y' THEN
1074 IF p_change_disp_rec.lot_number IS NULL THEN
1075 RAISE LOT_MUST_BE_SPECIFIED;
1076 ELSE
1077 validate_parentlot_lot (
1078 p_inventory_item_id => l_inventory_item_id
1079 ,p_organization_id => l_organization_id
1080 ,p_parent_lot_number => p_change_disp_rec.parent_lot_number
1081 ,p_lot_number => p_change_disp_rec.lot_number
1082 ,x_return_status => l_return_status
1083 ,x_msg_count => l_msg_count
1084 ,x_msg_data => l_message_data);
1085
1086 IF (l_return_status <> 'S') THEN
1087 IF (l_debug = 'Y') THEN
1088 gmd_debug.put_line('Entered Lot/Sublot is not Valid ');
1089 END IF;
1090 RAISE FND_API.G_EXC_ERROR;
1091 ELSE
1092 UPDATE GMD_SAMPLES
1093 SET parent_lot_number = p_change_disp_rec.parent_lot_number,
1094 lot_number = p_change_disp_rec.lot_number
1095 WHERE sample_id = l_update_disp_rec.sample_id ;
1096
1097 UPDATE GMD_SAMPLING_EVENTS
1098 SET parent_lot_number = p_change_disp_rec.parent_lot_number,
1099 lot_number = p_change_disp_rec.lot_number
1100 WHERE sampling_event_id = (SELECT sampling_event_id FROM gmd_samples
1101 WHERE sample_id = l_update_disp_rec.sample_id );
1102
1103 l_parent_lot_number := p_change_disp_rec.parent_lot_number;
1104 l_lot_number := p_change_disp_rec.lot_number;
1105 END IF;
1106 END IF;
1107 END IF;
1108 END IF;
1109
1110 IF (l_update_disp_rec.no_of_samples_for_event = 1)
1111 AND (l_sample_type <> 'M') AND (l_source <> 'T') --Not a Monitoring Sample and Stability Study Sample
1112 AND (l_spec_vr_id IS NOT NULL)
1113 AND (l_wms_enabled_flag = 'Y') -- Org is WMS Enabled
1114 AND (l_lpn_id IS NULL)
1115 AND (l_update_disp_rec.curr_disposition = '3C') THEN
1116
1117 OPEN cr_get_delayed_lpn_entry;
1118 FETCH cr_get_delayed_lpn_entry INTO l_delayed_lpn_entry;
1119 CLOSE cr_get_delayed_lpn_entry;
1120
1121 IF l_delayed_lpn_entry = 'Y' THEN
1122 IF p_change_disp_rec.lpn IS NULL AND p_change_disp_rec.lpn_id IS NULL THEN
1123 NULL; -- RAISE No need to raise error as LPN is not a mandatory field
1124 ELSE
1125 validate_lpn (
1126 p_inventory_item_id => l_inventory_item_id
1127 ,p_organization_id => l_organization_id
1128 ,p_lot_number => p_change_disp_rec.lot_number
1129 ,p_lpn_id => p_change_disp_rec.lpn_id
1130 ,p_lpn => p_change_disp_rec.lpn
1131 ,x_lpn_id => l_lpn_id
1132 ,x_lpn => l_lpn
1133 ,x_return_status => l_return_status
1134 ,x_msg_count => l_msg_count
1135 ,x_msg_data => l_message_data);
1136
1137 IF (l_return_status <> 'S') THEN
1138 IF (l_debug = 'Y') THEN
1139 gmd_debug.put_line('Entered LPN is not Valid ');
1140 END IF;
1141 RAISE FND_API.G_EXC_ERROR;
1142 ELSE
1143 UPDATE GMD_SAMPLES
1144 SET lpn_id = l_lpn_id
1145 WHERE sample_id = l_update_disp_rec.sample_id ;
1146
1147 UPDATE GMD_SAMPLING_EVENTS
1148 SET lpn_id = l_lpn_id
1149 WHERE sampling_event_id = (SELECT sampling_event_id FROM gmd_samples
1150 WHERE sample_id = l_update_disp_rec.sample_id );
1151
1152 END IF;
1153 END IF;
1154 END IF;
1155 END IF;
1156
1157 l_pos := '005';
1158
1159 ELSIF l_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
1160
1161 SELECT gse.organization_id,gse.inventory_item_id,gse.parent_lot_number, gse.lot_number,msi.grade_control_flag,msi.lot_control_code,msi.child_lot_flag,msi.lot_status_enabled,
1162 gse.creation_date,gse.subinventory,gse.locator_id,mil.concatenated_segments,gse.lpn_id,wlpn.license_plate_number,mp.wms_enabled_flag,
1163 NVL(gse.sample_type, 'I'),nvl(gse.source,'I'),esd.spec_id,esd.spec_vr_id,csd.disposition,gsb.spec_name,gsb.spec_vers
1164 INTO l_organization_id,l_inventory_item_id,l_parent_lot_number,l_lot_number,l_grade_control_flag,l_lot_control_code,l_child_lot_flag,l_lot_status_enabled,
1165 l_sampling_event_date,l_subinventory,l_locator_id,l_locator,l_lpn_id,l_lpn,l_wms_enabled_flag,
1166 l_sample_type,l_source,l_spec_id,l_spec_vr_id,l_update_disp_rec.curr_disposition,l_spec_name,l_spec_vers
1167 FROM gmd_composite_spec_disp csd,
1168 gmd_event_spec_disp esd ,
1169 gmd_sampling_events gse,
1170 mtl_system_items_b msi,
1171 gmd_specifications_b gsb,
1172 mtl_item_locations_kfv mil,
1173 wms_license_plate_numbers wlpn,
1174 mtl_parameters mp
1175 WHERE csd.composite_spec_disp_id = l_update_disp_rec.composite_spec_disp_id
1176 AND csd.event_spec_disp_id = esd.event_spec_disp_id
1177 AND esd.sampling_event_id = gse.sampling_event_id
1178 AND gse.organization_id = msi.organization_id(+)
1179 AND gse.inventory_item_id = msi.inventory_item_id(+)
1180 AND esd.spec_id = gsb.spec_id(+)
1181 AND gse.organization_id = mil.organization_id(+)
1182 AND gse.locator_id = mil.inventory_location_id(+)
1183 AND gse.lpn_id = wlpn.lpn_id(+)
1184 AND gse.organization_id = mp.organization_id;
1185
1186 IF (l_sample_type <> 'M') AND (l_source <> 'T') -- Not a Monitoring and Stability Study Sample
1187 AND (l_spec_vr_id IS NOT NULL)
1188 AND (l_lot_control_code = 2) -- Lot controlled item
1189 AND (l_lot_number IS NULL)
1190 AND (l_update_disp_rec.curr_disposition = '3C') THEN
1191
1192 OPEN cr_get_delayed_lot_entry;
1193 FETCH cr_get_delayed_lot_entry INTO l_delayed_lot_entry;
1194 CLOSE cr_get_delayed_lot_entry;
1195
1196 IF l_delayed_lot_entry = 'Y' THEN
1197 IF p_change_disp_rec.lot_number IS NULL THEN
1198 RAISE LOT_MUST_BE_SPECIFIED;
1199 ELSE
1200 validate_parentlot_lot (
1201 p_inventory_item_id => l_inventory_item_id
1202 ,p_organization_id => l_organization_id
1203 ,p_parent_lot_number => p_change_disp_rec.parent_lot_number
1204 ,p_lot_number => p_change_disp_rec.lot_number
1205 ,x_return_status => l_return_status
1206 ,x_msg_count => l_msg_count
1207 ,x_msg_data => l_message_data);
1208
1209 IF (l_return_status <> 'S') THEN
1210 IF (l_debug = 'Y') THEN
1211 gmd_debug.put_line('Entered Lot/Sublot is not Valid ');
1212 END IF;
1213 RAISE FND_API.G_EXC_ERROR;
1214 ELSE
1215 UPDATE GMD_SAMPLES
1216 SET parent_lot_number = p_change_disp_rec.parent_lot_number,
1217 lot_number = p_change_disp_rec.lot_number
1218 WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
1219
1220 UPDATE GMD_SAMPLING_EVENTS
1221 SET parent_lot_number = p_change_disp_rec.parent_lot_number,
1222 lot_number = p_change_disp_rec.lot_number
1223 WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
1224
1225 l_parent_lot_number := p_change_disp_rec.parent_lot_number;
1226 l_lot_number := p_change_disp_rec.lot_number;
1227 END IF;
1228 END IF;
1229 END IF;
1230 END IF;
1231
1232 IF (l_sample_type <> 'M') AND (l_source <> 'T') -- Not a Monitoring and Stability Study Sample
1233 AND (l_spec_vr_id IS NOT NULL)
1234 AND (l_wms_enabled_flag = 'Y') -- Org is WMS Enabled
1235 AND (l_lpn_id IS NULL)
1236 AND (l_update_disp_rec.curr_disposition = '3C') THEN
1237
1238 OPEN cr_get_delayed_lpn_entry;
1239 FETCH cr_get_delayed_lpn_entry INTO l_delayed_lpn_entry;
1240 CLOSE cr_get_delayed_lpn_entry;
1241
1242 IF l_delayed_lpn_entry = 'Y' THEN
1243 IF p_change_disp_rec.lpn IS NULL AND p_change_disp_rec.lpn_id IS NULL THEN
1244 NULL; -- RAISE No need to raise error as LPN is not a mandatory field
1245 ELSE
1246 validate_lpn (
1247 p_inventory_item_id => l_inventory_item_id
1248 ,p_organization_id => l_organization_id
1249 ,p_lot_number => p_change_disp_rec.lot_number
1250 ,p_lpn_id => p_change_disp_rec.lpn_id
1251 ,p_lpn => p_change_disp_rec.lpn
1252 ,x_lpn_id => l_lpn_id
1253 ,x_lpn => l_lpn
1254 ,x_return_status => l_return_status
1255 ,x_msg_count => l_msg_count
1256 ,x_msg_data => l_message_data);
1257
1258 IF (l_return_status <> 'S') THEN
1259 IF (l_debug = 'Y') THEN
1260 gmd_debug.put_line('Entered LPN is not Valid ');
1261 END IF;
1262 RAISE FND_API.G_EXC_ERROR;
1263 ELSE
1264 UPDATE GMD_SAMPLES
1265 SET lpn_id = l_lpn_id
1266 WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
1267
1268 UPDATE GMD_SAMPLING_EVENTS
1269 SET lpn_id = l_lpn_id
1270 WHERE sampling_event_id = l_update_disp_rec.sampling_event_id;
1271
1272 END IF;
1273 END IF;
1274 END IF;
1275 END IF;
1276
1277 END IF; -- IF sample_id and composite_id
1278
1279 l_pos := '006';
1280
1281 --Cheking Current Disposition.
1282 IF l_update_disp_rec.curr_disposition IN ('0PL','1P','7CN','0RT') THEN
1283 RAISE INVALID_DISPOSITION;
1284 ELSIF l_update_disp_rec.curr_disposition IN ('4A','5AV','6RJ')
1285 AND l_update_disp_rec.sample_id IS NOT NULL THEN
1286 RAISE INVALID_DISPOSITION; --Already in Final Disposition. No target disp available.
1287 END IF;
1288
1289 IF p_change_disp_rec.reason_code IS NOT NULL THEN
1290 validate_reason_code (
1291 p_reason_code => p_change_disp_rec.reason_code
1292 ,x_reason_id => l_reason_id
1293 ,x_return_status => l_return_status
1294 ,x_msg_count => l_msg_count
1295 ,x_msg_data => l_message_data);
1296
1297 IF l_return_status <> 'S' THEN
1298 IF (l_debug = 'Y') THEN
1299 gmd_debug.put_line('Not a Valid Reason Code ');
1300 END IF;
1301 RAISE FND_API.G_EXC_ERROR;
1302 END IF;
1303 END IF;
1304
1305 IF (l_update_disp_rec.no_of_samples_for_event = 1 OR l_update_disp_rec.composite_spec_disp_id IS NOT NULL) THEN
1306 IF p_change_disp_rec.to_disposition IN ('4A','5AV','6RJ') THEN
1307 IF l_spec_vr_id IS NOT NULL THEN
1308 OPEN cr_get_lot_status;
1309 FETCH cr_get_lot_status INTO l_ctrl_lot_attrib_ind,l_in_spec_lot_sts_id,l_out_spec_lot_sts_id,l_ctrl_batch_step_ind;
1310 CLOSE cr_get_lot_status;
1311 IF l_ctrl_batch_step_ind = 'Y' THEN
1312 IF p_change_disp_rec.to_disposition = '6RJ' THEN
1313 l_to_qc_status := 5;
1314 ELSE
1315 l_to_qc_status := 6;
1316 END IF;
1317 ELSE
1318 l_to_qc_status := NULL;
1319 END IF;
1320 END IF;
1321
1322 l_pos := '007';
1323
1324 IF l_lot_number IS NOT NULL THEN
1325 IF l_lot_status_enabled = 'Y' THEN
1326 populate_hold_date (
1327 p_parent_lot_number => l_parent_lot_number
1328 ,p_lot_number => l_lot_number
1329 ,p_inventory_item_id => l_inventory_item_id
1330 ,p_organization_id => l_organization_id
1331 ,x_lot_created => l_lot_created
1332 ,x_hold_date => l_hold_date );
1333
1334 IF l_hold_date < l_lot_created THEN
1335 -- hold date cannot be lesser then lot creation date.
1336 IF (l_debug = 'Y') THEN
1337 gmd_debug.put_line('Hold date cannot be lesser then lot creation date. ');
1338 END IF;
1339 RAISE GMD_QC_LESS_LOT_DATE;
1340 END IF;
1341
1342 BEGIN
1343 SELECT grade_code, status_id
1344 INTO l_from_grade_code, l_from_lot_status_id
1345 FROM mtl_lot_numbers
1346 WHERE ((lot_number = l_lot_number) OR (l_lot_number IS NULL))
1347 AND ((parent_lot_number = l_parent_lot_number) OR (l_parent_lot_number IS NULL))
1348 AND organization_id = l_organization_id
1349 AND inventory_item_id = l_inventory_item_id;
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 NULL;
1353 END;
1354
1355 IF p_change_disp_rec.to_disposition = '6RJ' THEN
1356 l_to_lot_status_id := l_out_spec_lot_sts_id ;
1357 ELSE
1358 l_to_lot_status_id := l_in_spec_lot_sts_id ;
1359 END IF;
1360
1361 IF p_change_disp_rec.to_lot_status IS NOT NULL THEN
1362 validate_lot_grade_status (
1363 p_is_lot => 'Y'
1364 ,p_status => p_change_disp_rec.to_lot_status
1365 ,x_return_status => l_return_status
1366 ,x_msg_count => l_msg_count
1367 ,x_msg_data => l_message_data);
1368 IF (l_return_status <> 'S') THEN
1369 IF (l_debug = 'Y') THEN
1370 gmd_debug.put_line('Lot Status is not valid ');
1371 END IF;
1372 RAISE FND_API.G_EXC_ERROR;
1373 END IF;
1374
1375 SELECT status_id INTO l_to_lot_status_id
1376 FROM mtl_material_statuses
1377 WHERE status_code = p_change_disp_rec.to_lot_status
1378 AND enabled_flag = 1;
1379 END IF;
1380 END IF;
1381
1382 l_pos := '008';
1383 IF l_grade_control_flag = 'Y' THEN
1384 IF p_change_disp_rec.to_disposition IN ('4A','5AV') AND p_change_disp_rec.to_grade_code IS NULL THEN
1385 BEGIN
1386 SELECT grade_code INTO l_to_grade_code
1387 FROM GMD_SPECIFICATIONS_B
1388 WHERE spec_id = l_spec_id ;
1389 EXCEPTION WHEN OTHERS THEN
1390 NULL ;
1391 END ;
1392 END IF;
1393 IF p_change_disp_rec.to_grade_code IS NOT NULL THEN
1394 validate_lot_grade_status (
1395 p_is_lot => 'N'
1396 ,p_status => p_change_disp_rec.to_grade_code
1397 ,x_return_status => l_return_status
1398 ,x_msg_count => l_msg_count
1399 ,x_msg_data => l_message_data
1400 );
1401 IF (l_return_status <> 'S') THEN
1402 IF (l_debug = 'Y') THEN
1403 gmd_debug.put_line('Grade is not valid ');
1404 END IF;
1405 RAISE FND_API.G_EXC_ERROR;
1406 END IF;
1407 END IF;
1408 END IF; -- grade_control_flag = Y
1409 END IF; -- lot_number is NOT NULL
1410
1411 IF l_to_lot_status_id IS NOT NULL OR l_to_grade_code IS NOT NULL THEN
1412 BEGIN
1413 -- for composite result orgn_code is not shown and is null.
1414 IF l_organization_id IS NOT NULL AND l_reason_id IS NULL THEN
1415 SELECT transaction_reason_id INTO l_reason_id
1416 FROM GMD_QUALITY_CONFIG
1417 WHERE organization_id = l_organization_id ;
1418 END IF;
1419 EXCEPTION WHEN OTHERS THEN
1420 NULL;
1421 END;
1422 END IF; -- p_change_disp_rec.to_lot_status_id IS NOT NULL
1423 END IF; -- p_change_disp_rec.to_disposition IN ('4A','5AV','6RJ')
1424 END IF; -- l_update_disp_rec.no_of_samples_for_event = 1
1425
1426 l_pos := '009';
1427
1428 Validate_disp (
1429 p_update_disp_rec => l_update_disp_rec
1430 ,p_to_disposition => p_change_disp_rec.to_disposition
1431 ,x_return_status => l_return_status
1432 ,x_msg_count => l_msg_count
1433 ,x_msg_data => l_message_data
1434 );
1435
1436 l_pos := '010';
1437
1438 IF (l_return_status <> 'S') THEN
1439 IF (l_debug = 'Y') THEN
1440 gmd_debug.put_line('Entered to Disposition is not Valid');
1441 END IF;
1442 RAISE FND_API.G_EXC_ERROR;
1443 END IF;
1444
1445 GMD_DISP_GRP.update_sample_comp_disp
1446 ( p_update_disp_rec => l_update_disp_rec
1447 , p_to_disposition => p_change_disp_rec.to_disposition
1448 , x_return_status => l_return_status
1449 , x_message_data => l_message_data );
1450
1451 l_pos := '011';
1452 IF (l_return_status <> 'S') THEN
1453 IF (l_debug = 'Y') THEN
1454 gmd_debug.put_line('Could not change the Disposition of Sample/Group');
1455 END IF;
1456 x_msg_data := l_message_data;
1457 RAISE FND_API.G_EXC_ERROR;
1458 ELSIF l_to_lot_status_id IS NOT NULL THEN
1459 Gmd_Disp_Grp.update_lot_grade_batch (
1460 p_sample_id => l_update_disp_rec.sample_id
1461 , p_composite_spec_disp_id => l_update_disp_rec.composite_spec_disp_id
1462 , p_to_lot_status_id => l_to_lot_status_id
1463 , p_from_lot_status_id => l_from_lot_status_id
1464 , p_to_grade_code => NVL(p_change_disp_rec.to_grade_code,l_to_grade_code)
1465 , p_from_grade_code => l_from_grade_code
1466 , p_to_qc_status => l_to_qc_status
1467 , p_reason_id => l_reason_id
1468 , p_hold_date => l_hold_date
1469 , x_return_status => l_return_status
1470 , x_message_data => l_message_data );
1471
1472 l_pos := '012';
1473 IF (l_return_status <> 'S') THEN
1474 IF (l_debug = 'Y') THEN
1475 gmd_debug.put_line('Could not change the lot status/grade');
1476 END IF;
1477 x_msg_data := l_message_data;
1478 RAISE FND_API.G_EXC_ERROR;
1479 END IF;
1480 END IF;
1481
1482 -- Standard Check of p_commit.
1483 IF FND_API.to_boolean(p_commit)
1484 THEN
1485 COMMIT WORK;
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN INVALID_PARAMETERS THEN
1490 gmd_api_pub.log_message('GMD_INVALID_PARAMETERS');
1491 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1492 x_return_status := FND_API.G_RET_STS_ERROR ;
1493 WHEN LOT_MUST_BE_SPECIFIED THEN
1494 gmd_api_pub.log_message('GMD_QM_NO_DELAYED_LOT');
1495 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1496 x_return_status := FND_API.G_RET_STS_ERROR ;
1497 WHEN INVALID_DISPOSITION THEN
1498 gmd_api_pub.log_message('GMD_SAMPLE_DISPOSITION_INVALID');
1499 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1500 x_return_status := FND_API.G_RET_STS_ERROR ;
1501 WHEN INVALID_SAMPLE THEN
1502 gmd_api_pub.log_message('GMD_QM_INVALID_SAMPLE');
1503 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1504 x_return_status := FND_API.G_RET_STS_ERROR ;
1505 WHEN GMD_QC_LESS_LOT_DATE THEN
1506 gmd_api_pub.log_message('GMD_QC_LESS_LOT_DATE');
1507 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1508 x_return_status := FND_API.G_RET_STS_ERROR ;
1509 WHEN FND_API.G_EXC_ERROR THEN
1510 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1511 x_return_status := FND_API.G_RET_STS_ERROR ;
1512 WHEN OTHERS THEN
1513 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_DISP_PUB.change_disposition','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_pos);
1514 x_msg_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1516
1517 END change_disposition;
1518
1519 END gmd_disp_pub;
[Home] [Help]