1 PACKAGE BODY GMD_SPEC_VRS_GRP AS
2 /* $Header: GMDGSVRB.pls 120.11.12010000.2 2008/11/13 19:34:43 asatpute ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDGSVRB.pls |
11 --| Package Name : GMD_SPEC_VRS_GRP |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Specification Validity |
16 --| Rules. |
17 --| |
18 --| HISTORY |
19 --| Chetan Nagar 26-Jul-2002 Created. |
20 --| Olivier Daboval 17-OCT-2002 bug 2630007, added spec_name in |
21 --| the VR_EXIST message |
22 --| Olivier Daboval 14-NOV-2002 Added NVL(spec_vr_id, -1) because |
23 --| the API passes a NULL spec_vr_id |
24 --| in insert mode. |
25 --| Olivier Daboval 02-DEC-2002 Added x_wip_vr and x_inv_vr in the |
26 --| validation procedures |
27 --| Olivier Daboval 01-APR-2003 Now, populate the lower levels |
28 --| Bug 2733426 Formula/Routing when recipe is given |
29 --| |
30 --| Brenda Stone 20-NOV-2003 Bug 3090290; allow duplicate spec vr |
31 --| with "OBSOLUTE" status |
32 --| Bug 2984784; add Version to msg for |
33 --| existing spec vr. |
34 --| Jeff Baird 30-Apr-2004 Bug #3500024 Front port os 3381762 |
35 --| |
36 --| SaiKiran 04-MAY-2004 Enhancement #3476560. added |
37 --| 'delayed_lot_entry' to the call to |
38 --| 'check_vr_controls' procedure at all |
39 --| places |
40 --| |
41 --| Saikiran 04-MAY-2004 Enhancement# 3476560 |
42 --| Added 'delayed_lot_entry' to the |
43 --| 'c_details_null' cursor and |
44 --| 'c_details_NOT_null' cursor. Added |
45 --| 'x_delayed_lot_entry' to the procedure|
46 --| signature |
47 --| |
48 --| Saikiran 04-MAY-2004 Enhancement# 3476560. Added |
49 --| 'Delayed Lot Entry' field to the signature.|
50 --| Added validation for 'Delayed Lot Entry' that|
51 --| it should be 'Y' or Null. Removed special validation|
52 --| for 'Lot Optional on sample' in case of WIP Validity rule.|
53 --| |
54 --| Saikiran 28-MAY-2004 Bug# 3652938 |
55 --| Added validation for the invalid |
56 --| combination of 'Lot Optional on Sample' and |
57 --| 'Delayed Lot Entry' in 'Check_VR_controls' |
58 --| procedure |
59 --| |
60 --| Saikiran 28-Apr-2005 Made Convergence changes |
61 --| RLNAGARA 27-Dec-2005 Bug # 4900420 |
62 --| Modified the procedure check_VR_controls |
63 --| PLOWE 22-MAR-2006 Bug # 4619570 |
64 --| Changed the c_batch cursor to include closed |
65 --| batches as per the profile option. |
66 --| PLOWE 04-Apr-2006 Bug 5117733 - added item revision to|
67 --| match in functions inv_vr_exist, |
68 --| wip_vr_exist,cust_vr_exist, |
69 --| and supp_vr_exist |
70 --| PLOWE 25-MAY-2006 -- bug 5223014 sql id 17532992 |
71 --| and 17532478 |
72 --| PLOWE 07-JUN-2006 -- bug 5223014 rework |
73 --| replace cursor with function as check was not working as designed |
74 --| bug 5223014 rework in proc check_for_null_and_fks_in_cvr |
75 --| srakrish 15-June-2006 Bug 5276602: Checking if the Lot |
76 --| optional feild is set when |
77 --| lot or parent lot or entered. |
78 --| This scenario exists when the |
79 --| api is called from the wrapper. |
80 --| srakrish 15-june-06 BUG 5251172: Checking if the |
81 --| responsibility is available to the |
82 --| organization in all of the |
83 --| check_for_null_and_fks_in_ functions.|
84 --| |
85 --|==========================================================================+
86 -- End of comments
87
88 -- Global variables
89 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_SPEC_VRS_GRP';
90
91 -- Global Cursors
92
93 CURSOR c_orgn ( p_organization_id NUMBER) IS
94 SELECT 1
95 FROM mtl_parameters m
96 WHERE m.process_enabled_flag = 'Y';
97
98
99 CURSOR c_status (p_status_code NUMBER) IS
100 SELECT 1
101 FROM gmd_qc_status
102 WHERE status_code = p_status_code
103 AND delete_mark = 0;
104
105
106
107
108 --Start of comments
109 --+========================================================================+
110 --| API Name : validate_mon_vr |
111 --| TYPE : Group |
112 --| Notes : This procedure validates all the fields of |
113 --| monitoring validity rule record. This procedure can be |
114 --| called from FORM or API and the caller need |
115 --| to specify this in p_called_from parameter |
116 --| while calling this procedure. Based on where |
117 --| it is called from certain validations will |
118 --| either be performed or skipped. |
119 --| |
120 --| If everything is fine then OUT parameter |
121 --| x_return_status is set to 'S' else appropriate |
122 --| error message is put on the stack and error |
123 --| is returned. |
124 --| |
125 --| HISTORY |
126 --| Olivier Daboval 11-MAR-2003 Created |
127 --| |
128 --| |
129 --+========================================================================+
130 -- End of comments
131
132 PROCEDURE validate_mon_vr
133 (
134 p_mon_vr IN GMD_MONITORING_SPEC_VRS%ROWTYPE
135 , p_called_from IN VARCHAR2
136 , p_operation IN VARCHAR2
137 , x_mon_vr OUT NOCOPY GMD_MONITORING_SPEC_VRS%ROWTYPE
138 , x_return_status OUT NOCOPY VARCHAR2
139 ) IS
140
141 -- Local Variables
142 dummy NUMBER;
143 l_return_status VARCHAR2(1);
144
145 l_spec GMD_SPECIFICATIONS%ROWTYPE;
146 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
147 l_mon_vr GMD_MONITORING_SPEC_VRS%ROWTYPE;
148 l_mon_vr_tmp GMD_MONITORING_SPEC_VRS%ROWTYPE;
149 l_item_mst IC_ITEM_MST%ROWTYPE;
150 l_item_mst_out IC_ITEM_MST%ROWTYPE;
151 l_sampling_plan GMD_SAMPLING_PLANS%ROWTYPE;
152 l_sampling_plan_out GMD_SAMPLING_PLANS%ROWTYPE;
153
154 -- Exceptions
155 e_spec_fetch_error EXCEPTION;
156 e_smpl_plan_fetch_error EXCEPTION;
157 e_error_fetch_item EXCEPTION;
158
159 BEGIN
160 -- Initialize API return status to success
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
164 -- Invalid Operation
165 GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
166 RAISE FND_API.G_EXC_ERROR;
167 END IF;
168
169 -- Verify that the specification exists.
170 l_spec.spec_id := p_mon_vr.spec_id;
171 IF NOT (GMD_Specifications_PVT.Fetch_Row(
172 p_specifications => l_spec,
173 x_specifications => l_spec_out)
174 ) THEN
175 -- Fetch Error
176 GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
177 RAISE e_spec_fetch_error;
178 END IF;
179
180 l_spec := l_spec_out ;
181
182 -- Verify that the Sampling Plan exists.
183 --odab added this test.
184 IF (p_mon_vr.sampling_plan_id IS NOT NULL)
185 THEN
186 l_sampling_plan.sampling_plan_id := p_mon_vr.sampling_plan_id;
187 IF NOT (GMD_Sampling_Plans_PVT.Fetch_Row(
188 p_sampling_plan => l_sampling_plan,
189 x_sampling_plan => l_sampling_plan_out)
190 ) THEN
191 -- Fetch Error
192 GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
193 RAISE e_smpl_plan_fetch_error;
194 END IF;
195 l_sampling_plan:= l_sampling_plan_out ;
196 END IF;
197
198 -- odaboval From this point, the l_mon_vr is used
199 -- and will populate the return parameter x_mon_vr
200 l_mon_vr := p_mon_vr;
201 IF (p_called_from = 'API') THEN
202 -- Check for NULLs and Valid Foreign Keys in the input parameter
203 check_for_null_and_fks_in_mvr
204 (
205 p_mon_vr => p_mon_vr
206 , p_spec => l_spec
207 , x_mon_vr => l_mon_vr_tmp
208 , x_return_status => l_return_status
209 );
210 -- No need if called from FORM since it is already
211 -- done in the form
212 -- All messages should be already raised
213 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
214 -- Message is alrady logged by check_for_null procedure
215 RAISE FND_API.G_EXC_ERROR;
216 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
217 -- Message is alrady logged by check_for_null procedure
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 END IF;
220
221 l_mon_vr := l_mon_vr_tmp;
222 END IF;
223
224 -- First Verify that the SAME VR does not exists
225 --IF (p_operation IN ('INSERT')
226 IF (p_operation IN ('INSERT', 'UPDATE')
227 AND mon_vr_exist(l_mon_vr, l_spec))
228 THEN
229 -- Disaster, Trying to insert duplicate
230 -- Put the message in function mon_vr_exist.
231 -- GMD_API_PUB.Log_Message('GMD_MON_VR_EXIST');
232 RAISE FND_API.G_EXC_ERROR;
233 END IF;
234
235 -- No need to check the return status because above procedure
236 -- logs appropriate message on the stack and raises an exception.
237
238 -- The Start Date must be less than the End Date
239 If ( l_mon_vr.end_date IS NOT NULL AND
240 l_mon_vr.start_date > l_mon_vr.end_date) THEN
241 GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
242 RAISE FND_API.G_EXC_ERROR;
243 END IF;
244
245 -- Spec VR Status Must be less than Spec Status upto Appoved Stages
246 IF (floor(l_spec.spec_status/100) <= 7 AND
247 floor(l_mon_vr.spec_vr_status/100) <= 7 AND
248 l_mon_vr.spec_vr_status > l_spec.spec_status) THEN
249 GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
250 RAISE FND_API.G_EXC_ERROR;
251 END IF;
252
253 -- All systems GO...
254 x_mon_vr := l_mon_vr;
255
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR OR
258 e_spec_fetch_error OR
259 e_smpl_plan_fetch_error OR
260 e_error_fetch_item
261 THEN
262 x_return_status := FND_API.G_RET_STS_ERROR ;
263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
265 WHEN OTHERS THEN
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
267
268
269 END validate_mon_vr;
270
271
272 --Start of comments
273 --+========================================================================+
274 --| API Name : check_for_null_and_fks_in_mvr |
275 --| TYPE : Group |
276 --| Notes : This procedure checks for NULL and Foreign Key |
277 --| constraints for the required filed in the Spec |
278 --| Monitoring VR record. |
279 --| |
280 --| If everything is fine then 'S' is returned in the |
281 --| parameter - x_return_status otherwise error message |
282 --| is logged and error status - E or U returned |
283 --| |
284 --| HISTORY |
285 --| Olivier Daboval 11-MAR-2003 Created |
286 --| Saikiran Vankadari 24-Apr-2005 Convergence Changes |
287 --| srakrish 15-june-06 BUG 5251172: Checking if the |
288 --| responsibility is available to the |
289 --| organization. |
290 --+========================================================================+
291 -- End of comments
292
293 PROCEDURE check_for_null_and_fks_in_mvr
294 (
295 p_mon_vr IN GMD_MONITORING_SPEC_VRS%ROWTYPE
296 , p_spec IN GMD_SPECIFICATIONS%ROWTYPE
297 , x_mon_vr OUT NOCOPY GMD_MONITORING_SPEC_VRS%ROWTYPE
298 , x_return_status OUT NOCOPY VARCHAR2
299 )
300 IS
301
302 l_mon_vr GMD_MONITORING_SPEC_VRS%ROWTYPE;
303
304 CURSOR c_subinventory IS
305 SELECT 1
306 FROM mtl_secondary_inventories
307 WHERE secondary_inventory_name = l_mon_vr.subinventory
308 AND organization_id = l_mon_vr.locator_organization_id;
309
310 CURSOR c_locator IS
311 SELECT 1
312 FROM mtl_item_locations
313 WHERE organization_id = l_mon_vr.locator_organization_id
314 AND inventory_location_id = l_mon_vr.locator_id;
315
316 cursor c_resources is
317 select 1
318 from cr_rsrc_mst
319 where resources = l_mon_vr.resources
320 and delete_mark = 0;
321
322 cursor c_resource_instance is
323 SELECT ri.INSTANCE_NUMBER
324 FROM GMP_RESOURCE_INSTANCES ri, CR_RSRC_DTL rd
325 WHERE rd.resource_id = ri.resource_id
326 AND rd.organization_id = NVL(l_mon_vr.resource_organization_id, rd.organization_id)
327 AND rd.resources = NVL(l_mon_vr.resources, rd.resources)
328 AND ri.INACTIVE_IND = 0
329 ORDER BY 1 ;
330
331 dummy NUMBER;
332 l_locator_type NUMBER;
333 l_return_status VARCHAR2(1);
334
335 BEGIN
336
337 l_mon_vr := p_mon_vr;
338
339 check_who( p_user_id => l_mon_vr.created_by);
340 check_who( p_user_id => l_mon_vr.last_updated_by);
341 IF (l_mon_vr.creation_date IS NULL
342 OR l_mon_vr.last_update_date IS NULL)
343 THEN
344 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
345 'WHAT', 'the dates must not be NULL');
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349
350 -- Bug 3451798
351 -- In case rule type is location, all resource-related info should be nulled
352 -- In case rule type is resource, all location-related info should be nulled
353 if (l_mon_vr.rule_type = 'R') then
354 l_mon_vr.locator_id := NULL;
355 l_mon_vr.locator_organization_id := NULL;
356 l_mon_vr.subinventory := NULL;
357 elsif (l_mon_vr.rule_type = 'L') then
358 l_mon_vr.resources := NULL;
359 l_mon_vr.resource_organization_id := NULL;
360 l_mon_vr.resource_instance_id := NULL;
361 else
362 -- Bug 3451839
363 -- Invalid Rule Type
364 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
365 'WHAT', 'The monitoring spec rule type');
366 RAISE FND_API.G_EXC_ERROR;
367 end if ;
368
369
370 -- Loct Organization is valid
371 IF (l_mon_vr.locator_organization_id IS NOT NULL) THEN
372 OPEN c_orgn( l_mon_vr.locator_organization_id);
373 FETCH c_orgn INTO dummy;
374 IF (c_orgn%NOTFOUND)
375 THEN
376 CLOSE c_orgn;
377 GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
378 'ORGN_ID', l_mon_vr.locator_organization_id);
379 RAISE FND_API.G_EXC_ERROR;
380 END IF;
381 CLOSE c_orgn;
382 END IF;
383
384 --srakrish BUG 5251172: Checking if the responsibility is available to the Locator organization.
385 IF NOT (gmd_api_grp.OrgnAccessible(p_mon_vr.locator_organization_id)) THEN
386 RAISE FND_API.G_EXC_ERROR;
387 END IF;
388
389 -- Resource is valid (Bug 3451868)
390 IF (l_mon_vr.resources IS NOT NULL) THEN
391 -- Check that Resource exists
392 OPEN c_resources ;
393 FETCH c_resources INTO dummy;
394 IF (c_resources%NOTFOUND)
395 THEN
396 CLOSE c_resources;
397 GMD_API_PUB.Log_Message('GMD_RESOURCE_NOT_FOUND',
398 'RESOURCE', l_mon_vr.resources);
399 RAISE FND_API.G_EXC_ERROR;
400 END IF;
401 CLOSE c_resources;
402 END IF;
403
404
405 -- Resource Organization is valid
406 IF (l_mon_vr.resource_organization_id IS NOT NULL) THEN
407 OPEN c_orgn( l_mon_vr.resource_organization_id);
408 FETCH c_orgn INTO dummy;
409 IF (c_orgn%NOTFOUND)
410 THEN
411 CLOSE c_orgn;
412 GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
413 'ORGN_ID', l_mon_vr.resource_organization_id);
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416 CLOSE c_orgn;
417 END IF;
418
419 --srakrish BUG 5251172: Checking if the responsibility is available to the Resource organization.
420 IF NOT (gmd_api_grp.OrgnAccessible(p_mon_vr.resource_organization_id)) THEN
421 RAISE FND_API.G_EXC_ERROR;
422 END IF;
423
424 -- Resource Instance is valid (Bug 3451868)
425 IF (l_mon_vr.resource_instance_id IS NOT NULL) THEN
426 -- Check that Resource instance idexists
427 OPEN c_resource_instance ;
428 FETCH c_resource_instance INTO dummy;
429 IF (c_resource_instance%NOTFOUND)
430 THEN
431 CLOSE c_resource_instance;
432 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
433 'WHAT', 'The resource instance');
434 RAISE FND_API.G_EXC_ERROR;
435 END IF;
436 CLOSE c_resource_instance;
437 END IF;
438
439
440 -- Subinventory is Valid
441 IF (l_mon_vr.subinventory IS NOT NULL) THEN
442 -- Check that Subinventory exist and is associated with locator organization.
443 OPEN c_subinventory;
444 FETCH c_subinventory INTO dummy;
445 IF (c_subinventory%NOTFOUND)
446 THEN
447 CLOSE c_subinventory;
448 GMD_API_PUB.Log_Message('GMD_SUBINVENTORY_NOT_FOUND',
449 'SUBINVENTORY', l_mon_vr.subinventory);
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452 CLOSE c_subinventory;
453
454 END IF;
455
456 --Find out if it is locator controlled
457 GMD_COMMON_GRP.item_is_locator_controlled (
458 p_organization_id => l_mon_vr.locator_organization_id
459 ,p_subinventory => l_mon_vr.subinventory
460 ,p_inventory_item_id => NULL
461 ,x_locator_type => l_locator_type
462 ,x_return_status => l_return_status);
463
464 -- Location is valid
465 IF (l_locator_type IN (2,3))
466 THEN
467 -- Here l_locator_type IN (2,3)
468 IF (l_mon_vr.locator_id IS NULL)
469 THEN
470 -- Location can be NULL in this case.
471 null;
472 ELSE
473 -- Check that Location exist in MTL_ITEM_LOCATIONS
474 OPEN c_locator;
475 FETCH c_locator INTO dummy;
476 IF (c_locator%NOTFOUND)
477 THEN
478 CLOSE c_locator;
479 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND');
480 RAISE FND_API.G_EXC_ERROR;
481 END IF;
482 CLOSE c_locator;
483 END IF; -- location IS NOT NULL
484 ELSE --l_locator_type NOT IN (2,3)
485 IF (l_mon_vr.locator_id IS NOT NULL)
486 THEN
487 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
488 'WHAT', 'locator should be NULL');
489 RAISE FND_API.G_EXC_ERROR;
490 END IF;
491 END IF; -- l_locator_type IN (2,3)
492
493 --=========================================================================
494 -- spec_vr_status :
495 --=========================================================================
496 -- Check that Spec VR Status exist in GMD_QC_STATUS
497 OPEN c_status(l_mon_vr.spec_vr_status);
498 FETCH c_status
499 INTO dummy;
500 IF (c_status%NOTFOUND)
501 THEN
502 CLOSE c_status;
503 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
504 'STATUS', l_mon_vr.spec_vr_status);
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507 CLOSE c_status;
508
509 --=========================================================================
510 -- start_date : This field is mandatory
511 --=========================================================================
512 IF (l_mon_vr.start_date IS NULL)
513 THEN
514 GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
515 RAISE FND_API.G_EXC_ERROR;
516 END IF;
517
518 x_mon_vr := l_mon_vr;
519
520
521 EXCEPTION
522 WHEN FND_API.G_EXC_ERROR THEN
523 x_return_status := FND_API.G_RET_STS_ERROR ;
524 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
526 WHEN OTHERS THEN
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528
529 END check_for_null_and_fks_in_mvr;
530
531
532
533
534
535 --Start of comments
536 --+========================================================================+
537 --| API Name : mon_vr_exist |
538 --| TYPE : Group |
539 --| Notes : This function returns TRUE if the monitoring VR already |
540 --| exists for the spcified parameter in the database, FALSE |
541 --| otherwise. |
542 --| |
543 --| HISTORY |
544 --| Chetan Nagar 26-Jul-2002 Created. |
545 --| Olivier Daboval 17-OCT-2002 bug 2630007, added spec_name in |
546 --| the VR_EXIST message |
547 --| Olivier Daboval 14-NOV-2002 Added NVL(spec_vr_id, -1) because |
548 --| the API passes a NULL spec_vr_id |
549 --| in insert mode. |
550 --| Brenda Stone 20-NOV-2003 Bug 3090290; allow duplicate spec vr |
551 --| with "OBSOLUTE" status |
552 --| Bug 2984784; add Version to msg for |
553 --| existing spec vr. |
554 --| |
555 --| Saikiran 12-Apr-2005 Convergence Changes |
556 --+========================================================================+
557 -- End of comments
558
559 FUNCTION mon_vr_exist(p_mon_vr GMD_MONITORING_SPEC_VRS%ROWTYPE,
560 p_spec GMD_SPECIFICATIONS%ROWTYPE)
561 RETURN BOOLEAN IS
562
563 CURSOR c_mon_vr IS
564 SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
565 FROM gmd_specifications_b s, gmd_monitoring_spec_vrs vr
566 WHERE s.spec_id = vr.spec_id
567 AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
568 (s.grade_code = p_spec.grade_code)
569 )
570 AND ((vr.locator_organization_id is NULL AND p_mon_vr.locator_organization_id is NULL) OR
571 (vr.locator_organization_id = p_mon_vr.locator_organization_id)
572 )
573 AND ((vr.subinventory is NULL AND p_mon_vr.subinventory is NULL) OR
574 (vr.subinventory = p_mon_vr.subinventory)
575 )
576 AND ((vr.locator_id is NULL AND p_mon_vr.locator_id is NULL) OR
577 (vr.locator_id = p_mon_vr.locator_id)
578 )
579 AND ((vr.resource_organization_id is NULL AND p_mon_vr.resource_organization_id is NULL) OR
580 (vr.resource_organization_id = p_mon_vr.resource_organization_id)
581 )
582 AND ((vr.resources is NULL AND p_mon_vr.resources is NULL) OR
583 (vr.resources = p_mon_vr.resources)
584 )
585 AND ((vr.resource_instance_id is NULL AND p_mon_vr.resource_instance_id is NULL) OR
586 (vr.resource_instance_id = p_mon_vr.resource_instance_id)
587 )
588 AND ((vr.end_date is NULL AND (p_mon_vr.end_date IS NULL OR
589 p_mon_vr.end_date >= vr.start_date)) OR
590 (p_mon_vr.end_date IS NULL AND
591 p_mon_vr.start_date <= nvl(vr.end_date, p_mon_vr.start_date)) OR
592 (p_mon_vr.start_date <= vr.end_date AND p_mon_vr.end_date >= vr.start_date)
593 )
594 AND ( floor(vr.spec_vr_status / 100) = floor(p_mon_vr.spec_vr_status/100) AND
595 /* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
596 p_mon_vr.spec_vr_status <> 1000 )
597 AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
598 WHERE status_type = 800)
599 AND vr.delete_mark = 0
600 AND s.delete_mark = 0
601 AND vr.spec_vr_id <> NVL(p_mon_vr.spec_vr_id, -1)
602 ;
603
604 dummy PLS_INTEGER;
605 specname VARCHAR2(80);
606 specvers NUMBER;
607
608 BEGIN
609
610 OPEN c_mon_vr;
611 FETCH c_mon_vr INTO dummy, specname, specvers;
612 IF c_mon_vr%FOUND THEN
613 CLOSE c_mon_vr;
614 FND_MESSAGE.SET_NAME('GMD', 'GMD_MON_VR_EXIST');
615 FND_MESSAGE.SET_TOKEN('spec', specname);
616 FND_MESSAGE.SET_TOKEN('vers', specvers);
617 FND_MSG_PUB.ADD;
618 RETURN TRUE;
619 ELSE
620 CLOSE c_mon_vr;
621 RETURN FALSE;
622 END IF;
623
624 EXCEPTION
625 -- Though there is no reason the program can reach
626 -- here, this is coded just for the reasons we can
627 -- not think of!
628 WHEN OTHERS THEN
629 FND_MESSAGE.SET_NAME('GMD', 'GMD_API_ERROR');
630 FND_MESSAGE.SET_TOKEN('PACKAGE', 'GMD_SPEC_VRS_GRP.MON_VR_EXIST' );
631 FND_MESSAGE.SET_TOKEN('ERROR', SUBSTR(SQLERRM,1,200));
632 FND_MSG_PUB.ADD;
633
634 RETURN TRUE;
635
636 END mon_vr_exist;
637
638
639 --Start of comments
640 --+========================================================================+
641 --| API Name : validate_inv_vr |
642 --| TYPE : Group |
643 --| Notes : This procedure validates all the fields of |
644 --| inventory validity rule record. This procedure can be |
645 --| called from FORM or API and the caller need |
646 --| to specify this in p_called_from parameter |
647 --| while calling this procedure. Based on where |
648 --| it is called from certain validations will |
649 --| either be performed or skipped. |
650 --| |
651 --| If everything is fine then OUT parameter |
652 --| x_return_status is set to 'S' else appropriate |
653 --| error message is put on the stack and error |
654 --| is returned. |
655 --| |
656 --| HISTORY |
657 --| Chetan Nagar 26-Jul-2002 Created. |
658 --| Olivier Daboval 02-DEC-2002 Added x_inv_vr as out parameter |
659 --| srakrish 15-June-2006 Bug 5276602: Checking if the Lot |
660 --| optionalfeild is set to yes when |
661 --| lot or parent lot or entered. |
662 --| This scenario exists when the |
663 --| api is called from the wrapper. |
664 --| |
665 --| |
666 --+========================================================================+
667 -- End of comments
668
669 PROCEDURE validate_inv_vr
670 (
671 p_inv_vr IN GMD_INVENTORY_SPEC_VRS%ROWTYPE
672 , p_called_from IN VARCHAR2
673 , p_operation IN VARCHAR2
674 , x_inv_vr OUT NOCOPY GMD_INVENTORY_SPEC_VRS%ROWTYPE
675 , x_return_status OUT NOCOPY VARCHAR2
676 ) IS
677
678 -- Local Variables
679 dummy NUMBER;
680 l_return_status VARCHAR2(1);
681
682 l_spec GMD_SPECIFICATIONS%ROWTYPE;
683 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
684 l_inv_vr GMD_INVENTORY_SPEC_VRS%ROWTYPE;
685 l_inv_vr_tmp GMD_INVENTORY_SPEC_VRS%ROWTYPE;
686 l_item_mst MTL_SYSTEM_ITEMS_B%ROWTYPE;
687 l_sampling_plan GMD_SAMPLING_PLANS%ROWTYPE;
688 l_sampling_plan_out GMD_SAMPLING_PLANS%ROWTYPE;
689 l_inventory_item_id NUMBER;
690 l_organization_id NUMBER;
691 l_uom_rate NUMBER;
692
693 -- Exceptions
694 e_spec_fetch_error EXCEPTION;
695 e_smpl_plan_fetch_error EXCEPTION;
696 e_error_fetch_item EXCEPTION;
697
698 BEGIN
699 -- Initialize API return status to success
700 x_return_status := FND_API.G_RET_STS_SUCCESS;
701
702 IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
703 -- Invalid Operation
704 GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
705 RAISE FND_API.G_EXC_ERROR;
706 END IF;
707
708 -- Verify that the specification exists.
709 l_spec.spec_id := p_inv_vr.spec_id;
710 IF NOT (GMD_Specifications_PVT.Fetch_Row(
711 p_specifications => l_spec,
712 x_specifications => l_spec_out)
713 ) THEN
714 -- Fetch Error
715 GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
716 RAISE e_spec_fetch_error;
717 END IF;
718
719 l_spec := l_spec_out ;
720
721 -- Verify that the Sampling Plan exists.
722 --odab added this test.
723 IF (p_inv_vr.sampling_plan_id IS NOT NULL)
724 THEN
725 l_sampling_plan.sampling_plan_id := p_inv_vr.sampling_plan_id;
726 IF NOT (GMD_Sampling_Plans_PVT.Fetch_Row(
727 p_sampling_plan => l_sampling_plan,
728 x_sampling_plan => l_sampling_plan_out)
729 ) THEN
730 -- Fetch Error
731 GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
732 RAISE e_smpl_plan_fetch_error;
733 END IF;
734 l_sampling_plan:= l_sampling_plan_out ;
735 END IF;
736
737 -- bug 2691994 02-DEC-02:
738 -- odaboval From this point, the l_inv_vr is used
739 -- and will populate the return parameter x_inv_vr
740 l_inv_vr := p_inv_vr;
741 IF (p_called_from = 'API') THEN
742 --For mini pack L, bug 3439865
743 IF ( nvl(p_inv_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
744 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
745 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
746 RAISE FND_API.G_EXC_ERROR;
747 END IF;
748 -- end 3439865
749 -- Check for NULLs and Valid Foreign Keys in the input parameter
750 check_for_null_and_fks_in_ivr
751 (
752 p_inv_vr => p_inv_vr
753 , p_spec => l_spec
754 , x_inv_vr => l_inv_vr_tmp
755 , x_return_status => l_return_status
756 );
757 -- No need if called from FORM since it is already
758 -- done in the form
759
760 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
761 -- Message is alrady logged by check_for_null procedure
762 RAISE FND_API.G_EXC_ERROR;
763 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
764 -- Message is alrady logged by check_for_null procedure
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 l_inv_vr := l_inv_vr_tmp;
769 END IF;
770
771 -- First Verify that the SAME VR does not exists
772 -- bug 2691994 02-DEC-02, odaboval changed p_inv_vr by l_inv_vr
773 --IF (p_operation IN ('INSERT')
774 IF (p_operation IN ('INSERT', 'UPDATE')
775 AND inv_vr_exist(l_inv_vr, l_spec))
776 THEN
777 -- Disaster, Trying to insert duplicate
778 -- bug 2630007, odaboval put the message in function inv_vr_exist.
779 -- GMD_API_PUB.Log_Message('GMD_INV_VR_EXIST');
780 RAISE FND_API.G_EXC_ERROR;
781 END IF;
782
783
784 -- Check to make sure that a samplingplan exists
785 -- if auto sample flag on
786 IF ((p_inv_vr.sampling_plan_id IS NULL) and
787 (p_inv_vr.auto_sample_ind = 'Y'))
788 THEN
789 GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
790 RAISE e_smpl_plan_fetch_error;
791 END IF;
792
793
794 -- Sample Quantity UOM must be convertible to Item's UOM
795 BEGIN
796 SELECT inventory_item_id INTO l_inventory_item_id FROM
797 gmd_specifications WHERE spec_id = p_inv_vr.spec_id;
798 SELECT owner_organization_id INTO l_organization_id FROM
799 gmd_specifications WHERE spec_id = p_inv_vr.spec_id;
800 SELECT * INTO l_item_mst
801 FROM mtl_system_items_b
802 WHERE inventory_item_id = l_inventory_item_id
803 AND organization_id = l_organization_id;
804 EXCEPTION
805 WHEN OTHERS THEN
806 GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
807 RAISE e_error_fetch_item;
808 END;
809
810 IF (l_inv_vr.sampling_plan_id IS NOT NULL)
811 THEN
812 --As part of Convergence, the following call is replaced with new one.
813 /*GMICUOM.icuomcv(pitem_id => l_item_mst.item_id,
814 plot_id => 0,
815 pcur_qty => 1,
816 pcur_uom => l_sampling_plan.sample_uom,
817 pnew_uom => l_item_mst.item_um,
818 onew_qty => dummy);*/
819 inv_convert.inv_um_conversion (
820 from_unit => l_sampling_plan.sample_qty_uom,
821 to_unit => l_item_mst.primary_uom_code,
822 item_id => l_inventory_item_id,
823 lot_number => NULL,
824 organization_id => l_organization_id ,
825 uom_rate => l_uom_rate );
826
827 IF l_uom_rate = -99999 THEN
828 GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
829 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831 END IF;
832
833 -- No need to check the return status because above procedure
834 -- logs appropriate message on the stack and raises an exception.
835
836 -- The Start Date must be less than the End Date
837 -- bug 2691994 02-DEC-02, odaboval changed p_inv_vr by l_inv_vr
838 If ( l_inv_vr.end_date IS NOT NULL AND
839 l_inv_vr.start_date > l_inv_vr.end_date) THEN
840 GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
841 RAISE FND_API.G_EXC_ERROR;
842 END IF;
843
844 -- Spec VR Status Must be less than Spec Status upto Appoved Stages
845 IF (floor(l_spec.spec_status/100) <= 7 AND
846 floor(l_inv_vr.spec_vr_status/100) <= 7 AND
847 l_inv_vr.spec_vr_status > l_spec.spec_status) THEN
848 GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
849 RAISE FND_API.G_EXC_ERROR;
850 END IF;
851
852 -- srakrish Bug 5276602: Checking if the Lot optionalfeild is set to yes when lot or parent lot or entered.
853 -- This scenario exists when the api is called from the wrapper.
854 IF l_inv_vr.lot_number IS NOT NULL or l_inv_vr.parent_lot_number IS NOT NULL then
855 IF l_inv_vr.lot_optional_on_sample = 'Y' THEN
856 GMD_API_PUB.Log_Message('GMD_SPEC_VR_LOT_CNTRL_INVALID');
857 RAISE FND_API.G_EXC_ERROR;
858 END IF;
859 END IF;
860
861 -- All systems GO...
862 x_inv_vr := l_inv_vr;
863
864 EXCEPTION
865 WHEN FND_API.G_EXC_ERROR OR
866 e_spec_fetch_error OR
867 e_smpl_plan_fetch_error OR
868 e_error_fetch_item
869 THEN
870 x_return_status := FND_API.G_RET_STS_ERROR ;
871 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
873 WHEN OTHERS THEN
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
875
876
877 END validate_inv_vr;
878
879
880 --Start of comments
881 --+========================================================================+
882 --| API Name : check_for_null_and_fks_in_ivr |
883 --| TYPE : Group |
884 --| Notes : This procedure checks for NULL and Foreign Key |
885 --| constraints for the required filed in the Spec |
886 --| Inventory VR record. |
887 --| |
888 --| If everything is fine then 'S' is returned in the |
889 --| parameter - x_return_status otherwise error message |
890 --| is logged and error status - E or U returned |
891 --| |
892 --| HISTORY |
893 --| Chetan Nagar 26-Jul-2002 Created. |
894 --| Olivier Daboval 02-DEC-2002 Added x_inv_vr as out parameter |
895 --| Jeff Baird 30-Apr-2004 Bug #3500024 |
896 --| Three following fixes ported to L. |
897 --| P.Raghu 23-JAN-2004 Bug#3381762 |
898 --| Modified the existing logic for Validation of |
899 --| lot_id and lot_no and sublot as suggested |
900 --| Jeff Baird 05-Mar-2004 Bug #3476572 |
901 --| Added update to lot_id if lot_no / sublot_no passed|
902 --| Jeff Baird 20-Apr-2004 Bug #3582010 |
903 --| Added l_sublot_no where it was left out. |
904 --| |
905 --| SaiKiran 04-MAY-2004 Enhancement #3476560. added |
906 --| 'delayed_lot_entry' to the call to 'check_vr_controls'|
907 --| procedure. |
908 --| |
909 --| Saikiran 24-Apr-2005 Convergence Changes |
910 --| srakrish 15-june-06 BUG 5251172: Checking if the |
911 --| responsibility is available to the |
912 --| organization |
913 --+========================================================================+
914 -- End of comments
915
916 PROCEDURE check_for_null_and_fks_in_ivr
917 (
918 p_inv_vr IN GMD_INVENTORY_SPEC_VRS%ROWTYPE
919 , p_spec IN GMD_SPECIFICATIONS%ROWTYPE
920 , x_inv_vr OUT NOCOPY GMD_INVENTORY_SPEC_VRS%ROWTYPE
921 , x_return_status OUT NOCOPY VARCHAR2
922 )
923 IS
924
925 l_inv_vr GMD_INVENTORY_SPEC_VRS%ROWTYPE;
926 l_spec GMD_SPECIFICATIONS%ROWTYPE;
927
928 CURSOR c_item_lot_number IS
929 SELECT 1
930 FROM mtl_lot_numbers
931 WHERE organization_id = l_inv_vr.organization_id
932 AND inventory_item_id = l_spec.inventory_item_id
933 AND lot_number = l_inv_vr.lot_number;
934
935 CURSOR c_item_parent_lot IS
936 SELECT 1
937 FROM mtl_lot_numbers
938 WHERE organization_id = l_inv_vr.organization_id
939 AND inventory_item_id = l_spec.inventory_item_id
940 AND parent_lot_number = l_inv_vr.parent_lot_number;
941
942
943 CURSOR c_subinventory IS
944 SELECT 1
945 FROM mtl_secondary_inventories
946 WHERE secondary_inventory_name = l_inv_vr.subinventory
947 AND organization_id = l_inv_vr.organization_id;
948
949 CURSOR c_locator IS
950 SELECT 1
951 FROM mtl_item_locations
952 WHERE organization_id = l_inv_vr.organization_id
953 AND inventory_location_id = l_inv_vr.locator_id;
954
955
956
957 l_sample_display GMD_SAMPLES_GRP.sample_display_rec;
958 dummy NUMBER;
959 l_status_ctl VARCHAR2(1);
960 l_lot_ctl NUMBER;
961 l_child_lot_ctl VARCHAR2(1);
962 l_locator_type NUMBER;
963 l_return_status VARCHAR2(1);
964
965 BEGIN
966 l_inv_vr := p_inv_vr;
967 l_spec := p_spec;
968
969 check_who( p_user_id => l_inv_vr.created_by);
970 check_who( p_user_id => l_inv_vr.last_updated_by);
971 IF (l_inv_vr.creation_date IS NULL
972 OR l_inv_vr.last_update_date IS NULL)
973 THEN
974 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
975 'WHAT', 'the dates must not be NULL');
976 RAISE FND_API.G_EXC_ERROR;
977 END IF;
978
979 -- Organization is valid
980 IF (l_inv_vr.organization_id IS NOT NULL) THEN
981 -- Check that Organization is a valid one.
982 OPEN c_orgn( l_inv_vr.organization_id);
983 FETCH c_orgn INTO dummy;
984 IF (c_orgn%NOTFOUND)
985 THEN
986 CLOSE c_orgn;
987 GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
988 'ORGN_ID', l_inv_vr.organization_id);
989 RAISE FND_API.G_EXC_ERROR;
990 END IF;
991 CLOSE c_orgn;
992 END IF;
993
994 --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
995 IF NOT (gmd_api_grp.OrgnAccessible(p_inv_vr.organization_id)) THEN
996 RAISE FND_API.G_EXC_ERROR;
997 END IF;
998
999 --=========================================================================
1000 -- Get Item Controls
1001 --=========================================================================
1002
1003 l_sample_display.organization_id := l_inv_vr.organization_id;
1004 l_sample_display.inventory_item_id := l_spec.inventory_item_id;
1005 GMD_SAMPLES_GRP.get_item_values (p_sample_display => l_sample_display);
1006 l_lot_ctl := l_sample_display.lot_control_code;
1007 l_status_ctl := l_sample_display.lot_status_enabled;
1008 l_child_lot_ctl := l_sample_display.child_lot_flag;
1009
1010 GMD_COMMON_GRP.item_is_locator_controlled (
1011 p_organization_id => l_inv_vr.organization_id
1012 ,p_subinventory => l_inv_vr.subinventory
1013 ,p_inventory_item_id => l_spec.inventory_item_id
1014 ,x_locator_type => l_locator_type
1015 ,x_return_status => l_return_status);
1016
1017 IF (l_inv_vr.lot_number IS NOT NULL)
1018 THEN
1019 IF (l_lot_ctl = 2) THEN --Item is lot controlled
1020 OPEN c_item_lot_number;
1021 FETCH c_item_lot_number INTO dummy;
1022 IF (c_item_lot_number%NOTFOUND)
1023 THEN
1024 CLOSE c_item_lot_number;
1025 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1026 'WHAT', 'lot_number');
1027 RAISE FND_API.G_EXC_ERROR;
1028 END IF;
1029 CLOSE c_item_lot_number;
1030 ELSE --Item is not lot controlled
1031 FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_LOT_CONTROL');
1032 FND_MSG_PUB.ADD;
1033 RAISE FND_API.G_EXC_ERROR;
1034 END IF;
1035 END IF;
1036
1037 IF (l_inv_vr.parent_lot_number IS NOT NULL)
1038 THEN
1039 IF (l_child_lot_ctl = 'Y') THEN --Item is child lot controlled
1040 OPEN c_item_parent_lot;
1041 FETCH c_item_parent_lot INTO dummy;
1042 IF (c_item_parent_lot%NOTFOUND)
1043 THEN
1044 CLOSE c_item_parent_lot;
1045 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1046 'WHAT', 'parent_lot_number');
1047 RAISE FND_API.G_EXC_ERROR;
1048 END IF;
1049 CLOSE c_item_parent_lot;
1050 ELSE --Item is not child lot controlled
1051 FND_MESSAGE.SET_NAME('GMD','GMD_ITEM_NOT_CHILD_LOT_CONTROL');
1052 FND_MSG_PUB.ADD;
1053 RAISE FND_API.G_EXC_ERROR;
1054 END IF;
1055 END IF;
1056
1057 -- Subinventory is Valid
1058 IF (l_inv_vr.subinventory IS NOT NULL) THEN
1059 OPEN c_subinventory;
1060 FETCH c_subinventory INTO dummy;
1061 IF (c_subinventory%NOTFOUND)
1062 THEN
1063 CLOSE c_subinventory;
1064 GMD_API_PUB.Log_Message('GMD_SUBINVENTORY_NOT_FOUND',
1065 'SUBINVENTORY', l_inv_vr.subinventory);
1066 RAISE FND_API.G_EXC_ERROR;
1067 END IF;
1068 CLOSE c_subinventory;
1069 END IF;
1070
1071 -- Location is valid
1072 IF (l_locator_type IN (2,3))
1073 THEN
1074 -- Here l_locator_type IN (2,3)
1075 IF (l_inv_vr.locator_id IS NULL)
1076 THEN
1077 -- Location can be NULL in this case.
1078 null;
1079 ELSE
1080 -- Check that Location exist in MTL_ITEM_LOCATIONS
1081 OPEN c_locator;
1082 FETCH c_locator INTO dummy;
1083 IF (c_locator%NOTFOUND)
1084 THEN
1085 CLOSE c_locator;
1086 GMD_API_PUB.Log_Message('GMD_LOCT_NOT_FOUND');
1087 RAISE FND_API.G_EXC_ERROR;
1088 END IF;
1089 CLOSE c_locator;
1090 END IF; -- location IS NOT NULL
1091 ELSE -- Here l_locator_type not IN (2,3)
1092 IF (l_inv_vr.locator_id IS NOT NULL)
1093 THEN
1094 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1095 'WHAT', 'locator should be NULL');
1096 RAISE FND_API.G_EXC_ERROR;
1097 END IF;
1098 END IF; -- l_locator_type IN (2,3)
1099 --=========================================================================
1100 -- lot_optional_on_sample :
1101 -- When this field is NOT NULL, all the following fields must be null :
1102 -- sample_inv_trans_ind, control_lot_attrib_ind, in_spec_lot_status, out_of_spec_lot_status
1103 --=========================================================================
1104 --Enhancement #3476560. added 'delayed_lot_entry' to the call to 'check_vr_controls' procedure
1105 check_VR_Controls( p_VR_type => 'INVENTORY'
1106 , p_lot_optional_on_sample => l_inv_vr.lot_optional_on_sample
1107 , p_delayed_lot_entry => l_inv_vr.delayed_lot_entry
1108 , p_sample_inv_trans_ind => l_inv_vr.sample_inv_trans_ind
1109 , p_lot_ctl => l_lot_ctl
1110 , p_status_ctl => l_status_ctl
1111 , p_control_lot_attrib_ind => l_inv_vr.control_lot_attrib_ind
1112 , p_in_spec_lot_status_id => l_inv_vr.in_spec_lot_status_id
1113 , p_out_of_spec_lot_status_id => l_inv_vr.out_of_spec_lot_status_id
1114 , p_control_batch_step_ind => NULL);
1115
1116 --=========================================================================
1117 -- spec_vr_status :
1118 --=========================================================================
1119 -- Check that Spec VR Status exist in GMD_QC_STATUS
1120 OPEN c_status(l_inv_vr.spec_vr_status);
1121 FETCH c_status
1122 INTO dummy;
1123 IF (c_status%NOTFOUND)
1124 THEN
1125 CLOSE c_status;
1126 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
1127 'STATUS', l_inv_vr.spec_vr_status);
1128 RAISE FND_API.G_EXC_ERROR;
1129 END IF;
1130 CLOSE c_status;
1131
1132 --=========================================================================
1133 -- start_date : This field is mandatory
1134 --=========================================================================
1135 IF (l_inv_vr.start_date IS NULL)
1136 THEN
1137 GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
1138 RAISE FND_API.G_EXC_ERROR;
1139 END IF;
1140
1141 --=========================================================================
1142 -- COA section :
1143 --=========================================================================
1144 check_COA( p_coa_type => l_inv_vr.coa_type
1145 , p_coa_at_ship_ind => l_inv_vr.coa_at_ship_ind
1146 , p_coa_at_invoice_ind => l_inv_vr.coa_at_invoice_ind
1147 , p_coa_req_from_supl_ind => l_inv_vr.coa_req_from_supl_ind);
1148
1149 x_inv_vr := l_inv_vr;
1150
1151
1152 EXCEPTION
1153 WHEN FND_API.G_EXC_ERROR THEN
1154 x_return_status := FND_API.G_RET_STS_ERROR ;
1155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1157 WHEN OTHERS THEN
1158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1159
1160 END check_for_null_and_fks_in_ivr;
1161
1162
1163
1164
1165
1166 --Start of comments
1167 --+========================================================================+
1168 --| API Name : inv_vr_exist |
1169 --| TYPE : Group |
1170 --| Notes : This function returns TRUE if the inventory VR already |
1171 --| exists for the spcified parameter in the database, FALSE |
1172 --| otherwise. |
1173 --| |
1174 --| HISTORY |
1175 --| Chetan Nagar 26-Jul-2002 Created. |
1176 --| Olivier Daboval 17-OCT-2002 bug 2630007, added spec_name in |
1177 --| the VR_EXIST message |
1178 --| Olivier Daboval 14-NOV-2002 Added NVL(spec_vr_id, -1) because |
1179 --| the API passes a NULL spec_vr_id |
1180 --| in insert mode. |
1181 --| Brenda Stone 20-NOV-2003 Bug 3090290; allow duplicate spec vr |
1182 --| with "OBSOLUTE" status |
1183 --| Bug 2984784; add Version to msg for |
1184 --| existing spec vr. |
1185 --| |
1186 --| Saikiran 12-Apr-2005 Convergence Changes |
1187 --| Plowe 04-Apr-2006 Bug 5117733 - added item revision to match
1188 --+========================================================================+
1189 -- End of comments
1190
1191 FUNCTION inv_vr_exist(p_inv_vr GMD_INVENTORY_SPEC_VRS%ROWTYPE,
1192 p_spec GMD_SPECIFICATIONS%ROWTYPE)
1193 RETURN BOOLEAN IS
1194
1195 CURSOR c_inv_vr IS
1196 SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
1197 FROM gmd_specifications_b s, gmd_inventory_spec_vrs vr
1198 WHERE s.spec_id = vr.spec_id
1199 AND s.owner_organization_id = p_spec.owner_organization_id
1200 AND s.inventory_item_id = p_spec.inventory_item_id
1201 AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
1202 (s.revision = p_spec.revision )
1203 )
1204 AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
1205 (s.grade_code = p_spec.grade_code)
1206 )
1207
1208 AND ((vr.organization_id is NULL AND p_inv_vr.organization_id is NULL) OR
1209 (vr.organization_id = p_inv_vr.organization_id)
1210 )
1211 AND ((vr.lot_number is NULL AND p_inv_vr.lot_number is NULL) OR
1212 (vr.lot_number = p_inv_vr.lot_number)
1213 )
1214 AND ((vr.parent_lot_number is NULL AND p_inv_vr.parent_lot_number is NULL) OR
1215 (vr.parent_lot_number = p_inv_vr.parent_lot_number)
1216 )
1217 AND ((vr.subinventory is NULL AND p_inv_vr.subinventory is NULL) OR
1218 (vr.subinventory = p_inv_vr.subinventory)
1219 )
1220 AND ((vr.locator_id is NULL AND p_inv_vr.locator_id is NULL) OR
1221 (vr.locator_id = p_inv_vr.locator_id)
1222 )
1223 AND ((vr.end_date is NULL AND (p_inv_vr.end_date IS NULL OR
1224 p_inv_vr.end_date >= vr.start_date)) OR
1225 (p_inv_vr.end_date IS NULL AND
1226 p_inv_vr.start_date <= nvl(vr.end_date, p_inv_vr.start_date)) OR
1227 (p_inv_vr.start_date <= vr.end_date AND p_inv_vr.end_date >= vr.start_date)
1228 )
1229 AND ( floor(vr.spec_vr_status / 100) = floor(p_inv_vr.spec_vr_status/100) AND
1230 /* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
1231 p_inv_vr.spec_vr_status <> 1000 )
1232 AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
1233 WHERE status_type = 800)
1234 AND vr.delete_mark = 0
1235 AND s.delete_mark = 0
1236 AND vr.spec_vr_id <> NVL(p_inv_vr.spec_vr_id, -1)
1237 ;
1238
1239 dummy PLS_INTEGER;
1240 specname VARCHAR2(80);
1241 specvers NUMBER;
1242
1243 BEGIN
1244
1245 OPEN c_inv_vr;
1246 FETCH c_inv_vr INTO dummy, specname, specvers;
1247 IF c_inv_vr%FOUND THEN
1248 CLOSE c_inv_vr;
1249 FND_MESSAGE.SET_NAME('GMD', 'GMD_INV_VR_EXIST');
1250 FND_MESSAGE.SET_TOKEN('spec', specname);
1251 FND_MESSAGE.SET_TOKEN('vers', specvers);
1252 FND_MSG_PUB.ADD;
1253 RETURN TRUE;
1254 ELSE
1255 CLOSE c_inv_vr;
1256 RETURN FALSE;
1257 END IF;
1258
1259 EXCEPTION
1260 -- Though there is no reason the program can reach
1261 -- here, this is coded just for the reasons we can
1262 -- not think of!
1263 WHEN OTHERS THEN
1264 FND_MESSAGE.SET_NAME('GMD', 'GMD_API_ERROR');
1265 FND_MESSAGE.SET_TOKEN('PACKAGE', 'GMD_SPEC_VRS_GRP.INV_VR_EXIST' );
1266 FND_MESSAGE.SET_TOKEN('ERROR', SUBSTR(SQLERRM,1,200));
1267 FND_MSG_PUB.ADD;
1268
1269 RETURN TRUE;
1270
1271 END inv_vr_exist;
1272
1273
1274 --Start of comments
1275 --+========================================================================+
1276 --| API Name : validate_wip_vr |
1277 --| TYPE : Group |
1278 --| Notes : This procedure validates all the fields of |
1279 --| WIP validity rule record. This procedure can be |
1280 --| called from FORM or API and the caller need |
1281 --| to specify this in p_called_from parameter |
1282 --| while calling this procedure. Based on where |
1283 --| it is called from certain validations will |
1284 --| either be performed or skipped. |
1285 --| |
1286 --| If everything is fine then OUT parameter |
1287 --| x_return_status is set to 'S' else appropriate |
1288 --| error message is put on the stack and error |
1289 --| is returned. |
1290 --| |
1291 --| HISTORY |
1292 --| Chetan Nagar 26-Jul-2002 Created. |
1293 --| Olivier Daboval 02-DEC-2002 Added x_wip_vr as out parameter |
1294 --| |
1295 --+========================================================================+
1296 -- End of comments
1297
1298 PROCEDURE validate_wip_vr
1299 (
1300 p_wip_vr IN GMD_WIP_SPEC_VRS%ROWTYPE
1301 , p_called_from IN VARCHAR2
1302 , p_operation IN VARCHAR2
1303 , x_wip_vr OUT NOCOPY GMD_WIP_SPEC_VRS%ROWTYPE
1304 , x_return_status OUT NOCOPY VARCHAR2
1305 ) IS
1306
1307 -- Local Variables
1308 dummy NUMBER;
1309 l_return_status VARCHAR2(1);
1310
1311 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1312 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
1313 l_wip_vr GMD_WIP_SPEC_VRS%ROWTYPE;
1314 l_wip_vr_tmp GMD_WIP_SPEC_VRS%ROWTYPE;
1315 l_item_mst MTL_SYSTEM_ITEMS_B%ROWTYPE;
1316 l_sampling_plan GMD_SAMPLING_PLANS%ROWTYPE;
1317 l_sampling_plan_out GMD_SAMPLING_PLANS%ROWTYPE;
1318 l_inventory_item_id NUMBER;
1319 l_organization_id NUMBER;
1320 l_uom_rate NUMBER;
1321
1322 -- Exceptions
1323 e_spec_fetch_error EXCEPTION;
1324 e_smpl_plan_fetch_error EXCEPTION;
1325 e_error_fetch_item EXCEPTION;
1326
1327 BEGIN
1328 -- Initialize API return status to success
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330
1331 IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
1332 -- Invalid Operation
1333 GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
1334 RAISE FND_API.G_EXC_ERROR;
1335 END IF;
1336
1337 -- Verify that the specification exists.
1338 l_spec.spec_id := p_wip_vr.spec_id;
1339 IF NOT (GMD_Specifications_PVT.Fetch_Row(
1340 p_specifications => l_spec,
1341 x_specifications => l_spec_out)
1342 ) THEN
1343 -- Fetch Error
1344 GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
1345 RAISE e_spec_fetch_error;
1346 END IF;
1347
1348 l_spec := l_spec_out ;
1349
1350 -- Verify that the Sampling Plan exists.
1351 --odab added this test.
1352 IF (p_wip_vr.sampling_plan_id IS NOT NULL)
1353 THEN
1354 l_sampling_plan.sampling_plan_id := p_wip_vr.sampling_plan_id;
1355 IF NOT (GMD_Sampling_Plans_PVT.Fetch_Row(
1356 p_sampling_plan => l_sampling_plan,
1357 x_sampling_plan => l_sampling_plan_out)
1358 ) THEN
1359 -- Fetch Error
1360 GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
1361 RAISE e_smpl_plan_fetch_error;
1362 END IF;
1363 l_sampling_plan := l_sampling_plan_out ;
1364 END IF;
1365
1366 -- Check to make sure that a samplingplan exists
1367 -- if auto sample flag on
1368
1369 IF ((p_wip_vr.sampling_plan_id IS NULL) and
1370 (p_wip_vr.auto_sample_ind = 'Y'))
1371 THEN
1372 GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
1373 RAISE FND_API.G_EXC_ERROR;
1374 END IF;
1375
1376
1377 -- bug 2691994 02-DEC-02:
1378 -- odaboval From this point, the l_wip_vr is used
1379 -- and will populate the return parameter x_wip_vr
1380 l_wip_vr := p_wip_vr;
1381 IF (p_called_from = 'API') THEN
1382 --For mini pack L, bug 3439865
1383 IF (nvl(p_wip_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
1384 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1385 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
1386 RAISE FND_API.G_EXC_ERROR;
1387 END IF;
1388 -- end 3439865
1389 -- Check for NULLs and Valid Foreign Keys in the input parameter
1390 GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_wvr
1391 (
1392 p_wip_vr => p_wip_vr
1393 , p_spec => l_spec
1394 , x_wip_vr => l_wip_vr_tmp
1395 , x_return_status => l_return_status
1396 );
1397 -- No need if called from FORM since it is already
1398 -- done in the form
1399
1400 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1401 -- Message is alrady logged by check_for_null procedure
1402 RAISE FND_API.G_EXC_ERROR;
1403 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1404 -- Message is alrady logged by check_for_null procedure
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 END IF;
1407
1408 l_wip_vr := l_wip_vr_tmp;
1409 END IF;
1410
1411 -- First Verify that the SAME VR does not exists
1412 -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1413 IF (p_operation IN ('INSERT', 'UPDATE')
1414 AND wip_vr_exist(l_wip_vr, l_spec))
1415 THEN
1416 -- Disaster, Trying to insert duplicate
1417 -- bug 2630007, odaboval put the message in function wip_vr_exist.
1418 -- GMD_API_PUB.Log_Message('GMD_WIP_VR_EXIST');
1419 RAISE FND_API.G_EXC_ERROR;
1420 END IF;
1421
1422 -- Sample Quantity UOM must be convertible to Item's UOM
1423 BEGIN
1424 SELECT inventory_item_id INTO l_inventory_item_id FROM
1425 gmd_specifications WHERE spec_id = p_wip_vr.spec_id;
1426 SELECT owner_organization_id INTO l_organization_id FROM
1427 gmd_specifications WHERE spec_id = p_wip_vr.spec_id;
1428 SELECT * INTO l_item_mst
1429 FROM mtl_system_items_b
1430 WHERE inventory_item_id = l_inventory_item_id
1431 AND organization_id = l_organization_id;
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
1435 RAISE e_error_fetch_item;
1436 END;
1437
1438
1439 --odab added this test.
1440 -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1441 IF (l_wip_vr.sampling_plan_id IS NOT NULL)
1442 THEN
1443
1444 inv_convert.inv_um_conversion (
1445 from_unit => l_sampling_plan.sample_qty_uom,
1446 to_unit => l_item_mst.primary_uom_code,
1447 item_id => l_inventory_item_id,
1448 lot_number => NULL,
1449 organization_id => l_organization_id,
1450 uom_rate => l_uom_rate );
1451
1452 IF l_uom_rate = -99999 THEN
1453 GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
1454 RAISE FND_API.G_EXC_ERROR;
1455 END IF;
1456 END IF;
1457
1458 -- No need to check the return status because above procedure
1459 -- logs appropriate message on the stack and raises an exception.
1460
1461 -- The Start Date must be less than the End Date
1462 -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1463 If ( l_wip_vr.end_date IS NOT NULL AND
1464 l_wip_vr.start_date > l_wip_vr.end_date) THEN
1465 GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
1466 RAISE FND_API.G_EXC_ERROR;
1467 END IF;
1468
1469 -- Spec VR Status Must be less than Spec Status upto Appoved Stages
1470 -- bug 2691994 02-DEC-02, odaboval changed p_wip_vr by l_wip_vr
1471 IF (floor(l_spec.spec_status/100) <= 7 AND
1472 floor(l_wip_vr.spec_vr_status/100) <= 7 AND
1473 l_wip_vr.spec_vr_status > l_spec.spec_status) THEN
1474 GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
1475 RAISE FND_API.G_EXC_ERROR;
1476 END IF;
1477
1478 -- All systems GO...
1479 x_wip_vr := l_wip_vr;
1480
1481 EXCEPTION
1482 WHEN FND_API.G_EXC_ERROR OR
1483 e_spec_fetch_error OR
1484 e_smpl_plan_fetch_error OR
1485 e_error_fetch_item
1486
1487 THEN
1488 x_return_status := FND_API.G_RET_STS_ERROR ;
1489 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1491 WHEN OTHERS THEN
1492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1493
1494
1495 END validate_wip_vr;
1496
1497
1498
1499
1500 --Start of comments
1501 --+========================================================================+
1502 --| API Name : check_for_null_and_fks_in_wvr |
1503 --| TYPE : Group |
1504 --| Notes : This procedure checks for NULL and Foreign Key |
1505 --| constraints for the required filed in the Spec |
1506 --| WIP VR record. |
1507 --| |
1508 --| If everything is fine then 'S' is returned in the |
1509 --| parameter - x_return_status otherwise error message |
1510 --| is logged and error status - E or U returned |
1511 --| |
1512 --| HISTORY |
1513 --| Chetan Nagar 26-Jul-2002 Created. |
1514 --| Olivier Daboval 02-DEC-2002 Added x_wip_vr as out parameter |
1515 --| Olivier Daboval 01-APR-2003 Now, populate the lower levels |
1516 --| Bug 2733426 Formula/Routing when recipe is given |
1517 --| SaiKiran 04-MAY-2004 Enhancement #3476560. added |
1518 --| 'delayed_lot_entry' to the call to 'check_vr_controls'|
1519 --| procedure. |
1520 --| |
1521 --| Saikiran 11-Apr-05 Convergence changes |
1522 --| srakrish 15-june-06 BUG 5251172: Checking if the |
1523 --| responsibility is available to the |
1524 --| organization |
1525 --| |
1526 --+========================================================================+
1527 -- End of comments
1528
1529 PROCEDURE check_for_null_and_fks_in_wvr
1530 (
1531 p_wip_vr IN GMD_WIP_SPEC_VRS%ROWTYPE
1532 , p_spec IN GMD_SPECIFICATIONS%ROWTYPE
1533 , x_wip_vr OUT NOCOPY GMD_WIP_SPEC_VRS%ROWTYPE
1534 , x_return_status OUT NOCOPY VARCHAR2
1535 )
1536 IS
1537
1538 l_wip_vr GMD_WIP_SPEC_VRS%ROWTYPE;
1539 l_spec GMD_SPECIFICATIONS%ROWTYPE;
1540
1541 -- bug 4924483 sql id 14687134 MJC take out org_access_view as not used in query
1542 -- bug 5223014 - sql id 17532478 NO change required as added created an index on Organization_Id
1543 -- to stop FTS on gmd_parameters_hdr
1544 CURSOR c_orgn_plant ( p_organization_id IN NUMBER) IS
1545 SELECT 1
1546 FROM
1547 -- org_access_view o,
1548 mtl_parameters m,
1549 gmd_parameters_hdr h
1550 WHERE h.organization_id = m.organization_id
1551 AND m.process_enabled_flag = 'Y'
1552 AND m.organization_id = p_organization_id ;
1553 -- AND h.lab_ind = 1; -- FP For bug 7419838
1554
1555
1556 -- bug 4924483 sql id 14687160 (shared mem > 1 mill) use base tables
1557 CURSOR c_batch IS
1558 SELECT gr.recipe_id, gr.recipe_no, gr.recipe_version
1559 , ffm.formula_id, ffm.formula_no, ffm.formula_vers
1560 , rout.routing_id, rout.routing_no, rout.routing_vers
1561 FROM gme_batch_header bh
1562 , gme_material_details md
1563 , gmd_recipes_b gr -- just need base table here not view
1564 , gmd_recipe_validity_rules rvr
1565 , gmd_status gs
1566 , fm_matl_dtl fmd
1567 , fm_form_mst_b ffm -- just need base table here not view
1568 , gmd_routings_b rout -- just need base table here not view
1569 WHERE rout.routing_id(+) = bh.routing_id
1570 AND rvr.recipe_validity_rule_id = bh.recipe_validity_rule_id
1571 AND rvr.recipe_id = gr.recipe_id
1572 AND ffm.formula_id = bh.formula_id
1573 AND ffm.formula_id = fmd.formula_id
1574 AND fmd.formula_id = bh.formula_id
1575 AND rout.delete_mark = 0
1576 AND gs.delete_mark = 0
1577 AND rvr.delete_mark = 0
1578 AND gr.delete_mark = 0
1579 AND bh.delete_mark = 0
1580 AND ffm.delete_mark = 0
1581 AND fmd.formula_id = gr.formula_id
1582 AND fmd.inventory_item_id = p_spec.inventory_item_id
1583 AND gr.recipe_status = gs.status_code
1584 AND gs.status_code <> '1000'
1585 AND gr.formula_id = bh.formula_id
1586 AND bh.batch_id = md.batch_id
1587 AND bh.batch_type = 0 -- Only BATCH, no FPO
1588 --AND bh.batch_status IN (1, 2) -- PENDING or WIP BATCH only.
1589 AND ( ( bh.batch_status IN (1,2, 3) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'N') )
1590 or ( bh.batch_status IN (1,2, 3,4 ) and ( NVL(fnd_profile.value('GMD_SAMPLE_CLOSED_BATCHES'),'N') = 'Y') ) ) -- Bug # 4619570
1591 AND md.inventory_item_id = p_spec.inventory_item_id
1592 AND bh.organization_id = l_wip_vr.organization_id
1593 AND bh.batch_id = l_wip_vr.batch_id
1594 AND NVL( l_wip_vr.recipe_id, gr.recipe_id) = gr.recipe_id
1595 AND NVL( l_wip_vr.formula_id, bh.formula_id) = bh.formula_id
1596 AND NVL( l_wip_vr.routing_id, bh.routing_id) = bh.routing_id;
1597
1598 CURSOR c_recipe_id IS
1599 SELECT r.recipe_no, r.recipe_version
1600 , ffm.formula_id, ffm.formula_no, ffm.formula_vers
1601 , rout.routing_id, rout.routing_no, rout.routing_vers
1602 FROM gmd_recipes r
1603 , gmd_status s
1604 , gmd_recipe_validity_rules rvr
1605 , gmd_routings rout
1606 , fm_form_mst ffm
1607 , fm_matl_dtl fmd
1608 WHERE rout.routing_id(+) = r.routing_id
1609 AND ffm.formula_id = r.formula_id
1610 AND rvr.recipe_id = r.recipe_id
1611 AND (NVL( l_wip_vr.organization_id, rvr.organization_id) = rvr.organization_id OR rvr.organization_id IS NULL)
1612 AND r.recipe_status = s.status_code
1613 AND r.formula_id = fmd.formula_id
1614 AND fmd.inventory_item_id = p_spec.inventory_item_id
1615 AND NVL(rout.delete_mark, 0) = 0
1616 AND rout.delete_mark = 0
1617 AND rvr.delete_mark = 0
1618 AND s.delete_mark = 0
1619 AND r.delete_mark = 0
1620 AND ffm.delete_mark = 0
1621 AND s.status_type <> '1000'
1622 AND r.recipe_id = l_wip_vr.recipe_id
1623 AND NVL( l_wip_vr.formula_id, r.formula_id) = r.formula_id
1624 AND NVL( l_wip_vr.routing_id, rout.routing_id) = rout.routing_id;
1625
1626 CURSOR c_recipe_no IS
1627 SELECT r.recipe_id, r.recipe_version
1628 FROM gmd_recipes r
1629 , gmd_status s
1630 , gmd_recipe_validity_rules rvr
1631 , gmd_routings rout
1632 , fm_form_mst ffm
1633 , fm_matl_dtl fmd
1634 WHERE rout.routing_id(+) = r.routing_id
1635 AND ffm.formula_id = r.formula_id
1636 AND rvr.recipe_id = r.recipe_id
1637 AND (NVL( l_wip_vr.organization_id, rvr.organization_id) = rvr.organization_id OR rvr.organization_id IS NULL)
1638 AND r.recipe_status = s.status_code
1639 AND r.formula_id = fmd.formula_id
1640 AND fmd.inventory_item_id = p_spec.inventory_item_id
1641 AND NVL(rout.delete_mark, 0) = 0
1642 AND rout.delete_mark = 0
1643 AND rvr.delete_mark = 0
1644 AND s.delete_mark = 0
1645 AND r.delete_mark = 0
1646 AND ffm.delete_mark = 0
1647 AND s.status_type <> '1000'
1648 AND r.recipe_no = l_wip_vr.recipe_no
1649 AND NVL( l_wip_vr.recipe_version, r.recipe_version) = r.recipe_version
1650 AND NVL( l_wip_vr.formula_id, r.formula_id) = r.formula_id
1651 AND NVL( l_wip_vr.routing_id, rout.routing_id) = rout.routing_id;
1652
1653 CURSOR c_formula_id IS
1654 SELECT ffm.formula_no, ffm.formula_vers
1655 FROM gmd_recipes grec
1656 , fm_form_mst ffm
1657 , fm_matl_dtl fmd
1658 , gem_lookups gl
1659 , gmd_status s
1660 WHERE s.status_code = ffm.formula_status
1661 AND gl.lookup_code = fmd.line_type
1662 AND gl.lookup_type = 'LINE_TYPE'
1663 AND grec.formula_id(+) = ffm.formula_id
1664 AND fmd.formula_id = ffm.formula_id
1665 AND fmd.inventory_item_id = p_spec.inventory_item_id
1666 AND s.delete_mark = 0
1667 AND grec.delete_mark = 0
1668 AND ffm.delete_mark = 0
1669 AND NVL( l_wip_vr.recipe_id, grec.recipe_id) = grec.recipe_id
1670 AND NVL( l_wip_vr.formulaline_id, fmd.formulaline_id) = fmd.formulaline_id
1671 AND ffm.formula_id = l_wip_vr.formula_id;
1672
1673 CURSOR c_formula_no IS
1674 SELECT ffm.formula_id, ffm.formula_vers
1675 FROM gmd_recipes grec
1676 , fm_form_mst ffm
1677 , fm_matl_dtl fmd
1678 , gem_lookups gl
1679 , gmd_status s
1680 WHERE s.status_code = ffm.formula_status
1681 AND gl.lookup_code = fmd.line_type
1682 AND gl.lookup_type = 'LINE_TYPE'
1683 AND grec.formula_id(+) = ffm.formula_id
1684 AND fmd.formula_id = ffm.formula_id
1685 AND fmd.inventory_item_id = p_spec.inventory_item_id
1686 AND s.delete_mark = 0
1687 AND grec.delete_mark = 0
1688 AND ffm.delete_mark = 0
1689 AND NVL( l_wip_vr.recipe_id, grec.recipe_id) = grec.recipe_id
1690 AND NVL( l_wip_vr.formulaline_id, fmd.formulaline_id) = fmd.formulaline_id
1691 AND NVL( l_wip_vr.formula_vers, ffm.formula_vers) = ffm.formula_vers
1692 AND ffm.formula_no = l_wip_vr.formula_no;
1693
1694
1695 CURSOR c_formulaline_id IS
1696 SELECT 1
1697 FROM fm_matl_dtl fmd
1698 WHERE fmd.inventory_item_id = p_spec.inventory_item_id
1699 AND fmd.formula_id = l_wip_vr.formula_id
1700 AND fmd.formulaline_id = l_wip_vr.formulaline_id;
1701
1702
1703 -- Bug 4640143: added this cursor
1704 CURSOR c_material_detail_id IS
1705 SELECT 1
1706 FROM gme_material_details
1707 WHERE inventory_item_id = p_spec.inventory_item_id
1708 AND batch_id = l_wip_vr.batch_id
1709 AND organization_id = l_wip_vr.organization_id
1710 AND material_detail_id = l_wip_vr.material_detail_id;
1711
1712
1713 CURSOR c_routing_id IS
1714 SELECT r.routing_no, r.routing_vers
1715 FROM gmd_recipes grec
1716 , gmd_status s
1717 , gmd_routings r
1718 WHERE grec.routing_id(+) = r.routing_id
1719 AND s.status_code = r.routing_status
1720 AND NVL( l_wip_vr.recipe_id, grec.recipe_id) = grec.recipe_id
1721 AND NVL( l_wip_vr.formula_id, grec.formula_id) = grec.formula_id
1722 AND s.delete_mark = 0
1723 AND grec.delete_mark = 0
1724 AND r.delete_mark = 0
1725 AND r.routing_id = l_wip_vr.routing_id;
1726
1727 CURSOR c_routing_no IS
1728 SELECT r.routing_id, r.routing_vers
1729 FROM gmd_recipes grec
1730 , gmd_status s
1731 , gmd_routings r
1732 WHERE grec.routing_id(+) = r.routing_id
1733 AND s.status_code = r.routing_status
1734 AND NVL( l_wip_vr.recipe_id, grec.recipe_id) = grec.recipe_id
1735 AND NVL( l_wip_vr.formula_id, grec.formula_id) = grec.formula_id
1736 AND s.delete_mark = 0
1737 AND grec.delete_mark = 0
1738 AND r.delete_mark = 0
1739 AND NVL( l_wip_vr.routing_vers, r.routing_vers) = r.routing_vers
1740 AND r.routing_no = l_wip_vr.routing_no;
1741
1742 CURSOR c_batchstep IS
1743 SELECT bs.batchstep_no
1744 FROM gme_batch_steps bs
1745 , gmd_operations o
1746 WHERE bs.oprn_id = o.oprn_id
1747 AND o.delete_mark = 0
1748 AND bs.delete_mark = 0
1749 AND NVL( l_wip_vr.oprn_id, o.oprn_id) = o.oprn_id
1750 AND NVL( l_wip_vr.oprn_no, o.oprn_no) = o.oprn_no
1751 AND NVL( l_wip_vr.step_no, bs.batchstep_no) = bs.batchstep_no
1752 AND bs.batchstep_id = l_wip_vr.step_id
1753 AND bs.batch_id = l_wip_vr.batch_id;
1754
1755 CURSOR c_routingstep IS
1756 SELECT rd.routingstep_no
1757 FROM fm_rout_dtl rd
1758 , gmd_operations o
1759 WHERE rd.oprn_id = o.oprn_id
1760 AND o.delete_mark = 0
1761 AND NVL( l_wip_vr.oprn_id, o.oprn_id) = o.oprn_id
1762 AND NVL( l_wip_vr.oprn_no, o.oprn_no) = o.oprn_no
1763 AND NVL( l_wip_vr.step_no, rd.routingstep_no) = rd.routingstep_no
1764 AND rd.routingstep_id = l_wip_vr.step_id
1765 AND rd.routing_id = l_wip_vr.routing_id;
1766
1767
1768 CURSOR c_oprn IS
1769 SELECT oprn_no, oprn_vers
1770 FROM gmd_operations o
1771 WHERE o.delete_mark = 0
1772 AND o.oprn_id = l_wip_vr.oprn_id;
1773
1774 dummy PLS_INTEGER;
1775 l_status_ctl VARCHAR2(1);
1776 l_lot_ctl NUMBER;
1777
1778 l_recipe_id GMD_RECIPES.RECIPE_ID%TYPE;
1779 l_recipe_no GMD_RECIPES.RECIPE_NO%TYPE;
1780 l_recipe_version GMD_RECIPES.RECIPE_VERSION%TYPE;
1781 l_formula_id FM_FORM_MST.FORMULA_ID%TYPE;
1782 l_formula_no FM_FORM_MST.FORMULA_NO%TYPE;
1783 l_formula_vers FM_FORM_MST.FORMULA_VERS%TYPE;
1784 l_routing_id GMD_ROUTINGS.ROUTING_ID%TYPE;
1785 l_routing_no GMD_ROUTINGS.ROUTING_NO%TYPE;
1786 l_routing_vers GMD_ROUTINGS.ROUTING_VERS%TYPE;
1787 l_step_no GMD_WIP_SPEC_VRS.STEP_NO%TYPE;
1788 l_oprn_no GMD_OPERATIONS.OPRN_NO%TYPE;
1789 l_oprn_vers GMD_OPERATIONS.OPRN_VERS%TYPE;
1790 l_sample_display GMD_SAMPLES_GRP.sample_display_rec;
1791
1792 BEGIN
1793 l_wip_vr := p_wip_vr;
1794 l_spec := p_spec;
1795
1796 -- At least one parameter is required for the WIP VR.
1797 IF (l_wip_vr.batch_id IS NULL AND
1798 l_wip_vr.recipe_id IS NULL AND
1799 l_wip_vr.recipe_no IS NULL AND
1800 l_wip_vr.formula_id IS NULL AND
1801 l_wip_vr.formula_no IS NULL AND
1802 l_wip_vr.routing_id IS NULL AND
1803 l_wip_vr.routing_no IS NULL AND
1804 l_wip_vr.oprn_id IS NULL AND
1805 l_wip_vr.oprn_no IS NULL) THEN
1806 GMD_API_PUB.Log_Message('GMD_WIP_VR_ALL_NULL');
1807 RAISE FND_API.G_EXC_ERROR;
1808 END IF;
1809
1810 --=========================================================================
1811 -- WHO section :
1812 --=========================================================================
1813 check_who( p_user_id => l_wip_vr.created_by);
1814 check_who( p_user_id => l_wip_vr.last_updated_by);
1815 IF (l_wip_vr.creation_date IS NULL
1816 OR l_wip_vr.last_update_date IS NULL)
1817 THEN
1818 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1819 'WHAT', 'the dates must not be NULL');
1820 RAISE FND_API.G_EXC_ERROR;
1821 END IF;
1822
1823 --=========================================================================
1824 -- Organization : must be a PLANT and belong to the USER
1825 --=========================================================================
1826 IF (l_wip_vr.organization_id IS NOT NULL)
1827 THEN
1828 -- Check that Owner Organization id exist in ORG_ACCESS_VIEW
1829 OPEN c_orgn_plant( l_wip_vr.organization_id);
1830 FETCH c_orgn_plant INTO dummy;
1831 IF (c_orgn_plant%NOTFOUND)
1832 THEN
1833 CLOSE c_orgn_plant;
1834 GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
1835 'ORGN_ID', l_wip_vr.organization_id);
1836 RAISE FND_API.G_EXC_ERROR;
1837 END IF;
1838 CLOSE c_orgn_plant;
1839 END IF;
1840
1841 --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
1842 IF NOT (gmd_api_grp.OrgnAccessible(p_wip_vr.organization_id)) THEN
1843 RAISE FND_API.G_EXC_ERROR;
1844 END IF;
1845
1846 --=========================================================================
1847 -- Get Item Controls
1848 --=========================================================================
1849
1850 l_sample_display.organization_id := l_wip_vr.organization_id;
1851 l_sample_display.inventory_item_id := l_spec.inventory_item_id;
1852 GMD_SAMPLES_GRP.get_item_values (p_sample_display => l_sample_display);
1853 l_lot_ctl := l_sample_display.lot_control_code;
1854 l_status_ctl := l_sample_display.lot_status_enabled;
1855
1856 --=========================================================================
1857 -- lot_optional_on_sample :
1858 -- When this field is NOT NULL, all the following fields must be null :
1859 -- sample_inv_trans_ind, control_lot_attrib_ind, in_spec_lot_status, out_of_spec_lot_status
1860 -- and control_batch_step_ind
1861 --=========================================================================
1862 --Enhancement #3476560. added 'delayed_lot_entry' to the call to 'check_vr_controls' procedure
1863 check_VR_Controls( p_VR_type => 'WIP'
1864 , p_lot_optional_on_sample => l_wip_vr.lot_optional_on_sample
1865 , p_delayed_lot_entry => l_wip_vr.delayed_lot_entry
1866 , p_sample_inv_trans_ind => l_wip_vr.sample_inv_trans_ind
1867 , p_lot_ctl => l_lot_ctl
1868 , p_status_ctl => l_status_ctl
1869 , p_control_lot_attrib_ind => l_wip_vr.control_lot_attrib_ind
1870 , p_in_spec_lot_status_id => l_wip_vr.in_spec_lot_status_id
1871 , p_out_of_spec_lot_status_id => l_wip_vr.out_of_spec_lot_status_id
1872 , p_control_batch_step_ind => l_wip_vr.control_batch_step_ind
1873 , p_auto_complete_batch_step => l_wip_vr.auto_complete_batch_step); -- Bug# 5440347
1874
1875
1876 --=========================================================================
1877 -- spec_vr_status :
1878 --=========================================================================
1879 OPEN c_status(l_wip_vr.spec_vr_status);
1880 FETCH c_status
1881 INTO dummy;
1882 IF (c_status%NOTFOUND)
1883 THEN
1884 CLOSE c_status;
1885 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
1886 'STATUS', l_wip_vr.spec_vr_status);
1887 RAISE FND_API.G_EXC_ERROR;
1888 END IF;
1889 CLOSE c_status;
1890
1891 --=========================================================================
1892 -- start_date : This field is mandatory
1893 --=========================================================================
1894 IF (l_wip_vr.start_date IS NULL)
1895 THEN
1896 GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
1897 RAISE FND_API.G_EXC_ERROR;
1898 END IF;
1899
1900 --=========================================================================
1901 -- COA section :
1902 --=========================================================================
1903 check_COA( p_coa_type => l_wip_vr.coa_type
1904 , p_coa_at_ship_ind => l_wip_vr.coa_at_ship_ind
1905 , p_coa_at_invoice_ind => l_wip_vr.coa_at_invoice_ind
1906 , p_coa_req_from_supl_ind => l_wip_vr.coa_req_from_supl_ind);
1907
1908 --=========================================================================
1909 -- Batch ID is valid
1910 -- When batch_id is NOT NULL, then orgn_code must be MOT NULL
1911 --=========================================================================
1912 IF (l_wip_vr.batch_id IS NOT NULL)
1913 THEN
1914 IF (l_wip_vr.organization_id IS NULL)
1915 THEN
1916 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1917 'WHAT', 'the organization id must not be NULL');
1918 RAISE FND_API.G_EXC_ERROR;
1919 END IF;
1920
1921 OPEN c_batch;
1922 FETCH c_batch
1923 INTO l_recipe_id, l_recipe_no, l_recipe_version
1924 , l_formula_id, l_formula_no, l_formula_vers
1925 , l_routing_id, l_routing_no, l_routing_vers;
1926 IF (c_batch%NOTFOUND)
1927 THEN
1928 CLOSE c_batch;
1929 GMD_API_PUB.Log_Message('GMD_BATCH_NOT_FOUND');
1930 RAISE FND_API.G_EXC_ERROR;
1931 END IF;
1932 CLOSE c_batch;
1933
1934 --=========================================================================
1935 -- Check the entered values with the one retrieved by cursor c_batch :
1936 -- recipe_id, recipe_no, recipe_version
1937 -- formula_id, formula_no, formula_vers
1938 -- routing_id, routing_no, routing_vers
1939 --=========================================================================
1940 -- 1: recipe_id
1941 IF ( NVL(l_wip_vr.recipe_id, l_recipe_id) <> l_recipe_id)
1942 THEN
1943 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1944 'WHAT', 'Passed recipe_id doesn''t match the batch''s recipe_id.');
1945 RAISE FND_API.G_EXC_ERROR;
1946 END IF;
1947 -- 2: recipe_no
1948 IF ( NVL(l_wip_vr.recipe_no, l_recipe_no) <> l_recipe_no)
1949 THEN
1950 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1951 'WHAT', 'Passed recipe_no doesn''t match the batch''s recipe_no.');
1952 RAISE FND_API.G_EXC_ERROR;
1953 END IF;
1954 -- 3: recipe_version
1955 IF ( NVL(l_wip_vr.recipe_version, l_recipe_version) <> l_recipe_version)
1956 THEN
1957 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1958 'WHAT', 'Passed recipe_version doesn''t match the batch''s recipe_version.');
1959 RAISE FND_API.G_EXC_ERROR;
1960 END IF;
1961
1962 -- 4: formula_id
1963 IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
1964 THEN
1965 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1966 'WHAT', 'Passed formula_id doesn''t match the batch''s formula_id.');
1967 RAISE FND_API.G_EXC_ERROR;
1968 END IF;
1969 -- 5: formula_no
1970 IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
1971 THEN
1972 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1973 'WHAT', 'Passed formula_no doesn''t match the batch''s formula_no.');
1974 RAISE FND_API.G_EXC_ERROR;
1975 END IF;
1976 -- 6: formula_vers
1977 IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
1978 THEN
1979 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1980 'WHAT', 'Passed formula_vers doesn''t match the batch''s formula_vers.');
1981 RAISE FND_API.G_EXC_ERROR;
1982 END IF;
1983
1984 -- 7: routing_id
1985 IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
1986 THEN
1987 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1988 'WHAT', 'Passed routing_id doesn''t match the batch''s routing_id.');
1989 RAISE FND_API.G_EXC_ERROR;
1990 END IF;
1991 -- 8: routing_no
1992 IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
1993 THEN
1994 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
1995 'WHAT', 'Passed routing_no doesn''t match the batch''s routing_no.');
1996 RAISE FND_API.G_EXC_ERROR;
1997 END IF;
1998 -- 9: routing_vers
1999 IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2000 THEN
2001 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2002 'WHAT', 'Passed routing_vers doesn''t match the batch''s routing_vers.');
2003 RAISE FND_API.G_EXC_ERROR;
2004 END IF;
2005
2006 -- At this stage, either l_wip_vr.recipe.... are NULL
2007 -- or they are equal to the local variables. I re-populate the fields (when they are NULL)
2008 l_wip_vr.recipe_id := l_recipe_id;
2009 l_wip_vr.recipe_no := l_recipe_no;
2010 l_wip_vr.recipe_version := l_recipe_version;
2011 l_wip_vr.formula_id := l_formula_id;
2012 l_wip_vr.formula_no := l_formula_no;
2013 l_wip_vr.formula_vers := l_formula_vers;
2014 l_wip_vr.routing_id := l_routing_id;
2015 l_wip_vr.routing_no := l_routing_no;
2016 l_wip_vr.routing_vers := l_routing_vers;
2017 ELSE
2018 -- In this part, batch_id is NULL...
2019
2020 --=========================================================================
2021 -- Recipe is valid
2022 -- If recipe_id NOT NULL, then recipe_no AND recipe_version populated
2023 -- And formula and routing (bug 2733426)
2024 -- If recipe_no NOT NULL, and recipe_version NOT NULL, then recipe_id populated
2025 -- If recipe_no NOT NULL, and recipe_version NULL, then nothing else populated
2026 --=========================================================================
2027 IF (l_wip_vr.recipe_id IS NOT NULL)
2028 THEN
2029 OPEN c_recipe_id;
2030 FETCH c_recipe_id
2031 INTO l_recipe_no, l_recipe_version
2032 , l_formula_id, l_formula_no, l_formula_vers
2033 , l_routing_id, l_routing_no, l_routing_vers;
2034 IF (c_recipe_id%NOTFOUND)
2035 THEN
2036 CLOSE c_recipe_id;
2037 GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2038 RAISE FND_API.G_EXC_ERROR;
2039 END IF;
2040 CLOSE c_recipe_id;
2041
2042 -- 4: formula_id
2043 IF ( NVL(l_wip_vr.formula_id, l_formula_id) <> l_formula_id)
2044 THEN
2045 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2046 'WHAT', 'Passed formula_id doesn''t match the batch''s formula_id.');
2047 RAISE FND_API.G_EXC_ERROR;
2048 END IF;
2049 -- 5: formula_no
2050 IF ( NVL(l_wip_vr.formula_no, l_formula_no) <> l_formula_no)
2051 THEN
2052 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2053 'WHAT', 'Passed formula_no doesn''t match the batch''s formula_no.');
2054 RAISE FND_API.G_EXC_ERROR;
2055 END IF;
2056 -- 6: formula_vers
2057 IF ( NVL(l_wip_vr.formula_vers, l_formula_vers) <> l_formula_vers)
2058 THEN
2059 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2060 'WHAT', 'Passed formula_vers doesn''t match the batch''s formula_vers.');
2061 RAISE FND_API.G_EXC_ERROR;
2062 END IF;
2063
2064 -- 7: routing_id
2065 IF ( NVL(l_wip_vr.routing_id, l_routing_id) <> l_routing_id)
2066 THEN
2067 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2068 'WHAT', 'Passed routing_id doesn''t match the batch''s routing_id.');
2069 RAISE FND_API.G_EXC_ERROR;
2070 END IF;
2071 -- 8: routing_no
2072 IF ( NVL(l_wip_vr.routing_no, l_routing_no) <> l_routing_no)
2073 THEN
2074 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2075 'WHAT', 'Passed routing_no doesn''t match the batch''s routing_no.');
2076 RAISE FND_API.G_EXC_ERROR;
2077 END IF;
2078 -- 9: routing_vers
2079 IF ( NVL(l_wip_vr.routing_vers, l_routing_vers) <> l_routing_vers)
2080 THEN
2081 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2082 'WHAT', 'Passed routing_vers doesn''t match the batch''s routing_vers.');
2083 RAISE FND_API.G_EXC_ERROR;
2084 END IF;
2085
2086 -- At this stage, either l_wip_vr.formula/routing.... are NULL
2087 -- or they are equal to the local variables.
2088 -- Populated the defaults, ignoring the passed values :
2089 l_wip_vr.recipe_no := l_recipe_no;
2090 l_wip_vr.recipe_version := l_recipe_version;
2091 l_wip_vr.formula_id := l_formula_id;
2092 l_wip_vr.formula_no := l_formula_no;
2093 l_wip_vr.formula_vers := l_formula_vers;
2094 l_wip_vr.routing_id := l_routing_id;
2095 l_wip_vr.routing_no := l_routing_no;
2096 l_wip_vr.routing_vers := l_routing_vers;
2097
2098 ELSIF (l_wip_vr.recipe_no IS NOT NULL)
2099 THEN
2100
2101 OPEN c_recipe_no;
2102 FETCH c_recipe_no
2103 INTO l_recipe_id, l_recipe_version;
2104 IF (c_recipe_no%NOTFOUND)
2105 THEN
2106 CLOSE c_recipe_no;
2107 GMD_API_PUB.Log_Message('GMD_RECIPE_NOT_FOUND');
2108 RAISE FND_API.G_EXC_ERROR;
2109 END IF;
2110 CLOSE c_recipe_no;
2111
2112 -- Populated the defaults :
2113 IF (l_wip_vr.recipe_version IS NOT NULL)
2114 THEN
2115 -- In that case : recipe_no, and recipe_version were given,
2116 -- So, I populate recipe_id
2117 l_wip_vr.recipe_id := l_recipe_id;
2118 END IF;
2119 END IF;
2120
2121 --=========================================================================
2122 -- Formula is valid
2123 -- If formula_id NOT NULL, then formula_no AND formula_vers populated
2124 -- If formula_no NOT NULL, and formula_vers NOT NULL, then formula_id populated
2125 -- If formula_no NOT NULL, and formula_vers NULL, then nothing else populated
2126 --=========================================================================
2127 IF (l_wip_vr.formula_id IS NOT NULL)
2128 THEN
2129 OPEN c_formula_id;
2130 FETCH c_formula_id
2131 INTO l_formula_no, l_formula_vers;
2132
2133 IF (c_formula_id%NOTFOUND)
2134 THEN
2135 CLOSE c_formula_id;
2136 GMD_API_PUB.Log_Message('GMD_FORMULA_NOT_FOUND');
2137 RAISE FND_API.G_EXC_ERROR;
2138 END IF;
2139 CLOSE c_formula_id;
2140
2141 -- Populated the defaults, ignoring the passed values :
2142 l_wip_vr.formula_no := l_formula_no;
2143 l_wip_vr.formula_vers := l_formula_vers;
2144
2145 ELSIF (l_wip_vr.formula_no IS NOT NULL)
2146 THEN
2147
2148 OPEN c_formula_no;
2149 FETCH c_formula_no
2150 INTO l_formula_id, l_formula_vers;
2151
2152 IF (c_formula_no%NOTFOUND)
2153 THEN
2154 CLOSE c_formula_no;
2155 GMD_API_PUB.Log_Message('GMD_FORMULA_NOT_FOUND');
2156 RAISE FND_API.G_EXC_ERROR;
2157 END IF;
2158 CLOSE c_formula_no;
2159
2160 -- Populated the defaults :
2161 IF (l_wip_vr.formula_vers IS NOT NULL)
2162 THEN
2163 -- In that case : formula_no, and formula_vers were given,
2164 -- So, I populate formula_id
2165 l_wip_vr.formula_id := l_formula_id;
2166 END IF;
2167 END IF;
2168
2169 --=========================================================================
2170 -- Routing is valid
2171 -- If routing_id NOT NULL, then routing_no AND routing_version populated
2172 -- If routing_no NOT NULL, and routing_vers NOT NULL, then routing_id populated
2173 -- If routing_no NOT NULL, and routing_vers NULL, then nothing else populated
2174 --=========================================================================
2175 IF (l_wip_vr.routing_id IS NOT NULL)
2176 THEN
2177 OPEN c_routing_id;
2178 FETCH c_routing_id
2179 INTO l_routing_no, l_routing_vers;
2180
2181 IF (c_routing_id%NOTFOUND)
2182 THEN
2183 CLOSE c_routing_id;
2184 GMD_API_PUB.Log_Message('GMD_ROUTING_NOT_FOUND');
2185 RAISE FND_API.G_EXC_ERROR;
2186 END IF;
2187 CLOSE c_routing_id;
2188
2189 -- Populated the defaults, ignoring the passed values :
2190 l_wip_vr.routing_no := l_routing_no;
2191 l_wip_vr.routing_vers := l_routing_vers;
2192
2193 ELSIF (l_wip_vr.routing_no IS NOT NULL)
2194 THEN
2195
2196 OPEN c_routing_no;
2197 FETCH c_routing_no
2198 INTO l_routing_id, l_routing_vers;
2199 IF (c_routing_no%NOTFOUND)
2200 THEN
2201 CLOSE c_routing_no;
2202 GMD_API_PUB.Log_Message('GMD_ROUTING_NOT_FOUND');
2203 RAISE FND_API.G_EXC_ERROR;
2204 END IF;
2205 CLOSE c_routing_no;
2206
2207 -- Populated the defaults :
2208 IF (l_wip_vr.routing_vers IS NOT NULL)
2209 THEN
2210 -- In that case : routing_no, and routing_vers were given,
2211 -- So, I populate routing_id
2212 l_wip_vr.routing_id := l_routing_id;
2213 END IF;
2214 END IF;
2215 END IF; -- batch_id NULL
2216
2217 --=========================================================================
2218 -- Formula Line is valid
2219 -- If formulaline_id is NOT NULL, then formula_id must be NOT NULL
2220 --=========================================================================
2221 IF (l_wip_vr.formulaline_id IS NOT NULL)
2222 AND (l_wip_vr.material_detail_id IS NULL)
2223 THEN
2224 IF (l_wip_vr.formula_id IS NULL)
2225 THEN
2226 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2227 'WHAT', 'Formula id must be NOT NULL');
2228 RAISE FND_API.G_EXC_ERROR;
2229 END IF;
2230
2231 OPEN c_formulaline_id;
2232 FETCH c_formulaline_id
2233 INTO dummy;
2234 IF (c_formulaline_id%NOTFOUND)
2235 THEN
2236 CLOSE c_formulaline_id;
2237 GMD_API_PUB.Log_Message('GMD_FORMULA_LINE_NOT_FOUND');
2238 RAISE FND_API.G_EXC_ERROR;
2239 END IF;
2240 CLOSE c_formulaline_id;
2241 END IF;
2242
2243
2244 --=========================================================================
2245 -- Batch Line (material detail id) is valid
2246 -- If material_detail_id is NOT NULL, then batch_id must be NOT NULL
2247 --=========================================================================
2248 IF (l_wip_vr.material_detail_id IS NOT NULL)
2249 THEN
2250 IF (l_wip_vr.batch_id IS NULL)
2251 THEN
2252 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2253 'WHAT', 'Batch id must be NOT NULL');
2254 RAISE FND_API.G_EXC_ERROR;
2255 END IF;
2256
2257 OPEN c_material_detail_id;
2258 FETCH c_material_detail_id
2259 INTO dummy;
2260 IF (c_material_detail_id%NOTFOUND)
2261 THEN
2262 CLOSE c_material_detail_id;
2263 GMD_API_PUB.Log_Message('GMD_MATERIAL_DTL_NOT_FOUND');
2264 RAISE FND_API.G_EXC_ERROR;
2265 END IF;
2266 CLOSE c_material_detail_id;
2267 END IF;
2268
2269
2270 --=========================================================================
2271 -- Step is valid
2272 -- A step can be either a batch step or a routing step
2273 -- If step_no NULL and step_id NOT NULL, then populate step_no
2274 -- If step_no NOT NULL and step_id NULL, then error.
2275 --=========================================================================
2276 IF (l_wip_vr.step_id IS NULL AND l_wip_vr.step_no IS NOT NULL)
2277 THEN
2278 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2279 'WHAT', 'Step id must be populated');
2280 RAISE FND_API.G_EXC_ERROR;
2281 END IF;
2282
2283 IF (l_wip_vr.batch_id IS NOT NULL AND l_wip_vr.step_id IS NOT NULL)
2284 THEN
2285 -- Step No is from Batch
2286 OPEN c_batchstep;
2287 FETCH c_batchstep
2288 INTO l_step_no;
2289 IF (c_batchstep%NOTFOUND)
2290 THEN
2291 CLOSE c_batchstep;
2292 GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2293 RAISE FND_API.G_EXC_ERROR;
2294 END IF;
2295 CLOSE c_batchstep;
2296
2297 -- Populated the defaults, ignoring the passed values :
2298 l_wip_vr.step_no := l_step_no;
2299
2300 ELSIF (l_wip_vr.routing_id IS NOT NULL AND l_wip_vr.step_id IS NOT NULL)
2301 THEN
2302 -- Step No is from Routing
2303 OPEN c_routingstep;
2304 FETCH c_routingstep
2305 INTO l_step_no;
2306 IF (c_routingstep%NOTFOUND)
2307 THEN
2308 CLOSE c_routingstep;
2309 GMD_API_PUB.Log_Message('GMD_ROUTING_STEP_NOT_FOUND');
2310 RAISE FND_API.G_EXC_ERROR;
2311 END IF;
2312 CLOSE c_routingstep;
2313
2314 -- Populated the defaults, ignoring the passed values :
2315 l_wip_vr.step_no := l_step_no;
2316
2317 END IF;
2318
2319 -- Operation is valid (check only if step is not specified, because
2320 -- otherwise it will default from the step chosen.)
2321 IF (l_wip_vr.step_id IS NULL AND l_wip_vr.oprn_id IS NOT NULL)
2322 THEN
2323 OPEN c_oprn;
2324 FETCH c_oprn
2325 INTO l_oprn_no, l_oprn_vers;
2326 IF (c_oprn%NOTFOUND)
2327 THEN
2328 CLOSE c_oprn;
2329 GMD_API_PUB.Log_Message('GMD_BATCH_STEP_NOT_FOUND');
2330 RAISE FND_API.G_EXC_ERROR;
2331 END IF;
2332 CLOSE c_oprn;
2333
2334 -- Populated the defaults, ignoring the passed values :
2335 l_wip_vr.oprn_no := l_oprn_no;
2336 l_wip_vr.oprn_vers := l_oprn_vers;
2337
2338 END IF;
2339
2340
2341 -- All Systems Go...
2342 x_wip_vr := l_wip_vr;
2343
2344 EXCEPTION
2345 WHEN FND_API.G_EXC_ERROR THEN
2346 x_return_status := FND_API.G_RET_STS_ERROR ;
2347 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349 WHEN OTHERS THEN
2350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2351
2352 END check_for_null_and_fks_in_wvr;
2353
2354
2355
2356
2357 --Start of comments
2358 --+========================================================================+
2359 --| API Name : wip_vr_exist |
2360 --| TYPE : Group |
2361 --| Notes : This function returns TRUE if the WIP VR already |
2362 --| exists for the spcified parameter in the database, FALSE |
2363 --| otherwise. |
2364 --| |
2365 --| HISTORY |
2366 --| Chetan Nagar 26-Jul-2002 Created. |
2367 --| Olivier Daboval 17-OCT-2002 bug 2630007, added spec_name in |
2368 --| the VR_EXIST message |
2369 --| Olivier Daboval 14-NOV-2002 Added NVL(spec_vr_id, -1) because |
2370 --| the API passes a NULL spec_vr_id |
2371 --| in insert mode. |
2372 --| Brenda Stone 20-NOV-2003 Bug 3090290; allow duplicate spec vr
2373 --| with "OBSOLUTE" status |
2374 --| Bug 2984784; add Version to msg for|
2375 --| existing spec vr. |
2376 --| |
2377 --| Saikiran 12-Apr-2005 Convergence Changes |
2378 --| Feinstein 18-Oct-2005 Added material detail id to samples|
2379 --| Plowe 04-Apr-2006 Bug 5117733 - added item revision to match
2380 --+========================================================================+
2381 -- End of comments
2382
2383 FUNCTION wip_vr_exist(p_wip_vr GMD_WIP_SPEC_VRS%ROWTYPE,
2384 p_spec GMD_SPECIFICATIONS%ROWTYPE)
2385 RETURN BOOLEAN IS
2386
2387 -- added material detail to cursor
2388 CURSOR c_wip_vr IS
2389 SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
2390 FROM gmd_specifications_b s,
2391 gmd_wip_spec_vrs vr
2392 WHERE s.spec_id = vr.spec_id
2393 AND s.owner_organization_id = p_spec.owner_organization_id
2394 AND s.inventory_item_id = p_spec.inventory_item_id
2395 AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
2396 (s.revision = p_spec.revision )
2397 )
2398 AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
2399 (s.grade_code = p_spec.grade_code)
2400 )
2401 AND ((vr.organization_id is NULL AND p_wip_vr.organization_id is NULL) OR
2402 (vr.organization_id = p_wip_vr.organization_id)
2403 )
2404 AND ((vr.batch_id is NULL AND p_wip_vr.batch_id is NULL) OR
2405 (vr.batch_id = p_wip_vr.batch_id)
2406 )
2407 AND ((vr.recipe_id is NULL AND p_wip_vr.recipe_id is NULL) OR
2408 (vr.recipe_id = p_wip_vr.recipe_id)
2409 )
2410 AND ((vr.recipe_no is NULL AND p_wip_vr.recipe_no is NULL) OR
2411 (vr.recipe_no = p_wip_vr.recipe_no)
2412 )
2413 AND ((vr.formula_id is NULL AND p_wip_vr.formula_id is NULL) OR
2414 (vr.formula_id = p_wip_vr.formula_id)
2415 )
2416 AND ((vr.formula_no is NULL AND p_wip_vr.formula_no is NULL) OR
2417 (vr.formula_no = p_wip_vr.formula_no)
2418 )
2419 AND ((vr.formulaline_id is NULL AND p_wip_vr.formulaline_id is NULL) OR
2420 (vr.formulaline_id = p_wip_vr.formulaline_id) OR
2421 (vr.batch_id IS NOT NULL) -- added for new Material detail field
2422 )
2423 AND ((vr.material_detail_id is NULL AND p_wip_vr.material_detail_id is NULL) OR
2424 (vr.material_detail_id = p_wip_vr.material_detail_id)
2425 )
2426 AND ((vr.routing_id is NULL AND p_wip_vr.routing_id is NULL) OR
2427 (vr.routing_id = p_wip_vr.routing_id)
2428 )
2429 AND ((vr.routing_no is NULL AND p_wip_vr.routing_no is NULL) OR
2430 (vr.routing_no = p_wip_vr.routing_no)
2431 )
2432 AND ((vr.step_id is NULL AND p_wip_vr.step_id is NULL) OR
2433 (vr.step_id = p_wip_vr.step_id)
2434 )
2435 AND ((vr.oprn_id is NULL AND p_wip_vr.oprn_id is NULL) OR
2436 (vr.oprn_id = p_wip_vr.oprn_id)
2437 )
2438 AND ((vr.oprn_no is NULL AND p_wip_vr.oprn_no is NULL) OR
2439 (vr.oprn_no = p_wip_vr.oprn_no)
2440 )
2441 AND ((vr.charge is NULL AND p_wip_vr.charge is NULL) OR
2442 (vr.charge = p_wip_vr.charge)
2443 )
2444 AND ((vr.end_date is NULL AND (p_wip_vr.end_date IS NULL OR
2445 p_wip_vr.end_date >= vr.start_date)) OR
2446 (p_wip_vr.end_date IS NULL AND
2447 p_wip_vr.start_date <= nvl(vr.end_date, p_wip_vr.start_date)) OR
2448 (p_wip_vr.start_date <= vr.end_date AND p_wip_vr.end_date >= vr.start_date)
2449 )
2450 AND (floor(vr.spec_vr_status/100) = floor(p_wip_vr.spec_vr_status/100) AND
2451 /* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
2452 p_wip_vr.spec_vr_status <> 1000 )
2453
2454 /* Bug 3090290 - Here's the problem - Both spec vr's have the same status 1000 */
2455 /* obsolete */
2456 AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
2457 WHERE status_type = 800)
2458 AND vr.delete_mark = 0
2459 AND s.delete_mark = 0
2460 AND vr.spec_vr_id <> NVL(p_wip_vr.spec_vr_id, -1)
2461 ;
2462
2463 dummy PLS_INTEGER;
2464 specname VARCHAR2(80);
2465 specvers NUMBER;
2466
2467 BEGIN
2468
2469 OPEN c_wip_vr;
2470 FETCH c_wip_vr INTO dummy, specname, specvers;
2471 IF c_wip_vr%FOUND THEN
2472 CLOSE c_wip_vr;
2473 FND_MESSAGE.SET_NAME('GMD', 'GMD_WIP_VR_EXIST');
2474 FND_MESSAGE.SET_TOKEN('spec', specname);
2475 FND_MESSAGE.SET_TOKEN('vers', specvers);
2476 FND_MSG_PUB.ADD;
2477 RETURN TRUE;
2478 ELSE
2479 CLOSE c_wip_vr;
2480 RETURN FALSE;
2481 END IF;
2482
2483 EXCEPTION
2484 -- Though there is no reason the program can reach
2485 -- here, this is coded just for the reasons we can
2486 -- not think of!
2487 WHEN OTHERS THEN
2488 FND_MESSAGE.SET_NAME('GMD', 'GMD_API_ERROR');
2489 FND_MESSAGE.SET_TOKEN('PACKAGE', 'GMD_SPEC_VRS_GRP.WIP_VR_EXIST' );
2490 FND_MESSAGE.SET_TOKEN('ERROR', SUBSTR(SQLERRM,1,200));
2491 RETURN TRUE;
2492
2493 END wip_vr_exist;
2494
2495
2496
2497 --Start of comments
2498 --+========================================================================+
2499 --| API Name : validate_cust_vr |
2500 --| TYPE : Group |
2501 --| Notes : This procedure validates all the fields of |
2502 --| Customer validity rule record. This procedure can be |
2503 --| called from FORM or API and the caller need |
2504 --| to specify this in p_called_from parameter |
2505 --| while calling this procedure. Based on where |
2506 --| it is called from certain validations will |
2507 --| either be performed or skipped. |
2508 --| |
2509 --| If everything is fine then OUT parameter |
2510 --| x_return_status is set to 'S' else appropriate |
2511 --| error message is put on the stack and error |
2512 --| is returned. |
2513 --| |
2514 --| HISTORY |
2515 --| Chetan Nagar 26-Jul-2002 Created. |
2516 --| |
2517 --+========================================================================+
2518 -- End of comments
2519
2520 PROCEDURE validate_cust_vr
2521 (
2522 p_cust_vr IN GMD_CUSTOMER_SPEC_VRS%ROWTYPE
2523 , p_called_from IN VARCHAR2
2524 , p_operation IN VARCHAR2
2525 , x_return_status OUT NOCOPY VARCHAR2
2526 ) IS
2527
2528 -- Local Variables
2529 dummy NUMBER;
2530 l_return_status VARCHAR2(1);
2531
2532 l_spec GMD_SPECIFICATIONS%ROWTYPE;
2533 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
2534 l_item_mst MTL_SYSTEM_ITEMS_B%ROWTYPE;
2535 l_sampling_plan GMD_SAMPLING_PLANS%ROWTYPE;
2536 l_sampling_plan_out GMD_SAMPLING_PLANS%ROWTYPE;
2537 l_inventory_item_id NUMBER;
2538 l_organization_id NUMBER;
2539 l_uom_rate NUMBER;
2540
2541 -- Exceptions
2542 e_spec_fetch_error EXCEPTION;
2543 e_smpl_plan_fetch_error EXCEPTION;
2544 e_error_fetch_item EXCEPTION;
2545
2546 BEGIN
2547 -- Initialize API return status to success
2548 x_return_status := FND_API.G_RET_STS_SUCCESS;
2549
2550 IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
2551 -- Invalid Operation
2552 GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
2553 RAISE FND_API.G_EXC_ERROR;
2554 END IF;
2555
2556 -- Verify that the specification exists.
2557 l_spec.spec_id := p_cust_vr.spec_id;
2558 IF NOT (GMD_Specifications_PVT.Fetch_Row(
2559 p_specifications => l_spec,
2560 x_specifications => l_spec_out)
2561 ) THEN
2562 -- Fetch Error
2563 GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
2564 RAISE e_spec_fetch_error;
2565 END IF;
2566
2567 l_spec := l_spec_out ;
2568
2569 -- Verify that the Sampling Plan exists.
2570 --odab added this test.
2571 IF (p_cust_vr.sampling_plan_id IS NOT NULL)
2572 THEN
2573 l_sampling_plan.sampling_plan_id := p_cust_vr.sampling_plan_id;
2574 IF NOT (GMD_Sampling_Plans_PVT.Fetch_Row(
2575 p_sampling_plan => l_sampling_plan,
2576 x_sampling_plan => l_sampling_plan_out)
2577 ) THEN
2578 -- Fetch Error
2579 GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
2580 RAISE e_smpl_plan_fetch_error;
2581 END IF;
2582 l_sampling_plan := l_sampling_plan_out;
2583 END IF;
2584
2585 IF (p_called_from = 'API') THEN
2586 -- Check for NULLs and Valid Foreign Keys in the input parameter
2587 GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_cvr
2588 (
2589 p_cust_vr => p_cust_vr
2590 , p_spec => l_spec
2591 , x_return_status => l_return_status
2592 );
2593 -- No need if called from FORM since it is already
2594 -- done in the form
2595
2596 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2597 -- Message is alrady logged by check_for_null procedure
2598 RAISE FND_API.G_EXC_ERROR;
2599 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2600 -- Message is alrady logged by check_for_null procedure
2601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603 END IF;
2604
2605 -- First Verify that the SAME VR does not exists
2606 IF (p_operation IN ('INSERT', 'UPDATE')
2607 AND cust_vr_exist(p_cust_vr, l_spec))
2608 THEN
2609 -- Disaster, Trying to insert duplicate
2610 -- bug 2630007, odaboval put the message in function cust_vr_exist.
2611 -- GMD_API_PUB.Log_Message('GMD_CUST_VR_EXIST');
2612 RAISE FND_API.G_EXC_ERROR;
2613 END IF;
2614
2615 -- Sample Quantity UOM must be convertible to Item's UOM
2616 BEGIN
2617 SELECT inventory_item_id INTO l_inventory_item_id FROM
2618 gmd_specifications WHERE spec_id = p_cust_vr.spec_id;
2619 SELECT owner_organization_id INTO l_organization_id FROM
2620 gmd_specifications WHERE spec_id = p_cust_vr.spec_id;
2621 SELECT * INTO l_item_mst
2622 FROM mtl_system_items_b
2623 WHERE inventory_item_id = l_inventory_item_id
2624 AND organization_id = l_organization_id;
2625 EXCEPTION
2626 WHEN OTHERS THEN
2627 GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
2628 RAISE e_error_fetch_item;
2629 END;
2630
2631 --odab added this test.
2632 IF (p_cust_vr.sampling_plan_id IS NOT NULL)
2633 THEN
2634 inv_convert.inv_um_conversion (
2635 from_unit => l_sampling_plan.sample_qty_uom,
2636 to_unit => l_item_mst.primary_uom_code,
2637 item_id => l_inventory_item_id,
2638 lot_number => NULL,
2639 organization_id => l_organization_id ,
2640 uom_rate => l_uom_rate );
2641
2642 IF l_uom_rate = -99999 THEN
2643 GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
2644 RAISE FND_API.G_EXC_ERROR;
2645 END IF;
2646 END IF;
2647
2648 -- No need to check the return status because above procedure
2649 -- logs appropriate message on the stack and raises an exception.
2650
2651 -- The Start Date must be less than the End Date
2652 If ( p_cust_vr.end_date IS NOT NULL AND
2653 p_cust_vr.start_date > p_cust_vr.end_date) THEN
2654 GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
2655 RAISE FND_API.G_EXC_ERROR;
2656 END IF;
2657
2658 -- Spec VR Status Must be less than Spec Status upto Appoved Stages
2659 IF (floor(l_spec.spec_status/100) <= 7 AND
2660 floor(p_cust_vr.spec_vr_status/100) <= 7 AND
2661 p_cust_vr.spec_vr_status > l_spec.spec_status) THEN
2662 GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
2663 RAISE FND_API.G_EXC_ERROR;
2664 END IF;
2665
2666 -- All systems GO...
2667
2668 EXCEPTION
2669 WHEN FND_API.G_EXC_ERROR OR
2670 e_spec_fetch_error OR
2671 e_smpl_plan_fetch_error OR
2672 e_error_fetch_item
2673 THEN
2674 x_return_status := FND_API.G_RET_STS_ERROR ;
2675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2677 WHEN OTHERS THEN
2678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2679
2680
2681 END validate_cust_vr;
2682
2683
2684
2685
2686 --Start of comments
2687 --+========================================================================+
2688 --| API Name : check_for_null_and_fks_in_cvr |
2689 --| TYPE : Group |
2690 --| Notes : This procedure checks for NULL and Foreign Key |
2691 --| constraints for the required filed in the Spec |
2692 --| Customer VR record. |
2693 --| |
2694 --| If everything is fine then 'S' is returned in the |
2695 --| parameter - x_return_status otherwise error message |
2696 --| is logged and error status - E or U returned |
2697 --| |
2698 --| HISTORY |
2699 --| Chetan Nagar 26-Jul-2002 Created. |
2700 --| |
2701 --| SaiKiran 04-MAY-2004 Enhancement #3476560. added |
2702 --| 'delayed_lot_entry' to the call to 'check_vr_controls'|
2703 --| procedure. |
2704 --| |
2705 --| Saikiran 11-Apr-2005 Convergence Changes |
2706 --| PLOWE 07-JUN-2006 -- bug 5223014 rework |
2707 --| replace cursor with function as check was not working as designed |
2708 --| bug 5223014 rework in proc check_for_null_and_fks_in_cvr |
2709 --|========================================================================+
2710 -- End of comments
2711
2712 PROCEDURE check_for_null_and_fks_in_cvr
2713 (
2714 p_cust_vr IN gmd_customer_spec_vrs%ROWTYPE
2715 , p_spec IN gmd_specifications%ROWTYPE
2716 , x_return_status OUT NOCOPY VARCHAR2
2717 )
2718 IS
2719
2720 CURSOR c_cust IS
2721 SELECT 1
2722 FROM hr_operating_units ou
2723 , hz_cust_acct_sites_all casa
2724 , hz_cust_site_uses_all csua
2725 , hz_parties hzp
2726 , hz_cust_accounts_all hzca
2727 WHERE ou.organization_id = csua.org_id
2728 AND casa.cust_acct_site_id = csua.cust_acct_site_id
2729 AND casa.cust_account_id = hzca.cust_account_id
2730 AND casa.org_id = csua.org_id
2731 AND hzp.party_id = hzca.party_id
2732 AND NVL( p_cust_vr.org_id, csua.org_id) = csua.org_id
2733 AND hzca.cust_account_id = p_cust_vr.cust_id;
2734
2735 -- bug 4924483 sql id 14687576 (MJC) don't use view
2736 CURSOR c_org IS
2737 /*SELECT 1
2738 FROM hr_operating_units
2739 WHERE organization_id = p_cust_vr.org_id; */
2740 -- bug 5223014 sql id 17532992 (MJC) don't need 2nd HR_ORGANIZATION_INFORMATION O3 for identification
2741 -- (takes out MJC)
2742 SELECT 1
2743 FROM HR_ALL_ORGANIZATION_UNITS O,
2744 HR_ORGANIZATION_INFORMATION O2
2745 --HR_ORGANIZATION_INFORMATION O3
2746 WHERE o.organization_id = p_cust_vr.org_id
2747 and O2.ORGANIZATION_ID = o.organization_id
2748 AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
2749 AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
2750 AND O2.ORG_INFORMATION2 = 'Y';
2751 --and O3.ORGANIZATION_ID = O2.ORGANIZATION_ID
2752 --AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information';
2753
2754 -- pal new
2755 CURSOR c_orgn_check ( p_organization_id NUMBER) IS
2756 SELECT 1
2757 FROM GMD_ORG_ACCESS_VW;
2758
2759 CURSOR c_ship_to IS
2760 SELECT 1
2761 FROM hz_cust_acct_sites_all casa
2762 , hz_cust_site_uses_all csua
2763 , hz_cust_accounts_all caa
2764 WHERE casa.cust_acct_site_id = csua.cust_acct_site_id
2765 AND casa.org_id = csua.org_id
2766 AND casa.cust_account_id = caa.cust_account_id
2767 AND csua.site_use_code = 'SHIP_TO'
2768 AND NVL( p_cust_vr.org_id, csua.org_id) = csua.org_id
2769 AND caa.cust_account_id = p_cust_vr.cust_id
2770 AND csua.site_use_id = p_cust_vr.ship_to_site_id;
2771
2772 CURSOR c_order IS
2773 SELECT 1
2774 FROM oe_order_headers_all oha
2775 , oe_order_lines_all oola
2776 , oe_transaction_types_tl ttt
2777 WHERE oola.header_id = oha.header_id
2778 AND oola.inventory_item_id = p_spec.inventory_item_id
2779 AND oha.order_type_id = ttt.transaction_type_id
2780 AND NVL( p_cust_vr.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
2781 AND NVL( p_cust_vr.org_id, oha.org_id) = oha.org_id
2782 AND NVL( p_cust_vr.cust_id, oha.sold_to_org_id) = oha.sold_to_org_id
2783 AND oha.header_id = p_cust_vr.order_id
2784 AND oha.cancelled_flag <> 'Y'
2785 AND ttt.language = USERENV('LANG');
2786
2787
2788 CURSOR c_order_line IS
2789 SELECT 1
2790 FROM oe_order_lines_all oola
2791 WHERE oola.header_id = p_cust_vr.order_id
2792 AND NVL( p_cust_vr.ship_to_site_id, oola.ship_to_org_id) = oola.ship_to_org_id
2793 AND oola.inventory_item_id = p_spec.inventory_item_id
2794 AND oola.header_id = p_cust_vr.order_id
2795 AND (oola.line_number + (oola.shipment_number / 10)) = p_cust_vr.order_line
2796 AND oola.line_id = p_cust_vr.order_line_id;
2797
2798 -- Local Variables
2799 dummy PLS_INTEGER;
2800 l_lot_ctl NUMBER;
2801 l_sample_display GMD_SAMPLES_GRP.sample_display_rec;
2802
2803 BEGIN
2804
2805 --=========================================================================
2806 -- WHO section :
2807 --=========================================================================
2808 check_who( p_user_id => p_cust_vr.created_by);
2809 check_who( p_user_id => p_cust_vr.last_updated_by);
2810 IF (p_cust_vr.creation_date IS NULL
2811 OR p_cust_vr.last_update_date IS NULL)
2812 THEN
2813 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2814 'WHAT', 'the dates must not be NULL');
2815 RAISE FND_API.G_EXC_ERROR;
2816 END IF;
2817
2818 --=========================================================================
2819 -- Organization : must belong to the USER
2820 --=========================================================================
2821
2822
2823 IF (p_cust_vr.organization_id IS NOT NULL)
2824 -- Check that Organization is a valid one
2825 THEN
2826 /*
2827 OPEN c_orgn( p_cust_vr.organization_id);
2828 FETCH c_orgn INTO dummy;
2829 IF (c_orgn%NOTFOUND)
2830 THEN
2831 CLOSE c_orgn;
2832 GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
2833 'ORGN_ID', p_cust_vr.organization_id);
2834 RAISE FND_API.G_EXC_ERROR;
2835 END IF;
2836 CLOSE c_orgn;*/
2837
2838 -- replace above with function as above check was not working as designed - bug 5223014 rework
2839
2840 IF NOT (gmd_api_grp.OrgnAccessible(p_cust_vr.organization_id)) THEN
2841 RAISE FND_API.G_EXC_ERROR;
2842 END IF;
2843
2844 END IF;
2845
2846 --=========================================================================
2847 -- Get Item Controls
2848 --=========================================================================
2849 l_sample_display.organization_id := p_cust_vr.organization_id;
2850 l_sample_display.inventory_item_id := p_spec.inventory_item_id;
2851 GMD_SAMPLES_GRP.get_item_values (p_sample_display => l_sample_display);
2852 l_lot_ctl := l_sample_display.lot_control_code;
2853
2854
2855 --=========================================================================
2856 -- lot_optional_on_sample :
2857 -- When this field is NOT NULL, all the following fields must be null :
2858 -- sample_inv_trans_ind
2859 --=========================================================================
2860 --Enhancement #3476560. added 'delayed_lot_entry' to the call to 'check_vr_controls' procedure
2861 check_VR_Controls( p_VR_type => 'CUSTOMER'
2862 , p_lot_optional_on_sample => p_cust_vr.lot_optional_on_sample
2863 , p_delayed_lot_entry => NULL
2864 , p_sample_inv_trans_ind => p_cust_vr.sample_inv_trans_ind
2865 , p_lot_ctl => l_lot_ctl
2866 , p_status_ctl => NULL
2867 , p_control_lot_attrib_ind => NULL
2868 , p_in_spec_lot_status_id => NULL
2869 , p_out_of_spec_lot_status_id=> NULL
2870 , p_control_batch_step_ind => NULL);
2871
2872 --=========================================================================
2873 -- spec_vr_status :
2874 --=========================================================================
2875 OPEN c_status(p_cust_vr.spec_vr_status);
2876 FETCH c_status
2877 INTO dummy;
2878 IF (c_status%NOTFOUND)
2879 THEN
2880 CLOSE c_status;
2881 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
2882 'STATUS', p_cust_vr.spec_vr_status);
2883 RAISE FND_API.G_EXC_ERROR;
2884 END IF;
2885 CLOSE c_status;
2886
2887
2888 --=========================================================================
2889 -- start_date : This field is mandatory
2890 --=========================================================================
2891 IF (p_cust_vr.start_date IS NULL)
2892 THEN
2893 GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
2894 RAISE FND_API.G_EXC_ERROR;
2895 END IF;
2896
2897 --=========================================================================
2898 -- COA section :
2899 --=========================================================================
2900 check_COA( p_coa_type => p_cust_vr.coa_type
2901 , p_coa_at_ship_ind => p_cust_vr.coa_at_ship_ind
2902 , p_coa_at_invoice_ind => p_cust_vr.coa_at_invoice_ind
2903 , p_coa_req_from_supl_ind => p_cust_vr.coa_req_from_supl_ind);
2904
2905 --=========================================================================
2906 -- cust_id : This field is mandatory
2907 --=========================================================================
2908 IF (p_cust_vr.cust_id IS NULL)
2909 THEN
2910 GMD_API_PUB.Log_Message('GMD_CUSTOMER_REQD');
2911 RAISE FND_API.G_EXC_ERROR;
2912 ELSE
2913 OPEN c_cust;
2914 FETCH c_cust
2915 INTO dummy;
2916 IF (c_cust%NOTFOUND)
2917 THEN
2918 CLOSE c_cust;
2919 GMD_API_PUB.Log_Message('GMD_CUSTOMER_NOT_FOUND');
2920 RAISE FND_API.G_EXC_ERROR;
2921 END IF;
2922 CLOSE c_cust;
2923 END IF;
2924
2925 --=========================================================================
2926 -- Org ID
2927 --=========================================================================
2928 IF (p_cust_vr.org_id IS NOT NULL)
2929 THEN
2930 OPEN c_org;
2931 FETCH c_org
2932 INTO dummy;
2933 IF (c_org%NOTFOUND)
2934 THEN
2935 CLOSE c_org;
2936 GMD_API_PUB.Log_Message('GMD_ORG_NOT_FOUND');
2937 RAISE FND_API.G_EXC_ERROR;
2938 END IF;
2939 CLOSE c_org;
2940 END IF;
2941
2942 --=========================================================================
2943 -- Ship To
2944 --=========================================================================
2945 IF (p_cust_vr.ship_to_site_id IS NOT NULL)
2946 THEN
2947 IF (p_cust_vr.cust_id IS NULL)
2948 THEN
2949 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2950 'WHAT', 'the customer number must not be NULL');
2951 RAISE FND_API.G_EXC_ERROR;
2952 END IF;
2953
2954 OPEN c_ship_to;
2955 FETCH c_ship_to
2956 INTO dummy;
2957 IF (c_ship_to%NOTFOUND)
2958 THEN
2959 CLOSE c_ship_to;
2960 GMD_API_PUB.Log_Message('GMD_SHIP_TO_NOT_FOUND');
2961 RAISE FND_API.G_EXC_ERROR;
2962 END IF;
2963 CLOSE c_ship_to;
2964 END IF;
2965
2966 --=========================================================================
2967 -- Order ID
2968 --=========================================================================
2969 IF (p_cust_vr.order_id IS NOT NULL)
2970 THEN
2971 OPEN c_order;
2972 FETCH c_order
2973 INTO dummy;
2974 IF (c_order%NOTFOUND)
2975 THEN
2976 CLOSE c_order;
2977 GMD_API_PUB.Log_Message('GMD_ORDER_NOT_FOUND');
2978 RAISE FND_API.G_EXC_ERROR;
2979 END IF;
2980 CLOSE c_order;
2981 END IF;
2982
2983 --=========================================================================
2984 -- Order Line ID
2985 -- Both order_line AND order_line_id are mandatory
2986 -- Also order_id must be NOT NULL
2987 --=========================================================================
2988 IF (p_cust_vr.order_line_id IS NOT NULL
2989 OR p_cust_vr.order_line IS NOT NULL)
2990 THEN
2991 IF (p_cust_vr.order_id IS NULL)
2992 THEN
2993 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
2994 'WHAT', 'the order number must not be NULL');
2995 RAISE FND_API.G_EXC_ERROR;
2996 END IF;
2997 IF (p_cust_vr.order_line_id IS NULL
2998 OR p_cust_vr.order_line IS NULL)
2999 THEN
3000 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3001 'WHAT', 'the order line AND id must not be NULL');
3002 RAISE FND_API.G_EXC_ERROR;
3003 END IF;
3004
3005 OPEN c_order_line;
3006 FETCH c_order_line
3007 INTO dummy;
3008 IF (c_order_line%NOTFOUND)
3009 THEN
3010 CLOSE c_order_line;
3011 GMD_API_PUB.Log_Message('GMD_ORDER_LINE_NOT_FOUND');
3012 RAISE FND_API.G_EXC_ERROR;
3013 END IF;
3014 CLOSE c_order_line;
3015 END IF;
3016
3017 -- All Systems Go...
3018
3019 EXCEPTION
3020 WHEN FND_API.G_EXC_ERROR THEN
3021 x_return_status := FND_API.G_RET_STS_ERROR ;
3022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3024 WHEN OTHERS THEN
3025 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3026
3027 END check_for_null_and_fks_in_cvr;
3028
3029
3030
3031
3032 --Start of comments
3033 --+========================================================================+
3034 --| API Name : cust_vr_exist |
3035 --| TYPE : Group |
3036 --| Notes : This function returns TRUE if the customer VR already |
3037 --| exists for the spcified parameter in the database, FALSE |
3038 --| otherwise. |
3039 --| |
3040 --| HISTORY |
3041 --| Chetan Nagar 26-Jul-2002 Created. |
3042 --| Olivier Daboval 17-OCT-2002 bug 2630007, added spec_name in |
3043 --| the VR_EXIST message |
3044 --| Olivier Daboval 14-NOV-2002 Added NVL(spec_vr_id, -1) because |
3045 --| the API passes a NULL spec_vr_id |
3046 --| in insert mode. |
3047 --| Brenda Stone 20-NOV-2003 Bug 3090290; allow duplicate spec vr |
3048 --| with "OBSOLUTE" status |
3049 --| Bug 2984784; add Version to msg for |
3050 --| existing spec vr. |
3051 --| |
3052 --| Saikiran 12-Apr-2005 Convergence Changes |
3053 --| Plowe 04-Apr-2006 Bug 5117733 - added item revision to match
3054 --+========================================================================+
3055 -- End of comments
3056
3057 FUNCTION cust_vr_exist(p_cust_vr GMD_CUSTOMER_SPEC_VRS%ROWTYPE,
3058 p_spec GMD_SPECIFICATIONS%ROWTYPE)
3059 RETURN BOOLEAN IS
3060
3061 CURSOR c_cust_vr IS
3062 SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
3063 FROM gmd_specifications_b s, gmd_customer_spec_vrs vr
3064 WHERE s.spec_id = vr.spec_id
3065 AND s.owner_organization_id = p_spec.owner_organization_id
3066 AND s.inventory_item_id = p_spec.inventory_item_id
3067 AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
3068 (s.revision = p_spec.revision )
3069 )
3070 AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
3071 (s.grade_code = p_spec.grade_code)
3072 )
3073 AND ((vr.organization_id is NULL AND p_cust_vr.organization_id is NULL) OR
3074 (vr.organization_id = p_cust_vr.organization_id)
3075 )
3076 AND ((vr.cust_id is NULL AND p_cust_vr.cust_id is NULL) OR
3077 (vr.cust_id = p_cust_vr.cust_id)
3078 )
3079 AND ((vr.org_id is NULL AND p_cust_vr.org_id is NULL) OR
3080 (vr.org_id = p_cust_vr.org_id)
3081 )
3082 AND ((vr.order_id is NULL AND p_cust_vr.order_id is NULL) OR
3083 (vr.order_id = p_cust_vr.order_id)
3084 )
3085 AND ((vr.order_line is NULL AND p_cust_vr.order_line is NULL) OR
3086 (vr.order_line = p_cust_vr.order_line)
3087 )
3088 AND ((vr.order_line_id is NULL AND p_cust_vr.order_line_id is NULL) OR
3089 (vr.order_line_id = p_cust_vr.order_line_id)
3090 )
3091 AND ((vr.ship_to_site_id is NULL AND p_cust_vr.ship_to_site_id is NULL) OR
3092 (vr.ship_to_site_id = p_cust_vr.ship_to_site_id)
3093 )
3094 AND ((vr.end_date is NULL AND (p_cust_vr.end_date IS NULL OR
3095 p_cust_vr.end_date >= vr.start_date)) OR
3096 (p_cust_vr.end_date IS NULL AND
3097 p_cust_vr.start_date <= nvl(vr.end_date, p_cust_vr.start_date)) OR
3098 (p_cust_vr.start_date <= vr.end_date AND p_cust_vr.end_date >= vr.start_date)
3099 )
3100 AND ( floor(vr.spec_vr_status/100) = floor(p_cust_vr.spec_vr_status/100) AND
3101 /* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
3102 p_cust_vr.spec_vr_status <> 1000 )
3103 AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
3104 WHERE status_type = 800)
3105 AND vr.delete_mark = 0
3106 AND s.delete_mark = 0
3107 AND vr.spec_vr_id <> NVL(p_cust_vr.spec_vr_id, -1)
3108 ;
3109
3110 dummy PLS_INTEGER;
3111 specname VARCHAR2(80);
3112 specvers NUMBER;
3113
3114 BEGIN
3115
3116 OPEN c_cust_vr;
3117 FETCH c_cust_vr INTO dummy, specname, specvers;
3118 IF c_cust_vr%FOUND THEN
3119 CLOSE c_cust_vr;
3120 FND_MESSAGE.SET_NAME('GMD', 'GMD_CUST_VR_EXIST');
3121 FND_MESSAGE.SET_TOKEN('spec', specname);
3122 FND_MESSAGE.SET_TOKEN('vers', specvers);
3123 FND_MSG_PUB.ADD;
3124 RETURN TRUE;
3125 ELSE
3126 CLOSE c_cust_vr;
3127 RETURN FALSE;
3128 END IF;
3129
3130 EXCEPTION
3131 -- Though there is no reason the program can reach
3132 -- here, this is coded just for the reasons we can
3133 -- not think of!
3134 WHEN OTHERS THEN
3135 FND_MESSAGE.SET_NAME('GMD', 'GMD_API_ERROR');
3136 FND_MESSAGE.SET_TOKEN('PACKAGE', 'GMD_SPEC_VRS_GRP.CUST_VR_EXIST' );
3137 FND_MESSAGE.SET_TOKEN('ERROR', SUBSTR(SQLERRM,1,200));
3138 RETURN TRUE;
3139
3140 END cust_vr_exist;
3141
3142
3143
3144
3145
3146
3147 --Start of comments
3148 --+========================================================================+
3149 --| API Name : validate_supp_vr |
3150 --| TYPE : Group |
3151 --| Notes : This procedure validates all the fields of |
3152 --| Supplier validity rule record. This procedure can be |
3153 --| called from FORM or API and the caller need |
3154 --| to specify this in p_called_from parameter |
3155 --| while calling this procedure. Based on where |
3156 --| it is called from certain validations will |
3157 --| either be performed or skipped. |
3158 --| |
3159 --| If everything is fine then OUT parameter |
3160 --| x_return_status is set to 'S' else appropriate |
3161 --| error message is put on the stack and error |
3162 --| is returned. |
3163 --| |
3164 --| HISTORY |
3165 --| Chetan Nagar 26-Jul-2002 Created. |
3166 --| |
3167 --+========================================================================+
3168 -- End of comments
3169
3170 PROCEDURE validate_supp_vr
3171 (
3172 p_supp_vr IN GMD_SUPPLIER_SPEC_VRS%ROWTYPE
3173 , p_called_from IN VARCHAR2
3174 , p_operation IN VARCHAR2
3175 , x_return_status OUT NOCOPY VARCHAR2
3176 ) IS
3177
3178 -- Local Variables
3179 dummy NUMBER;
3180 l_return_status VARCHAR2(1);
3181
3182 l_spec GMD_SPECIFICATIONS%ROWTYPE;
3183 l_spec_out GMD_SPECIFICATIONS%ROWTYPE;
3184 l_item_mst MTL_SYSTEM_ITEMS_B%ROWTYPE;
3185 l_sampling_plan GMD_SAMPLING_PLANS%ROWTYPE;
3186 l_sampling_plan_out GMD_SAMPLING_PLANS%ROWTYPE;
3187 l_inventory_item_id NUMBER;
3188 l_organization_id NUMBER;
3189 l_uom_rate NUMBER;
3190
3191 -- Exceptions
3192 e_spec_fetch_error EXCEPTION;
3193 e_smpl_plan_fetch_error EXCEPTION;
3194 e_error_fetch_item EXCEPTION;
3195
3196 BEGIN
3197 -- Initialize API return status to success
3198 x_return_status := FND_API.G_RET_STS_SUCCESS;
3199
3200 IF NOT (p_operation in ('INSERT', 'UPDATE', 'DELETE')) THEN
3201 -- Invalid Operation
3202 GMD_API_PUB.Log_Message('GMD_INVALID_OPERATION');
3203 RAISE FND_API.G_EXC_ERROR;
3204 END IF;
3205
3206 -- Verify that the specification exists.
3207 l_spec.spec_id := p_supp_vr.spec_id;
3208 IF NOT (GMD_Specifications_PVT.Fetch_Row(
3209 p_specifications => l_spec,
3210 x_specifications => l_spec_out)
3211 ) THEN
3212 -- Fetch Error
3213 GMD_API_PUB.Log_Message('GMD_SPEC_FETCH_ERROR');
3214 RAISE e_spec_fetch_error;
3215 END IF;
3216
3217 l_spec := l_spec_out ;
3218
3219 -- Verify that the Sampling Plan exists.
3220 --odab added this test.
3221 IF (p_supp_vr.sampling_plan_id IS NOT NULL)
3222 THEN
3223 l_sampling_plan.sampling_plan_id := p_supp_vr.sampling_plan_id;
3224 IF NOT (GMD_Sampling_Plans_PVT.Fetch_Row(
3225 p_sampling_plan => l_sampling_plan,
3226 x_sampling_plan => l_sampling_plan_out)
3227 ) THEN
3228 -- Fetch Error
3229 GMD_API_PUB.Log_Message('GMD_SAMPLING_PLAN_FETCH_ERROR');
3230 RAISE e_smpl_plan_fetch_error;
3231 END IF;
3232 l_sampling_plan := l_sampling_plan_out ;
3233 END IF;
3234
3235 IF (p_called_from = 'API') THEN
3236 --For mini pack L, bug 3439865
3237 IF (nvl(p_supp_vr.auto_sample_ind,'N') not in ('N','Y')) THEN
3238 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3239 'WHAT', 'INVALID_AUTO_SAMPLE_IND');
3240 RAISE FND_API.G_EXC_ERROR;
3241 END IF;
3242 -- end 3439865
3243 -- Check for NULLs and Valid Foreign Keys in the input parameter
3244 GMD_SPEC_VRS_GRP.check_for_null_and_fks_in_svr
3245 (
3246 p_supp_vr => p_supp_vr
3247 , p_spec => l_spec
3248 , x_return_status => l_return_status
3249 );
3250 -- No need if called from FORM since it is already
3251 -- done in the form
3252
3253 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3254 -- Message is alrady logged by check_for_null procedure
3255 RAISE FND_API.G_EXC_ERROR;
3256 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3257 -- Message is alrady logged by check_for_null procedure
3258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3259 END IF;
3260 END IF;
3261
3262 -- First Verify that the SAME VR does not exists
3263 IF (p_operation IN ('INSERT', 'UPDATE')
3264 AND supp_vr_exist(p_supp_vr, l_spec))
3265 THEN
3266 -- Disaster, Trying to insert duplicate
3267 -- bug 2630007, odaboval put the message in function supp_vr_exist.
3268 -- GMD_API_PUB.Log_Message('GMD_SUPP_VR_EXIST');
3269 RAISE FND_API.G_EXC_ERROR;
3270 END IF;
3271
3272 -- Check to make sure that a samplingplan exists
3273 -- if auto sample flag on
3274 IF ((p_supp_vr.sampling_plan_id IS NULL) and
3275 (p_supp_vr.auto_sample_ind = 'Y'))
3276 THEN
3277 GMD_API_PUB.Log_Message('GMD_NEED_SAMPLE_PLAN');
3278 RAISE e_smpl_plan_fetch_error;
3279 END IF;
3280
3281 -- Sample Quantity UOM must be convertible to Item's UOM
3282 BEGIN
3283 SELECT inventory_item_id INTO l_inventory_item_id FROM
3284 gmd_specifications WHERE spec_id = p_supp_vr.spec_id;
3285 SELECT owner_organization_id INTO l_organization_id FROM
3286 gmd_specifications WHERE spec_id = p_supp_vr.spec_id;
3287 SELECT * INTO l_item_mst
3288 FROM mtl_system_items_b
3289 WHERE inventory_item_id = l_inventory_item_id
3290 AND organization_id = l_organization_id;
3291 EXCEPTION
3292 WHEN OTHERS THEN
3293 GMD_API_PUB.Log_Message('GMD_ITEM_FETCH_ERROR');
3294 RAISE e_error_fetch_item;
3295 END;
3296
3297 --odab added this test.
3298 IF (p_supp_vr.sampling_plan_id IS NOT NULL)
3299 THEN
3300 inv_convert.inv_um_conversion (
3301 from_unit => l_sampling_plan.sample_qty_uom,
3302 to_unit => l_item_mst.primary_uom_code,
3303 item_id => l_inventory_item_id,
3304 lot_number => NULL,
3305 organization_id => l_organization_id ,
3306 uom_rate => l_uom_rate );
3307
3308 IF l_uom_rate = -99999 THEN
3309 GMD_API_PUB.Log_Message('GMD_UOM_CONVERSION_ERROR');
3310 RAISE FND_API.G_EXC_ERROR;
3311 END IF;
3312 END IF;
3313
3314 -- No need to check the return status because above procedure
3315 -- logs appropriate message on the stack and raises an exception.
3316
3317 -- The Start Date must be less than the End Date
3318 If ( p_supp_vr.end_date IS NOT NULL AND
3319 p_supp_vr.start_date > p_supp_vr.end_date) THEN
3320 GMD_API_PUB.Log_Message('GMD_SPEC_VR_EFF_DATE_ERROR');
3321 RAISE FND_API.G_EXC_ERROR;
3322 END IF;
3323
3324 -- Spec VR Status Must be less than Spec Status upto Appoved Stages
3325 IF (floor(l_spec.spec_status/100) <= 7 AND
3326 floor(p_supp_vr.spec_vr_status/100) <= 7 AND
3327 p_supp_vr.spec_vr_status > l_spec.spec_status) THEN
3328 GMD_API_PUB.Log_Message('GMD_SPEC_VR_STATUS_HIGHER');
3329 RAISE FND_API.G_EXC_ERROR;
3330 END IF;
3331
3332 -- All systems GO...
3333
3334 EXCEPTION
3335 WHEN FND_API.G_EXC_ERROR OR
3336 e_spec_fetch_error OR
3337 e_smpl_plan_fetch_error OR
3338 e_error_fetch_item
3339 THEN
3340 x_return_status := FND_API.G_RET_STS_ERROR ;
3341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3343 WHEN OTHERS THEN
3344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3345
3346
3347 END validate_supp_vr;
3348
3349
3350
3351
3352 --Start of comments
3353 --+========================================================================+
3354 --| API Name : check_for_null_and_fks_in_svr |
3355 --| TYPE : Group |
3356 --| Notes : This procedure checks for NULL and Foreign Key |
3357 --| constraints for the required filed in the Spec |
3358 --| Supplier VR record. |
3359 --| |
3360 --| If everything is fine then 'S' is returned in the |
3361 --| parameter - x_return_status otherwise error message |
3362 --| is logged and error status - E or U returned |
3363 --| |
3364 --| HISTORY |
3365 --| Chetan Nagar 26-Jul-2002 Created. |
3366 --| |
3367 --| SaiKiran 04-MAY-2004 Enhancement #3476560. added |
3368 --| 'delayed_lot_entry' to the call to 'check_vr_controls'|
3369 --| procedure. |
3370 --| |
3371 --| Saikiran 11-Apr-2005 Convergence Changes |
3372 --| srakrish 15-june-06 BUG 5251172: Checking if the |
3373 --| responsibility is available to the |
3374 --| organization |
3375 --+========================================================================+
3376 -- End of comments
3377
3378 PROCEDURE check_for_null_and_fks_in_svr
3379 (
3380 p_supp_vr IN gmd_supplier_spec_vrs%ROWTYPE
3381 , p_spec IN gmd_specifications%ROWTYPE
3382 , x_return_status OUT NOCOPY VARCHAR2
3383 )
3384 IS
3385
3386 CURSOR c_item_controls IS
3387 SELECT lot_control_code
3388 FROM mtl_system_items_b
3389 WHERE inventory_item_id = p_spec.inventory_item_id
3390 AND organization_id = p_spec.owner_organization_id;
3391
3392 CURSOR c_supplier IS
3393 SELECT 1
3394 FROM po_vendors v
3395 WHERE v.vendor_id = p_supp_vr.supplier_id
3396 AND v.enabled_flag = 'Y'
3397 AND sysdate between nvl(v.start_date_active, sysdate-1)
3398 AND nvl(v.end_date_active, sysdate+1);
3399
3400 CURSOR c_supplier_site IS
3401 SELECT 1
3402 FROM po_vendor_sites_all v
3403 WHERE (v.purchasing_site_flag = 'Y'
3404 OR v.rfq_only_site_flag = 'Y')
3405 AND sysdate < NVL(inactive_date, sysdate + 1)
3406 AND v.vendor_id = p_supp_vr.supplier_id
3407 AND v.vendor_site_id = p_supp_vr.supplier_site_id;
3408
3409 -- bug 4924483 sql id 14687791 - cost is down from 4,380,562 to 6 - no IN
3410
3411 /*CURSOR c_po IS
3412 SELECT 1
3413 FROM po_headers_all pha
3414 WHERE pha.po_header_id IN
3415 (SELECT pla.po_header_id
3416 FROM po_lines_all pla
3417 WHERE pla.po_header_id = pha.po_header_id
3418 AND pla.item_id = p_spec.inventory_item_id
3419 AND pha.vendor_id = p_supp_vr.supplier_id
3420 AND pha.vendor_site_id = p_supp_vr.supplier_site_id
3421 AND pha.po_header_id = p_supp_vr.po_header_id); */
3422
3423 -- fix
3424 CURSOR c_po IS
3425 SELECT 1
3426 FROM po_headers_all pha, po_lines_all pla
3427 WHERE pha.po_header_id = p_supp_vr.po_header_id
3428 AND pha.vendor_id = p_supp_vr.supplier_id
3429 AND pha.vendor_site_id = p_supp_vr.supplier_site_id
3430 AND pha.po_header_id = pla.po_header_id
3431 AND pla.item_id = p_spec.inventory_item_id;
3432
3433
3434
3435
3436
3437
3438 CURSOR c_po_line IS
3439 SELECT 1
3440 FROM po_lines_all pla
3441 WHERE pla.item_id = p_spec.inventory_item_id
3442 AND pla.po_header_id = p_supp_vr.po_header_id
3443 AND pla.po_line_id = p_Supp_vr.po_line_id;
3444
3445 -- Local variables
3446 dummy PLS_INTEGER;
3447 l_lot_ctl NUMBER;
3448
3449 BEGIN
3450
3451 --=========================================================================
3452 -- WHO section :
3453 --=========================================================================
3454 check_who( p_user_id => p_supp_vr.created_by);
3455 check_who( p_user_id => p_supp_vr.last_updated_by);
3456 IF (p_supp_vr.creation_date IS NULL
3457 OR p_supp_vr.last_update_date IS NULL)
3458 THEN
3459 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3460 'WHAT', 'the dates must not be NULL');
3461 RAISE FND_API.G_EXC_ERROR;
3462 END IF;
3463
3464
3465
3466 -- Organization is valid
3467 IF (p_supp_vr.organization_id IS NOT NULL) THEN
3468 -- Check that organization is accessible to the user's responsibility
3469 OPEN c_orgn( p_supp_vr.organization_id);
3470 FETCH c_orgn INTO dummy;
3471 IF c_orgn%NOTFOUND THEN
3472 CLOSE c_orgn;
3473 GMD_API_PUB.Log_Message('GMD_ORGANIZATION_ID_NOT_FOUND',
3474 'ORGN_ID', p_supp_vr.organization_id);
3475 RAISE FND_API.G_EXC_ERROR;
3476 END IF;
3477 CLOSE c_orgn;
3478 END IF;
3479
3480 --srakrish BUG 5251172: Checking if the responsibility is available to the organization.
3481 IF NOT (gmd_api_grp.OrgnAccessible(p_supp_vr.organization_id)) THEN
3482 RAISE FND_API.G_EXC_ERROR;
3483 END IF;
3484
3485
3486
3487
3488
3489 --=========================================================================
3490 -- Get Item Controls
3491 --=========================================================================
3492 OPEN c_item_controls;
3493 FETCH c_item_controls
3494 INTO l_lot_ctl;
3495 IF (c_item_controls%NOTFOUND)
3496 THEN
3497 CLOSE c_item_controls;
3498 FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
3499 FND_MESSAGE.SET_TOKEN('WHAT', 'INVENTORY_ITEM_ID');
3500 FND_MESSAGE.SET_TOKEN('VALUE', p_spec.inventory_item_id);
3501 FND_MSG_PUB.ADD;
3502 RAISE FND_API.G_EXC_ERROR;
3503 END IF;
3504 CLOSE c_item_controls;
3505
3506 --=========================================================================
3507 -- lot_optional_on_sample :
3508 -- When this field is NOT NULL, all the following fields must be null :
3509 -- sample_inv_trans_ind
3510 --=========================================================================
3511 --Enhancement #3476560. added 'delayed_lot_entry' to the call to 'check_vr_controls' procedure
3512 check_VR_Controls( p_VR_type => 'SUPPLIER'
3513 , p_lot_optional_on_sample => p_supp_vr.lot_optional_on_sample
3514 , p_delayed_lot_entry => p_supp_vr.delayed_lot_entry
3515 , p_sample_inv_trans_ind => p_supp_vr.sample_inv_trans_ind
3516 , p_lot_ctl => l_lot_ctl
3517 , p_status_ctl => NULL
3518 , p_control_lot_attrib_ind => p_supp_vr.CONTROL_LOT_ATTRIB_IND
3519 , p_in_spec_lot_status_id => p_supp_vr.in_spec_lot_status_id
3520 , p_out_of_spec_lot_status_id => p_supp_vr.out_of_spec_lot_status_id
3521 , p_control_batch_step_ind => NULL);
3522
3523 --=========================================================================
3524 -- spec_vr_status :
3525 --=========================================================================
3526 OPEN c_status(p_supp_vr.spec_vr_status);
3527 FETCH c_status
3528 INTO dummy;
3529 IF (c_status%NOTFOUND)
3530 THEN
3531 CLOSE c_status;
3532 GMD_API_PUB.Log_Message('GMD_SPEC_STATUS_NOT_FOUND',
3533 'STATUS', p_supp_vr.spec_vr_status);
3534 RAISE FND_API.G_EXC_ERROR;
3535 END IF;
3536 CLOSE c_status;
3537
3538 --=========================================================================
3539 -- start_date : This field is mandatory
3540 --=========================================================================
3541 IF (p_supp_vr.start_date IS NULL)
3542 THEN
3543 GMD_API_PUB.Log_Message('GMD_SPEC_VR_START_DATE_REQD');
3544 RAISE FND_API.G_EXC_ERROR;
3545 END IF;
3546
3547 --=========================================================================
3548 -- COA section :
3549 --=========================================================================
3550 check_COA( p_coa_type => p_supp_vr.coa_type
3551 , p_coa_at_ship_ind => p_supp_vr.coa_at_ship_ind
3552 , p_coa_at_invoice_ind => p_supp_vr.coa_at_invoice_ind
3553 , p_coa_req_from_supl_ind => p_supp_vr.coa_req_from_supl_ind);
3554
3555 --=========================================================================
3556 -- supplier_id : This field is mandatory
3557 --=========================================================================
3558 IF (p_supp_vr.supplier_id IS NULL)
3559 THEN
3560 GMD_API_PUB.Log_Message('GMD_SUPPLIER_REQD');
3561 RAISE FND_API.G_EXC_ERROR;
3562 ELSE
3563 OPEN c_supplier;
3564 FETCH c_supplier INTO dummy;
3565 IF (c_supplier%NOTFOUND)
3566 THEN
3567 CLOSE c_supplier;
3568 GMD_API_PUB.Log_Message('GMD_SUPPLIER_NOT_FOUND');
3569 RAISE FND_API.G_EXC_ERROR;
3570 END IF;
3571 CLOSE c_supplier;
3572 END IF;
3573
3574 --=========================================================================
3575 -- supplier_site_id :
3576 --=========================================================================
3577 IF ( p_supp_vr.supplier_site_id IS NOT NULL)
3578 THEN
3579 OPEN c_supplier_site;
3580 FETCH c_supplier_site
3581 INTO dummy;
3582 IF (c_supplier_site%NOTFOUND)
3583 THEN
3584 CLOSE c_supplier_site;
3585 FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
3586 FND_MESSAGE.SET_TOKEN('WHAT', 'SUPPLIER_SITE_ID');
3587 FND_MESSAGE.SET_TOKEN('VALUE', p_supp_vr.supplier_site_id);
3588 FND_MSG_PUB.ADD;
3589 RAISE FND_API.G_EXC_ERROR;
3590 END IF;
3591 CLOSE c_supplier_site;
3592 END IF;
3593
3594 --=========================================================================
3595 -- po_header_id :
3596 -- When po_header_id is NOT NULL, then supplier_site_id must be NOT NULL
3597 --=========================================================================
3598 -- PO
3599 IF (p_supp_vr.po_header_id IS NOT NULL)
3600 THEN
3601 IF (p_supp_vr.supplier_site_id IS NULL)
3602 THEN
3603 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3604 'WHAT', 'supplier_site_id must not be NULL');
3605 RAISE FND_API.G_EXC_ERROR;
3606 END IF;
3607
3608 OPEN c_po;
3609 FETCH c_po INTO dummy;
3610 IF (c_po%NOTFOUND)
3611 THEN
3612 CLOSE c_po;
3613 GMD_API_PUB.Log_Message('GMD_PO_NOT_FOUND');
3614 RAISE FND_API.G_EXC_ERROR;
3615 END IF;
3616 CLOSE c_po;
3617 END IF;
3618
3619 --=========================================================================
3620 -- po_line_id :
3621 -- When po_line_id is NOT NULL, then supplier_site_id AND po_header_id must be NOT NULL
3622 --=========================================================================
3623 -- PO Line
3624 IF (p_supp_vr.po_line_id IS NOT NULL)
3625 THEN
3626 IF (p_supp_vr.po_header_id IS NULL)
3627 THEN
3628 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3629 'WHAT', 'po_header_id must not be NULL');
3630 RAISE FND_API.G_EXC_ERROR;
3631 END IF;
3632
3633 IF (p_supp_vr.supplier_site_id IS NULL)
3634 THEN
3635 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
3636 'WHAT', 'supplier site must not be NULL');
3637 RAISE FND_API.G_EXC_ERROR;
3638 END IF;
3639
3640 OPEN c_po_line;
3641 FETCH c_po_line INTO dummy;
3642 IF (c_po_line%NOTFOUND)
3643 THEN
3644 CLOSE c_po_line;
3645 GMD_API_PUB.Log_Message('GMD_PO_LINE_NOT_FOUND');
3646 RAISE FND_API.G_EXC_ERROR;
3647 END IF;
3648 CLOSE c_po_line;
3649 END IF;
3650
3651 -- All Systems Go...
3652
3653 EXCEPTION
3654 WHEN FND_API.G_EXC_ERROR THEN
3655 x_return_status := FND_API.G_RET_STS_ERROR ;
3656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3658 WHEN OTHERS THEN
3659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3660
3661 END check_for_null_and_fks_in_svr;
3662
3663
3664
3665
3666 --Start of comments
3667 --+========================================================================+
3668 --| API Name : supp_vr_exist |
3669 --| TYPE : Group |
3670 --| Notes : This function returns TRUE if the supplier VR already |
3671 --| exists for the spcified parameter in the database, FALSE |
3672 --| otherwise. |
3673 --| |
3674 --| HISTORY |
3675 --| Chetan Nagar 26-Jul-2002 Created. |
3676 --| Olivier Daboval 17-OCT-2002 bug 2630007, added spec_name in |
3677 --| the VR_EXIST message |
3678 --| Olivier Daboval 14-NOV-2002 Added NVL(spec_vr_id, -1) because |
3679 --| the API passes a NULL spec_vr_id |
3680 --| in insert mode. |
3681 --| Brenda Stone 20-NOV-2003 Bug 3090290; allow duplicate spec vr |
3682 --| with "OBSOLUTE" status |
3683 --| Bug 2984784; add Version to msg for |
3684 --| existing spec vr. |
3685 --| |
3686 --| Saikiran 12-Apr-2005 Convergence Changes |
3687 --| Plowe 04-Apr-2006 Bug 5117733 - added item revision to match
3688 --+========================================================================+
3689 -- End of comments
3690
3691 FUNCTION supp_vr_exist(p_supp_vr GMD_SUPPLIER_SPEC_VRS%ROWTYPE,
3692 p_spec GMD_SPECIFICATIONS%ROWTYPE)
3693 RETURN BOOLEAN IS
3694
3695 CURSOR c_supp_vr IS
3696 SELECT vr.spec_vr_id, s.spec_name, s.spec_vers
3697 FROM gmd_specifications_b s, gmd_supplier_spec_vrs vr
3698 WHERE s.spec_id = vr.spec_id
3699 AND s.owner_organization_id = p_spec.owner_organization_id
3700 AND s.inventory_item_id = p_spec.inventory_item_id
3701 AND ( (s.revision is null and p_spec.revision is NULL ) OR -- handle item revision 5117733
3702 (s.revision = p_spec.revision )
3703 )
3704 AND ((s.grade_code is NULL AND p_spec.grade_code is NULL) OR
3705 (s.grade_code = p_spec.grade_code)
3706 )
3707 AND ((vr.organization_id is NULL AND p_supp_vr.organization_id is NULL) OR
3708 (vr.organization_id = p_supp_vr.organization_id)
3709 )
3710 AND ((vr.supplier_id is NULL AND p_supp_vr.supplier_id is NULL) OR
3711 (vr.supplier_id = p_supp_vr.supplier_id)
3712 )
3713 AND ((vr.supplier_site_id is NULL AND p_supp_vr.supplier_site_id is NULL) OR
3714 (vr.supplier_site_id = p_supp_vr.supplier_site_id)
3715 )
3716 AND ((vr.po_header_id is NULL AND p_supp_vr.po_header_id is NULL) OR
3717 (vr.po_header_id = p_supp_vr.po_header_id)
3718 )
3719 AND ((vr.po_line_id is NULL AND p_supp_vr.po_line_id is NULL) OR
3720 (vr.po_line_id = p_supp_vr.po_line_id)
3721 )
3722 AND ((vr.end_date is NULL AND (p_supp_vr.end_date IS NULL OR
3723 p_supp_vr.end_date >= vr.start_date)) OR
3724 (p_supp_vr.end_date IS NULL AND
3725 p_supp_vr.start_date <= nvl(vr.end_date, p_supp_vr.start_date)) OR
3726 (p_supp_vr.start_date <= vr.end_date AND p_supp_vr.end_date >= vr.start_date)
3727 )
3728 AND ( floor(vr.spec_vr_status/100) = floor(p_supp_vr.spec_vr_status/100) AND
3729 /* Bug 3090290; allow duplicate spec vr with "OBSOLUTE" status */
3730 p_supp_vr.spec_vr_status <> 1000 )
3731 AND vr.spec_vr_status NOT IN (SELECT status_code FROM gmd_qc_status
3732 WHERE status_type = 800)
3733 AND vr.delete_mark = 0
3734 AND s.delete_mark = 0
3735 AND vr.spec_vr_id <> NVL(p_supp_vr.spec_vr_id, -1)
3736 ;
3737
3738 dummy PLS_INTEGER;
3739 specname VARCHAR2(80);
3740 specvers NUMBER;
3741
3742 BEGIN
3743
3744 OPEN c_supp_vr;
3745 FETCH c_supp_vr INTO dummy, specname, specvers;
3746 IF c_supp_vr%FOUND THEN
3747 CLOSE c_supp_vr;
3748 FND_MESSAGE.SET_NAME('GMD', 'GMD_SUPP_VR_EXIST');
3749 FND_MESSAGE.SET_TOKEN('spec', specname);
3750 FND_MESSAGE.SET_TOKEN('vers', specvers);
3751 FND_MSG_PUB.ADD;
3752 RETURN TRUE;
3753 ELSE
3754 CLOSE c_supp_vr;
3755 RETURN FALSE;
3756 END IF;
3757
3758 EXCEPTION
3759 -- Though there is no reason the program can reach
3760 -- here, this is coded just for the reasons we can
3761 -- not think of!
3762 WHEN OTHERS THEN
3763 FND_MESSAGE.SET_NAME('GMD', 'GMD_API_ERROR');
3764 FND_MESSAGE.SET_TOKEN('PACKAGE', 'GMD_SPEC_VRS_GRP.SUPP_VR_EXIST' );
3765 FND_MESSAGE.SET_TOKEN('ERROR', SUBSTR(SQLERRM,1,200));
3766 RETURN TRUE;
3767
3768 END supp_vr_exist;
3769
3770 /*===========================================================================
3771 PROCEDURE NAME: validate_before_delete_inv_vrs
3772
3773 DESCRIPTION: This procedure validates:
3774 a) Primary key supplied
3775 b) Inventory Spec VRS is not already delete_marked
3776
3777 PARAMETERS:
3778
3779 CHANGE HISTORY: Created 09-JUL-02 KYH
3780 ===========================================================================*/
3781
3782 PROCEDURE VALIDATE_BEFORE_DELETE_INV_VRS(
3783 p_spec_id IN NUMBER,
3784 p_spec_vr_id IN NUMBER,
3785 x_return_status OUT NOCOPY VARCHAR2,
3786 x_message_data OUT NOCOPY VARCHAR2) IS
3787
3788 l_progress VARCHAR2(3);
3789 l_temp VARCHAR2(1);
3790 l_inventory_spec_vrs GMD_INVENTORY_SPEC_VRS%ROWTYPE;
3791 l_inventory_spec_vrs_out GMD_INVENTORY_SPEC_VRS%ROWTYPE;
3792
3793 BEGIN
3794 l_progress := '010';
3795 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3796
3797 -- validate for primary key
3798 -- ========================
3799 IF p_spec_id IS NULL THEN
3800 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3801 RAISE FND_API.G_EXC_ERROR;
3802 ELSE
3803 l_inventory_spec_vrs.spec_id := p_spec_id;
3804 END IF;
3805
3806 IF p_spec_vr_id IS NULL THEN
3807 GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3808 RAISE FND_API.G_EXC_ERROR;
3809 ELSE
3810 l_inventory_spec_vrs.spec_vr_id := p_spec_vr_id;
3811 END IF;
3812
3813 -- Fetch the row
3814 -- =============
3815 IF NOT GMD_Inventory_Spec_VRS_PVT.Fetch_Row(l_inventory_spec_vrs,l_inventory_spec_vrs_out)
3816 THEN
3817 GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3818 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
3819 'l_column_name', 'SPEC_VR_ID',
3820 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
3821 RAISE FND_API.G_EXC_ERROR;
3822 END IF;
3823
3824 l_inventory_spec_vrs := l_inventory_spec_vrs_out ;
3825
3826 -- Terminate if the row is already delete marked
3827 -- =============================================
3828 IF l_inventory_spec_vrs.delete_mark <> 0
3829 THEN
3830 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3831 'l_table_name', 'GMD_INVENTORY_SPEC_VRS',
3832 'l_column_name', 'SPEC_VR_ID',
3833 'l_key_value', l_inventory_spec_vrs.spec_vr_id);
3834 RAISE FND_API.G_EXC_ERROR;
3835 END IF;
3836
3837
3838 EXCEPTION
3839 WHEN FND_API.G_EXC_ERROR THEN
3840 x_return_status := FND_API.G_RET_STS_ERROR ;
3841 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3842
3843 WHEN OTHERS THEN
3844 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
3845 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
3846 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
3847 FND_MESSAGE.Set_Token('POSITION',l_progress );
3848 FND_MSG_PUB.ADD;
3849 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3851
3852 END VALIDATE_BEFORE_DELETE_INV_VRS ;
3853
3854 /*===========================================================================
3855 PROCEDURE NAME: validate_before_delete_wip_vrs
3856
3857 DESCRIPTION: This procedure validates:
3858 a) Primary key supplied
3859 b) WIP Spec VRS is not already delete_marked
3860
3861 PARAMETERS:
3862
3863 CHANGE HISTORY: Created 09-JUL-02 KYH
3864 ===========================================================================*/
3865
3866 PROCEDURE VALIDATE_BEFORE_DELETE_WIP_VRS(
3867 p_spec_id IN NUMBER,
3868 p_spec_vr_id IN NUMBER,
3869 x_return_status OUT NOCOPY VARCHAR2,
3870 x_message_data OUT NOCOPY VARCHAR2) IS
3871
3872 l_progress VARCHAR2(3);
3873 l_temp VARCHAR2(1);
3874 l_wip_spec_vrs GMD_WIP_SPEC_VRS%ROWTYPE;
3875 l_wip_spec_vrs_out GMD_WIP_SPEC_VRS%ROWTYPE;
3876
3877 BEGIN
3878 l_progress := '010';
3879 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3880
3881 -- validate for primary key
3882 -- ========================
3883 IF p_spec_id IS NULL THEN
3884 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3885 RAISE FND_API.G_EXC_ERROR;
3886 ELSE
3887 l_wip_spec_vrs.spec_id := p_spec_id;
3888 END IF;
3889
3890 IF p_spec_vr_id IS NULL THEN
3891 GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3892 RAISE FND_API.G_EXC_ERROR;
3893 ELSE
3894 l_wip_spec_vrs.spec_vr_id := p_spec_vr_id;
3895 END IF;
3896
3897 -- Fetch the row
3898 -- =============
3899 IF NOT GMD_WIP_Spec_VRS_PVT.Fetch_Row(l_wip_spec_vrs,l_wip_spec_vrs_out)
3900 THEN
3901 GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3902 'l_table_name', 'GMD_WIP_SPEC_VRS',
3903 'l_column_name', 'SPEC_VR_ID',
3904 'l_key_value', l_wip_spec_vrs.spec_vr_id);
3905 RAISE FND_API.G_EXC_ERROR;
3906 END IF;
3907
3908 l_wip_spec_vrs := l_wip_spec_vrs_out ;
3909
3910 -- Terminate if the row is already delete marked
3911 -- =============================================
3912 IF l_wip_spec_vrs.delete_mark <> 0
3913 THEN
3914 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3915 'l_table_name', 'GMD_WIP_SPEC_VRS',
3916 'l_column_name', 'SPEC_VR_ID',
3917 'l_key_value', l_wip_spec_vrs.spec_vr_id);
3918 RAISE FND_API.G_EXC_ERROR;
3919 END IF;
3920
3921
3922 EXCEPTION
3923 WHEN FND_API.G_EXC_ERROR THEN
3924 x_return_status := FND_API.G_RET_STS_ERROR ;
3925 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3926
3927 WHEN OTHERS THEN
3928 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
3929 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
3930 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
3931 FND_MESSAGE.Set_Token('POSITION',l_progress );
3932 FND_MSG_PUB.ADD;
3933 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
3934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3935
3936 END VALIDATE_BEFORE_DELETE_WIP_VRS ;
3937
3938 /*===========================================================================
3939 PROCEDURE NAME: validate_before_delete_cst_vrs
3940
3941 DESCRIPTION: This procedure validates:
3942 a) Primary key supplied
3943 b) Customer Spec VRS is not already delete_marked
3944
3945 PARAMETERS:
3946
3947 CHANGE HISTORY: Created 09-JUL-02 KYH
3948 ===========================================================================*/
3949
3950 PROCEDURE VALIDATE_BEFORE_DELETE_CST_VRS(
3951 p_spec_id IN NUMBER,
3952 p_spec_vr_id IN NUMBER,
3953 x_return_status OUT NOCOPY VARCHAR2,
3954 x_message_data OUT NOCOPY VARCHAR2) IS
3955
3956 l_progress VARCHAR2(3);
3957 l_customer_spec_vrs GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
3958 l_customer_spec_vrs_out GMD_CUSTOMER_SPEC_VRS%ROWTYPE;
3959
3960 BEGIN
3961 l_progress := '010';
3962 x_return_status := FND_API.G_RET_STS_SUCCESS ;
3963
3964 -- validate for primary key
3965 -- ========================
3966 IF p_spec_id IS NULL THEN
3967 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
3968 RAISE FND_API.G_EXC_ERROR;
3969 ELSE
3970 l_customer_spec_vrs.spec_id := p_spec_id;
3971 END IF;
3972
3973 IF p_spec_vr_id IS NULL THEN
3974 GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
3975 RAISE FND_API.G_EXC_ERROR;
3976 ELSE
3977 l_customer_spec_vrs.spec_vr_id := p_spec_vr_id;
3978 END IF;
3979
3980 -- Fetch the row
3981 -- =============
3982 IF NOT GMD_Customer_Spec_VRS_PVT.Fetch_Row(l_customer_spec_vrs,l_customer_spec_vrs_out)
3983 THEN
3984 GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
3985 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
3986 'l_column_name', 'SPEC_VR_ID',
3987 'l_key_value', l_customer_spec_vrs.spec_vr_id);
3988 RAISE FND_API.G_EXC_ERROR;
3989 END IF;
3990
3991 l_customer_spec_vrs := l_customer_spec_vrs_out ;
3992
3993 -- Terminate if the row is already delete marked
3994 -- =============================================
3995 IF l_customer_spec_vrs.delete_mark <> 0
3996 THEN
3997 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
3998 'l_table_name', 'GMD_CUSTOMER_SPEC_VRS',
3999 'l_column_name', 'SPEC_VR_ID',
4000 'l_key_value', l_customer_spec_vrs.spec_vr_id);
4001 RAISE FND_API.G_EXC_ERROR;
4002 END IF;
4003
4004
4005 EXCEPTION
4006 WHEN FND_API.G_EXC_ERROR THEN
4007 x_return_status := FND_API.G_RET_STS_ERROR ;
4008 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4009
4010 WHEN OTHERS THEN
4011 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4012 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4013 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4014 FND_MESSAGE.Set_Token('POSITION',l_progress );
4015 FND_MSG_PUB.ADD;
4016 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4018
4019 END VALIDATE_BEFORE_DELETE_CST_VRS ;
4020
4021 /*===========================================================================
4022 PROCEDURE NAME: validate_before_delete_sup_vrs
4023
4024 DESCRIPTION: This procedure validates:
4025 a) Primary key supplied
4026 b) Supplier Spec VRS is not already delete_marked
4027
4028 PARAMETERS:
4029
4030 CHANGE HISTORY: Created 09-JUL-02 KYH
4031 ===========================================================================*/
4032
4033 PROCEDURE VALIDATE_BEFORE_DELETE_SUP_VRS(
4034 p_spec_id IN NUMBER,
4035 p_spec_vr_id IN NUMBER,
4036 x_return_status OUT NOCOPY VARCHAR2,
4037 x_message_data OUT NOCOPY VARCHAR2) IS
4038
4039 l_progress VARCHAR2(3);
4040 l_supplier_spec_vrs GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
4041 l_supplier_spec_vrs_out GMD_SUPPLIER_SPEC_VRS%ROWTYPE;
4042
4043 BEGIN
4044 l_progress := '010';
4045 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4046
4047 -- validate for primary key
4048 -- ========================
4049 IF p_spec_id IS NULL THEN
4050 GMD_API_PUB.Log_Message('GMD_SPEC_ID_REQUIRED');
4051 RAISE FND_API.G_EXC_ERROR;
4052 ELSE
4053 l_supplier_spec_vrs.spec_id := p_spec_id;
4054 END IF;
4055
4056 IF p_spec_vr_id IS NULL THEN
4057 GMD_API_PUB.Log_Message('GMD_SPEC_VR_ID_REQUIRED');
4058 RAISE FND_API.G_EXC_ERROR;
4059 ELSE
4060 l_supplier_spec_vrs.spec_vr_id := p_spec_vr_id;
4061 END IF;
4062
4063 -- Fetch the row
4064 -- =============
4065 IF NOT GMD_Supplier_Spec_VRS_PVT.Fetch_Row(l_supplier_spec_vrs,l_supplier_spec_vrs_out)
4066 THEN
4067 GMD_API_PUB.Log_Message('GMD_FAILED_TO_FETCH_ROW',
4068 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
4069 'l_column_name', 'SPEC_VR_ID',
4070 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
4071 RAISE FND_API.G_EXC_ERROR;
4072 END IF;
4073
4074 l_supplier_spec_vrs := l_supplier_spec_vrs_out ;
4075
4076 -- Terminate if the row is already delete marked
4077 -- =============================================
4078 IF l_supplier_spec_vrs.delete_mark <> 0
4079 THEN
4080 GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
4081 'l_table_name', 'GMD_SUPPLIER_SPEC_VRS',
4082 'l_column_name', 'SPEC_VR_ID',
4083 'l_key_value', l_supplier_spec_vrs.spec_vr_id);
4084 RAISE FND_API.G_EXC_ERROR;
4085 END IF;
4086
4087
4088 EXCEPTION
4089 WHEN FND_API.G_EXC_ERROR THEN
4090 x_return_status := FND_API.G_RET_STS_ERROR ;
4091 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4092
4093 WHEN OTHERS THEN
4094 FND_MESSAGE.Set_Name('GMD','GMD_API_ERROR');
4095 FND_MESSAGE.Set_Token('PACKAGE','GMD_SPEC_GRP.VALIDATE_BEFORE_DELETE');
4096 FND_MESSAGE.Set_Token('ERROR', substr(sqlerrm,1,100));
4097 FND_MESSAGE.Set_Token('POSITION',l_progress );
4098 FND_MSG_PUB.ADD;
4099 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
4100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4101
4102 END VALIDATE_BEFORE_DELETE_SUP_VRS ;
4103
4104 /*===========================================================================
4105 PROCEDURE NAME: check_who
4106
4107 DESCRIPTION: This procedure validates the user_id
4108
4109 PARAMETERS:
4110
4111 CHANGE HISTORY: Created 13-NOV-02 odaboval
4112 ===========================================================================*/
4113 PROCEDURE check_who( p_user_id IN NUMBER)
4114 IS
4115
4116 CURSOR c_who (userid IN NUMBER) IS
4117 SELECT 1
4118 FROM fnd_user
4119 WHERE user_id = userid;
4120
4121 dummy PLS_INTEGER;
4122
4123 BEGIN
4124
4125 IF (p_user_id IS NULL)
4126 THEN
4127 FND_MESSAGE.SET_NAME('GMD','GMD_WRONG_VALUE');
4128 FND_MESSAGE.SET_TOKEN('WHAT', 'USER_ID');
4129 FND_MSG_PUB.ADD;
4130 RAISE FND_API.G_EXC_ERROR;
4131 ELSE
4132 OPEN c_who( p_user_id);
4133 FETCH c_who
4134 INTO dummy;
4135
4136 IF (c_who%NOTFOUND)
4137 THEN
4138 CLOSE c_who;
4139 FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4140 FND_MESSAGE.SET_TOKEN('WHAT', 'USER_ID');
4141 FND_MESSAGE.SET_TOKEN('VALUE', p_user_id);
4142 FND_MSG_PUB.ADD;
4143 RAISE FND_API.G_EXC_ERROR;
4144 END IF;
4145 CLOSE c_who;
4146 END IF;
4147
4148 END check_who;
4149
4150
4151 /*===========================================================================
4152 PROCEDURE NAME: check_COA
4153
4154 DESCRIPTION: This procedure validates the Certificate Of Analysis fields
4155
4156 PARAMETERS:
4157
4158 CHANGE HISTORY: Created 13-NOV-02 odaboval
4159 ===========================================================================*/
4160 PROCEDURE check_COA( p_coa_type IN VARCHAR2
4161 , p_coa_at_ship_ind IN VARCHAR2
4162 , p_coa_at_invoice_ind IN VARCHAR2
4163 , p_coa_req_from_supl_ind IN VARCHAR2)
4164 IS
4165
4166 CURSOR c_coa_type IS
4167 SELECT 1
4168 FROM gem_lookups
4169 WHERE lookup_type = 'GMD_QC_CERTIFICATE_TYPE'
4170 AND lookup_code = p_coa_type;
4171
4172 dummy PLS_INTEGER;
4173
4174 BEGIN
4175
4176 -- Value Check :
4177 -- The only value for these controls are (NULL, 'Y')
4178 IF (p_coa_at_ship_ind IS NOT NULL)
4179 AND (p_coa_at_ship_ind <> 'Y')
4180 THEN
4181 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4182 'WHAT', 'coa_at_ship_ind value must be either NULL or Y');
4183 RAISE FND_API.G_EXC_ERROR;
4184 END IF;
4185 IF (p_coa_at_invoice_ind IS NOT NULL)
4186 AND (p_coa_at_invoice_ind <> 'Y')
4187 THEN
4188 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4189 'WHAT', 'coa_at_invoice_ind value must be either NULL or Y');
4190 RAISE FND_API.G_EXC_ERROR;
4191 END IF;
4192 IF (p_coa_req_from_supl_ind IS NOT NULL)
4193 AND (p_coa_req_from_supl_ind <> 'Y')
4194 THEN
4195 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4196 'WHAT', 'coa_req_from_supl_ind value must be either NULL or Y');
4197 RAISE FND_API.G_EXC_ERROR;
4198 END IF;
4199 IF (p_coa_type IS NOT NULL)
4200 THEN
4201 OPEN c_coa_type;
4202 FETCH c_coa_type
4203 INTO dummy;
4204 IF (c_coa_type%NOTFOUND)
4205 THEN
4206 CLOSE c_coa_type;
4207 FND_MESSAGE.Set_Name('GMD','GMD_NOTFOUND');
4208 FND_MESSAGE.Set_Token('WHAT', 'COA_TYPE');
4209 FND_MESSAGE.Set_Token('VALUE', p_coa_type);
4210 FND_MSG_PUB.Add;
4211 RAISE FND_API.G_EXC_ERROR;
4212 END IF;
4213 CLOSE c_coa_type;
4214 END IF;
4215
4216
4217 -- Functional Check :
4218 --=========================================================================
4219 -- COA :
4220 -- When COA_TYPE is NULL, then these following fields MUST be NULL :
4221 -- coa_at_ship_ind, coa_at_invoice_ind, coa_req_from_supl_ind
4222 --=========================================================================
4223 IF (p_coa_type IS NULL)
4224 THEN
4225 IF (p_coa_at_ship_ind IS NOT NULL)
4226 THEN
4227 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4228 'WHAT', 'coa_at_ship_ind must be NULL');
4229 RAISE FND_API.G_EXC_ERROR;
4230 END IF;
4231
4232 IF (p_coa_at_invoice_ind IS NOT NULL)
4233 THEN
4234 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4235 'WHAT', 'coa_at_invoice_ind must be NULL');
4236 RAISE FND_API.G_EXC_ERROR;
4237 END IF;
4238
4239 IF (p_coa_req_from_supl_ind IS NOT NULL)
4240 THEN
4241 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4242 'WHAT', 'coa_req_from_supl_ind must be NULL');
4243 RAISE FND_API.G_EXC_ERROR;
4244 END IF;
4245 END IF; -- coa_type is NULL
4246
4247 END check_COA;
4248
4249 /*===========================================================================
4250 PROCEDURE NAME: check_VR_controls
4251
4252 DESCRIPTION: This procedure validates the entries in the Controls group
4253
4254 PARAMETERS:
4255
4256 CHANGE HISTORY: Created 13-NOV-02 odaboval
4257
4258 Enhancement# 3476560. Added 04-MAY-04 Saikiran vankadari
4259 'Delayed Lot Entry' field to the signature.
4260 Added validation for 'Delayed Lot Entry' that
4261 it should be 'Y' or Null. Removed special validation
4262 for 'Lot Optional on sample' in case of WIP Validity rule.
4263
4264 Bug# 3652938. 28-MAY-04 Saikiran vankadari
4265 Added validation for the invalid combination of
4266 'Lot Optional on Sample' and 'Delayed Lot Entry'.
4267
4268 Convergence changes 11-Apr-05 Saikiran Vankadari
4269
4270 Bug # 4900420 27-DEC-05 RLNAGARA
4271 Removed the code which was validating the control_lot_attributes when lot_optional_on_sample was not NULL
4272 ===========================================================================*/
4273 PROCEDURE check_VR_Controls
4274 ( p_VR_type IN VARCHAR2
4275 , p_lot_optional_on_sample IN VARCHAR2
4276 , p_delayed_lot_entry IN VARCHAR2 DEFAULT NULL
4277 , p_sample_inv_trans_ind IN VARCHAR2
4278 , p_lot_ctl IN NUMBER
4279 , p_status_ctl IN VARCHAR2
4280 , p_control_lot_attrib_ind IN VARCHAR2
4281 , p_in_spec_lot_status_id IN NUMBER
4282 , p_out_of_spec_lot_status_id IN NUMBER
4283 , p_control_batch_step_ind IN VARCHAR2
4284 , p_auto_complete_batch_step IN VARCHAR2 DEFAULT NULL) IS -- Bug# 5440347
4285
4286 CURSOR c_lot_status (lot_status_id IN VARCHAR2) IS
4287 SELECT 1
4288 FROM mtl_material_statuses
4289 WHERE NVL(enabled_flag,0) = 1
4290 AND status_id = lot_status_id;
4291
4292 dummy PLS_INTEGER;
4293
4294 BEGIN
4295
4296 -- Value Check :
4297 -- The only value for these controls are (NULL, 'Y')
4298 IF (p_lot_optional_on_sample IS NOT NULL)
4299 AND (p_lot_optional_on_sample <> 'Y')
4300 THEN
4301 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4302 'WHAT', 'lot_optional_on_sample value must be either NULL or Y');
4303 RAISE FND_API.G_EXC_ERROR;
4304 END IF;
4305
4306 --Enhancement# 3476560. Added validation for 'Delayed Lot Entry' that it should be 'Y' or Null.
4307 IF (p_delayed_lot_entry IS NOT NULL)
4308 AND(p_delayed_lot_entry<>'Y')
4309 THEN
4310 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4311 'WHAT', 'delayed_lot_entry value must be either NULL or Y');
4312 RAISE FND_API.G_EXC_ERROR;
4313 END IF;
4314
4315 --Bug# 3652938. Added validation for the invalid combination of 'Lot Optional on Sample' and 'Delayed Lot Entry'.
4316 IF (p_lot_optional_on_sample IS NULL)
4317 AND(p_delayed_lot_entry = 'Y')
4318 THEN
4319 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4320 'WHAT', 'delayed_lot_entry value cannot be Y when lot_optional_on_sample is NULL');
4321 RAISE FND_API.G_EXC_ERROR;
4322 END IF;
4323
4324 IF (p_VR_type IN ('INVENTORY', 'WIP','SUPPLIER'))
4325 THEN
4326 IF (p_control_lot_attrib_ind IS NOT NULL)
4327 AND (p_control_lot_attrib_ind <> 'Y')
4328 THEN
4329 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4330 'WHAT', 'control_lot_attrib_ind value must be either NULL or Y');
4331 RAISE FND_API.G_EXC_ERROR;
4332 END IF;
4333
4334 IF (p_sample_inv_trans_ind IS NOT NULL)
4335 AND (p_sample_inv_trans_ind <> 'Y')
4336 THEN
4337 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4338 'WHAT', 'sample_inv_trans_ind value must be either NULL or Y');
4339 RAISE FND_API.G_EXC_ERROR;
4340 END IF;
4341
4342 -- Extra field for WIP :
4343 IF (p_VR_type = 'WIP')
4344 THEN
4345 IF (p_control_batch_step_ind IS NOT NULL)
4346 AND (p_control_batch_step_ind <> 'Y')
4347 THEN
4348 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4349 'WHAT', 'control_batch_step_ind value must be either NULL or Y');
4350 RAISE FND_API.G_EXC_ERROR;
4351 END IF;
4352
4353 -- Bug# 5440347 start
4354 IF (p_auto_complete_batch_step IS NOT NULL)
4355 AND (p_auto_complete_batch_step <> 'Y')
4356 THEN
4357 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4358 'WHAT', 'auto_complete_batch_step value must be either NULL or Y');
4359 RAISE FND_API.G_EXC_ERROR;
4360 END IF;
4361 -- Bug# 5440347 end
4362
4363
4364 --Enhancement# 3476560. Removed special validation for 'Lot Optional on sample'.
4365 --IF ( p_lot_optional_on_sample IS NOT NULL)
4366 --THEN
4367 -- GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4368 -- 'WHAT', 'lot_optional_on_sample must be NULL');
4369 -- RAISE FND_API.G_EXC_ERROR;
4370 --END IF;
4371
4372 END IF;
4373
4374 END IF;
4375
4376 -- Functional Check :
4377 -- Bug 2698118 : When non-lot-controlled item then lot_optional_on_sample MUST be NULL
4378 IF (p_lot_ctl = 1) AND (p_lot_optional_on_sample IS NOT NULL)
4379 THEN
4380 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4381 'WHAT', 'For a non controlled item, lot_optional_on_sample must be NULL');
4382 RAISE FND_API.G_EXC_ERROR;
4383 END IF;
4384
4385 IF (p_VR_type IN ('INVENTORY', 'WIP','SUPPLIER'))
4386 THEN
4387 IF (p_lot_optional_on_sample IS NOT NULL ) THEN
4388 IF (p_sample_inv_trans_ind IS NOT NULL)
4389 THEN
4390 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4391 'WHAT', 'sample_inv_trans_ind must be NULL');
4392 RAISE FND_API.G_EXC_ERROR;
4393 END IF;
4394
4395 --RLNAGARA Bug # 4900420 Removed the validation code for control_lot_attributes
4396
4397 -- A special extra field for WIP :
4398 -- Bug# 5440347
4399 -- control_batch_step_ind is not dependent on lot_optional_on_sample.
4400 /*IF (p_VR_type = 'WIP')
4401 THEN
4402 IF (p_control_batch_step_ind IS NOT NULL)
4403 THEN
4404 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4405 'WHAT', 'control_batch_step_ind must be NULL');
4406 RAISE FND_API.G_EXC_ERROR;
4407 END IF;
4408 END IF;*/
4409 ELSE --p_lot_optional_on_sample IS NULL
4410
4411 --=========================================================================
4412 -- status_ctl :
4413 -- When the item is NOT status_ctl, then these fields MUST be NULL :
4414 -- control_lot_attrib_ind, in_spec_lot_status_id, out_of_spec_lot_status_id
4415 --=========================================================================
4416 IF (p_status_ctl = 'N')
4417 THEN
4418 --=========================================================================
4419 -- In this case, these fields MUST be NULL :
4420 -- control_lot_attrib_ind, in_spec_lot_status_id, out_of_spec_lot_status_id
4421 --=========================================================================
4422 IF (p_control_lot_attrib_ind IS NOT NULL)
4423 THEN
4424 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4425 'WHAT', 'control_lot_attrib_ind must be NULL');
4426 RAISE FND_API.G_EXC_ERROR;
4427 END IF;
4428
4429 IF (p_in_spec_lot_status_id IS NOT NULL)
4430 THEN
4431 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4432 'WHAT', 'in_spec_lot_status_id must be NULL');
4433 RAISE FND_API.G_EXC_ERROR;
4434 END IF;
4435
4436 IF (p_out_of_spec_lot_status_id IS NOT NULL)
4437 THEN
4438 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4439 'WHAT', 'out_of_spec_lot_status_id must be NULL');
4440 RAISE FND_API.G_EXC_ERROR;
4441 END IF;
4442 ELSE --p_status_ctl <> 'N'
4443 IF (p_control_lot_attrib_ind IS NULL)
4444 THEN
4445 --=========================================================================
4446 -- In this case, these fields MUST be NULL :
4447 -- in_spec_lot_status_id, out_of_spec_lot_status_id
4448 --=========================================================================
4449 IF (p_in_spec_lot_status_id IS NOT NULL)
4450 THEN
4451 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4452 'WHAT', 'in_spec_lot_status_id must be NULL');
4453 RAISE FND_API.G_EXC_ERROR;
4454 END IF;
4455
4456 IF (p_out_of_spec_lot_status_id IS NOT NULL)
4457 THEN
4458 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4459 'WHAT', 'out_of_spec_lot_status_id must be NULL');
4460 RAISE FND_API.G_EXC_ERROR;
4461 END IF;
4462 ELSE
4463 --=========================================================================
4464 -- In this case, control_lot_attrib_ind IS NOT NULL,
4465 -- then these fields are MANDATORY :
4466 -- in_spec_lot_status_id, out_of_spec_lot_status_id
4467 --=========================================================================
4468 -- Check the values of in_spec_lot_status_id and out_of_spec_lot_status_id
4469 IF (p_in_spec_lot_status_id IS NULL)
4470 THEN
4471 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4472 'WHAT', 'in_spec_lot_status_id is MANDATORY');
4473 RAISE FND_API.G_EXC_ERROR;
4474 ELSE
4475 OPEN c_lot_status(p_in_spec_lot_status_id);
4476 FETCH c_lot_status INTO dummy;
4477 IF (c_lot_status%NOTFOUND)
4478 THEN
4479 CLOSE c_lot_status;
4480 FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4481 FND_MESSAGE.SET_TOKEN('WHAT', 'IN_SPEC_LOT_STATUS_ID');
4482 FND_MESSAGE.SET_TOKEN('VALUE', p_in_spec_lot_status_id);
4483 FND_MSG_PUB.ADD;
4484 RAISE FND_API.G_EXC_ERROR;
4485 END IF;
4486 CLOSE c_lot_status;
4487 END IF; -- in_spec_lot_status IS NULL
4488
4489 IF (p_out_of_spec_lot_status_id IS NULL)
4490 THEN
4491 GMD_API_PUB.Log_Message('GMD_WRONG_VALUE',
4492 'WHAT', 'out_of_spec_lot_status_id is MANDATORY');
4493 RAISE FND_API.G_EXC_ERROR;
4494 ELSE
4495 OPEN c_lot_status(p_out_of_spec_lot_status_id);
4496 FETCH c_lot_status INTO dummy;
4497 IF (c_lot_status%NOTFOUND)
4498 THEN
4499 CLOSE c_lot_status;
4500 FND_MESSAGE.SET_NAME('GMD','GMD_NOTFOUND');
4501 FND_MESSAGE.SET_TOKEN('WHAT', 'OUT_OF_SPEC_LOT_STATUS_ID');
4502 FND_MESSAGE.SET_TOKEN('VALUE', p_out_of_spec_lot_status_id);
4503 FND_MSG_PUB.ADD;
4504 RAISE FND_API.G_EXC_ERROR;
4505 END IF;
4506 CLOSE c_lot_status;
4507 END IF; -- out_of_spec_lot_status_id IS NULL
4508 END IF; -- control_lot_attrib_ind IS NOT NULL
4509 END IF; -- status_ctl
4510 END IF; -- lot_optional_on_sample IS NOT NULL
4511 END IF; --- p_VR_type IN ('INVENTORY', 'WIP')
4512
4513 END check_VR_controls;
4514
4515 END GMD_SPEC_VRS_GRP;
[Home] [Help]