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