1 PACKAGE BODY GMD_SAMPLES_GRP AS
2 --$Header: GMDGSMPB.pls 120.29.12000000.3 2007/02/07 12:01:32 rlnagara ship $
3 -- Global variables
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_SAMPLES_GRP';
5
6 --Bug 3222090, magupta removed call to FND_PROFILE.VALUE('AFLOG_ENABLED')
7 --forward decl.
8 FUNCTION set_debug_flag RETURN VARCHAR2;
9 --l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
10 l_debug VARCHAR2(1) := set_debug_flag;
11
12 FUNCTION set_debug_flag RETURN VARCHAR2 IS
13 l_debug VARCHAR2(1):= 'N';
14 BEGIN
15 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
16 l_debug := 'Y';
17 END IF;
18 RETURN l_debug;
19 END set_debug_flag;
20
21 -- Start of comments
22 --+==========================================================================+
23 --| Copyright (c) 1998 Oracle Corporation |
24 --| Redwood Shores, CA, USA |
25 --| All rights reserved. |
26 --+==========================================================================+
27 --| File Name : GMDGSMPB.pls |
28 --| Package Name : GMD_Samples_GRP |
29 --| Type : Group |
30 --| |
31 --| Notes |
32 --| This package contains group layer APIs for Specification Entity |
33 --| |
34 --| HISTORY |
35 --| Chetan Nagar 26-Jul-2002 Created. |
36 --| Chetan Nagar 05-Nov-2002 Removed logging of error message |
37 --| from sampling_event_exist and sampling_event_exist_wo_spec |
38 --| |
39 --| RLNAGARA 19-Dec-2005 Bug#4868950 |
40 --| -- Modified the procedure sample_source_display |
41 --| RLNAGARA 20-Dec-2005 Bug# 4880152 |
42 --| -- Modified the procedure sample_source_display
43 --| J. DiIorio 25-Jan-2006 Bug# 4695552 |
44 --| -- Changed sample_source_display to handle grade_code,
45 --| -- storage_subinventory, and storage_locator.
46 --| -- Changed stability_study_source to not override locator.
47 --| M. Grosser 28-Feb-2006 Bug 5016617 - Added retrieval of supplier name |
48 --| to procedure sample_source_display.
49 --| Peter Lowe 22-Mar-2006 -Bug 4754855 changed logic for |
50 --| retrieval of Cur_formulaline in api wip_source
51 --| Peter Lowe 13-MAR-2006 FP of Bug # 4359797 4619570 |
52 --| Added code so that samples are created for Closed batches |
53 --| depending upon the profile option but inv is not updated |
54 --| RLNAGARA 04-Apr-2006 B5106199 UOM Conv Changes in the procedure update_remaining_qty |
55 --| Peter Lowe 14-Apr-2006 - Bug 5127352 - reversed logic of Bug 4754855 |
56 --| as QA now states that we do not need formula line no or type on |
57 --| Samples Summary form |
58 --| M. Grosser 03-May-2006 Bug 5115015 - Modified procedure validate_sample|
59 --| to not validate sample number when automatic sample number |
60 --| creation is in effect so that the number can be retrieved |
61 --| AFTER the sample has passed validation. Sample numbers were |
62 --| being lost.
63 --| srakrish bug 5394566: Commenting the cursors as these have |
64 --| hardcoded values and material_detail_id is directly passed |
65 --| in create_wip_txn| |
66 --| RAGSRIVA 01-Nov-2006 Bug 5629709 Modified procedure create_wip_txn to |
67 --| Undo the fix for bug# 5394566 and pass the transaction type |
68 --| id and the lot information in the call to |
69 --| GME_API_PUB.create_material_txn |
70 --| RLNAGARA 28-Nov-2006 B5668965 Modified the proc update_lot_grade_batch |
71 --| RLNAGARA 12-Jan-2007 B5738041 Added Revisions to the cursors in the proc create_wip_txn |
72 --+==========================================================================+
73 -- End of comments
74
75
76
77 --Start of comments
78 --+========================================================================+
79 --| API Name : sampling_event_exist |
80
81 --| event that matches with the sample supplied, otherwise |
82 --| returns FALSE. |
83 --| |
84 --| The function also populate OUT variable - |
85 --| sampling_event_id of the GMD_SAMPLING_EVENT record if |
86 --| it is found. |
87 --| |
88 --| HISTORY |
89 --| Chetan Nagar 26-Jul-2002 Created. |
90 --| Chetan Nagar 03-Dec-2002 Added checks to see if the Spec and|
91 --| Validity Rule associated with the |
92 --| Sampling Event are still active. |
93 --| Chetan Nagar 24-Jan-2003 The previous check for Spec and |
94 --| VR should be for the latest Spec |
95 --| VR from the Event Spec Disp. |
96 --| Susan Feinstein 14-Apr-2003 Bug 2825696 |
97 --| Added orgn_code to sampling_event |
98 --| table. Changed cursors in sampling|
99 --| exist with and without spec. |
100 --| |
101 --| Susan Feinstein 26-Jun-2003 Took out references to validity |
102 --| Bug #2952823 rule tables and spec tables from |
103 --| the sql. Samples will now look |
104 --| first for a spec id and then for |
105 --| a matching sampling event whereas |
106 --| it used to do the search in |
107 --| the opposite order, ie. first |
108 --| sampling event and then spec. |
109 --| Lakshmi Swamy 20-NOV-2003 Bug3264636 |
110 --| Created procedure sampling_event_with_vr_id. If spec_vr_id passed |
111 --| we call this new procedure from sampling_event_exist function |
112 --| Otherwise - its old code where cursors look at specifications table |
113 --+========================================================================+
114 -- End of comments
115
116 FUNCTION sampling_event_with_vr_id
117 (
118 p_sample IN gmd_samples%ROWTYPE
119 , x_sampling_event_id OUT NOCOPY NUMBER
120 , p_spec_vr_id IN NUMBER DEFAULT NULL
121 ) RETURN BOOLEAN IS
122
123 -- Bug 3086932: added source = p_sample.source to where clause because
124 -- otherwise inventory sample could become part of another source
125 -- group using inventory spec vr
126 CURSOR c_inv_sampling_event IS
127 SELECT se.sampling_event_id
128 FROM gmd_sampling_events se,
129 -- gmd_specifications_b s,
130 -- gmd_inventory_spec_vrs ivr,
131 gmd_event_spec_disp esd
132 -- WHERE s.spec_id = ivr.spec_id
133 -- AND ivr.spec_vr_id = esd.spec_vr_id
134 -- AND esd.sampling_event_id = se.sampling_event_id
135 WHERE esd.sampling_event_id = se.sampling_event_id
136 AND ( (esd.spec_vr_id = p_spec_vr_id) OR
137 (esd.spec_vr_id is null and p_spec_vr_id is null ))
138 AND esd.spec_used_for_lot_attrib_ind = 'Y'
139 AND se.source = p_sample.source
140 AND se.organization_id = p_sample.organization_id
141 -- AND s.item_id = p_sample.inventory_item_id
142 AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
143 (se.subinventory = p_sample.subinventory)
144 )
145 AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
146 (se.locator_id = p_sample.locator_id)
147 )
148 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
149 (se.lot_number = p_sample.lot_number)
150 )
151 AND ((se.lot_retest_ind IS NULL AND p_sample.lot_retest_ind IS NULL) OR
152 (se.lot_retest_ind = p_sample.lot_retest_ind)
153 )
154 AND se.disposition IN ('1P', '2I') -- Pending or In Process
155 ORDER BY se.creation_date desc
156 ;
157
158 -- Bug 3086932: added source = p_sample.source to where clause because
159 -- otherwise inventory sample could become part of wip source
160 -- group using inventory spec vr
161 -- Bug 4640143: added material detail id
162 CURSOR c_wip_sampling_event IS
163 SELECT se.sampling_event_id
164 FROM gmd_sampling_events se,
165 gmd_event_spec_disp esd
166 WHERE esd.sampling_event_id = se.sampling_event_id
167 AND ( (esd.spec_vr_id = p_spec_vr_id) OR
168 (esd.spec_vr_id is null and p_spec_vr_id is null ))
169 AND esd.spec_used_for_lot_attrib_ind = 'Y'
170 AND se.source = p_sample.source
171 AND se.organization_id = p_sample.organization_id
172 AND ((se.batch_id is NULL AND p_sample.batch_id is NULL) OR
173 (se.batch_id = p_sample.batch_id)
174 )
175 AND ((se.recipe_id is NULL AND p_sample.recipe_id is NULL) OR
176 (se.recipe_id = p_sample.recipe_id)
177 )
178 AND ((se.formula_id is NULL AND p_sample.formula_id is NULL) OR
179 (se.formula_id = p_sample.formula_id)
180 )
181 AND ((se.formulaline_id is NULL AND p_sample.formulaline_id is NULL) OR
182 (se.formulaline_id = p_sample.formulaline_id
183 AND p_sample.batch_id IS NULL)
184 )
185 AND ((se.material_detail_id is NULL AND p_sample.material_detail_id is NULL) OR
186 (se.material_detail_id = p_sample.material_detail_id)
187 )
188 AND ((se.routing_id is NULL AND p_sample.routing_id is NULL) OR
189 (se.routing_id = p_sample.routing_id)
190 )
191 AND ((se.step_id is NULL AND p_sample.step_id is NULL) OR
192 (se.step_id = p_sample.step_id)
193 )
194 AND ((se.oprn_id is NULL AND p_sample.oprn_id is NULL) OR
195 (se.oprn_id = p_sample.oprn_id)
196 )
197 AND ((se.charge is NULL AND p_sample.charge is NULL) OR
198 (se.charge = p_sample.charge)
199 )
200 AND se.disposition IN ('1P', '2I') -- Pending or In Process
201 -- AND s.delete_mark = 0 -- Spec is still active
202 -- AND ((s.spec_status between 400 and 499) OR
203 -- (s.spec_status between 700 and 799) OR
204 -- (s.spec_status between 900 and 999)
205 -- )
206 -- AND wvr.delete_mark = 0 -- Validity rule is still active
207 -- AND ((wvr.spec_vr_status between 400 and 499) OR
208 -- (wvr.spec_vr_status between 700 and 799) OR
209 -- (wvr.spec_vr_status between 900 and 999)
210 -- )
211 -- AND wvr.start_date <= SYSDATE
212 -- AND (wvr.end_date is NULL OR wvr.end_date >= SYSDATE)
213 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot id
214 (se.lot_number = p_sample.lot_number)
215 )
216 AND ((se.subinventory IS NULL AND p_sample.source_subinventory IS NULL) OR --Bug# 3736716. Added Source warehouse
217 (se.subinventory = p_sample.source_subinventory)
218 )
219 AND ((se.locator_id IS NULL AND p_sample.source_locator_id IS NULL) OR --Bug# 3736716. Added Source Location
220 (se.locator_id = p_sample.source_locator_id)
221 )
222 ORDER BY se.creation_date desc
223 ;
224
225
226 -- Bug 3086932: added source = p_sample.source to where clause because
227 -- otherwise inventory sample could become part of cust source
228 -- group using inventory spec vr
229 CURSOR c_cust_sampling_event IS
230 SELECT se.sampling_event_id
231 FROM gmd_sampling_events se,
232 -- gmd_specifications_b s,
233 -- gmd_customer_spec_vrs cvr,
234 gmd_event_spec_disp esd
235 -- WHERE s.spec_id = cvr.spec_id
236 -- AND cvr.spec_vr_id = esd.spec_vr_id
237 WHERE esd.sampling_event_id = se.sampling_event_id
238 AND ( (esd.spec_vr_id = p_spec_vr_id) OR
239 (esd.spec_vr_id is null and p_spec_vr_id is null ))
240 AND esd.spec_used_for_lot_attrib_ind = 'Y'
241 AND se.source = p_sample.source
242 AND se.organization_id = p_sample.organization_id
243 AND ((se.cust_id is NULL AND p_sample.cust_id is NULL) OR
244 (se.cust_id = p_sample.cust_id)
245 )
246 AND ((se.org_id is NULL AND p_sample.org_id is NULL) OR
247 (se.org_id = p_sample.org_id)
248 )
249 AND ((se.order_id is NULL AND p_sample.order_id is NULL) OR
250 (se.order_id = p_sample.order_id)
251 )
252 AND ((se.order_line_id is NULL AND p_sample.order_line_id is NULL) OR
253 (se.order_line_id = p_sample.order_line_id)
254 )
255 AND ((se.ship_to_site_id is NULL AND p_sample.ship_to_site_id is NULL) OR
256 (se.ship_to_site_id = p_sample.ship_to_site_id)
257 )
258 AND se.disposition IN ('1P', '2I') -- Pending or In Process
259 -- AND s.delete_mark = 0 -- Spec is still active
260 -- AND ((s.spec_status between 400 and 499) OR
261 -- (s.spec_status between 700 and 799) OR
262 -- (s.spec_status between 900 and 999)
263 -- )
264 -- AND cvr.delete_mark = 0 -- Validity rule is still active
265 -- AND ((cvr.spec_vr_status between 400 and 499) OR
266 -- (cvr.spec_vr_status between 700 and 799) OR
267 -- (cvr.spec_vr_status between 900 and 999)
268 -- )
269 -- AND cvr.start_date <= SYSDATE
270 -- AND (cvr.end_date is NULL OR cvr.end_date >= SYSDATE)
271 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot id
272 (se.lot_number = p_sample.lot_number)
273 )
274 ORDER BY se.creation_date desc
275 ;
276
277
278 -- Bug 3086932: added source = p_sample.source to where clause because
279 -- otherwise inventory sample could become part of supplier source
280 -- group using inventory spec vr
281 -- Bug 3143796: added whse, location and lot_number
282 CURSOR c_supp_sampling_event IS
283 SELECT se.sampling_event_id
284 FROM gmd_sampling_events se,
285 -- gmd_specifications_b s,
286 -- gmd_supplier_spec_vrs svr,
287 gmd_event_spec_disp esd
288 -- WHERE s.spec_id = svr.spec_id
289 -- AND svr.spec_vr_id = esd.spec_vr_id
290 WHERE esd.sampling_event_id = se.sampling_event_id
291 AND ( (esd.spec_vr_id = p_spec_vr_id) OR
292 (esd.spec_vr_id is null and p_spec_vr_id is null ))
293 AND esd.spec_used_for_lot_attrib_ind = 'Y'
294 AND se.source = p_sample.source
295 AND se.organization_id = p_sample.organization_id
296 AND ((se.supplier_id is NULL AND p_sample.supplier_id is NULL) OR
297 (se.supplier_id = p_sample.supplier_id)
298 )
299 AND ((se.supplier_site_id is NULL AND p_sample.supplier_site_id is NULL) OR
300 (se.supplier_site_id = p_sample.supplier_site_id)
301 )
302 AND ((se.po_header_id is NULL AND p_sample.po_header_id is NULL) OR
303 (se.po_header_id = p_sample.po_header_id)
304 )
305 AND ((se.po_line_id is NULL AND p_sample.po_line_id is NULL) OR
306 (se.po_line_id = p_sample.po_line_id)
307 )
308 AND ((se. subinventory is NULL AND p_sample.subinventory is NULL) OR
309 (se. subinventory = p_sample. subinventory)
310 )
311 AND ((se. locator_id is NULL AND p_sample.locator_id is NULL) OR
312 (se. locator_id = p_sample. locator_id)
313 )
314 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
315 (se.lot_number = p_sample.lot_number)
316 )
317 AND se.disposition IN ('1P', '2I') -- Pending or In Process
318 /* AND s.delete_mark = 0 -- Spec is still active
319 AND ((s.spec_status between 400 and 499) OR
320 (s.spec_status between 700 and 799) OR
321 (s.spec_status between 900 and 999)
322 )
323 AND svr.delete_mark = 0 -- Validity rule is still active
324 AND ((svr.spec_vr_status between 400 and 499) OR
325 (svr.spec_vr_status between 700 and 799) OR
326 (svr.spec_vr_status between 900 and 999)
327 )
328 AND svr.start_date <= SYSDATE
329 AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
330 */
331 ORDER BY se.creation_date desc
332 ;
333
334 -- Bug 2959466: added source = p_sample.source to where clause because
335 -- if se.resource was null then location sampling events were
336 -- attached to resource samples.
337 CURSOR c_res_sampling_event IS
338 SELECT se.sampling_event_id
339 FROM gmd_sampling_events se,
340 -- gmd_specifications_b s,
341 -- gmd_monitoring_spec_vrs svr,
342 gmd_event_spec_disp esd
343 -- WHERE s.spec_id = svr.spec_id
344 -- AND svr.spec_vr_id = esd.spec_vr_id
345 WHERE esd.sampling_event_id = se.sampling_event_id
346 AND ( (esd.spec_vr_id = p_spec_vr_id) OR
347 (esd.spec_vr_id is null and p_spec_vr_id is null ))
348 AND ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
349 (se.organization_id = p_sample.organization_id)
350 )
351 AND ((se.resources IS NULL and p_sample.resources IS NULL) OR
352 ( (se.resources = p_sample.resources) AND
353 ((se.instance_id IS NULL AND p_sample.instance_id IS NULL) OR
354 (se.instance_id = p_sample.instance_id) ) )
355 )
356 AND se.source = p_sample.source
357 AND se.disposition IN ('1P', '2I') -- Pending or In Process
358 /* AND s.delete_mark = 0 -- Spec is still active
359 AND ((s.spec_status between 400 and 499) OR
360 (s.spec_status between 700 and 799) OR
361 (s.spec_status between 900 and 999)
362 )
363 AND svr.delete_mark = 0 -- Validity rule is still active
364 AND ((svr.spec_vr_status between 400 and 499) OR
365 (svr.spec_vr_status between 700 and 799) OR
366 (svr.spec_vr_status between 900 and 999)
367 )
368 AND svr.start_date <= SYSDATE
369 AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
370 */
371 ORDER BY se.creation_date desc
372 ;
373
374 -- bug# 3467845
375 -- changed location and whse code where clause.
376 -- it was picking sampling event even if sample location was
377 -- different than sampling event's location
378
379 -- bug# 3482454
380 -- sample with location L1 was getting assigned to existing sample group with "NULL" location
381 -- added extra and clause that both locations should be NULL.
382
383 CURSOR c_loc_sampling_event IS
384 SELECT se.sampling_event_id
385 FROM gmd_sampling_events se,
386 -- gmd_specifications_b s,
387 -- gmd_monitoring_spec_vrs svr,
388 gmd_event_spec_disp esd
389 -- WHERE s.spec_id = svr.spec_id
390 -- AND svr.spec_vr_id = esd.spec_vr_id
391 WHERE esd.sampling_event_id = se.sampling_event_id
392 AND se.source = p_sample.source
393 AND ( (esd.spec_vr_id = p_spec_vr_id) OR
394 (esd.spec_vr_id is null and p_spec_vr_id is null ))
395 AND ((se.organization_id is NULL) OR
396 (se.organization_id = p_sample.organization_id )
397 )
398 AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
399 (se.subinventory = p_sample.subinventory)
400 )
401 AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
402 (se.locator_id = p_sample.locator_id)
403 )
404 AND se.disposition IN ('1P', '2I') -- Pending or In Process
405 -- AND s.delete_mark = 0 -- Spec is still active
406 -- AND ((s.spec_status between 400 and 499) OR
407 -- (s.spec_status between 700 and 799) OR
408 -- (s.spec_status between 900 and 999)
409 -- )
410 -- AND svr.delete_mark = 0 -- Validity rule is still active
411 -- AND ((svr.spec_vr_status between 400 and 499) OR
412 -- (svr.spec_vr_status between 700 and 799) OR
413 -- (svr.spec_vr_status between 900 and 999)
414 -- )
415 -- AND svr.start_date <= SYSDATE
416 -- AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
417 ORDER BY se.creation_date desc
418 ;
419
420
421 BEGIN
422 -- Based on the Sample Source, open appropriate cursor and
423 -- try to locate the sampling event record.
424
425 IF p_sample.source = 'I' THEN
426 -- Sample Source is "Inventory"
427 OPEN c_inv_sampling_event;
428 FETCH c_inv_sampling_event INTO x_sampling_event_id;
429 IF c_inv_sampling_event%NOTFOUND THEN
430 CLOSE c_inv_sampling_event;
431 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434 CLOSE c_inv_sampling_event;
435 ELSIF p_sample.source = 'W' THEN
436 -- Sample Source is "WIP"
437 OPEN c_wip_sampling_event;
438 FETCH c_wip_sampling_event INTO x_sampling_event_id;
439 IF c_wip_sampling_event%NOTFOUND THEN
440 CLOSE c_wip_sampling_event;
441 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444 CLOSE c_wip_sampling_event;
445 ELSIF p_sample.source = 'C' THEN
446 -- Sample Source is "Customer"
447 OPEN c_cust_sampling_event;
448 FETCH c_cust_sampling_event INTO x_sampling_event_id;
449 IF c_cust_sampling_event%NOTFOUND THEN
450 CLOSE c_cust_sampling_event;
451 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
452 RAISE FND_API.G_EXC_ERROR;
453 END IF;
454 CLOSE c_cust_sampling_event;
455 ELSIF p_sample.source = 'S' THEN
456 -- Sample Source is "Supplier"
457 OPEN c_supp_sampling_event;
458 FETCH c_supp_sampling_event INTO x_sampling_event_id;
459 IF c_supp_sampling_event%NOTFOUND THEN
460 CLOSE c_supp_sampling_event;
461 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
462 RAISE FND_API.G_EXC_ERROR;
463 END IF;
464 CLOSE c_supp_sampling_event;
465 ELSIF p_sample.source = 'L' THEN
466 -- Sample Source is "Monitor - Location"
467 OPEN c_loc_sampling_event;
468 FETCH c_loc_sampling_event INTO x_sampling_event_id;
469 IF c_loc_sampling_event%NOTFOUND THEN
470 CLOSE c_loc_sampling_event;
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473 CLOSE c_loc_sampling_event;
474 ELSIF p_sample.source = 'R' THEN
475 -- Sample Source is "Monitor - Resource"
476 OPEN c_res_sampling_event;
477 FETCH c_res_sampling_event INTO x_sampling_event_id;
478 IF c_res_sampling_event%NOTFOUND THEN
479 CLOSE c_res_sampling_event;
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482 CLOSE c_res_sampling_event;
483 ELSE
484 --GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
485 RAISE FND_API.G_EXC_ERROR;
486 END IF;
487
488 -- If we reached here then we have found a Sampling event record
489 RETURN TRUE;
490
491 EXCEPTION
492 WHEN FND_API.G_EXC_ERROR THEN
493 RETURN FALSE;
494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495 RETURN FALSE;
496 WHEN OTHERS THEN
497 RETURN FALSE;
498
499 END sampling_event_with_vr_id;
500
501
502 FUNCTION sampling_event_exist
503 (
504 p_sample IN gmd_samples%ROWTYPE
505 , x_sampling_event_id OUT NOCOPY NUMBER
506 , p_spec_vr_id IN NUMBER DEFAULT NULL
507 ) RETURN BOOLEAN IS
508
509 CURSOR c_inv_sampling_event IS
510 SELECT se.sampling_event_id
511 FROM gmd_sampling_events se,
512 gmd_specifications_b s,
513 gmd_inventory_spec_vrs ivr,
514 gmd_event_spec_disp esd
515 WHERE s.spec_id = ivr.spec_id
516 AND ivr.spec_vr_id = esd.spec_vr_id
517 AND esd.sampling_event_id = se.sampling_event_id
518 AND esd.spec_used_for_lot_attrib_ind = 'Y'
519 AND se.organization_id = p_sample.organization_id
520 AND s.inventory_item_id = p_sample.inventory_item_id
521 AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
522 (se.subinventory = p_sample.subinventory)
523 )
524 AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
525 (se.locator_id = p_sample.locator_id)
526 )
527 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
528 (se.lot_number = p_sample.lot_number)
529 )
530 AND ((se.lot_retest_ind IS NULL AND p_sample.lot_retest_ind IS NULL) OR
531 (se.lot_retest_ind = p_sample.lot_retest_ind)
532 )
533 AND se.disposition IN ('1P', '2I') -- Pending or In Process
534 AND s.delete_mark = 0 -- Spec is still active
535 AND ((s.spec_status between 400 and 499) OR
536 (s.spec_status between 700 and 799) OR
537 (s.spec_status between 900 and 999)
538 )
539 AND ivr.delete_mark = 0 -- Validity rule is still active
540 AND ((ivr.spec_vr_status between 400 and 499) OR
541 (ivr.spec_vr_status between 700 and 799) OR
542 (ivr.spec_vr_status between 900 and 999)
543 )
544 AND ivr.start_date <= SYSDATE
545 AND (ivr.end_date is NULL OR ivr.end_date >= SYSDATE)
546 ORDER BY se.creation_date desc
547 ;
548
549 -- Bug 4640143: added material detail id
550 CURSOR c_wip_sampling_event IS
551 SELECT se.sampling_event_id
552 FROM gmd_sampling_events se,
553 gmd_specifications_b s,
554 gmd_wip_spec_vrs wvr,
555 gmd_event_spec_disp esd
556 WHERE s.spec_id = wvr.spec_id
557 AND wvr.spec_vr_id = esd.spec_vr_id
558 AND esd.sampling_event_id = se.sampling_event_id
559 AND esd.spec_used_for_lot_attrib_ind = 'Y'
560 AND se.organization_id = p_sample.organization_id
561 AND s.inventory_item_id = p_sample.inventory_item_id
562 AND ((se.batch_id is NULL AND p_sample.batch_id is NULL) OR
563 (se.batch_id = p_sample.batch_id)
564 )
565 AND ((se.recipe_id is NULL AND p_sample.recipe_id is NULL) OR
566 (se.recipe_id = p_sample.recipe_id)
567 )
568 AND ((se.formula_id is NULL AND p_sample.formula_id is NULL) OR
569 (se.formula_id = p_sample.formula_id)
570 )
571 AND ((se.formulaline_id is NULL AND p_sample.formulaline_id is NULL) OR
572 (se.formulaline_id = p_sample.formulaline_id
573 AND p_sample.batch_id IS NULL)
574 )
575 AND ((se.material_detail_id is NULL AND p_sample.material_detail_id is NULL) OR
576 (se.material_detail_id = p_sample.material_detail_id)
577 )
578 AND ((se.routing_id is NULL AND p_sample.routing_id is NULL) OR
579 (se.routing_id = p_sample.routing_id)
580 )
581 AND ((se.step_id is NULL AND p_sample.step_id is NULL) OR
582 (se.step_id = p_sample.step_id)
583 )
584 AND ((se.oprn_id is NULL AND p_sample.oprn_id is NULL) OR
585 (se.oprn_id = p_sample.oprn_id)
586 )
587 AND ((se.charge is NULL AND p_sample.charge is NULL) OR
588 (se.charge = p_sample.charge)
589 )
590 AND se.disposition IN ('1P', '2I') -- Pending or In Process
591 AND s.delete_mark = 0 -- Spec is still active
592 AND ((s.spec_status between 400 and 499) OR
593 (s.spec_status between 700 and 799) OR
594 (s.spec_status between 900 and 999)
595 )
596 AND wvr.delete_mark = 0 -- Validity rule is still active
597 AND ((wvr.spec_vr_status between 400 and 499) OR
598 (wvr.spec_vr_status between 700 and 799) OR
599 (wvr.spec_vr_status between 900 and 999)
600 )
601 AND wvr.start_date <= SYSDATE
602 AND (wvr.end_date is NULL OR wvr.end_date >= SYSDATE)
603 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot no.
604 (se.lot_number = p_sample.lot_number)
605 )
606 AND ((se.subinventory IS NULL AND p_sample.source_subinventory IS NULL) OR --Bug# 3736716. Added Source warehouse
607 (se.subinventory = p_sample.source_subinventory)
608 )
609 AND ((se.locator_id IS NULL AND p_sample.source_locator_id IS NULL) OR --Bug# 3736716. Added Source Location
610 (se.locator_id = p_sample.source_locator_id)
611 )
612 ORDER BY se.creation_date desc
613 ;
614
615
616 CURSOR c_cust_sampling_event IS
617 SELECT se.sampling_event_id
618 FROM gmd_sampling_events se,
619 gmd_specifications_b s,
620 gmd_customer_spec_vrs cvr,
621 gmd_event_spec_disp esd
622 WHERE s.spec_id = cvr.spec_id
623 AND cvr.spec_vr_id = esd.spec_vr_id
624 AND esd.sampling_event_id = se.sampling_event_id
625 AND esd.spec_used_for_lot_attrib_ind = 'Y'
626 AND se.organization_id = p_sample.organization_id
627 AND s.inventory_item_id = p_sample.inventory_item_id
628 AND ((se.cust_id is NULL AND p_sample.cust_id is NULL) OR
629 (se.cust_id = p_sample.cust_id)
630 )
631 AND ((se.org_id is NULL AND p_sample.org_id is NULL) OR
632 (se.org_id = p_sample.org_id)
633 )
634 AND ((se.order_id is NULL AND p_sample.order_id is NULL) OR
635 (se.order_id = p_sample.order_id)
636 )
637 AND ((se.order_line_id is NULL AND p_sample.order_line_id is NULL) OR
638 (se.order_line_id = p_sample.order_line_id)
639 )
640 AND ((se.ship_to_site_id is NULL AND p_sample.ship_to_site_id is NULL) OR
641 (se.ship_to_site_id = p_sample.ship_to_site_id)
642 )
643 AND se.disposition IN ('1P', '2I') -- Pending or In Process
644 AND s.delete_mark = 0 -- Spec is still active
645 AND ((s.spec_status between 400 and 499) OR
646 (s.spec_status between 700 and 799) OR
647 (s.spec_status between 900 and 999)
648 )
649 AND cvr.delete_mark = 0 -- Validity rule is still active
650 AND ((cvr.spec_vr_status between 400 and 499) OR
651 (cvr.spec_vr_status between 700 and 799) OR
652 (cvr.spec_vr_status between 900 and 999)
653 )
654 AND cvr.start_date <= SYSDATE
655 AND (cvr.end_date is NULL OR cvr.end_date >= SYSDATE)
656 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot no.
657 (se.lot_number = p_sample.lot_number)
658 )
659 ORDER BY se.creation_date desc
660 ;
661
662
663 CURSOR c_supp_sampling_event IS
664 SELECT se.sampling_event_id
665 FROM gmd_sampling_events se,
666 gmd_specifications_b s,
667 gmd_supplier_spec_vrs svr,
668 gmd_event_spec_disp esd
669 WHERE s.spec_id = svr.spec_id
670 AND svr.spec_vr_id = esd.spec_vr_id
671 AND esd.sampling_event_id = se.sampling_event_id
672 AND esd.spec_used_for_lot_attrib_ind = 'Y'
673 AND se.organization_id = p_sample.organization_id
674 AND s.inventory_item_id = p_sample.inventory_item_id
675 AND ((se.supplier_id is NULL AND p_sample.supplier_id is NULL) OR
676 (se.supplier_id = p_sample.supplier_id)
677 )
678 AND ((se.supplier_site_id is NULL AND p_sample.supplier_site_id is NULL) OR
679 (se.supplier_site_id = p_sample.supplier_site_id)
680 )
681 AND ((se.po_header_id is NULL AND p_sample.po_header_id is NULL) OR
682 (se.po_header_id = p_sample.po_header_id)
683 )
684 AND ((se.po_line_id is NULL AND p_sample.po_line_id is NULL) OR
685 (se.po_line_id = p_sample.po_line_id)
686 )
687 AND se.disposition IN ('1P', '2I') -- Pending or In Process
688 AND s.delete_mark = 0 -- Spec is still active
689 AND ((s.spec_status between 400 and 499) OR
690 (s.spec_status between 700 and 799) OR
691 (s.spec_status between 900 and 999)
692 )
693 AND svr.delete_mark = 0 -- Validity rule is still active
694 AND ((svr.spec_vr_status between 400 and 499) OR
695 (svr.spec_vr_status between 700 and 799) OR
696 (svr.spec_vr_status between 900 and 999)
697 )
698 AND svr.start_date <= SYSDATE
699 AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
700 ORDER BY se.creation_date desc
701 ;
702
703 -- Bug 2959466: added source = p_sample.source to where clause because
704 -- if se.resource was null then location sampling events were
705 -- attached to resource samples.
706 CURSOR c_res_sampling_event IS
707 SELECT se.sampling_event_id
708 FROM gmd_sampling_events se,
709 gmd_specifications_b s,
710 gmd_monitoring_spec_vrs svr,
711 gmd_event_spec_disp esd
712 WHERE s.spec_id = svr.spec_id
713 AND svr.spec_vr_id = esd.spec_vr_id
714 AND esd.sampling_event_id = se.sampling_event_id
715 AND ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
716 (se.organization_id = p_sample.organization_id)
717 )
718 AND ((se.resources IS NULL and p_sample.resources IS NULL) OR
719 ( (se.resources = p_sample.resources) AND
720 ((se.instance_id IS NULL AND p_sample.instance_id IS NULL) OR
721 (se.instance_id = p_sample.instance_id) ) )
722 )
723 AND se.source = p_sample.source
724 AND se.disposition IN ('1P', '2I') -- Pending or In Process
725 AND s.delete_mark = 0 -- Spec is still active
726 AND ((s.spec_status between 400 and 499) OR
727 (s.spec_status between 700 and 799) OR
728 (s.spec_status between 900 and 999)
729 )
730 AND svr.delete_mark = 0 -- Validity rule is still active
731 AND ((svr.spec_vr_status between 400 and 499) OR
732 (svr.spec_vr_status between 700 and 799) OR
733 (svr.spec_vr_status between 900 and 999)
734 )
735 AND svr.start_date <= SYSDATE
736 AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
737 ORDER BY se.creation_date desc
738 ;
739
740 -- bug# 3467845
741 -- changed location and whse code where clause.
742 -- also added one more condition se.source = p_sample.source
743 -- which was missed out as part of bug fix 2959466.
744
745 -- bug# 3482454
746 -- sample with location L1 was getting assigned to existing sample group with "NULL" location
747 -- added extra and clause that both locations should be NULL.
748
749 CURSOR c_loc_sampling_event IS
750 SELECT se.sampling_event_id
751 FROM gmd_sampling_events se,
752 gmd_specifications_b s,
753 gmd_monitoring_spec_vrs svr,
754 gmd_event_spec_disp esd
755 WHERE s.spec_id = svr.spec_id
756 AND svr.spec_vr_id = esd.spec_vr_id
757 AND esd.sampling_event_id = se.sampling_event_id
758 AND se.source = p_sample.source
759 AND ((se.organization_id is NULL) OR
760 (se.organization_id = p_sample.organization_id )
761 )
762 AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
763 (se.subinventory = p_sample.subinventory)
764 )
765 AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
766 (se.locator_id = p_sample.locator_id)
767 )
768 AND se.disposition IN ('1P', '2I') -- Pending or In Process
769 AND s.delete_mark = 0 -- Spec is still active
770 AND ((s.spec_status between 400 and 499) OR
771 (s.spec_status between 700 and 799) OR
772 (s.spec_status between 900 and 999)
773 )
774 AND svr.delete_mark = 0 -- Validity rule is still active
775 AND ((svr.spec_vr_status between 400 and 499) OR
776 (svr.spec_vr_status between 700 and 799) OR
777 (svr.spec_vr_status between 900 and 999)
778 )
779 AND svr.start_date <= SYSDATE
780 AND (svr.end_date is NULL OR svr.end_date >= SYSDATE)
781 ORDER BY se.creation_date desc
782 ;
783
784
785 BEGIN
786 -- Based on the Sample Source, open appropriate cursor and
787 -- try to locate the sampling event record.
788 IF (p_spec_vr_id is NOT NULL) THEN
789 IF (sampling_event_with_vr_id ( p_sample => p_sample
790 , x_sampling_event_id => x_sampling_event_id
791 , p_spec_vr_id => p_spec_vr_id)) THEN
792 RETURN TRUE;
793 ELSE
794 RETURN FALSE;
795 END IF;
796 ELSE
797 IF p_sample.source = 'I' THEN
798 -- Sample Source is "Inventory"
799 OPEN c_inv_sampling_event;
800 FETCH c_inv_sampling_event INTO x_sampling_event_id;
801 IF c_inv_sampling_event%NOTFOUND THEN
802 CLOSE c_inv_sampling_event;
803 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 CLOSE c_inv_sampling_event;
807 ELSIF p_sample.source = 'W' THEN
808 -- Sample Source is "WIP"
809 OPEN c_wip_sampling_event;
810 FETCH c_wip_sampling_event INTO x_sampling_event_id;
811 IF c_wip_sampling_event%NOTFOUND THEN
812 CLOSE c_wip_sampling_event;
813 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
814 RAISE FND_API.G_EXC_ERROR;
815 END IF;
816 CLOSE c_wip_sampling_event;
817 ELSIF p_sample.source = 'C' THEN
818 -- Sample Source is "Customer"
819 OPEN c_cust_sampling_event;
820 FETCH c_cust_sampling_event INTO x_sampling_event_id;
821 IF c_cust_sampling_event%NOTFOUND THEN
822 CLOSE c_cust_sampling_event;
823 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
824 RAISE FND_API.G_EXC_ERROR;
825 END IF;
826 CLOSE c_cust_sampling_event;
827 ELSIF p_sample.source = 'S' THEN
828 -- Sample Source is "Supplier"
829 OPEN c_supp_sampling_event;
830 FETCH c_supp_sampling_event INTO x_sampling_event_id;
831 IF c_supp_sampling_event%NOTFOUND THEN
832 CLOSE c_supp_sampling_event;
833 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
834 RAISE FND_API.G_EXC_ERROR;
835 END IF;
836 CLOSE c_supp_sampling_event;
837 ELSIF p_sample.source = 'L' THEN
838 -- Sample Source is "Monitor - Location"
839 OPEN c_loc_sampling_event;
840 FETCH c_loc_sampling_event INTO x_sampling_event_id;
841 IF c_loc_sampling_event%NOTFOUND THEN
842 CLOSE c_loc_sampling_event;
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845 CLOSE c_loc_sampling_event;
846 ELSIF p_sample.source = 'R' THEN
847 -- Sample Source is "Monitor - Resource"
848 OPEN c_res_sampling_event;
849 FETCH c_res_sampling_event INTO x_sampling_event_id;
850 IF c_res_sampling_event%NOTFOUND THEN
851 CLOSE c_res_sampling_event;
852 RAISE FND_API.G_EXC_ERROR;
853 END IF;
854 CLOSE c_res_sampling_event;
855 ELSE
856 --GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
857 RAISE FND_API.G_EXC_ERROR;
858 END IF;
859
860 -- If we reached here then we have found a Sampling event record
861 RETURN TRUE;
862
863 END IF;
864 EXCEPTION
865 WHEN FND_API.G_EXC_ERROR THEN
866 RETURN FALSE;
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868 RETURN FALSE;
869 WHEN OTHERS THEN
870 RETURN FALSE;
871
872 END sampling_event_exist;
873
874
875 --Start of comments
876 --+========================================================================+
877 --| API Name : sampling_event_exist_wo_spec |
878 --| TYPE : Group |
879 --| Notes : This function return TRUE if there exist a Sampling |
880 --| event without the Spec that matches with the sample |
881 --| supplied, otherwise returns FALSE. |
882 --| |
883 --| The function also populate OUT variable - |
884 --| sampling_event_id of the GMD_SAMPLING_EVENT record if |
885 --| it is found. |
886 --| |
887 --| HISTORY |
888 --| Chetan Nagar 30-Aug-2002 Created. |
889 --| |
890 --+========================================================================+
891 -- End of comments
892
893 FUNCTION sampling_event_exist_wo_spec
894 (
895 p_sample IN gmd_samples%ROWTYPE
896 , x_sampling_event_id OUT NOCOPY NUMBER
897 ) RETURN BOOLEAN IS
898
899 CURSOR c_inv_sampling_event IS
900 SELECT se.sampling_event_id
901 FROM gmd_sampling_events se
902 WHERE se.inventory_item_id = p_sample.inventory_item_id
903 AND se.organization_id = p_sample.organization_id
904 AND se.original_spec_vr_id IS NULL
905 AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
906 (se.subinventory = p_sample.subinventory)
907 )
908 AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
909 (se.locator_id = p_sample.locator_id)
910 )
911 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
912 (se.lot_number = p_sample.lot_number)
913 )
914 AND ((se.lot_retest_ind IS NULL AND p_sample.lot_retest_ind IS NULL) OR
915 (se.lot_retest_ind = p_sample.lot_retest_ind)
916 )
917 AND se.disposition IN ('1P', '2I') -- Pending or In Process
918 ORDER BY se.creation_date desc
919 ;
920
921 -- Bug 4640143: added material detail id
922 CURSOR c_wip_sampling_event IS
923 SELECT se.sampling_event_id
924 FROM gmd_sampling_events se
925 WHERE se.inventory_item_id = p_sample.inventory_item_id
926 AND se.organization_id = p_sample.organization_id
927 AND se.original_spec_vr_id IS NULL
928 AND ((se.batch_id is NULL AND p_sample.batch_id is NULL) OR
929 (se.batch_id = p_sample.batch_id)
930 )
931 AND ((se.recipe_id is NULL AND p_sample.recipe_id is NULL) OR
932 (se.recipe_id = p_sample.recipe_id)
933 )
934 AND ((se.formula_id is NULL AND p_sample.formula_id is NULL) OR
935 (se.formula_id = p_sample.formula_id)
936 )
937 AND ((se.formulaline_id is NULL AND p_sample.formulaline_id is NULL) OR
938 (se.formulaline_id = p_sample.formulaline_id
939 AND p_sample.batch_id IS NULL)
940 )
941 AND ((se.material_detail_id is NULL AND p_sample.material_detail_id is NULL) OR
942 (se.material_detail_id = p_sample.material_detail_id)
943 )
944 AND ((se.routing_id is NULL AND p_sample.routing_id is NULL) OR
945 (se.routing_id = p_sample.routing_id)
946 )
947 AND ((se.step_id is NULL AND p_sample.step_id is NULL) OR
948 (se.step_id = p_sample.step_id)
949 )
950 AND ((se.oprn_id is NULL AND p_sample.oprn_id is NULL) OR
951 (se.oprn_id = p_sample.oprn_id)
952 )
953 AND ((se.charge is NULL AND p_sample.charge is NULL) OR
954 (se.charge = p_sample.charge)
955 )
956 AND se.disposition IN ('1P', '2I') -- Pending or In Process
957 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added lot_number id
958 (se.lot_number = p_sample.lot_number)
959 )
960 AND ((se.subinventory IS NULL AND p_sample.source_subinventory IS NULL) OR --Bug# 3736716. Added Source warehouse
961 (se.subinventory = p_sample.source_subinventory)
962 )
963 AND ((se.locator_id IS NULL AND p_sample.source_locator_id IS NULL) OR --Bug# 3736716. Added Source Location
964 (se.locator_id = p_sample.source_locator_id)
965 )
966 ORDER BY se.creation_date desc
967 ;
968
969
970 CURSOR c_cust_sampling_event IS
971 SELECT se.sampling_event_id
972 FROM gmd_sampling_events se
973 WHERE se.inventory_item_id = p_sample.inventory_item_id
974 AND se.organization_id = p_sample.organization_id
975 AND se.original_spec_vr_id IS NULL
976 AND ((se.cust_id is NULL AND p_sample.cust_id is NULL) OR
977 (se.cust_id = p_sample.cust_id)
978 )
979 AND ((se.org_id is NULL AND p_sample.org_id is NULL) OR
980 (se.org_id = p_sample.org_id)
981 )
982 AND ((se.order_id is NULL AND p_sample.order_id is NULL) OR
983 (se.order_id = p_sample.order_id)
984 )
985 AND ((se.order_line_id is NULL AND p_sample.order_line_id is NULL) OR
986 (se.order_line_id = p_sample.order_line_id)
987 )
988 AND ((se.ship_to_site_id is NULL AND p_sample.ship_to_site_id is NULL) OR
989 (se.ship_to_site_id = p_sample.ship_to_site_id)
990 )
991 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR --Bug# 3736716. Added Lot no.
992 (se.lot_number = p_sample.lot_number)
993 )
994 AND se.disposition IN ('1P', '2I') -- Pending or In Process
995 ORDER BY se.creation_date desc
996 ;
997
998 -- Bug 3143796: added whse, location and lot_id
999 CURSOR c_supp_sampling_event IS
1000 SELECT se.sampling_event_id
1001 FROM gmd_sampling_events se
1002 WHERE se.inventory_item_id = p_sample.inventory_item_id
1003 AND se.organization_id = p_sample.organization_id
1004 AND se.original_spec_vr_id IS NULL
1005 AND ((se.supplier_id is NULL AND p_sample.supplier_id is NULL) OR
1006 (se.supplier_id = p_sample.supplier_id)
1007 )
1008 AND ((se.supplier_site_id is NULL AND p_sample.supplier_site_id is NULL) OR
1009 (se.supplier_site_id = p_sample.supplier_site_id)
1010 )
1011 AND ((se.po_header_id is NULL AND p_sample.po_header_id is NULL) OR
1012 (se.po_header_id = p_sample.po_header_id)
1013 )
1014 AND ((se.po_line_id is NULL AND p_sample.po_line_id is NULL) OR
1015 (se.po_line_id = p_sample.po_line_id)
1016 )
1017 AND ((se.subinventory is NULL AND p_sample.subinventory is NULL) OR
1018 (se.subinventory = p_sample.subinventory)
1019 )
1020 AND ((se.locator_id is NULL AND p_sample.locator_id is NULL) OR
1021 (se.locator_id = p_sample.locator_id)
1022 )
1023 AND ((se.lot_number IS NULL AND p_sample.lot_number IS NULL) OR
1024 (se.lot_number = p_sample.lot_number)
1025 )
1026 AND se.disposition IN ('1P', '2I') -- Pending or In Process
1027 ORDER BY se.creation_date desc
1028 ;
1029
1030 -- Bug 2959466: added source = p_sample.source to where clause because
1031 -- if se.resource was null then location sampling events were
1032 -- attached to resource samples.
1033
1034
1035 CURSOR c_res_sampling_event IS
1036 SELECT se.sampling_event_id
1037 FROM gmd_sampling_events se
1038 WHERE ((se.organization_id is NULL AND p_sample.organization_id IS NULL) OR
1039 (se.organization_id = p_sample.organization_id)
1040 )
1041 AND se.source = p_sample.source
1042 AND se.original_spec_vr_id IS NULL
1043 AND ((se.resources IS NULL AND p_sample.resources IS NULL) OR
1044 ( (se.resources = p_sample.resources) AND
1045 ((se.instance_id is NULL AND p_sample.instance_id is NULL) OR
1046 (se.instance_id = p_sample.instance_id) ) )
1047 )
1048 AND se.disposition IN ('1P', '2I') -- Pending or In Process
1049 ORDER BY se.creation_date desc
1050 ;
1051
1052 -- bug 3467845
1053 -- changed whse and location code where clause.
1054
1055 -- Bug 3401377: added source = p_sample.source to where clause because
1056 -- if se.location was null then resource sampling events were
1057 -- attached to location samples.
1058
1059 -- bug# 3482454
1060 -- sample with location L1 was getting assigned to existing sample group with "NULL" location
1061 -- added extra and clause that both locations should be NULL.
1062 -- also added one more condition se.original_spec_vr_id IS NULL which was missing here and in
1063 -- resource sampling event cursor.
1064
1065 CURSOR c_loc_sampling_event IS
1066 SELECT se.sampling_event_id
1067 FROM gmd_sampling_events se
1068 WHERE ((se.organization_id is NULL) OR
1069 (se.organization_id = p_sample.organization_id )
1070 )
1071 AND se.source = p_sample.source
1072 AND se.original_spec_vr_id IS NULL
1073 AND ((se.subinventory IS NULL AND p_sample.subinventory IS NULL) OR
1074 (se.subinventory = p_sample.subinventory)
1075 )
1076 AND ((se.locator_id IS NULL AND p_sample.locator_id IS NULL) OR
1077 (se.locator_id = p_sample.locator_id)
1078 )
1079 AND se.disposition IN ('1P', '2I') -- Pending or In Process
1080 ORDER BY se.creation_date desc
1081 ;
1082
1083
1084
1085 BEGIN
1086 -- Based on the Sample Source, open appropriate cursor and
1087 -- try to locate the sampling event record without the Spec.
1088
1089 IF p_sample.source = 'I' THEN
1090 -- Sample Source is "Inventory"
1091 OPEN c_inv_sampling_event;
1092 FETCH c_inv_sampling_event INTO x_sampling_event_id;
1093 IF c_inv_sampling_event%NOTFOUND THEN
1094 CLOSE c_inv_sampling_event;
1095 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098 CLOSE c_inv_sampling_event;
1099 ELSIF p_sample.source = 'W' THEN
1100 -- Sample Source is "WIP"
1101 OPEN c_wip_sampling_event;
1102 FETCH c_wip_sampling_event INTO x_sampling_event_id;
1103 IF c_wip_sampling_event%NOTFOUND THEN
1104 CLOSE c_wip_sampling_event;
1105 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
1106 RAISE FND_API.G_EXC_ERROR;
1107 END IF;
1108 CLOSE c_wip_sampling_event;
1109 ELSIF p_sample.source = 'C' THEN
1110 -- Sample Source is "Customer"
1111 OPEN c_cust_sampling_event;
1112 FETCH c_cust_sampling_event INTO x_sampling_event_id;
1113 IF c_cust_sampling_event%NOTFOUND THEN
1114 CLOSE c_cust_sampling_event;
1115 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
1116 RAISE FND_API.G_EXC_ERROR;
1117 END IF;
1118 CLOSE c_cust_sampling_event;
1119 ELSIF p_sample.source = 'S' THEN
1120 -- Sample Source is "Supplier"
1121 OPEN c_supp_sampling_event;
1122 FETCH c_supp_sampling_event INTO x_sampling_event_id;
1123 IF c_supp_sampling_event%NOTFOUND THEN
1124 CLOSE c_supp_sampling_event;
1125 --GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
1126 RAISE FND_API.G_EXC_ERROR;
1127 END IF;
1128 CLOSE c_supp_sampling_event;
1129
1130 ELSIF p_sample.source = 'L' THEN
1131 -- Sample Source is "Monitor - Location"
1132 OPEN c_loc_sampling_event;
1133 FETCH c_loc_sampling_event INTO x_sampling_event_id;
1134 IF c_loc_sampling_event%NOTFOUND THEN
1135 CLOSE c_loc_sampling_event;
1136 RAISE FND_API.G_EXC_ERROR;
1137 END IF;
1138 CLOSE c_loc_sampling_event;
1139 ELSIF p_sample.source = 'R' THEN
1140 -- Sample Source is "Monitor - Resource"
1141 OPEN c_res_sampling_event;
1142 FETCH c_res_sampling_event INTO x_sampling_event_id;
1143 IF c_res_sampling_event%NOTFOUND THEN
1144 CLOSE c_res_sampling_event;
1145 RAISE FND_API.G_EXC_ERROR;
1146 END IF;
1147 CLOSE c_res_sampling_event;
1148
1149 ELSE
1150 --GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
1151 RAISE FND_API.G_EXC_ERROR;
1152 END IF;
1153
1154 -- If we reached here then we have found a Sampling event record
1155 RETURN TRUE;
1156
1157 EXCEPTION
1158 WHEN FND_API.G_EXC_ERROR THEN
1159 RETURN FALSE;
1160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1161 RETURN FALSE;
1162 WHEN OTHERS THEN
1163 RETURN FALSE;
1164
1165 END sampling_event_exist_wo_spec;
1166
1167
1168 --Start of comments
1169 --+========================================================================+
1170 --| API Name : sample_exist |
1171 --| TYPE : Group |
1172 --| Notes : This function returns TRUE if the Sample with given |
1173 --| Sample No. already exist in the database, FALSE |
1174 --| otherwise. |
1175 --| |
1176 --| HISTORY |
1177 --| Chetan Nagar 26-Jul-2002 Created. |
1178 --| |
1179 --+========================================================================+
1180 -- End of comments
1181
1182 FUNCTION sample_exist(p_organization_id NUMBER, p_sample_no VARCHAR2)
1183 RETURN BOOLEAN IS
1184
1185 CURSOR c_sample_no (p_organization_id VARCHAR2, p_sample_no VARCHAR2) IS
1186 SELECT 1
1187 FROM gmd_samples
1188 WHERE organization_id = p_organization_id
1189 AND sample_no = p_sample_no
1190 ;
1191
1192 dummy PLS_INTEGER;
1193
1194 BEGIN
1195
1196 OPEN c_sample_no(p_organization_id, p_sample_no);
1197 FETCH c_sample_no INTO dummy;
1198 IF c_sample_no%FOUND THEN
1199 CLOSE c_sample_no;
1200 RETURN TRUE;
1201 ELSE
1202 CLOSE c_sample_no;
1203 RETURN FALSE;
1204 END IF;
1205
1206 EXCEPTION
1207 -- Though there is no reason the program can reach
1208 -- here, this is coded just for the reasons we can
1209 -- not think of!
1210 WHEN OTHERS THEN
1211 RETURN TRUE;
1212
1213 END sample_exist;
1214
1215
1216
1217
1218
1219 --Start of comments
1220 --+========================================================================+
1221 --| API Name : validate_sample |
1222 --| TYPE : Group |
1223 --| Notes : This procedure validates all the fields of a sample. |
1224 --| This procedure can be |
1225 --| called from FORM or API and the caller need |
1226 --| to specify this in p_called_from parameter |
1227 --| while calling this procedure. Based on where |
1228 --| it is called from certain validations will |
1229 --| either be performed or skipped. |
1230 --| |
1231 --| If everything is fine then OUT parameter |
1232 --| x_return_status is set to 'S' else appropriate |
1233 --| error message is put on the stack and error |
1234 --| is returned. |
1235 --| |
1236 --| HISTORY |
1237 --| Chetan Nagar 26-Jul-2002 Created. |
1238 --| Susan Feinstein Bug 4165704: updated for inventory convergence |
1239 --| M. Grosser 03-May-2006 Bug 5115015 - Modified procedure validate_sample|
1240 --| to not validate sample number when automatic sample number |
1241 --| creation is in effect so that the number can be retrieved |
1242 --| AFTER the sample has passed validation. Sample numbers were |
1243 --| being lost. |
1244 --+========================================================================+
1245 -- End of comments
1246
1247 PROCEDURE validate_sample
1248 (
1249 p_sample IN gmd_samples%ROWTYPE
1250 , p_called_from IN VARCHAR2
1251 , p_operation IN VARCHAR2
1252 , x_return_status OUT NOCOPY VARCHAR2
1253 ) IS
1254
1255 Cursor fetch_mtl_system_items IS
1256 SELECT primary_uom_code
1257 FROM mtl_system_items_b
1258 WHERE inventory_item_id = p_sample.inventory_item_id
1259 AND organization_id = p_sample.organization_id;
1260
1261 -- Local Variables
1262 l_return_status VARCHAR2(1);
1263 dummy NUMBER;
1264
1265 --l_item_mst MTL_SYSTEM_ITEMS_B_KFV%ROWTYPE;
1266 --l_in_item_mst MTL_SYSTEM_ITEMS_B_KFV%ROWTYPE;
1267 l_sampling_plan GMD_SAMPLING_PLANS%ROWTYPE;
1268 l_primary_uom_code VARCHAR2(3);
1269 from_name VARCHAR2(50);
1270 to_name VARCHAR2(50);
1271 l_trans_qty2 NUMBER;
1272
1273 -- M. Grosser 03-May-2006 Bug 5115015 - Modified procedure validate_sample
1274 -- to not validate sample number when automatic sample number
1275 -- creation is in effect so that the number can be retrieved
1276 -- AFTER the sample has passed validation. Sample numbers were
1277 -- being lost.
1278 --
1279 quality_config GMD_QUALITY_CONFIG%ROWTYPE;
1280 found BOOLEAN;
1281
1282 -- Exceptions
1283 e_smpl_plan_fetch_error EXCEPTION;
1284 e_error_fetch_item EXCEPTION;
1285
1286 BEGIN
1287 IF (l_debug = 'Y') THEN
1288 gmd_debug.put_line('Entered Procedure VALIDATE SAMPLES');
1289 END IF;
1290
1291 -- Initialize API return status to success
1292 x_return_status := FND_API.G_RET_STS_SUCCESS;
1293
1294 IF (p_called_from = 'API') THEN
1295 -- Check for NULLs and Valid Foreign Keys in the input parameter
1296 check_for_null_and_fks_in_smpl
1297 (
1298 p_sample => p_sample
1299 , x_return_status => l_return_status
1300 );
1301 -- No need if called from FORM since it is already
1302 -- done in the form
1303
1304 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1305 -- Message is alrady logged by check_for_null procedure
1306 RAISE FND_API.G_EXC_ERROR;
1307 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1308 -- Message is alrady logged by check_for_null procedure
1309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1310 END IF;
1311 END IF;
1312
1313 -- Perform all other business validations.
1314
1315 IF (l_debug = 'Y') THEN
1316 gmd_debug.put_line('Starting check for duplicate sample number:');
1317 END IF;
1318
1319 -- M. Grosser 03-May-2006 Bug 5115015 - Modified procedure validate_sample
1320 -- to not validate sample number when automatic sample number
1321 -- creation is in effect so that the number can be retrieved
1322 -- AFTER the sample has passed validation. Sample numbers were
1323 -- being lost.
1324 --
1325 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
1326 p_organization_id => p_sample.organization_id
1327 , x_quality_parameters => quality_config
1328 , x_return_status => x_return_status
1329 , x_orgn_found => found );
1330
1331 IF NOT(found) OR (x_return_status <> 'S') THEN
1332 GMD_API_PUB.Log_Message('GMD_QM_ORG_PARAMETER');
1333 RAISE FND_API.G_EXC_ERROR;
1334 END IF;
1335
1336 -- Skip if automatic assignmnet type, so sample number can be retrived after
1337 -- validation
1338 IF quality_config.sample_assignment_type <> 2 THEN
1339
1340 -- If inserting a Sample, Sample_No must be unique
1341 IF sample_exist(p_sample.organization_id, p_sample.sample_no) THEN
1342 -- Huston, we have a problem...
1343 GMD_API_PUB.Log_Message('GMD_SAMPLE_EXIST',
1344 'ORGN_CODE', p_sample.organization_id,
1345 'SAMPLE_NO', p_sample.sample_no);
1346 RAISE FND_API.G_EXC_ERROR;
1347 END IF;
1348
1349 END IF; -- Not automatic sample no assignment
1350 -- M. Grosser 03-May-2006 Bug 5115015 - End of changes
1351
1352 IF (l_debug = 'Y') THEN
1353 gmd_debug.put_line('Starting uom conversion:');
1354 END IF;
1355
1356 -- Sample Quantity UOM must be convertible to Item's UOM
1357 IF p_sample.sample_type = 'I' THEN
1358 -- Bug 4165704: got primary uom from mtl_system_items instead of ic_item_mst
1359 --l_in_item_mst.inventory_item_id := p_sample.inventory_item_id;
1360 --IF NOT gmivdbl.ic_item_mst_select (l_in_item_mst, l_item_mst) THEN
1361 -- RAISE e_error_fetch_item;
1362 --END IF;
1363
1364 OPEN fetch_mtl_system_items;
1365 FETCH fetch_mtl_system_items into l_primary_uom_code;
1366 CLOSE fetch_mtl_system_items;
1367 END IF;
1368
1369 BEGIN
1370 -- UOM conversion is only needed for Material samples
1371 -- Bug 4165704: Changed UOM conversion for Inventory Convergence
1372 IF p_sample.sample_type = 'I'
1373 AND l_primary_uom_code IS NOT NULL THEN
1374
1375 l_trans_qty2 := INV_CONVERT. inv_um_convert (
1376 item_id => p_sample.inventory_item_id,
1377 lot_number => NULL,
1378 organization_id => p_sample.organization_id,
1379 precision => 5, -- decimal point precision
1380 from_quantity => p_sample.sample_qty,
1381 from_unit => p_sample.sample_qty_uom,
1382 to_unit => l_primary_uom_code,
1383 from_name => NULL ,
1384 to_name => NULL) ;
1385 IF (l_debug = 'Y') THEN
1386 gmd_debug.put_line('After uom conversion qty2 ='||l_trans_qty2);
1387 END IF;
1388
1389 --GMICUOM.icuomcv(pitem_id => l_item_mst.item_id,
1390 -- plot_id => 0,
1391 -- pcur_qty => p_sample.sample_qty,
1392 -- pcur_uom => p_sample.sample_qty_uom,
1393 -- pnew_uom => l_item_mst.item_um,
1394 -- onew_qty => dummy);
1395 END IF;
1396
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 -- The message is already set, just put it on the stack.
1400 FND_MSG_PUB.ADD;
1401 RAISE FND_API.G_EXC_ERROR;
1402 END;
1403
1404 IF (l_debug = 'Y') THEN
1405 gmd_debug.put_line('end validate sample');
1406 END IF;
1407 -- All systems GO...
1408
1409 EXCEPTION
1410 WHEN FND_API.G_EXC_ERROR THEN
1411 x_return_status := FND_API.G_RET_STS_ERROR ;
1412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1414 WHEN OTHERS THEN
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1416
1417
1418 END validate_sample;
1419
1420
1421 --Start of comments
1422 --+========================================================================+
1423 --| API Name : update_sample_comp_disp |
1424 --| TYPE : Group |
1425 --| Notes : This procedure updates sample or composite disposition |
1426 --| depending upon whether sample_id or composite_spec_disp_id |
1427 --| is passed. |
1428 --| If everything is fine then OUT parameter |
1429 --| x_return_status is set to 'S' else appropriate |
1430 --| error message is returned. |
1431 --| Called_from_results set to Y in Results form. Update |
1432 --| event_spec_disp and sampling_events regardless of |
1433 --| number of samples when Results form is changing the |
1434 --| sample disposition to In Progress. |
1435 --| |
1436 --| HISTORY |
1437 --| Mahesh Chandak 18-Sep-2002 Created. |
1438 --| Saikiran 19-Jan-2005 Fixed bug# 4951244
1439 --| |
1440 --+========================================================================+
1441 -- End of comments
1442
1443 PROCEDURE update_sample_comp_disp
1444 (
1445 p_update_disp_rec IN UPDATE_DISP_REC
1446 , p_to_disposition IN VARCHAR2
1447 , x_return_status OUT NOCOPY VARCHAR2
1448 , x_message_data OUT NOCOPY VARCHAR2
1449 ) IS
1450
1451 l_event_spec_disp_id NUMBER(15);
1452 l_sampling_event_id NUMBER(15);
1453 l_last_updated_by NUMBER;
1454 l_last_update_login NUMBER;
1455 l_last_update_date DATE ;
1456 l_position VARCHAR2(3);
1457 l_compare_sample_disp VARCHAR2(4);
1458 l_sample_curr_disp VARCHAR2(4);
1459 req_fields_missing EXCEPTION;
1460 invalid_parameter EXCEPTION;
1461 sample_spec_changed EXCEPTION;
1462 sample_disp_changed EXCEPTION;
1463 l_active_cnt NUMBER(5);
1464 l_req_cnt NUMBER(5);
1465 l_curr_event_disp VARCHAR2(4);
1466 l_max_disposition VARCHAR2(4);
1467 l_min_disposition VARCHAR2(4);
1468 l_final_event_disp VARCHAR2(4);
1469 l_sample_disp_curr_flag VARCHAR2(1);
1470 l_temp_numb NUMBER;
1471 -- Begin bug 4951244
1472 l_step_id GMD_SAMPLES.step_id%TYPE;
1473 l_sample_type GMD_SAMPLES.sample_type%TYPE;
1474 l_source GMD_SAMPLES.source%TYPE;
1475 return_status VARCHAR2(20);
1476 l_dummy_cnt NUMBER :=0;
1477 l_data VARCHAR2(2000);
1478 l_batch_organization_id NUMBER;
1479
1480 Cursor cur_sample_details IS
1481 SELECT step_id,organization_id,sample_type,source
1482 FROM gmd_samples
1483 WHERE sample_id = p_update_disp_rec.sample_id;
1484 -- End bug 4951244
1485
1486 --Bug# 5440347 start
1487 --this cursor is used for single samples
1488 CURSOR cur_auto_complete_bstep IS
1489 SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
1490 FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,GMD_SAMPLES gs
1491 WHERE gs.SAMPLE_ID = p_update_disp_rec.sample_id
1492 AND gse.SAMPLING_EVENT_ID = gs.SAMPLING_EVENT_ID
1493 AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
1494
1495 --the below two cursors are used for sample groups
1496 CURSOR cur_sampling_event_details(p_sampling_event_id NUMBER) IS
1497 SELECT step_id,organization_id,sample_type,source
1498 FROM gmd_sampling_events
1499 WHERE sampling_event_id = p_sampling_event_id;
1500
1501 CURSOR cur_comp_auto_complete_bstep(p_sampling_event_id NUMBER) IS
1502 SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
1503 FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse
1504 WHERE gse.SAMPLING_EVENT_ID = p_sampling_event_id
1505 AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
1506
1507 l_auto_complete_bstep VARCHAR2(1) := NULL;
1508 x_message_count NUMBER;
1509 x_message_list VARCHAR2(2000);
1510 xx_return_status VARCHAR2(1);
1511 l_exception_material_tbl GME_COMMON_PVT.exceptions_tab;
1512 p_batch_step_rec GME_BATCH_STEPS%ROWTYPE;
1513 x_batch_step_rec GME_BATCH_STEPS%ROWTYPE;
1514
1515 --Bug# 5440347 end
1516
1517 BEGIN
1518
1519 IF (l_debug = 'Y') THEN
1520 gmd_debug.put_line('Entered Procedure UPDATE_SAMPLE_COMP_DISP');
1521 END IF;
1522
1523 -- Initialize API return status to success
1524 x_return_status := FND_API.G_RET_STS_SUCCESS;
1525
1526 l_position := '010' ;
1527
1528 IF (l_debug = 'Y') THEN
1529 gmd_debug.put_line('Input Parameters:');
1530 gmd_debug.put_line('Sample ID: ' || p_update_disp_rec.sample_id);
1531 gmd_debug.put_line('Composite Spec Disp ID: ' || p_update_disp_rec.composite_spec_disp_id);
1532 gmd_debug.put_line('Event Spec Disp ID: ' || p_update_disp_rec.event_spec_disp_id);
1533 gmd_debug.put_line('Change Disp From: ' || p_update_disp_rec.curr_disposition);
1534 gmd_debug.put_line('Change Disp To: ' || p_to_disposition);
1535 END IF;
1536
1537 IF (p_update_disp_rec.sample_id IS NULL AND p_update_disp_rec.composite_spec_disp_id IS NULL) OR (p_to_disposition IS NULL) THEN
1538 raise REQ_FIELDS_MISSING;
1539 END IF;
1540
1541 IF p_update_disp_rec.sample_id IS NOT NULL AND p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
1542 raise INVALID_PARAMETER;
1543 END IF;
1544
1545 IF (p_update_disp_rec.curr_disposition IS NULL OR p_update_disp_rec.event_spec_disp_id IS NULL) THEN
1546 raise REQ_FIELDS_MISSING;
1547 END IF;
1548
1549 l_last_updated_by := FND_GLOBAL.USER_ID ;
1550 l_last_update_login := FND_GLOBAL.LOGIN_ID ;
1551 l_last_update_date := SYSDATE ;
1552
1553
1554 IF p_update_disp_rec.sample_id IS NOT NULL THEN
1555
1556 l_sample_curr_disp := p_update_disp_rec.curr_disposition;
1557 l_event_spec_disp_id := p_update_disp_rec.event_spec_disp_id;
1558
1559 -- check whether the passed driving spec is current.If not raise error.
1560 SELECT SPEC_USED_FOR_LOT_ATTRIB_IND ,sampling_event_id,disposition
1561 INTO l_sample_disp_curr_flag , l_sampling_event_id , l_curr_event_disp
1562 FROM gmd_event_spec_disp
1563 WHERE event_spec_disp_id = l_event_spec_disp_id
1564 FOR UPDATE OF SPEC_USED_FOR_LOT_ATTRIB_IND NOWAIT;
1565
1566 IF NVL(l_sample_disp_curr_flag,'N') = 'N' THEN
1567 RAISE SAMPLE_SPEC_CHANGED;
1568 END IF;
1569
1570 -- check whether the sample disposition has changed.if yes raise error
1571 SELECT disposition INTO l_compare_sample_disp
1572 FROM gmd_sample_spec_disp
1573 WHERE event_spec_disp_id = l_event_spec_disp_id
1574 AND sample_id = p_update_disp_rec.sample_id
1575 FOR UPDATE OF disposition NOWAIT ;
1576
1577 IF l_compare_sample_disp <> l_sample_curr_disp THEN
1578 RAISE SAMPLE_DISP_CHANGED;
1579 END IF;
1580
1581 l_position := '020' ;
1582
1583 -- Set the disposition of the sample spec disp
1584 UPDATE gmd_sample_spec_disp
1585 SET disposition = p_to_disposition,
1586 last_updated_by = l_last_updated_by,
1587 last_update_date = l_last_update_date,
1588 last_update_login = l_last_update_login
1589 WHERE event_spec_disp_id = l_event_spec_disp_id
1590 AND sample_id = p_update_disp_rec.sample_id ;
1591
1592
1593 -- Begin bug 4951244
1594 OPEN cur_sample_details;
1595 FETCH cur_sample_details INTO l_step_id, l_batch_organization_id, l_sample_type, l_source;
1596 CLOSE cur_sample_details;
1597 IF ((l_sample_type = 'I') AND (l_source = 'W' )) THEN
1598 IF l_step_id IS NOT NULL THEN
1599 IF (p_to_disposition in ('4A', '5AV')) THEN
1600 --changing the quality status of batch step to 'In Spec'
1601 gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 6, return_status);
1602 -- Bug# 5440347 start
1603 IF (return_status <> 'S') THEN
1604 FND_MSG_PUB.GET(p_msg_index => 1,
1605 p_data => l_data,
1606 p_encoded => FND_API.G_FALSE,
1607 p_msg_index_out => l_dummy_cnt);
1608 x_message_data := substr(x_message_data || l_data,1,2000) ;
1609 END IF;
1610
1611 --Get the value of auto_complete_batch_step flag.
1612 OPEN cur_auto_complete_bstep;
1613 FETCH cur_auto_complete_bstep INTO l_auto_complete_bstep;
1614 IF cur_auto_complete_bstep%NOTFOUND THEN
1615 l_auto_complete_bstep := 'N';
1616 END IF;
1617 CLOSE cur_auto_complete_bstep;
1618
1619 /*If auto_complete_batch_step flag is checked in the VR which is being used
1620 then only call the gme API to complete the batch step.*/
1621 IF l_auto_complete_bstep = 'Y' THEN
1622 p_batch_step_rec.batchstep_id := l_step_id;
1623
1624 IF (l_debug = 'Y') THEN
1625 gmd_debug.put_line('Before Calling Batch Step Completion API for BATCHSTEP_ID:'||l_step_id);
1626 END IF;
1627 --call the batch step completion API.
1628 GME_API_PUB.complete_step(
1629 p_api_version => 2.0
1630 ,p_validation_level => gme_common_pvt.g_max_errors
1631 ,p_init_msg_list => fnd_api.g_false
1632 ,p_commit => fnd_api.g_false
1633 ,x_message_count => x_message_count
1634 ,x_message_list => x_message_list
1635 ,x_return_status => xx_return_status
1636 ,p_batch_step_rec => p_batch_step_rec
1637 ,p_batch_no => NULL
1638 ,p_org_code => NULL
1639 ,p_ignore_exception => fnd_api.g_false
1640 ,p_override_quality => fnd_api.g_false
1641 ,p_validate_flexfields => fnd_api.g_false
1642 ,x_batch_step_rec => x_batch_step_rec
1643 ,x_exception_material_tbl => l_exception_material_tbl);
1644 --After returning from the API we are not handling any exceptions. Any exceptions will be written in the Debug Log
1645
1646 IF (l_debug = 'Y') THEN
1647 gmd_debug.put_line('Returned from Batch Step Completion Call');
1648 gmd_debug.put_line('x_return_status = '||xx_return_status);
1649 gmd_debug.put_line('x_batch_step_rec.batch_id = '||TO_CHAR(x_batch_step_rec.batch_id));
1650 gmd_debug.put_line('x_batch_step_rec.batchstep_id = '||TO_CHAR(x_batch_step_rec.batchstep_id));
1651 gmd_debug.put_line('x_batch_step_rec.batchstep_no = '||TO_CHAR(x_batch_step_rec.batchstep_no));
1652 gmd_debug.put_line('x_batch_step_rec.actual_start_date = '||TO_CHAR(x_batch_step_rec.actual_start_date,'DD-MON-YYYY HH24:MI:SS'));
1653 gmd_debug.put_line('x_batch_step_rec.actual_cmplt_date = '||TO_CHAR(x_batch_step_rec.actual_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
1654 gmd_debug.put_line('x_batch_step_rec.step_status = '||TO_CHAR(x_batch_step_rec.step_status));
1655 END IF;
1656 END IF; --l_auto_complete_bstep = 'Y'
1657 --Bug# 5440347 end
1658
1659 ELSIF (p_to_disposition = '6RJ') THEN
1660 --changing the quality status of batch step to 'Action Required'
1661 gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 5, return_status);
1662 --Bug# 5440347 start
1663 IF (return_status <> 'S') THEN
1664 FND_MSG_PUB.GET(p_msg_index => 1,
1665 p_data => l_data,
1666 p_encoded => FND_API.G_FALSE,
1667 p_msg_index_out => l_dummy_cnt);
1668 x_message_data := substr(x_message_data || l_data,1,2000) ;
1669 END IF;
1670 --Bug# 5440347 end
1671 END IF; -- (l_sample_type = 'I') AND (l_source = 'W' )
1672 END IF; --step id is not null
1673 END IF; --test for WIP sample
1674 -- End bug 4951244
1675
1676
1677 -- If sample disposition is changed to "retain" or "cancel" ,
1678 -- decrement the sample active count
1679 -- also if retain is changed to pending, increment the sample count
1680 -- the above condition will happen only thru change disposition form
1681 IF (l_sample_curr_disp NOT IN ('0RT','7CN') AND
1682 p_to_disposition IN ('0RT','7CN'))
1683 THEN
1684
1685 UPDATE gmd_sampling_events
1686 SET sample_active_cnt = sample_active_cnt - 1,
1687 recomposite_ind = 'Y',
1688 last_updated_by = l_last_updated_by,
1689 last_update_date = l_last_update_date,
1690 last_update_login = l_last_update_login
1691 WHERE sampling_event_id = l_sampling_event_id ;
1692
1693 ELSIF (l_sample_curr_disp IN ('0RT','7CN') AND
1694 p_to_disposition NOT IN ('0RT','7CN'))
1695 THEN
1696
1697 UPDATE gmd_sampling_events
1698 SET sample_active_cnt = sample_active_cnt + 1,
1699 recomposite_ind = 'Y',
1700 last_updated_by = l_last_updated_by,
1701 last_update_date = l_last_update_date,
1702 last_update_login = l_last_update_login
1703 WHERE sampling_event_id = l_sampling_event_id ;
1704 END IF;
1705
1706 l_position := '025' ;
1707
1708 SELECT nvl(sample_active_cnt,0),nvl(sample_req_cnt,1)
1709 INTO l_active_cnt,l_req_cnt
1710 FROM gmd_sampling_events
1711 WHERE sampling_event_id = l_sampling_event_id
1712 FOR UPDATE OF disposition NOWAIT ;
1713
1714 ELSIF p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
1715
1716 l_sample_curr_disp := p_update_disp_rec.curr_disposition;
1717 l_event_spec_disp_id := p_update_disp_rec.event_spec_disp_id;
1718
1719 l_position := '030' ;
1720
1721 -- check whether the passed driving spec is current.If not raise error.
1722 SELECT esd.SPEC_USED_FOR_LOT_ATTRIB_IND ,esd.sampling_event_id,csd.disposition
1723 INTO l_sample_disp_curr_flag , l_sampling_event_id ,l_compare_sample_disp
1724 FROM gmd_composite_spec_disp csd , gmd_event_spec_disp esd
1725 WHERE csd.composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id
1726 AND esd.event_spec_disp_id = csd.event_spec_disp_id
1727 FOR UPDATE OF esd.SPEC_USED_FOR_LOT_ATTRIB_IND , csd.disposition NOWAIT;
1728
1729 IF NVL(l_sample_disp_curr_flag,'N') = 'N' THEN
1730 RAISE SAMPLE_SPEC_CHANGED;
1731 END IF;
1732
1733 -- check whether the composite sample disposition has changed.if yes raise error
1734 IF l_compare_sample_disp <> l_sample_curr_disp THEN
1735 RAISE SAMPLE_DISP_CHANGED;
1736 END IF;
1737
1738 l_position := '035' ;
1739
1740 UPDATE gmd_composite_spec_disp
1741 SET disposition = p_to_disposition
1742 WHERE composite_spec_disp_id = p_update_disp_rec.composite_spec_disp_id ;
1743
1744 SELECT nvl(sample_active_cnt,0),nvl(sample_req_cnt,1)
1745 INTO l_active_cnt,l_req_cnt
1746 FROM gmd_sampling_events
1747 WHERE sampling_event_id = l_sampling_event_id
1748 FOR UPDATE OF disposition NOWAIT ;
1749
1750 --Bug# 5440347 start
1751 --Get the sampling event details
1752 OPEN cur_sampling_event_details(l_sampling_event_id);
1753 FETCH cur_sampling_event_details INTO l_step_id,l_batch_organization_id,l_sample_type, l_source;
1754 CLOSE cur_sampling_event_details;
1755
1756 IF ((l_sample_type = 'I') AND (l_source = 'W') AND l_step_id IS NOT NULL) THEN
1757 IF (p_to_disposition in ('4A', '5AV')) THEN
1758 --changing the quality status of batch step to 'In Spec'
1759 gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 6, return_status);
1760 IF (return_status <> 'S') THEN
1761 FND_MSG_PUB.GET(p_msg_index => 1,
1762 p_data => l_data,
1763 p_encoded => FND_API.G_FALSE,
1764 p_msg_index_out => l_dummy_cnt);
1765 x_message_data := substr(x_message_data || l_data,1,2000) ;
1766 END IF;
1767 --Get the value of auto_complete_batch_step flag.
1768 OPEN cur_comp_auto_complete_bstep(l_sampling_event_id);
1769 FETCH cur_comp_auto_complete_bstep INTO l_auto_complete_bstep;
1770 IF cur_comp_auto_complete_bstep%NOTFOUND THEN
1771 l_auto_complete_bstep := 'N';
1772 END IF;
1773 CLOSE cur_comp_auto_complete_bstep;
1774 /*If auto_complete_batch_step flag is checked in the VR which is being used
1775 then only call the gme API to complete the batch step.*/
1776 IF l_auto_complete_bstep = 'Y' THEN
1777 p_batch_step_rec.batchstep_id := l_step_id;
1778 IF (l_debug = 'Y') THEN
1779 gmd_debug.put_line('Before Calling Batch Step Completion API for BATCHSTEP_ID:'||l_step_id);
1780 END IF;
1781 --call the batch step completion API.
1782 GME_API_PUB.complete_step(
1783 p_api_version => 2.0
1784 ,p_validation_level => gme_common_pvt.g_max_errors
1785 ,p_init_msg_list => fnd_api.g_false
1786 ,p_commit => fnd_api.g_false
1787 ,x_message_count => x_message_count
1788 ,x_message_list => x_message_list
1789 ,x_return_status => xx_return_status
1790 ,p_batch_step_rec => p_batch_step_rec
1791 ,p_batch_no => NULL
1792 ,p_org_code => NULL
1793 ,p_ignore_exception => fnd_api.g_false
1794 ,p_override_quality => fnd_api.g_false
1795 ,p_validate_flexfields => fnd_api.g_false
1796 ,x_batch_step_rec => x_batch_step_rec
1797 ,x_exception_material_tbl => l_exception_material_tbl);
1798 --After returning from the API we are not handling any exceptions. Any exceptions will be written in the Debug Log
1799 IF (l_debug = 'Y') THEN
1800 gmd_debug.put_line('Returned from Batch Step Completion Call');
1801 gmd_debug.put_line('x_return_status = '||xx_return_status);
1802 gmd_debug.put_line('x_batch_step_rec.batch_id = '||TO_CHAR(x_batch_step_rec.batch_id));
1803 gmd_debug.put_line('x_batch_step_rec.batchstep_id = '||TO_CHAR(x_batch_step_rec.batchstep_id));
1804 gmd_debug.put_line('x_batch_step_rec.batchstep_no = '||TO_CHAR(x_batch_step_rec.batchstep_no));
1805 gmd_debug.put_line('x_batch_step_rec.actual_start_date = '||TO_CHAR(x_batch_step_rec.actual_start_date,'DD-MON-YYYY HH24:MI:SS'));
1806 gmd_debug.put_line('x_batch_step_rec.actual_cmplt_date = '||TO_CHAR(x_batch_step_rec.actual_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
1807 gmd_debug.put_line('x_batch_step_rec.step_status = '||TO_CHAR(x_batch_step_rec.step_status));
1808 END IF;
1809 END IF; --l_auto_complete_bstep = 'Y'
1810 ELSIF (p_to_disposition = '6RJ') THEN
1811 --changing the quality status of batch step to 'Action Required'
1812 gme_api_grp.update_step_quality_status(l_step_id, l_batch_organization_id, 5, return_status);
1813 IF (return_status <> 'S') THEN
1814 FND_MSG_PUB.GET(p_msg_index => 1,
1815 p_data => l_data,
1816 p_encoded => FND_API.G_FALSE,
1817 p_msg_index_out => l_dummy_cnt);
1818 x_message_data := substr(x_message_data || l_data,1,2000) ;
1819 END IF;
1820 END IF; --p_to_disposition in ('4A', '5AV')
1821 END IF; --test for WIP sample with step
1822 --Bug# 5440347 end
1823
1824 END IF;
1825
1826 -- If calling from composite result form,update the event disposition to
1827 -- whatever passed.
1828 l_position := '040' ;
1829
1830 IF p_update_disp_rec.composite_spec_disp_id IS NOT NULL THEN
1831 l_final_event_disp := p_to_disposition ;
1832 ELSE
1833 IF (l_debug = 'Y') THEN
1834 gmd_debug.put_line('l_active_cnt=>'||l_active_cnt);
1835 gmd_debug.put_line('l_req_cnt=>'||l_req_cnt);
1836 gmd_debug.put_line('to_disp=>'||p_to_disposition);
1837 gmd_debug.put_line('curr_event_disp=>'||l_curr_event_disp);
1838 END IF;
1839
1840 -- if there is no active sample,update the event to 'Pending'
1841 -- active count can't be negative in real scenario.Just to be on safe side.
1842 IF l_active_cnt <= 0 THEN
1843 l_final_event_disp := '1P';
1844 ELSIF (l_active_cnt = 1 AND l_req_cnt = 1) THEN
1845
1846 l_position := '050' ;
1847 IF l_curr_event_disp in ('4A','5AV','6RJ')
1848 THEN
1849 RETURN;
1850 END IF;
1851
1852 IF (l_debug = 'Y') THEN
1853 gmd_debug.put_line('Sampling Event ID: '||l_sampling_event_id);
1854 END IF;
1855
1856 -- get MAXIMUM sample disposition from all the samples for that event.
1857 SELECT MAX(ssd.disposition) INTO l_max_disposition
1858 FROM gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
1859 WHERE esd.event_spec_disp_id = l_event_spec_disp_id
1860 AND esd.event_spec_disp_id = ssd.event_spec_disp_id
1861 AND esd.delete_mark = 0
1862 AND ssd.delete_mark = 0
1863 AND ssd.disposition NOT IN ('0RT', '7CN');
1864
1865 IF (l_debug = 'Y') THEN
1866 gmd_debug.put_line('max disp=>'||l_max_disposition);
1867 END IF;
1868
1869 -- there could be a scenario where one has 2 samples.S1 with disp = '4A'
1870 -- and S2 with disp = 'In progess'.Now user tries to change the disp
1871 -- of S2(current sample in this case) to Cancel/Retain, then don't
1872 -- update the event spec to Approve.Make it Complete.
1873 IF p_to_disposition IN ('0RT','7CN') AND l_max_disposition in ('4A','5AV','6RJ') THEN
1874 l_final_event_disp := '3C';
1875 ELSE
1876 l_final_event_disp := l_max_disposition;
1877 END IF;
1878 IF (l_debug = 'Y') THEN
1879 gmd_debug.put_line('FInal disp=>'||l_final_event_disp);
1880 END IF;
1881 ELSE
1882 -- either required count > 1 or active count > 1
1883 -- if event is already approved/rejected , don't update the event .
1884 l_position := '060' ;
1885
1886 IF l_curr_event_disp in ('4A','5AV','6RJ') THEN
1887 RETURN;
1888 END IF;
1889
1890 SELECT MAX(ssd.disposition),MIN(ssd.disposition)
1891 INTO l_max_disposition,l_min_disposition
1892 FROM gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
1893 WHERE
1894 esd.event_spec_disp_id = l_event_spec_disp_id
1895 AND esd.event_spec_disp_id = ssd.event_spec_disp_id
1896 AND esd.delete_mark = 0
1897 AND ssd.delete_mark = 0
1898 AND ssd.disposition NOT IN ('0RT','7CN');
1899
1900 l_position := '070' ;
1901
1902 IF l_active_cnt < l_req_cnt THEN
1903 IF l_max_disposition = '1P' THEN
1904 l_final_event_disp := '1P';
1905 ELSE
1906 l_final_event_disp := '2I';
1907 END IF;
1908 ELSE
1909 IF (l_min_disposition = '1P' AND l_max_disposition = '1P') THEN
1910 l_final_event_disp := '1P';
1911 ELSIF (l_min_disposition IN ('3C','4A','5AV','6RJ'))
1912 AND (l_max_disposition IN ('3C','4A','5AV','6RJ')) THEN
1913 l_final_event_disp := '3C';
1914 ELSE
1915 l_final_event_disp := '2I';
1916 END IF;
1917 END IF;
1918 END IF; -- end of l_active_cnt <= 0
1919 END IF;
1920
1921 l_position := '080' ;
1922
1923 IF l_final_event_disp IS NULL THEN
1924 l_final_event_disp := '1P';
1925 END IF;
1926
1927 IF (l_debug = 'Y') THEN
1928 gmd_debug.put_line('Final disp last =>'||l_final_event_disp);
1929 END IF;
1930
1931 -- Set the disposition of the Event spec disp
1932 UPDATE gmd_event_spec_disp
1933 SET disposition = l_final_event_disp,
1934 last_updated_by = l_last_updated_by,
1935 last_update_date = l_last_update_date,
1936 last_update_login = l_last_update_login
1937 WHERE event_spec_disp_id = l_event_spec_disp_id ;
1938
1939 -- Set the disposition of the Sampling Event
1940 UPDATE gmd_sampling_events
1941 SET disposition = l_final_event_disp,
1942 last_updated_by = l_last_updated_by,
1943 last_update_date = l_last_update_date,
1944 last_update_login = l_last_update_login
1945 WHERE sampling_event_id = l_sampling_event_id ;
1946
1947
1948 EXCEPTION WHEN REQ_FIELDS_MISSING THEN
1949 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
1950 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1951 x_return_status := FND_API.G_RET_STS_ERROR ;
1952 WHEN INVALID_PARAMETER THEN
1953 gmd_api_pub.log_message('GMD_INVALID_PARAM','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
1954 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1955 x_return_status := FND_API.G_RET_STS_ERROR ;
1956 WHEN SAMPLE_SPEC_CHANGED THEN
1957 gmd_api_pub.log_message('GMD_SAMPLE_SPEC_CHANGED','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
1958 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1959 x_return_status := FND_API.G_RET_STS_ERROR ;
1960 WHEN SAMPLE_DISP_CHANGED THEN
1961 gmd_api_pub.log_message('GMD_SMPL_DISP_CHANGE','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
1962 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1963 x_return_status := FND_API.G_RET_STS_ERROR ;
1964 WHEN OTHERS THEN
1965 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1966 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1968
1969 END update_sample_comp_disp ;
1970
1971
1972 --Start of comments
1973 --+========================================================================+
1974 --| API Name : update_change_disp_table
1975 --| TYPE : Group |
1976 --| Notes : This procedure creates records in change disposition |
1977 --| tables. If everything is fine then OUT parameter |
1978 --| x_return_status is set to 'S' else appropriate |
1979 --| error message is returned. |
1980 --| |
1981 --| HISTORY |
1982 --| S. Feinstein 05-MAY-2005 Created for Inventory Convergence |
1983 --+========================================================================+
1984 -- End of comments
1985 PROCEDURE update_change_disp_table
1986 (
1987 p_update_change_disp_rec IN UPDATE_CHANGE_DISP_REC
1988 , x_return_status OUT NOCOPY VARCHAR2
1989 , x_message_data OUT NOCOPY VARCHAR2
1990 ) IS
1991 --xxx
1992 CURSOR Cur_get_seq IS
1993 SELECT gmd_qc_change_disp_id_s.NEXTVAL
1994 FROM DUAL;
1995
1996 CURSOR Cur_get_lot IS
1997 SELECT lot_number
1998 FROM MTL_LOT_NUMBERS
1999 WHERE inventory_item_id = p_update_change_disp_rec.inventory_item_id
2000 AND organization_id = p_update_change_disp_rec.organization_id
2001 AND parent_lot_number = p_update_change_disp_rec.parent_lot_number ;
2002
2003 l_change_disp_id NUMBER;
2004 l_lot_number VARCHAR2(80);
2005
2006 BEGIN
2007 -- Initialize API return status to success
2008 x_return_status := FND_API.G_RET_STS_SUCCESS;
2009
2010 OPEN Cur_get_seq;
2011 FETCH Cur_get_seq INTO l_change_disp_id;
2012 CLOSE Cur_get_seq;
2013
2014 IF (l_debug = 'Y') THEN
2015 gmd_debug.put_line('In Procedure update_change_disp_table and input parameters = ');
2016 gmd_debug.put_line(' change_disp_id: ' || l_change_disp_id);
2017 gmd_debug.put_line(' organization ID: ' || p_update_change_disp_rec.organization_id);
2018 gmd_debug.put_line(' Sample ID: ' || p_update_change_disp_rec.sample_id);
2019 gmd_debug.put_line(' sampling_event_id : ' || p_update_change_disp_rec.sampling_event_id);
2020 gmd_debug.put_line(' disposition_from : ' || p_update_change_disp_rec.disposition_from);
2021 gmd_debug.put_line(' disposition_to : ' || p_update_change_disp_rec.disposition_to);
2022 gmd_debug.put_line(' parent lot number : ' || p_update_change_disp_rec.parent_lot_number);
2023 gmd_debug.put_line(' lot number: ' || p_update_change_disp_rec.lot_number);
2024 gmd_debug.put_line(' lot status id: ' || p_update_change_disp_rec.to_lot_status_id);
2025 gmd_debug.put_line(' lot status id: ' || p_update_change_disp_rec.from_lot_status_id);
2026 gmd_debug.put_line(' grade code: ' || p_update_change_disp_rec.to_grade_code);
2027 gmd_debug.put_line(' grade code: ' || p_update_change_disp_rec.from_grade_code);
2028 gmd_debug.put_line(' hold date: ' || p_update_change_disp_rec.hold_date);
2029 gmd_debug.put_line(' reason id : ' || p_update_change_disp_rec.reason_id);
2030 END IF;
2031
2032 INSERT INTO GMD_CHANGE_DISPOSITION
2033 (
2034 CHANGE_DISP_ID
2035 ,ORGANIZATION_ID
2036 ,SAMPLE_ID
2037 ,SAMPLING_EVENT_ID
2038 ,DISPOSITION_FROM
2039 ,DISPOSITION_TO
2040 ,PARENT_LOT_NUMBER
2041 ,LOT_NUMBER
2042 ,LOT_STATUS_ID
2043 ,GRADE_CODE
2044 ,REASON_ID
2045 ,HOLD_DATE
2046 ,CREATION_DATE
2047 ,CREATED_BY
2048 ,LAST_UPDATED_BY
2049 ,LAST_UPDATE_DATE
2050 ,LAST_UPDATE_LOGIN
2051 )
2052 VALUES
2053 (
2054 l_change_disp_id
2055 ,p_update_change_disp_rec.ORGANIZATION_ID
2056 ,p_update_change_disp_rec.SAMPLE_ID
2057 ,p_update_change_disp_rec.SAMPLING_EVENT_ID
2058 ,p_update_change_disp_rec.DISPOSITION_FROM
2059 ,p_update_change_disp_rec.DISPOSITION_TO
2060 ,p_update_change_disp_rec.PARENT_LOT_NUMBER
2061 ,p_update_change_disp_rec.LOT_NUMBER
2062 ,p_update_change_disp_rec.TO_LOT_STATUS_ID
2063 ,p_update_change_disp_rec.TO_GRADE_CODE
2064 ,p_update_change_disp_rec.REASON_ID
2065 ,p_update_change_disp_rec.HOLD_DATE
2066 ,SYSDATE
2067 ,fnd_global.user_id
2068 ,fnd_global.user_id
2069 ,SYSDATE
2070 ,fnd_global.user_id
2071 );
2072
2073
2074 IF SQL%NOTFOUND THEN
2075 gmd_api_pub.log_message('GMD_QM_CHANGE_DISP_ERR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_CHANGE_DISP_TABLE');
2076 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2077 x_return_status := FND_API.G_RET_STS_ERROR ;
2078 END IF; -- SQL%NOTFOUND THEN
2079
2080
2081
2082 IF (p_update_change_disp_rec.LOT_NUMBER IS NOT NULL) THEN
2083 -- just one lot updated
2084 INSERT INTO GMD_CHANGE_LOTS
2085 (
2086 CHANGE_DISP_ID
2087 ,LOT_NUMBER
2088 ,FROM_LOT_STATUS_ID
2089 ,FROM_GRADE_CODE
2090 ,CREATION_DATE
2091 ,CREATED_BY
2092 ,LAST_UPDATED_BY
2093 ,LAST_UPDATE_DATE
2094 ,LAST_UPDATE_LOGIN
2095 )
2096 VALUES
2097 (
2098 l_change_disp_id
2099 ,p_update_change_disp_rec.LOT_NUMBER
2100 ,p_update_change_disp_rec.FROM_LOT_STATUS_ID
2101 ,p_update_change_disp_rec.FROM_GRADE_CODE
2102 ,SYSDATE
2103 ,fnd_global.user_id
2104 ,fnd_global.user_id
2105 ,SYSDATE
2106 ,fnd_global.user_id
2107 );
2108 ELSIF (p_update_change_disp_rec.PARENT_LOT_NUMBER IS NOT NULL) THEN
2109
2110 OPEN cur_get_lot;
2111
2112 LOOP
2113 FETCH cur_get_lot into l_lot_number ;
2114 EXIT WHEN cur_get_lot%NOTFOUND ; -- exit when last row is fetched
2115
2116 INSERT INTO GMD_CHANGE_LOTS
2117 (
2118 CHANGE_DISP_ID
2119 ,LOT_NUMBER
2120 ,FROM_LOT_STATUS_ID
2121 ,FROM_GRADE_CODE
2122 ,CREATION_DATE
2123 ,CREATED_BY
2124 ,LAST_UPDATED_BY
2125 ,LAST_UPDATE_DATE
2126 ,LAST_UPDATE_LOGIN
2127 )
2128 VALUES
2129 (
2130 l_change_disp_id
2131 ,L_LOT_NUMBER
2132 ,p_update_change_disp_rec.FROM_LOT_STATUS_ID
2133 ,p_update_change_disp_rec.FROM_GRADE_CODE
2134 ,SYSDATE
2135 ,fnd_global.user_id
2136 ,fnd_global.user_id
2137 ,SYSDATE
2138 ,fnd_global.user_id
2139 );
2140
2141
2142 END LOOP;
2143 CLOSE cur_get_lot;
2144
2145 END IF; -- (p_update_change_disp_rec.LOT_NUMBER IS NOT NULL)
2146
2147 IF SQL%NOTFOUND THEN
2148 gmd_api_pub.log_message('GMD_QM_CHANGE_LOT_ERR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_CHANGE_DISP_TABLE');
2149 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2150 x_return_status := FND_API.G_RET_STS_ERROR ;
2151 END IF; -- SQL%NOTFOUND THEN
2152
2153 END update_change_disp_table;
2154
2155
2156 --Start of comments
2157 --+========================================================================+
2158 --| API Name : update_lot_grade_batch |
2159 --| TYPE : Group |
2160 --| Notes : This procedure updates lot status and/or grade |
2161 --| and/or batch step status |
2162 --| If everything is fine then OUT parameter |
2163 --| x_return_status is set to 'S' else appropriate |
2164 --| error message is returned. |
2165 --| |
2166 --| |
2167 --| |
2168 --| HISTORY |
2169 --| Mahesh Chandak 18-Sep-2002 Created. |
2170 --| |
2171 --| S. Feinstein 28-Apr-2005 Changes for Inventory Convergence |
2172 --| 1. added Update Child lots |
2173 --| 2. calls to GMIPAPI changed |
2174 --| 3. cursor c_ic_lots modified |
2175 --| 4. added parent_lot_number |
2176 --| Peter Lowe 02-Mar-2006 Bug 50061731. | |
2177 --| If the sample_id is not null then fetching the values of |
2178 --| subinventory (whse_code) |
2179 --| and location of the sample and while changing the status of the lot,|
2180 --| using these subinventory and location also as part of the criteria.
2181 --| |
2182 --+========================================================================+
2183 --| |
2184 --+========================================================================+
2185 -- End of comments
2186
2187 PROCEDURE update_lot_grade_batch
2188 (
2189 p_sample_id IN NUMBER DEFAULT NULL
2190 , p_composite_spec_disp_id IN NUMBER DEFAULT NULL
2191 , p_to_lot_status_id IN NUMBER
2192 , p_from_lot_status_id IN NUMBER
2193 , p_to_grade_code IN VARCHAR2
2194 , p_from_grade_code IN VARCHAR2 DEFAULT NULL
2195 , p_to_qc_status IN NUMBER
2196 , p_reason_id IN NUMBER
2197 , p_hold_date IN DATE DEFAULT SYSDATE
2198 , x_return_status OUT NOCOPY VARCHAR2
2199 , x_message_data OUT NOCOPY VARCHAR2
2200 ) IS
2201
2202 l_position VARCHAR2(3) := '010';
2203 l_grade VARCHAR2(150);
2204 l_sampling_event_id NUMBER(15);
2205
2206 -- taken out with bug 4165704: inventory convergence
2207 -- l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
2208 -- l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
2209 -- l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
2210 -- l_count NUMBER := 0;
2211 -- l_loop_cnt NUMBER :=0;
2212 -- l_dummy_cnt NUMBER :=0;
2213 -- l_trans_rec_grade GMIGAPI.qty_rec_typ; --bug 4165704
2214 -- l_trans_rec_lot_status GMIGAPI.qty_rec_typ; --bug 4165704
2215 -- l_tempb BOOLEAN;
2216
2217 l_data VARCHAR2(2000);
2218 l_parent_lot_number MTL_LOT_NUMBERS.parent_lot_number%TYPE;
2219 l_lot_number MTL_LOT_NUMBERS.lot_number%TYPE;
2220 l_inventory_item_number VARCHAR2(2000);
2221 l_inventory_item_id MTL_SYSTEM_ITEMS.inventory_item_id%TYPE;
2222 l_organization_id NUMBER;
2223 l_batch_id NUMBER(15);
2224 l_step_no NUMBER(10);
2225 l_curr_qc_status NUMBER(2);
2226 l_rowid ROWID;
2227 l_locator_id number; -- 50061731
2228 l_subinventory varchar2(10); -- 50061731
2229
2230 l_batch_status NUMBER ; -- Bug # 4619570
2231
2232
2233 req_fields_missing EXCEPTION;
2234 invalid_sample EXCEPTION;
2235 invalid_qc_status EXCEPTION;
2236 sample_disp_changed EXCEPTION;
2237
2238 TYPE GET_CURR_GRADE_REC_TYP IS RECORD (
2239 lot_number VARCHAR2(80),
2240 grade_code VARCHAR2(150)
2241 );
2242
2243 TYPE cr_get_curr_grade IS REF CURSOR RETURN GET_CURR_GRADE_REC_TYP;
2244
2245 get_curr_grade_cv cr_get_curr_grade; -- declare cursor variable
2246 get_curr_grade_rec GET_CURR_GRADE_REC_TYP ;
2247
2248 -- Bug 4165704: Changed record type for new lot_status transaction
2249 --TYPE GET_CURR_LOT_STATUS_REC_TYP IS RECORD (
2250 -- lot_number VARCHAR2(80)
2251 -- ,subinventory VARCHAR2(10)
2252 -- ,locator_id NUMBER
2253 -- );
2254 TYPE GET_CURR_LOT_STATUS_REC_TYP IS RECORD (
2255 lot_number VARCHAR2(80)
2256 ,status_id NUMBER
2257 );
2258
2259 TYPE cr_get_curr_lot_status IS REF CURSOR RETURN GET_CURR_LOT_STATUS_REC_TYP;
2260
2261 get_curr_lot_status_cv cr_get_curr_lot_status; -- declare cursor variable
2262 get_curr_lot_status_rec GET_CURR_LOT_STATUS_REC_TYP ;
2263
2264 -- Manish Gupta B3143795, Update hold date
2265 -- Bug 4165704: changed so that cursor is going against correct table
2266 CURSOR c_mtl_lot_numbers(p_parent_lot_number IN VARCHAR2,
2267 p_organization_id IN NUMBER,
2268 p_inventory_item_id IN NUMBER) IS
2269 SELECT hold_date
2270 FROM mtl_lot_numbers
2271 WHERE parent_lot_number = p_parent_lot_number
2272 AND inventory_item_id = p_inventory_item_id
2273 AND organization_id = p_organization_id;
2274
2275 -- Bug 4165704: changed so that cursor is going against correct table
2276 CURSOR c_lots (p_parent_lot_number IN VARCHAR2,
2277 p_organization_id IN NUMBER,
2278 p_inventory_item_id IN NUMBER) IS
2279 SELECT 1
2280 FROM mtl_lot_numbers
2281 WHERE parent_lot_number = p_parent_lot_number
2282 AND organization_id = p_organization_id
2283 AND inventory_item_id = p_inventory_item_id;
2284
2285 CURSOR c_lot_status (
2286 p_lot_number IN VARCHAR2,
2287 p_organization_id IN NUMBER,
2288 p_inventory_item_id IN NUMBER) IS
2289 SELECT lot_number,
2290 status_id
2291 FROM MTL_LOT_NUMBERS
2292 WHERE inventory_item_id = l_inventory_item_id
2293 AND organization_id = l_organization_id
2294 AND lot_number = l_lot_number ;
2295
2296 record_lock EXCEPTION ;
2297 pragma exception_init(record_lock,-00054) ;
2298
2299 -- Manish Gupta B3143795, Update hold date
2300
2301 --Bug# 5440347 start
2302 --this cursor is used for single samples
2303 CURSOR cur_auto_complete_bstep_smpl IS
2304 SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
2305 FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,GMD_SAMPLES gs
2306 WHERE gs.SAMPLE_ID = p_sample_id
2307 AND gse.SAMPLING_EVENT_ID = gs.SAMPLING_EVENT_ID
2308 AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
2309
2310 CURSOR cur_auto_complete_bstep_comp IS
2311 SELECT NVL(wip.AUTO_COMPLETE_BATCH_STEP,'N')
2312 FROM GMD_WIP_SPEC_VRS wip,GMD_SAMPLING_EVENTS gse,
2313 GMD_EVENT_SPEC_DISP esd, GMD_COMPOSITE_SPEC_DISP csd
2314 WHERE csd.COMPOSITE_SPEC_DISP_ID = p_composite_spec_disp_id
2315 AND esd.EVENT_SPEC_DISP_ID = csd.EVENT_SPEC_DISP_ID
2316 AND gse.SAMPLING_EVENT_ID = esd.SAMPLING_EVENT_ID
2317 AND wip.SPEC_VR_ID = gse.ORIGINAL_SPEC_VR_ID;
2318
2319 l_bstep_id NUMBER;
2320 l_auto_complete_bstep VARCHAR2(1) := NULL;
2321 x_message_count NUMBER;
2322 x_message_list VARCHAR2(2000);
2323 xx_return_status VARCHAR2(1);
2324 l_exception_material_tbl GME_COMMON_PVT.exceptions_tab;
2325 p_batch_step_rec GME_BATCH_STEPS%ROWTYPE;
2326 x_batch_step_rec GME_BATCH_STEPS%ROWTYPE;
2327 l_step_status NUMBER;
2328
2329 --Bug# 5440347 end
2330
2331
2332 BEGIN
2333 -- Initialize API return status to success
2334 x_return_status := FND_API.G_RET_STS_SUCCESS;
2335
2336 IF (p_sample_id IS NULL AND p_composite_spec_disp_id IS NULL)
2337 OR (p_to_lot_status_id IS NULL AND p_to_grade_code IS NULL AND p_to_qc_status IS NULL) THEN
2338 RAISE REQ_FIELDS_MISSING;
2339 END IF;
2340
2341 -- Bug 4165704: reason code is no longer required after convergence
2342 -- IF (p_to_lot_status IS NOT NULL OR p_to_grade_code IS NOT NULL) AND (p_reason_code IS NULL) THEN
2343 -- RAISE REQ_FIELDS_MISSING;
2344 -- END IF;
2345
2346 IF p_to_qc_status IS NOT NULL AND p_to_qc_status NOT IN (5,6) THEN
2347 RAISE INVALID_QC_STATUS;
2348 END IF;
2349
2350 IF p_sample_id IS NOT NULL THEN
2351 -- BUG 4165704: changed ic_item_mst to mtl_system_items, and updated field names for inventory convergence
2352 SELECT gs.organization_id ,
2353 gs.inventory_item_id,
2354 iim.concatenated_segments,
2355 gs.parent_lot_number,
2356 gs.lot_number,
2357 gs.batch_id,
2358 gs.step_no,
2359 gs.locator_id, -- 50061731
2360 gs.subinventory -- 50061731
2361 INTO l_organization_id,
2362 l_inventory_item_id,
2363 l_inventory_item_number,
2364 l_parent_lot_number,
2365 l_lot_number,
2366 l_batch_id,
2367 l_step_no,
2368 l_locator_id, -- 50061731
2369 l_subinventory -- 50061731
2370 FROM GMD_SAMPLES gs,
2371 MTL_SYSTEM_ITEMS_b_kfv iim
2372 WHERE gs.sample_id = p_sample_id
2373 AND gs.inventory_item_id = iim.inventory_item_id
2374 AND gs.organization_id = iim.organization_id;
2375 ELSE
2376 -- BUG 4165704: changed ic_item_mst to mtl_system_items, and updated field names for inventory convergence
2377 SELECT gse.organization_id,
2378 gse.inventory_item_id,
2379 iim.concatenated_segments,
2380 gse.parent_lot_number,
2381 gse.lot_number,
2382 gse.sampling_event_id,
2383 gse.batch_id,
2384 gse.step_no
2385 INTO l_organization_id,
2386 l_inventory_item_id,
2387 l_inventory_item_number,
2388 l_parent_lot_number,
2389 l_lot_number,
2390 l_sampling_event_id,
2391 l_batch_id,
2392 l_step_no
2393 FROM GMD_COMPOSITE_SPEC_DISP csd,
2394 GMD_EVENT_SPEC_DISP esd ,
2395 GMD_SAMPLING_EVENTS gse,
2396 MTL_SYSTEM_ITEMS_b_kfv iim
2397 WHERE csd.composite_spec_disp_id = p_composite_spec_disp_id
2398 and csd.event_spec_disp_id = esd.event_spec_disp_id
2399 and esd.sampling_event_id = gse.sampling_event_id
2400 and gse.inventory_item_id = iim.inventory_item_id
2401 and gse.organization_id = iim.organization_id ;
2402
2403 -- select the orgn_code from the first sample.
2404 -- Bug 4165704: took out the following code since orgn is now kept on sampling event.
2405 -- added organization to select statement above
2406 --SELECT orgn_code INTO l_orgn_code
2407 --FROM GMD_SAMPLES
2408 --WHERE sampling_event_id = l_sampling_event_id
2409 --AND rownum = 1 ;
2410
2411 END IF;
2412
2413 -- Manish Gupta B3143795, Update hold date
2414 -- Bug 4165704: changed cursor to use lot_number, parent_lot_number and mtl_lot_numbers table
2415 IF l_parent_lot_number IS NOT NULL
2416 AND l_lot_number IS NULL THEN
2417 FOR l_lots in c_lots(
2418 l_parent_lot_number,
2419 l_organization_id ,
2420 l_inventory_item_id)
2421 LOOP
2422
2423 --Lock the mtl_lot_numbers before updating.
2424 OPEN c_mtl_lot_numbers(l_parent_lot_number,
2425 l_inventory_item_id,
2426 l_organization_id);
2427 CLOSE c_mtl_lot_numbers;
2428
2429 -- Bug 4165704: hold date is now updated on mtl_lot_numbers
2430 --UPDATE ic_lots_cpg
2431 --SET ic_hold_date = p_hold_date
2432 --WHERE item_id =l_lot_number.item_id
2433 --AND lot_id = l_lot_number.lot_id;
2434 UPDATE mtl_lot_numbers
2435 SET hold_date = p_hold_date
2436 WHERE inventory_item_id = l_inventory_item_id
2437 AND organization_id = l_organization_id
2438 AND ((parent_lot_number = l_parent_lot_number )
2439 OR ( lot_number = l_parent_lot_number
2440 AND parent_lot_number IS NULL) );
2441 END LOOP;
2442
2443 ELSIF l_lot_number IS NOT NULL THEN
2444 UPDATE mtl_lot_numbers
2445 SET hold_date = p_hold_date
2446 WHERE inventory_item_id = l_inventory_item_id
2447 AND organization_id = l_organization_id
2448 AND lot_number = l_lot_number;
2449
2450 END IF; -- test for parent lot
2451
2452 l_position := '015' ;
2453
2454 -- set up constants needed for the inventory API.
2455 -- Bug 4165704- not needed after inventory convergence
2456 --l_tempb := GMIGUTL.SETUP(FND_GLOBAL.USER_NAME);
2457 --IF (NOT l_tempb) THEN
2458 -- x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2459 -- x_return_status := FND_API.G_RET_STS_ERROR ;
2460 -- return;
2461 --END IF;
2462
2463 l_position := '020' ;
2464 --gml_sf_log('start 020');
2465
2466 IF p_to_grade_code IS NOT NULL THEN
2467
2468 IF l_parent_lot_number IS NOT NULL
2469 AND l_lot_number IS NULL THEN
2470 -- Bug 4165704: sublot removed from db for inventory conversion
2471 -- change all lots under the parent lot
2472 -- IF l_sublot_no IS NOT NULL THEN
2473 --OPEN get_curr_grade_cv FOR
2474 --SELECT lot_no,sublot_no FROM IC_LOTS_MST
2475 --WHERE item_id = l_item_id
2476 --AND lot_no = l_lot_no
2477 --AND sublot_no = l_sublot_no
2478 --AND qc_grade <> p_to_grade_code;
2479 --ELSE
2480 OPEN get_curr_grade_cv FOR
2481 SELECT lot_number,
2482 grade_code
2483 FROM mtl_lot_numbers
2484 WHERE inventory_item_id = l_inventory_item_id
2485 AND organization_id = l_organization_id
2486 AND ((parent_lot_number = l_parent_lot_number )
2487 OR ( lot_number = l_parent_lot_number
2488 AND parent_lot_number IS NULL) )
2489 AND grade_code <> p_to_grade_code;
2490
2491 LOOP
2492 --gml_sf_log('in first loop for grade');
2493 FETCH get_curr_grade_cv into get_curr_grade_rec ;
2494 EXIT WHEN get_curr_grade_cv%NOTFOUND ; -- exit when last row is fetched
2495
2496 --RLNAGARA B5668965 Removed the IF-ELSE cond below
2497 --when only parent_lot is given it means change the grade for all the child lots
2498 --irrespective of the current_grade(from_grade).
2499
2500 -- Bug 4165704: if from_grade_code changed, return to form
2501 -- IF (p_from_grade_code <> get_curr_grade_rec.grade_code) THEN
2502 --error out020
2503 --gml_sf_log('grade code is wrong');
2504 -- RAISE FND_API.G_EXC_ERROR;
2505 -- ELSE
2506 -- Bug 4165704: Changed from call to inventory posting to INV_GRADE_PKG.UPDATE_GRADE
2507 -- process data record
2508 -- , p_lot_number => l_lot_number
2509 INV_GRADE_PKG.UPDATE_GRADE
2510 ( p_organization_id => l_organization_id
2511 , p_update_method => 2 -- (Manual)
2512 , p_inventory_item_id => l_inventory_item_id
2513 , p_from_grade_code => p_from_grade_code
2514 , p_to_grade_code => p_to_grade_code
2515 , p_reason_id => p_reason_id
2516 , p_lot_number => get_curr_grade_rec.lot_number
2517 , x_Status => x_return_status
2518 , x_Message => l_data
2519 , p_update_from_mobile => 'N' -- default value
2520 , p_primary_quantity => NULL -- not sure what this value is yet
2521 , p_secondary_quantity => NULL ); --xxx not sure what this value is yet
2522
2523 IF x_return_status <> 'S'
2524 THEN
2525 RAISE FND_API.G_EXC_ERROR;
2526 END IF; -- x_return_status <> 'S'
2527 -- END IF; -- p_from_grade_code has not changed
2528
2529 -- Taken out with Inventory Convergence and replaced with above code
2530 -- l_trans_rec_grade.trans_type := 5;
2531 -- l_trans_rec_grade.item_no := l_item_no ;
2532 -- l_trans_rec_grade.lot_no := get_curr_grade_rec.lot_no;
2533 -- l_trans_rec_grade.co_code := l_co_code ;
2534 -- l_trans_rec_grade.orgn_code := l_orgn_code ;
2535 -- l_trans_rec_grade.qc_grade := p_to_grade_code ;
2536 -- l_trans_rec_grade.reason_code := p_reason_code ;
2537 -- l_trans_rec_grade.user_name := FND_GLOBAL.USER_NAME ;
2538 -- l_trans_rec_grade.trans_date := SYSDATE ;
2539 -- l_trans_rec_grade.trans_qty := NULL;
2540
2541 -- GMIPAPI.Inventory_Posting
2542 -- ( p_api_version => 3.0
2543 -- p_init_msg_list => FND_API.G_TRUE
2544 -- p_commit => FND_API.G_FALSE
2545 -- p_validation_level => FND_API.G_VALID_LEVEL_FULL
2546 -- p_qty_rec => l_trans_rec_grade
2547 -- x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
2548 -- x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
2549 -- x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
2550 -- x_return_status => x_return_status
2551 -- x_msg_count => l_count
2552 -- x_msg_data => l_data
2553 -- );
2554 END LOOP ;
2555 CLOSE get_curr_grade_cv;
2556 ELSE
2557 -- only update the one lot specified
2558 OPEN get_curr_grade_cv FOR
2559 SELECT lot_number,
2560 grade_code
2561 FROM mtl_lot_numbers
2562 WHERE inventory_item_id = l_inventory_item_id
2563 AND organization_id = l_organization_id
2564 AND lot_number = l_lot_number
2565 AND grade_code <> p_to_grade_code;
2566
2567 LOOP
2568 FETCH get_curr_grade_cv into get_curr_grade_rec ;
2569 EXIT WHEN get_curr_grade_cv%NOTFOUND ; -- exit when last row is fetched
2570 --RLNAGARA B5668965 Changed the exception from FND_API.G_EXC_ERROR to SAMPLE_DISP_CHANGED
2571 -- Bug 4165704: if from_grade_code changed, return to form
2572 IF (p_from_grade_code <> get_curr_grade_rec.grade_code) THEN
2573 --xxxerror out
2574 RAISE SAMPLE_DISP_CHANGED;
2575 ELSE
2576 -- Bug 4165704: Changed from call to inventory posting to INV_GRADE_PKG.UPDATE_GRADE
2577 -- process data record
2578 INV_GRADE_PKG.UPDATE_GRADE
2579 ( p_organization_id => l_organization_id
2580 , p_update_method => 2 -- (Manual)
2581 , p_inventory_item_id => l_inventory_item_id
2582 , p_from_grade_code => p_from_grade_code
2583 , p_to_grade_code => p_to_grade_code
2584 , p_reason_id => p_reason_id
2585 , p_lot_number => l_lot_number
2586 , x_Status => x_return_status
2587 , x_Message => l_data
2588 , p_update_from_mobile => 'N' -- default value
2589 , p_primary_quantity => NULL -- not sure what this value is yet
2590 , p_secondary_quantity => NULL ); --xxx not sure what this value is yet
2591
2592 IF x_return_status <> 'S' THEN
2593 RAISE FND_API.G_EXC_ERROR;
2594 END IF; -- x_return_status <> 'S'
2595
2596 END IF; -- (p_from_grade_code <> get_curr_grade_rec.grade_code) THEN
2597 END LOOP;
2598 END IF; -- l_parent_lot_number IS NOT NULL
2599 END IF; -- p_to_grade_code IS NOT NULL THEN
2600
2601 -- if grade update failed, then do not continue further.
2602 IF x_return_status <> 'S' THEN
2603 RETURN;
2604 END IF;
2605
2606 l_position := '030' ;
2607
2608 IF p_to_lot_status_id IS NOT NULL THEN
2609 -- Bug 4165704: For inventory convergence:
2610 -- 1. removed sublot, lot_id
2611 -- 2. changed lot_no to lot_number
2612 -- 3. use mtl_lot_numbers instead of ic_lots_mst
2613 -- 4. use parent_lot_number and changed logic so that if parent lot specified
2614 -- but lot number is not specified, all lots for that parent are updated
2615
2616 -- IF l_sublot_no IS NOT NULL THEN
2617 --OPEN get_curr_lot_status_cv FOR
2618 -- SELECT b.lot_no,a.whse_code,a.location
2619 -- FROM IC_LOCT_INV a , IC_LOTS_MST b
2620 -- WHERE a.item_id = l_inventory_item_id
2621 -- AND b.item_id = a.item_id
2622 -- AND b.lot_no = l_lot_number
2623 -- AND a.lot_status <> p_to_lot_status;
2624 --ELSE
2625
2626 IF l_parent_lot_number IS NOT NULL
2627 AND l_lot_number IS NULL THEN
2628
2629 -- update all lots for the parent lot specified
2630 /* OPEN get_curr_lot_status_cv FOR
2631 SELECT lot_number,
2632 status_id
2633 FROM MTL_LOT_NUMBERS
2634 WHERE inventory_item_id = l_inventory_item_id
2635 AND organization_id = l_organization_id
2636 AND ((parent_lot_number = l_parent_lot_number )
2637 OR ( lot_number = l_parent_lot_number
2638 AND parent_lot_number IS NULL) )
2639 AND status_id <> p_to_lot_status_id; */
2640
2641 --RLNAGARA B5668965 Commented the below cursor and added the next cursor by
2642 --removing the fix done for the bug 5006173
2643 /*
2644
2645 OPEN get_curr_lot_status_cv FOR
2646 SELECT lot_number,
2647 a.status_id
2648 FROM MTL_LOT_NUMBERS a, mtl_item_locations_kfv b -- 50061731
2649 WHERE a.inventory_item_id = l_inventory_item_id
2650 AND a.organization_id = l_organization_id
2651 AND ((a.parent_lot_number = l_parent_lot_number )
2652 OR ( a.lot_number = l_parent_lot_number
2653 AND a.parent_lot_number IS NULL) )
2654 AND a.status_id <> p_to_lot_status_id
2655
2656 AND b.organization_id = l_organization_id -- 50061731
2657 AND b.subinventory_code = nvl(l_subinventory, b.subinventory_code ) -- 50061731
2658 AND b.inventory_location_id = nvl(l_locator_id,b.inventory_location_id ); -- 50061731
2659 */
2660 OPEN get_curr_lot_status_cv FOR
2661 SELECT lot_number,
2662 a.status_id
2663 FROM MTL_LOT_NUMBERS a
2664 WHERE a.inventory_item_id = l_inventory_item_id
2665 AND a.organization_id = l_organization_id
2666 AND ((a.parent_lot_number = l_parent_lot_number )
2667 OR ( a.lot_number = l_parent_lot_number
2668 AND a.parent_lot_number IS NULL) )
2669 AND a.status_id <> p_to_lot_status_id;
2670
2671
2672 LOOP
2673 FETCH get_curr_lot_status_cv into get_curr_lot_status_rec ;
2674 EXIT WHEN get_curr_lot_status_cv%NOTFOUND ; -- exit when last row is fetched
2675
2676 --RLNAGARA B5668965 Removed the IF-ELSE cond below
2677 --when only parent_lot is given it means change the lot status for all the child lots
2678 --irrespective of the current_status(from_status).
2679
2680 -- Bug 4165704: if from_lot_status changed, return to form
2681 -- IF (p_from_lot_status_id <> get_curr_lot_status_rec.status_id ) THEN
2682 --mxxxerror out
2683 -- RAISE SAMPLE_DISP_CHANGED;
2684 --RAISE FND_API.G_EXC_ERROR;
2685 -- ELSE
2686
2687 -- Bug 4165704: replaced GMIPAPI.Inventory_Posting with Inv_Status_Pkg.update_status
2688 -- l_trans_rec_lot_status.trans_type := 4;
2689 -- l_trans_rec_lot_status.item_no := l_inventory_item_number ;
2690 -- l_trans_rec_lot_status.lot_no := get_curr_lot_status_rec.lot_no;
2691 -- l_trans_rec_lot_status.from_whse_code := get_curr_lot_status_rec.whse_code ;
2692 -- l_trans_rec_lot_status.from_location := get_curr_lot_status_rec.location;
2693 -- l_trans_rec_lot_status.orgn_code := l_orgn_code ;
2694 -- l_trans_rec_lot_status.lot_status := p_to_lot_status ;
2695 --- l_trans_rec_lot_status.reason_code := p_reason_code ;
2696 -- l_trans_rec_lot_status.user_name := FND_GLOBAL.USER_NAME ;
2697 -- l_trans_rec_lot_status.trans_date := SYSDATE ;
2698 -- l_trans_rec_lot_status.trans_qty := NULL;
2699 --GMIPAPI.Inventory_Posting
2700 --( p_api_version => 3.0
2701 --, p_init_msg_list => FND_API.G_TRUE
2702 --, p_commit => FND_API.G_FALSE
2703 --, p_validation_level => FND_API.G_VALID_LEVEL_FULL
2704 --, p_qty_rec => l_trans_rec_lot_status
2705 --, x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
2706 --, x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
2707 --, x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
2708 --, x_return_status => x_return_status
2709 --, x_msg_count => l_count
2710 --, x_msg_data => l_data );
2711
2712 Inv_Status_Pkg.update_status(
2713 p_update_method => 2 --(Manual)
2714 , p_organization_id => l_organization_id
2715 , p_inventory_item_id => l_inventory_item_id
2716 , p_sub_code => NULL
2717 , p_sub_status_id => NULL
2718 , p_sub_reason_id => NULL
2719 , p_locator_id => NULL
2720 , p_loc_status_id => NULL
2721 , p_loc_reason_id => NULL
2722 , p_from_lot_number => get_curr_lot_status_rec.lot_number --from_lot_number
2723 , p_to_lot_number => get_curr_lot_status_rec.lot_number --to_lot_number
2724 , p_lot_status_id => p_to_lot_status_id
2725 , p_lot_reason_id => p_reason_id
2726 , p_from_SN => NULL
2727 , p_to_SN => NULL
2728 , p_serial_status_id => NULL
2729 , p_serial_reason_id => NULL
2730 , x_Status => x_return_status
2731 , x_Message => l_data
2732 , p_update_from_mobile => 'N' --(DEFAULT 'Y')
2733 , p_grade_code => NULL --(DEFAULT NULL)
2734 , p_primary_onhand => NULL --(DEFAULT NULL)
2735 , p_secondary_onhand => NULL ); --(DEFAULT NULL)
2736
2737 IF x_return_status <> 'S'
2738 THEN
2739 RAISE FND_API.G_EXC_ERROR;
2740 END IF; -- x_return_status <> 'S'
2741
2742 -- END IF; -- (p_from_lot_status_id <> get_curr_lot_status_rec.status_id THEN
2743
2744 END LOOP ;
2745 CLOSE get_curr_lot_status_cv;
2746
2747 ELSIF l_lot_number IS NOT NULL THEN
2748
2749 OPEN c_lot_status(l_lot_number,
2750 l_inventory_item_id,
2751 l_organization_id);
2752 FETCH c_lot_status into get_curr_lot_status_rec ;
2753 CLOSE c_lot_status;
2754
2755 -- Bug 4165704: if from_lot_status changed, return to form
2756 IF (p_from_lot_status_id <> get_curr_lot_status_rec.status_id ) THEN
2757 --xxxerror out
2758 RAISE SAMPLE_DISP_CHANGED;
2759 --RAISE FND_API.G_EXC_ERROR;
2760 ELSE
2761
2762 -- update the one lot specified
2763 Inv_Status_Pkg.update_status(
2764 p_update_method => 2 --(Manual)
2765 , p_organization_id => l_organization_id
2766 , p_inventory_item_id => l_inventory_item_id
2767 , p_sub_code => NULL
2768 , p_sub_status_id => NULL
2769 , p_sub_reason_id => NULL
2770 , p_locator_id => NULL
2771 , p_loc_status_id => NULL
2772 , p_loc_reason_id => NULL
2773 , p_from_lot_number => l_lot_number --from_lot_number
2774 , p_to_lot_number => l_lot_number --to_lot_number
2775 , p_lot_status_id => p_to_lot_status_id
2776 , p_lot_reason_id => p_reason_id
2777 , p_from_SN => NULL
2778 , p_to_SN => NULL
2779 , p_serial_status_id => NULL
2780 , p_serial_reason_id => NULL
2781 , x_Status => x_return_status
2782 , x_Message => l_data
2783 , p_update_from_mobile => 'N' --(DEFAULT 'Y')
2784 , p_grade_code => NULL --(DEFAULT NULL)
2785 , p_primary_onhand => NULL --(DEFAULT NULL)
2786 , p_secondary_onhand => NULL ); --(DEFAULT NULL)
2787
2788 IF x_return_status <> 'S' THEN
2789 GMD_API_PUB.Log_Message('GMD_QM_INV_REASON_CODE');
2790 RAISE FND_API.G_EXC_ERROR;
2791 END IF; -- x_return_status <> 'S'
2792
2793 END IF; -- (p_from_lot_status_id <> get_curr_lot_status_rec.status_id THEN
2794
2795 END IF ; -- Test for parent lot not null but lot number null
2796 END IF; -- p_to_lot_status IS NOT NULL THEN
2797
2798 -- B2985070 Check if the batch id and step no is available
2799 IF p_to_qc_status IS NOT NULL AND
2800 l_batch_id IS NOT NULL AND
2801 l_step_no IS NOT NULL THEN
2802
2803 -- Bug # 4619570 Allow update of batch step quality status if batch is not closed
2804 /*SELECT batch_status INTO l_batch_status -- Bug # 4619570 Need to know if batch is closed
2805 FROM gme_batch_header
2806 WHERE batch_id = l_batch_id; */
2807
2808 -- Bug# 5440347
2809 SELECT step_status INTO l_step_status
2810 FROM gme_batch_steps
2811 WHERE batch_id = l_batch_id
2812 AND batchstep_no = l_step_no;
2813
2814
2815 --IF l_batch_status <> 4 THEN
2816 IF l_step_status < 3 THEN -- Bug# 5440347
2817 SELECT quality_status,rowid, batchstep_id INTO l_curr_qc_status,l_rowid, l_bstep_id -- Bug# 5440347 Added batchstep_id
2818 FROM GME_BATCH_STEPS
2819 WHERE BATCH_ID = l_batch_id
2820 AND batchstep_no = l_step_no
2821 FOR UPDATE OF quality_status NOWAIT ;
2822
2823 IF l_curr_qc_status = 3 THEN -- Results Required
2824 UPDATE GME_BATCH_STEPS
2825 SET quality_status = p_to_qc_status
2826 ,last_updated_by = FND_GLOBAL.USER_ID
2827 ,last_update_date = SYSDATE
2828 ,last_update_login = FND_GLOBAL.LOGIN_ID
2829 WHERE rowid = l_rowid ;
2830
2831 --Bug# 5440347 start
2832 -- Dont call complete_step if the qc status is Action Required
2833 IF p_to_qc_status = 5 THEN -- Action Required
2834 RETURN;
2835 END IF;
2836
2837 IF p_sample_id IS NOT NULL THEN
2838 OPEN cur_auto_complete_bstep_smpl;
2839 FETCH cur_auto_complete_bstep_smpl INTO l_auto_complete_bstep;
2840 IF cur_auto_complete_bstep_smpl%NOTFOUND THEN
2841 l_auto_complete_bstep := 'N';
2842 END IF;
2843 CLOSE cur_auto_complete_bstep_smpl;
2844 ELSE
2845 OPEN cur_auto_complete_bstep_comp;
2846 FETCH cur_auto_complete_bstep_comp INTO l_auto_complete_bstep;
2847 IF cur_auto_complete_bstep_comp%NOTFOUND THEN
2848 l_auto_complete_bstep := 'N';
2849 END IF;
2850 CLOSE cur_auto_complete_bstep_comp;
2851 END IF;
2852
2853 /*If auto_complete_batch_step flag is checked in the VR which is being used
2854 then only call the gme API to complete the batch step.*/
2855 IF l_auto_complete_bstep = 'Y' THEN
2856 p_batch_step_rec.batchstep_id := l_bstep_id;
2857
2858 IF (l_debug = 'Y') THEN
2859 gmd_debug.put_line('Before Calling Batch Step Completion API for BATCHSTEP_ID:'||l_bstep_id);
2860 END IF;
2861
2862 --call the batch step completion API.
2863 GME_API_PUB.complete_step(
2864 p_api_version => 2.0
2865 ,p_validation_level => gme_common_pvt.g_max_errors
2866 ,p_init_msg_list => fnd_api.g_false
2867 ,p_commit => fnd_api.g_false
2868 ,x_message_count => x_message_count
2869 ,x_message_list => x_message_list
2870 ,x_return_status => xx_return_status
2871 ,p_batch_step_rec => p_batch_step_rec
2872 ,p_batch_no => NULL
2873 ,p_org_code => NULL
2874 ,p_ignore_exception => fnd_api.g_false
2875 ,p_override_quality => fnd_api.g_false
2876 ,p_validate_flexfields => fnd_api.g_false
2877 ,x_batch_step_rec => x_batch_step_rec
2878 ,x_exception_material_tbl => l_exception_material_tbl);
2879 --After returning from the API we are not handling any exceptions. Any exceptions will be written in the Debug Log
2880
2881 IF (l_debug = 'Y') THEN
2882 gmd_debug.put_line('Returned from Batch Step Completion Call');
2883 gmd_debug.put_line('x_return_status = '||xx_return_status);
2884 gmd_debug.put_line('x_batch_step_rec.batch_id = '||TO_CHAR(x_batch_step_rec.batch_id));
2885 gmd_debug.put_line('x_batch_step_rec.batchstep_id = '||TO_CHAR(x_batch_step_rec.batchstep_id));
2886 gmd_debug.put_line('x_batch_step_rec.batchstep_no = '||TO_CHAR(x_batch_step_rec.batchstep_no));
2887 gmd_debug.put_line('x_batch_step_rec.actual_start_date = '||TO_CHAR(x_batch_step_rec.actual_start_date,'DD-MON-YYYY HH24:MI:SS'));
2888 gmd_debug.put_line('x_batch_step_rec.actual_cmplt_date = '||TO_CHAR(x_batch_step_rec.actual_cmplt_date,'DD-MON-YYYY HH24:MI:SS'));
2889 gmd_debug.put_line('x_batch_step_rec.step_status = '||TO_CHAR(x_batch_step_rec.step_status));
2890 END IF;
2891
2892 END IF; --l_auto_complete_bstep = 'Y'
2893 -- Bug# 5440347 end
2894
2895 END IF; -- l_curr_qc_status = 3
2896 END IF; -- l_step_status < 3
2897
2898 END IF;
2899
2900 EXCEPTION
2901 WHEN SAMPLE_DISP_CHANGED THEN
2902 --RLNAGARA B5668965 Changed the message from GMD_SMPL_DISP_CHANGE to GMD_QM_CURRENT_LOT_VALUE_CHANG
2903 gmd_api_pub.log_message('GMD_QM_CURRENT_LOT_VALUE_CHANG','PACKAGE','GMD_SAMPLES_GRP.UPDATE_SAMPLE_COMP_DISP');
2904 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2905 x_return_status := FND_API.G_RET_STS_ERROR ;
2906 WHEN REQ_FIELDS_MISSING THEN
2907 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SAMPLES_GRP.UPDATE_LOT_GRADE_BATCH');
2908 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2909 x_return_status := FND_API.G_RET_STS_ERROR ;
2910 WHEN INVALID_SAMPLE THEN
2911 gmd_api_pub.log_message('GMD_QM_INVALID_SAMPLE');
2912 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2913 x_return_status := FND_API.G_RET_STS_ERROR ;
2914 WHEN INVALID_QC_STATUS THEN
2915 gmd_api_pub.log_message('GME_INV_STEP_QUALITY_STATUS');
2916 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2917 x_return_status := FND_API.G_RET_STS_ERROR ;
2918
2919 WHEN RECORD_LOCK THEN
2920 GMD_API_PUB.Log_Message('GMD_IC_LOTS_CPG_LOCKED');
2921 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2922 x_return_status := FND_API.G_RET_STS_ERROR ;
2923
2924 WHEN OTHERS THEN
2925 IF get_curr_grade_cv%ISOPEN THEN
2926 CLOSE get_curr_grade_cv;
2927 END IF;
2928 IF get_curr_lot_status_cv%ISOPEN THEN
2929 CLOSE get_curr_lot_status_cv;
2930 END IF;
2931 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SAMPLES_GRP.UPDATE_LOT_GRADE_BATCH','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
2932 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
2933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2934 END update_lot_grade_batch ;
2935
2936
2937
2938 --Start of comments
2939 --+========================================================================+
2940 --| API Name : check_for_null_and_fks_in_smpl |
2941 --| TYPE : Group |
2942 --| Notes : This procedure checks for NULL and Foreign Key |
2943 --| constraints for the required filed in the Sample |
2944 --| record. |
2945 --| |
2946 --| If everything is fine then 'S' is returned in the |
2947 --| parameter - x_return_status otherwise error message |
2948 --| is logged and error status - E or U returned |
2949 --| |
2950 --| HISTORY |
2951 --| Chetan Nagar 26-Jul-2002 Created. |
2952 --| Chetan Nagar 11-Nov-2002 |
2953 --| Commenting code to check sampling_event_id IS NOT NULL as this |
2954 --| routine gets called only from the Public API and the Public API |
2955 --| has not yet determined the Samling Event ID. |
2956 --| |
2957 --| The check will be done by the public API itself. |
2958 --| |
2959 --| Uday Phadtare Bug2982490 06-Aug-2003. Close cursor c_item_lot |
2960 --| instead of c_whse. |
2961 --| |
2962 --| Bug 4165704: changes for inventory convergence include organization|
2963 --| inventory_item_id, and lots. |
2964 --| Bug 4640143: added material_detail_id to samples
2965 --| Peter Lowe FP of Bug # 4359797 - 4619570 |
2966 --| if profile GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y then allow |
2967 --| retrieval of closed batches
2968 --===========================================================================+
2969 -- Bug 4640143: added cursor
2970 -- End of comments
2971
2972 PROCEDURE check_for_null_and_fks_in_smpl
2973 (
2974 p_sample IN gmd_samples%ROWTYPE
2975 , x_return_status OUT NOCOPY VARCHAR2
2976 ) IS
2977
2978 -- Bug 4165704: changed for inventory convergence
2979 CURSOR c_orgn (p_organization_id NUMBER) IS
2980 SELECT 1
2981 FROM mtl_parameters m,
2982 org_access_view v
2983 WHERE v.organization_id = m.organization_id
2984 AND m.organization_id = p_organization_id
2985 AND m. process_enabled_flag = 'Y' ;
2986
2987 CURSOR c_sampler (p_orgn_code VARCHAR2 , p_sampler_id NUMBER) IS
2988 SELECT 1
2989 FROM FND_USER
2990 WHERE user_id = p_sampler_id;
2991
2992 -- Bug 4165704: changed for inventory convergence
2993 CURSOR c_lab_orgn (p_organization_id NUMBER) IS
2994 SELECT 1
2995 FROM mtl_parameters m,
2996 gmd_quality_config g ,
2997 org_access_view v
2998 WHERE g.quality_lab_ind = 'Y'
2999 AND g.organization_id = m.organization_id
3000 AND v.organization_id = m.organization_id
3001 AND m.organization_id = p_organization_id
3002 AND m. process_enabled_flag = 'Y' ;
3003
3004 -- Bug 4165704: changed for inventory convergence
3005 CURSOR c_item(p_inventory_item_id NUMBER, p_organization_id NUMBER) IS
3006 SELECT 1
3007 FROM mtl_system_items_b_kfv
3008 WHERE organization_id = p_organization_id
3009 AND process_quality_enabled_flag = 'Y'
3010 AND inventory_item_id = p_inventory_item_id;
3011
3012 CURSOR c_sampling_event(p_sampling_event_id NUMBER) IS
3013 SELECT 1
3014 FROM gmd_sampling_events
3015 WHERE sampling_event_id = p_sampling_event_id
3016 ;
3017
3018 -- Bug 4165704: changed for inventory convergence
3019 CURSOR c_subinventory IS
3020 SELECT 1
3021 FROM mtl_secondary_inventories s
3022 WHERE s.organization_id = p_sample.organization_id
3023 AND s.secondary_inventory_name = p_sample.subinventory;
3024
3025 -- Bug 4165704: changed for inventory convergence
3026 CURSOR c_locator IS
3027 SELECT 1
3028 FROM mtl_item_locations_kfv
3029 WHERE organization_id = p_sample.organization_id
3030 AND subinventory_code = p_sample.subinventory
3031 AND inventory_location_id = p_sample.locator_id;
3032
3033 -- Bug 4165704: changed for inventory convergence
3034 CURSOR c_item_lot IS
3035 SELECT 1
3036 FROM mtl_lot_numbers
3037 WHERE organization_id = p_sample.organization_id
3038 AND inventory_item_id = p_sample.inventory_item_id
3039 AND lot_number = p_sample.lot_number;
3040
3041 -- Bug 4165704: removed for inventory convergence
3042 --CURSOR c_item_sublot IS
3043
3044 CURSOR c_batch IS
3045 SELECT 1
3046 FROM gme_batch_header bh
3047 WHERE bh.batch_id = p_sample.batch_id
3048 AND bh.batch_type = 0 -- Only Batches, No FPOs
3049 AND ( ( bh.batch_status IN (1,2) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') ) -- Bug # 4619570 Pending or WIP Batches Only
3050 OR ( bh.batch_status IN (1,2, 4 ) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) -- Pending or WIP Or Closed Batches Only
3051 AND exists -- Only Batches with Spec Item in it
3052 (SELECT 1
3053 FROM gme_material_details md
3054 WHERE md.batch_id = bh.batch_id
3055 AND md.inventory_item_id = p_sample.inventory_item_id)
3056 ;
3057
3058 CURSOR c_recipe_id IS
3059 SELECT 1
3060 FROM gmd_recipes r, gmd_status s
3061 WHERE r.recipe_status = s.status_code
3062 AND r.recipe_id = p_sample.recipe_id
3063 AND s.status_type <> '1000'
3064 AND r.delete_mark = 0
3065 AND exists
3066 (SELECT 1
3067 FROM fm_matl_dtl md
3068 WHERE md.formula_id = r.formula_id
3069 AND md.inventory_item_id = p_sample.inventory_item_id)
3070 ;
3071
3072
3073 CURSOR c_formulaline_id IS
3074 SELECT 1
3075 FROM fm_form_mst f, fm_matl_dtl md
3076 WHERE f.formula_id = md.formula_id
3077 AND f.formula_id = nvl(p_sample.formula_id, f.formula_id)
3078 AND md.formulaline_id = p_sample.formulaline_id
3079 AND md.inventory_item_id = p_sample.inventory_item_id
3080 AND f.delete_mark = 0
3081 ;
3082
3083 -- Bug 4640143: added cursor
3084 CURSOR c_material_detail_id IS
3085 SELECT 1
3086 FROM gme_material_details
3087 WHERE inventory_item_id = p_sample.inventory_item_id
3088 AND batch_id = p_sample.batch_id
3089 AND organization_id = p_sample.organization_id
3090 AND material_detail_id = p_sample.material_detail_id;
3091
3092
3093 CURSOR c_batchstep IS
3094 SELECT 1
3095 FROM gme_batch_steps
3096 WHERE batch_id = p_sample.batch_id
3097 AND batchstep_no = p_sample.step_no
3098 ;
3099
3100 -- Bug 4165704: changed fm_rout_dtl for gmd_routings_b
3101 CURSOR c_routingstep IS
3102 SELECT 1
3103 FROM gmd_routings_b
3104 WHERE routing_id = p_sample.routing_id;
3105 --FROM fm_rout_dtl
3106 --WHERE routing_id = p_sample.routing_id
3107 --AND routingstep_no = p_sample.step_no
3108
3109
3110 CURSOR c_oprn IS
3111 SELECT 1
3112 FROM gmd_operations
3113 WHERE oprn_id = p_sample.oprn_id
3114 AND delete_mark = 0
3115 ;
3116
3117 CURSOR c_cust IS
3118 SELECT 1
3119 FROM hz_cust_accounts_all
3120 WHERE cust_account_id = p_sample.cust_id
3121 ;
3122
3123 -- Bug 4165704: took this check out since gl_plcy_mst no longer exists
3124 --CURSOR c_org IS
3125 --SELECT 1
3126 --FROM gl_plcy_mst
3127 --WHERE org_id = p_sample.org_id ;
3128
3129
3130 CURSOR c_ship_to IS
3131 SELECT 1
3132 FROM hz_cust_acct_sites_all a,
3133 hz_cust_site_uses_all s,
3134 hz_cust_accounts_all c
3135 WHERE a.cust_acct_site_id = s.cust_acct_site_id
3136 AND a.org_id = s.org_id
3137 AND a.cust_account_id = c.cust_account_id
3138 AND c.cust_account_id = p_sample.cust_id
3139 AND s.site_use_code = 'SHIP_TO'
3140 AND s.org_id = p_sample.org_id
3141 AND s.site_use_id = p_sample.ship_to_site_id
3142 ;
3143
3144 CURSOR c_order IS
3145 SELECT 1
3146 FROM oe_order_headers_all h,
3147 oe_transaction_types_tl t
3148 WHERE h.sold_to_org_id = p_sample.cust_id
3149 AND h.org_id = p_sample.org_id
3150 AND h.header_id = p_sample.order_id
3151 AND h.cancelled_flag <> 'Y'
3152 AND h.order_type_id = t.transaction_type_id
3153 AND t.language = USERENV('LANG')
3154 ;
3155
3156 CURSOR c_order_line IS
3157 SELECT 1
3158 FROM oe_order_lines_all l,
3159 mtl_system_items_b m,
3160 mtl_parameters mp,
3161 ic_item_mst i
3162 WHERE l.header_id = p_sample.order_id
3163 AND l.line_id = p_sample.order_line_id
3164 AND l.ship_to_org_id = p_sample.ship_to_site_id
3165 AND m.inventory_item_id = l.inventory_item_id
3166 AND m.organization_id = l.ship_from_org_id
3167 AND mp.organization_id = m.organization_id
3168 AND mp.process_enabled_flag = 'Y'
3169 AND i.item_id = p_sample.inventory_item_id
3170 AND m.segment1 = i.item_no
3171 AND l.cancelled_flag <> 'Y'
3172 ;
3173
3174 CURSOR c_supplier IS
3175 SELECT 1
3176 FROM po_vendors v
3177 WHERE v.vendor_id = p_sample.supplier_id
3178 AND v.enabled_flag = 'Y'
3179 AND sysdate between nvl(v.start_date_active, sysdate-1)
3180 and nvl(v.end_date_active, sysdate+1)
3181 ;
3182
3183 CURSOR c_po IS
3184 SELECT 1
3185 FROM po_headers_all
3186 WHERE vendor_id = p_sample.supplier_id
3187 AND po_header_id = p_sample.po_header_id
3188 ;
3189
3190 -- Bug 4165704: removed ic_item_mst_b test
3191 CURSOR c_po_line IS
3192 SELECT 1
3193 FROM po_lines_all l
3194 WHERE l.po_header_id = p_sample.po_header_id
3195 AND l.po_line_id = p_sample.po_line_id
3196 AND EXISTS
3197 (SELECT 1
3198 FROM mtl_system_items_b msi
3199 WHERE msi.inventory_item_id = l.item_id
3200 AND msi.inventory_item_id = p_sample.inventory_item_id) ;
3201
3202 dummy NUMBER;
3203
3204 BEGIN
3205
3206 -- Initialize API return status to success
3207 x_return_status := FND_API.G_RET_STS_SUCCESS;
3208
3209 -- Orgn Code
3210 IF (p_sample.organization_id IS NULL) THEN
3211 GMD_API_PUB.Log_Message('GMD_ORGN_CODE_REQD');
3212 RAISE FND_API.G_EXC_ERROR;
3213 ELSE
3214 -- Check that orgn Code exist in SY_ORGN_MST
3215 OPEN c_orgn(p_sample.organization_id);
3216 FETCH c_orgn INTO dummy;
3217 IF c_orgn%NOTFOUND THEN
3218 CLOSE c_orgn;
3219 GMD_API_PUB.Log_Message('GMD_ORGN_CODE_NOT_FOUND',
3220 'ORGN', p_sample.lab_organization_id);
3221 RAISE FND_API.G_EXC_ERROR;
3222 END IF;
3223 CLOSE c_orgn;
3224 END IF;
3225
3226 -- Sampler Id Validation
3227
3228 IF (p_sample.sampler_id IS NULL ) THEN
3229 GMD_API_PUB.Log_Message('GMD_SAMPLER_ID_REQD');
3230 RAISE FND_API.G_EXC_ERROR;
3231 ELSE
3232 -- Check that orgn Code exist in SY_ORGN_MST
3233 OPEN c_sampler(p_sample.organization_id, p_sample.sampler_id);
3234 FETCH c_sampler INTO dummy;
3235 IF c_sampler%NOTFOUND THEN
3236 GMD_API_PUB.Log_Message('GMD_SAMPLER_ID_NOTFOUND',
3237 'SAMPLER', p_sample.sampler_id);
3238 RAISE FND_API.G_EXC_ERROR;
3239 CLOSE c_sampler;
3240 END IF;
3241 CLOSE c_sampler;
3242 END IF;
3243
3244
3245 -- Sample No
3246 IF (ltrim(rtrim(p_sample.sample_no)) IS NULL) THEN
3247 GMD_API_PUB.Log_Message('GMD_SAMPLE_NUMBER_REQD');
3248 RAISE FND_API.G_EXC_ERROR;
3249 END IF;
3250
3251 -- Sample Source
3252 IF (p_sample.source IS NULL OR
3253 (NOT (p_sample.source in ('I', 'W', 'C', 'S','L','R','T')))
3254 ) THEN
3255 -- Now, what is the source of this sample? Where did it come from?
3256 GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
3257 RAISE FND_API.G_EXC_ERROR;
3258 END IF;
3259
3260 -- QC Lab Orgn Code
3261 IF (p_sample.lab_organization_id IS NULL) THEN
3262 GMD_API_PUB.Log_Message('GMD_QC_LAB_ORGN_CODE_REQD');
3263 RAISE FND_API.G_EXC_ERROR;
3264 ELSE
3265 -- Check that QC Lab Orgn Code exist in SY_ORGN_MST
3266 OPEN c_lab_orgn(p_sample.lab_organization_id);
3267 FETCH c_lab_orgn INTO dummy;
3268 IF c_lab_orgn%NOTFOUND THEN
3269 CLOSE c_lab_orgn;
3270 GMD_API_PUB.Log_Message('GMD_QC_LAB_ORGN_CODE_NOT_FOUND',
3271 'ORGN', p_sample.lab_organization_id);
3272 RAISE FND_API.G_EXC_ERROR;
3273 END IF;
3274 CLOSE c_lab_orgn;
3275 END IF;
3276
3277 -- Sample Disposition
3278 -- Chetan Nagar 13-Nov-2002 Removed '2I', '4A', '5AV', '6RJ' from the
3279 -- valid sample_disposition list.
3280 IF (p_sample.sample_disposition IS NULL OR
3281 (NOT (p_sample.sample_disposition in ('0RT', '1P')))
3282 ) THEN
3283 -- Now, what is the disposition of this sample?
3284 GMD_API_PUB.Log_Message('GMD_SAMPLE_DISPOSITION_INVALID');
3285 RAISE FND_API.G_EXC_ERROR;
3286 END IF;
3287
3288 -- Item ID
3289 IF (p_sample.inventory_item_id IS NULL) and
3290 (p_sample.sample_type = 'M' ) THEN
3291 GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_REQD');
3292 RAISE FND_API.G_EXC_ERROR;
3293 ELSE
3294 -- Get the Item No
3295 OPEN c_item(p_sample.inventory_item_id, p_sample.organization_id);
3296 FETCH c_item INTO dummy;
3297 IF c_item%NOTFOUND THEN
3298 CLOSE c_item;
3299 GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
3300 RAISE FND_API.G_EXC_ERROR;
3301 END IF;
3302 CLOSE c_item;
3303 END IF;
3304
3305 -- Sampling Event ID
3306 -- Chetan Nagar 13-Nov-2002
3307 -- Commenting following code as this routine gets called only from
3308 -- the Public API and the Public API has not yet determined the
3309 -- Samling Event ID. The check will be done by the public API itself.
3310 /** COMMENT START **
3311 IF (p_sample.sampling_event_id IS NOT NULL) THEN
3312 OPEN c_sampling_event(p_sample.sampling_event_id);
3313 FETCH c_sampling_event INTO dummy;
3314 IF c_sampling_event%NOTFOUND THEN
3315 CLOSE c_sampling_event;
3316 GMD_API_PUB.Log_Message('GMD_SAMPLING_EVENT_NOT_FOUND');
3317 RAISE FND_API.G_EXC_ERROR;
3318 END IF;
3319 CLOSE c_sampling_event;
3320 END IF;
3321 ** COMMENT END **/
3322
3323 -- Sample Qty
3324 IF (p_sample.sample_qty IS NULL) THEN
3325 GMD_API_PUB.Log_Message('GMD_SAMPLE_QTY_REQD');
3326 RAISE FND_API.G_EXC_ERROR;
3327 END IF;
3328
3329 -- Sample UOM
3330 IF (p_sample.sample_qty_uom IS NULL) THEN
3331 GMD_API_PUB.Log_Message('GMD_SAMPLE_UOM_REQD');
3332 RAISE FND_API.G_EXC_ERROR;
3333 END IF;
3334
3335 -- Lot No
3336 IF (p_sample.lot_number IS NOT NULL) THEN
3337 OPEN c_item_lot;
3338 FETCH c_item_lot INTO dummy;
3339 IF c_item_lot%NOTFOUND THEN
3340 --Uday Phadtare Bug2982490 changed CLOSE c_whse to CLOSE c_item_lot
3341 CLOSE c_item_lot;
3342 GMD_API_PUB.Log_Message('GMD_ITEM_LOT_NOT_FOUND',
3343 'LOT_NO', p_sample.lot_number);
3344 RAISE FND_API.G_EXC_ERROR;
3345 END IF;
3346 CLOSE c_item_lot;
3347 END IF;
3348
3349
3350 IF (p_sample.source = 'I') THEN
3351 -- Sample is from source 'Inventory' so check only
3352 -- those parameters related to Inventory
3353
3354 -- Whse Code
3355 IF (p_sample.subinventory IS NOT NULL) THEN
3356 -- Check that Whse Code exist in IC_WHSE_MST
3357 OPEN c_subinventory;
3358 FETCH c_subinventory INTO dummy;
3359 IF c_subinventory%NOTFOUND THEN
3360 CLOSE c_subinventory;
3361 GMD_API_PUB.Log_Message('GMD_SPEC_WHSE_NOT_FOUND',
3362 'WHSE', p_sample.subinventory);
3363 RAISE FND_API.G_EXC_ERROR;
3364 END IF;
3365 CLOSE c_subinventory;
3366 END IF;
3367
3368 -- Location
3369 IF (p_sample.locator_id IS NOT NULL) THEN
3370 -- Check that Location exist in table
3371 OPEN c_locator;
3372 FETCH c_locator INTO dummy;
3373 IF c_locator%NOTFOUND THEN
3374 CLOSE c_locator;
3375 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
3376 'LOCATION', p_sample.locator_id);
3377 RAISE FND_API.G_EXC_ERROR;
3378 END IF;
3379 CLOSE c_locator;
3380 END IF;
3381
3382 END IF; -- Validation for Inventory Sample
3383
3384 IF (p_sample.source = 'W') THEN
3385 -- Sample is from source 'WIP' so check only
3386 -- those parameters related to WIP
3387
3388 -- For WIP sample, at least Batch No or Recipe ID is required
3389 IF (p_sample.batch_id IS NULL AND p_sample.recipe_id IS NULL) THEN
3390 GMD_API_PUB.Log_Message('GMD_NO_WIP_PARAM');
3391 RAISE FND_API.G_EXC_ERROR;
3392 END IF;
3393
3394 -- Batch ID is valid
3395
3396 IF (p_sample.batch_id IS NOT NULL) THEN
3397 OPEN c_batch;
3398 FETCH c_batch INTO dummy;
3399 IF c_batch%NOTFOUND THEN
3400 CLOSE c_batch;
3401 GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
3402 RAISE FND_API.G_EXC_ERROR;
3403 END IF;
3404 CLOSE c_batch;
3405 END IF;
3406
3407 -- Recipe is valid (Check only if Batch was not specified)
3408 IF (p_sample.batch_id IS NULL AND p_sample.recipe_id IS NOT NULL) THEN
3409 OPEN c_recipe_id;
3410 FETCH c_recipe_id INTO dummy;
3411 IF c_recipe_id%NOTFOUND THEN
3412 CLOSE c_recipe_id;
3413 GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
3414 RAISE FND_API.G_EXC_ERROR;
3415 END IF;
3416 CLOSE c_recipe_id;
3417 END IF;
3418
3419 -- Formula Line is valid
3420 -- Bug 4640143: added batch id to test
3421 IF (p_sample.formula_id IS NOT NULL AND
3422 p_sample.batch_id IS NULL AND
3423 p_sample.formulaline_id IS NOT NULL) THEN
3424 OPEN c_formulaline_id;
3425 FETCH c_formulaline_id INTO dummy;
3426 IF c_formulaline_id%NOTFOUND THEN
3427 CLOSE c_formulaline_id;
3428 GMD_API_PUB.Log_Message('GMD_FORMULA_LINE_NOT_FOUND');
3429 RAISE FND_API.G_EXC_ERROR;
3430 END IF;
3431 CLOSE c_formulaline_id;
3432 END IF;
3433
3434 -- Material Detail is valid
3435 -- Bug 4640143: added material detail id to samples
3436 IF (p_sample.batch_id IS NOT NULL AND
3437 p_sample.material_detail_id IS NOT NULL) THEN
3438 OPEN c_material_detail_id;
3439 FETCH c_material_detail_id INTO dummy;
3440 IF c_material_detail_id%NOTFOUND THEN
3441 CLOSE c_material_detail_id;
3442 GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
3443 RAISE FND_API.G_EXC_ERROR;
3444 END IF;
3445 CLOSE c_material_detail_id;
3446 END IF;
3447
3448 -- Step is valid
3449 IF (p_sample.batch_id IS NOT NULL AND p_sample.step_no IS NOT NULL) THEN
3450 -- Step No is from Batch
3451 OPEN c_batchstep;
3452 FETCH c_batchstep INTO dummy;
3453 IF c_batchstep%NOTFOUND THEN
3454 CLOSE c_batchstep;
3455 GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
3456 RAISE FND_API.G_EXC_ERROR;
3457 END IF;
3458 CLOSE c_batchstep;
3459 ELSIF (p_sample.routing_id IS NOT NULL AND p_sample.step_no IS NOT NULL) THEN
3460 -- Step No is from Routing
3461 OPEN c_routingstep;
3462 FETCH c_routingstep INTO dummy;
3463 IF c_routingstep%NOTFOUND THEN
3464 CLOSE c_routingstep;
3465 GMD_API_PUB.Log_Message('GMD_ROUTING_STEP_NOT_FOUND');
3466 RAISE FND_API.G_EXC_ERROR;
3467 END IF;
3468 CLOSE c_routingstep;
3469 END IF;
3470
3471 -- Operation is valid (check only if step is not specified, because
3472 -- otherwise it will default from the step chosen.)
3473 IF (p_sample.step_id IS NULL AND p_sample.oprn_id IS NOT NULL) THEN
3474 OPEN c_oprn;
3475 FETCH c_oprn INTO dummy;
3476 IF c_oprn%NOTFOUND THEN
3477 CLOSE c_oprn;
3478 GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
3479 RAISE FND_API.G_EXC_ERROR;
3480 END IF;
3481 CLOSE c_oprn;
3482 END IF;
3483
3484 END IF; -- Validation for Customer Sample
3485
3486 IF (p_sample.source = 'C') THEN
3487 -- Sample is from source 'Customer' so check only
3488 -- those parameters related to Customer
3489
3490 -- Customer
3491 IF (p_sample.cust_id IS NULL) THEN
3492 GMD_API_PUB.Log_Message('GMD_CUSTOMER_REQD');
3493 RAISE FND_API.G_EXC_ERROR;
3494 ELSE
3495 OPEN c_cust;
3496 FETCH c_cust INTO dummy;
3497 IF c_cust%NOTFOUND THEN
3498 CLOSE c_cust;
3499 GMD_API_PUB.Log_Message('GMD_CUSTOMER_NOT_FOUND');
3500 RAISE FND_API.G_EXC_ERROR;
3501 END IF;
3502 CLOSE c_cust;
3503 END IF;
3504
3505 -- Org ID
3506 --IF (p_sample.org_id IS NOT NULL) THEN
3507 -- OPEN c_org;
3508 -- FETCH c_org INTO dummy;
3509 -- IF c_cust%NOTFOUND THEN
3510 -- CLOSE c_org;
3511 -- GMD_API_PUB.Log_Message('GMD_ORG_NOT_FOUND');
3512 -- RAISE FND_API.G_EXC_ERROR;
3513 -- END IF;
3514 -- CLOSE c_org;
3515 --END IF;
3516
3517 -- Ship To
3518 IF (p_sample.ship_to_site_id IS NOT NULL) THEN
3519 OPEN c_ship_to;
3520 FETCH c_ship_to INTO dummy;
3521 IF c_ship_to%NOTFOUND THEN
3522 CLOSE c_ship_to;
3523 GMD_API_PUB.Log_Message('GMD_SHIP_TO_NOT_FOUND');
3524 RAISE FND_API.G_EXC_ERROR;
3525 END IF;
3526 CLOSE c_ship_to;
3527 END IF;
3528
3529 -- Order ID
3530 IF (p_sample.order_id IS NOT NULL) THEN
3531 OPEN c_order;
3532 FETCH c_order INTO dummy;
3533 IF c_order%NOTFOUND THEN
3534 CLOSE c_order;
3535 GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
3536 RAISE FND_API.G_EXC_ERROR;
3537 END IF;
3538 CLOSE c_order;
3539 END IF;
3540
3541 -- Order Line ID
3542 IF (p_sample.order_line_id IS NOT NULL) THEN
3543 OPEN c_order_line;
3544 FETCH c_order_line INTO dummy;
3545 IF c_order_line%NOTFOUND THEN
3546 CLOSE c_order_line;
3547 GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
3548 RAISE FND_API.G_EXC_ERROR;
3549 END IF;
3550 CLOSE c_order_line;
3551 END IF;
3552
3553 END IF; -- Validation for Customer Sample
3554
3555 IF (p_sample.source = 'S') THEN
3556 -- Sample is from source 'Supplier' so check only
3557 -- those parameters related to Supplier
3558
3559 -- Supplier
3560 IF (p_sample.supplier_id IS NULL) THEN
3561 GMD_API_PUB.Log_Message('GMD_SUPPLIER_REQD');
3562 RAISE FND_API.G_EXC_ERROR;
3563 ELSIF (p_sample.supplier_id IS NOT NULL) THEN
3564 OPEN c_supplier;
3565 FETCH c_supplier INTO dummy;
3566 IF c_supplier%NOTFOUND THEN
3567 CLOSE c_supplier;
3568 GMD_API_PUB.Log_Message('GMD_SUPPLIER_NOT_FOUND');
3569 RAISE FND_API.G_EXC_ERROR;
3570 END IF;
3571 CLOSE c_supplier;
3572 END IF;
3573
3574 -- PO
3575 IF (p_sample.po_header_id IS NOT NULL) THEN
3576 OPEN c_po;
3577 FETCH c_po INTO dummy;
3578 IF c_po%NOTFOUND THEN
3579 CLOSE c_po;
3580 GMD_API_PUB.Log_Message('GMD_PO_NOT_FOUND');
3581 RAISE FND_API.G_EXC_ERROR;
3582 END IF;
3583 CLOSE c_po;
3584 END IF;
3585
3586 -- PO Line
3587 IF (p_sample.po_line_id IS NOT NULL) THEN
3588 OPEN c_po_line;
3589 FETCH c_po_line INTO dummy;
3590 IF c_po_line%NOTFOUND THEN
3591 CLOSE c_po_line;
3592 GMD_API_PUB.Log_Message('GMD_PO_LINE_NOT_FOUND');
3593 RAISE FND_API.G_EXC_ERROR;
3594 END IF;
3595 CLOSE c_po_line;
3596 END IF;
3597
3598 END IF; -- Validation for Supplier Sample
3599
3600
3601 EXCEPTION
3602 WHEN FND_API.G_EXC_ERROR THEN
3603 x_return_status := FND_API.G_RET_STS_ERROR ;
3604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3606 WHEN OTHERS THEN
3607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3608
3609 END check_for_null_and_fks_in_smpl;
3610
3611
3612 --Start of comments
3613 --+========================================================================+
3614 --| API Name : GMIGAPI_format |
3615 --| TYPE : Group |
3616 --| Notes : This function returns the format of GMAGAPI which |
3617 --| is used by the inventory transaction in the samples |
3618 --| form gmdqsmpl.fmb |
3619 --| |
3620 --| HISTORY |
3621 --| S. Feinstein 22-SEQ-2002 Created. |
3622 --| Bug 4165704: taken out for Inventory Convergence |
3623 --| |
3624 --+========================================================================+
3625 --FUNCTION GMIGAPI_format RETURN GMIGAPI.qty_rec_typ IS
3626 --l_temp GMIGAPI.qty_rec_typ;
3627 --BEGIN
3628 -- return l_temp;
3629 --END GMIGAPI_format;
3630
3631 --+========================================================================+
3632 --| API Name : create_inv_txn |
3633 --| TYPE : Group |
3634 --| Notes : This procedure creates Inventory transaction for |
3635 --| sample quantity for all sample types except WIP sample. |
3636 --| |
3637 --| If everything is fine then 'S' is returned in the |
3638 --| parameter - x_return_status otherwise error message |
3639 --| is logged and error status - E or U returned |
3640 --| |
3641 --| HISTORY |
3642 --| Chetan Nagar 13-Nov-2002 Created. |
3643 --| This procedure has been transferred from the form. |
3644 --| Bug 4165704: updated for Inventory Convergence |
3645 --| |
3646 --| RLNAGARA 09-Mar-2006 Bug 4753039 Added IF cond. |
3647 --| RAGSRIVA 29-Jun-2006 Bug 5332105 Modified cursor fetch_subinventory_loc|
3648 --| and changed length of l_msg_data from 200 to 2000 |
3649 --+========================================================================+
3650
3651 PROCEDURE create_inv_txn
3652 ( p_sample IN GMD_SAMPLES%ROWTYPE
3653 , p_user_name IN NUMBER
3654 , x_return_status OUT NOCOPY VARCHAR2
3655 , x_message_count OUT NOCOPY NUMBER
3656 , x_message_data OUT NOCOPY VARCHAR2
3657 )
3658 IS
3659 -- Bug# 5332105
3660 -- Added additional where clause destination_type_code = 'INVENTORY' and rt.transaction_type = 'DELIVER'
3661 -- since subinventory and locator information exists only for inventory transaction and not for RECEIVING
3662 CURSOR fetch_subinventory_loc IS
3663 SELECT DISTINCT subinventory,
3664 locator_id
3665 FROM rcv_transactions rt
3666 WHERE rt.shipment_header_id = p_sample.receipt_id
3667 AND rt.shipment_line_id = p_sample.receipt_line_id
3668 AND rt.destination_type_code = 'INVENTORY'
3669 AND rt.transaction_type = 'DELIVER';
3670
3671 CURSOR Cur_get_seq IS
3672 SELECT mtl_material_transactions_s.NEXTVAL
3673 FROM DUAL;
3674
3675 p_validation_level NUMBER := 100;
3676
3677 -- 2995114
3678 -- add position variable for debugging.
3679
3680 quality_config GMD_QUALITY_CONFIG%ROWTYPE;
3681 l_revision VARCHAR2(3);
3682 l_subinventory VARCHAR2(10);
3683 l_locator_id NUMBER;
3684 l_msg_count NUMBER;
3685 processed NUMBER;
3686 l_msg_data VARCHAR2(2000); -- Bug# 5332105 changed length from 200 to 2000
3687 p_transaction_interface_id NUMBER;
3688 p_header_id NUMBER;
3689 l_trans_count NUMBER;
3690 found BOOLEAN;
3691
3692 l_position VARCHAR2(3) := '010' ;
3693
3694 BEGIN
3695
3696 -- Initialize API return status to success
3697 x_return_status := FND_API.G_RET_STS_SUCCESS;
3698
3699 -- Get the reason code
3700 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
3701 p_organization_id => p_sample.organization_id
3702 , x_quality_parameters => quality_config
3703 , x_return_status => x_return_status
3704 , x_orgn_found => found );
3705
3706 IF (x_return_status <> 'S') THEN
3707 GMD_API_PUB.Log_Message('GMD_QM_INV_REASON_CODE');
3708 RAISE FND_API.G_EXC_ERROR;
3709 END IF;
3710 l_position := '020' ;
3711
3712 --IF NOT GMIGUTL.setup(FND_GLOBAL.USER_NAME) THEN
3713 -- Error Message must have been logged.
3714 -- RAISE FND_API.G_EXC_ERROR;
3715 --END IF;
3716 --l_trans_rec.trans_type := 2; -- adjustment transaction
3717
3718 l_position := '030' ;
3719
3720 OPEN Cur_get_seq;
3721 FETCH Cur_get_seq INTO p_transaction_interface_id;
3722 CLOSE Cur_get_seq;
3723
3724 OPEN Cur_get_seq;
3725 FETCH Cur_get_seq INTO p_header_id;
3726 CLOSE Cur_get_seq;
3727
3728 IF (l_debug = 'Y') THEN
3729 gmd_debug.put_line('In Procedure create_inv_txn');
3730 gmd_debug.put_line('Input to mtl_transaction_lots_interface table=');
3731 gmd_debug.put_line(' transaction interface ID: ' || p_transaction_interface_id);
3732 gmd_debug.put_line(' header ID: ' || p_header_id);
3733 gmd_debug.put_line(' Sample ID: ' || p_sample.sample_id);
3734 gmd_debug.put_line(' user name : ' || p_user_name);
3735 gmd_debug.put_line(' date : ' || p_sample.date_drawn);
3736 gmd_debug.put_line(' qty : ' || -1*p_sample.sample_qty);
3737 gmd_debug.put_line(' lot : ' || p_sample.lot_number);
3738 gmd_debug.put_line(' reason id : ' || quality_config.transaction_reason_id);
3739 END IF;
3740
3741 l_position := '040' ;
3742
3743 --gml_sf_log('b4 insert');
3744 -- create entry in mtl_transaction_lots_interface table (MTLI)
3745
3746 --RLNAGARA Bug4753039 Insert into this table only for lot controlled item ie when the lot number is not null
3747 --because from the form we are passing lot number as NULL for Non-lot controlled items.
3748
3749 IF p_sample.lot_number IS NOT NULL THEN
3750 INSERT INTO mtl_transaction_lots_interface
3751 ( transaction_interface_id
3752 , source_code
3753 , source_line_id
3754 , last_updated_by
3755 , last_update_date
3756 , created_by
3757 , creation_date
3758 , last_update_login
3759 , transaction_quantity
3760 , lot_number
3761 , reason_id
3762 , description )
3763 VALUES
3764 ( p_transaction_interface_id
3765 , 'SAMPLES'
3766 , p_sample.sample_id
3767 , p_user_name
3768 , p_sample.date_drawn
3769 , p_user_name
3770 , p_sample.date_drawn
3771 , p_user_name
3772 , -1*p_sample.sample_qty
3773 , p_sample.lot_number
3774 , quality_config.transaction_reason_id
3775 , 'Sample creation');
3776 END IF;
3777
3778
3779
3780 l_position := '050' ;
3781
3782 IF p_sample.source = 'S' THEN -- supplier samples
3783 IF p_sample.receipt_id is not null AND p_sample.receipt_line_id is not null THEN
3784 OPEN fetch_subinventory_loc;
3785 FETCH fetch_subinventory_loc INTO l_subinventory,
3786 l_locator_id;
3787 CLOSE fetch_subinventory_loc;
3788 END IF;
3789 ELSIF p_sample.source = 'W' THEN
3790 l_subinventory := p_sample.source_subinventory ;
3791 l_locator_id := p_sample.source_locator_id ;
3792 ELSE
3793 l_subinventory := p_sample.subinventory ;
3794 l_locator_id := p_sample.locator_id ;
3795 END IF;
3796
3797 IF (l_debug = 'Y') THEN
3798 gmd_debug.put_line('after insert into lot table:');
3799 gmd_debug.put_line(' subinventory = '||l_subinventory);
3800 gmd_debug.put_line(' locator id = '||l_locator_id);
3801 gmd_debug.put_line(' sample source = '||p_sample.source);
3802 END IF;
3803
3804 -- Bug 4165704: uom convergence not needed after inventory convergence
3805 -- l_item_rec.inventory_item_id := p_sample.inventory_item_id;
3806 -- l_item_rec.organization_id := p_sample.organization_id;
3807
3808 -- Gmd_samples_grp.Get_item_values( p_sample_display => l_item_rec);
3809
3810 -- IF l_item_rec.Dual_uom_control = 3 THEN
3811 -- l_trans_rec.trans_qty2
3812 -- := INV_CONVERT.inv_um_convert(
3813 -- item_id => p_sample.inventory_item_id
3814 -- , precision => 5
3815 -- , from_quantity => p_sample.sample_qty
3816 -- , from_unit => p_sample.sample_qty_uom
3817 -- , to_unit => l_item_rec.primary_uom_code
3818 -- , from_name => NULL
3819 -- , to_name => NULL);
3820
3821 -- IF l_trans_rec.trans_qty2 < 0) THEN
3822 -- GMD_API_PUB.Log_Message('FM_SCALE_BAD_UOM_CONV',
3823 -- 'FROM_UOM',p_sample.sample_qty_uom,
3824 -- 'TO_UOM', l_item_rec.primary_uom_code ,
3825 -- 'ITEM_NO', item_rec.item_number);
3826 --
3827 -- RAISE FND_API.G_EXC_ERROR;
3828 -- END IF;
3829 -- ELSE
3830 -- l_trans_rec.trans_qty2 := NULL ;
3831 -- END IF;
3832
3833 l_position := '060' ;
3834 --gml_sf_log('b4 second insert and locator_id ='||l_locator_id);
3835
3836 -- Create the record for mtl_transaction_interface_table (MTI)
3837 INSERT INTO mtl_transactions_interface
3838 (transaction_interface_id
3839 , transaction_header_id
3840 , source_code
3841 , source_line_id
3842 , source_header_id
3843 , process_flag
3844 , validation_required
3845 , transaction_mode
3846 , lock_flag
3847 , last_updated_by
3848 , last_update_date
3849 , created_by
3850 , creation_date
3851 , last_update_login
3852 , organization_id
3853 , inventory_item_id
3854 , revision
3855 , transaction_quantity
3856 , transaction_uom
3857 , transaction_date
3858 , subinventory_code
3859 , locator_id
3860 , transaction_source_id
3861 , transaction_source_type_id
3862 , transaction_type_id
3863 , distribution_account_id
3864 , reason_id )
3865 VALUES
3866 ( p_transaction_interface_id
3867 , p_header_id
3868 , 'SAMPLES'
3869 , p_sample.sample_id
3870 , p_sample.sampling_event_id
3871 , 1 -- process enabled
3872 , 1 -- (full validation required)
3873 , 1 -- (process immediate)
3874 , 2 -- (TM will not lock the trans)
3875 , p_user_name
3876 , p_sample.date_drawn
3877 , p_user_name
3878 , p_sample.date_drawn
3879 , p_user_name
3880 , p_sample.Organization_id
3881 , p_sample.Inventory_Item_id
3882 , p_sample.revision
3883 , -1*p_sample.sample_qty
3884 , P_sample.sample_qty_uom
3885 , p_sample.date_drawn
3886 , l_subinventory
3887 , l_locator_id
3888 , NULL
3889 , 13 --(Inventory)
3890 , 1001 --(Deduct Sample Qty)
3891 , quality_config.distribution_account_id --hardcode 23843 take this out xxx
3892 , quality_config.transaction_reason_id) ;
3893
3894 l_position := '070' ;
3895
3896 IF (l_debug = 'Y') THEN
3897 gmd_debug.put_line('after insert into transaction table:');
3898 END IF;
3899
3900 --gml_sf_log('b4 call to process transactions and header id ='|| p_header_id);
3901 -- only to test error_code in mtl_transactions_interface uncomment the following commit
3902 -- commit;
3903 --call transaction manager
3904 processed := INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS
3905 (p_api_version => 1.0
3906 ,p_init_msg_list => fnd_api.g_false
3907 ,p_commit => fnd_api.g_false
3908 ,p_validation_level => p_validation_level
3909 ,x_return_status => x_return_status
3910 ,x_msg_count => l_msg_count
3911 ,x_msg_data => l_msg_data
3912 ,x_trans_count => l_trans_count
3913 ,p_table => 1 -- (MTI)
3914 ,p_header_id => p_header_id); -- foreign key to MTI, can be null
3915
3916 --gml_sf_log('after call and return value='||processed||' and status='||x_return_status);
3917 IF (l_debug = 'Y') THEN
3918 gmd_debug.put_line('after INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS status ='||processed);
3919 END IF;
3920
3921 IF processed < 0 THEN
3922 -- x_message_count and x_msg_data is already populated
3923 RAISE FND_API.G_EXC_ERROR;
3924 END IF;
3925
3926 EXCEPTION
3927 WHEN FND_API.G_EXC_ERROR THEN
3928 x_return_status := FND_API.G_RET_STS_ERROR ;
3929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3931 WHEN OTHERS THEN
3932 GMD_API_PUB.Log_Message('GMD_API_ERROR',
3933 'PACKAGE','CREATE_INV_TXN',
3934 'ERROR', SUBSTR(SQLERRM,1,100),
3935 'POSITION',l_position);
3936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3937
3938
3939 END create_inv_txn;
3940
3941
3942 --Start of comments
3943 --+========================================================================+
3944 --| API Name : create_wip_txn |
3945 --| TYPE : Group |
3946 --| Notes : This procedure creates Inventory transaction for |
3947 --| WIP sample. |
3948 --| |
3949 --| If everything is fine then 'S' is returned in the |
3950 --| parameter - x_return_status otherwise error message |
3951 --| is logged and error status - E or U returned |
3952 --| |
3953 --| HISTORY |
3954 --| Chetan Nagar 13-Nov-2002 Created. |
3955 --| This procedure has been transferred from the form. |
3956 --| |
3957 --|Saikiran Vankadari 15-JUL-2004 Bug# 3741488. Added item_um to the |
3958 --| cursor Cur_material_detail_no_step. Added IF |
3959 --| condition to check if replenish whse_code exists|
3960 --| |
3961 --| S Feinstein 21-MAR-2005 Inventory Convergence bug 41165704 |
3962 --| - batch update proc changed from gme_api_pub.insert_line_allocation|
3963 --| to gme_api_pub.create_material_txn |
3964 --| srakrish bug 5394566: Commenting the cursors as these have |
3965 --| hardcoded values and material_detail_id is directly passed |
3966 --| RAGSRIVA 01-Nov-2006 Bug 5629709 Modified procedure create_wip_txn |
3967 --| to Undo the fix for bug# 5394566 and pass the transaction |
3968 --| type id and the lot information in the call to |
3969 --| GME_API_PUB.create_material_txn |
3970 --| RLNAGARA 12-Jan-2007 B5738041 Added Revision to the cursors Cur_material_detail_with_step |
3971 --| and Cur_material_detail_no_step |
3972 --+========================================================================+
3973 -- End of comments
3974
3975 PROCEDURE create_wip_txn
3976 ( p_sample IN GMD_SAMPLES%ROWTYPE
3977 , x_return_status OUT NOCOPY VARCHAR2
3978 , x_message_count OUT NOCOPY NUMBER
3979 , x_message_data OUT NOCOPY VARCHAR2
3980 )
3981 IS
3982
3983 -- bug# 2995114
3984 -- removed Cursors Cur_replenish_whse and Cur_replenish_whse_plant
3985 -- instead moved it into public layer api
3986
3987 CURSOR Cur_global_configurator IS
3988 SELECT transaction_reason_id
3989 FROM gmd_quality_config
3990 WHERE organization_id = p_sample.organization_id
3991 order by 1 ;
3992
3993 --srakrish bug 5394566: Commenting the cursors as these have hardcoded values and material_detail_id is directly passed .
3994 -- Bug# 5629709 uncomment the cursor since its required to check if the sample item is defined as a byproduct in the batch
3995 -- with byproduct type as sample.
3996 CURSOR Cur_material_detail_with_step IS
3997 SELECT d.material_detail_id,
3998 d.inventory_item_id, d.revision, d.dtl_um --RLNAGARA B5738041 Added Revision
3999 FROM gme_material_details d,
4000 gme_batch_step_items i
4001 WHERE d.material_detail_id = i.material_detail_id
4002 AND d.line_type = 2
4003 AND d.release_type = 1
4004 AND d.by_product_type = 'S'
4005 AND d.batch_id = p_sample.batch_id
4006 AND d.inventory_item_id = p_sample.inventory_item_id
4007 AND (p_sample.step_id IS NULL
4008 OR i.batchstep_id = p_sample.step_id);
4009
4010 CURSOR Cur_material_detail_no_step IS
4011 SELECT d.material_detail_id,
4012 d.inventory_item_id, d.revision, d.dtl_um --RLNAGARA B5738041 Added Revision
4013 FROM gme_material_details d
4014 WHERE d.line_type = 2
4015 AND d.release_type = 1
4016 AND d.by_product_type = 'S'
4017 AND d.batch_id = p_sample.batch_id
4018 AND d.inventory_item_id = p_sample.inventory_item_id;
4019
4020 CURSOR c_item_no(p_item_id NUMBER) IS
4021 SELECT concatenated_segments item_no ,
4022 dual_uom_control,
4023 primary_uom_code,
4024 secondary_uom_code
4025 FROM mtl_system_items_b_kfv
4026 WHERE inventory_item_id = p_item_id
4027 AND organization_id = p_sample.organization_id;
4028
4029 -- bug 4165704: following added for new GME proc
4030 p_mmti_rec mtl_transactions_interface%ROWTYPE;
4031 p_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
4032 x_mmt_rec mtl_material_transactions%ROWTYPE;
4033 x_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
4034
4035 --p_tran_row gme_inventory_txns_gtmp%ROWTYPE;
4036 --x_material_detail gme_material_details%ROWTYPE;
4037 --x_tran_row gme_inventory_txns_gtmp%ROWTYPE;
4038 x_def_tran_row gme_inventory_txns_gtmp%ROWTYPE;
4039 p_validation_level NUMBER := 100; --gme_api_pub.max_errors;
4040
4041 message_number NUMBER := 0;
4042 dummy_cnt NUMBER := 0;
4043 dummy_number NUMBER := 0;
4044
4045 p_create_lot VARCHAR2(10) ; --BOOLEAN := TRUE;
4046 temp_lot_no VARCHAR2(32) := NULL;
4047 material_detail_item_um gme_material_details.dtl_um%TYPE;
4048 l_item_no VARCHAR2(80);
4049 l_dualum_ind NUMBER(1);
4050 l_item_um2 VARCHAR2(3);
4051 l_primary_uom_code VARCHAR2(3);
4052
4053 BEGIN
4054
4055 -- Initialize API return status to success
4056 x_return_status := FND_API.G_RET_STS_SUCCESS;
4057
4058 -- test for batch id, step id and material detail
4059 IF p_sample.batch_id IS NULL THEN
4060 GMD_API_PUB.Log_Message('GMD_INVALID_PARAMETERS');
4061 RAISE FND_API.G_EXC_ERROR;
4062 END IF;
4063 --srakrish bug 5394566: Commenting the cursors as these have hardcoded values and material_detail_id is directly passed.
4064
4065 -- Bug# 5629709 uncomment the following since its required to check if the sample item is defined as a byproduct in the batch
4066 -- with byproduct type as sample.
4067 OPEN Cur_material_detail_with_step;
4068 FETCH Cur_material_detail_with_step INTO p_mmti_rec.trx_source_line_id,
4069 p_mmti_rec.inventory_item_id,
4070 p_mmti_rec.revision, --RLNAGARA B5738041 Added Revision
4071 material_detail_item_um;
4072 IF (Cur_material_detail_with_step%NOTFOUND ) THEN
4073 OPEN Cur_material_detail_no_step;
4074 --Bug# 3741488. Added item_um to the cursor
4075 FETCH Cur_material_detail_no_step INTO p_mmti_rec.trx_source_line_id,
4076 p_mmti_rec.inventory_item_id,
4077 p_mmti_rec.revision, --RLNAGARA B5738041 Added Revision
4078 material_detail_item_um;
4079 CLOSE Cur_material_detail_no_step;
4080 END IF;
4081 CLOSE Cur_material_detail_with_step;
4082
4083 --srakrish bug 5394566: Assigning the values passed to procedure.
4084 -- Bug# 5629709 comment the following code since its fetched above
4085 /*p_mmti_rec.trx_source_line_id := p_sample.material_detail_id;
4086 p_mmti_rec.inventory_item_id := p_sample.inventory_item_id;
4087 material_detail_item_um := p_sample.sample_qty_uom;*/
4088
4089 -- Verify that we have the trx_source_line_id (material_detail_id)
4090 IF p_mmti_rec.trx_source_line_id IS NULL THEN
4091 GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
4092 RAISE FND_API.G_EXC_ERROR;
4093 END IF;
4094
4095 OPEN c_item_no (p_sample.inventory_item_id);
4096 FETCH c_item_no INTO l_item_no ,
4097 l_dualum_ind ,
4098 l_primary_uom_code ,
4099 l_item_um2;
4100 CLOSE c_item_no;
4101
4102 -- When the transation is getting updated in the GME make sure it is in alloc_qty
4103
4104 IF material_detail_item_um <> p_sample.sample_qty_uom THEN
4105 -- bug 4165704: conversion routine changed
4106 -- p_tran_row.transaction_quantity := gmicuom.uom_conversion(
4107 -- p_sample.inventory_item_id,
4108 -- 0,
4109 -- p_sample.sample_qty,
4110 -- p_sample.sample_qty_uom,
4111 -- material_detail_item_um,
4112 -- 0);
4113
4114 p_mmti_rec.transaction_quantity := INV_CONVERT. inv_um_convert (
4115 item_id => p_sample.inventory_item_id,
4116 lot_number => 0,
4117 organization_id => p_sample.organization_id,
4118 precision => 5, -- decimal point precision
4119 from_quantity => p_sample.sample_qty,
4120 from_unit => p_sample.sample_qty_uom,
4121 to_unit => l_primary_uom_code,
4122 from_name => NULL ,
4123 to_name => NULL) ;
4124 IF (l_debug = 'Y') THEN
4125 gmd_debug.put_line('After uom conversion qty2 ='|| p_mmti_rec.transaction_quantity);
4126 END IF;
4127
4128 IF (p_mmti_rec.transaction_quantity< 0 ) THEN
4129 GMD_API_PUB.Log_Message('FM_SCALE_BAD_UOM_CONV',
4130 'FROM_UOM',p_sample.sample_qty_uom,
4131 'TO_UOM',material_detail_item_um,
4132 'ITEM_NO',l_item_no);
4133 RAISE FND_API.G_EXC_ERROR;
4134 END IF;
4135 END IF;
4136
4137 -- Get the reason code from the global configurator
4138 OPEN Cur_global_configurator;
4139 FETCH Cur_global_configurator INTO p_mmti_rec.reason_id ;
4140 IF Cur_global_configurator%NOTFOUND THEN
4141 CLOSE Cur_global_configurator;
4142 GMD_API_PUB.Log_Message('GMD_QM_INV_REASON_CODE');
4143 RAISE FND_API.G_EXC_ERROR;
4144 END IF;
4145 CLOSE Cur_global_configurator;
4146
4147 -- bug# 2995114
4148 -- implement fix as done in forms.refer to forms bug# 2719300 for more details.
4149 -- if source whse specified take that warehouse.
4150 p_mmti_rec.subinventory_code := p_sample.source_subinventory ;
4151
4152
4153 -- bug# 2995114
4154 -- API errors out if no location was passed in case item and warehouse are both location controlled.
4155 -- pass source location to the API
4156 p_mmti_rec.locator_id := p_sample.source_locator_id;
4157
4158 --Bug# 3741488. Added IF condition to check if replenish whse_code exists
4159 -- API call to create an inventory insert transaction
4160 IF p_mmti_rec.subinventory_code IS NOT NULL THEN
4161
4162 IF (p_mmti_rec.transaction_quantity IS NULL ) THEN
4163 p_mmti_rec.transaction_quantity := p_sample.sample_qty;
4164 END IF;
4165
4166 p_mmti_rec.transaction_uom := p_sample.sample_qty_uom;
4167 p_mmti_rec.transaction_date := NULL;
4168 p_mmti_rec.secondary_transaction_quantity := NULL;
4169 -- bug 4165704 - no equivalent for the following
4170 -- p_tran_row.doc_id := p_sample.batch_id;xxx
4171 -- p_tran_row.alloc_um := p_sample.sample_qty_uom;
4172 -- p_tran_row.completed_ind := 1;
4173
4174 IF l_dualum_ind = 3 THEN
4175 -- bug 4165704: changed uom conversion routine for inventory convergence
4176 -- p_mmti_rec.secondary_transaction_quantity := gmicuom.uom_conversion(
4177 -- p_sample.inventory_item_id,
4178 -- 0,
4179 -- p_sample.sample_qty,
4180 -- p_sample.sample_qty_uom,
4181 -- l_item_um2,
4182 -- 0);
4183
4184 p_mmti_rec.secondary_transaction_quantity := INV_CONVERT. inv_um_convert (
4185 item_id => p_sample.inventory_item_id,
4186 lot_number => 0,
4187 organization_id => p_sample.organization_id,
4188 precision => 5, -- decimal point precision
4189 from_quantity => p_sample.sample_qty,
4190 from_unit => p_sample.sample_qty_uom,
4191 to_unit => l_item_um2,
4192 from_name => NULL ,
4193 to_name => NULL) ;
4194
4195 IF (p_mmti_rec.secondary_transaction_quantity< 0 ) THEN
4196 GMD_API_PUB.Log_Message('FM_SCALE_BAD_UOM_CONV',
4197 'FROM_UOM',p_sample.sample_qty_uom,
4198 'TO_UOM',l_item_um2,
4199 'ITEM_NO',l_item_no);
4200 RAISE FND_API.G_EXC_ERROR;
4201 END IF;
4202 ELSE
4203 p_mmti_rec.secondary_transaction_quantity := NULL;
4204 END IF;
4205
4206 IF p_sample.lot_number IS NOT NULL THEN
4207 -- lot exists
4208 p_mmti_rec.source_lot_number := p_sample.lot_number;
4209 p_create_lot := FND_API.G_FALSE; -- FALSE;
4210 temp_lot_no := NULL;
4211 ELSE
4212 -- lot does not exists and needs to be created
4213 p_mmti_rec.source_lot_number := NULL;
4214 p_create_lot := FND_API.G_TRUE; --TRUE;
4215 temp_lot_no := p_sample.lot_number;
4216 END IF; -- test for lot id
4217
4218 -- Bug 4165704: gme_api_pub.insert_line_allocation replaced by create_material_txns
4219 -- GME_API_PUB.insert_line_allocation(
4220 -- p_api_version => 2.0
4221 --, p_validation_level => p_validation_level
4222 --, p_init_msg_list => FALSE
4223 --, p_commit => FALSE
4224 --, x_message_count => x_message_count
4225 --, x_message_list => x_message_data
4226 --, x_return_status => x_return_status
4227 --, p_material_transaction_inter_rec => p_material_transaction_inter_rec
4228 --, p_batch_no => NULL
4229 --, p_org_code => NULL
4230 --, p_line_no => NULL
4231 --, p_line_type => NULL
4232 --, p_create_lot => p_create_lot --TRUE
4233 --, p_generate_lot => FALSE
4234 --, p_generate_parent_lot => FALSE
4235 --, p_transaction_lot_inter_tbl => FALSE -- lot info for lot interface table
4236 --, x_material_trasaction_rec => x_material_detail -- contains the newly created transaction
4237 --, x_transaction_lot_tbl => x_tran_row); -- contains info for lot transactions
4238
4239 p_mmti_rec.organization_id := p_sample.organization_id;
4240
4241 -- Bug# 5629709 Pass the transaction_type_id and lot information
4242 p_mmti_rec.transaction_type_id := 1002;
4243 IF p_sample.lot_number IS NOT NULL THEN
4244 p_mmli_tbl(1).lot_number := p_sample.lot_number;
4245 p_mmli_tbl(1).transaction_quantity := p_mmti_rec.transaction_quantity;
4246 p_mmli_tbl(1).primary_quantity := p_mmti_rec.transaction_quantity;
4247 p_mmli_tbl(1).secondary_transaction_quantity := p_mmti_rec.secondary_transaction_quantity;
4248 END IF;
4249
4250 GME_API_PUB.create_material_txn(
4251 p_api_version => 2.0
4252 , p_validation_level => p_validation_level
4253 , p_init_msg_list => fnd_api.g_false --FALSE
4254 , p_commit => fnd_api.g_false --FALSE
4255 , x_message_count => x_message_count
4256 , x_message_list => x_message_data
4257 , x_return_status => x_return_status
4258 , p_org_code => NULL
4259 , p_mmti_rec => p_mmti_rec
4260 , p_mmli_tbl => p_mmli_tbl
4261 , p_batch_no => NULL
4262 , p_line_no => NULL
4263 , p_line_type => NULL
4264 , p_create_lot => p_create_lot -- TRUE
4265 , p_generate_lot => fnd_api.g_false --FALSE
4266 , p_generate_parent_lot => fnd_api.g_false --FALSE
4267 , x_mmt_rec => x_mmt_rec -- contains the newly created transaction
4268 , x_mmln_tbl => x_mmln_tbl ); -- contains info for lot transactions
4269
4270 IF (X_return_status <> 'S') THEN
4271 -- x_message_count and x_message_data is already populated
4272 RAISE FND_API.G_EXC_ERROR;
4273 END IF;
4274 ELSE
4275 -- Bug 3492053: if replenish whse is not there inventory is not generated
4276 GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS');
4277 RAISE FND_API.G_EXC_ERROR;
4278 END IF; -- check for replenish whse code
4279
4280 EXCEPTION
4281 WHEN FND_API.G_EXC_ERROR THEN
4282 x_return_status := FND_API.G_RET_STS_ERROR ;
4283 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4285 WHEN OTHERS THEN
4286 GMD_API_PUB.Log_Message('GMD_API_ERROR',
4287 'PACKAGE','INVENTORY_TRANS_INSERT',
4288 'ERROR', SUBSTR(SQLERRM,1,100));
4289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4290
4291
4292 END create_wip_txn;
4293
4294
4295
4296
4297 --Start of comments
4298 --+========================================================================+
4299 --| API Name : post_wip_txn |
4300 --| TYPE : Group |
4301 --| Notes : This procedure calls GME public API - save_batch to |
4302 --| write transactions from the temporary tables to actual |
4303 --| tables. |
4304 --| |
4305 --| If everything is fine then 'S' is returned in the |
4306 --| parameter - x_return_status otherwise error message |
4307 --| is logged and error status - E or U returned |
4308 --| |
4309 --| HISTORY |
4310 --| Chetan Nagar 13-Nov-2002 Created. |
4311 --| This procedure has been transferred from the form. |
4312 --| |
4313 --+========================================================================+
4314 -- End of comments
4315
4316 PROCEDURE post_wip_txn
4317 ( p_batch_id IN NUMBER
4318 , x_return_status OUT NOCOPY VARCHAR2
4319 ) IS
4320
4321 l_batch_header GME_BATCH_HEADER%ROWTYPE;
4322 l_return_status VARCHAR2(1);
4323
4324 BEGIN
4325
4326 -- Initialize API return status to success
4327 x_return_status := FND_API.G_RET_STS_SUCCESS;
4328
4329 l_batch_header.batch_id := p_batch_id;
4330
4331 /* took this code out but must put back gme functionality
4332 gme_api_pub.save_batch(p_batch_header => l_batch_header,
4333 p_commit => FALSE,
4334 x_return_status => l_return_status);
4335
4336 IF (l_return_status <> 'S') THEN
4337 RAISE FND_API.G_EXC_ERROR;
4338 END IF;
4339 */
4340 EXCEPTION
4341 WHEN FND_API.G_EXC_ERROR THEN
4342 x_return_status := FND_API.G_RET_STS_ERROR ;
4343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4345 WHEN OTHERS THEN
4346 GMD_API_PUB.Log_Message('GMD_API_ERROR',
4347 'PACKAGE','POST_WIP_TXN',
4348 'ERROR', SUBSTR(SQLERRM,1,100));
4349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4350
4351 END post_wip_txn;
4352
4353
4354 --Start of comments
4355 --+========================================================================+
4356 --| API Name : find_max_test_duration |
4357 --| TYPE : Group |
4358 --| Notes : This procedure is called by samples to help validate |
4359 --| the required date. It gets the maximum test duration |
4360 --| of all tests for the samples spec. |
4361 --| |
4362 --| It will return the maximum duration time and a test |
4363 --| test description for a test having that duration |
4364 --| |
4365 --| HISTORY |
4366 --| Susan Feinstein 27-Jan-2003 Created for bug 2752102 |
4367 --| |
4368 --+========================================================================+
4369 -- End of comments
4370
4371 PROCEDURE get_max_test_method_duration
4372 ( p_spec_id IN NUMBER
4373 , x_test_dur OUT NOCOPY NUMBER
4374 , x_test_code OUT NOCOPY VARCHAR2
4375 ) IS
4376
4377 Cursor Cur_find_max_dur IS
4378 SELECT qc.test_code, mthd.test_duration
4379 FROM gmd_qc_tests_vl qc,
4380 gmd_test_methods_b mthd,
4381 gmd_spec_tests_b spec
4382 WHERE qc.test_method_id = mthd.test_method_id
4383 AND qc.test_id = spec.test_id
4384 AND spec.spec_id = p_spec_id
4385 AND ( mthd.test_duration = (SELECT MAX(test_duration)
4386 FROM gmd_test_methods_b mthd2,
4387 gmd_spec_tests_b spec2
4388 WHERE spec2.TEST_METHOD_ID = mthd2.test_method_id
4389 AND spec2.spec_id = p_spec_id));
4390
4391 BEGIN
4392 x_test_dur := 0;
4393
4394 OPEN Cur_find_max_dur;
4395 FETCH Cur_find_max_dur INTO x_test_code,
4396 x_test_dur ;
4397 CLOSE Cur_find_max_dur;
4398
4399
4400 END get_max_test_method_duration;
4401
4402
4403 --Start of comments
4404 --+========================================================================+
4405 --| API Name : update_remaining_qty |
4406 --| TYPE : Group |
4407 --| Notes : This procedure is called by results to update the |
4408 --| remaining quantity on the samples table. |
4409 --| |
4410 --| HISTORY |
4411 --| Susan Feinstein 05-Aug-2003 Created for bug 3088216 |
4412 --| RLNAGARA 04-Apr-2006 B5106199 UOM Conv Changes |
4413 --+========================================================================+
4414 -- End of comments
4415
4416 PROCEDURE update_remaining_qty
4417 ( p_result_id IN NUMBER,
4418 p_sample_id IN NUMBER default 0,
4419 qty IN NUMBER,
4420 x_return_status OUT NOCOPY VARCHAR2
4421 ) IS
4422
4423 --RLNAGARA B5106191 Replaced the query in the cursor C_item_no which was using ic_item_mst.
4424 /* CURSOR C_item_no(item_id VARCHAR2) IS
4425 SELECT item_no
4426 FROM ic_item_mst
4427 WHERE item_id = item_id;
4428 */
4429 CURSOR C_item_no(p_inventory_item_id NUMBER,p_organization_id NUMBER) IS
4430 SELECT concatenated_segments item_number
4431 FROM mtl_system_items_kfv
4432 WHERE inventory_item_id = p_inventory_item_id
4433 AND organization_id = p_organization_id;
4434
4435
4436
4437 -- added this code to prevent locking error if sample is locked by another form.
4438 CURSOR C_lock_sample(samp_id NUMBER) IS
4439 SELECT 'x' from gmd_samples
4440 where sample_id = samp_id
4441 for update of sample_id NOWAIT ;
4442
4443 record_lock EXCEPTION ;
4444 pragma exception_init(record_lock,-00054) ;
4445 -- end update for locking error
4446
4447 l_in_samples gmd_samples%ROWTYPE;
4448 l_samples gmd_samples%ROWTYPE;
4449
4450 l_in_results gmd_results%ROWTYPE;
4451 l_results gmd_results%ROWTYPE;
4452
4453 l_samples_item_no VARCHAR2(32);
4454
4455 converted_qty NUMBER;
4456
4457 result BOOLEAN;
4458
4459 BEGIN
4460 -- Initialize API return status to success
4461 x_return_status := FND_API.G_RET_STS_SUCCESS;
4462
4463 IF (l_debug = 'Y') THEN
4464 gmd_debug.put_line('In Procedure update_remaining_qty');
4465 gmd_debug.put_line('Input Parameters=');
4466 gmd_debug.put_line(' Result ID: ' || p_result_id);
4467 gmd_debug.put_line(' Sample ID: ' || p_sample_id);
4468 gmd_debug.put_line(' Quantity : ' || qty);
4469 END IF;
4470
4471 -- Get the results record
4472 l_in_results.result_id := p_result_id;
4473 result := GMD_RESULTS_PVT.fetch_row(
4474 p_results => l_in_results,
4475 x_results => l_results);
4476
4477 IF (l_debug = 'Y') THEN
4478 gmd_debug.put_line('after fetch row from result');
4479 END IF;
4480
4481 -- If sample_id is specified use it. If not, get sample_id from result
4482 IF (p_sample_id > 0) THEN
4483
4484 IF (l_debug = 'Y') THEN
4485 gmd_debug.put_line('1');
4486 END IF;
4487
4488 l_in_samples.sample_id := p_sample_id;
4489 ELSE
4490
4491 IF (l_debug = 'Y') THEN
4492 gmd_debug.put_line('2');
4493 END IF;
4494
4495 -- Get the sample id from the results record
4496 IF (l_results.reserve_sample_id IS NULL) THEN
4497 l_in_samples.sample_id := l_results.sample_id;
4498 ELSE
4499 l_in_samples.sample_id := l_results.reserve_sample_id;
4500 END IF;
4501 END IF;
4502
4503 IF (l_debug = 'Y') THEN
4504 gmd_debug.put_line('sample id changed to: ');
4505 gmd_debug.put_line(' Sample ID: ' || l_in_samples.sample_id);
4506 END IF;
4507
4508 result := GMD_SAMPLES_PVT.fetch_row(
4509 p_samples => l_in_samples,
4510 x_samples => l_samples);
4511
4512 IF (l_debug = 'Y') THEN
4513 gmd_debug.put_line('quantities = ');
4514 gmd_debug.put_line(' Sample qty: ' || l_samples.sample_qty);
4515 gmd_debug.put_line(' Remaining qty: ' || l_samples.remaining_qty);
4516 gmd_debug.put_line(' other qty: ' || qty);
4517 gmd_debug.put_line('result uom: '||l_results.test_qty_uom);
4518 gmd_debug.put_line('sample uom: '||l_samples.sample_qty_uom);
4519 END IF;
4520
4521
4522 -- Bug 3251084: if sample_qty or test_qty are null, then no need
4523 -- to update the remaining qty. Leave this procedure.
4524 -- Bug 3278903: test qty should not be tested here. Changed to qty.
4525 IF ((NVL(l_samples.sample_qty, 0) = 0 )
4526 OR (NVL(qty, 0) = 0)) THEN
4527 RETURN;
4528 END IF;
4529
4530 -- Convert consumed qty to sample_qty_uom
4531 IF l_results.test_qty_uom <> l_samples.sample_qty_uom THEN
4532
4533 --RLNAGARA B5106199 Replaced the call gmicuom.uom_conversion with INV_CONVERT.inv_um_convert below
4534 /* converted_qty := gmicuom.uom_conversion(l_samples.inventory_item_id,
4535 0,
4536 qty,
4537 l_results.test_qty_uom,
4538 l_samples.sample_qty_uom,
4539 0);
4540 */
4541
4542 converted_qty := INV_CONVERT.inv_um_convert(
4543 item_id => l_samples.inventory_item_id,
4544 lot_number => NULL,
4545 organization_id => l_samples.organization_id,
4546 precision => 5,
4547 from_quantity => qty,
4548 from_unit => l_results.test_qty_uom,
4549 to_unit => l_samples.sample_qty_uom,
4550 from_name => NULL,
4551 to_name => NULL);
4552
4553 IF (l_debug = 'Y') THEN
4554 gmd_debug.put_line('after conversion : ');
4555 gmd_debug.put_line('converted qty : '|| converted_qty);
4556 END IF;
4557
4558 IF (converted_qty < 0 ) THEN
4559
4560 OPEN C_item_no(l_samples.inventory_item_id,l_samples.organization_id ); --RLNAGARA B5106191 passing organization_id also
4561 FETCH C_item_no INTO l_samples_item_no;
4562 CLOSE C_item_no;
4563
4564 GMD_API_PUB.Log_Message('FM_SCALE_BAD_UOM_CONV',
4565 'FROM_UOM',l_results.test_qty_uom,
4566 'TO_UOM', l_samples.sample_qty_uom,
4567 'ITEM_NO',l_samples_item_no);
4568 RAISE FND_API.G_EXC_ERROR;
4569
4570 -- Bug 3088216: update samples remaining qty
4571 ELSIF converted_qty <= l_samples.remaining_qty THEN
4572
4573 IF (l_debug = 'Y') THEN
4574 gmd_debug.put_line('before update : ');
4575 END IF;
4576
4577 -- added this code to prevent locking error if sample is locked by another form.
4578 OPEN C_lock_sample(l_samples.sample_id);
4579 CLOSE C_lock_sample;
4580
4581 UPDATE gmd_samples
4582 SET remaining_qty = remaining_qty - converted_qty
4583 WHERE sample_id = l_samples.sample_id;
4584
4585 ELSE
4586 GMD_API_PUB.Log_Message('GMD_QM_REMAIN_QTY_NEG');
4587 RAISE FND_API.G_EXC_ERROR;
4588 END IF; -- Bug 3088216
4589
4590 ELSE -- samples uom = test uom
4591
4592 IF (l_debug = 'Y') THEN
4593 gmd_debug.put_line(' samples uom = test uom : ');
4594 END IF;
4595
4596 IF qty <= l_samples.remaining_qty THEN
4597 -- added this code to prevent locking error if sample is locked by another form.
4598 OPEN C_lock_sample(l_samples.sample_id);
4599 CLOSE C_lock_sample;
4600
4601 UPDATE gmd_samples
4602 SET remaining_qty = remaining_qty - qty
4603 WHERE sample_id = l_samples.sample_id;
4604
4605 ELSE
4606 GMD_API_PUB.Log_Message('GMD_QM_REMAIN_QTY_NEG');
4607 RAISE FND_API.G_EXC_ERROR;
4608 END IF; -- Bug 3088216
4609
4610
4611 END IF;
4612
4613 EXCEPTION
4614 WHEN FND_API.G_EXC_ERROR THEN
4615 x_return_status := FND_API.G_RET_STS_ERROR ;
4616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4618 WHEN RECORD_LOCK THEN -- added to prevent record locking of samples
4619 IF (l_debug = 'Y') THEN
4620 gmd_debug.put_line('Reached in lock on samples');
4621 END IF;
4622 IF C_lock_sample%ISOPEN THEN
4623 CLOSE C_lock_sample;
4624 END IF;
4625 GMD_API_PUB.Log_Message('GMD_QM_SAMPLES_LOCKED'); -- Bug# 5463117
4626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4627 WHEN OTHERS THEN
4628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4629
4630 END update_remaining_qty;
4631
4632
4633 --+========================================================================+
4634 --| API Name : sample_source_display |
4635 --| TYPE : Group |
4636 --| Notes : This procedure retrieves samples values for display from |
4637 --| the ids on the samples table |
4638 --| |
4639 --| HISTORY |
4640 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
4641 --| Bug 4165704 |
4642 --| RLNAGARA 19-Dec-2005 Bug#4868950 |
4643 --| -Assigned the value of resources and subinventory to the output |
4644 --| RLNAGARA 20-Dec-2005 Bug# 4880152 |
4645 --| -Added the revision variable.
4646 --| M. Grosser 28-Feb-2006 Bug 5016617 - Added retrieval of supplier name|
4647 --| to procedure sample_source_display. |
4648 --| RLANGARA 04-Apr-2006 Bug 5130051 modified the cursor parameters of Cur_disposition |
4649 --+========================================================================+
4650 PROCEDURE Sample_source_display (
4651 p_id IN NUMBER
4652 , p_type IN VARCHAR2
4653 , x_display OUT NOCOPY sample_display_rec
4654 , x_return_status OUT NOCOPY VARCHAR2
4655 ) IS
4656 CURSOR Cur_user(name_id NUMBER) IS
4657 SELECT user_name, description
4658 FROM fnd_user
4659 WHERE user_id = name_id ;
4660
4661 CURSOR Cur_organization_code(orgn_id NUMBER) IS
4662 SELECT organization_code
4663 FROM mtl_parameters
4664 WHERE organization_id = orgn_id;
4665
4666 CURSOR Cur_locator(loc_id NUMBER) IS
4667 SELECT concatenated_segments
4668 FROM mtl_item_locations_kfv
4669 WHERE inventory_location_id = loc_id;
4670
4671 --RLNAGARA B5130051 modified parameter from sample_id to p_sample_id
4672 CURSOR Cur_disposition(p_sample_id NUMBER) IS
4673 SELECT l.lookup_code,
4674 l.meaning
4675 FROM gmd_sample_spec_disp ssd,
4676 gem_lookups l
4677 WHERE ssd.sample_id = p_sample_id
4678 AND l.lookup_type = 'GMD_QC_SAMPLE_DISP'
4679 AND l.lookup_code = ssd.disposition ;
4680
4681 in_sample gmd_samples%ROWTYPE;
4682 out_sample gmd_samples%ROWTYPE;
4683 in_sampling_event gmd_sampling_events%ROWTYPE;
4684 out_sampling_event gmd_sampling_events%ROWTYPE;
4685 sampling_event gmd_sampling_events%ROWTYPE;
4686
4687 /*=================================
4688 Added for BUG#4695552.
4689 =================================*/
4690 CURSOR get_grade(v_organization NUMBER, v_lot_number VARCHAR2) IS
4691 SELECT grade_code
4692 FROM mtl_lot_numbers
4693 WHERE organization_id = v_organization
4694 AND lot_number = v_lot_number;
4695
4696 -- Exceptions
4697 e_sampling_event_fetch_error EXCEPTION;
4698 e_sample_fetch_error EXCEPTION;
4699
4700 BEGIN
4701 -- Initialize API return status to success
4702 x_return_status := FND_API.G_RET_STS_SUCCESS;
4703
4704 -- test for whether sample id or sampling event id was passed
4705 IF p_type = 'SAMPLE' THEN -- sample_id is passed
4706 in_sample.sample_id := p_id;
4707 IF NOT ( gmd_samples_pvt.fetch_row(p_samples => in_sample,
4708 x_samples => out_sample))
4709 THEN
4710 -- Fetch Error.
4711 RAISE e_sample_fetch_error;
4712 END IF;
4713
4714 x_display.sample_no := out_sample.sample_no ;
4715 x_display.parent_lot_number := out_sample.parent_lot_number;
4716 x_display.lot_number := out_sample.lot_number;
4717 x_display.sampling_event_id := out_sample.sampling_event_id;
4718 x_display.retain_as := out_sample.retain_as ;
4719 x_display.sample_type := out_sample.sample_type ;
4720 x_display.source := out_sample.source ;
4721 x_display.subinventory := out_sample.subinventory ;
4722 x_display.po_header_id := out_sample.po_header_id ;
4723 x_display.inventory_item_id := out_sample.inventory_item_id;
4724 x_display.revision := out_sample.revision ; --RLNAGARA Bug # 4880152
4725 x_display.organization_id := out_sample.organization_id;
4726 x_display.creation_date := out_sample.creation_date ;
4727 x_display.resources := out_sample.resources ; --RLNAGARA Bug # 4868950
4728
4729
4730 -- To Fetch Sample Disposition
4731 OPEN Cur_disposition(p_id);
4732 FETCH Cur_disposition INTO x_display.sample_disposition,
4733 x_display.sample_disposition_desc;
4734 CLOSE Cur_disposition;
4735
4736 -- To Fetch Sampler Name
4737 IF out_sample.sampler_id IS NOT NULL THEN
4738 OPEN Cur_user(out_sample.sampler_id);
4739 FETCH Cur_user INTO x_display.sampler,
4740 x_display.sampler_name;
4741 CLOSE Cur_user;
4742 END IF;
4743
4744 -- To Fetch Storage Organization Code
4745 IF out_sample.storage_organization_id IS NOT NULL THEN
4746 OPEN Cur_organization_code(out_sample.storage_organization_id);
4747 FETCH Cur_organization_code INTO x_display.storage_organization_code ;
4748 CLOSE Cur_organization_code;
4749
4750 x_display.storage_locator_id := out_sample.storage_locator_id;
4751 IF out_sample.storage_locator_id IS NOT NULL THEN
4752 OPEN Cur_locator(out_sample.storage_locator_id);
4753 FETCH Cur_locator INTO x_display.storage_locator ;
4754 CLOSE Cur_locator;
4755 END IF;
4756 END IF;
4757
4758 -- To Fetch Lab Organization Code
4759 IF out_sample.Lab_organization_id IS NOT NULL THEN
4760 OPEN Cur_organization_code(out_sample.Lab_organization_id);
4761 FETCH Cur_organization_code INTO x_display.Lab_organization_code ;
4762 CLOSE Cur_organization_code;
4763 END IF;
4764
4765 -- test for whether sample event id exists because then sampling event fields are needed
4766 IF ((NVL(out_sample.sample_id, 0) <> 0)
4767 AND (NVL(out_sample.sampling_event_id, 0) <> 0)) THEN
4768 in_sampling_event.sampling_event_id := out_sample.sampling_event_id;
4769 IF NOT (GMD_SAMPLING_EVENTS_PVT.fetch_row(p_sampling_events => in_sampling_event,
4770 x_sampling_events => out_sampling_event))
4771 THEN
4772 -- Fetch Error.
4773 RAISE e_sampling_event_fetch_error;
4774 END IF;
4775
4776 x_display.sample_req_cnt := out_sampling_event.sample_req_cnt;
4777 x_display.sample_taken_cnt := out_sampling_event.sample_taken_cnt;
4778 x_display.archived_taken := out_sampling_event.archived_taken;
4779 x_display.reserved_taken := out_sampling_event.reserved_taken;
4780 x_display.sample_active_cnt := out_sampling_event.sample_active_cnt;
4781
4782 END IF;
4783
4784 Gmd_samples_grp.get_item_values(p_sample_display => x_display);
4785
4786 IF out_sample.source = 'I' THEN
4787 Gmd_samples_grp.Inventory_source(p_locator_id => out_sample.locator_id
4788 , p_subinventory => out_sample.subinventory
4789 , p_organization_id => out_sample.organization_id
4790 , x_display => x_display);
4791 ELSIF out_sample.source = 'C' THEN
4792 Gmd_samples_grp.customer_source(p_org_id => out_sample.org_id
4793 , p_ship_to_site_id => out_sample.ship_to_site_id
4794 , p_order_id => out_sample.order_id
4795 , p_order_line_id => out_sample.order_line_id
4796 , p_cust_id => out_sample.cust_id
4797 , x_display => x_display);
4798 ELSIF out_sample.source = 'S' THEN
4799 Gmd_samples_grp.supplier_source(p_supplier_id => out_sample.supplier_id
4800 , p_po_header_id => out_sample.po_header_id
4801 , p_po_line_id => out_sample.po_line_id
4802 , p_receipt_id => out_sample.receipt_id
4803 , p_receipt_line_id => out_sample.receipt_line_id
4804 , p_supplier_site_id => out_sample.supplier_site_id
4805 , p_org_id => out_sample.org_id
4806 , p_organization_id => out_sample.organization_id
4807 , p_subinventory => out_sample.subinventory
4808 , x_display => x_display);
4809 ELSIF out_sample.source = 'W' THEN
4810 Gmd_samples_grp.wip_source(p_batch_id => out_sample.batch_id
4811 , p_step_id => out_sample.step_id
4812 , p_recipe_id => out_sample.recipe_id
4813 , p_formula_id => out_sample.formula_id
4814 , p_formulaline_id => out_sample.formulaline_id
4815 , p_material_detail_id => out_sample.material_detail_id
4816 , p_routing_id => out_sample.routing_id
4817 , p_oprn_id => out_sample.oprn_id
4818 , p_inventory_item_id => out_sample.inventory_item_id
4819 , p_organization_id => out_sample.organization_id
4820 , x_display => x_display);
4821 ELSIF out_sample.source = 'T' THEN
4822 Gmd_samples_grp.stability_study_source(p_variant_id => out_sample.variant_id
4823 ,p_time_point_id => out_sample.time_point_id
4824 , x_display => x_display);
4825 ELSIF out_sample.source = 'L' THEN
4826 Gmd_samples_grp.physical_location_source(p_locator_id => out_sample.locator_id
4827 , p_subinventory => out_sample.subinventory
4828 , p_organization_id => out_sample.organization_id
4829 , x_display => x_display);
4830 ELSIF out_sample.source = 'R' THEN
4831 Gmd_samples_grp.resource_source(p_instance_id => out_sample.instance_id
4832 , x_display => x_display);
4833 END IF;
4834
4835 ELSIF p_type = 'EVENT' THEN -- sampling_event_id is passed
4836 in_sampling_event.sampling_event_id := p_id;
4837 IF NOT (GMD_SAMPLING_EVENTS_PVT.fetch_row(p_sampling_events => in_sampling_event,
4838 x_sampling_events => out_sampling_event))
4839 THEN
4840 -- Fetch Error.
4841 RAISE e_sampling_event_fetch_error;
4842 END IF;
4843
4844 x_display.sample_req_cnt := out_sampling_event.sample_req_cnt;
4845 x_display.sample_taken_cnt := out_sampling_event.sample_taken_cnt;
4846 x_display.archived_taken := out_sampling_event.archived_taken;
4847 x_display.reserved_taken := out_sampling_event.reserved_taken;
4848 x_display.sample_active_cnt := out_sampling_event.sample_active_cnt;
4849
4850 x_display.inventory_item_id := out_sampling_event.inventory_item_id;
4851 x_display.organization_id := out_sampling_event.organization_id;
4852 x_display.creation_date := out_sampling_event.creation_date ;
4853 x_display.subinventory := out_sampling_event.subinventory ; --RLNAGARA Bug# 4868950
4854
4855 Gmd_samples_grp.get_item_values(p_sample_display => x_display);
4856
4857 IF out_sampling_event.source = 'I' THEN
4858 Gmd_samples_grp.Inventory_source(p_locator_id => out_sampling_event.locator_id
4859 , p_subinventory => out_sampling_event.subinventory
4860 , p_organization_id => out_sampling_event.organization_id
4861 , x_display => x_display);
4862 ELSIF out_sampling_event.source = 'C' THEN
4863 Gmd_samples_grp.customer_source(p_org_id => out_sampling_event.org_id
4864 , p_ship_to_site_id => out_sampling_event.ship_to_site_id
4865 , p_order_id => out_sampling_event.order_id
4866 , p_order_line_id => out_sampling_event.order_line_id
4867 , p_cust_id => out_sampling_event.cust_id
4868 , x_display => x_display);
4869 ELSIF out_sampling_event.source = 'S' THEN
4870 Gmd_samples_grp.supplier_source(p_supplier_id => out_sampling_event.supplier_id
4871 , p_po_header_id => out_sampling_event.po_header_id
4872 , p_po_line_id => out_sampling_event.po_line_id
4873 , p_receipt_id => out_sampling_event.receipt_id
4874 , p_receipt_line_id => out_sampling_event.receipt_line_id
4875 , p_supplier_site_id => out_sampling_event.supplier_site_id
4876 , p_org_id => out_sampling_event.org_id
4877 , p_organization_id => out_sampling_event.organization_id
4878 , p_subinventory => out_sampling_event.subinventory
4879 , x_display => x_display);
4880 ELSIF out_sampling_event.source = 'W' THEN
4881 Gmd_samples_grp.wip_source(p_batch_id => out_sampling_event.batch_id
4882 , p_step_id => out_sampling_event.step_id
4883 , p_recipe_id => out_sampling_event.recipe_id
4884 , p_formula_id => out_sampling_event.formula_id
4885 , p_formulaline_id => out_sampling_event.formulaline_id
4886 , p_material_detail_id => out_sampling_event.material_detail_id
4887 , p_routing_id => out_sampling_event.routing_id
4888 , p_oprn_id => out_sampling_event.oprn_id
4889 , p_inventory_item_id => out_sampling_event.inventory_item_id
4890 , p_organization_id => out_sample.organization_id
4891 , x_display => x_display);
4892 ELSIF out_sampling_event.source = 'T' THEN
4893 Gmd_samples_grp.stability_study_source(p_variant_id => out_sampling_event.variant_id
4894 ,p_time_point_id => out_sampling_event.time_point_id
4895 , x_display => x_display);
4896 ELSIF out_sampling_event.source = 'L' THEN
4897 Gmd_samples_grp.physical_location_source(p_locator_id => out_sampling_event.locator_id
4898 , p_subinventory => out_sampling_event.subinventory
4899 , p_organization_id => out_sampling_event.organization_id
4900 , x_display => x_display);
4901 ELSIF out_sampling_event.source = 'R' THEN
4902 Gmd_samples_grp.resource_source(p_instance_id => out_sampling_event.instance_id
4903 , x_display => x_display);
4904 END IF;
4905 ELSE -- p_type parameter incorrect
4906 x_return_status := FND_API.G_RET_STS_ERROR ;
4907 END IF; -- end p_type = 'SAMPLE'
4908
4909 /*============================================
4910 BUG#469552
4911 Moved set of storage_subinventory and
4912 added grade retrieval.
4913 ============================================*/
4914 x_display.storage_subinventory := out_sample.storage_subinventory;
4915
4916
4917 IF (out_sample.lot_number IS NOT NULL) THEN
4918 OPEN get_grade (out_sample.organization_id, out_sample.lot_number);
4919 FETCH get_grade INTO x_display.grade_code;
4920 IF (get_grade%NOTFOUND) THEN
4921 x_display.grade_code := NULL;
4922 END IF;
4923 CLOSE get_grade;
4924 END IF;
4925
4926 EXCEPTION
4927 WHEN FND_API.G_EXC_ERROR OR
4928 e_sampling_event_fetch_error OR
4929 e_sample_fetch_error
4930 THEN
4931 x_return_status := FND_API.G_RET_STS_ERROR ;
4932 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4934 WHEN OTHERS THEN
4935 GMD_API_PUB.Log_Message('GMD_API_ERROR',
4936 'PACKAGE','SAMPLE_SOURCE_DISPLAY','ERROR', SUBSTR(SQLERRM,1,100));
4937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4938 END Sample_source_display ;
4939
4940
4941 --+========================================================================+
4942 --| API Name : get_item_values |
4943 --| TYPE : Group |
4944 --| Notes : This procedure gets item control values from items table |
4945 --| |
4946 --| HISTORY |
4947 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
4948 --| Bug 4165704 |
4949 --+========================================================================+
4950 PROCEDURE Get_item_values (p_sample_display IN OUT NOCOPY sample_display_rec)
4951 IS
4952 CURSOR Cur_get_item IS
4953 SELECT concatenated_segments, -- (Item_Number)
4954 description,
4955 Restrict_subinventories_code,
4956 restrict_locators_code,
4957 location_control_code,
4958 Revision_qty_control_code, -- (revision cntrl)
4959 Lot_control_code,
4960 Lot_status_enabled,
4961 grade_control_flag,
4962 Primary_uom_code,
4963 Dual_uom_control,
4964 Eng_item_flag, -- (experimental item)
4965 Child_lot_flag, -- parent lot control
4966 Indivisible_flag,
4967 Serial_number_control_code, --(must = 0 to generate inv transaction)
4968 process_yield_subinventory, -- replenish subinventory
4969 process_yield_locator_id -- replenish locator_id
4970 FROM mtl_system_items_b_kfv
4971 WHERE organization_id = p_sample_display.organization_id
4972 AND inventory_item_id = p_sample_display.inventory_item_id;
4973
4974
4975 CURSOR Cur_source_locator IS
4976 SELECT concatenated_segments
4977 FROM mtl_item_locations_kfv
4978 WHERE inventory_location_id = p_sample_display.source_locator_id;
4979
4980 BEGIN
4981 IF (p_sample_display.inventory_item_id IS NOT NULL
4982 AND p_sample_display.organization_id IS NOT NULL) THEN
4983 OPEN Cur_get_item;
4984 FETCH Cur_get_item INTO p_sample_display.item_number,
4985 p_sample_display.item_description,
4986 p_sample_display.Restrict_subinventories_code,
4987 p_sample_display.restrict_locators_code,
4988 p_sample_display.location_control_code,
4989 p_sample_display.Revision_qty_control_code,
4990 p_sample_display.Lot_control_code,
4991 p_sample_display.Lot_status_enabled,
4992 p_sample_display.Grade_control_flag,
4993 p_sample_display.Primary_uom_code,
4994 p_sample_display.Dual_uom_control,
4995 p_sample_display.Eng_item_flag,
4996 p_sample_display.Child_lot_flag,
4997 p_sample_display.Indivisible_flag,
4998 p_sample_display.Serial_number_control_code,
4999 p_sample_display.source_subinventory,
5000 p_sample_display.source_locator_id ;
5001 CLOSE Cur_get_item;
5002
5003 -- If replenish locator id was found, get source locator
5004 IF (p_sample_display.source_locator_id IS NOT NULL) THEN
5005 OPEN Cur_source_locator;
5006 FETCH Cur_source_locator INTO p_sample_display.locator;
5007 CLOSE Cur_source_locator;
5008 END IF;
5009
5010 END IF;
5011 END Get_item_values;
5012
5013
5014 --+=======================================================================9+
5015 --| API Name : inventory_source |
5016 --| TYPE : Group |
5017 --| Notes : This procedure |
5018 --| |
5019 --| HISTORY |
5020 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5021 --| Bug 4165704 |
5022 --+========================================================================+
5023 PROCEDURE Inventory_source (
5024 p_locator_id IN NUMBER
5025 ,p_subinventory IN VARCHAR2
5026 ,p_organization_id IN NUMBER
5027 , x_display IN OUT NOCOPY sample_display_rec)
5028
5029 IS
5030 CURSOR Cur_locator IS
5031 SELECT concatenated_segments
5032 FROM mtl_item_locations_kfv
5033 WHERE inventory_location_id = p_locator_id;
5034
5035 CURSOR Cur_subinventory IS
5036 SELECT Locator_type, -- locator control
5037 description
5038 FROM mtl_secondary_inventories
5039 WHERE secondary_inventory_name = p_subinventory
5040 AND organization_id = p_organization_id;
5041
5042 BEGIN
5043 IF (p_subinventory IS NOT NULL) THEN
5044 OPEN Cur_subinventory;
5045 FETCH Cur_subinventory INTO x_display.locator_type,
5046 x_display.subinventory_desc;
5047 CLOSE Cur_subinventory;
5048 END IF;
5049
5050 IF (p_locator_id IS NOT NULL) THEN
5051 OPEN Cur_locator;
5052 FETCH Cur_locator INTO x_display.locator;
5053 CLOSE Cur_locator;
5054 END IF;
5055 END Inventory_source;
5056
5057
5058
5059 --+=======================================================================9+
5060 --| API Name : supplier_source |
5061 --| TYPE : Group |
5062 --| Notes : This procedure |
5063 --| |
5064 --| HISTORY |
5065 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5066 --| Bug 4165704 |
5067 --| M. Grosser 28-Feb-2006 Bug 5016617 - Added retrieval of supplier name|
5068 --+========================================================================+
5069 PROCEDURE Supplier_source (
5070 p_supplier_id IN NUMBER
5071 ,p_po_header_id IN NUMBER
5072 ,p_po_line_id IN NUMBER
5073 ,p_receipt_id IN NUMBER
5074 ,p_receipt_line_id IN NUMBER
5075 ,p_supplier_site_id IN NUMBER
5076 ,p_org_id IN NUMBER
5077 ,p_organization_id IN NUMBER
5078 ,p_subinventory IN VARCHAR2
5079 ,x_display IN OUT NOCOPY sample_display_rec)
5080
5081 IS
5082 -- M. Grosser 28-Feb-2006 Bug 5016617 - Added retrieval of supplier name
5083 CURSOR Cur_get_supplier IS
5084 SELECT SEGMENT1, VENDOR_NAME
5085 FROM PO_VENDORS
5086 WHERE vendor_id = p_supplier_id;
5087
5088 CURSOR Cur_get_po IS
5089 SELECT SEGMENT1
5090 FROM PO_HEADERS_ALL
5091 WHERE PO_HEADER_ID = p_PO_HEADER_ID;
5092
5093 CURSOR Cur_get_po_line_info IS
5094 SELECT line_num
5095 FROM po_lines_all
5096 WHERE po_line_id = p_po_line_id;
5097
5098 CURSOR Cur_get_receipt_info IS
5099 SELECT rsh.receipt_num receipt
5100 FROM rcv_shipment_headers rsh
5101 WHERE rsh.shipment_header_id = p_receipt_id;
5102
5103 CURSOR Cur_get_receipt_line_info IS
5104 SELECT rsh.receipt_num receipt_no,
5105 rsl.line_num receipt_line_num
5106 FROM rcv_shipment_lines rsl ,
5107 rcv_shipment_headers rsh
5108 WHERE rsl.po_header_id = p_po_header_id
5109 AND rsl.po_line_id = p_po_line_id
5110 AND rsl.shipment_header_id = rsh.shipment_header_id
5111 AND rsh.shipment_header_id = p_receipt_id ;
5112
5113 CURSOR Cur_get_site IS
5114 SELECT vendor_site_code
5115 FROM po_vendor_sites_all
5116 WHERE vendor_site_id = p_supplier_site_id;
5117
5118 /*CURSOR Cur_operating_unit IS
5119 SELECT name
5120 FROM HR_OPERATING_UNITS
5121 WHERE organization_id = p_org_id;*/
5122
5123 -- Bug# 5226352
5124 -- Commented the above cursor definition and modified to fix performance issues
5125 CURSOR Cur_operating_unit IS
5126 SELECT OTL.name
5127 FROM HR_ALL_ORGANIZATION_UNITS_TL OTL,
5128 HR_ORGANIZATION_INFORMATION O2
5129 WHERE OTL.organization_id = p_org_id
5130 AND OTL.ORGANIZATION_ID = O2.ORGANIZATION_ID
5131 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
5132 AND O2.ORG_INFORMATION2 = 'Y'
5133 AND OTL.LANGUAGE = userenv('LANG');
5134
5135 CURSOR Cur_locator_ctrl IS
5136 SELECT locator_type -- locator control
5137 FROM mtl_secondary_inventories
5138 WHERE organization_id = p_organization_id
5139 AND secondary_inventory_name = p_subinventory;
5140
5141
5142 BEGIN
5143 IF (p_SUPPLIER_ID IS NOT NULL) THEN
5144 OPEN Cur_get_supplier;
5145 -- M. Grosser 28-Feb-2006 Bug 5016617 - Added retrieval of supplier name
5146 FETCH Cur_get_supplier INTO x_display.supplier_no,
5147 x_display.supplier_name;
5148 CLOSE Cur_get_supplier;
5149
5150 OPEN Cur_get_site;
5151 FETCH Cur_get_site INTO x_display.supplier_site;
5152 CLOSE Cur_get_site;
5153
5154 OPEN Cur_operating_unit;
5155 FETCH Cur_operating_unit INTO x_display.sup_operating_unit_name;
5156 CLOSE Cur_operating_unit;
5157
5158 END IF;
5159
5160 IF (p_po_header_id IS NOT NULL) THEN
5161 OPEN Cur_get_po;
5162 FETCH Cur_get_po INTO x_display.po_number;
5163 CLOSE Cur_get_po;
5164 END IF;
5165
5166 IF p_po_line_id is not null THEN
5167 OPEN Cur_get_po_line_info;
5168 FETCH Cur_get_po_line_info INTO x_display.po_line_no ;
5169 CLOSE Cur_get_po_line_info;
5170 END IF; -- po_line_id
5171
5172 IF p_receipt_line_id is not null THEN
5173 OPEN Cur_get_receipt_line_info;
5174 FETCH Cur_get_receipt_line_info INTO x_display.receipt,
5175 x_display.receipt_line ;
5176 CLOSE Cur_get_receipt_line_info;
5177
5178 ELSIF p_receipt_id is not null THEN
5179 OPEN Cur_get_receipt_info;
5180 FETCH Cur_get_receipt_info INTO x_display.receipt ;
5181 CLOSE Cur_get_receipt_info;
5182 END IF; -- receipt_line_id
5183
5184 IF (p_subinventory IS NOT NULL
5185 AND p_organization_id IS NOT NULL) THEN
5186 OPEN Cur_locator_ctrl;
5187 FETCH Cur_locator_ctrl INTO x_display.locator_type;
5188 CLOSE Cur_locator_ctrl;
5189 END IF;
5190
5191 END Supplier_source;
5192
5193 --+=======================================================================9+
5194 --| API Name : customer_source |
5195 --| TYPE : Group |
5196 --| Notes : This procedure |
5197 --| |
5198 --| HISTORY |
5199 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5200 --| Bug 4165704 |
5201 --+========================================================================+
5202 PROCEDURE Customer_source (
5203 p_ship_to_site_id IN NUMBER
5204 , p_org_id IN NUMBER
5205 , p_order_id IN NUMBER
5206 , p_order_line_id IN NUMBER
5207 , p_cust_id IN NUMBER
5208 ,x_display IN OUT NOCOPY sample_display_rec)
5209
5210 IS
5211 /*CURSOR Cur_operating_unit IS
5212 SELECT name
5213 FROM HR_OPERATING_UNITS
5214 WHERE organization_id = p_org_id;*/
5215
5216 -- Bug# 5226352
5217 -- Commented the above cursor definition and modified to fix performance issues
5218 CURSOR Cur_operating_unit IS
5219 SELECT OTL.name
5220 FROM HR_ALL_ORGANIZATION_UNITS_TL OTL,
5221 HR_ORGANIZATION_INFORMATION O2
5222 WHERE OTL.organization_id = p_org_id
5223 AND OTL.ORGANIZATION_ID = O2.ORGANIZATION_ID
5224 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
5225 AND O2.ORG_INFORMATION2 = 'Y'
5226 AND OTL.LANGUAGE = userenv('LANG');
5227
5228 CURSOR Cur_ship_to IS
5229 SELECT location
5230 FROM hz_cust_site_uses_all
5231 WHERE site_use_id = p_ship_to_site_id;
5232
5233 CURSOR Cur_order_number IS
5234 SELECT h.order_number,
5235 t.name
5236 FROM oe_order_headers_all h,
5237 oe_transaction_types_tl t
5238 WHERE h.header_id = p_order_id
5239 AND h.order_type_id = t.transaction_type_id
5240 AND t.language = USERENV('LANG');
5241
5242 CURSOR Cur_order_line IS
5243 SELECT l.line_number||
5244 decode(l.shipment_number,'','','.'|| l.shipment_number) ||
5245 decode(l.option_number||l.component_number||l.service_number,'','','.'||l.option_number) ||
5246 decode(l.component_number||l.service_number,'','','.'|| l.component_number) ||
5247 decode(l.service_number,'','','.'|| l.service_number)
5248 FROM oe_order_lines_all l
5249 WHERE line_id = p_order_line_id ;
5250
5251 CURSOR Cur_get_cust IS
5252 SELECT p.party_name /* cust_name */
5253 FROM hz_cust_accounts_all a,
5254 hz_parties p
5255 WHERE a.cust_account_id = p_cust_id
5256 AND a.party_id = p.party_id;
5257
5258 BEGIN
5259 IF (p_cust_id IS NOT NULL) THEN
5260 OPEN Cur_get_cust;
5261 FETCH Cur_get_cust INTO x_display.cust_name;
5262 CLOSE Cur_get_cust;
5263 END IF;
5264
5265 IF p_org_id IS NOT NULL THEN
5266 OPEN Cur_operating_unit;
5267 FETCH Cur_operating_unit INTO x_display.operating_unit_name; -- pal
5268 CLOSE Cur_operating_unit;
5269
5270 IF p_ship_to_site_id IS NOT NULL THEN
5271 OPEN Cur_ship_to;
5272 FETCH Cur_ship_to INTO x_display.ship_to_name;
5273 CLOSE Cur_ship_to;
5274 END IF; -- cur_ship_to
5275
5276 IF p_order_id IS NOT NULL THEN
5277 OPEN Cur_order_number;
5278 FETCH Cur_order_number INTO x_display.order_number,
5279 x_display.order_type;
5280 CLOSE Cur_order_number;
5281 END IF; -- Cur_order_number
5282
5283 IF p_order_line_id IS NOT NULL THEN
5284 OPEN Cur_order_line;
5285 FETCH Cur_order_line INTO x_display.order_line_no;
5286 CLOSE Cur_order_line;
5287 END IF; -- Cur_order_line
5288
5289 END IF; -- Cur_operating_unit
5290 END Customer_Source;
5291
5292
5293
5294 --+=======================================================================9+
5295 --| API Name : stability_study_source |
5296 --| TYPE : Group |
5297 --| Notes : This procedure |
5298 --| |
5299 --| HISTORY |
5300 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5301 --| Bug 4165704 |
5302 --+========================================================================+
5303 PROCEDURE Stability_study_source (
5304 p_variant_id IN NUMBER
5305 ,p_time_point_id IN NUMBER
5306 ,x_display IN OUT NOCOPY sample_display_rec)
5307 IS
5308 CURSOR Cur_stability_study IS
5309 SELECT p.organization_code,
5310 ss_no,
5311 variant_no,
5312 v.storage_locator_id, --xxx needs to changed to v.storage_locator
5313 v.STORAGE_subinventory, -- needs to be changed to v.storage_subinventory,
5314 v.resources,
5315 ri.instance_number
5316 FROM GMD_SS_VARIANTS v,
5317 GMD_STABILITY_STUDIES_B ss,
5318 GMP_RESOURCE_INSTANCES ri,
5319 MTL_PARAMETERS p
5320 WHERE variant_id = p_variant_id
5321 AND ss.ss_id = v.ss_id
5322 AND ri.instance_id(+) = v.resource_instance_id
5323 AND p.organization_id = ss.organization_id;
5324
5325 CURSOR Cur_timepoint IS
5326 SELECT tp.name , -- Time Point
5327 tp.scheduled_date
5328 FROM GMD_SS_TIME_POINTS tp
5329 WHERE tp.time_point_id = p_time_point_id;
5330
5331 /*============================================
5332 BUG#469552 - Don't get storage locator
5333 again into the output area.
5334 ============================================*/
5335 l_storage_locator NUMBER;
5336
5337 BEGIN
5338 IF (p_variant_id IS NOT NULL) THEN
5339 OPEN Cur_stability_study;
5340 FETCH Cur_stability_study INTO x_display.ss_organization_code,
5341 x_display.ss_no,
5342 x_display.variant_no,
5343 l_storage_locator,
5344 x_display.storage_subinventory,
5345 x_display.variant_resource,
5346 x_display.instance_number;
5347
5348 CLOSE Cur_stability_study;
5349 END IF;
5350
5351 -- To Fetch Timepoint
5352 IF ( p_time_point_id IS NOT NULL ) THEN
5353 OPEN Cur_timepoint;
5354 FETCH Cur_timepoint INTO x_display.time_point_name,
5355 x_display.scheduled_date;
5356 CLOSE Cur_timepoint;
5357 END IF; -- time_point_id is not null
5358 END Stability_Study_Source ;
5359
5360
5361 --+=======================================================================9+
5362 --| API Name : physical_location_source |
5363 --| TYPE : Group |
5364 --| Notes : This procedure |
5365 --| |
5366 --| HISTORY |
5367 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5368 --| Bug 4165704 |
5369 --| Susan Feinstein 31-Jan-2006 Bug 4602223: added subinventory desc |
5370 --+========================================================================+
5371 PROCEDURE Physical_location_source (
5372 p_locator_id IN NUMBER
5373 ,p_subinventory IN VARCHAR2
5374 ,p_organization_id IN NUMBER
5375 ,x_display IN OUT NOCOPY sample_display_rec)
5376
5377 IS
5378 o_display sample_display_rec;
5379
5380 BEGIN
5381 Gmd_samples_grp.Inventory_source (
5382 p_locator_id => p_locator_id
5383 , p_subinventory => p_subinventory
5384 , p_organization_id => p_organization_id
5385 , x_display => o_display);
5386
5387 X_display.locator := o_display.locator;
5388 X_display.locator_type := o_display.locator_type;
5389 X_display.subinventory_desc := o_display.subinventory_desc;
5390
5391 END; -- Physical Location Source
5392
5393
5394
5395 --+=======================================================================9+
5396 --| API Name : resource_source |
5397 --| TYPE : Group |
5398 --| Notes : This procedure gets all the resource values |
5399 --| |
5400 --| HISTORY |
5401 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5402 --| Bug 4165704 |
5403 --+========================================================================+
5404 PROCEDURE Resource_source (
5405 p_instance_id IN NUMBER
5406 ,x_display IN OUT NOCOPY sample_display_rec)
5407
5408 IS
5409 CURSOR Cur_instance IS
5410 SELECT INSTANCE_NUMBER
5411 FROM GMP_RESOURCE_INSTANCES
5412 WHERE instance_id = p_instance_id;
5413 BEGIN
5414 IF (p_instance_id IS NOT NULL) THEN
5415 OPEN Cur_instance;
5416 FETCH Cur_instance INTO x_display.instance_number;
5417 CLOSE Cur_instance;
5418 END IF;
5419 END Resource_Source ;
5420
5421
5422 --+=======================================================================9+
5423 --| API Name : wip_source |
5424 --| TYPE : Group |
5425 --| Notes : This procedure gets all the wip values for the form |
5426 --| |
5427 --| HISTORY |
5428 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5429 --| Bug 4165704
5430 --| Peter Lowe 22-Mar-2006 -Bug 4754855 changed logic for |
5431 --| retrieval of Cur_formulaline |
5432 --| Peter Lowe 14-Apr-2006 - Bug 5127352 - reversed logic of Bug 4754855|
5433 --| as QA now states that we do not need formula line no or type on |
5434 --| Samples Summary form |
5435 --+=======================================================================+
5436 PROCEDURE Wip_source (
5437 p_batch_id IN NUMBER
5438 , p_step_id IN NUMBER
5439 , p_recipe_id IN NUMBER
5440 , p_formula_id IN NUMBER
5441 , p_formulaline_id IN NUMBER
5442 , p_material_detail_id IN NUMBER
5443 , p_routing_id IN NUMBER
5444 , p_oprn_id IN NUMBER
5445 , p_inventory_item_id IN NUMBER
5446 , p_organization_id IN NUMBER
5447 ,x_display IN OUT NOCOPY sample_display_rec)
5448
5449
5450 IS
5451 CURSOR Cur_vbatch IS
5452 SELECT batch_no
5453 FROM gme_batch_header
5454 WHERE batch_id = P_batch_id;
5455
5456 CURSOR Cur_vstep IS
5457 SELECT bs.batchstep_no,
5458 o.oprn_no,
5459 o.oprn_vers
5460 FROM gme_batch_steps bs,
5461 gmd_operations o
5462 WHERE bs.oprn_id = o.oprn_id
5463 AND bs.batch_id = p_batch_id
5464 AND bs.batchstep_id = p_step_id
5465 AND bs.delete_mark = 0;
5466
5467 CURSOR Cur_vrecipe IS
5468 SELECT recipe_no, recipe_version
5469 FROM gmd_recipes
5470 WHERE recipe_id = P_recipe_id;
5471
5472 CURSOR Cur_vformula IS
5473 SELECT formula_no, formula_vers
5474 FROM fm_form_mst
5475 WHERE formula_id = P_formula_id;
5476
5477 CURSOR Cur_vrouting IS
5478 SELECT routing_no, routing_vers
5479 FROM fm_rout_hdr
5480 WHERE routing_id = P_routing_id;
5481
5482
5483 CURSOR Cur_voprn IS
5484 SELECT oprn_no,oprn_vers
5485 FROM fm_oprn_mst
5486 WHERE oprn_id = P_oprn_id;
5487
5488 -- Peter Lowe 14-Apr-2006 - Bug 5127352 - reversed logic of Bug 4754855 |
5489 -- bug 4640143: added cursors for formulaline and batch line
5490 /* CURSOR Cur_formulaline IS
5491 SELECT fd.line_no,
5492 gem.meaning
5493 FROM fm_matl_dtl fd,
5494 gem_lookups gem
5495 WHERE fd.formula_id = P_formula_id
5496 AND fd.formulaline_id = P_formulaline_id
5497 AND fd.inventory_item_id = P_inventory_item_id
5498 AND gem.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
5499 AND gem.lookup_code = fd.line_type; */
5500
5501 CURSOR Cur_batchline IS
5502 SELECT line_no,
5503 gem.meaning
5504 FROM gme_material_details md,
5505 gem_lookups gem
5506 WHERE batch_id = P_batch_id
5507 AND material_detail_id = P_material_detail_id
5508 AND inventory_item_id = P_inventory_item_id
5509 AND organization_id = P_organization_id
5510 AND gem.lookup_type = 'GMD_FORMULA_ITEM_TYPE'
5511 AND gem.lookup_code = md.line_type;
5512
5513 BEGIN
5514 IF ( P_batch_id IS NOT NULL ) THEN
5515 OPEN Cur_vbatch;
5516 FETCH Cur_vbatch INTO X_DISPLAY.batch_no;
5517 CLOSE Cur_vbatch;
5518
5519 IF (P_step_id IS NOT NULL ) THEN
5520 OPEN Cur_vstep;
5521 FETCH Cur_vstep INTO X_DISPLAY.step_no,
5522 X_DISPLAY.oprn_no,
5523 X_DISPLAY.oprn_vers;
5524 CLOSE Cur_vstep;
5525 END IF;
5526
5527 IF (P_material_detail_id IS NOT NULL ) THEN
5528 OPEN Cur_batchline;
5529 FETCH Cur_batchline INTO X_DISPLAY.formula_line,
5530 X_DISPLAY.formula_type;
5531 CLOSE Cur_batchline;
5532 END IF;
5533 END IF; -- batch_id is not null
5534
5535 -- Peter Lowe 14-Apr-2006 - Bug 5127352 - reversed logic of Bug 4754855 |
5536 -- bug 4754855 changed logic for retrieval of Cur_formulaline
5537
5538 /*IF (P_formulaline_id IS NOT NULL) THEN -- bug 4754855
5539 OPEN Cur_formulaline;
5540 FETCH Cur_formulaline INTO X_DISPLAY.formula_line,
5541 X_DISPLAY.formula_type;
5542 CLOSE Cur_formulaline;
5543 END IF; -- bug 4754855 */
5544
5545
5546 IF ( P_recipe_id IS NOT NULL ) THEN
5547 OPEN Cur_vrecipe;
5548 FETCH Cur_vrecipe INTO X_DISPLAY.recipe_no,
5549 X_DISPLAY.recipe_version;
5550 CLOSE Cur_vrecipe;
5551 END IF;
5552
5553 IF ( P_formula_id IS NOT NULL ) THEN
5554 OPEN Cur_vformula;
5555 FETCH Cur_vformula INTO X_DISPLAY.formula_no,
5556 X_DISPLAY.formula_vers;
5557 CLOSE Cur_vformula;
5558 END IF;
5559
5560 IF ( P_routing_id IS NOT NULL ) THEN
5561 OPEN Cur_vrouting;
5562 FETCH Cur_vrouting INTO X_DISPLAY.routing_no,
5563 X_DISPLAY.routing_vers;
5564 CLOSE Cur_vrouting;
5565 END IF;
5566
5567 IF ( P_oprn_id IS NOT NULL ) THEN
5568 OPEN Cur_voprn;
5569 FETCH Cur_voprn INTO X_DISPLAY.oprn_no,
5570 X_DISPLAY.oprn_vers;
5571 CLOSE Cur_voprn;
5572 END IF;
5573
5574
5575 END WIP_Source;
5576
5577
5578 --+=======================================================================9+
5579 --| API Name : get_sample_spec_disposition |
5580 --| TYPE : Group |
5581 --| Notes : This procedure |
5582 --| |
5583 --| HISTORY |
5584 --| Susan Feinstein 07-Jan-2005 Created for inventory convergence |
5585 --| Bug 4165704 |
5586 --+========================================================================+
5587 PROCEDURE get_sample_spec_disposition
5588 ( p_sample IN OUT NOCOPY SAMPLE_SOURCE_REC
5589 , x_return_status OUT NOCOPY VARCHAR2
5590 ) IS
5591
5592 CURSOR Cur_get_sample_disposition IS
5593 SELECT b.disposition,
5594 d.meaning sample_disposition,
5595 e.meaning source
5596 FROM gmd_sample_spec_disp b,
5597 gmd_event_spec_disp c,
5598 gmd_samples s,
5599 fnd_lookup_values_vl d,
5600 fnd_lookup_values_vl e
5601 WHERE b.sample_id = p_sample.sample_id
5602 and b.event_spec_disp_id = c.event_spec_disp_id
5603 and c.spec_used_for_lot_attrib_ind = 'Y'
5604 and b.disposition = d.lookup_code
5605 and d.lookup_type = 'GMD_QC_SAMPLE_DISP'
5606 and e.lookup_type = 'GMD_QC_SOURCE'
5607 and s.sample_id = b.sample_id
5608 and e.lookup_code = s.source
5609 UNION
5610 SELECT b.disposition,
5611 d.meaning sample_disposition,
5612 e.meaning source
5613 FROM gmd_sample_spec_disp b,
5614 gmd_event_spec_disp c,
5615 gmd_samples s,
5616 fnd_lookup_values d,
5617 fnd_lookup_values e
5618 WHERE b.sample_id =p_sample.sample_id
5619 and b.event_spec_disp_id = c.event_spec_disp_id
5620 and c.spec_used_for_lot_attrib_ind = 'Y'
5621 and b.disposition = d.lookup_code
5622 and d.lookup_type = 'GMD_QC_SAMPLE_DISP'
5623 and e.lookup_type = 'GMD_QC_MONITOR_RULE_TYPE'
5624 and s.sample_id = b.sample_id
5625 and e.lookup_code = s.source ;
5626
5627 BEGIN
5628 IF (NVL(p_sample.sample_id,0) <> 0) THEN
5629 OPEN Cur_get_sample_disposition;
5630 FETCH Cur_get_sample_disposition INTO p_sample.disposition,
5631 p_sample.sample_disposition_desc,
5632 p_sample.sample_source_desc;
5633 CLOSE Cur_get_sample_disposition;
5634 END IF;
5635
5636 EXCEPTION
5637 WHEN FND_API.G_EXC_ERROR THEN
5638 x_return_status := FND_API.G_RET_STS_ERROR ;
5639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5641 WHEN OTHERS THEN
5642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5643
5644 END get_sample_spec_disposition;
5645
5646
5647 END GMD_SAMPLES_GRP;
5648
[Home] [Help]