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