1 PACKAGE BODY GMD_SAMPLES_PUB AS
2 /* $Header: GMDPSMPB.pls 120.20.12020000.3 2013/04/01 20:34:44 plowe ship $
3 *****************************************************************
4 * *
5 * Package GMD_SAMPLES_PUB *
6 * *
7 * Contents CREATE_SAMPLES *
8 * DELETE_SAMPLES *
9 * *
10 * *
11 * Use This is the public layer for the QC SAMPLES *
12 * *
13 * History *
14 * Written by H Verdding, OPM Development (EMEA) *
15 * *
16 * Updated By For *
17 * *
18 * HVerddin B2711643: Added call to set user_context *
19 * *
20 * 10-APR-2003 H.Verdding -- Added the following Validation *
21 * -- Validate_item_controls *
22 * -- Validate_inv_sample *
23 * -- Validate_wip_sample *
24 * -- Validate_cust_sample *
25 * -- Validate_supp_sample *
26 * -- Validate_sample *
27 * *
28 * 20-Mar-2003 Chetan Nagar In error message GMD_SAMPLE_SOURCE_INVALID
29 * pass proper column SOURCE *
30 *
31 * 19-NOV-2003 M. Anil Kumar Bug#3256248 *
32 * Modified cursor c_batch in validate_wip_sample procedure to *
33 * consider completed batches also. *
34 *
35 * 27-JAN-2004 S. Feinstein Bug #3401377
36 * Updated for Mini Pack K (API Version 2.0)
37 * Added the following Validations
38 * -- Validate_stability_sample
39 * -- Validate_resource_sample
40 * -- Validate_location_sample
41 *
42 * 03-JUN-2004 Saikiran Vankadari Bug# 3576573. added
43 * validations for receipt information
44 * in 'VALIDATE_SUPP_SAMPLE' procedure
45 *
46 * 20-MAY-2005 Susan Feinstein Bug 4165704 Inventory Convergence
47 * 18-OCT-2005 Susan Feinstein Bug 4640143 Added material detail id to gmd_wip_spec_vrs table
48 * 05-JAN-2006 Joe DiIorio Bug#4691545 Removed profile reference to
49 * NVL(fnd_profile.value('QC$EXACTSPECMATCH'),'N');
50 * and replaced with call to gmd_quality_config.
51 * Added FUNCTION GET_CONF_MATCH_VALUE as
52 * profile needs to be retrieved for each org that
53 * is passed.
54 * 18-Jan-2006 Saikiran Fixed bug# 4916871
55 * 23-MAR-2006 Peter Lowe FP of 4359797 - 4619570 added code so
56 * that samples are now created for Closed batches
57 * depending upon the profile option but inv is not updated
58 * 07-JUN-2006 Peter Lowe - bug 5291723 - check for plant code is obsolete - replace
59 * organization 5291495
60 * 09-JUN-2006 Peter Lowe - bug 5291495 - Make sure that the input UOM for the sample qty is validated
61 * for spec type of 'I' (not 'M')
62 * 14-JUN-2006 Peter Lowe - bug 5283854 various API creation item sample errors
63 * 16-Jun-2006 PLOWE Fixed bug# 5335008 in PROCEDURE VALIDATE_CUST_SAMPLE
64 * CURSOR c_order rewritten as part of bug# 5335008
65 * 19-May-2008 PLOWE 7027149 added support for LPN
66 * 23 Feb-2009 PLOWE 8276017 - make sure SVR lot optional ind is taken into account at correct place in validation
67 * P LOWE 26-AUG-2011 Bug 12846165 - API INCORRECTLY MATCHES 2 QUALITY SAMPLES TO THE SAME SAMPLING EVENT.
68 * Modified the procedure CREATE_SAMPLES by adding new parameter create_new_sample_group default N
69 * This will always create a new sample group for the sample if set to Y
70 * QZENG 13-Jan-2012 Bug 14179187 - In CREATE_SAMPLES, added line to c_batch cursor to support item which is not in formula
71 *
72 *************************************************************************************************
73 */
74
75 -- Global variables
76
77 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_SAMPLES_PUB';
78
79 -- 5283854
80 G_LOT_CTL VARCHAR2(1);
81 G_CHILD_LOT_FLAG VARCHAR2(1);
82
83
84 -- bug# 2995114
85 -- create new local function to fetch inventory indicator
86
87 FUNCTION get_inventory_ind_from_vr( p_spec_type IN VARCHAR2,
88 p_spec_vr_id IN NUMBER )
89 RETURN VARCHAR2 IS
90
91 l_sample_inv_trans_ind VARCHAR2(1);
92
93 BEGIN
94
95 IF p_spec_vr_id IS NULL THEN
96 RETURN (NULL) ;
97 END IF;
98
99
100 -- get spec_type if it is null
101 IF p_spec_type IS NULL THEN
102 /*SELECT s.sample_inv_trans_ind INTO l_sample_inv_trans_ind
103 FROM gmd_all_spec_vrs WHERE spec_vr_id = p_spec_vr_id ;*/
104 --Query rewritten as part of bug# 4916871
105 select s.sample_inv_trans_ind INTO l_sample_inv_trans_ind
106 FROM (select spec_vr_id, sample_inv_trans_ind from gmd_inventory_spec_vrs
107 union all
108 select spec_vr_id, sample_inv_trans_ind from gmd_wip_spec_vrs
109 union all
110 select spec_vr_id, sample_inv_trans_ind from gmd_customer_spec_vrs
111 union all
112 select spec_vr_id, sample_inv_trans_ind from gmd_supplier_spec_vrs
113 union all
114 select spec_vr_id, NULL sample_inv_trans_ind from gmd_monitoring_spec_vrs
115 union all
116 select spec_vr_id, NULL sample_inv_trans_ind from gmd_stability_spec_vrs) s
117 WHERE s.spec_vr_id = p_spec_vr_id ;
118 ELSE
119 IF p_spec_type = 'I' THEN
120 SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
121 FROM gmd_inventory_spec_vrs
122 WHERE spec_vr_id = p_spec_vr_id ;
123 ELSIF p_spec_type = 'C' THEN
124 SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
125 FROM gmd_customer_spec_vrs
126 WHERE spec_vr_id = p_spec_vr_id ;
127 ELSIF p_spec_type = 'W' THEN
128 SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
129 FROM gmd_wip_spec_vrs
130 WHERE spec_vr_id = p_spec_vr_id ;
131 ELSIF p_spec_type = 'S' THEN
132 SELECT SAMPLE_INV_TRANS_IND INTO l_sample_inv_trans_ind
133 FROM gmd_supplier_spec_vrs
134 WHERE spec_vr_id = p_spec_vr_id ;
135 END IF;
136
137 END IF; -- IF p_spec_type IS NULL
138
139 RETURN (l_sample_inv_trans_ind);
140
141 EXCEPTION WHEN OTHERS THEN
142 RETURN(NULL);
143
144 END get_inventory_ind_from_vr ;
145
146
147
148 PROCEDURE CREATE_SAMPLES
149 ( p_api_version IN NUMBER
150 , p_init_msg_list IN VARCHAR2
151 , p_commit IN VARCHAR2
152 , p_validation_level IN NUMBER
153 , p_qc_samples_rec IN GMD_SAMPLES%ROWTYPE
154 , p_user_name IN VARCHAR2
155 , p_find_matching_spec IN VARCHAR2
156 , p_grade IN VARCHAR2 DEFAULT NULL --3431884
157 , p_lpn IN VARCHAR2 DEFAULT NULL -- 7027149
158 , p_create_new_sample_group IN VARCHAR2 -- 12846165
159 , x_qc_samples_rec OUT NOCOPY GMD_SAMPLES%ROWTYPE
160 , x_sampling_events_rec OUT NOCOPY GMD_SAMPLING_EVENTS%ROWTYPE
161 , x_sample_spec_disp OUT NOCOPY GMD_SAMPLE_SPEC_DISP%ROWTYPE
162 , x_event_spec_disp_rec OUT NOCOPY GMD_EVENT_SPEC_DISP%ROWTYPE
163 , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
164 , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
165 , x_return_status OUT NOCOPY VARCHAR2
166 , x_msg_count OUT NOCOPY NUMBER
167 , x_msg_data OUT NOCOPY VARCHAR2
168 )
169 IS
170 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_SAMPLES';
171 l_api_version CONSTANT NUMBER := 3.0;
172 l_msg_count NUMBER :=0;
173 l_msg_data VARCHAR2(2000);
174 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
175 l_samples_val_rec GMD_SAMPLES%ROWTYPE;
176 l_qc_samples_rec GMD_SAMPLES%ROWTYPE;
177 l_qc_samples_out_rec GMD_SAMPLES%ROWTYPE;
178 l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
179 l_event_spec_disp_rec GMD_EVENT_SPEC_DISP%ROWTYPE;
180 l_sampling_events GMD_SAMPLING_EVENTS%ROWTYPE;
181 l_sampling_events_out GMD_SAMPLING_EVENTS%ROWTYPE;
182 l_results_tab GMD_API_PUB.gmd_results_tab;
183 l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
184 l_user_id NUMBER(15);
185 l_assign_type NUMBER;
186 l_sampling_event_id NUMBER;
187 l_sample_req_cnt NUMBER;
188 l_sample_active_cnt NUMBER;
189 l_spec_vr_id NUMBER;
190 l_spec_id NUMBER;
191 l_sampling_plan_id NUMBER;
192 l_date DATE := SYSDATE;
193 l_spec_type GMD_SPECIFICATIONS.SPEC_TYPE%TYPE;
194 l_sampling_event_exist VARCHAR2(1);
195
196 -- Bug 4165704: needed to fetch values from quality config table
197 quality_config GMD_QUALITY_CONFIG%ROWTYPE;
198 found BOOLEAN;
199 l_batch_status NUMBER; -- Bug # 4619570
200 l_sample_inv_trans_ind VARCHAR2(1); -- Bug # 4619570
201 L_LOT_OPTIONAL_ON_SAMPLE VARCHAR2(1) := 'N'; -- 5283854
202 l_wms_enabled_flag VARCHAR2(1) := 'N'; -- 7027149
203 dummy NUMBER;
204 -- Cursor Definitions
205 -- Bug 4165704: no longer required
206 --CURSOR c_doc_numbering ( l_orgn_code VARCHAR2) IS
207 --SELECT assignment_type
208 --FROM sy_docs_seq
209 --WHERE orgn_code = l_orgn_code
210 --AND doc_type = 'SMPL';
211
212 /*CURSOR c_get_sample_cnt (p_spec_vr_id NUMBER)
213 IS
214 SELECT b.sample_cnt_req
215 FROM gmd_all_spec_vrs s, gmd_sampling_plans b
216 WHERE s.spec_vr_id = p_spec_vr_id
217 AND s.sampling_plan_id = b.sampling_plan_id;*/
218
219 --CURSOR c_get_sample_cnt rewritten as part of bug# 4916871
220 CURSOR c_get_sample_cnt (p_spec_vr_id NUMBER)
221 IS
222 SELECT b.sample_cnt_req
223 FROM
224 (select spec_vr_id, sampling_plan_id from gmd_inventory_spec_vrs
225 union all
226 select spec_vr_id, sampling_plan_id from gmd_wip_spec_vrs
227 union all
228 select spec_vr_id, sampling_plan_id from gmd_customer_spec_vrs
229 union all
230 select spec_vr_id, sampling_plan_id from gmd_supplier_spec_vrs
231 union all
232 select spec_vr_id, sampling_plan_id from gmd_monitoring_spec_vrs
233 union all
234 select spec_vr_id, sampling_plan_id from gmd_stability_spec_vrs) s, gmd_sampling_plans b
235 WHERE s.spec_vr_id = p_spec_vr_id
236 AND s.sampling_plan_id = b.sampling_plan_id;
237
238 -- Cursors
239 /* CURSOR Cur_replenish_whse (replenish_item_id NUMBER) IS
240 SELECT whse_code
241 FROM ps_whse_eff
242 WHERE plant_code = l_qc_samples_out_rec.orgn_code
243 AND whse_item_id = replenish_item_id
244 AND replen_ind = 1
245 AND delete_mark = 0;
246
247 CURSOR Cur_replenish_whse_plant IS
248 SELECT whse_code
249 FROM ps_whse_eff
250 WHERE plant_code = l_qc_samples_out_rec.orgn_code
251 AND replen_ind = 1
252 AND delete_mark = 0;
253 */
254
255 CURSOR Cur_batch_status IS -- Bug # 4619570 Need to know if batch is closed
256 SELECT batch_status
257 FROM gme_batch_header
258 WHERE batch_id = l_qc_samples_out_rec.batch_id;
259
260 -- 5283854
261 CURSOR c_get_lot_optional (p_spec_vr_id NUMBER)
262 IS
263 SELECT LOT_OPTIONAL_ON_SAMPLE
264 FROM
265 (select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_inventory_spec_vrs
266 union all
267 select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_wip_spec_vrs
268 union all
269 select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_customer_spec_vrs
270 union all
271 select spec_vr_id, LOT_OPTIONAL_ON_SAMPLE from gmd_supplier_spec_vrs
272 )
273 WHERE spec_vr_id = p_spec_vr_id;
274
275 -- 7027149
276 cursor get_lpn_no(p_lpn VARCHAR2) is
277 SELECT lpn_id
278 FROM
279 WMS_LICENSE_PLATE_NUMBERS WHERE LICENSE_PLATE_NUMBER = p_lpn;
280
281 CURSOR get_wms_flag IS
282 SELECT wms_enabled_flag
283 FROM mtl_parameters
284 WHERE organization_id = p_qc_samples_rec.organization_id;
285
286 -- 7027149
287 cursor get_lpn is
288 SELECT 1
289 FROM
290 WMS_LICENSE_PLATE_NUMBERS WHERE lpn_id = p_qc_samples_rec.lpn_id;
291
292 BEGIN
293
294 -- Standard Start OF API savepoint
295
296 SAVEPOINT CREATE_SAMPLES;
297
298 --dbms_output.put_line('Entered Procedure CREATE_SAMPLES');
299
300 /* Standard call to check for call compatibility. */
301
302 IF NOT FND_API.Compatible_API_CALL
303 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
304 THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 /* Initialize message list if p_int_msg_list is set TRUE. */
309 IF FND_API.to_boolean(p_init_msg_list)
310 THEN
311 FND_MSG_PUB.Initialize;
312 END IF;
313
314 -- Initialize API return Parameters
315
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317 l_samples_val_rec := p_qc_samples_rec;
318
319
320 -- Validate User Name Parameter
321 GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
322 ,x_user_id => l_user_id);
323
324 IF NVL(l_user_id, -1) < 0
325 THEN
326 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
327 'L_USER_NAME', p_user_name);
328 RAISE FND_API.G_EXC_ERROR;
329 ELSE
330 -- Added below for BUG 2711643. Hverddin
331 GMD_API_PUB.SET_USER_CONTEXT(p_user_id => l_user_id,
332 x_return_status => l_return_status);
333
334 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
335 RAISE FND_API.G_EXC_ERROR;
336 END IF;
337
338 l_samples_val_rec.created_by := l_user_id;
339 l_samples_val_rec.last_updated_by := l_user_id;
340 END IF;
341
342
343 -- Validate organization_id Passed.
344 IF (l_samples_val_rec.organization_id IS NULL) THEN
345 GMD_API_PUB.Log_Message('GMD_ORGN_CODE_REQD');
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349
350 -- Validate RETAIN_AS Passed.
351 IF ((l_samples_val_rec.sample_disposition NOT IN ('0RT', '0PL'))
352 AND (l_samples_val_rec.retain_as IS NOT NULL)) THEN
353 GMD_API_PUB.Log_Message('GMD_PLANNED_RETAINED_SAMPLES');
354 RAISE FND_API.G_EXC_ERROR;
355 END IF;
356
357 -- Determine Type of Doc Sequencing defined for sample no.
358 -- Bug 4165704: doc numbering now kept in quality parameters table
359 -- OPEN c_doc_numbering(l_samples_val_rec.orgn_code);
360 -- FETCH c_doc_numbering INTO l_assign_type;
361 -- IF c_doc_numbering%NOTFOUND THEN
362 -- GMD_API_PUB.Log_Message('GMD_ORGN_DOC_SEQ',
363 -- 'ORGN_CODE', l_samples_val_rec.orgn_code,
364 -- 'DOC_TYPE' , p_user_name);
365 -- END IF;
366 -- CLOSE c_doc_numbering;
367
368 GMD_QUALITY_PARAMETERS_GRP.get_quality_parameters(
369 p_organization_id => p_qc_samples_rec.organization_id
370 , x_quality_parameters => quality_config
371 , x_return_status => l_return_status
372 , x_orgn_found => found );
373
374 IF (l_return_status <> 'S') THEN
375 GMD_API_PUB.Log_Message('GMD_QM_ORG_PARAMETER');
376 RAISE FND_API.G_EXC_ERROR;
377 END IF;
378
379 l_assign_type := quality_config.sample_assignment_type;
380
381 -- Assign Sample No if Automatic Numbering Defined.
382 IF NVL(l_assign_type,0) = 2 THEN -- Then auto Sample Numbering defined.
383 -- Bug 4165704: routine to get sample no is now handled by quality routine
384 -- l_samples_val_rec.sample_no := GMA_GLOBAL_GRP.Get_Doc_No(
385 -- p_doc_type => 'SMPL',
386 -- p_orgn_code => l_samples_val_rec.orgn_code);
387
388 l_samples_val_rec.sample_no := GMD_QUALITY_PARAMETERS_GRP.get_next_sample_no(l_samples_val_rec.organization_id);
389 END IF;
390
391 -- bug# 2995114
392 -- p_qc_samples_out_rec.sample_inv_trans_ind
393 -- It can have 3 values.
394 -- Y - Don't go to VR and fetch the value. Just deduct inventory.
395 -- N - Don't go to VR and fetch the value. Don't deduct inventory.
396 -- null - Fetch the value from VR and depending upon that deduct or do not deduct inventory.
397
398
399 --dbms_output.put_line('b4 validate sample ');
400 -- 7027149
401 IF ( p_lpn IS NOT NULL OR l_samples_val_rec.lpn_id IS NOT NULL ) THEN
402 OPEN get_wms_flag;
403 FETCH get_wms_flag INTO l_wms_enabled_flag;
404 CLOSE get_wms_flag;
405
406 IF l_wms_enabled_flag = 'N' then
407 GMD_API_PUB.Log_Message('WMS_ONLY_FUNCTIONALITY');
408 RAISE FND_API.G_EXC_ERROR;
409 END IF;
410
411 END IF; -- IF l_samples_val_rec.lpn IS NOT NULL ) THEN
412
413 IF p_lpn IS NOT NULL THEN
414 OPEN get_lpn_no(p_lpn);
415 FETCH get_lpn_no INTO l_samples_val_rec.lpn_id;
416 IF get_lpn_no%NOTFOUND THEN
417 CLOSE get_lpn_no;
418 GMD_API_PUB.Log_Message('WMS_LPN_NOT_FOUND');
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421 CLOSE get_lpn_no;
422 END IF;
423
424 IF p_qc_samples_rec.lpn_id IS NOT NULL THEN
425 OPEN get_lpn;
426 FETCH get_lpn INTO dummy;
427 IF get_lpn%NOTFOUND THEN
428 CLOSE get_lpn;
429 GMD_API_PUB.Log_Message('WMS_LPN_NOT_FOUND');
430 RAISE FND_API.G_EXC_ERROR;
431 END IF;
432 CLOSE get_lpn;
433 END IF;
434
435
436
437
438
439
440 -- Validate Sample Record
441 VALIDATE_SAMPLE(
442 p_sample_rec => l_samples_val_rec,
443 p_grade => p_grade, -- 3431884
444 x_sample_rec => l_qc_samples_rec,
445 x_return_status => l_return_status
446 );
447
448 --dbms_output.put_line('after validate, before return stat check');
449
450 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
451 RAISE FND_API.G_EXC_ERROR;
452 END IF;
453
454 --dbms_output.put_line('after validate, after return stat check');
455
456 IF l_qc_samples_rec.sampling_event_id is NULL THEN
457
458 -- If Sampling Event Id is not specified should we derive it
459 IF p_find_matching_spec = 'Y' THEN
460 -- bug 3467845
461 -- the API was using different sequence for getting spec than the forms
462 -- first it was looking if any sampling event exist without a VR.If it exist it will
463 -- take the sampling event and won't do a spec match.
464 -- changed the whole sequence.
465 -- first it will look for the latest spec vr. if the spec is found , it will look for
466 -- an VALID sampling event with that spec vr. If it exists , it will assign sample
467 -- to that sampling event else create a new event.
468
469 --dbms_output.put_line('FIND MAtching Spec' );
470
471 IF NOT GMD_SAMPLES_PUB.FIND_MATCHING_SPEC
472 ( p_samples_rec => l_qc_samples_rec,
473 p_grade => p_grade, -- 3431884
474 x_spec_id => l_spec_id,
475 x_spec_type => l_spec_type,
476 x_spec_vr_id => l_spec_vr_id,
477 x_return_status => l_return_status,
478 x_msg_data => l_msg_data
479 )THEN
480 GMD_API_PUB.Log_Message('GMD_SPEC_NOT_FOUND');
481 RAISE FND_API.G_EXC_ERROR;
482 END IF;
483
484 IF l_qc_samples_rec.source IN ('I','C','W','S') AND p_qc_samples_rec.sample_inv_trans_ind IS NULL THEN
485
486
487 --dbms_output.put_line('b4 get_inventory_ind_from_vr');
488
489 l_qc_samples_rec.sample_inv_trans_ind := get_inventory_ind_from_vr(
490 p_spec_type => l_spec_type ,
491 p_spec_vr_id => l_spec_vr_id );
492
493 --dbms_output.put_line('FIND inv indicator for new sampling event=>' || l_qc_samples_rec.sample_inv_trans_ind);
494 END IF;
495
496 -- 5283854 need check here for lot_optional on svr
497 IF l_qc_samples_rec.source IN ('I','C','W','S') THEN
498
499 OPEN c_get_lot_optional(l_spec_vr_id);
500 FETCH c_get_lot_optional INTO L_LOT_OPTIONAL_ON_SAMPLE;
501 IF c_get_lot_optional%NOTFOUND THEN
502 CLOSE c_get_lot_optional;
503 END IF;
504 CLOSE c_get_lot_optional;
505
506 IF NVL(L_LOT_OPTIONAL_ON_SAMPLE,'N') <> 'Y' AND NVL(G_LOT_CTL,0) = 2 then
507 IF ((l_qc_samples_rec.lot_number IS NULL)
508 AND ((l_qc_samples_rec.parent_lot_number IS NULL) AND (NVL(G_CHILD_LOT_FLAG,'N') = 'Y'))) THEN
509 GMD_API_PUB.Log_Message('GMD_QM_LOT_REQUIRED'); -- 8276017 added this message instead.
510 --GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
511 -- 'WHAT', 'lot_number');
512 RAISE FND_API.G_EXC_ERROR;
513
514 END IF;
515
516
517 END IF; -- IF L_LOT_OPTIONAL_ON_SAMPLE <> 'Y' then
518
519 END IF; -- IF l_qc_samples_rec.source IN ('I','C','W','S') THEN
520
521
522 -- find sampling event with matching spec vr id.
523 -- BUT do not call this if api call wants to create new sampling event - bug 16523872
524
525 IF nvl(p_create_new_sample_group,'N') <> 'Y' THEN -- 16523872
526 IF GMD_SAMPLES_GRP.sampling_event_exist(
527 p_sample => l_qc_samples_rec,
528 x_sampling_event_id => l_sampling_events.sampling_event_id,
529 p_spec_vr_id => l_spec_vr_id
530 ) THEN
531
532 NULL ;
533
534 END IF;
535 END IF; -- IF p_create_new_sample_group <> 'Y' 16523872
536
537 ELSE -- p_find_matching_spec is N
538
539 --dbms_output.put_line('b4 sampling_event_exist_wo_spec');
540
541 -- Try and find a sampling event without a spec
542 IF nvl(p_create_new_sample_group,'N') <> 'Y' THEN -- 16523872
543 IF NOT GMD_SAMPLES_GRP.sampling_event_exist_wo_spec
544 ( p_sample => l_qc_samples_rec,
545 x_sampling_event_id => l_sampling_events.sampling_event_id
546 ) THEN
547
548 l_spec_vr_id := NULL;
549
550 END IF; --For find matching SE without Spec.
551 l_spec_vr_id := NULL;
552 --dbms_output.put_line('after sampling_event_exist_wo_spec');
553
554 END IF; -- IF nvl(p_create_new_sample_group,'N') <> 'Y' -- 16523872
555
556
557 END IF; -- For find matching spec.
558 ELSE -- Sampling event id is passed.
559 -- Assign the sampling event id to local sampling event record.
560 l_sampling_events.sampling_event_id := l_qc_samples_rec.sampling_event_id;
561
562 END IF; -- sampling_event_id logic
563
564 -- Determine value of sample active cnt
565 -- Get this from sampling event.
566 -- Bug 3401377: added Planned Samples for MPL and they do not add to active count
567 IF (l_qc_samples_rec.sample_disposition = '0PL')
568 OR (l_qc_samples_rec.sample_disposition = '0RT') THEN
569 l_sample_active_cnt := 0;
570 ELSIF (l_qc_samples_rec.sample_disposition = '1P') THEN
571 l_sample_active_cnt := 1;
572 ELSE
573 GMD_API_PUB.Log_Message('GMD_SAMPLE_DISPOSITION_INVALID');
574 RAISE FND_API.G_EXC_ERROR;
575 END IF;
576
577
578 -- start bug 12846165 - new param if create_new_sampling_event parameter = Y then make l_sampling_events.sampling_event_id := NULL so that new one is created.
579 IF p_create_new_sample_group = 'Y' THEN
580 l_sampling_events.sampling_event_id := NULL;
581 END IF;
582 -- end bug 12846165
583
584
585 IF l_sampling_events.sampling_event_id is NULL THEN
586
587 -- bug# 2995114
588 l_sampling_event_exist := 'N' ;
589
590 -- we need to create a S.E record.
591 -- Only if p_matching_spec is set to Y and find_matching_spec
592 -- Returns True will the l_spec_vr_id be populated.
593
594
595 --dbms_output.put_line('after get_sample_cnt');
596
597 IF l_spec_vr_id IS NULL THEN
598 l_sample_req_cnt := 1;
599 ELSE
600 -- Check if the validity rule has a sampling plan.
601 OPEN c_get_sample_cnt(l_spec_vr_id);
602 FETCH c_get_sample_cnt INTO l_sample_req_cnt;
603 IF c_get_sample_cnt%NOTFOUND THEN
604 l_sample_req_cnt := 1;
605 END IF;
606 CLOSE c_get_sample_cnt;
607 END IF;
608
609
610 --dbms_output.put_line('after get sample cnt');
611
612 l_sampling_events.original_spec_vr_id := l_spec_vr_id;
613 l_sampling_events.disposition := l_qc_samples_rec.sample_disposition;
614 l_sampling_events.receipt_id := l_qc_samples_rec.receipt_id;
615 l_sampling_events.po_header_id := l_qc_samples_rec.po_header_id;
616 l_sampling_events.source := l_qc_samples_rec.source;
617 l_sampling_events.inventory_item_id := l_qc_samples_rec.inventory_item_id;
618 l_sampling_events.revision := l_qc_samples_rec.revision;
619 l_sampling_events.lot_number := l_qc_samples_rec.lot_number;
620 l_sampling_events.parent_lot_number := l_qc_samples_rec.parent_lot_number;
621 l_sampling_events.subinventory := l_qc_samples_rec.subinventory;
622 l_sampling_events.locator_id := l_qc_samples_rec.locator_id;
623 l_sampling_events.batch_id := l_qc_samples_rec.batch_id;
624 l_sampling_events.recipe_id := l_qc_samples_rec.recipe_id;
625 l_sampling_events.formula_id := l_qc_samples_rec.formula_id;
626 l_sampling_events.formulaline_id := l_qc_samples_rec.formulaline_id;
627 l_sampling_events.material_detail_id := l_qc_samples_rec.material_detail_id;
628 l_sampling_events.routing_id := l_qc_samples_rec.routing_id;
629 l_sampling_events.oprn_id := l_qc_samples_rec.oprn_id;
630 l_sampling_events.charge := l_qc_samples_rec.charge;
631 l_sampling_events.cust_id := l_qc_samples_rec.cust_id;
632 l_sampling_events.order_id := l_qc_samples_rec.order_id;
633 l_sampling_events.order_line_id := l_qc_samples_rec.order_line_id;
634 l_sampling_events.org_id := l_qc_samples_rec.org_id;
635 l_sampling_events.supplier_id := l_qc_samples_rec.supplier_id;
636 l_sampling_events.po_line_id := l_qc_samples_rec.po_line_id;
637 l_sampling_events.receipt_line_id := l_qc_samples_rec.receipt_line_id;
638 l_sampling_events.supplier_lot_no := l_qc_samples_rec.supplier_lot_no;
639 l_sampling_events.supplier_site_id := l_qc_samples_rec.supplier_site_id;
640 l_sampling_events.ship_to_site_id := l_qc_samples_rec.ship_to_site_id;
641 l_sampling_events.step_no := l_qc_samples_rec.step_no;
642 l_sampling_events.step_id := l_qc_samples_rec.step_id;
643 l_sampling_events.lot_retest_ind := l_qc_samples_rec.lot_retest_ind;
644 l_sampling_events.lot_retest_ind := l_qc_samples_rec.lot_retest_ind;
645 l_sampling_events.sample_req_cnt := l_sample_req_cnt;
646 l_sampling_events.sample_taken_cnt := 1;
647 l_sampling_events.sample_active_cnt := l_sample_active_cnt;
648 l_sampling_events.CREATION_DATE := l_date;
649 l_sampling_events.CREATED_BY := l_user_id;
650 l_sampling_events.LAST_UPDATED_BY := l_user_id;
651 l_sampling_events.LAST_UPDATE_DATE := l_date;
652 l_sampling_events.sample_type := l_qc_samples_rec.sample_type;
653 l_sampling_events.organization_id := l_qc_samples_rec.organization_id;
654 -- 7027149
655 l_sampling_events.lpn_id := l_qc_samples_rec.lpn_id;
656
657
658 -- Bug 3401377: added instance_id, resources, time_point_id and variant_id to sampling event
659 -- table for MPK
660 l_sampling_events.instance_id := l_qc_samples_rec.instance_id;
661 l_sampling_events.resources := l_qc_samples_rec.resources;
662 l_sampling_events.time_point_id := l_qc_samples_rec.time_point_id;
663 l_sampling_events.variant_id := l_qc_samples_rec.variant_id;
664
665
666 -- Bug 3401377: added retain_as, archived_taken, reserved_taken to sampling
667 -- event table for MPL
668
669 -- bug# 3465073
670 -- l_sampling_events.retain_as := l_qc_samples_rec.retain_as;
671
672 IF ((l_qc_samples_rec.retain_as = ( 'A'))
673 AND (l_qc_samples_rec.sample_disposition <> '0PL')) THEN
674 l_sampling_events.archived_taken := 1;
675 l_sampling_events.reserved_taken := 0;
676 ELSIF ((l_qc_samples_rec.retain_as = ( 'R'))
677 AND (l_qc_samples_rec.sample_disposition <> '0PL')) THEN
678 l_sampling_events.archived_taken := 0;
679 l_sampling_events.reserved_taken := 1;
680 ELSE
681 l_sampling_events.archived_taken := 0;
682 l_sampling_events.reserved_taken := 0;
683 END IF;
684
685
686 --dbms_output.put_line('before insert se row ');
687
688 IF NOT GMD_SAMPLING_EVENTS_PVT.insert_row (
689 p_sampling_events => l_sampling_events,
690 x_sampling_events => l_sampling_events_out) THEN
691 RAISE FND_API.G_EXC_ERROR;
692 END IF;
693
694 --dbms_output.put_line('after insert se row ');
695
696
697 -- Bug 2987571: make sure sample instance is saved to new sample
698 l_qc_samples_rec.sample_instance := 1;
699
700 ELSE -- WE need to update the SE table.
701
702
703 --dbms_output.put_line('WE need to update the SE table. ');
704
705 -- bug# 2995114
706 l_sampling_event_exist := 'Y' ;
707
708 IF NOT GMD_SAMPLING_EVENTS_PVT.lock_row
709 (
710 p_sampling_event_id => l_sampling_events.sampling_event_id
711 ) THEN
712 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
713 'l_table_name', 'GMD_SAMPLING_EVENTS',
714 'l_column_name','SAMPLING_EVENT_ID',
715 'l_key_value', l_sampling_events.sampling_event_id);
716 RAISE FND_API.G_EXC_ERROR;
717 ELSE
718 -- Bug 3401377: added retain_as, archived_taken, reserved_taken to sampling
719 -- event table for MPL
720 IF ((l_qc_samples_rec.retain_as = ( 'A'))
721 AND (l_qc_samples_rec.sample_disposition <> '0PL')) THEN
722 l_sampling_events.archived_taken := 1;
723 l_sampling_events.reserved_taken := 0;
724 ELSIF ((l_qc_samples_rec.retain_as = ( 'R'))
725 AND (l_qc_samples_rec.sample_disposition <> '0PL')) THEN
726 l_sampling_events.archived_taken := 0;
727 l_sampling_events.reserved_taken := 1;
728 ELSE
729 l_sampling_events.archived_taken := 0;
730 l_sampling_events.reserved_taken := 0;
731 END IF;
732
733 -- Update the sampling events Table
734 -- Bug 3401377: added archived_taken, reserved_taken to sampling event
735 UPDATE GMD_SAMPLING_EVENTS
736 SET SAMPLE_TAKEN_CNT = sample_taken_cnt + 1,
737 SAMPLE_ACTIVE_CNT = sample_active_cnt + l_sample_active_cnt ,
738 ARCHIVED_TAKEN = NVL(ARCHIVED_TAKEN, 0) + l_sampling_events.archived_taken ,
739 RESERVED_TAKEN = NVL(RESERVED_TAKEN, 0) + l_sampling_events.reserved_taken ,
740 LAST_UPDATED_BY = l_user_id,
741 LAST_UPDATE_DATE = l_date
742 WHERE SAMPLING_EVENT_ID = l_sampling_events.sampling_event_id;
743
744 IF NOT GMD_SAMPLING_EVENTS_PVT.fetch_row(
745 p_sampling_events => l_sampling_events,
746 x_sampling_events => l_sampling_events_out) THEN
747 RAISE FND_API.G_EXC_ERROR;
748 END IF;
749
750 -- Bug 2987571: make sure sample instance is saved to new sample
751 -- Select from the sampling events Table
752 SELECT sample_taken_cnt INTO l_qc_samples_rec.sample_instance
753 FROM GMD_SAMPLING_EVENTS
754 WHERE SAMPLING_EVENT_ID = l_sampling_events.sampling_event_id;
755
756 END IF;
757
758 END IF;
759
760 --dbms_output.put_line('Insert SAMPLE Row');
761
762 l_qc_samples_rec.delete_mark := 0;
763 l_qc_samples_rec.last_update_date := l_date;
764 l_qc_samples_rec.creation_date := l_date;
765 l_qc_samples_rec.sampling_event_id := l_sampling_events_out.sampling_event_id;
766
767 -- Bug 2987571: make sure sample remaining qty is saved to new sample
768 --l_qc_samples_rec.remaining_qty := l_qc_samples_rec.sample_qty;
769 IF (l_qc_samples_rec.sample_type = 'I' ) THEN
770 -- Bug 3401377: added Planned Samples for MPL and they may have null qty
771 IF (l_qc_samples_rec.remaining_qty IS NULL)
772 AND (l_qc_samples_rec.sample_disposition <> '0PL' ) THEN
773 l_qc_samples_rec.remaining_qty := l_qc_samples_rec.sample_qty;
774 END IF;
775 END IF;
776
777 -- bug# 2995114
778 -- possible values in database are Y and null.
779
780
781 IF p_qc_samples_rec.sample_inv_trans_ind = 'N' THEN
782 l_qc_samples_rec.sample_inv_trans_ind := NULL ;
783 END IF;
784
785
786 IF NOT GMD_SAMPLES_PVT.insert_row (
787 p_samples => l_qc_samples_rec,
788 x_samples => l_qc_samples_out_rec) THEN
789 RAISE FND_API.G_EXC_ERROR;
790
791 END IF;
792
793
794 --dbms_output.put_line('end Insert SAMPLE Row');
795
796 -- Lets Create the Corresponding Result records.
797 -- Only if the sample disposition is not RETAIN
798
799 -- Bug 3401377: added disposition '0PL' in MPL
800 -- and add 'migration' parameter to function call (added for planned samples)
801 -- and if disposition = retained, the create rslt routine still needs to be called
802 --IF l_qc_samples_out_rec.sample_disposition <> '0RT' THEN
803
804 -- bug# 3468060
805 -- if a planned sample was created using public layer api and queried in forms application,
806 -- it was not querying the record. Error message was shown with no rows in gmd_sample_spec_disp
807 -- removed the below if condition for planned samples. Need to create event spec dispositions rows.
808 --IF l_qc_samples_out_rec.sample_disposition <> '0PL' THEN
809 GMD_RESULTS_GRP.create_rslt_and_spec_rslt_rows(
810 p_sample => l_qc_samples_out_rec,
811 p_migration => 'N',
812 x_sample_spec_disp => l_sample_spec_disp,
813 x_event_spec_disp => l_event_spec_disp_rec,
814 x_results_tab => l_results_tab,
815 x_spec_results_tab => l_spec_results_tab,
816 x_return_status => l_return_status);
817
818 -- dbms_output.put_line('RES Return Status => ' || l_return_status);
819
820 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821 RAISE FND_API.G_EXC_ERROR;
822 END IF;
823
824 --dbms_output.put_line('end RES Return Status => ' || l_return_status);
825
826
827 -- bug# 2995114
828 -- if new sample is tied to existing sampling event, get_spec_match is not called and hence
829 -- inventory indictor is not fetched.
830 -- we need to fetch indicator whether to decrease(update) inventory for the sample for an
831 -- existing sampling event.
832
833 IF l_sampling_event_exist = 'Y' and l_qc_samples_rec.source IN ('I','C','W','S')
834 AND p_qc_samples_rec.sample_inv_trans_ind IS NULL THEN
835
836 l_qc_samples_rec.sample_inv_trans_ind := get_inventory_ind_from_vr(
837 p_spec_type => NULL ,
838 p_spec_vr_id => l_event_spec_disp_rec.spec_vr_id );
839
840 l_qc_samples_out_rec.sample_inv_trans_ind := l_qc_samples_rec.sample_inv_trans_ind ;
841 l_sample_inv_trans_ind := l_qc_samples_out_rec.sample_inv_trans_ind; -- Bug # 4619570
842
843 IF l_qc_samples_out_rec.batch_id IS NOT NULL THEN -- Bug # 4619570
844 OPEN Cur_batch_status;
845 FETCH Cur_batch_status into l_batch_status;
846 CLOSE Cur_batch_status;
847
848 IF l_batch_status = 4 then
849
850 l_sample_inv_trans_ind := NULL;
851 END IF;
852 END IF;
853
854
855
856
857 UPDATE gmd_samples
858 SET SAMPLE_INV_TRANS_IND = l_sample_inv_trans_ind -- Bug # 4619570
859 WHERE sample_id = l_qc_samples_out_rec.sample_id ;
860
861 END IF;
862
863 --dbms_output.put_line('1');
864
865 -- end of bug 2995114
866
867 --END IF;
868
869
870 -- Added new functionality as part of BUG 2677712.
871 -- If sample source not wip and sample_inv_trans_id
872 -- is set to Y call create inv transaction.
873 -- If source type is Wip then create wip transaction
874
875
876 -- Bug 3401377 : if planned sample, do not generate the inventory transaction (from MPL)
877 IF l_qc_samples_out_rec.source <> 'W' AND
878 l_qc_samples_out_rec.sample_disposition <> '0PL' AND
879 l_qc_samples_out_rec.sample_inv_trans_ind = 'Y' THEN
880
881 -- Bug 3516802; changed test from 'NVL' to 'is not null'
882 IF (l_qc_samples_out_rec.source = 'S') THEN
883 IF (l_qc_samples_out_rec.source_subinventory IS NOT NULL ) THEN
884 l_qc_samples_out_rec.subinventory := l_qc_samples_out_rec.source_subinventory;
885 l_qc_samples_out_rec.locator_id := l_qc_samples_out_rec.source_locator_id;
886 END IF; -- source = 'S'
887
888 IF (l_qc_samples_out_rec.source_subinventory IS NULL ) THEN
889 GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS_API2');
890 RAISE FND_API.G_EXC_ERROR;
891 ELSIF (l_qc_samples_out_rec.receipt_line_id IS NULL ) THEN
892 GMD_API_PUB.Log_Message('GMD_QM_NO_INVENTORY_TRANS_API');
893 RAISE FND_API.G_EXC_ERROR;
894 END IF;
895
896 --END IF; -- end Bug 3516802
897
898 -- Bug 3491783: if whse is not specified, can not generate inv trans
899 ELSIF (l_qc_samples_out_rec.subinventory IS NULL) THEN
900 GMD_API_PUB.Log_Message('GMD_QM_WHSE_REQ_INV');
901 RAISE FND_API.G_EXC_ERROR;
902 END IF; -- end bug
903
904
905 GMD_SAMPLES_GRP.create_inv_txn
906 ( p_sample => l_qc_samples_out_rec,
907 p_user_name => l_user_id,
908 x_return_status => l_return_status,
909 x_message_count => l_msg_count,
910 x_message_data => l_msg_data
911 );
912
913 --dbms_output.put_line('after create inv txn');
914
915 -- dbms_output.put_line('end create inv txn Return Status => ' || l_return_status);
916
917 IF (l_return_status <> 'S') THEN
918 RAISE FND_API.G_EXC_ERROR;
919 END IF;
920 -- Bug 3401377 : if planned sample, do not generate the inventory transaction (from MPL)
921 ELSIF l_qc_samples_out_rec.source = 'W' AND
922 l_qc_samples_out_rec.sample_disposition <> '0PL' AND
923 l_qc_samples_out_rec.sample_inv_trans_ind = 'Y' THEN
924
925 -- bug# 2995114
926 -- added code to get whse for wip sample.
927 -- bug 4165704: source subinventory is now retrieved from mtl_system_items
928 --IF l_qc_samples_out_rec.source_whse IS NULL THEN
929 -- Get Replenish Warehouse for item and/or plant
930 -- OPEN Cur_replenish_whse(l_qc_samples_out_rec.inventory_item_id);
931 -- FETCH Cur_replenish_whse INTO l_qc_samples_out_rec.source_whse ;
932 -- IF Cur_replenish_whse%NOTFOUND THEN
933 -- CLOSE Cur_replenish_whse;
934 -- OPEN Cur_replenish_whse_plant;
935 -- FETCH Cur_replenish_whse_plant INTO l_qc_samples_out_rec.source_whse ;
936 -- IF Cur_replenish_whse_plant%NOTFOUND THEN
937 -- CLOSE Cur_replenish_whse_plant;
938 -- GMD_API_PUB.Log_Message('GMD_REPLENISH_WHSE_NOT_FOUND');
939 -- RAISE FND_API.G_EXC_ERROR;
940 -- END IF;
941 -- CLOSE Cur_replenish_whse_plant;
942 -- ELSE
943 -- CLOSE Cur_replenish_whse;
944 -- END IF;
945
946 -- need to update source whse back to samples since insert of sample has already taken place.
947 UPDATE GMD_SAMPLES
948 SET source_subinventory = l_qc_samples_out_rec.source_subinventory
949 WHERE sample_id = l_qc_samples_out_rec.sample_id ;
950
951 -- END IF;
952
953 -- Bug # 4619570 Disable create_wip_txn for wip sample against closed batch
954 IF l_qc_samples_out_rec.batch_id IS NOT NULL THEN
955 OPEN Cur_batch_status;
956 FETCH Cur_batch_status into l_batch_status;
957 CLOSE Cur_batch_status;
958
959 IF l_batch_status <> 4 then
960
961 GMD_SAMPLES_GRP.create_wip_txn
962 ( p_sample => l_qc_samples_out_rec,
963 x_return_status => l_return_status,
964 x_message_count => l_msg_count,
965 x_message_data => l_msg_data
966 );
967
968 IF (l_return_status <> 'S') THEN
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971
972
973 GMD_SAMPLES_GRP.post_wip_txn
974 ( p_batch_id => l_qc_samples_out_rec.batch_id,
975 x_return_status => l_return_status
976 );
977
978 IF (l_return_status <> 'S') THEN
979 RAISE FND_API.G_EXC_ERROR;
980 END IF;
981
982 -- bug# 2995114
983 -- added create_inv_txn to decrease the inventory which was increased by create_wip_txn/post_wip_txn
984
985 --dbms_output.put_line('create inv trans after wip');
986
987 GMD_SAMPLES_GRP.create_inv_txn
988 ( p_sample => l_qc_samples_out_rec,
989 p_user_name => p_user_name,
990 x_return_status => l_return_status,
991 x_message_count => l_msg_count,
992 x_message_data => l_msg_data
993 );
994
995
996 IF (l_return_status <> 'S') THEN
997 RAISE FND_API.G_EXC_ERROR;
998 END IF;
999
1000 END IF; -- IF l_batch_status <> 4 then
1001
1002 END IF;
1003 END IF;
1004 -- Standard Check of p_commit.
1005 IF FND_API.to_boolean(p_commit) THEN
1006 COMMIT WORK;
1007 END IF;
1008
1009 -- Set return Parameters
1010
1011 x_return_status := l_return_status;
1012 x_qc_samples_rec := l_qc_samples_out_rec;
1013 x_sampling_events_rec := l_sampling_events_out;
1014 x_results_tab := l_results_tab;
1015 x_spec_results_tab := l_spec_results_tab;
1016 x_event_spec_disp_rec := l_event_spec_disp_rec;
1017 x_sample_spec_disp := l_sample_spec_disp;
1018
1019 --dbms_output.put_line('The end');
1020
1021
1022 EXCEPTION
1023 WHEN FND_API.G_EXC_ERROR THEN
1024 ROLLBACK TO CREATE_SAMPLES;
1025 x_return_status := FND_API.G_RET_STS_ERROR;
1026 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1027 , p_count => x_msg_count
1028 , p_data => x_msg_data
1029 );
1030
1031 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1032 ROLLBACK TO CREATE_SAMPLES;
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1035 , p_count => x_msg_count
1036 , p_data => x_msg_data
1037 );
1038
1039
1040
1041 WHEN OTHERS THEN
1042 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1043 ROLLBACK TO CREATE_SAMPLES;
1044 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1045 , l_api_name
1046 );
1047
1048 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1049 , p_count => x_msg_count
1050 , p_data => x_msg_data
1051 );
1052
1053 END CREATE_SAMPLES;
1054
1055 /*=====================================
1056 Function added by Joe DiIorio
1057 Bug#4691545 01/05/2006
1058 =====================================*/
1059 FUNCTION GET_CONF_MATCH_VALUE
1060 ( p_org_id IN NUMBER
1061 )
1062
1063 RETURN VARCHAR2
1064
1065 IS
1066
1067 CURSOR get_match_ind IS
1068 SELECT exact_spec_match_ind
1069 FROM gmd_quality_config
1070 WHERE organization_id = p_org_id;
1071
1072 l_exact_spec_match_ind gmd_quality_config.exact_spec_match_ind%TYPE;
1073
1074 BEGIN
1075 OPEN get_match_ind;
1076 FETCH get_match_ind INTO l_exact_spec_match_ind;
1077 IF (get_match_ind%NOTFOUND) THEN
1078 CLOSE get_match_ind;
1079 RETURN 'N';
1080 END IF;
1081 CLOSE get_match_ind;
1082 RETURN NVL(l_exact_spec_match_ind,'N');
1083
1084 END GET_CONF_MATCH_VALUE;
1085
1086 FUNCTION FIND_MATCHING_SPEC
1087 ( p_samples_rec IN GMD_SAMPLES%ROWTYPE,
1088 p_grade IN VARCHAR2 DEFAULT NULL, -- 3431884
1089 x_spec_id OUT NOCOPY NUMBER,
1090 x_spec_type OUT NOCOPY VARCHAR2,
1091 x_spec_vr_id OUT NOCOPY NUMBER,
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_data OUT NOCOPY VARCHAR2
1094 )
1095
1096
1097 RETURN BOOLEAN
1098 IS
1099 l_inv_spec GMD_SPEC_MATCH_GRP.INVENTORY_SPEC_REC_TYPE;
1100 l_cust_spec GMD_SPEC_MATCH_GRP.CUSTOMER_SPEC_REC_TYPE;
1101 l_supp_spec GMD_SPEC_MATCH_GRP.SUPPLIER_SPEC_REC_TYPE;
1102 l_wip_spec GMD_SPEC_MATCH_GRP.WIP_SPEC_REC_TYPE;
1103 l_location_spec GMD_SPEC_MATCH_GRP.LOCATION_SPEC_REC_TYPE;
1104 l_resource_spec GMD_SPEC_MATCH_GRP.RESOURCE_SPEC_REC_TYPE;
1105
1106 BEGIN
1107
1108
1109 IF p_samples_rec.source = 'I' THEN -- Find matching inventory spec.
1110
1111 -- Build inventory spec record
1112 l_inv_spec.inventory_item_id := p_samples_rec.inventory_item_id;
1113 l_inv_spec.revision := p_samples_rec.revision ;
1114 l_inv_spec.grade_code := p_grade; -- 3431884
1115 l_inv_spec.organization_id := p_samples_rec.organization_id ;
1116 l_inv_spec.lot_number := p_samples_rec.lot_number;
1117 l_inv_spec.parent_lot_number := p_samples_rec.parent_lot_number;
1118 l_inv_spec.subinventory := p_samples_rec.subinventory;
1119 l_inv_spec.locator_id := p_samples_rec.locator_id;
1120 -- Bug3151607 - Use the date drawn instead of creation date.
1121 -- l_inv_spec.date_effective := NVL(p_samples_rec.creation_date,SYSDATE);
1122 l_inv_spec.date_effective := NVL(p_samples_rec.date_drawn,SYSDATE);
1123
1124 /*====================================================
1125 BUG#4691545 - get gmd_quality_config match value.
1126 ====================================================*/
1127 l_inv_spec.exact_match := get_conf_match_value(p_samples_rec.organization_id);
1128
1129 -- Find Inventory Spec.
1130
1131
1132 IF GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC(
1133 p_inventory_spec_rec => l_inv_spec,
1134 x_spec_id => x_spec_id,
1135 x_spec_vr_id => x_spec_vr_id,
1136 x_return_status => x_return_status,
1137 x_message_data => x_msg_data) THEN
1138
1139 RETURN TRUE;
1140 -- dbms_output.put_line('Return True');
1141 END IF;
1142
1143 ELSIF p_samples_rec.source = 'C' THEN -- Find Matching Customer spec
1144
1145 l_cust_spec.inventory_item_id := p_samples_rec.inventory_item_id;
1146 l_cust_spec.revision := p_samples_rec.revision;
1147 l_cust_spec.grade_code := p_grade; -- 3431884
1148 l_cust_spec.organization_id := p_samples_rec.organization_id;
1149 l_cust_spec.subinventory := p_samples_rec.subinventory;
1150 l_cust_spec.org_id := p_samples_rec.org_id;
1151 l_cust_spec.cust_id := p_samples_rec.cust_id;
1152 l_cust_spec.ship_to_site_id := p_samples_rec.ship_to_site_id;
1153 l_cust_spec.order_id := p_samples_rec.order_id;
1154 l_cust_spec.order_line_id := p_samples_rec.order_line_id;
1155 l_cust_spec.lot_number := p_samples_rec.lot_number;
1156 l_cust_spec.parent_lot_number := p_samples_rec.parent_lot_number;
1157
1158 /*====================================================
1159 BUG#4691545 - get gmd_quality_config match value.
1160 ====================================================*/
1161 l_cust_spec.exact_match := get_conf_match_value(p_samples_rec.organization_id);
1162 -- Bug3151607
1163 l_cust_spec.date_effective := NVL(p_samples_rec.date_drawn,SYSDATE);
1164
1165 -- Find Cust Spec.
1166
1167 IF GMD_SPEC_MATCH_GRP.FIND_CUST_OR_INV_SPEC(
1168 p_customer_spec_rec => l_cust_spec,
1169 x_spec_id => x_spec_id,
1170 x_spec_vr_id => x_spec_vr_id,
1171 x_spec_type => x_spec_type,
1172 x_return_status => x_return_status,
1173 x_message_data => x_msg_data) THEN
1174
1175
1176 RETURN TRUE;
1177 END IF;
1178
1179 ELSIF p_samples_rec.source = 'W' THEN -- Find Matching Prod Spec.
1180
1181 l_wip_spec.inventory_item_id := p_samples_rec.inventory_item_id;
1182 l_wip_spec.revision := p_samples_rec.revision;
1183 l_wip_spec.grade_code := p_grade; -- 3431884
1184 l_wip_spec.organization_id := p_samples_rec.organization_id;
1185 -- l_wip_spec.whse_code := p_samples_rec.whse_code;
1186 l_wip_spec.batch_id := p_samples_rec.batch_id;
1187 l_wip_spec.recipe_id := p_samples_rec.recipe_id;
1188 l_wip_spec.formula_id := p_samples_rec.formula_id;
1189 l_wip_spec.formulaline_id := p_samples_rec.formulaline_id;
1190 l_wip_spec.material_detail_id := p_samples_rec.material_detail_id;
1191 l_wip_spec.routing_id := p_samples_rec.routing_id;
1192 l_wip_spec.step_id := p_samples_rec.step_id;
1193 l_wip_spec.step_no := p_samples_rec.step_no;
1194 l_wip_spec.oprn_id := p_samples_rec.oprn_id;
1195 l_wip_spec.charge := p_samples_rec.charge;
1196 -- Bug 3151607 - Use the date drawn instead of creation date.
1197 -- l_wip_spec.date_effective := NVL(p_samples_rec.creation_date,SYSDATE);
1198 l_wip_spec.date_effective := NVL(p_samples_rec.date_drawn,SYSDATE);
1199 l_wip_spec.lot_number := p_samples_rec.lot_number;
1200 l_wip_spec.parent_lot_number := p_samples_rec.parent_lot_number;
1201
1202 /*====================================================
1203 BUG#4691545 - get gmd_quality_config match value.
1204 ====================================================*/
1205 l_wip_spec.exact_match := get_conf_match_value(p_samples_rec.organization_id);
1206
1207
1208 -- Find WIP Spec.
1209
1210 IF GMD_SPEC_MATCH_GRP.FIND_WIP_OR_INV_SPEC(
1211 p_wip_spec_rec => l_wip_spec,
1212 x_spec_id => x_spec_id,
1213 x_spec_vr_id => x_spec_vr_id,
1214 x_spec_type => x_spec_type,
1215 x_return_status => x_return_status,
1216 x_message_data => x_msg_data) THEN
1217
1218 RETURN TRUE;
1219 END IF;
1220
1221
1222 ELSIF p_samples_rec.source= 'S' THEN -- Find Matching Supplier Spec.
1223
1224 --dbms_output.put_line('CAlling Supplier Spec MAtching');
1225
1226 l_supp_spec.inventory_item_id := p_samples_rec.inventory_item_id;
1227 l_supp_spec.revision := p_samples_rec.revision ;
1228 l_supp_spec.organization_id := p_samples_rec.organization_id;
1229 l_supp_spec.subinventory := p_samples_rec.subinventory;
1230 l_supp_spec.org_id := p_samples_rec.org_id;
1231 l_supp_spec.supplier_id := p_samples_rec.supplier_id;
1232 l_supp_spec.po_header_id := p_samples_rec.po_header_id;
1233 l_supp_spec.po_line_id := p_samples_rec.po_line_id;
1234 -- bug# 3447362
1235 -- passing supplier_site_id.was missing before
1236 l_supp_spec.supplier_site_id := p_samples_rec.supplier_site_id;
1237 l_supp_spec.grade_code := p_grade; -- 3431884
1238 -- Bug 3151607 - Use the date drawn instead of creation date.
1239 l_supp_spec.date_effective := NVL(p_samples_rec.date_drawn,SYSDATE);
1240 l_supp_spec.lot_number := p_samples_rec.lot_number;
1241 l_supp_spec.parent_lot_number := p_samples_rec.parent_lot_number;
1242
1243 /*====================================================
1244 BUG#4691545 - get gmd_quality_config match value.
1245 ====================================================*/
1246 l_supp_spec.exact_match := get_conf_match_value(p_samples_rec.organization_id);
1247
1248 -- Bug #3401377 : added rec_whse and rec_location to form MPL
1249 l_supp_spec.subinventory := p_samples_rec.subinventory;
1250 l_supp_spec.locator_id := p_samples_rec.locator_id;
1251
1252
1253 -- Find Supplier Spec.
1254
1255 IF GMD_SPEC_MATCH_GRP.FIND_SUPPLIER_OR_INV_SPEC(
1256 p_supplier_spec_rec => l_supp_spec,
1257 x_spec_id => x_spec_id,
1258 x_spec_vr_id => x_spec_vr_id,
1259 x_spec_type => x_spec_type,
1260 x_return_status => x_return_status,
1261 x_message_data => x_msg_data) THEN
1262
1263 RETURN TRUE;
1264 END IF;
1265
1266 -- Bug #3401377 : added get spec for location, resource and stability samples
1267 ELSIF p_samples_rec.source= 'L' THEN -- Find Matching Location Spec.
1268
1269 l_location_spec.subinventory := p_samples_rec.subinventory;
1270 l_location_spec.locator_id := p_samples_rec.locator_id;
1271 l_location_spec.locator_organization_id := p_samples_rec.organization_id;
1272 l_location_spec.date_effective := NVL(p_samples_rec.date_drawn,sysdate);
1273
1274 IF GMD_SPEC_MATCH_GRP.FIND_LOCATION_SPEC(
1275 p_location_spec_rec => l_location_spec,
1276 x_spec_id => x_spec_id,
1277 x_spec_vr_id => x_spec_vr_id,
1278 x_return_status => x_return_status,
1279 x_message_data => x_msg_data) THEN
1280
1281 RETURN TRUE;
1282 END IF;
1283
1284 ELSIF p_samples_rec.source= 'R' THEN -- Find Matching Resource Spec.
1285 l_resource_spec.resources := p_samples_rec.resources;
1286 l_resource_spec.resource_instance_id := p_samples_rec.instance_id;
1287 l_resource_spec.resource_organization_id := p_samples_rec.organization_id;
1288 l_resource_spec.date_effective := nvl(p_samples_rec.date_drawn,sysdate);
1289
1290
1291 IF GMD_SPEC_MATCH_GRP.FIND_RESOURCE_SPEC(
1292 p_resource_spec_rec => l_resource_spec,
1293 x_spec_id => x_spec_id,
1294 x_spec_vr_id => x_spec_vr_id,
1295 x_return_status => x_return_status,
1296 x_message_data => x_msg_data) THEN
1297
1298 RETURN TRUE;
1299 END IF;
1300
1301 -- end Bug #3401377 : added get spec for location, resource and stability samples
1302 ELSE
1303 GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID',
1304 'l_source', p_samples_rec.source);
1305 RAISE FND_API.G_EXC_ERROR;
1306 END IF;
1307
1308 RETURN FALSE;
1309
1310 EXCEPTION
1311
1312 WHEN FND_API.G_EXC_ERROR THEN
1313 RETURN FALSE;
1314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1315 RETURN FALSE;
1316 WHEN OTHERS THEN
1317 RETURN FALSE;
1318
1319 END FIND_MATCHING_SPEC;
1320
1321
1322 PROCEDURE DELETE_SAMPLES
1323 ( p_api_version IN NUMBER
1324 , p_init_msg_list IN VARCHAR2
1325 , p_commit IN VARCHAR2
1326 , p_validation_level IN NUMBER
1327 , p_qc_samples_rec IN GMD_SAMPLES%ROWTYPE
1328 , p_user_name IN VARCHAR2
1329 , x_return_status OUT NOCOPY VARCHAR2
1330 , x_msg_count OUT NOCOPY NUMBER
1331 , x_msg_data OUT NOCOPY VARCHAR2
1332 )
1333 IS
1334 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_SAMPLES';
1335 l_api_version CONSTANT NUMBER := 3.0;
1336 l_msg_count NUMBER :=0;
1337 l_msg_data VARCHAR2(2000);
1338 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
1339 l_qc_samples_out_rec GMD_SAMPLES%ROWTYPE;
1340 l_qc_samples_rec GMD_SAMPLES%ROWTYPE;
1341 l_rowid VARCHAR2(10);
1342 l_test_type VARCHAR2(10);
1343 l_test_id NUMBER(10);
1344 l_user_id NUMBER(15);
1345
1346 BEGIN
1347
1348
1349 -- Standard Start OF API savepoint
1350
1351 SAVEPOINT DELETE_SAMPLES;
1352
1353 -- Standard call to check for call compatibility.
1354
1355 IF NOT FND_API.Compatible_API_CALL
1356 (l_api_version , p_api_version , l_api_name , G_PKG_NAME)
1357 THEN
1358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1359 END IF;
1360
1361 -- Initialize message list if p_int_msg_list is set TRUE.
1362 IF FND_API.to_boolean(p_init_msg_list)
1363 THEN
1364 FND_MSG_PUB.Initialize;
1365 END IF;
1366
1367 -- Initialize API return Parameters
1368
1369 l_return_status := FND_API.G_RET_STS_SUCCESS;
1370
1371 -- Validate User Name Parameter
1372
1373 GMA_GLOBAL_GRP.Get_Who ( p_user_name => p_user_name
1374 ,x_user_id => l_user_id);
1375
1376 IF NVL(l_user_id, -1) < 0
1377 THEN
1378 GMD_API_PUB.Log_Message('GMD_INVALID_USER_NAME',
1379 'l_user_name', p_user_name);
1380 RAISE FND_API.G_EXC_ERROR;
1381 END IF;
1382
1383 -- Check Required Fields Present
1384
1385 IF ( p_qc_samples_rec.sample_id is NULL) THEN
1386 -- Validate that composite keys are present
1387
1388 IF ( p_qc_samples_rec.sample_no is NULL) THEN
1389 -- GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
1390 /* GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1391 'SAMPLE NO', ' IS NULL');*/
1392 GMD_API_PUB.Log_Message('GMD_SAMPLE_NUM_NULL');
1393 RAISE FND_API.G_EXC_ERROR;
1394 END IF;
1395
1396 IF ( p_qc_samples_rec.organization_id is NULL) THEN
1397 GMD_API_PUB.Log_Message('GMD_SAMPLE_ORGN_CODE_REQD');
1398 RAISE FND_API.G_EXC_ERROR;
1399 END IF;
1400
1401 END IF; -- Key Sample values Present
1402
1403
1404 -- Fetch the Test Header Row.
1405
1406 IF NOT GMD_SAMPLES_PVT.fetch_row (
1407 p_samples => p_qc_samples_rec,
1408 x_samples => l_qc_samples_out_rec) THEN
1409 -- dbms_output.put_line('Sample Record Not Found');
1410 RAISE FND_API.G_EXC_ERROR;
1411 END IF;
1412
1413 -- Validate that the Sample Header is Not Already Marked For Purge
1414
1415 IF l_qc_samples_out_rec.delete_mark = 1 THEN
1416 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
1417 'l_table_name', 'GMD_SAMPLES',
1418 'l_column_name', 'SAMPLE_ID',
1419 'l_key_value', l_qc_samples_out_rec.sample_id);
1420 RAISE FND_API.G_EXC_ERROR;
1421 END IF;
1422
1423
1424 -- Mark this record for Purge, this routine will also lock the row.
1425
1426 -- dbms_output.put_line('Delete Row');
1427 IF NOT GMD_SAMPLES_PVT.delete_row(
1428 p_sample_id => l_qc_samples_out_rec.sample_id,
1429 p_organization_id => l_qc_samples_out_rec.organization_id,
1430 p_sample_no => l_qc_samples_out_rec.sample_no
1431 ) THEN
1432 GMD_API_PUB.Log_Message('GMD_FAILED_TO_DELETE_ROW',
1433 'l_table_name', 'GMD_SAMPLES',
1434 'l_column_name','SAMPLE_ID',
1435 'l_key_value', l_qc_samples_out_rec.sample_id);
1436
1437 RAISE FND_API.G_EXC_ERROR;
1438
1439 END IF;
1440
1441 -- If the sample dispostion is not Retain or Cancel
1442 -- Then we must keep the active cnt on the Sampling event in synch.
1443
1444 IF l_qc_samples_out_rec.sample_disposition NOT IN ('0RT','7CN') THEN
1445
1446 -- Lock Sampling event row
1447 IF NOT GMD_SAMPLING_EVENTS_PVT.lock_row
1448 ( p_sampling_event_id => l_qc_samples_out_rec.sampling_event_id
1449 ) THEN
1450 GMD_API_PUB.Log_Message('GMD_LOCKING_FAILURE',
1451 'l_table_name', 'GMD_SAMPLING_EVENTS',
1452 'l_column_name','SAMPLING_EVENT_ID',
1453 'l_key_value', l_qc_samples_out_rec.sampling_event_id);
1454 RAISE FND_API.G_EXC_ERROR;
1455 ELSE
1456
1457 -- Update the sampling events Table
1458 UPDATE GMD_SAMPLING_EVENTS
1459 SET SAMPLE_ACTIVE_CNT = sample_active_cnt -1,
1460 LAST_UPDATED_BY = l_user_id,
1461 LAST_UPDATE_DATE = SYSDATE
1462 WHERE SAMPLING_EVENT_ID = l_qc_samples_out_rec.sampling_event_id;
1463
1464 END IF;
1465
1466 END IF; -- Sample Disposition Not Retain OR Cancel
1467
1468 -- Standard Check of p_commit.
1469 IF FND_API.to_boolean(p_commit)
1470 THEN
1471 COMMIT WORK;
1472 END IF;
1473
1474
1475 x_return_status := l_return_status;
1476
1477 EXCEPTION
1478 WHEN FND_API.G_EXC_ERROR THEN
1479 ROLLBACK TO DELETE_SAMPLES;
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481 FND_MSG_PUB.Count_AND_GET ( p_encoded => FND_API.G_FALSE
1482 , p_count => x_msg_count
1483 , p_data => x_msg_data
1484 );
1485
1486 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1487 ROLLBACK TO DELETE_SAMPLES;
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1490 , p_count => x_msg_count
1491 , p_data => x_msg_data
1492 );
1493
1494
1495
1496 WHEN OTHERS THEN
1497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1498 ROLLBACK TO DELETE_SAMPLES;
1499 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1500 , l_api_name
1501 );
1502
1503 FND_MSG_PUB.Count_AND_GET ( p_encoded=> FND_API.G_FALSE
1504 , p_count => x_msg_count
1505 , p_data => x_msg_data
1506 );
1507
1508 END DELETE_SAMPLES;
1509
1510
1511 PROCEDURE VALIDATE_ITEM_CONTROLS
1512 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
1513 p_grade IN VARCHAR2, -- Bug 4165704: added to validate grade control
1514 x_sample_rec OUT NOCOPY GMD_SAMPLES%ROWTYPE,
1515 x_return_status OUT NOCOPY VARCHAR2
1516 )
1517 IS
1518
1519 -- Bug 4165704: item controls gotten from call to Get_item_values
1520 -- CURSOR c_item_controls IS
1521 -- SELECT status_ctl
1522 -- , lot_ctl
1523 -- , sublot_ctl
1524 -- , loct_ctl
1525 -- FROM ic_item_mst
1526 -- WHERE item_id = p_sample_rec.item_id
1527 -- AND inactive_ind = 0
1528 -- AND delete_mark = 0;
1529
1530 -- Bug 4165704: mtl_lot_numbers replaced ic_lots_mst and lot_id no longer used
1531 --CURSOR c_item_lot IS
1532 --SELECT lot_no, sublot_no
1533 --FROM ic_lots_mst
1534 --WHERE item_id = p_sample_rec.item_id
1535 --AND lot_id = p_sample_rec.lot_id
1536 --AND delete_mark = 0;
1537 /*CURSOR c_item_lot IS
1538 SELECT 1
1539 FROM mtl_lot_numbers
1540 WHERE inventory_item_id = p_sample_rec.inventory_item_id
1541 --AND lot_number = p_sample_rec.lot_number
1542 AND organization_id = p_sample_rec.organization_id
1543 AND ((p_sample_rec.parent_lot_number IS NULL )
1544 OR (parent_lot_number = p_sample_rec.parent_lot_number));*/
1545
1546 -- srakrish bug 5687499: Implementing Child lot controlled onstraints. Replaced the above cursor with the one below.
1547 CURSOR c_item_lot(p_child_lot_flag varchar2) IS
1548 SELECT 1
1549 FROM mtl_lot_numbers
1550 WHERE inventory_item_id = p_sample_rec.inventory_item_id
1551 --AND lot_number = p_sample_rec.lot_number
1552 AND((p_child_lot_flag = 'Y' and p_sample_rec.lot_number IS NOT NULL and lot_number = p_sample_rec.lot_number)
1553 OR (p_child_lot_flag = 'Y' and p_sample_rec.lot_number IS NULL)
1554 OR (p_child_lot_flag = 'N' and lot_number = p_sample_rec.lot_number))
1555 AND organization_id = p_sample_rec.organization_id
1556 AND ((p_sample_rec.parent_lot_number IS NULL )
1557 OR (parent_lot_number = p_sample_rec.parent_lot_number));
1558
1559 -- bug# 3447280
1560 -- get lot_id if lot/sublot is specified.
1561 -- if lot and sublot both are specified without lot id
1562 -- and if new sampling event is created , lot id goes as NULL in gmd_sampling_events.
1563
1564 -- Bug 4165704: This cursor is no longer needed
1565 --CURSOR c_item_sublot IS
1566 --SELECT lot_id
1567 --FROM ic_lots_mst
1568 --WHERE item_id = p_sample_rec.item_id
1569 --AND lot_no = p_sample_rec.lot_no
1570 --AND sublot_no = p_sample_rec.sublot_no
1571 --AND delete_mark = 0;
1572
1573 l_dummy NUMBER;
1574 l_lot_ctl NUMBER;
1575 l_child_lot_flag VARCHAR2(2);
1576 l_lot_number MTL_LOT_NUMBERS.lot_number%TYPE;
1577 l_parent_lot_number MTL_LOT_NUMBERS.parent_lot_number%TYPE;
1578 l_sample_rec GMD_SAMPLES%ROWTYPE;
1579 l_return_status VARCHAR2(1);
1580
1581 l_sample_display GMD_SAMPLES_GRP.sample_display_rec;
1582
1583 BEGIN
1584
1585 -- Assign API local Variables;
1586 l_sample_rec := p_sample_rec;
1587 l_return_status := FND_API.G_RET_STS_SUCCESS;
1588
1589 -- We should only be validating W,I,C,S source type which
1590 -- all reqire the ite to exist.
1591
1592 IF (l_sample_rec.inventory_item_id IS NULL) THEN
1593 GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_REQD');
1594 RAISE FND_API.G_EXC_ERROR;
1595 ELSE
1596 -- Validate that the item is valid.
1597 -- Bug 4165704: validation is now done with call to get_item_values
1598 -- OPEN c_item_controls;
1599 -- FETCH c_item_controls INTO l_status_ctl, l_lot_ctl , l_sublot_ctl, l_item_loct_ctl;
1600 -- IF (c_item_controls%NOTFOUND) THEN
1601 -- CLOSE c_item_controls;
1602 -- GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
1603 -- RAISE FND_API.G_EXC_ERROR;
1604 -- END IF;
1605 -- CLOSE c_item_controls;
1606
1607 l_sample_display.organization_id := p_sample_rec.organization_id;
1608 l_sample_display.inventory_item_id := p_sample_rec.inventory_item_id;
1609
1610 gmd_samples_grp.get_item_values(p_sample_display => l_sample_display);
1611
1612 -- test for whether an item was found
1613 IF l_sample_display.item_number IS NULL THEN
1614 GMD_API_PUB.Log_Message('GMD_SPEC_ITEM_NOT_FOUND');
1615 RAISE FND_API.G_EXC_ERROR;
1616
1617 ELSE
1618 l_lot_ctl := l_sample_display.lot_control_code ;
1619 G_LOT_CTL := l_sample_display.lot_control_code ; -- 528854
1620 l_child_lot_flag := l_sample_display.child_lot_flag ;
1621 G_CHILD_LOT_FLAG := l_sample_display.child_lot_flag; -- 528854
1622 -- Bug 4165704: this is no longer needed; handled by 'item is locator controlled' routine
1623 -- and source subinventory and source locator are now retrieved here
1624 -- l_item_loct_ctl := l_sample_display.location_control_code ;
1625 l_sample_rec.source_subinventory := l_sample_display.source_subinventory;
1626 l_sample_rec.source_locator_id := l_sample_display.source_locator_id ;
1627 END IF ;
1628 END IF; -- Validate item.
1629
1630 IF (l_lot_ctl = 2) THEN -- item is lot controlled
1631
1632 -- 5283854 - move the below check to CREATE_SAMPLE
1633 /* IF ((l_sample_rec.lot_number IS NULL)
1634 AND ((l_sample_rec.parent_lot_number IS NULL) AND (l_sample_display.child_lot_flag = 'Y'))) THEN
1635 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1636 'WHAT', 'lot_number');
1637 RAISE FND_API.G_EXC_ERROR;
1638
1639 END IF; */
1640
1641 -- Bug 4165704: Logic changed for new parent lot/lot instead of lot/sublot
1642 -- ELSE -- Item is not lot controlled. . Item can only be sublot controlled
1643 IF ((l_sample_rec.lot_number IS NOT NULL)
1644 OR (l_sample_rec.parent_lot_number IS NOT NULL)) THEN
1645
1646 IF ( l_child_lot_flag = 'N') AND ( l_sample_rec.parent_lot_number IS NOT NULL) THEN
1647 GMD_API_PUB.Log_Message('GMD_QM_PARENT_LOT_NULL');
1648 RAISE FND_API.G_EXC_ERROR;
1649 END IF; -- If item is not child lot controlled parent lot shouldn't exist.
1650
1651
1652 OPEN c_item_lot(l_child_lot_flag); --srakrish bug 5687499: Passing the child lot flags to the cursor.
1653 FETCH c_item_lot INTO l_dummy;
1654 IF (c_item_lot%NOTFOUND) THEN
1655 CLOSE c_item_lot;
1656 GMD_API_PUB.Log_Message('GMD_ITEM_LOT_NOT_FOUND',
1657 'LOT_NUMBER', l_sample_rec.lot_number);
1658 RAISE FND_API.G_EXC_ERROR;
1659 END IF;
1660 CLOSE c_item_lot;
1661
1662
1663 -- Bug 4165704: this is handled elsewhere
1664 --ELSE -- Item is child controlled.
1665 --IF ( l_sample_rec.parent_lot_number IS NOT NULL) THEN
1666 --OPEN c_item_sublot;
1667 --FETCH c_item_sublot INTO l_lot_id;
1668 --IF (c_item_sublot%NOTFOUND) THEN
1669 --CLOSE c_item_sublot;
1670 --GMD_API_PUB.Log_Message('GMD_ITEM_SUBLOT_NOT_FOUND',
1671 -- 'SUBLOT_NO', l_sample_rec.sublot_no);
1672 --RAISE FND_API.G_EXC_ERROR;
1673 --END IF;
1674 --CLOSE c_item_sublot;
1675 --l_sample_rec.lot_id := l_lot_id;
1676 --END IF; -- validating lot/sublot
1677 --END IF; -- if item is lot/sublot controlled.
1678 -- 8276017 - move the below check to CREATE_SAMPLE as we needed to check lot optional on svr - SO comment out below 3 lines
1679 --ELSIF ( l_child_lot_flag = 'N') THEN --srakrish bug 5687499: Included the else part to check for the lot when the item is not child lot controlled. Lot is a required field.
1680 -- GMD_API_PUB.Log_Message('GMD_QM_LOT_REQUIRED');
1681 -- RAISE FND_API.G_EXC_ERROR;
1682 END IF; -- If item is lot controlled an lot_id specified.
1683 END IF; --lot control = 2
1684
1685
1686
1687
1688
1689 -- Bug 4165704: added grade control check here
1690 IF (l_sample_display.grade_control_flag = 'N' AND p_grade is NOT NULL) THEN
1691 GMD_API_PUB.Log_Message('GMD_GRADE_MUST_NULL');
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694
1695 -- Bug 5283854 : added revision control check here
1696 IF l_sample_display.Revision_qty_control_code = 2 then
1697 IF (l_sample_rec.revision IS NULL) THEN
1698 GMD_API_PUB.Log_Message('GMD_API_REVISION_CTRL');
1699 RAISE FND_API.G_EXC_ERROR;
1700
1701 END IF;
1702
1703 END IF;
1704 -- end 5283854
1705
1706 -- Set return parameters
1707 x_sample_rec := l_sample_rec;
1708 x_return_status := l_return_status;
1709
1710 EXCEPTION
1711
1712 WHEN FND_API.G_EXC_ERROR THEN
1713 x_return_status := FND_API.G_RET_STS_ERROR ;
1714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1716 WHEN OTHERS THEN
1717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1718
1719 END VALIDATE_ITEM_CONTROLS;
1720
1721
1722 -- Bug 4165704: changed the way locator control was handled
1723 PROCEDURE VALIDATE_INV_SAMPLE
1724 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
1725 p_locator_control IN NUMBER,
1726 x_return_status OUT NOCOPY VARCHAR2
1727 )
1728 IS
1729
1730
1731 -- Bug 4165704: changed for inventory convergence
1732 CURSOR c_subinventory_loct IS
1733 SELECT 1
1734 FROM mtl_item_locations
1735 WHERE organization_id = p_sample_rec.organization_id
1736 AND inventory_location_id = p_sample_rec.locator_id
1737 AND subinventory_code = p_sample_rec.subinventory;
1738 --FROM ic_loct_mst
1739 --WHERE whse_code = p_sample_rec.whse_code
1740 --AND location = p_sample_rec.location
1741 --AND delete_mark = 0;
1742
1743
1744 l_dummy NUMBER;
1745 l_return_status VARCHAR2(1);
1746
1747 l_locator_control NUMBER;
1748
1749 BEGIN
1750
1751 -- Assign API local Variables;
1752 l_return_status := FND_API.G_RET_STS_SUCCESS;
1753
1754 -- This Procedure Assumes that the whse and item have been validated.
1755
1756
1757
1758
1759 -- Validate the location
1760 IF (l_locator_control = 1) THEN
1761 IF (p_sample_rec.locator_id IS NOT NULL) THEN
1762 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
1763 RAISE FND_API.G_EXC_ERROR;
1764 END IF;
1765 ELSIF (l_locator_control > 1 ) THEN -- Item is location controlled.
1766 IF (p_sample_rec.locator_id IS NOT NULL) THEN
1767 -- Check that Location exist in MTL_ITEM_LOCATIONS
1768 OPEN c_subinventory_loct;
1769 FETCH c_subinventory_loct INTO l_dummy;
1770 IF (c_subinventory_loct%NOTFOUND) THEN
1771 -- CLOSE c_subinventory_loct;
1772 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
1773 'LOCATION', p_sample_rec.locator_id);
1774 -- RAISE FND_API.G_EXC_ERROR;
1775 END IF;
1776 CLOSE c_subinventory_loct;
1777 ELSE -- location CANNOT NULL
1778 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
1779 RAISE FND_API.G_EXC_ERROR;
1780 END IF; -- location IS NOT NULL
1781 END IF; -- l_locator_control
1782
1783
1784 -- Set return parameters
1785 x_return_status := l_return_status;
1786
1787 EXCEPTION
1788
1789 WHEN FND_API.G_EXC_ERROR THEN
1790 x_return_status := FND_API.G_RET_STS_ERROR ;
1791 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1793 WHEN OTHERS THEN
1794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1795
1796 END VALIDATE_INV_SAMPLE;
1797
1798 PROCEDURE VALIDATE_WIP_SAMPLE
1799 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
1800 x_sample_rec OUT NOCOPY GMD_SAMPLES%ROWTYPE,
1801 x_return_status OUT NOCOPY VARCHAR2
1802 )
1803 IS
1804
1805 --bug#2995114
1806 -- batch cursor was not correct.
1807 -- changed the batch cursor exactly as the batch Record Group in the Samples Form
1808 -- Bug # 4619570 Changed this Cursor to allow closed batches
1809 -- QZENG Bug 14179187 Added line to this cursor to support item which is not in formula
1810 CURSOR c_batch
1811 IS
1812 SELECT DISTINCT gr.recipe_id,
1813 ffm.formula_id,
1814 rout.routing_id
1815 FROM gme_batch_header bh
1816 , gme_material_details md
1817 , gmd_recipes_b gr/*gmd_recipes gr bug#4916871*/
1818 , gmd_recipe_validity_rules rvr
1819 , gmd_status gs
1820 , fm_matl_dtl fmd
1821 , fm_form_mst_b ffm /*fm_form_mst ffm bug# 4916871*/
1822 , gmd_routings_b rout /*gmd_routings rout bug#4916871*/
1823 , gem_lookups gl
1824 , gem_lookups gl2
1825 WHERE rout.routing_id(+) = bh.routing_id
1826 AND rvr.recipe_validity_rule_id = bh.recipe_validity_rule_id
1827 AND rvr.recipe_id = gr.recipe_id
1828 AND ffm.formula_id = bh.formula_id
1829 AND ffm.formula_id = fmd.formula_id
1830 AND fmd.formula_id = bh.formula_id
1831 AND ffm.delete_mark = 0
1832 AND fmd.formula_id = gr.formula_id
1833 AND (fmd.inventory_item_id = p_sample_rec.inventory_item_id
1834 OR md.formulaline_id IS NULL) -- 14179187 QZENG added for when item is not on formula
1835 AND gr.recipe_status = gs.status_code
1836 AND gs.status_code <> '1000'
1837 AND gr.delete_mark = 0
1838 AND gr.formula_id = bh.formula_id
1839 AND bh.batch_id = md.batch_id
1840 AND bh.batch_type = 0
1841 AND ( ( bh.batch_status IN (2, 3) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') ) /*-- wip or completed */
1842 OR ( bh.batch_status IN (2, 3,4 ) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) /*-- 4619570 wip or completed or closed */
1843 AND md.inventory_item_id = p_sample_rec.inventory_item_id /*--batch must be for item*/
1844 --AND bh.plant_code = p_sample_rec.organization_id -- bug 5291723 - plant code replace with organization
1845 AND bh.organization_id = p_sample_rec.organization_id -- bug 5291723
1846 AND bh.batch_status = gl.lookup_code
1847 AND gl.lookup_type = 'GME_BATCH_STATUS'
1848 AND gl2.lookup_type = 'GME_YES_NO'
1849 AND gl2.lookup_code = bh.terminated_ind
1850 AND bh.batch_id = p_sample_rec.batch_id
1851 AND NVL( p_sample_rec.recipe_id, gr.recipe_id) = gr.recipe_id
1852 AND NVL( p_sample_rec.formula_id, bh.formula_id) = bh.formula_id --
1853 AND ((p_sample_rec.routing_id IS NULL) OR (p_sample_rec.routing_id = bh.routing_id)) ;
1854
1855
1856 -- 9020340 new cursor use if p_sample_rec.recipe_id is null and p_sample_rec.formula_id is null and p_sample_rec.routing_id is null
1857
1858 cursor batch_no_formula is
1859 SELECT gr.recipe_id
1860 , gr.formula_id
1861 , gr.routing_id
1862 FROM gmd_recipes_b gr
1863 , gmd_recipe_validity_rules grvr
1864 , gme_batch_header gbh
1865 WHERE gbh.batch_id = p_sample_rec.batch_id
1866 AND grvr.recipe_validity_rule_id = gbh.recipe_validity_rule_id
1867 AND gr.recipe_id = grvr.recipe_id
1868 AND gbh.batch_type = 0
1869 AND ( ( gbh.batch_status IN (2, 3) and (
1870 NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') ) /*--
1871 wip or completed */
1872 OR ( gbh.batch_status IN (2, 3, 4 ) and (
1873 NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) /*-- wip
1874 or completed or closed */
1875 ;
1876
1877
1878
1879 CURSOR c_formulaline_id ( l_formula_id In NUMBER)
1880 IS
1881 SELECT 1
1882 FROM fm_matl_dtl fmd
1883 WHERE fmd.inventory_item_id = p_sample_rec.inventory_item_id
1884 AND fmd.formula_id = l_formula_id
1885 AND fmd.formulaline_id = p_sample_rec.formulaline_id;
1886
1887
1888 -- Bug 4640143: added material_detail_id to samples
1889 CURSOR c_material_detail_id ( l_batch_id In NUMBER)
1890 IS
1891 SELECT 1
1892 FROM gme_material_details
1893 WHERE inventory_item_id = p_sample_rec.inventory_item_id
1894 AND organization_id = p_sample_rec.organization_id
1895 AND batch_id = l_batch_id
1896 AND material_detail_id = p_sample_rec.material_detail_id;
1897
1898
1899 CURSOR c_batchstep
1900 IS
1901 SELECT bs.batchstep_no, bs.oprn_id
1902 FROM gme_batch_steps bs,
1903 gmd_operations o
1904 WHERE bs.oprn_id = o.oprn_id
1905 AND bs.batchstep_id = p_sample_rec.step_id
1906 AND bs.batch_id = p_sample_rec.batch_id
1907 AND NVL( p_sample_rec.step_no, bs.batchstep_no) = bs.batchstep_no
1908 AND o.delete_mark = 0
1909 AND bs.delete_mark = 0;
1910
1911 CURSOR c_oprn
1912 IS
1913 SELECT 1
1914 FROM gmd_operations o
1915 WHERE o.delete_mark = 0
1916 AND o.oprn_id = p_sample_rec.oprn_id;
1917
1918
1919
1920 l_dummy NUMBER;
1921 l_return_status VARCHAR2(1);
1922 l_sample_rec GMD_SAMPLES%ROWTYPE;
1923
1924
1925 BEGIN
1926
1927
1928 -- Assign API local Variables;
1929 l_return_status := FND_API.G_RET_STS_SUCCESS;
1930 l_sample_rec := p_sample_rec;
1931
1932
1933 -- For WIP sample the batch_id must be specified.
1934 IF (p_sample_rec.batch_id IS NULL ) THEN
1935 GMD_API_PUB.Log_Message('GMD_NO_WIP_PARAM');
1936 RAISE FND_API.G_EXC_ERROR;
1937 END IF;
1938
1939 ----------------------------------------------
1940 -- Derive Values Using Batch_id
1941 -----------------------------------------------
1942 -- 9020340
1943 --use new cursor if only batch id is passed
1944
1945 IF ( p_sample_rec.batch_id IS NOT NULL and p_sample_rec.recipe_id is null
1946 and p_sample_rec.formula_id is null and p_sample_rec.routing_id is null ) THEN -- get values
1947 OPEN batch_no_formula;
1948 FETCH batch_no_formula
1949 INTO l_sample_rec.recipe_id,
1950 l_sample_rec.formula_id,
1951 l_sample_rec.routing_id;
1952 IF (batch_no_formula%NOTFOUND) THEN
1953 CLOSE batch_no_formula;
1954 GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
1955 -- GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
1956 RAISE FND_API.G_EXC_ERROR;
1957 END IF;
1958 CLOSE batch_no_formula;
1959
1960 ELSIF p_sample_rec.batch_id IS NOT NULL then
1961
1962 OPEN c_batch;
1963 FETCH c_batch
1964 INTO l_sample_rec.recipe_id,
1965 l_sample_rec.formula_id,
1966 l_sample_rec.routing_id;
1967 IF (c_batch%NOTFOUND) THEN
1968 CLOSE c_batch;
1969 GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
1970 RAISE FND_API.G_EXC_ERROR;
1971 END IF;
1972 CLOSE c_batch;
1973
1974 END IF;
1975
1976 ----------------------------------------------
1977 -- Validate derived values against Sample rec
1978 -----------------------------------------------
1979
1980 -- Validate formula_line_id if specified.
1981
1982 -- Bug 4640143: added test for batch_id
1983 IF ( l_sample_rec.formulaline_id IS NOT NULL)
1984 AND (l_sample_rec.batch_id IS NULL) THEN
1985 OPEN c_formulaline_id ( l_sample_rec.formula_id);
1986 FETCH c_formulaline_id INTO l_dummy;
1987 IF (c_formulaline_id%NOTFOUND) THEN
1988 CLOSE c_formulaline_id;
1989 GMD_API_PUB.Log_Message('GMD_FORMULA_LINE_NOT_FOUND');
1990 RAISE FND_API.G_EXC_ERROR;
1991 END IF;
1992 CLOSE c_formulaline_id;
1993 END IF;
1994
1995 -- Bug 4640143: Validate material_detail_id if specified.
1996 IF ( l_sample_rec.material_detail_id is NOT NULL) THEN
1997 OPEN c_material_detail_id ( l_sample_rec.batch_id);
1998 FETCH c_material_detail_id INTO l_dummy;
1999 IF (c_material_detail_id%NOTFOUND) THEN
2000 CLOSE c_material_detail_id;
2001 GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
2002 RAISE FND_API.G_EXC_ERROR;
2003 END IF;
2004 CLOSE c_material_detail_id;
2005 END IF;
2006
2007
2008
2009 -- VAlidate Step_no , even though the step_id can be defined
2010 -- for the batch or routing, we will only validate against
2011 -- the batch.
2012
2013 IF (l_sample_rec.step_id is NOT NULL ) THEN
2014 OPEN c_batchstep;
2015 FETCH c_batchstep
2016 INTO l_sample_rec.step_no,l_sample_rec.oprn_id;
2017 IF (c_batchstep%NOTFOUND) THEN
2018 CLOSE c_batchstep;
2019 GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2020 RAISE FND_API.G_EXC_ERROR;
2021 END IF;
2022 CLOSE c_batchstep;
2023
2024 END IF;
2025
2026 -- Operation is valid (check only if step is not specified, because
2027 -- otherwise it will default from the step chosen.)
2028 IF (l_sample_rec.step_id IS NULL AND l_sample_rec.oprn_id IS NOT NULL) THEN
2029 OPEN c_oprn;
2030 FETCH c_oprn
2031 INTO l_dummy;
2032 IF (c_oprn%NOTFOUND) THEN
2033 CLOSE c_oprn;
2034 GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2035 RAISE FND_API.G_EXC_ERROR;
2036 END IF;
2037 CLOSE c_oprn;
2038 END IF;
2039
2040 -- Set return parameters
2041 x_sample_rec := l_sample_rec;
2042 x_return_status := l_return_status;
2043
2044
2045 EXCEPTION
2046
2047 WHEN FND_API.G_EXC_ERROR THEN
2048 x_return_status := FND_API.G_RET_STS_ERROR ;
2049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2051 WHEN OTHERS THEN
2052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2053
2054 END VALIDATE_WIP_SAMPLE;
2055
2056 PROCEDURE VALIDATE_CUST_SAMPLE
2057 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
2058 x_return_status OUT NOCOPY VARCHAR2
2059 )
2060 IS
2061
2062 CURSOR c_cust IS
2063 SELECT csua.org_id
2064 FROM hr_operating_units ou
2065 , hz_cust_acct_sites_all casa
2066 , hz_cust_site_uses_all csua
2067 , hz_parties hzp
2068 , hz_cust_accounts_all hzca
2069 WHERE ou.organization_id = csua.org_id
2070 AND casa.cust_acct_site_id = csua.cust_acct_site_id
2071 AND casa.cust_account_id = hzca.cust_account_id
2072 AND casa.org_id = csua.org_id
2073 AND hzp.party_id = hzca.party_id
2074 AND NVL( p_sample_rec.org_id, csua.org_id) = csua.org_id
2075 AND hzca.cust_account_id = p_sample_rec.cust_id;
2076
2077
2078 CURSOR c_ship_to IS
2079 SELECT 1
2080 FROM hz_cust_acct_sites_all casa
2081 , hz_cust_site_uses_all csua
2082 , hz_cust_accounts_all caa
2083 WHERE casa.cust_acct_site_id = csua.cust_acct_site_id
2084 AND casa.org_id = csua.org_id
2085 AND casa.cust_account_id = caa.cust_account_id
2086 AND csua.site_use_code = 'SHIP_TO'
2087 AND NVL( p_sample_rec.org_id, csua.org_id) = csua.org_id
2088 AND caa.cust_account_id = p_sample_rec.cust_id
2089 AND csua.site_use_id = p_sample_rec.ship_to_site_id;
2090
2091 /*CURSOR c_order IS
2092 SELECT 1
2093 FROM oe_order_headers_all oha
2094 , oe_order_lines_all oola
2095 , oe_transaction_types_tl ttt
2096 WHERE oola.header_id = oha.header_id
2097 AND oola.inventory_item_id IN
2098 (SELECT msi.inventory_item_id
2099 FROM mtl_system_items msi
2100 WHERE msi.segment1 IN
2101 (SELECT segment1
2102 FROM mtl_system_items_b
2103 WHERE inventory_item_id = p_sample_rec.inventory_item_id))
2104 AND oha.order_type_id = ttt.transaction_type_id
2105 AND NVL( p_sample_rec.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
2106 AND NVL( p_sample_rec.organization_id, oha.org_id) = oha.org_id
2107 AND p_sample_rec.cust_id = oha.sold_to_org_id
2108 AND oha.header_id = p_sample_rec.order_id
2109 AND oha.cancelled_flag <> 'Y'
2110 AND ttt.language = USERENV('LANG');*/
2111
2112 --CURSOR c_order rewritten as part of bug# 4916871
2113 --CURSOR c_order rewritten as part of bug# 5335008
2114 CURSOR c_order IS
2115 SELECT 1
2116 FROM oe_order_headers_all oha
2117 , oe_order_lines_all oola
2118 , oe_transaction_types_tl ttt
2119 WHERE oola.header_id = oha.header_id
2120 AND oola.inventory_item_id = p_sample_rec.inventory_item_id
2121 AND oha.order_type_id = ttt.transaction_type_id
2122 AND (NVL( p_sample_rec.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id -- 5335008
2123 OR NVL( p_sample_rec.ship_to_site_id, oola.invoice_to_org_id) = oola.invoice_to_org_id) -- 5335008
2124 AND NVL( p_sample_rec.org_id, oha.org_id) = oha.org_id -- 5335008
2125 AND NVL( p_sample_rec.cust_id,oha.sold_to_org_id) = oha.sold_to_org_id -- 5335008
2126 AND oha.header_id = p_sample_rec.order_id
2127 AND oha.cancelled_flag <> 'Y'
2128 AND ttt.language = USERENV('LANG');
2129
2130 CURSOR c_order_line IS
2131 SELECT 1
2132 FROM oe_order_lines_all oola
2133 WHERE oola.header_id = p_sample_rec.order_id
2134 AND NVL( p_sample_rec.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
2135 AND oola.inventory_item_id IN
2136 (SELECT msi.inventory_item_id
2137 FROM mtl_system_items msi
2138 WHERE msi.segment1 IN
2139 (SELECT segment1
2140 FROM mtl_system_items_b
2141 WHERE inventory_item_id = p_sample_rec.inventory_item_id))
2142 AND oola.header_id = p_sample_rec.order_id
2143 AND oola.line_id = p_sample_rec.order_line_id;
2144
2145
2146 l_dummy NUMBER;
2147 l_return_status VARCHAR2(1);
2148
2149
2150 BEGIN
2151
2152
2153 -- Assign API local Variables;
2154 l_return_status := FND_API.G_RET_STS_SUCCESS;
2155
2156 -- For A Customer Sample Source the Cust_id
2157 -- Must be Specified. This also validates the Org_id
2158
2159 IF (p_sample_rec.cust_id IS NULL) THEN
2160 GMD_API_PUB.Log_Message('GMD_CUSTOMER_REQD');
2161 RAISE FND_API.G_EXC_ERROR;
2162 ELSE
2163 OPEN c_cust;
2164 FETCH c_cust
2165 INTO l_dummy;
2166 IF (c_cust%NOTFOUND) THEN
2167 CLOSE c_cust;
2168 GMD_API_PUB.Log_Message('GMD_CUSTOMER_NOT_FOUND');
2169 RAISE FND_API.G_EXC_ERROR;
2170 END IF;
2171 CLOSE c_cust;
2172 END IF;
2173
2174 -- Validate Ship_to
2175
2176 IF (p_sample_rec.ship_to_site_id IS NOT NULL) THEN
2177 OPEN c_ship_to;
2178 FETCH c_ship_to
2179 INTO l_dummy;
2180 IF (c_ship_to%NOTFOUND) THEN
2181 CLOSE c_ship_to;
2182 GMD_API_PUB.Log_Message('GMD_SHIP_TO_NOT_FOUND');
2183 RAISE FND_API.G_EXC_ERROR;
2184 END IF;
2185 CLOSE c_ship_to;
2186 END IF;
2187
2188 -- Validate Order ID
2189
2190 IF (p_sample_rec.order_id IS NOT NULL)
2191 THEN
2192 OPEN c_order;
2193 FETCH c_order
2194 INTO l_dummy;
2195 IF (c_order%NOTFOUND)
2196 THEN
2197 CLOSE c_order;
2198 GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
2199 RAISE FND_API.G_EXC_ERROR;
2200 END IF;
2201 CLOSE c_order;
2202 END IF;
2203
2204 -- Validate Order Line ID
2205 -- Bug 3151607 ( modified the IF/ELSE condition below)
2206
2207 /* IF (p_sample_rec.order_line_id IS NOT NULL
2208 AND p_sample_rec.order_id IS NOT NULL) THEN
2209
2210 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2211 'WHAT', 'the order_id must not be NULL');
2212 RAISE FND_API.G_EXC_ERROR;
2213 ELSE
2214
2215 OPEN c_order_line;
2216 FETCH c_order_line
2217 INTO l_dummy;
2218 IF (c_order_line%NOTFOUND) THEN
2219 CLOSE c_order_line;
2220 GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
2221 RAISE FND_API.G_EXC_ERROR;
2222 END IF;
2223 CLOSE c_order_line;
2224 END IF; */
2225
2226 IF (p_sample_rec.order_line_id IS NOT NULL) THEN
2227 IF (p_sample_rec.order_id IS NULL) THEN
2228 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2229 'WHAT', 'the order_id must not be NULL');*/
2230 GMD_API_PUB.Log_Message('GMD_ORDER_ID_MUST_NOT_NULL');
2231 RAISE FND_API.G_EXC_ERROR;
2232 END IF;
2233
2234 OPEN c_order_line;
2235 FETCH c_order_line
2236 INTO l_dummy;
2237
2238 IF (c_order_line%NOTFOUND) THEN
2239 CLOSE c_order_line;
2240 GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
2241 RAISE FND_API.G_EXC_ERROR;
2242 END IF;
2243 CLOSE c_order_line;
2244
2245 END IF;
2246
2247
2248 -- Set return parameters
2249 x_return_status := l_return_status;
2250
2251 EXCEPTION
2252
2253 WHEN FND_API.G_EXC_ERROR THEN
2254 x_return_status := FND_API.G_RET_STS_ERROR ;
2255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2257 WHEN OTHERS THEN
2258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2259 END VALIDATE_CUST_SAMPLE;
2260
2261
2262
2263
2264 PROCEDURE VALIDATE_SUPP_SAMPLE
2265 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
2266 x_return_status OUT NOCOPY VARCHAR2
2267 )
2268 IS
2269
2270 CURSOR c_supplier IS
2271 SELECT 1
2272 FROM po_vendors v
2273 WHERE v.vendor_id = p_sample_rec.supplier_id
2274 AND v.enabled_flag = 'Y';
2275
2276 CURSOR c_supplier_site IS
2277 SELECT 1
2278 FROM po_vendor_sites_all v
2279 WHERE (v.purchasing_site_flag = 'Y'
2280 OR v.rfq_only_site_flag = 'Y')
2281 AND sysdate < NVL(inactive_date, sysdate + 1)
2282 AND v.vendor_id = p_sample_rec.supplier_id
2283 AND v.vendor_site_id = p_sample_rec.supplier_site_id;
2284
2285 -- Bug# 5226352
2286 -- Changed mtl_system_items to mtl_system_items_b and added organization_id where clause for msi to fix performance issues
2287 CURSOR c_po
2288 IS
2289 SELECT 1
2290 FROM po_headers_all pha
2291 WHERE pha.po_header_id IN
2292 (SELECT pla.po_header_id
2293 FROM po_lines_all pla
2294 WHERE pla.po_header_id = pha.po_header_id
2295 AND pla.item_id IN
2296 (SELECT msi.inventory_item_id
2297 FROM mtl_system_items_b msi
2298 WHERE organization_id = p_sample_rec.organization_id
2299 AND inventory_item_id = p_sample_rec.inventory_item_id))
2300 AND pha.vendor_id = p_sample_rec.supplier_id
2301 AND pha.vendor_site_id = p_sample_rec.supplier_site_id
2302 AND pha.po_header_id = p_sample_rec.po_header_id;
2303
2304 CURSOR c_po_line IS
2305 SELECT 1
2306 FROM po_lines_all pla
2307 WHERE pla.item_id IN
2308 (SELECT msi.inventory_item_id
2309 FROM mtl_system_items msi
2310 WHERE msi.segment1 IN
2311 (SELECT segment1
2312 FROM mtl_system_items_b
2313 WHERE inventory_item_id = p_sample_rec.inventory_item_id))
2314 AND pla.po_header_id = p_sample_rec.po_header_id
2315 AND pla.po_line_id = p_sample_rec.po_line_id;
2316
2317 --Bug# 3576573. Added cusor c_receipt_info
2318 -- Bug 3970893: receipt line id is no longer the transaction id, it is now shipment_line_id
2319 /*CURSOR c_receipt_info IS
2320 SELECT 1
2321 FROM rcv_shipment_headers rsh , rcv_transactions rt
2322 WHERE (p_sample_rec.receipt_id, p_sample_rec.receipt_line_id) IN
2323 (SELECT rsh.shipment_header_id,
2324 rsl.shipment_line_id -- rt.transaction_id
2325 FROM rcv_shipment_lines rsl
2326 WHERE rsl.po_header_id = p_sample_rec.po_header_id
2327 AND rsl.item_id IN
2328 (SELECT msi.inventory_item_id
2329 FROM mtl_system_items msi
2330 WHERE msi.segment1 IN
2331 (SELECT segment1
2332 FROM mtl_system_items_b
2333 WHERE inventory_item_id = p_sample_rec.inventory_item_id))
2334 AND rsl.po_line_id = p_sample_rec.po_line_id
2335 AND rsl.shipment_header_id = rsh.shipment_header_id);*/
2336
2337 --CURSOR c_receipt_info rewritten as part of bug# 4916871
2338 CURSOR c_receipt_info IS
2339 SELECT 1
2340 FROM rcv_shipment_headers rsh
2341 WHERE (p_sample_rec.receipt_id, p_sample_rec.receipt_line_id) IN
2342 (SELECT rsh.shipment_header_id,
2343 rsl.shipment_line_id
2344 FROM rcv_shipment_lines rsl
2345 WHERE rsl.po_header_id = p_sample_rec.po_header_id
2346 AND rsl.item_id = p_sample_rec.inventory_item_id
2347 AND rsl.po_line_id = p_sample_rec.po_line_id
2348 AND rsl.shipment_header_id = rsh.shipment_header_id);
2349
2350 -- AND rt.shipment_header_id = rsl.shipment_header_id
2351 -- AND rt.shipment_line_id = rsl.shipment_line_id
2352 -- AND rt.transaction_type = 'RECEIVE');
2353
2354 l_dummy NUMBER;
2355 l_return_status VARCHAR2(1);
2356
2357
2358
2359 BEGIN
2360 --dbms_output.put_line('b4 validate supplier sample ');
2361
2362 -- Assign API local Variables;
2363 l_return_status := FND_API.G_RET_STS_SUCCESS;
2364
2365 -- supplier_id : This field is mandatory
2366
2367 IF (p_sample_rec.supplier_id IS NULL)
2368 THEN
2369 GMD_API_PUB.Log_Message('GMD_SUPPLIER_REQD');
2370 RAISE FND_API.G_EXC_ERROR;
2371 ELSE
2372 OPEN c_supplier;
2373 FETCH c_supplier INTO l_dummy;
2374 IF (c_supplier%NOTFOUND)
2375 THEN
2376 CLOSE c_supplier;
2377 GMD_API_PUB.Log_Message('GMD_SUPPLIER_NOT_FOUND');
2378 RAISE FND_API.G_EXC_ERROR;
2379 END IF;
2380 CLOSE c_supplier;
2381 END IF;
2382
2383 --=========================================================================
2384 -- supplier_site_id :
2385 --=========================================================================
2386 IF ( p_sample_rec.supplier_site_id IS NOT NULL)
2387 THEN
2388 OPEN c_supplier_site;
2389 FETCH c_supplier_site
2390 INTO l_dummy;
2391 IF (c_supplier_site%NOTFOUND)
2392 THEN
2393 CLOSE c_supplier_site;
2394 FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
2395 FND_MESSAGE.SET_TOKEN('WHAT', 'SUPPLIER_SITE_ID');
2396 FND_MESSAGE.SET_TOKEN('VALUE', p_sample_rec.supplier_site_id);
2397 FND_MSG_PUB.ADD;
2398 RAISE FND_API.G_EXC_ERROR;
2399 END IF;
2400 CLOSE c_supplier_site;
2401 END IF;
2402
2403 --=========================================================================
2404 -- po_header_id :
2405 -- When po_header_id is NOT NULL, then supplier_site_id must be NOT NULL
2406 --=========================================================================
2407 -- PO
2408 IF (p_sample_rec.po_header_id IS NOT NULL)
2409 THEN
2410 IF (p_sample_rec.supplier_site_id IS NULL)
2411 THEN
2412 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2413 'WHAT', 'supplier_site_id must not be NULL');*/
2414 GMD_API_PUB.Log_Message('GMD_SUPP_SITE_MUST_NOT_NULL');
2415 RAISE FND_API.G_EXC_ERROR;
2416 END IF;
2417
2418 OPEN c_po;
2419 FETCH c_po INTO l_dummy;
2420 IF (c_po%NOTFOUND)
2421 THEN
2422 CLOSE c_po;
2423 GMD_API_PUB.Log_Message('GMD_PO_NOT_FOUND');
2424 RAISE FND_API.G_EXC_ERROR;
2425 END IF;
2426 CLOSE c_po;
2427 END IF;
2428
2429
2430 --=========================================================================
2431 -- po_line_id :
2432 -- When po_line_id is NOT NULL, then supplier_site_id AND po_header_id must be NOT NULL
2433 --=========================================================================
2434 -- PO Line
2435 IF (p_sample_rec.po_line_id IS NOT NULL)
2436 THEN
2437 IF (p_sample_rec.po_header_id IS NULL)
2438 THEN
2439 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2440 'WHAT', 'po_header_id must not be NULL');*/
2441 GMD_API_PUB.Log_Message('GMD_PO_HEADER_MUST_NOT_NULL');
2442 RAISE FND_API.G_EXC_ERROR;
2443 END IF;
2444
2445 IF (p_sample_rec.supplier_site_id IS NULL)
2446 THEN
2447 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2448 'WHAT', 'supplier site must not be NULL');*/
2449 GMD_API_PUB.Log_Message('GMD_SUPP_SITE_MUST_NOT_NULL');
2450 RAISE FND_API.G_EXC_ERROR;
2451 END IF;
2452
2453 OPEN c_po_line;
2454 FETCH c_po_line INTO l_dummy;
2455 IF (c_po_line%NOTFOUND)
2456 THEN
2457 CLOSE c_po_line;
2458 GMD_API_PUB.Log_Message('GMD_PO_LINE_NOT_FOUND');
2459 RAISE FND_API.G_EXC_ERROR;
2460 END IF;
2461 CLOSE c_po_line;
2462 END IF;
2463
2464 --Bug# 3576573. added validations for receipt information
2465 --=========================================================================================
2466 --receipt_id and receipt_line_id
2467 --when receipt information is NOT NULL po_header_id and po_line_id must not be NULL
2468 --=========================================================================================
2469
2470 IF(p_sample_rec.receipt_id IS NOT NULL OR p_sample_rec.receipt_line_id IS NOT NULL)
2471 THEN
2472 IF (p_sample_rec.po_header_id IS NULL)
2473 THEN
2474 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2475 'WHAT', 'po_header_id must not be NULL');*/
2476 GMD_API_PUB.Log_Message('GMD_PO_HEADER_MUST_NOT_NULL');
2477 RAISE FND_API.G_EXC_ERROR;
2478 END IF;
2479 IF (p_sample_rec.po_line_id IS NULL)
2480 THEN
2481 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2482 'WHAT', 'po_line_id must not be NULL');*/
2483 GMD_API_PUB.Log_Message('GMD_PO_LINE_MUST_NOT_NULL');
2484 RAISE FND_API.G_EXC_ERROR;
2485 END IF;
2486 END IF;
2487
2488 IF (p_sample_rec.receipt_id IS NOT NULL AND p_sample_rec.receipt_line_id IS NULL)
2489 THEN
2490 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2491 'WHAT', 'receipt_line_id must not be NULL');*/
2492 GMD_API_PUB.Log_Message('GMD_RECEIPT_LINE_MUST_NOT_NULL');
2493 RAISE FND_API.G_EXC_ERROR;
2494 END IF;
2495
2496 IF (p_sample_rec.receipt_line_id IS NOT NULL AND p_sample_rec.receipt_id IS NULL)
2497 THEN
2498 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2499 'WHAT', 'receipt_id must not be NULL');*/
2500 GMD_API_PUB.Log_Message('GMD_RECEIPT_MUST_NOT_NULL');
2501 RAISE FND_API.G_EXC_ERROR;
2502 END IF;
2503
2504 IF(p_sample_rec.receipt_id IS NOT NULL AND p_sample_rec.receipt_line_id IS NOT NULL)
2505 THEN
2506 OPEN c_receipt_info;
2507 FETCH c_receipt_info INTO l_dummy;
2508 IF (c_receipt_info%NOTFOUND)
2509 THEN
2510 CLOSE c_receipt_info;
2511 GMD_API_PUB.Log_Message('GMD_RECEIPT_NOT_FOUND');
2512 RAISE FND_API.G_EXC_ERROR;
2513 END IF;
2514 CLOSE c_receipt_info;
2515 END IF;
2516
2517
2518
2519 -- Set return parameters
2520 x_return_status := l_return_status;
2521
2522
2523 EXCEPTION
2524
2525 WHEN FND_API.G_EXC_ERROR THEN
2526 x_return_status := FND_API.G_RET_STS_ERROR ;
2527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2529 WHEN OTHERS THEN
2530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2531 END VALIDATE_SUPP_SAMPLE;
2532
2533
2534 -- Added for MPK Bug
2535 PROCEDURE VALIDATE_STABILITY_SAMPLE
2536 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
2537 x_return_status OUT NOCOPY VARCHAR2
2538 )
2539 IS
2540
2541 BEGIN
2542
2543 -- Stability Study samples cannot be loaded through Create Private API
2544 x_return_status := FND_API.G_RET_STS_ERROR;
2545
2546 END VALIDATE_STABILITY_SAMPLE;
2547
2548
2549
2550 PROCEDURE VALIDATE_RESOURCE_SAMPLE
2551 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
2552 x_return_status OUT NOCOPY VARCHAR2
2553 )
2554 IS
2555
2556 -- bug# 3452384
2557 -- cursor to validate resource
2558 CURSOR c_resource IS
2559 SELECT 1
2560 FROM cr_rsrc_mst
2561 WHERE delete_mark = 0
2562 and resources = p_sample_rec.resources ;
2563
2564 l_dummy NUMBER(1);
2565 l_return_status VARCHAR2(1);
2566
2567 BEGIN
2568 -- Assign API local Variables;
2569 l_return_status := FND_API.G_RET_STS_SUCCESS;
2570
2571 IF ((p_sample_rec.inventory_item_id IS NOT NULL)
2572 OR (p_sample_rec.lot_number IS NOT NULL)) THEN
2573 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2574 'Item and Lot', ' should not be specified for Monitor sample');*/
2575 GMD_API_PUB.Log_Message('GMD_ITEM_LOT_MONITOR_SAMPLE');
2576 RAISE FND_API.G_EXC_ERROR;
2577 END IF; -- test item sources
2578
2579 IF ((p_sample_rec.sample_qty IS NOT NULL)
2580 OR (p_sample_rec.sample_qty_uom IS NOT NULL)) THEN
2581 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2582 'Quantity and UOM', ' should not be specified for Monitor sample');*/
2583 GMD_API_PUB.Log_Message('GMD_QTY_UOM_MONITOR_SAMPLE');
2584 RAISE FND_API.G_EXC_ERROR;
2585 END IF; -- test item sources
2586
2587 IF p_sample_rec.resources IS NULL THEN
2588 GMD_API_PUB.Log_Message('GMD_QC_RESOURCE_REQD');
2589 RAISE FND_API.G_EXC_ERROR;
2590 END IF; -- test item sources
2591
2592 -- validate resource
2593 -- start of bug# 3452384
2594
2595 OPEN c_resource;
2596 FETCH c_resource INTO l_dummy;
2597 IF (c_resource%NOTFOUND)
2598 THEN
2599 CLOSE c_resource;
2600 GMD_API_PUB.Log_Message('GMD_RESOURCE_NOT_FOUND',
2601 'RESOURCE', p_sample_rec.resources);
2602 RAISE FND_API.G_EXC_ERROR;
2603 END IF;
2604 CLOSE c_resource;
2605
2606 -- end of bug 3452384
2607
2608 -- Set return parameters
2609 x_return_status := l_return_status;
2610
2611 EXCEPTION
2612
2613 WHEN FND_API.G_EXC_ERROR THEN
2614 x_return_status := FND_API.G_RET_STS_ERROR ;
2615 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2617 WHEN OTHERS THEN
2618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2619
2620 END VALIDATE_RESOURCE_SAMPLE;
2621
2622
2623
2624 PROCEDURE VALIDATE_LOCATION_SAMPLE
2625 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE,
2626 p_locator_control IN NUMBER,
2627 x_return_status OUT NOCOPY VARCHAR2
2628 )
2629 IS
2630
2631 -- Bug 4165704: changed for inventory convergence
2632 CURSOR c_subinventory_loct IS
2633 SELECT 1
2634 FROM mtl_item_locations
2635 WHERE organization_id = p_sample_rec.organization_id
2636 AND inventory_location_id = p_sample_rec.locator_id
2637 AND subinventory_code = p_sample_rec.subinventory;
2638 --FROM ic_loct_mst
2639 --WHERE whse_code = p_sample_rec.whse_code
2640 --AND location = p_sample_rec.location
2641 --AND delete_mark = 0;
2642
2643 l_dummy NUMBER;
2644 l_return_status VARCHAR2(1);
2645
2646
2647 BEGIN
2648 -- Assign API local Variables;
2649 l_return_status := FND_API.G_RET_STS_SUCCESS;
2650
2651 IF ((p_sample_rec.inventory_item_id IS NOT NULL)
2652 OR (p_sample_rec.lot_number IS NOT NULL)) THEN
2653 GMD_API_PUB.Log_Message('GMD_ITEM_LOT_MONITOR_SAMPLE');
2654 RAISE FND_API.G_EXC_ERROR;
2655 END IF; -- test item sources
2656
2657 IF ((p_sample_rec.sample_qty IS NOT NULL)
2658 OR (p_sample_rec.sample_qty_uom IS NOT NULL)) THEN
2659 GMD_API_PUB.Log_Message('GMD_QTY_UOM_MONITOR_SAMPLE');
2660 RAISE FND_API.G_EXC_ERROR;
2661 END IF; -- test item sources
2662
2663 IF p_sample_rec.subinventory IS NULL THEN
2664 GMD_API_PUB.Log_Message('GMD_QC_WHSE_REQD');
2665 RAISE FND_API.G_EXC_ERROR;
2666 END IF; -- test item sources
2667
2668 -- This Procedure Assumes that the whse and item have been validated.
2669
2670 -- Validate the location
2671 --=======================================================================
2672 -- Location :
2673 -- : The locator is nullable even if locator_control > 1,
2674 -- but must be checked.
2675
2676 -- Bug 4165704: This table is changed with new locator control
2677 -- and checking for location control is up to new routine
2678 -- called earlier
2679 -- This field should follow this table :
2680 -- item_loct_ctl whse_code whse_loct_ctl location Possible
2681 -- 0 NULL - NULL No
2682 -- 0 NULL - Checked No
2683 -- 0 NOTNULL 0 NULL No
2684 -- 0 NOTNULL 0 Checked No
2685 -- 0 NOTNULL 1 NULL No
2686 -- 0 NOTNULL 1 Checked No
2687 -- 1 NULL - NULL Yes
2688 -- 1 NULL - Checked No
2689 -- 1 NOTNULL 0 NULL Yes
2690 -- 1 NOTNULL 0 Checked No
2691 -- 1 NOTNULL 1 NULL No
2692 -- 1 NOTNULL 1 Checked Yes
2693 --=========================================================================
2694
2695 IF (p_locator_control = 1) THEN
2696 IF (p_sample_rec.locator_id IS NOT NULL) THEN
2697 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
2698 RAISE FND_API.G_EXC_ERROR;
2699 END IF;
2700 ELSE
2701 -- Item is location controlled.
2702 IF (p_sample_rec.locator_id IS NULL) THEN
2703 -- Location cannot be NULL in this case.
2704 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
2705 RAISE FND_API.G_EXC_ERROR;
2706 ELSE
2707 -- Check that Location exist in MTL_ITEM_LOCATIONS
2708 OPEN c_subinventory_loct;
2709 FETCH c_subinventory_loct INTO l_dummy;
2710 IF (c_subinventory_loct%NOTFOUND) THEN
2711 --CLOSE c_subinventory_loct;
2712 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
2713 'LOCATION', p_sample_rec.locator_id);
2714 --RAISE FND_API.G_EXC_ERROR;
2715 END IF;
2716 CLOSE c_subinventory_loct;
2717 END IF; -- location IS NOT NULL
2718 END IF; -- p_locator_control = 1
2719
2720
2721 -- Set return parameters
2722 x_return_status := l_return_status;
2723
2724 EXCEPTION
2725
2726 WHEN FND_API.G_EXC_ERROR THEN
2727 x_return_status := FND_API.G_RET_STS_ERROR ;
2728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2730 WHEN OTHERS THEN
2731 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2732
2733 END VALIDATE_LOCATION_SAMPLE;
2734
2735
2736
2737 PROCEDURE VALIDATE_SAMPLE
2738 ( p_sample_rec IN GMD_SAMPLES%ROWTYPE
2739 , p_grade IN VARCHAR2 --3431884
2740 , x_sample_rec OUT NOCOPY GMD_SAMPLES%ROWTYPE
2741 , x_return_status OUT NOCOPY VARCHAR2
2742 )
2743 IS
2744 -- Bug 4165704: organization validation changed with inventory convergence
2745 CURSOR c_orgn (p_organization_id NUMBER) IS
2746 -- SELECT 1
2747 -- FROM sy_orgn_mst
2748 -- WHERE orgn_code = p_orgn_code
2749 -- AND delete_mark = 0;
2750 SELECT 1
2751 FROM mtl_parameters m,
2752 gmd_quality_config g
2753 WHERE g.organization_id = m.organization_id
2754 AND m.organization_id = p_organization_id
2755 AND m. process_enabled_flag = 'Y' ;
2756
2757 CURSOR c_sampler (p_orgn_code VARCHAR2 , p_sampler_id NUMBER) IS
2758 SELECT 1
2759 FROM FND_USER
2760 WHERE user_id = p_sampler_id ;
2761 -- bug 4165704: taken out for invconv
2762 --AND user_id in
2763 -- ( select user_id
2764 -- from sy_orgn_usr
2765 -- where orgn_code = p_orgn_code);
2766
2767
2768 -- Bug 4165704: organization validation changed with inventory convergence
2769 CURSOR c_lab_orgn (p_organization_id NUMBER) IS
2770 -- SELECT 1
2771 -- FROM sy_orgn_mst
2772 -- WHERE orgn_code = p_orgn_code
2773 -- AND plant_ind <> 0
2774 -- AND delete_mark = 0;
2775 SELECT 1
2776 FROM mtl_parameters m,
2777 gmd_quality_config g
2778 WHERE g.organization_id = m.organization_id
2779 AND g.quality_lab_ind = 'Y'
2780 AND m.organization_id = p_organization_id
2781 AND m. process_enabled_flag = 'Y' ;
2782
2783 -- Bug 4165704: This cursor is not used
2784 --CURSOR c_item(p_inventory_item_id NUMBER, p_organization_id NUMBER) IS
2785 --SELECT 1
2786 --FROM ic_item_mst
2787 --WHERE item_id = p_item_id
2788 --AND delete_mark = 0;
2789
2790 CURSOR c_sampling_event(p_sampling_event_id NUMBER) IS
2791 SELECT 1
2792 FROM gmd_sampling_events
2793 WHERE sampling_event_id = p_sampling_event_id
2794 ;
2795
2796 -- Bug 4165704: changed for inventory convergence
2797 CURSOR c_subinventory IS
2798 SELECT 1
2799 FROM mtl_secondary_inventories s
2800 WHERE s.organization_id = p_sample_rec.organization_id
2801 AND s.secondary_inventory_name = p_sample_rec.subinventory;
2802 --CURSOR c_whse IS
2803 --SELECT loct_ctl
2804 --FROM ic_whse_mst
2805 --WHERE whse_code = p_sample_rec.whse_code
2806 --AND delete_mark = 0;
2807
2808 -- Bug 4165704: Updated for inventory convergence
2809 CURSOR c_subinventory_locator IS
2810 SELECT 1
2811 FROM mtl_item_locations
2812 WHERE subinventory_code = p_sample_rec.subinventory
2813 AND organization_id = p_sample_rec.organization_id
2814 AND inventory_location_id = p_sample_rec.locator_id;
2815
2816 -- Bug 4165704: no longer need c_orgn_whse or c_grade_ctl cursors after inventory convergence
2817 -- CURSOR c_orgn_whse IS
2818 -- SELECT 1
2819 -- FROM ic_whse_mst iwm
2820 -- , sy_orgn_mst som
2821 -- WHERE som.orgn_code = iwm.orgn_code
2822 -- AND iwm.whse_code = p_sample_rec.whse_code
2823 -- AND iwm.orgn_code = p_sample_rec.orgn_code
2824 -- AND som.delete_mark = 0
2825 -- AND iwm.delete_mark = 0;
2826
2827 --CURSOR c_grade_ctl IS
2828 --SELECT grade_ctl
2829 --FROM IC_ITEM_MST_B
2830 --WHERE ITEM_ID = p_sample_rec.inventory_item_id ;
2831
2832 -- bug 5291495
2833 CURSOR uom is
2834 SELECT PRIMARY_UOM_CODE
2835 FROM mtl_system_items_b
2836 WHERE organization_id = p_sample_rec.organization_id
2837 AND inventory_item_id = p_sample_rec.inventory_item_id;
2838 l_uom mtl_units_of_measure.uom_code%TYPE;
2839 l_test_qty number;
2840
2841 -- 5283854 rework
2842 CURSOR c_storage_subinventory_locator IS
2843 SELECT 1
2844 FROM mtl_item_locations
2845 WHERE subinventory_code = p_sample_rec.storage_subinventory
2846 AND organization_id = p_sample_rec.storage_organization_id
2847 AND inventory_location_id = p_sample_rec.storage_locator_id;
2848
2849 l_grade_ctl NUMBER;
2850 l_dummy NUMBER;
2851 l_sample_rec GMD_SAMPLES%ROWTYPE;
2852 l_sample_out_rec GMD_SAMPLES%ROWTYPE;
2853 l_locator_control NUMBER;
2854 l_return_status VARCHAR2(1);
2855
2856 BEGIN
2857
2858 -- Initialize API return status to success
2859 l_return_status := FND_API.G_RET_STS_SUCCESS;
2860 l_sample_rec := p_sample_rec;
2861
2862 ----------------------------------
2863 -- Validate Sample Required Fields
2864 ----------------------------------
2865
2866 -- Orgn Code
2867 IF (p_sample_rec.organization_id IS NULL) THEN
2868 GMD_API_PUB.Log_Message('GMD_ORGN_CODE_REQD');
2869 RAISE FND_API.G_EXC_ERROR;
2870 ELSE
2871 -- Check that orgn Code exist in SY_ORGN_MST
2872 OPEN c_orgn(p_sample_rec.organization_id);
2873 FETCH c_orgn INTO l_dummy;
2874 IF c_orgn%NOTFOUND THEN
2875 CLOSE c_orgn;
2876 GMD_API_PUB.Log_Message('GMD_ORGN_CODE_NOT_FOUND',
2877 'ORGN', p_sample_rec.organization_id);
2878 RAISE FND_API.G_EXC_ERROR;
2879 END IF;
2880 CLOSE c_orgn;
2881 END IF;
2882
2883 -- Sampler Id Validation
2884
2885 IF (p_sample_rec.sampler_id IS NULL ) THEN
2886 GMD_API_PUB.Log_Message('GMD_SAMPLER_ID_REQD');
2887 RAISE FND_API.G_EXC_ERROR;
2888 ELSE
2889 -- Check that orgn Code exist in mtl_parameters
2890 OPEN c_sampler(p_sample_rec.organization_id, p_sample_rec.sampler_id);
2891 FETCH c_sampler INTO l_dummy;
2892 IF c_sampler%NOTFOUND THEN
2893 GMD_API_PUB.Log_Message('GMD_SAMPLER_ID_NOTFOUND',
2894 'SAMPLER', p_sample_rec.sampler_id);
2895 RAISE FND_API.G_EXC_ERROR;
2896 CLOSE c_sampler;
2897 END IF;
2898 CLOSE c_sampler;
2899 END IF;
2900
2901 -- Sample No
2902 IF (ltrim(rtrim(p_sample_rec.sample_no)) IS NULL) THEN
2903 GMD_API_PUB.Log_Message('GMD_SAMPLE_NUMBER_REQD');
2904 RAISE FND_API.G_EXC_ERROR;
2905 END IF;
2906
2907
2908 -- QC Lab Orgn Code
2909 IF (p_sample_rec.lab_organization_id IS NULL) THEN
2910 GMD_API_PUB.Log_Message('GMD_QC_LAB_ORGN_CODE_REQD');
2911 RAISE FND_API.G_EXC_ERROR;
2912 ELSE
2913 -- Check that QC Lab Orgn Code exist in SY_ORGN_MST
2914 OPEN c_lab_orgn(p_sample_rec.lab_organization_id);
2915 FETCH c_lab_orgn INTO l_dummy;
2916 IF c_lab_orgn%NOTFOUND THEN
2917 CLOSE c_lab_orgn;
2918 GMD_API_PUB.Log_Message('GMD_QC_LAB_ORGN_CODE_NOT_FOUND',
2919 'ORGN', p_sample_rec.lab_organization_id);
2920 RAISE FND_API.G_EXC_ERROR;
2921 END IF;
2922 CLOSE c_lab_orgn;
2923 END IF;
2924
2925 -- Sample Disposition
2926 -- Bug 3401377: added planned samples to MPL
2927 IF ((p_sample_rec.sample_disposition IS NULL) OR
2928 (NOT (p_sample_rec.sample_disposition in ('0RT', '0PL', '1P')))) THEN
2929 -- Now, what is the disposition of this sample?
2930 GMD_API_PUB.Log_Message('GMD_SAMPLE_DISPOSITION_INVALID');
2931 RAISE FND_API.G_EXC_ERROR;
2932 END IF;
2933
2934 -- Sample Source
2935 -- Bug 3401377: added Stability Study and Monitor samples to MPK
2936 IF (p_sample_rec.source IS NULL OR
2937 (NOT (p_sample_rec.source in ('I', 'W', 'C', 'S', 'T','L','R')))
2938 ) THEN
2939 -- Now, what is the source of this sample? Where did it come from?
2940 GMD_API_PUB.Log_Message('GMD_SAMPLE_SOURCE_INVALID');
2941 RAISE FND_API.G_EXC_ERROR;
2942
2943 ELSE -- VAlidate that sample type is Correct
2944
2945 IF ( p_sample_rec.sample_type is NULL ) THEN
2946 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2947 'WHAT', 'SAMPLE TYPE must be Specified');*/
2948 GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE');
2949 RAISE FND_API.G_EXC_ERROR;
2950
2951 --ELSIF ( p_sample_rec.sample_type <> 'I') THEN
2952 ELSIF ( p_sample_rec.sample_type NOT IN ('M' , 'I')) THEN
2953
2954 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2955 'WHAT', 'Only sample_type = M or I are Suppported');*/
2956 GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE_SUPPORTED');
2957 RAISE FND_API.G_EXC_ERROR;
2958
2959 ELSIF ( p_sample_rec.sample_type = 'I')
2960 AND ( NOT (p_sample_rec.source in ('I', 'W', 'C', 'S', 'T'))) THEN
2961 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2962 'WHAT', 'SAMPLE TYPE Inventory does not match source');*/
2963 GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE_INVENTORY');
2964 RAISE FND_API.G_EXC_ERROR;
2965
2966 ELSIF ( p_sample_rec.sample_type = 'M')
2967 AND ( NOT (p_sample_rec.source in ('R', 'L'))) THEN
2968 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2969 'WHAT', 'SAMPLE TYPE Monitor does not match source');*/
2970 GMD_API_PUB.Log_Message('GMD_SAMPLE_TYPE_MONITOR');
2971 RAISE FND_API.G_EXC_ERROR;
2972 END IF;
2973
2974 END IF;
2975
2976 -- bug 5291495 UOM passed must be either item primary for that item or convertible for 'I' samples
2977
2978 IF p_sample_rec.sample_type <> 'M' then
2979 OPEN uom;
2980 FETCH uom INTO l_uom;
2981 IF (uom%NOTFOUND) THEN
2982 GMD_API_PUB.Log_Message('GMD_SAMPLE_UOM_REQD');
2983 RAISE FND_API.G_EXC_ERROR;
2984 END IF;
2985 CLOSE uom;
2986
2987 IF p_sample_rec.sample_qty_uom <> l_uom THEN
2988
2989 l_test_qty := INV_CONVERT.inv_um_convert(
2990 item_id => p_sample_rec.inventory_item_id,
2991 organization_id => p_sample_rec.organization_id,
2992 precision => NULL,
2993 from_quantity => p_sample_rec.SAMPLE_QTY,
2994 from_unit => p_sample_rec.sample_qty_uom,
2995 to_unit => l_uom,
2996 lot_number => 0,
2997 from_name => NULL,
2998 to_name => NULL);
2999 IF (l_test_qty < 0 ) THEN
3000 GMD_API_PUB.Log_Message('FM_UOMMUSTCONVERT');
3001 RAISE FND_API.G_EXC_ERROR;
3002 END IF;
3003
3004 END IF; -- IF p_sample_rec.sample_qty_uom <> l_uom THEN
3005
3006 END IF; -- IF p_sample_rec.sample_type <> 'M' then
3007
3008 -- end bug 5291495
3009
3010
3011
3012 -- Sample Qty
3013 -- Bug 3401377: Monitor samples do not have a qty or uom
3014 IF ((p_sample_rec.sample_qty IS NULL)
3015 AND (p_sample_rec.sample_type = 'I' )) THEN
3016 GMD_API_PUB.Log_Message('GMD_SAMPLE_QTY_REQD');
3017 RAISE FND_API.G_EXC_ERROR;
3018 END IF;
3019
3020 -- Sample UOM
3021 -- Bug 3401377: Monitor samples do not have a qty or uom
3022 IF ( (p_sample_rec.sample_qty_uom IS NULL)
3023 AND (p_sample_rec.sample_type = 'I' )) THEN
3024 GMD_API_PUB.Log_Message('GMD_SAMPLE_UOM_REQD');
3025 RAISE FND_API.G_EXC_ERROR;
3026 END IF;
3027
3028 -- Validate Whse_code if passed.
3029
3030 IF (p_sample_rec.subinventory IS NOT NULL) THEN
3031 -- Bug 4165704: changed for Inventory Convergence
3032 -- Check that Warehouse Code exist in MTL_SUBINVENTORIES
3033 -- AND Get the loct_ctl of the subinventory
3034 OPEN c_subinventory;
3035 FETCH c_subinventory INTO l_dummy;
3036 IF (c_subinventory%NOTFOUND) THEN
3037 CLOSE c_subinventory;
3038 GMD_API_PUB.Log_Message('GMD_WHSE_NOT_FOUND',
3039 'WHSE_CODE', p_sample_rec.subinventory);
3040 RAISE FND_API.G_EXC_ERROR;
3041 END IF;
3042 CLOSE c_subinventory;
3043
3044 -- Bug 4165704: taken out because it was not needed
3045 -- Check that Warehouse Code exist in IC_WHSE_MST linked to the organization
3046 --OPEN c_orgn_whse;
3047 --FETCH c_orgn_whse INTO l_dummy;
3048 --IF (c_orgn_whse%NOTFOUND) THEN
3049 -- CLOSE c_orgn_whse;
3050 -- GMD_API_PUB.Log_Message('GMD_WHSE_AND_ORGN_CODE');
3051 -- RAISE FND_API.G_EXC_ERROR;
3052 -- END IF;
3053 -- CLOSE c_orgn_whse;
3054 END IF;
3055
3056 -- Validate location
3057
3058 -- 5283854 if subinv is not specified and locator is then error out
3059 IF p_sample_rec.subinventory IS NULL and p_sample_rec.locator_id is NOT NULL THEN
3060 GMD_API_PUB.Log_Message('GMD_API_LOCATOR_NOT_REQD');
3061 RAISE FND_API.G_EXC_ERROR;
3062 END IF;
3063 -- end 5283854
3064
3065 -- Bug 4165704: used common routine to determine location control
3066
3067
3068 GMD_COMMON_GRP.item_is_locator_controlled (
3069 p_organization_id => p_sample_rec.organization_id
3070 ,p_subinventory => p_sample_rec.subinventory
3071 ,p_inventory_item_id => p_sample_rec.inventory_item_id
3072 ,x_locator_type => l_locator_control
3073 ,x_return_status => l_return_status );
3074
3075 IF ( l_locator_control = 1 ) THEN -- Not location controlled.
3076
3077 IF ( p_sample_rec.locator_id is NOT NULL) THEN
3078 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3079 'WHAT', 'Location should be NULL');*/
3080 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
3081 RAISE FND_API.G_EXC_ERROR;
3082 END IF;
3083
3084 ELSIF ( l_locator_control > 1) THEN -- Validate that location exists in whse
3085 -- Bug 4165704: updated for locator and subinventory
3086
3087 -- 5283854 if subinv is specified and locator is NOT then error out
3088 IF p_sample_rec.subinventory IS NOT NULL and p_sample_rec.locator_id is NULL THEN
3089 GMD_API_PUB.Log_Message('GMD_API_SUBINV_REQD');
3090 RAISE FND_API.G_EXC_ERROR;
3091 END IF;
3092 -- end 5283854
3093
3094 IF ( p_sample_rec.locator_id is NOT NULL) THEN -- location specified
3095
3096 OPEN c_subinventory_locator;
3097 FETCH c_subinventory_locator INTO l_dummy;
3098 IF (c_subinventory_locator%NOTFOUND) THEN
3099 --CLOSE c_subinventory_locator;
3100 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
3101 'LOCATOR', p_sample_rec.locator_id);
3102 RAISE FND_API.G_EXC_ERROR; -- 5283854 rework - take out -- from this line
3103 END IF;
3104 CLOSE c_subinventory_locator;
3105 END IF;
3106
3107 END IF;
3108
3109 -- 5283854 rework
3110 -- Validate storage details and location
3111
3112
3113 IF p_sample_rec.storage_organization_id is NULL and ( p_sample_rec.storage_subinventory IS NOT NULL or p_sample_rec.storage_locator_id is NOT NULL )
3114 then
3115 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
3116 RAISE FND_API.G_EXC_ERROR;
3117 END IF;
3118
3119
3120 IF p_sample_rec.storage_organization_id IS NOT NULL
3121 and ( p_sample_rec.storage_subinventory IS NOT NULL or p_sample_rec.storage_locator_id is NOT NULL )
3122 then
3123
3124
3125
3126 -- if subinv is not specified and locator is then error out
3127 IF p_sample_rec.storage_subinventory IS NULL and p_sample_rec.storage_locator_id is NOT NULL THEN
3128 GMD_API_PUB.Log_Message('GMD_API_LOCATOR_NOT_REQD');
3129 RAISE FND_API.G_EXC_ERROR;
3130 END IF;
3131
3132
3133 GMD_COMMON_GRP.item_is_locator_controlled (
3134 p_organization_id => p_sample_rec.storage_organization_id
3135 ,p_subinventory => p_sample_rec.storage_subinventory
3136 ,p_inventory_item_id => p_sample_rec.inventory_item_id
3137 ,x_locator_type => l_locator_control
3138 ,x_return_status => l_return_status );
3139
3140 IF ( l_locator_control = 1 ) THEN -- Not location controlled.
3141
3142 IF ( p_sample_rec.storage_locator_id is NOT NULL) THEN
3143 /*GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3144 'WHAT', 'Location should be NULL');*/
3145 GMD_API_PUB.Log_Message('GMD_LOCATION_MUST_NULL');
3146 RAISE FND_API.G_EXC_ERROR;
3147 END IF;
3148
3149 ELSIF ( l_locator_control > 1) THEN -- Validate that locater exists in subinv
3150
3151
3152 -- if subinv is specified and locator is NOT then error out
3153 IF p_sample_rec.storage_subinventory IS NOT NULL and p_sample_rec.storage_locator_id is NULL THEN
3154 GMD_API_PUB.Log_Message('GMD_API_SUBINV_REQD');
3155 RAISE FND_API.G_EXC_ERROR;
3156 END IF;
3157
3158 IF ( p_sample_rec.storage_locator_id is NOT NULL) THEN -- location specified
3159
3160 OPEN c_storage_subinventory_locator;
3161 FETCH c_storage_subinventory_locator INTO l_dummy;
3162 IF (c_storage_subinventory_locator%NOTFOUND) THEN
3163 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND',
3164 'LOCATOR', p_sample_rec.storage_locator_id);
3165 RAISE FND_API.G_EXC_ERROR;
3166 END IF;
3167 CLOSE c_storage_subinventory_locator;
3168 END IF;
3169
3170 END IF; -- IF ( l_locator_control = 1 ) THEN -- Not location controlled.
3171
3172 END IF; -- IF p_sample_rec.storage_organization_id IS NOT NULL
3173
3174
3175 -- end 5283854 rework
3176
3177
3178
3179 ----------------------------------
3180 -- Validate Item Definition
3181 ----------------------------------
3182 -- Bug 3401377: Monitor samples do not have items
3183 IF (p_sample_rec.sample_type = 'I') THEN
3184 Validate_item_controls(
3185 p_sample_rec => p_sample_rec,
3186 p_grade => p_grade,
3187 x_sample_rec => l_sample_rec,
3188 x_return_status => l_return_status);
3189
3190 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3191 -- Message is alrady logged by check_for_null procedure
3192 RAISE FND_API.G_EXC_ERROR;
3193 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3194 -- Message is alrady logged by check_for_null procedure
3195 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3196 END IF;
3197 END IF; -- bug 3401377
3198
3199 --3431884
3200 ----------------------------------
3201 -- Validate Grade
3202 ----------------------------------
3203 -- Bug 4165704: grade control is now done in validate_item_controls procedure
3204 -- OPEN c_grade_ctl;
3205 -- FETCH c_grade_ctl INTO l_grade_ctl;
3206
3207 ----------------------------------
3208 -- Validate Sample Source Types
3209 ----------------------------------
3210 IF (p_sample_rec.source = 'I') THEN
3211
3212 Validate_inv_sample (
3213 p_sample_rec => l_sample_rec,
3214 p_locator_control => l_locator_control,
3215 x_return_status => l_return_status);
3216
3217 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3218 -- Message is alrady logged by check_for_null procedure
3219 RAISE FND_API.G_EXC_ERROR;
3220 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3221 -- Message is alrady logged by check_for_null procedure
3222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3223 END IF;
3224
3225
3226 ELSIF ( p_sample_rec.source = 'W') THEN
3227
3228 Validate_wip_sample (
3229 p_sample_rec => l_sample_rec,
3230 x_sample_rec => l_sample_out_rec,
3231 x_return_status => l_return_status);
3232
3233 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3234 -- Message is alrady logged by check_for_null procedure
3235 RAISE FND_API.G_EXC_ERROR;
3236 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3237 -- Message is alrady logged by check_for_null procedure
3238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3239 END IF;
3240
3241 l_sample_rec := l_sample_out_rec;
3242
3243 ELSIF ( p_sample_rec.source = 'C') THEN
3244
3245 Validate_cust_sample (
3246 p_sample_rec => l_sample_rec,
3247 x_return_status => l_return_status);
3248
3249 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3250 -- Message is alrady logged by check_for_null procedure
3251 RAISE FND_API.G_EXC_ERROR;
3252 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3253 -- Message is alrady logged by check_for_null procedure
3254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3255 END IF;
3256
3257 ELSIF ( p_sample_rec.source = 'S') THEN
3258 -- validate_supp_sample;
3259
3260 --dbms_output.put_line('Call to validate supp samples');
3261
3262 Validate_supp_sample (
3263 p_sample_rec => l_sample_rec,
3264 x_return_status => l_return_status);
3265
3266 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3267 -- Message is alrady logged by check_for_null procedure
3268 RAISE FND_API.G_EXC_ERROR;
3269 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3270 -- Message is alrady logged by check_for_null procedure
3271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3272 END IF;
3273
3274 --dbms_output.put_line('after Call to validate supp samples');
3275
3276 -- Bug 3401377: added Resource, Location and Stability Study samples to MPK
3277 ELSIF ( p_sample_rec.source = 'T') THEN
3278 -- validate_stability_study_sample;
3279
3280 Validate_stability_sample (
3281 p_sample_rec => l_sample_rec,
3282 x_return_status => l_return_status);
3283
3284 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3285 RAISE FND_API.G_EXC_ERROR;
3286 END IF;
3287
3288 ELSIF ( p_sample_rec.source = 'R') THEN
3289 -- validate_resource_sample;
3290
3291 Validate_resource_sample (
3292 p_sample_rec => l_sample_rec,
3293 x_return_status => l_return_status);
3294
3295 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3296 -- Message is alrady logged by check_for_null procedure
3297 RAISE FND_API.G_EXC_ERROR;
3298 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3299 -- Message is alrady logged by check_for_null procedure
3300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3301 END IF;
3302
3303 ELSIF ( p_sample_rec.source = 'L') THEN
3304 -- validate_location_sample;
3305
3306 Validate_location_sample (
3307 p_sample_rec => l_sample_rec,
3308 p_locator_control => l_locator_control,
3309 x_return_status => l_return_status);
3310
3311 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3312 -- Message is alrady logged by check_for_null procedure
3313 RAISE FND_API.G_EXC_ERROR;
3314 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3315 -- Message is alrady logged by check_for_null procedure
3316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3317 END IF;
3318
3319 END IF; -- Validation for Supplier Sample
3320
3321 --Validate Priority Field.
3322 IF l_sample_rec.priority IS NULL THEN
3323 l_sample_rec.priority := '5N';
3324
3325 ELSIF (NOT GMD_QC_TESTS_GRP.validate_test_priority
3326 (p_test_priority => l_sample_rec.priority)
3327 ) THEN
3328 GMD_API_PUB.Log_Message('GMD_INVALID_TEST_PRIORITY');
3329 RAISE FND_API.G_EXC_ERROR;
3330 END IF;
3331
3332 --dbms_output.put_line('after validate_test_priority');
3333
3334 -- bug 2995114
3335
3336 IF NVL(l_sample_rec.sample_inv_trans_ind ,'N') NOT IN ('N','Y') THEN
3337 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3338 'WHAT', 'SAMPLE_INV_TRANS_IND');
3339
3340 RAISE FND_API.G_EXC_ERROR;
3341 END IF;
3342
3343
3344
3345 -- Now Call Group Layer Validate Samples API
3346 -- To perform business logic validation.
3347
3348 --dbms_output.put_line('b4 GMD_SAMPLES_GRP.validate_sample');
3349
3350
3351 GMD_SAMPLES_GRP.validate_sample(
3352 p_sample => l_sample_rec,
3353 p_called_from => 'PUBLIC',
3354 p_operation => 'INSERT',
3355 x_return_status => l_return_status
3356 );
3357
3358 --dbms_output.put_line('after GMD_SAMPLES_GRP.validate_sample');
3359
3360
3361 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3362 -- Message is alrady logged by check_for_null procedure
3363 RAISE FND_API.G_EXC_ERROR;
3364 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3365 -- Message is alrady logged by check_for_null procedure
3366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3367 END IF;
3368
3369
3370
3371 -- Set return parameters
3372 x_return_status := l_return_status;
3373 x_sample_rec := l_sample_rec;
3374
3375
3376
3377 EXCEPTION
3378 WHEN FND_API.G_EXC_ERROR THEN
3379 x_return_status := FND_API.G_RET_STS_ERROR ;
3380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3382 WHEN OTHERS THEN
3383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3384
3385 END VALIDATE_SAMPLE;
3386
3387 END GMD_SAMPLES_PUB;
[Home] [Help]