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