[Home] [Help]
PACKAGE BODY: APPS.GMD_AUTO_SAMPLE_PKG
Source
1 PACKAGE BODY GMD_AUTO_SAMPLE_PKG AS
2 /* $Header: GMDQMASB.pls 120.7.12020000.2 2012/07/17 10:09:59 mtou ship $ */
3
4 PROCEDURE create_samples (x_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE,
5 L_spec_id number,
6 L_spec_vr_id number,
7 X_return_status OUT NOCOPY varchar2) IS
8
9 p_sample GMD_SAMPLES%ROWTYPE;
10 x_sample GMD_SAMPLES%ROWTYPE;
11 p_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
12 x_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
13 p_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
14 x_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
15 smp_cnt binary_integer;
16 l_reserve_cnt_req number := 0;
17 l_archive_cnt_req number := 0;
18 l_sample_cnt_req number := 0;
19 l_sample_cnt_req2 number := 0; -- Bug 4896237
20 sample_instance number := 0;
21 l_sample_qty number := 0;
22 l_sample_qty_uom varchar2(10) ;
23 l_reserve_qty number := 0 ;
24 l_archive_qty number := 0 ;
25 l_log varchar2(4000);
26 l_inv_trans_ind varchar2(2) := 'N' ;
27
28 -- Bug 4165704: new item table used for inventory convergence
29 cursor get_item_desc (x_inventory_item_id number) is
30 --select nvl(item_desc1, '')
31 --from ic_item_mst
32 --where item_id = x_item_id_in ;
33 --QZENG Bug 13089703: cut length of item_desc to 80 to support sample_desc Varchar2(80) in table GMD_SAMPLES
34 SELECT substrb(nvl(description, ''),1,80)
35 --QZENG Bug 13881118 Change view from mtl_system_items_b_kfv to mtl_system_items_vl to support NLS
36 --FROM mtl_system_items_b_kfv
37 FROM mtl_system_items_vl
38 WHERE organization_id = x_sampling_event.organization_id
39 AND inventory_item_id = x_inventory_item_id;
40
41 cursor sampling_plan_info (x_sampling_plan_id number) is
42 select nvl(sample_cnt_req, 0) sample_cnt_req,
43 nvl(RESERVE_CNT_REQ, 0) reserve_cnt_req,
44 nvl(ARCHIVE_CNT_REQ,0) archive_cnt_req,
45 nvl(sample_qty, 0) sample_qty, sample_qty_uom,
46 nvl(RESERVE_QTY, 0) reserve_qty,
47 nvl(ARCHIVE_QTY,0) archive_qty
48 from gmd_sampling_plans_b sm
49 where sm.sampling_plan_id = x_sampling_plan_id ;
50
51 -- bug 4924526 SQL Id 14689707 - fix this 13,582,446 shared memory FTS -
52 cursor get_vr_info (x_spec_vr_id number) is
53 /* select nvl (SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
54 from gmd_all_spec_vrs
55 where spec_vr_id = x_spec_vr_id ; */
56 SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
57 FROM GMD_INVENTORY_SPEC_VRS V ,
58 GMD_SPECIFICATIONS_B S ,
59 MTL_SYSTEM_ITEMS_KFV I ,
60 GMD_QC_STATUS_TL T ,
61 GMD_QC_STATUS_TL P
62 WHERE V.SPEC_ID = S.SPEC_ID
63 AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
64 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
65 AND V.SPEC_VR_STATUS = T.STATUS_CODE
66 AND T.ENTITY_TYPE = 'S'
67 AND T.LANGUAGE = USERENV ( 'LANG' )
68 AND S.SPEC_STATUS = P.STATUS_CODE
69 AND P.ENTITY_TYPE = 'S'
70 AND P.LANGUAGE = USERENV ( 'LANG' )
71 and v.spec_vr_id = x_spec_vr_id
72 UNION
73 SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
74 FROM GMD_WIP_SPEC_VRS V ,
75 GMD_SPECIFICATIONS_B S ,
76 MTL_SYSTEM_ITEMS_KFV I ,
77 GMD_QC_STATUS_TL T ,
78 GMD_QC_STATUS_TL P
79 WHERE V.SPEC_ID = S.SPEC_ID
80 AND I.ORGANIZATION_ID = S.OWNER_ORGANIZATION_ID
81 AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
82 AND V.SPEC_VR_STATUS = T.STATUS_CODE
83 AND T.ENTITY_TYPE = 'S'
84 AND T.LANGUAGE = USERENV ( 'LANG' )
85 AND S.SPEC_STATUS = P.STATUS_CODE
86 AND P.ENTITY_TYPE = 'S'
87 AND P.LANGUAGE = USERENV ( 'LANG' )
88 and v.spec_vr_id = x_spec_vr_id
89 UNION
90 SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
91 FROM GMD_CUSTOMER_SPEC_VRS V ,
92 GMD_SPECIFICATIONS_B S ,
93 MTL_SYSTEM_ITEMS_KFV I ,
94 GMD_QC_STATUS_TL T ,
95 GMD_QC_STATUS_TL P
96 WHERE V.SPEC_ID = S.SPEC_ID
97 AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
98 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
99 AND V.SPEC_VR_STATUS = T.STATUS_CODE
100 AND T.ENTITY_TYPE = 'S'
101 AND T.LANGUAGE = USERENV ( 'LANG' )
102 AND S.SPEC_STATUS = P.STATUS_CODE -- NEW
103 AND P.ENTITY_TYPE = 'S' -- NEW
104 AND P.LANGUAGE = USERENV ( 'LANG' ) -- NEW
105 and v.spec_vr_id = x_spec_vr_id
106 UNION
107 SELECT nvl (v.SAMPLE_INV_TRANS_IND, 'N') SAMPLE_INV_TRANS_IND
108 FROM GMD_SUPPLIER_SPEC_VRS V ,
109 GMD_SPECIFICATIONS_B S ,
110 MTL_SYSTEM_ITEMS_KFV I ,
111 GMD_QC_STATUS_TL T ,
112 GMD_QC_STATUS_TL P
113 WHERE V.SPEC_ID = S.SPEC_ID
114 AND S.OWNER_ORGANIZATION_ID = I.ORGANIZATION_ID
115 AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
116 AND V.SPEC_VR_STATUS = T.STATUS_CODE
117 AND T.ENTITY_TYPE = 'S'
118 AND T.LANGUAGE = USERENV ( 'LANG' )
119 AND S.SPEC_STATUS = P.STATUS_CODE
120 AND P.ENTITY_TYPE = 'S'
121 AND P.LANGUAGE = USERENV ( 'LANG' )
122 and v.spec_vr_id = x_spec_vr_id
123 UNION
124 SELECT 'N' SAMPLE_INV_TRANS_IND
125 FROM GMD_MONITORING_SPEC_VRS V ,
126 GMD_SPECIFICATIONS_B S ,
127 GMD_QC_STATUS_TL P ,
128 GMD_QC_STATUS_TL T
129 WHERE V.SPEC_ID = S.SPEC_ID
130 AND V.SPEC_VR_STATUS = T.STATUS_CODE
131 AND T.ENTITY_TYPE = 'S'
132 AND T.LANGUAGE = USERENV ( 'LANG' )
133 AND S.SPEC_STATUS = P.STATUS_CODE
134 AND P.ENTITY_TYPE = 'S'
135 AND P.LANGUAGE = USERENV ( 'LANG' )
136 and v.spec_vr_id = x_spec_vr_id
137 UNION
138 SELECT 'N' SAMPLE_INV_TRANS_IND
139 FROM GMD_STABILITY_SPEC_VRS V ,
140 GMD_SPECIFICATIONS_B S ,
141 GMD_QC_STATUS_TL T ,
142 GMD_QC_STATUS_TL L
143 WHERE V.SPEC_ID = S.SPEC_ID
144 AND V.SPEC_VR_STATUS = T.STATUS_CODE
145 AND S.SPEC_STATUS = L.STATUS_CODE
146 AND T.ENTITY_TYPE = 'S'
147 AND L.ENTITY_TYPE = 'S'
148 AND T.LANGUAGE = USERENV ( 'LANG' )
149 AND L.LANGUAGE = USERENV ( 'LANG' )
150 and v.spec_vr_id = x_spec_vr_id;
151
152 BEGIN
153
154 -- Begin bug#6276012
155 fnd_global.apps_initialize (user_id => x_sampling_event.last_updated_by,
156 resp_id => NULL,
157 resp_appl_id => NULL
158 );
159 fnd_profile.initialize (x_sampling_event.last_updated_by);
160 --End bug#6276012
161 /* Update created sampling event */
162 p_event_spec_disp.sampling_event_id := x_sampling_event.sampling_event_id;
163 p_event_spec_disp.SAMPLING_EVENT_ID:= x_sampling_event.sampling_event_id;
164 p_event_spec_disp.SPEC_ID := l_spec_id;
165 p_event_spec_disp.SPEC_VR_ID := l_spec_vr_id;
166 p_event_spec_disp.DISPOSITION := '0PL';
167 p_event_spec_disp.SPEC_USED_FOR_LOT_ATTRIB_IND := 'Y';
168 p_event_spec_disp.DELETE_MARK := 0;
169 p_event_spec_disp.CREATION_DATE := sysdate;
170 p_event_spec_disp.CREATED_BY := FND_GLOBAL.USER_ID;
171 p_event_spec_disp.LAST_UPDATE_DATE := sysdate;
172 p_event_spec_disp.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
173
174
175 IF NOT GMD_EVENT_SPEC_DISP_PVT.insert_row(
176 p_event_spec_disp =>p_event_spec_disp,
177 x_event_spec_disp =>x_event_spec_disp) THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181
182 open get_vr_info (l_spec_vr_id) ;
183 fetch get_vr_info into l_inv_trans_ind ;
184 close get_vr_info ;
185
186 open sampling_plan_info (x_Sampling_event.sampling_plan_id );
187 fetch sampling_plan_info into l_sample_cnt_req2, l_reserve_cnt_req, l_archive_cnt_req,
188 l_sample_qty, l_sample_qty_uom, l_reserve_qty, l_archive_qty;
189 close sampling_plan_info ;
190
191 -- Bug 4896237. svankada. Use l_sample_cnt_req from x_sampling_event passed as that has reference to transaction
192 -- quantities for quantity based sampling plans.
193 l_sample_cnt_req := x_sampling_event.sample_req_cnt;
194 IF NVL(l_sample_cnt_req , 0) = 0 THEN
195 l_sample_cnt_req := l_sample_cnt_req2;
196 END IF;
197 -- End Bug 4896237. svankada
198
199 FOR smp_cnt in 1..l_sample_cnt_req LOOP
200 /* Create a Regular sample */
201 sample_instance := sample_instance + 1;
202 p_sample.sampling_event_id := x_sampling_event.sampling_event_id;
203 p_sample.sample_qty := l_sample_qty;
204 p_sample.sample_qty_uom := l_sample_qty_uom;
205 p_sample.sample_inv_trans_ind := l_inv_trans_ind ;
206 p_sample.source := x_sampling_event.source;
207 p_sample.sample_type := 'I';
208 p_sample.retain_as := NULL;
209 -- Bug 4165704: sample no now taken from quality parameters table
210 -- p_sample.sample_no :=
211 -- GMA_GLOBAL_GRP.Get_Doc_No('SMPL', x_sampling_event.ORGN_CODE);
212 p_sample.sample_no := GMD_QUALITY_PARAMETERS_GRP.get_next_sample_no(x_sampling_event.organization_id);
213 p_Sample.inventory_item_id := x_sampling_event.inventory_item_id ;
214 p_Sample.revision := x_sampling_event.revision;
215 p_sample.delete_mark := 0;
216 p_sample.creation_date := sysdate;
217 p_sample.created_by := FND_GLOBAL.USER_ID;
218 p_sample.last_update_date := sysdate;
219 p_sample.last_updated_by := FND_GLOBAL.USER_ID;
220 p_sample.sampler_id := FND_GLOBAL.USER_ID;
221
222 p_sample.priority := '5N' ;
223 p_sample.remaining_qty := p_sample.sample_qty;
224
225 --RLNAGARA B5463399 Commented the below line and added next line so that the correct operating unit get populated.
226 --p_sample.org_id := p_sample.org_id;
227 p_sample.org_id := x_sampling_event.org_id;
228
229 p_sample.source := x_sampling_event.source ;
230 p_sample.supplier_id := x_sampling_event.supplier_id ;
231 p_sample.supplier_site_id := x_sampling_event.supplier_site_id ;
232 p_sample.po_header_id := x_sampling_event.po_header_id ;
233 p_sample.po_line_id := x_sampling_event.po_line_id ;
234 p_sample.sample_type := x_sampling_event.sample_type ;
235 p_sample.organization_id := x_sampling_event.organization_id ;
236 p_sample.receipt_id := x_sampling_event.receipt_id ; /*Bug 3378697*/
237 p_sample.receipt_line_id := x_sampling_event.receipt_line_id ; /*Bug 3378697*/
238 p_sample.lot_number := x_sampling_event.lot_number ;
239 p_sample.parent_lot_number := x_sampling_event.parent_lot_number ;
240 p_sample.supplier_lot_no := x_sampling_event.supplier_lot_no; --Bug#6491872
241 --srakrish bug 5844806: Populating the WIP fields properly.
242 --p_sample.subinventory := x_sampling_event.subinventory ;
243 --p_sample.locator_id := x_sampling_event.locator_id ;
244 IF x_sampling_event.source = 'W' THEN
245 p_sample.source_locator_id := x_sampling_event.locator_id ;
246 p_sample.source_subinventory := x_sampling_event.subinventory ;
247 ELSE
248 p_sample.locator_id := x_sampling_event.locator_id ;
249 p_sample.subinventory := x_sampling_event.subinventory ;
250 END IF;
251 p_sample.lot_retest_ind := x_sampling_event.lot_retest_ind ;
252 p_sample.batch_ID := x_sampling_event.batch_ID ;
253 p_sample.recipe_ID := x_sampling_event.recipe_ID ;
254 p_sample.formula_id := x_sampling_event.formula_id ;
255 p_sample.formulaline_id := x_sampling_event.formulaline_id ;
256 p_sample.material_detail_id := x_sampling_event.material_detail_id ;
257 p_sample.routing_id := x_sampling_event.routing_id ;
258 p_sample.step_id := x_sampling_event.step_id ;
259 p_sample.step_no := x_sampling_event.step_no ;
260 p_sample.oprn_id := x_sampling_event.oprn_id ;
261 p_sample.sample_disposition := '0PL' ;
262 /* Get item Desc and default it in sample */
263 open get_item_desc (x_sampling_event.inventory_item_id) ;
264 fetch get_item_desc into p_sample.sample_desc ;
265 close get_item_desc;
266
267 p_sample.sample_instance := sample_instance;
268 IF not GMD_SAMPLES_PVT.insert_row (
269 p_sample, x_sample ) THEN
270 raise fnd_api.g_exc_error;
271 END IF;
272
273 p_sample_spec_disp.sample_id := x_sample.sample_id;
274 p_sample_spec_disp.event_spec_disp_id := x_event_spec_disp.event_spec_disp_id;
275 p_sample_spec_disp.disposition := '0PL';
276 p_sample_spec_disp.delete_mark := 0;
277 p_sample_spec_disp.creation_date := sysdate;
278 p_sample_spec_disp.created_by := FND_GLOBAL.USER_ID;
279 p_sample_spec_disp.last_update_date := sysdate;
280 p_sample_spec_disp.last_updated_by := FND_GLOBAL.USER_ID;
281
282 IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
283 p_sample_spec_disp ) THEN
284 raise fnd_api.g_exc_error;
285 END IF;
286 --gml_sf_log('creating sample');
287 END LOOP;
288
289 FOR smp_cnt in 1..l_archive_cnt_req LOOP
290 /* Create a Archive sample */
291 sample_instance := sample_instance + 1;
292 p_sample.sample_qty := l_archive_qty;
293 p_sample.sample_qty_uom := l_sample_qty_uom;
294 p_sample.sample_inv_trans_ind := l_inv_trans_ind ;
295 p_sample.sampling_event_id := x_sampling_event.sampling_event_id;
296 -- Bug 4165704: sample no now taken from quality parameters table
297 -- p_sample.sample_no :=
298 -- GMA_GLOBAL_GRP.Get_Doc_No('SMPL', x_sampling_event.ORGN_CODE);
299 p_sample.sample_no := GMD_QUALITY_PARAMETERS_GRP.get_next_sample_no(x_sampling_event.organization_id);
300 p_Sample.inventory_item_id := x_sampling_event.inventory_item_id ;
301 p_Sample.revision := x_sampling_event.revision;
302 p_sample.delete_mark := 0;
303 p_sample.creation_date := sysdate;
304 p_sample.created_by := FND_GLOBAL.USER_ID;
305 p_sample.last_update_date := sysdate;
306 p_sample.last_updated_by := FND_GLOBAL.USER_ID;
307 p_sample.sampler_id := FND_GLOBAL.USER_ID;
308
309 p_sample.priority := '5N' ;
310 p_sample.remaining_qty := p_sample.sample_qty;
311 --RLNAGARA B5463399 Commented the below line and added next line so that the correct operating unit get populated.
312 --p_sample.org_id := p_sample.org_id;
313 p_sample.org_id := x_sampling_event.org_id;
314
315 p_sample.source := x_sampling_event.source ;
316 p_sample.supplier_id := x_sampling_event.supplier_id ;
317 p_sample.supplier_site_id := x_sampling_event.supplier_site_id ;
318 p_sample.po_header_id := x_sampling_event.po_header_id ;
319 p_sample.po_line_id := x_sampling_event.po_line_id ;
320 p_sample.sample_type := x_sampling_event.sample_type ;
321 p_sample.organization_id := x_sampling_event.organization_id ;
322 p_sample.receipt_id := x_sampling_event.receipt_id ;
323 p_sample.receipt_line_id := x_sampling_event.receipt_line_id ;
324 p_sample.lot_number := x_sampling_event.lot_number ;
325 p_sample.parent_lot_number := x_sampling_event.parent_lot_number ;
326 p_sample.supplier_lot_no := x_sampling_event.supplier_lot_no; --Bug#6491872
327 --srakrish bug 5844806: Populating the WIP fields properly.
328 --p_sample.subinventory := x_sampling_event.subinventory ;
329 --p_sample.locator_id := x_sampling_event.locator_id ;
330 IF x_sampling_event.source = 'W' THEN
331 p_sample.source_locator_id := x_sampling_event.locator_id ;
332 p_sample.source_subinventory := x_sampling_event.subinventory ;
333 ELSE
334 p_sample.locator_id := x_sampling_event.locator_id ;
335 p_sample.subinventory := x_sampling_event.subinventory ;
336 END IF;
337 p_sample.lot_retest_ind := x_sampling_event.lot_retest_ind ;
338 p_sample.batch_ID := x_sampling_event.batch_ID ;
339 p_sample.recipe_ID := x_sampling_event.recipe_ID ;
340 p_sample.formula_id := x_sampling_event.formula_id ;
341 p_sample.formulaline_id := x_sampling_event.formulaline_id ;
342 p_sample.material_detail_id := x_sampling_event.material_detail_id ;
343 p_sample.routing_id := x_sampling_event.routing_id ;
344 p_sample.step_id := x_sampling_event.step_id ;
345 p_sample.step_no := x_sampling_event.step_no ;
346 p_sample.oprn_id := x_sampling_event.oprn_id ;
347
348
349 /* Get item Desc and default it in sample */
350 open get_item_desc (x_sampling_event.inventory_item_id) ;
351 fetch get_item_desc into p_sample.sample_desc ;
352 close get_item_desc;
353
354 p_sample.sample_instance := sample_instance;
355 p_sample.retain_as := 'A' ;
356 p_sample.sample_disposition := '0PL' ;
357
358 IF not GMD_SAMPLES_PVT.insert_row (
359 p_sample, x_sample ) THEN
360 raise fnd_api.g_exc_error;
361 END IF;
362
363 p_sample_spec_disp.sample_id := x_sample.sample_id;
364 p_sample_spec_disp.event_spec_disp_id := x_event_spec_disp.event_spec_disp_id;
365 p_sample_spec_disp.disposition := '0PL';
366
367 IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
368 p_sample_spec_disp ) THEN
369 raise fnd_api.g_exc_error;
370 END IF;
371 END LOOP;
372
373 FOR smp_cnt in 1..l_reserve_cnt_req LOOP
374 /* Create a Reserve sample */
375 sample_instance := sample_instance + 1;
376 p_sample.sample_qty := l_reserve_qty;
377 p_sample.sample_qty_uom := l_sample_qty_uom;
378 p_sample.sample_inv_trans_ind := l_inv_trans_ind ;
379 p_sample.sampling_event_id := x_sampling_event.sampling_event_id;
380 -- Bug 4165704: sample no now taken from quality parameters table
381 -- p_sample.sample_no :=
382 -- GMA_GLOBAL_GRP.Get_Doc_No('SMPL', x_sampling_event.ORGN_CODE);
383 p_sample.sample_no := GMD_QUALITY_PARAMETERS_GRP.get_next_sample_no(x_sampling_event.organization_id);
384 p_Sample.inventory_item_id := x_sampling_event.inventory_item_id ;
385 p_Sample.revision := x_sampling_event.revision;
386 p_sample.delete_mark := 0;
387 p_sample.creation_date := sysdate;
388 p_sample.created_by := FND_GLOBAL.USER_ID;
389 p_sample.last_update_date := sysdate;
390 p_sample.last_updated_by := FND_GLOBAL.USER_ID;
391 p_sample.sampler_id := FND_GLOBAL.USER_ID;
392
393 p_sample.priority := '5N' ;
394 p_sample.remaining_qty := p_sample.sample_qty;
395
396 --RLNAGARA B5463399 Commented the below line and added next line so that the correct operating unit get populated.
397 --p_sample.org_id := p_sample.org_id;
398 p_sample.org_id := x_sampling_event.org_id;
399
400 p_sample.source := x_sampling_event.source ;
401 p_sample.supplier_id := x_sampling_event.supplier_id ;
402 p_sample.supplier_site_id := x_sampling_event.supplier_site_id ;
403 p_sample.po_header_id := x_sampling_event.po_header_id ;
404 p_sample.po_line_id := x_sampling_event.po_line_id ;
405 p_sample.sample_type := x_sampling_event.sample_type ;
406 p_sample.organization_id := x_sampling_event.organization_id ;
407 p_sample.receipt_id := x_sampling_event.receipt_id ;
408 p_sample.receipt_line_id := x_sampling_event.receipt_line_id ;
409 p_sample.lot_number := x_sampling_event.lot_number ;
410 p_sample.parent_lot_number := x_sampling_event.parent_lot_number ;
411 p_sample.supplier_lot_no := x_sampling_event.supplier_lot_no; --Bug#6491872
412 --srakrish bug 5844806: Populating the WIP fields properly.
413 --p_sample.subinventory := x_sampling_event.subinventory ;
414 --p_sample.locator_id := x_sampling_event.locator_id ;
415 IF x_sampling_event.source = 'W' THEN
416 p_sample.source_locator_id := x_sampling_event.locator_id ;
417 p_sample.source_subinventory := x_sampling_event.subinventory ;
418 ELSE
419 p_sample.locator_id := x_sampling_event.locator_id ;
420 p_sample.subinventory := x_sampling_event.subinventory ;
421 END IF;
422 p_sample.lot_retest_ind := x_sampling_event.lot_retest_ind ;
423 p_sample.batch_ID := x_sampling_event.batch_ID ;
424 p_sample.recipe_ID := x_sampling_event.recipe_ID ;
425 p_sample.formula_id := x_sampling_event.formula_id ;
426 p_sample.formulaline_id := x_sampling_event.formulaline_id ;
427 p_sample.material_detail_id := x_sampling_event.material_detail_id ;
428 p_sample.routing_id := x_sampling_event.routing_id ;
429 p_sample.step_id := x_sampling_event.step_id ;
430 p_sample.step_no := x_sampling_event.step_no ;
431 p_sample.oprn_id := x_sampling_event.oprn_id ;
432
433 /* Get item Desc and default it in sample */
434 open get_item_desc (x_sampling_event.inventory_item_id) ;
435 fetch get_item_desc into p_sample.sample_desc ;
436 close get_item_desc;
437
438 p_sample.sample_instance := sample_instance;
439 p_sample.retain_as := 'R' ;
440 p_sample.sample_disposition := '0PL' ;
441
442 IF not GMD_SAMPLES_PVT.insert_row (
443 p_sample, x_sample ) THEN
444 raise fnd_api.g_exc_error;
445 END IF;
446
447 p_sample_spec_disp.sample_id := x_sample.sample_id;
448 p_sample_spec_disp.event_spec_disp_id := x_event_spec_disp.event_spec_disp_id;
449 p_sample_spec_disp.disposition := '0PL';
450
451 IF not GMD_SAMPLE_SPEC_DISP_PVT.insert_row (
452 p_sample_spec_disp ) THEN
453 raise fnd_api.g_exc_error;
454 END IF;
455 END LOOP;
456
457 /* Update the sampling event samples taken */
458 update gmd_sampling_events
459 set SAMPLE_TAKEN_CNT = 0
460 where sampling_event_id = x_sampling_event.sampling_event_id ;
461
462 update gmd_sampling_events
463 set ARCHIVED_TAKEN = 0
464 where sampling_event_id = x_sampling_event.sampling_event_id ;
465
466 update gmd_sampling_events
467 set RESERVED_TAKEN = 0
468 where sampling_event_id = x_sampling_event.sampling_event_id ;
469
470 update gmd_sampling_events
471 set SAMPLE_REQ_CNT = l_sample_cnt_req
472 where sampling_event_id = x_sampling_event.sampling_event_id ;
473
474 /* Update the sampling event disposition to Planned */
475 update gmd_sampling_events
476 set disposition = '0PL'
477 where sampling_event_id = x_sampling_event.sampling_event_id ;
478 --gml_sf_log('return success - created samples');
479
480 x_return_status := 'SUCCESS';
481
482 EXCEPTION
483 WHEN OTHERS THEN
484 WF_CORE.CONTEXT ('GMD_AUTO_SAMPLE_PKG','CREATING AUTO SAMPLES',0,0,l_log );
485 raise;
486 END create_samples;
487
488
489 END GMD_AUTO_SAMPLE_PKG;