1 PACKAGE BODY GMD_SPEC_MATCH_MIG_GRP AS
2 /* $Header: GMDGSMMB.pls 120.0 2005/05/25 18:49:15 appldev 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 : GMDGSMMS.pls |
11 --| Package Name : GMD_SPEC_MATCH_MIG_GRP |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Specification Match for |
16 --| migration. |
17 --| |
18 --| HISTORY |
19 --| B. Stone 13-Oct_2004 Created. Bug 3934121. |
20 --| Removed find_location_spec and |
21 --| find_resource_spec, these are not used by |
22 --| migration. |
23 --| |
24 --+==========================================================================+
25 -- End of comments
26
27
28 --Start of comments
29 --+========================================================================+
30 --| API Name : find_inventory_spec |
31 --| TYPE : Group |
32 --| Notes : This function RETURN TRUE if matching inventory |
33 --| spec. is found else it RETURN FALSE. |
34 --| If matching inventory spec is found,then |
35 --| it returns matching spec_id and spec_vr_id |
36 --| Calling Program : - Samples form |
37 --| - Subscriber for the Receiving Event(if |
38 --| matching supplier spec is not found) (Workflow) |
39 --| - Inventory Transaction Event |
40 --| - Lot Expiration Transcation Event |
41 --| - Lot Retest Transcation Event |
42 --| HISTORY |
43 --| Mahesh Chandak 6-Aug-2002 Created. |
44 --| Brenda Stone 1-Mar-2004 Bug 3473559; Added Hints for spec_id |
45 --| |
46 --+========================================================================+
47 -- End of comments
48
49 FUNCTION FIND_INVENTORY_SPEC(p_inventory_spec_rec IN inventory_spec_rec_type,
50 x_spec_id OUT NOCOPY NUMBER,
51 x_spec_vr_id OUT NOCOPY NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2,
53 x_message_data OUT NOCOPY VARCHAR2 )
54 RETURN BOOLEAN IS
55
56 l_position VARCHAR2(3);
57 l_grade_ctl NUMBER ;
58 l_item_default_grade QC_GRAD_MST.QC_GRADE%TYPE ;
59 l_check_for_given_grade VARCHAR2(1) := 'N';
60 l_check_for_null_grade VARCHAR2(1) := 'N';
61 l_check_for_default_grade VARCHAR2(1) := 'N';
62 l_lot_no IC_LOTS_MST.lot_no%TYPE;
63 l_sublot_no IC_LOTS_MST.sublot_no%TYPE;
64
65 REQ_FIELDS_MISSING EXCEPTION;
66 INVALID_LOT EXCEPTION;
67 INVALID_ITEM EXCEPTION;
68
69 /* Bug 3473559; Added Hint for spec_id */
70
71 CURSOR cr_match_spec IS
72 SELECT /*+ INDEX ( b GMD_INVENTORY_SPEC_VRS_N1 ) */
73 a.spec_id,b.spec_vr_id,a.grade,decode(a.grade,
74 p_inventory_spec_rec.grade,1,2),
75 b.location,b.whse_code,b.sublot_no,b.lot_no,
76 b.orgn_code
77 FROM gmd_specifications_b a,gmd_inventory_spec_vrs b
78 WHERE
79 a.item_id = p_inventory_spec_rec.item_id
80 AND a.delete_mark = 0
81 AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade = a.grade ) OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
82 OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
83 AND a.spec_id = b.spec_id
84 AND b.delete_mark = 0
85 AND p_inventory_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_inventory_spec_rec.date_effective)
86 AND ((p_inventory_spec_rec.location = b.location) OR ( p_inventory_spec_rec.location IS NULL AND b.location IS NULL)
87 OR (p_inventory_spec_rec.location IS NOT NULL AND b.location IS NULL ))
88 AND ((p_inventory_spec_rec.whse_code = b.whse_code) OR ( p_inventory_spec_rec.whse_code IS NULL AND b.whse_code IS NULL)
89 OR (p_inventory_spec_rec.whse_code IS NOT NULL AND b.whse_code IS NULL ))
90 AND ((l_lot_no = b.lot_no) OR ( l_lot_no IS NULL AND b.lot_no IS NULL)
91 OR (l_lot_no IS NOT NULL AND b.lot_no IS NULL ))
92 AND ((l_sublot_no = b.sublot_no) OR ( l_sublot_no IS NULL AND b.sublot_no IS NULL)
93 OR (l_sublot_no IS NOT NULL AND b.sublot_no IS NULL ))
94 AND ((p_inventory_spec_rec.orgn_code = b.orgn_code) OR ( p_inventory_spec_rec.orgn_code IS NULL AND b.orgn_code IS NULL)
95 OR (p_inventory_spec_rec.orgn_code IS NOT NULL AND b.orgn_code IS NULL ))
96 ORDER BY decode(a.grade,p_inventory_spec_rec.grade,1,2),b.sublot_no,b.lot_no,b.location,b.whse_code,b.orgn_code ;
97
98 -- Production team requirement to look for a specific test in a spec.
99 CURSOR cr_match_spec_test IS
100 SELECT a.spec_id,b.spec_vr_id,a.grade,decode(a.grade,
101 p_inventory_spec_rec.grade,1,2),
102 b.location,b.whse_code,b.sublot_no,b.lot_no,
103 b.orgn_code
104 FROM gmd_specifications_b a,gmd_inventory_spec_vrs b , gmd_spec_tests_b c
105 WHERE
106 a.item_id = p_inventory_spec_rec.item_id
107 AND a.delete_mark = 0
108 AND ((l_check_for_given_grade = 'Y' and p_inventory_spec_rec.grade = a.grade )
109 OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
110 OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
111 AND a.spec_id = c.spec_id
112 AND c.test_id = p_inventory_spec_rec.test_id
113 AND a.spec_id = b.spec_id
114 AND b.delete_mark = 0
115 AND p_inventory_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_inventory_spec_rec.date_effective)
116 AND ((p_inventory_spec_rec.location = b.location)
117 OR ( p_inventory_spec_rec.location IS NULL AND b.location IS NULL)
118 OR (p_inventory_spec_rec.location IS NOT NULL AND b.location IS NULL ))
119 AND ((p_inventory_spec_rec.whse_code = b.whse_code) OR ( p_inventory_spec_rec.whse_code IS NULL AND b.whse_code IS NULL)
120 OR (p_inventory_spec_rec.whse_code IS NOT NULL AND b.whse_code IS NULL ))
121 AND ((l_lot_no = b.lot_no) OR ( l_lot_no IS NULL AND b.lot_no IS NULL)
122 OR (l_lot_no IS NOT NULL AND b.lot_no IS NULL ))
123 AND ((l_sublot_no = b.sublot_no) OR ( l_sublot_no IS NULL AND b.sublot_no IS NULL)
124 OR (l_sublot_no IS NOT NULL AND b.sublot_no IS NULL ))
125 AND ((p_inventory_spec_rec.orgn_code = b.orgn_code) OR ( p_inventory_spec_rec.orgn_code IS NULL AND b.orgn_code IS NULL)
126 OR (p_inventory_spec_rec.orgn_code IS NOT NULL AND b.orgn_code IS NULL ))
127 ORDER BY decode(a.grade,p_inventory_spec_rec.grade,1,2),b.sublot_no,b.lot_no,b.location,b.whse_code,b.orgn_code ;
128
129 l_match_spec_rec cr_match_spec%ROWTYPE;
130
131 BEGIN
132
133 x_return_status := FND_API.G_RET_STS_SUCCESS;
134 FND_MSG_PUB.initialize;
135 l_position := '010';
136
137 IF p_inventory_spec_rec.item_id IS NULL OR p_inventory_spec_rec.date_effective IS NULL THEN
138 RAISE REQ_FIELDS_MISSING;
139 END IF;
140
141 -- if lot_id is passed and corr. lot_no and sublot_no is not there,
142 -- fetch the corr. lot and sublot_no
143 IF p_inventory_spec_rec.lot_id IS NOT NULL
144 AND p_inventory_spec_rec.lot_no IS NULL
145 AND p_inventory_spec_rec.sublot_no IS NULL THEN
146 BEGIN
147 SELECT lot_no,sublot_no INTO l_lot_no,l_sublot_no
148 FROM IC_LOTS_MST
149 WHERE lot_id = p_inventory_spec_rec.lot_id ;
150 EXCEPTION
151 WHEN OTHERS THEN
152 RAISE INVALID_LOT;
153 END;
154 ELSE
155 l_lot_no := p_inventory_spec_rec.lot_no;
156 l_sublot_no := p_inventory_spec_rec.sublot_no;
157 END IF;
158
159 IF p_inventory_spec_rec.grade IS NULL THEN
160 BEGIN
161 SELECT grade_ctl,qc_grade INTO l_grade_ctl ,l_item_default_grade
162 FROM IC_ITEM_MST_B
163 WHERE ITEM_ID = p_inventory_spec_rec.item_id ;
164
165 IF l_grade_ctl = 0 THEN
166 l_check_for_null_grade := 'Y';
167 ELSE
168 -- if item is grade ctl and grade is not passed,
169 -- check for null grade and item's default grade in that order.
170 l_check_for_null_grade := 'Y';
171 l_check_for_default_grade := 'Y';
172 END IF;
173
174 EXCEPTION WHEN OTHERS THEN
175 RAISE INVALID_ITEM;
176 END ;
177 ELSE
178 l_grade_ctl := 1;
179 l_check_for_given_grade := 'Y';
180 l_check_for_null_grade := 'Y';
181 END IF;
182
183 l_position := '020';
184
185 IF p_inventory_spec_rec.test_id IS NULL THEN
186
187 OPEN cr_match_spec;
188 FETCH cr_match_spec INTO l_match_spec_rec ;
189 IF cr_match_spec%NOTFOUND THEN
190 CLOSE cr_match_spec;
191 RETURN FALSE;
192 END IF;
193 CLOSE cr_match_spec;
194 ELSE
195
196 OPEN cr_match_spec_test;
197 FETCH cr_match_spec_test INTO l_match_spec_rec ;
198 IF cr_match_spec_test%NOTFOUND THEN
199 CLOSE cr_match_spec_test;
200 RETURN FALSE;
201 END IF;
202 CLOSE cr_match_spec_test;
203 END IF;
204
205 l_position := '030';
206
207 IF p_inventory_spec_rec.exact_match = 'Y' THEN
208 IF (( p_inventory_spec_rec.grade = l_match_spec_rec.grade)
209 OR ( p_inventory_spec_rec.grade IS NULL AND l_match_spec_rec.grade IS NULL))
210 AND
211 (( p_inventory_spec_rec.orgn_code = l_match_spec_rec.orgn_code)
212 OR ( p_inventory_spec_rec.orgn_code IS NULL AND l_match_spec_rec.orgn_code IS NULL))
213 AND
214 (( l_lot_no = l_match_spec_rec.lot_no)
215 OR ( l_lot_no IS NULL AND l_match_spec_rec.lot_no IS NULL))
216 AND
217 (( l_sublot_no = l_match_spec_rec.sublot_no)
218 OR ( l_sublot_no IS NULL AND l_match_spec_rec.sublot_no IS NULL))
219 AND
220 (( p_inventory_spec_rec.whse_code = l_match_spec_rec.whse_code)
221 OR ( p_inventory_spec_rec.whse_code IS NULL AND l_match_spec_rec.whse_code IS NULL))
222 AND
223 (( p_inventory_spec_rec.location = l_match_spec_rec.location)
224 OR ( p_inventory_spec_rec.location IS NULL AND l_match_spec_rec.location IS NULL))
225 THEN
226 x_spec_id := l_match_spec_rec.spec_id ;
227 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
228 RETURN TRUE;
229 ELSE
230 RETURN FALSE;
231 END IF;
232 ELSE
233 x_spec_id := l_match_spec_rec.spec_id ;
234 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
235 RETURN TRUE;
236 END IF;
237
238 EXCEPTION
239 WHEN REQ_FIELDS_MISSING THEN
240 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC');
241 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
242 x_return_status := FND_API.G_RET_STS_ERROR ;
243 RETURN FALSE;
244 WHEN INVALID_LOT THEN
245 gmd_api_pub.log_message('GMD_INVALID_LOT','LOT',to_char(p_inventory_spec_rec.lot_id));
246 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
247 x_return_status := FND_API.G_RET_STS_ERROR ;
248 RETURN FALSE;
249 WHEN INVALID_ITEM THEN
250 gmd_api_pub.log_message('GMD_INVALID_ITEM','ITEM',to_char(p_inventory_spec_rec.item_id));
251 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
252 x_return_status := FND_API.G_RET_STS_ERROR ;
253 RETURN FALSE;
254 WHEN OTHERS THEN
255 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
256 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
258 RETURN FALSE;
259 END FIND_INVENTORY_SPEC;
260
261
262 --Start of comments
263 --+========================================================================+
264 --| API Name : find_customer_spec |
265 --| TYPE : Group |
266 --| Notes : This function RETURN TRUE if matching customer |
267 --| spec. is found else it RETURN FALSE. |
268 --| If matching customer spec is found,then |
269 --| it returns matching spec_id and spec_vr_id |
270 --| Calling Program : - Samples form |
271 --| - Spec matching in Order Management(Pick lots form) |
272 --| - Shipment screen in OM (in Future ) |
273 --| HISTORY |
274 --| Mahesh Chandak 6-Aug-2002 Created. |
275 --| Brenda Stone 1-Mar-2004 Bug 3473559; Added Hints for spec_id |
276 --| |
277 --+========================================================================+
278 -- End of comments
279
280 -- item_id, cust_id,date_effective are required.
281 -- either orgn_code or whse_code must be present.
282 -- Pick lot screen and Samples will pass order_line_id(if exists) and not line_number.
283 -- set look_in_other_orgn = 'Y' when calling from pick lots screen else set to 'N'
284 -- when calling from samples screen.
285 -- date_effective - from pick lots screen would be shipment date.
286 -- from sample screen, sample creation date.
287 -- order_line_id(if it exists) will be passed from Pick lot and Samples screen.They won't pass order_line
288 -- End of comments
289
290 FUNCTION FIND_CUSTOMER_SPEC(p_customer_spec_rec IN customer_spec_rec_type,
291 x_spec_id OUT NOCOPY NUMBER,
292 x_spec_vr_id OUT NOCOPY NUMBER,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_message_data OUT NOCOPY VARCHAR2 )
295 RETURN BOOLEAN IS
296
297 l_orgn_code VARCHAR2(4);
298 l_position VARCHAR2(3);
299 l_order_line_number NUMBER;
303 l_check_for_null_grade VARCHAR2(1) := 'N';
300 l_grade_ctl NUMBER ;
301 l_item_default_grade QC_GRAD_MST.QC_GRADE%TYPE ;
302 l_check_for_given_grade VARCHAR2(1) := 'N';
304 l_check_for_default_grade VARCHAR2(1) := 'N';
305
306
307 REQ_FIELDS_MISSING EXCEPTION;
308 NO_ORGN_WHSE EXCEPTION;
309 INVALID_WHSE EXCEPTION;
310 INVALID_ORDER_LINE EXCEPTION;
311 INVALID_ITEM EXCEPTION;
312
313 /* Bug 3473559; Added Hint for spec_id */
314 -- Bug 3934121; Removed spec_status from Where clause
315
316 CURSOR cr_match_spec IS
317 SELECT /*+ INDEX (b gmd_customer_spec_vrs_n1 ) */
318 a.spec_id,b.spec_vr_id,a.grade grade,DECODE(a.grade,p_customer_spec_rec.grade,1,2) grade_order_by,b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id,DECODE(b.orgn_code,l_orgn_code,1,NULL,2,3) orgn_code_order_by,b.orgn_code
319 FROM gmd_specifications_b a,gmd_customer_spec_vrs b
320 WHERE
321 a.item_id = p_customer_spec_rec.item_id
322 AND a.delete_mark = 0
323 AND ((l_check_for_given_grade = 'Y' and p_customer_spec_rec.grade = a.grade )
324 OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
325 OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
326 AND a.spec_id = b.spec_id
327 AND b.delete_mark = 0
328 AND b.cust_id = p_customer_spec_rec.cust_id
329 AND p_customer_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_customer_spec_rec.date_effective + 1)
330 AND ((l_orgn_code = NVL(b.orgn_code,l_orgn_code)) OR ( p_customer_spec_rec.look_in_other_orgn = 'Y' and l_orgn_code <> b.orgn_code ))
331 AND ((p_customer_spec_rec.org_id = b.org_id) OR ( p_customer_spec_rec.org_id IS NULL AND b.org_id IS NULL)
332 OR (p_customer_spec_rec.org_id IS NOT NULL AND b.org_id IS NULL ))
333 AND ((p_customer_spec_rec.ship_to_site_id = b.ship_to_site_id)
334 OR ( p_customer_spec_rec.ship_to_site_id IS NULL AND b.ship_to_site_id IS NULL)
335 OR (p_customer_spec_rec.ship_to_site_id IS NOT NULL AND b.ship_to_site_id IS NULL ))
336 AND ((p_customer_spec_rec.order_id = b.order_id)
337 OR ( p_customer_spec_rec.order_id IS NULL AND b.order_id IS NULL)
338 OR (p_customer_spec_rec.order_id IS NOT NULL AND b.order_id IS NULL ))
339 AND ((l_order_line_number = b.order_line) OR ( l_order_line_number IS NULL AND b.order_line IS NULL)
340 OR (l_order_line_number IS NOT NULL AND b.order_line IS NULL ))
341 ORDER BY DECODE(a.grade,p_customer_spec_rec.grade,1,2),b.order_line_id,b.order_line,b.order_id,b.ship_to_site_id,b.org_id,DECODE(b.orgn_code,l_orgn_code,1,NULL,2,3);
342
343 --bug# 2982799
344 --compare only the order line number. not the line id.remove the order_line_id CLAUSE.
345
346 l_match_spec_rec cr_match_spec%ROWTYPE;
347
348 BEGIN
349
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351 FND_MSG_PUB.initialize;
352 l_position := '010';
353
354 IF p_customer_spec_rec.item_id IS NULL OR p_customer_spec_rec.cust_id IS NULL OR
355 p_customer_spec_rec.date_effective IS NULL THEN
356 RAISE REQ_FIELDS_MISSING;
357 END IF;
358
359 IF p_customer_spec_rec.orgn_code IS NULL THEN
360 IF p_customer_spec_rec.whse_code IS NOT NULL THEN
361 BEGIN
362 SELECT orgn_code INTO l_orgn_code
363 FROM IC_WHSE_MST
364 WHERE whse_code = p_customer_spec_rec.whse_code;
365 EXCEPTION
366 WHEN OTHERS THEN
367 RAISE INVALID_WHSE;
368 END;
369 ELSE
370 RAISE NO_ORGN_WHSE;
371 END IF;
372 ELSE
373 l_orgn_code := p_customer_spec_rec.orgn_code;
374 END IF;
375
376 --bug# 2982799
377 --compare only the order line number. not the line id.
378 --Validity rule form stores the line number only till 1.1(line_number + shipment_number/10)
379 --In Samples form user enters complete line number (it could be 1.1 and if it is split it could be 1.1.1)
380 --But while passing,sample will pass as 1.1 Also line_id is passed from sample form.
381 --Pick lot screen always passes the line_id. We would derive the line number from the line_id in the format d.d
382 --While matching the spec, we would ignore the order_line_id.
383
384
385 -- get the line_number for the order_line_id
386 IF p_customer_spec_rec.order_line IS NULL THEN
387 IF p_customer_spec_rec.order_line_id IS NOT NULL THEN
388 BEGIN
389 SELECT line_number + (shipment_number/10) INTO l_order_line_number
390 FROM OE_ORDER_LINES_ALL
391 WHERE line_id = p_customer_spec_rec.order_line_id ;
392 EXCEPTION
393 WHEN OTHERS THEN
394 RAISE INVALID_ORDER_LINE;
395 END;
396 END IF;
397 ELSE
398 l_order_line_number := p_customer_spec_rec.order_line ;
399 END IF;
400
401 IF p_customer_spec_rec.grade IS NULL THEN
402 BEGIN
403 SELECT grade_ctl,qc_grade INTO l_grade_ctl ,l_item_default_grade
404 FROM IC_ITEM_MST_B
405 WHERE ITEM_ID = p_customer_spec_rec.item_id ;
406
407 IF l_grade_ctl = 0 THEN
408 l_check_for_null_grade := 'Y';
409 ELSE
410 -- if item is grade ctl and grade is not passed, check for null grade and item's default grade in that order.
411 l_check_for_null_grade := 'Y';
412 l_check_for_default_grade := 'Y';
413 END IF;
414
415 EXCEPTION WHEN OTHERS THEN
416 RAISE INVALID_ITEM;
417 END ;
418 ELSE
419 l_grade_ctl := 1;
423
420 l_check_for_given_grade := 'Y';
421 l_check_for_null_grade := 'Y';
422 END IF;
424 l_position := '020';
425
426 OPEN cr_match_spec;
427 FETCH cr_match_spec INTO l_match_spec_rec ;
428 IF cr_match_spec%NOTFOUND THEN
429 CLOSE cr_match_spec;
430 RETURN FALSE;
431 END IF;
432 CLOSE cr_match_spec;
433
434 -- no need to compare order_line_no. Compare just the order_line_id. Line_no is not passed by the calling program.
435 IF p_customer_spec_rec.exact_match = 'Y' THEN
436 IF (( p_customer_spec_rec.grade = l_match_spec_rec.grade)
437 OR ( p_customer_spec_rec.grade IS NULL AND l_match_spec_rec.grade IS NULL))
438 AND
439 (( p_customer_spec_rec.order_line_id = l_match_spec_rec.order_line_id)
440 OR ( p_customer_spec_rec.order_line_id IS NULL AND l_match_spec_rec.order_line_id IS NULL))
441 AND
442 (( p_customer_spec_rec.order_id = l_match_spec_rec.order_id)
443 OR ( p_customer_spec_rec.order_id IS NULL AND l_match_spec_rec.order_id IS NULL))
444 AND
445 (( p_customer_spec_rec.ship_to_site_id = l_match_spec_rec.ship_to_site_id)
446 OR ( p_customer_spec_rec.ship_to_site_id IS NULL AND l_match_spec_rec.ship_to_site_id IS NULL))
447 AND
448 (( p_customer_spec_rec.org_id = l_match_spec_rec.org_id)
449 OR ( p_customer_spec_rec.org_id IS NULL AND l_match_spec_rec.org_id IS NULL))
450 AND (l_orgn_code = l_match_spec_rec.orgn_code)
451 THEN
452 x_spec_id := l_match_spec_rec.spec_id ;
453 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
454 RETURN TRUE;
455 ELSE
456 RETURN FALSE;
457 END IF;
458 ELSE
459 x_spec_id := l_match_spec_rec.spec_id ;
460 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
461 RETURN TRUE;
462 END IF;
463
464
465 EXCEPTION
466 WHEN REQ_FIELDS_MISSING THEN
467 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC');
468 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
469 x_return_status := FND_API.G_RET_STS_ERROR ;
470 RETURN FALSE;
471 WHEN NO_ORGN_WHSE THEN
472 gmd_api_pub.log_message('GMD_NO_ORGN_WHSE');
473 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
474 x_return_status := FND_API.G_RET_STS_ERROR ;
475 RETURN FALSE;
476 WHEN INVALID_WHSE THEN
477 gmd_api_pub.log_message('GMD_INVALID_WHSE','WHSE',p_customer_spec_rec.whse_code);
478 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
479 x_return_status := FND_API.G_RET_STS_ERROR ;
480 RETURN FALSE;
481 WHEN INVALID_ORDER_LINE THEN
482 gmd_api_pub.log_message('GMD_INVALID_ORDER_LINE','LINE',to_char(p_customer_spec_rec.order_line_id));
483 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
484 x_return_status := FND_API.G_RET_STS_ERROR ;
485 RETURN FALSE;
486 WHEN INVALID_ITEM THEN
487 gmd_api_pub.log_message('GMD_INVALID_ITEM','ITEM',to_char(p_customer_spec_rec.item_id));
488 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
489 x_return_status := FND_API.G_RET_STS_ERROR ;
490 RETURN FALSE;
491
492 WHEN OTHERS THEN
493 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
494 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
496 RETURN FALSE;
497 END FIND_CUSTOMER_SPEC;
498
499 FUNCTION FIND_CUST_OR_INV_SPEC(
500 p_customer_spec_rec IN customer_spec_rec_type,
501 x_spec_id OUT NOCOPY NUMBER,
502 x_spec_vr_id OUT NOCOPY NUMBER,
503 x_spec_type OUT NOCOPY VARCHAR2,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_message_data OUT NOCOPY VARCHAR2 )
506 RETURN BOOLEAN IS
507
508 l_position VARCHAR2(3);
509 l_inventory_spec_rec_type inventory_spec_rec_type ;
510
511 BEGIN
512 l_position := '010';
513 IF FIND_CUSTOMER_SPEC(p_customer_spec_rec => p_customer_spec_rec,
514 x_spec_id => x_spec_id,
515 x_spec_vr_id => x_spec_vr_id,
516 x_return_status => x_return_status,
517 x_message_data => x_message_data ) THEN
518 x_spec_type := 'C';
519 RETURN TRUE;
520 ELSIF x_return_status <> 'S' THEN
521 RETURN FALSE;
522 END IF;
523
524 l_position := '020';
525
526 IF NVL(p_customer_spec_rec.exact_match,'N') = 'N' THEN
527 l_inventory_spec_rec_type.item_id := p_customer_spec_rec.item_id ;
528 l_inventory_spec_rec_type.grade := p_customer_spec_rec.grade ;
529 l_inventory_spec_rec_type.orgn_code := p_customer_spec_rec.orgn_code ;
530 l_inventory_spec_rec_type.lot_id := p_customer_spec_rec.lot_id ;
531 l_inventory_spec_rec_type.lot_no := p_customer_spec_rec.lot_no ;
532 l_inventory_spec_rec_type.sublot_no := p_customer_spec_rec.sublot_no ;
533 l_inventory_spec_rec_type.whse_code := p_customer_spec_rec.whse_code ;
534 l_inventory_spec_rec_type.date_effective := p_customer_spec_rec.date_effective ;
538
535 l_inventory_spec_rec_type.exact_match := p_customer_spec_rec.exact_match ;
536
537 l_position := '030';
539 IF FIND_INVENTORY_SPEC(p_inventory_spec_rec => l_inventory_spec_rec_type,
540 x_spec_id => x_spec_id,
541 x_spec_vr_id => x_spec_vr_id,
542 x_return_status => x_return_status,
543 x_message_data => x_message_data ) THEN
544 x_spec_type := 'I';
545 RETURN TRUE;
546 ELSE
547 RETURN FALSE;
548 END IF;
549 ELSE
550 RETURN FALSE;
551 END IF;
552
553 EXCEPTION WHEN OTHERS THEN
554 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_CUST_OR_INV_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
555 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
557 RETURN FALSE;
558 END FIND_CUST_OR_INV_SPEC ;
559
560
561
562 --Start of comments
563 --+========================================================================+
564 --| API Name : find_wip_spec |
565 --| TYPE : Group |
566 --| Notes : This function RETURN TRUE if matching WIP |
567 --| spec. is found else it RETURN FALSE. |
568 --| If matching WIP spec is found,then |
569 --| it returns matching spec_id and spec_vr_id |
570 --| HISTORY |
571 --| Mahesh Chandak 6-Aug-2002 Created. |
572 --| Brenda Stone 1-Mar-2004 Bug 3473559; Added Hints for spec_id |
573 --| |
574 --| Calling Program : - Samples form |
575 --| - Batch Creation |
576 --+========================================================================+
577 -- End of comments
578
579 FUNCTION FIND_WIP_SPEC(p_wip_spec_rec IN wip_spec_rec_type,
580 x_spec_id OUT NOCOPY NUMBER,
581 x_spec_vr_id OUT NOCOPY NUMBER,
582 x_return_status OUT NOCOPY VARCHAR2,
583 x_message_data OUT NOCOPY VARCHAR2 )
584 RETURN BOOLEAN IS
585
586
587 l_position VARCHAR2(3);
588 l_grade_ctl NUMBER ;
589 l_item_default_grade QC_GRAD_MST.QC_GRADE%TYPE ;
590 l_check_for_given_grade VARCHAR2(1) := 'N';
591 l_check_for_null_grade VARCHAR2(1) := 'N';
592 l_check_for_default_grade VARCHAR2(1) := 'N';
593 l_recipe_no GMD_RECIPES_B.recipe_no%TYPE;
594 l_formula_no FM_FORM_MST_B.formula_no%TYPE;
595 l_routing_no GMD_ROUTINGS_B.routing_no%TYPE;
596 l_oprn_no GMD_OPERATIONS_B.oprn_no%TYPE;
597 l_recipe_version GMD_RECIPES_B.recipe_version%TYPE;
598 l_formula_vers FM_FORM_MST_B.formula_vers%TYPE;
599 l_routing_vers GMD_ROUTINGS_B.routing_vers%TYPE;
600 l_oprn_vers GMD_OPERATIONS_B.oprn_vers%TYPE;
601 l_step_no NUMBER(10);
602
603 REQ_FIELDS_MISSING EXCEPTION;
604 INVALID_ITEM EXCEPTION;
605 INVALID_RECIPE EXCEPTION;
606 INVALID_FORMULA EXCEPTION;
607 INVALID_OPRN EXCEPTION;
608 INVALID_ROUTING EXCEPTION;
609 INVALID_STEP EXCEPTION;
610
611 /* Bug 3473559; Added Hint for spec_id */
612 -- Bug 3934121; Removed spec_status from Where clause
613
614 CURSOR cr_match_spec IS
615 SELECT /*+ INDEX ( b gmd_wip_spec_vrs_n1 ) */
616 a.spec_id,b.spec_vr_id,a.grade grade,
617 DECODE(a.grade,p_wip_spec_rec.grade,1,2) grade_order_by,
618 b.charge,b.step_no,
619 b.routing_vers,b.routing_no,b.formulaline_id,
620 b.formula_vers,b.formula_no,b.recipe_version,
621 b.recipe_no,b.batch_id,b.oprn_vers,b.oprn_no,b.orgn_code
622 FROM gmd_specifications_b a,gmd_wip_spec_vrs b
623 WHERE
624 a.item_id = p_wip_spec_rec.item_id
625 AND a.delete_mark = 0
626 AND ((l_check_for_given_grade = 'Y' and p_wip_spec_rec.grade = a.grade ) OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
627 OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
628 AND a.spec_id = b.spec_id
629 AND b.delete_mark = 0
630 AND p_wip_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_wip_spec_rec.date_effective)
631 AND (p_wip_spec_rec.orgn_code = NVL(b.orgn_code,p_wip_spec_rec.orgn_code))
632 AND ((p_wip_spec_rec.batch_id = b.batch_id) OR ( p_wip_spec_rec.batch_id IS NULL AND b.batch_id IS NULL)
633 OR (p_wip_spec_rec.batch_id IS NOT NULL AND b.batch_id IS NULL ))
634 AND ((l_formula_no = b.formula_no) OR ( l_formula_no IS NULL AND b.formula_no IS NULL)
635 OR (l_formula_no IS NOT NULL AND b.formula_no IS NULL ))
636 AND ((l_formula_vers = b.formula_vers) OR ( l_formula_vers IS NULL AND b.formula_vers IS NULL)
637 OR (l_formula_vers IS NOT NULL AND b.formula_vers IS NULL ))
638 AND ((l_recipe_no = b.recipe_no) OR ( l_recipe_no IS NULL AND b.recipe_no IS NULL)
639 OR (l_recipe_no IS NOT NULL AND b.recipe_no IS NULL ))
640 AND ((l_recipe_version = b.recipe_version) OR ( l_recipe_version IS NULL AND b.recipe_version IS NULL)
641 OR (l_recipe_version IS NOT NULL AND b.recipe_version IS NULL ))
642 AND ((p_wip_spec_rec.charge = b.charge) OR ( p_wip_spec_rec.charge IS NULL AND b.charge IS NULL)
643 OR (p_wip_spec_rec.charge IS NOT NULL AND b.charge IS NULL ))
644 AND ((l_step_no = b.step_no) OR (l_step_no IS NULL AND b.step_no IS NULL)
645 OR (nvl(p_wip_spec_rec.find_spec_with_step,'N') = 'N' and l_step_no IS NOT NULL AND b.step_no IS NULL ))
646 AND ((l_routing_no = b.routing_no) OR ( l_routing_no IS NULL AND b.routing_no IS NULL)
647 OR (l_routing_no IS NOT NULL AND b.routing_no IS NULL ))
648 AND ((l_routing_vers = b.routing_vers) OR ( l_routing_vers IS NULL AND b.routing_vers IS NULL)
649 OR (l_routing_vers IS NOT NULL AND b.routing_vers IS NULL ))
650 AND ((p_wip_spec_rec.formulaline_id = b.formulaline_id)
651 OR ( p_wip_spec_rec.formulaline_id IS NULL AND b.formulaline_id IS NULL)
652 OR (p_wip_spec_rec.formulaline_id IS NOT NULL AND b.formulaline_id IS NULL ))
653 AND ((l_oprn_no = b.oprn_no) OR ( l_oprn_no IS NULL AND b.oprn_no IS NULL)
654 OR (l_oprn_no IS NOT NULL AND b.oprn_no IS NULL ))
655 AND ((l_oprn_vers = b.oprn_vers) OR ( l_oprn_vers IS NULL AND b.oprn_vers IS NULL)
656 OR (l_oprn_vers IS NOT NULL AND b.oprn_vers IS NULL ))
657 ORDER BY DECODE(a.grade,p_wip_spec_rec.grade,1,2),b.charge,b.step_no,
658 b.routing_id,b.routing_no,b.formulaline_id,b.formula_id,b.formula_no,
659 b.recipe_id,b.recipe_no,b.batch_id,b.oprn_id,b.oprn_no,b.orgn_code ;
660
661 l_match_spec_rec cr_match_spec%ROWTYPE;
662
663 BEGIN
664
665
666 x_return_status := FND_API.G_RET_STS_SUCCESS;
667 FND_MSG_PUB.initialize;
668 l_position := '010';
669
670 IF p_wip_spec_rec.item_id IS NULL OR p_wip_spec_rec.orgn_code IS NULL OR p_wip_spec_rec.date_effective IS NULL THEN
671 RAISE REQ_FIELDS_MISSING;
672 END IF;
673
674 IF p_wip_spec_rec.recipe_id IS NOT NULL AND p_wip_spec_rec.recipe_no IS NULL AND p_wip_spec_rec.recipe_version IS NULL THEN
675 BEGIN
676 SELECT recipe_no,recipe_version INTO l_recipe_no,l_recipe_version
677 FROM GMD_RECIPES_B
678 WHERE recipe_id = p_wip_spec_rec.recipe_id ;
679
680 EXCEPTION
681 WHEN OTHERS THEN
682 RAISE INVALID_RECIPE;
683 END;
684 ELSE
685 l_recipe_no := p_wip_spec_rec.recipe_no;
686 l_recipe_version := p_wip_spec_rec.recipe_version;
687 END IF;
688
689 IF p_wip_spec_rec.formula_id IS NOT NULL AND p_wip_spec_rec.formula_no IS NULL AND p_wip_spec_rec.formula_vers IS NULL THEN
690 BEGIN
691 SELECT formula_no,formula_vers INTO l_formula_no,l_formula_vers
692 FROM fm_form_mst_b
693 WHERE formula_id = p_wip_spec_rec.formula_id;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 RAISE INVALID_FORMULA;
698 END;
699 ELSE
700 l_formula_no := p_wip_spec_rec.formula_no;
701 l_formula_vers := p_wip_spec_rec.formula_vers;
702 END IF ;
703
704 IF p_wip_spec_rec.routing_id IS NOT NULL AND p_wip_spec_rec.routing_no IS NULL AND p_wip_spec_rec.routing_vers IS NULL THEN
705 BEGIN
706 SELECT routing_no,routing_vers INTO l_routing_no,l_routing_vers
707 FROM gmd_routings_b
711 WHEN OTHERS THEN
708 WHERE routing_id = p_wip_spec_rec.routing_id ;
709
710 EXCEPTION
712 RAISE INVALID_ROUTING;
713 END;
714 ELSE
715 l_routing_no := p_wip_spec_rec.routing_no;
716 l_routing_vers := p_wip_spec_rec.routing_vers;
717 END IF ;
718
719 IF p_wip_spec_rec.oprn_id IS NOT NULL AND p_wip_spec_rec.oprn_no IS NULL AND p_wip_spec_rec.oprn_vers IS NULL THEN
720 BEGIN
721 SELECT oprn_no,oprn_vers INTO l_oprn_no,l_oprn_vers
722 FROM gmd_operations_b
723 WHERE oprn_id = p_wip_spec_rec.oprn_id;
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 RAISE INVALID_OPRN;
728 END;
729 ELSE
730 l_oprn_no := p_wip_spec_rec.oprn_no;
731 l_oprn_vers := p_wip_spec_rec.oprn_vers;
732 END IF;
733
734 -- get step_no if step_id is passed instead of step_no.
735 IF p_wip_spec_rec.step_id IS NOT NULL AND p_wip_spec_rec.step_no IS NULL THEN
736 IF p_wip_spec_rec.batch_id IS NOT NULL THEN
737 BEGIN
738 SELECT BATCHSTEP_NO INTO l_step_no
739 FROM gme_batch_steps
740 WHERE batchstep_id = p_wip_spec_rec.step_id
741 AND batch_id = p_wip_spec_rec.batch_id;
742
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 BEGIN
747 SELECT ROUTINGSTEP_NO INTO l_step_no
748 FROM fm_rout_dtl
749 WHERE routingstep_id = p_wip_spec_rec.step_id
750 AND routing_id = p_wip_spec_rec.routing_id;
751
752 EXCEPTION WHEN OTHERS THEN
753 RAISE INVALID_STEP;
754 END ;
755 END;
756 ELSIF p_wip_spec_rec.routing_id IS NOT NULL THEN
757 BEGIN
758 SELECT ROUTINGSTEP_NO INTO l_step_no
759 FROM fm_rout_dtl
760 WHERE routingstep_id = p_wip_spec_rec.step_id
761 AND routing_id = p_wip_spec_rec.routing_id ;
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 BEGIN
766 SELECT BATCHSTEP_NO INTO l_step_no
767 FROM gme_batch_steps
768 WHERE batchstep_id = p_wip_spec_rec.step_id
769 AND batch_id = p_wip_spec_rec.batch_id;
770
771 EXCEPTION WHEN OTHERS THEN
772 RAISE INVALID_STEP;
773 END ;
774 END;
775 ELSE
776 RAISE INVALID_STEP; -- should have either batch or routing with the step.
777 END IF;
778 ELSE
779 l_step_no := p_wip_spec_rec.step_no;
780 END IF;
781
782 --MCHANDAK bug# 2645698
783 -- created additional paramater find_spec_with_step which will be set to 'Y' when
784 -- calling from batch step creation workflow.Also changed the main select.
785
786 -- need to pass a step if one needs a WIP with step.
787 IF p_wip_spec_rec.find_spec_with_step = 'Y' and l_step_no IS NULL THEN
788 RAISE INVALID_STEP;
789 END IF;
790
791 l_position := '020';
792
793 IF p_wip_spec_rec.grade IS NULL THEN
794 BEGIN
795 SELECT grade_ctl,qc_grade INTO l_grade_ctl ,l_item_default_grade
796 FROM IC_ITEM_MST_B
797 WHERE ITEM_ID = p_wip_spec_rec.item_id ;
798
799 IF l_grade_ctl = 0 THEN
800 l_check_for_null_grade := 'Y';
801 ELSE
802 -- if item is grade ctl and grade is not passed, check for null grade and item's default grade in that order.
803 l_check_for_null_grade := 'Y';
804 l_check_for_default_grade := 'Y';
805 END IF;
806
807 EXCEPTION WHEN OTHERS THEN
808 RAISE INVALID_ITEM;
809 END ;
810 ELSE
811 l_grade_ctl := 1;
812 l_check_for_given_grade := 'Y';
813 l_check_for_null_grade := 'Y';
814 END IF;
815
816 l_position := '030';
817
818 OPEN cr_match_spec;
819 FETCH cr_match_spec INTO l_match_spec_rec ;
820 IF cr_match_spec%NOTFOUND THEN
821 CLOSE cr_match_spec;
822 RETURN FALSE;
823 END IF;
824 CLOSE cr_match_spec;
825
826 l_position := '040';
827
828 IF p_wip_spec_rec.exact_match = 'Y' THEN
829 IF (( p_wip_spec_rec.grade = l_match_spec_rec.grade)
830 OR ( p_wip_spec_rec.grade IS NULL AND l_match_spec_rec.grade IS NULL))
831 AND
832 (( p_wip_spec_rec.batch_id = l_match_spec_rec.batch_id)
833 OR ( p_wip_spec_rec.batch_id IS NULL AND l_match_spec_rec.batch_id IS NULL))
834 AND
835 (( l_recipe_no = l_match_spec_rec.recipe_no)
836 OR ( l_recipe_no IS NULL AND l_match_spec_rec.recipe_no IS NULL))
837 AND
838 (( l_recipe_version = l_match_spec_rec.recipe_version)
839 OR ( l_recipe_version IS NULL AND l_match_spec_rec.recipe_version IS NULL))
840 AND
841 (( l_routing_no = l_match_spec_rec.routing_no)
842 OR ( l_routing_no IS NULL AND l_match_spec_rec.routing_no IS NULL))
843 AND
844 (( l_routing_vers = l_match_spec_rec.routing_vers)
845 OR ( l_routing_vers IS NULL AND l_match_spec_rec.routing_vers IS NULL))
846 AND
847 (( l_oprn_no = l_match_spec_rec.oprn_no)
848 OR ( l_oprn_no IS NULL AND l_match_spec_rec.oprn_no IS NULL))
849 AND
850 (( l_oprn_vers = l_match_spec_rec.oprn_vers)
851 OR ( l_oprn_vers IS NULL AND l_match_spec_rec.oprn_vers IS NULL))
852 AND
853 (( l_formula_no = l_match_spec_rec.formula_no)
854 OR ( l_formula_no IS NULL AND l_match_spec_rec.formula_no IS NULL))
855 AND
856 (( l_formula_vers = l_match_spec_rec.formula_vers)
857 OR ( l_formula_vers IS NULL AND l_match_spec_rec.formula_vers IS NULL))
858 AND
859 (( p_wip_spec_rec.formulaline_id = l_match_spec_rec.formulaline_id)
863 OR ( l_step_no IS NULL AND l_match_spec_rec.step_no IS NULL))
860 OR ( p_wip_spec_rec.formulaline_id IS NULL AND l_match_spec_rec.formulaline_id IS NULL))
861 AND
862 (( l_step_no = l_match_spec_rec.step_no)
864 AND
865 (( p_wip_spec_rec.charge = l_match_spec_rec.charge)
866 OR ( p_wip_spec_rec.charge IS NULL AND l_match_spec_rec.charge IS NULL))
867 AND (p_wip_spec_rec.orgn_code = l_match_spec_rec.orgn_code)
868 THEN
869 x_spec_id := l_match_spec_rec.spec_id ;
870 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
871 RETURN TRUE;
872 ELSE
873 RETURN FALSE;
874 END IF;
875 ELSE
876 x_spec_id := l_match_spec_rec.spec_id ;
877 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
878 RETURN TRUE;
879 END IF;
880
881 EXCEPTION
882 WHEN REQ_FIELDS_MISSING THEN
883 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC');
884 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
885 x_return_status := FND_API.G_RET_STS_ERROR ;
886 RETURN FALSE;
887 WHEN INVALID_ITEM THEN
888 gmd_api_pub.log_message('GMD_INVALID_ITEM','ITEM',to_char(p_wip_spec_rec.item_id));
889 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
890 x_return_status := FND_API.G_RET_STS_ERROR ;
891 RETURN FALSE;
892 WHEN INVALID_FORMULA THEN
893 gmd_api_pub.log_message('GMD_INVALID_FORMULA','FORMULA',to_char(p_wip_spec_rec.formula_id));
894 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
895 x_return_status := FND_API.G_RET_STS_ERROR ;
896 RETURN FALSE;
897 WHEN INVALID_ROUTING THEN
898 gmd_api_pub.log_message('GMD_INVALID_ROUTING','ROUTING',to_char(p_wip_spec_rec.routing_id));
899 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
900 x_return_status := FND_API.G_RET_STS_ERROR ;
901 RETURN FALSE;
902 WHEN INVALID_OPRN THEN
903 gmd_api_pub.log_message('GMD_INVALID_OPRN','OPRN',to_char(p_wip_spec_rec.oprn_id));
904 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
905 x_return_status := FND_API.G_RET_STS_ERROR ;
906 RETURN FALSE;
907 WHEN INVALID_STEP THEN
908 gmd_api_pub.log_message('GMD_INVALID_STEP','STEP',to_char(p_wip_spec_rec.step_id));
909 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
910 x_return_status := FND_API.G_RET_STS_ERROR ;
911 RETURN FALSE;
912 WHEN INVALID_RECIPE THEN
913 gmd_api_pub.log_message('GMD_INVALID_RECIPE','RECIPE',to_char(p_wip_spec_rec.recipe_id));
914 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
915 x_return_status := FND_API.G_RET_STS_ERROR ;
916 RETURN FALSE;
917 WHEN OTHERS THEN
918 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_WIP_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
919 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
921 RETURN FALSE;
922 END FIND_WIP_SPEC;
923
924 FUNCTION FIND_WIP_OR_INV_SPEC(
925 p_wip_spec_rec IN wip_spec_rec_type,
926 x_spec_id OUT NOCOPY NUMBER,
927 x_spec_vr_id OUT NOCOPY NUMBER,
928 x_spec_type OUT NOCOPY VARCHAR2,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_message_data OUT NOCOPY VARCHAR2 )
931
932 RETURN BOOLEAN IS
933 l_inventory_spec_rec_type inventory_spec_rec_type ;
934 l_position VARCHAR2(3);
935 BEGIN
936
937 l_position := '010';
938 IF FIND_WIP_SPEC( p_wip_spec_rec => p_wip_spec_rec,
939 x_spec_id => x_spec_id,
940 x_spec_vr_id => x_spec_vr_id,
941 x_return_status => x_return_status,
942 x_message_data => x_message_data ) THEN
943 x_spec_type := 'W';
944 RETURN TRUE;
945 ELSIF x_return_status <> 'S' THEN
946 RETURN FALSE;
947 END IF;
948
949 l_position := '020';
950
951 IF NVL(p_wip_spec_rec.exact_match,'N') = 'N' THEN
952 l_inventory_spec_rec_type.item_id := p_wip_spec_rec.item_id ;
953 l_inventory_spec_rec_type.grade := p_wip_spec_rec.grade ;
954 l_inventory_spec_rec_type.orgn_code := p_wip_spec_rec.orgn_code ;
955 l_inventory_spec_rec_type.lot_id := p_wip_spec_rec.lot_id ;
956 l_inventory_spec_rec_type.lot_no := p_wip_spec_rec.lot_no ;
957 l_inventory_spec_rec_type.sublot_no := p_wip_spec_rec.sublot_no ;
958 l_inventory_spec_rec_type.date_effective := p_wip_spec_rec.date_effective ;
959 l_inventory_spec_rec_type.exact_match := p_wip_spec_rec.exact_match ;
960
961 l_position := '030';
962 IF FIND_INVENTORY_SPEC(p_inventory_spec_rec => l_inventory_spec_rec_type,
963 x_spec_id => x_spec_id,
964 x_spec_vr_id => x_spec_vr_id,
965 x_return_status => x_return_status,
966 x_message_data => x_message_data ) THEN
967 x_spec_type := 'I';
968 RETURN TRUE;
969 ELSE
970 RETURN FALSE;
971 END IF;
972 ELSE
973 RETURN FALSE;
974 END IF;
975
976 EXCEPTION WHEN OTHERS THEN
977 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_WIP_OR_INV_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
978 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
980 RETURN FALSE;
981 END FIND_WIP_OR_INV_SPEC ;
982
983
984 --Start of comments
985 --+========================================================================+
986 --| API Name : find_supplier_spec |
990 --| If matching supplier spec is found,then |
987 --| TYPE : Group |
988 --| Notes : This function RETURN TRUE if matching supplier |
989 --| spec. is found else it RETURN FALSE. |
991 --| it returns matching spec_id and spec_vr_id |
992 --| HISTORY |
993 --| Mahesh Chandak 6-Aug-2002 Created. |
994 --| Brenda Stone 1-Mar-2004 Bug 3473559; Added Hints for spec_id |
995 --| Calling Program : Samples form |
996 --| Receiving Transaction Event(Workflow) |
997 --+========================================================================+
998 -- End of comments
999
1000 FUNCTION FIND_SUPPLIER_SPEC(p_supplier_spec_rec IN supplier_spec_rec_type,
1001 x_spec_id OUT NOCOPY NUMBER,
1002 x_spec_vr_id OUT NOCOPY NUMBER,
1003 x_return_status OUT NOCOPY VARCHAR2,
1004 x_message_data OUT NOCOPY VARCHAR2 )
1005 RETURN BOOLEAN IS
1006
1007 l_orgn_code VARCHAR2(4);
1008 l_position VARCHAR2(3);
1009 l_grade_ctl NUMBER ;
1010 l_item_default_grade QC_GRAD_MST.QC_GRADE%TYPE ;
1011 l_check_for_given_grade VARCHAR2(1) := 'N';
1012 l_check_for_null_grade VARCHAR2(1) := 'N';
1013 l_check_for_default_grade VARCHAR2(1) := 'N';
1014
1015 NO_ORGN_WHSE EXCEPTION;
1016 INVALID_WHSE EXCEPTION;
1017 REQ_FIELDS_MISSING EXCEPTION;
1018 INVALID_ITEM EXCEPTION;
1019
1020 /* Bug 3473559; Added Hint for spec_id */
1021 -- Bug 3934121; Removed spec_status from Where clause
1022
1023 CURSOR cr_match_spec IS
1024 SELECT /*+ INDEX ( b gmd_supplier_spec_vrs_n1) */
1025 a.spec_id,b.spec_vr_id,a.grade,
1026 decode(a.grade,p_supplier_spec_rec.grade,1,2),b.po_line_id,
1027 b.po_header_id,b.supplier_site_id,b.supplier_id,b.orgn_code
1028 FROM gmd_specifications_b a,gmd_supplier_spec_vrs b
1029 WHERE
1030 a.item_id = p_supplier_spec_rec.item_id
1031 AND a.delete_mark = 0
1032 AND ((l_check_for_given_grade = 'Y' and p_supplier_spec_rec.grade = a.grade )
1033 OR (l_check_for_null_grade = 'Y' AND a.grade IS NULL )
1034 OR (l_check_for_default_grade = 'Y' AND a.grade = l_item_default_grade))
1035 AND a.spec_id = b.spec_id
1036 AND b.delete_mark = 0
1037 AND b.supplier_id = p_supplier_spec_rec.supplier_id
1038 AND p_supplier_spec_rec.date_effective between b.start_date and nvl(b.end_date,p_supplier_spec_rec.date_effective)
1039 AND ((p_supplier_spec_rec.po_line_id = b.po_line_id)
1040 OR ( p_supplier_spec_rec.po_line_id IS NULL AND b.po_line_id IS NULL)
1041 OR (p_supplier_spec_rec.po_line_id IS NOT NULL AND b.po_line_id IS NULL ))
1042 AND ((p_supplier_spec_rec.po_header_id = b.po_header_id)
1043 OR ( p_supplier_spec_rec.po_header_id IS NULL AND b.po_header_id IS NULL)
1044 OR (p_supplier_spec_rec.po_header_id IS NOT NULL AND b.po_header_id IS NULL ))
1045 AND ((p_supplier_spec_rec.supplier_site_id = b.supplier_site_id)
1046 OR ( p_supplier_spec_rec.supplier_site_id IS NULL AND b.supplier_site_id IS NULL)
1047 OR (p_supplier_spec_rec.supplier_site_id IS NOT NULL AND b.supplier_site_id IS NULL ))
1048 AND (l_orgn_code = NVL(b.orgn_code,l_orgn_code))
1049 ORDER BY DECODE(a.grade,p_supplier_spec_rec.grade,1,2),b.po_line_id,
1050 b.po_header_id,b.supplier_site_id,b.supplier_id,b.orgn_code ;
1051
1052 l_match_spec_rec cr_match_spec%ROWTYPE;
1053
1054 BEGIN
1055
1056 x_return_status := FND_API.G_RET_STS_SUCCESS;
1057 FND_MSG_PUB.initialize;
1058 l_position := '010';
1059
1060 IF p_supplier_spec_rec.item_id IS NULL
1061 OR p_supplier_spec_rec.date_effective IS NULL
1062 OR p_supplier_spec_rec.supplier_id IS NULL THEN
1063 RAISE REQ_FIELDS_MISSING;
1064 END IF;
1065
1066 IF p_supplier_spec_rec.orgn_code IS NULL THEN
1067 IF p_supplier_spec_rec.whse_code IS NOT NULL THEN
1068 BEGIN
1069 SELECT orgn_code INTO l_orgn_code
1070 FROM IC_WHSE_MST
1071 WHERE whse_code = p_supplier_spec_rec.whse_code;
1072 EXCEPTION
1073 WHEN OTHERS THEN
1074 RAISE INVALID_WHSE;
1075 END;
1076 ELSE
1077 RAISE NO_ORGN_WHSE;
1078 END IF;
1079 ELSE
1080 l_orgn_code := p_supplier_spec_rec.orgn_code;
1081 END IF;
1082
1083 IF p_supplier_spec_rec.grade IS NULL THEN
1084 BEGIN
1085 SELECT grade_ctl,qc_grade INTO l_grade_ctl ,l_item_default_grade
1086 FROM IC_ITEM_MST_B
1087 WHERE ITEM_ID = p_supplier_spec_rec.item_id ;
1088
1089 IF l_grade_ctl = 0 THEN
1090 l_check_for_null_grade := 'Y';
1091 ELSE
1092 -- if item is grade ctl and grade is not passed, check for null grade and item's default grade in that order.
1093 l_check_for_null_grade := 'Y';
1094 l_check_for_default_grade := 'Y';
1095 END IF;
1096
1097 EXCEPTION WHEN OTHERS THEN
1098 RAISE INVALID_ITEM;
1099 END ;
1100 ELSE
1101 l_grade_ctl := 1;
1102 l_check_for_given_grade := 'Y';
1103 l_check_for_null_grade := 'Y';
1104 END IF;
1105
1106 l_position := '020';
1107
1108 OPEN cr_match_spec;
1109 FETCH cr_match_spec INTO l_match_spec_rec ;
1110 IF cr_match_spec%NOTFOUND THEN
1111 CLOSE cr_match_spec;
1112 RETURN FALSE;
1113 END IF;
1114 CLOSE cr_match_spec;
1115
1116 l_position := '030';
1117
1118 -- do we need to compare grade also ??
1119 IF p_supplier_spec_rec.exact_match = 'Y' THEN
1120 IF (( p_supplier_spec_rec.grade = l_match_spec_rec.grade)
1121 OR ( p_supplier_spec_rec.grade IS NULL AND l_match_spec_rec.grade IS NULL))
1122 AND
1126 (( p_supplier_spec_rec.supplier_site_id = l_match_spec_rec.supplier_site_id)
1123 (( p_supplier_spec_rec.supplier_id = l_match_spec_rec.supplier_id)
1124 OR ( p_supplier_spec_rec.supplier_id IS NULL AND l_match_spec_rec.supplier_id IS NULL))
1125 AND
1127 OR ( p_supplier_spec_rec.supplier_site_id IS NULL AND l_match_spec_rec.supplier_site_id IS NULL))
1128 AND
1129 (( p_supplier_spec_rec.po_header_id = l_match_spec_rec.po_header_id)
1130 OR ( p_supplier_spec_rec.po_header_id IS NULL AND l_match_spec_rec.po_header_id IS NULL))
1131 AND
1132 (( p_supplier_spec_rec.po_line_id = l_match_spec_rec.po_line_id)
1133 OR ( p_supplier_spec_rec.po_line_id IS NULL AND l_match_spec_rec.po_line_id IS NULL))
1134 AND (l_orgn_code = l_match_spec_rec.orgn_code)
1135 THEN
1136 x_spec_id := l_match_spec_rec.spec_id ;
1137 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
1138 RETURN TRUE;
1139 ELSE
1140 RETURN FALSE;
1141 END IF;
1142 ELSE
1143 x_spec_id := l_match_spec_rec.spec_id ;
1144 x_spec_vr_id := l_match_spec_rec.spec_vr_id ;
1145 RETURN TRUE;
1146 END IF;
1147
1148
1149 EXCEPTION
1150 WHEN REQ_FIELDS_MISSING THEN
1151 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_SUPPLIER_SPEC');
1152 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1153 x_return_status := FND_API.G_RET_STS_ERROR ;
1154 RETURN FALSE;
1155 WHEN NO_ORGN_WHSE THEN
1156 gmd_api_pub.log_message('GMD_NO_ORGN_WHSE');
1157 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1158 x_return_status := FND_API.G_RET_STS_ERROR ;
1159 RETURN FALSE;
1160 WHEN INVALID_WHSE THEN
1161 gmd_api_pub.log_message('GMD_INVALID_WHSE','WHSE',p_supplier_spec_rec.whse_code);
1162 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1163 x_return_status := FND_API.G_RET_STS_ERROR ;
1164 RETURN FALSE;
1165 WHEN INVALID_ITEM THEN
1166 gmd_api_pub.log_message('GMD_INVALID_ITEM','ITEM',to_char(p_supplier_spec_rec.item_id));
1167 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1168 x_return_status := FND_API.G_RET_STS_ERROR ;
1169 RETURN FALSE;
1170 WHEN OTHERS THEN
1171 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_SUPPLIER_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1172 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1174 RETURN FALSE;
1175 END FIND_SUPPLIER_SPEC;
1176
1177
1178 FUNCTION FIND_SUPPLIER_OR_INV_SPEC(
1179 p_supplier_spec_rec IN supplier_spec_rec_type,
1180 x_spec_id OUT NOCOPY NUMBER,
1181 x_spec_vr_id OUT NOCOPY NUMBER,
1182 x_spec_type OUT NOCOPY VARCHAR2,
1183 x_return_status OUT NOCOPY VARCHAR2,
1184 x_message_data OUT NOCOPY VARCHAR2)
1185 RETURN BOOLEAN IS
1186 l_inventory_spec_rec_type inventory_spec_rec_type ;
1187 l_position VARCHAR2(3);
1188
1189 BEGIN
1190 l_position := '010';
1191 IF FIND_SUPPLIER_SPEC( p_supplier_spec_rec => p_supplier_spec_rec,
1192 x_spec_id => x_spec_id,
1193 x_spec_vr_id => x_spec_vr_id,
1194 x_return_status => x_return_status,
1195 x_message_data => x_message_data ) THEN
1196 x_spec_type := 'S';
1197 RETURN TRUE;
1198 END IF;
1199
1200 l_position := '020';
1201
1202 IF NVL(p_supplier_spec_rec.exact_match,'N') = 'N' THEN
1203 l_inventory_spec_rec_type.item_id := p_supplier_spec_rec.item_id ;
1204 l_inventory_spec_rec_type.orgn_code := p_supplier_spec_rec.orgn_code ;
1205 l_inventory_spec_rec_type.lot_id := p_supplier_spec_rec.lot_id ;
1206 l_inventory_spec_rec_type.date_effective := p_supplier_spec_rec.date_effective ;
1207 l_inventory_spec_rec_type.exact_match := 'N' ;
1208
1209 --l_inventory_spec_rec_type.grade := p_supplier_spec_rec.grade ;
1210 l_inventory_spec_rec_type.lot_no := p_supplier_spec_rec.lot_no ;
1211 l_inventory_spec_rec_type.sublot_no := p_supplier_spec_rec.sublot_no ;
1212
1213 -- Bug 3143796: Added whse and location to supplier sample, so passing
1214 -- them to inventory spec match
1215 l_inventory_spec_rec_type.whse_code := p_supplier_spec_rec.whse_code ;
1216 l_inventory_spec_rec_type.location := p_supplier_spec_rec.location ;
1217
1218 l_position := '030';
1219
1220 IF FIND_INVENTORY_SPEC(p_inventory_spec_rec => l_inventory_spec_rec_type,
1221 x_spec_id => x_spec_id,
1222 x_spec_vr_id => x_spec_vr_id,
1223 x_return_status => x_return_status,
1224 x_message_data => x_message_data ) THEN
1225 x_spec_type := 'I';
1226 RETURN TRUE;
1227 ELSE
1228
1229 RETURN FALSE;
1230 END IF;
1231 ELSE
1232 RETURN FALSE;
1233 END IF;
1234
1235 EXCEPTION WHEN OTHERS THEN
1236 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.FIND_SUPPLIER_OR_INV_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1237 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1239 RETURN FALSE;
1240 END FIND_SUPPLIER_OR_INV_SPEC ;
1241
1242
1243
1244
1245
1246
1247 --Start of comments
1248 --+=====================================================================================================+
1249 --| API Name : get_result_match_for_spec |
1250 --| TYPE : Group |
1254 --| whse_code - IN PARAMETER warehouse |
1251 --| Notes : |
1252 --| Parameters : item_id - IN PARAMETER item_id of the order line |
1253 --| lot_id - IN PARAMETER lot_id |
1255 --| location - IN PARAMETER location |
1256 --| result_type - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
1257 --| result_type will have 2 values - 'I' for Individual Result, |
1258 --| 'C' - for Composite Result |
1259 --| sample_id - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
1260 --| - This will be used to navigate to the Result form. |
1261 --| spec_match_type - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
1262 --| It can have 3 values. |
1263 --| - NULL If no sample is found, OR no results can be found for this lot, |
1264 --| - 'U' for Unaccepted. If the latest accepted final result is not |
1265 --| within the spec. test range. |
1266 --| - 'A' for Accepted.All the test results for the customer spec are |
1267 --| within the spec. test range |
1268 --| event_spec_disp_id - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
1269 --| - This will be used to navigate to the composite results form. |
1270 --| |
1271 --| Calling Program : - Order Management(Pick lots form) |
1272 --| HISTORY |
1273 --| Mahesh Chandak 1-sep-2002 Created. |
1274 --| |
1275 --+=====================================================================================================+
1276 -- End of comments
1277
1278
1279
1280 PROCEDURE get_result_match_for_spec
1281 ( p_spec_id IN NUMBER
1282 , p_lots IN OUT NOCOPY result_lot_match_tbl
1283 , x_return_status OUT NOCOPY VARCHAR2
1284 , x_message_data OUT NOCOPY VARCHAR2 ) IS
1285
1286 l_position VARCHAR2(3);
1287 l_lot_no VARCHAR2(32);
1288 l_sublot_no VARCHAR2(32);
1289 l_whse_code VARCHAR2(4);
1290 l_location VARCHAR2(16);
1291 l_item_id NUMBER;
1292 l_old_sample_id NUMBER;
1293
1294 -- pick up only required test
1295 CURSOR cr_get_req_spec_tests IS
1296 SELECT gst.test_id
1297 FROM GMD_SPEC_TESTS_B gst
1298 WHERE gst.spec_id = p_spec_id
1299 AND gst.optional_ind is NULL ;
1300
1301 CURSOR cr_get_sample_for_lot IS
1302 SELECT gs.sample_id,gr.test_id,gr.result_value_num,gr.result_value_char
1303 FROM GMD_SAMPLES gs , GMD_RESULTS gr
1304 WHERE gs.sample_id = gr.sample_id
1305 AND gs.delete_mark = 0
1306 AND gs.sample_id IN ( SELECT ssd.sample_id FROM gmd_sample_spec_disp ssd
1307 WHERE ssd.sample_id = gs.sample_id
1308 AND ssd.disposition IN ('3C','4A','5AV','6RJ') )
1309 AND gs.item_id = l_item_id
1310 AND (gs.lot_no = l_lot_no OR gs.lot_no IS NULL)
1311 AND (gs.sublot_no = l_sublot_no OR gs.sublot_no IS NULL)
1312 AND (gs.whse_code = l_whse_code OR gs.whse_code IS NULL)
1313 AND (gs.location = l_location OR gs.location IS NULL )
1314 AND gr.delete_mark = 0
1315 AND (gr.result_value_num IS NOT NULL or gr.result_value_char IS NOT NULL)
1316 ORDER BY gs.lot_no,gs.date_drawn desc,gs.sublot_no,gs.location,gs.whse_code,gr.result_date desc ;
1317 -- 2651353 changed the order by clause. sample date takes preference over sub lot no.
1318 -- looks for a sample within a lot_no with latest sample date.
1319
1320 l_lot_counter BINARY_INTEGER;
1321 l_spec_test_counter BINARY_INTEGER;
1322 REQ_FIELDS_MISSING EXCEPTION;
1323 INVALID_LOT EXCEPTION;
1324 l_sample_rec cr_get_sample_for_lot%ROWTYPE;
1325
1326 TYPE spec_test_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1327
1328 spec_test_list spec_test_tab ;
1329
1330 TYPE result_test_tab IS TABLE OF cr_get_sample_for_lot%ROWTYPE INDEX BY BINARY_INTEGER;
1331
1332 result_test_list result_test_tab ;
1333 l_spec_tests_exist_in_sample BOOLEAN := FALSE;
1334 l_result_in_spec BOOLEAN := TRUE;
1335 l_in_spec VARCHAR2(1); -- returned by the results API
1336 BEGIN
1337
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339 FND_MSG_PUB.initialize;
1340 l_position := '010';
1341
1342 IF p_spec_id IS NULL THEN
1343 RETURN;
1344 END IF;
1345
1346 FOR spec_test_row IN cr_get_req_spec_tests LOOP
1347 spec_test_list(spec_test_row.test_id) := spec_test_row.test_id;
1348 END LOOP;
1349
1350 l_position := '020';
1351
1352 l_lot_counter := p_lots.FIRST;
1353 WHILE l_lot_counter IS NOT NULL
1354 LOOP
1355 IF p_lots(l_lot_counter).item_id IS NULL OR p_lots(l_lot_counter).lot_id IS NULL OR
1356 p_lots(l_lot_counter).whse_code IS NULL THEN
1357 RAISE REQ_FIELDS_MISSING;
1358 END IF;
1359
1360 IF p_lots(l_lot_counter).lot_id IS NOT NULL THEN
1361 BEGIN
1362 SELECT lot_no,sublot_no INTO l_lot_no,l_sublot_no
1363 FROM IC_LOTS_MST
1364 WHERE lot_id = p_lots(l_lot_counter).lot_id
1365 AND item_id = p_lots(l_lot_counter).item_id;
1366
1370 END;
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 RAISE INVALID_LOT;
1371 END IF;
1372
1373 l_item_id := p_lots(l_lot_counter).item_id;
1374 l_whse_code := p_lots(l_lot_counter).whse_code;
1375 l_location := p_lots(l_lot_counter).location;
1376
1377 l_old_sample_id := null;
1378 l_spec_tests_exist_in_sample := FALSE;
1379 l_result_in_spec := TRUE;
1380 result_test_list.DELETE;
1381
1382 l_position := '030';
1383
1384 OPEN cr_get_sample_for_lot ;
1385 LOOP
1386 FETCH cr_get_sample_for_lot INTO l_sample_rec;
1387 IF cr_get_sample_for_lot%NOTFOUND THEN
1388 EXIT ;
1389 END IF;
1390
1391 -- sample changed.check for tests against each sample.
1392 IF l_old_sample_id IS NULL OR l_sample_rec.sample_id <> l_old_sample_id THEN
1393 l_old_sample_id := l_sample_rec.sample_id ;
1394
1395 IF result_test_list.COUNT = spec_test_list.COUNT THEN
1396 l_spec_tests_exist_in_sample := TRUE;
1397 EXIT; -- once a matching sample with all the reqd spec test is found,then do not continue further.
1398 END IF;
1399 result_test_list.DELETE;
1400
1401 -- If the current test is not in the spec, ignore it.
1402 -- If the test is already in the result test list, skip this row.
1403 END IF;
1404 IF spec_test_list.EXISTS(l_sample_rec.test_id) AND
1405 NOT (result_test_list.EXISTS(l_sample_rec.test_id)) THEN
1406 result_test_list(l_sample_rec.test_id) := l_sample_rec;
1407 END IF;
1408
1409 END LOOP;
1410 CLOSE cr_get_sample_for_lot;
1411 -- do check again since the last sample won't go through the first test.
1412 IF result_test_list.COUNT = spec_test_list.COUNT THEN
1413 l_spec_tests_exist_in_sample := TRUE;
1414 END IF;
1415
1416 l_position := '040';
1417
1418 IF l_spec_tests_exist_in_sample THEN
1419 -- check test results against the selected sample are in range as per the given specification
1420 l_spec_test_counter := spec_test_list.FIRST;
1421 WHILE l_spec_test_counter IS NOT NULL
1422 LOOP
1423 l_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec(
1424 p_spec_id => p_spec_id
1425 , p_test_id => spec_test_list(l_spec_test_counter)
1426 , p_rslt_value_num => result_test_list(l_spec_test_counter).result_value_num
1427 , p_rslt_value_char => result_test_list(l_spec_test_counter).result_value_char ) ;
1428 IF l_in_spec IS NULL THEN
1429 l_result_in_spec := FALSE;
1430 EXIT;
1431 END IF;
1432 l_spec_test_counter := spec_test_list.NEXT(l_spec_test_counter);
1433 END LOOP ;
1434 l_position := '050';
1435 IF l_result_in_spec THEN
1436 p_lots(l_lot_counter).sample_id := result_test_list(result_test_list.FIRST).sample_id;
1437 p_lots(l_lot_counter).spec_match_type := 'A';
1438 p_lots(l_lot_counter).result_type := 'I' ;
1439 ELSE
1440 p_lots(l_lot_counter).sample_id := result_test_list(result_test_list.FIRST).sample_id;
1441 p_lots(l_lot_counter).spec_match_type := 'U';
1442 p_lots(l_lot_counter).result_type := 'I' ;
1443 END IF;
1444 ELSE
1445 p_lots(l_lot_counter).spec_match_type := null;
1446 END IF;
1447 l_lot_counter := p_lots.NEXT(l_lot_counter);
1448 END LOOP;
1449
1450
1451 EXCEPTION
1452 WHEN REQ_FIELDS_MISSING THEN
1453 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_MATCH_GRP.GET_RESULT_MATCH_FOR_SPEC');
1454 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1455 x_return_status := FND_API.G_RET_STS_ERROR ;
1456 WHEN INVALID_LOT THEN
1457 gmd_api_pub.log_message('GMD_INVALID_LOT','LOT',to_char(p_lots(l_lot_counter).lot_id));
1458 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1459 x_return_status := FND_API.G_RET_STS_ERROR ;
1460 WHEN OTHERS THEN
1461 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.GET_RESULT_MATCH_FOR_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1462 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1464 END get_result_match_for_spec ;
1465
1466
1467 END gmd_spec_match_mig_grp;
[Home] [Help]