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