1 Package Body GMD_RECIPE_VAL AS
2 /* $Header: GMDRVALB.pls 120.0 2005/05/25 18:49:38 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_RECIPE_VAL';
5
6
7 /* Purpose: Validate entities within a recipe */
8 /* */
9 /* RECIPE_EXISTS in: id, name, version; out: id */
10 /* RECIPE_NAME in: name, version, action_code; out: id */
11 /* get_new_id in: null; out: next id in sequence */
12 /* RECIPE_FOR_UPDATE in: recipe_id; out: recipe_data, lock row */
13 /* RECIPE_DESCRIPTION in: description; out: success or failure */
14 /* RECIPE_ORGN_CODE */
15 /* PROCESS_LOSS_FOR_UPDATE in: recipe_id, orgn_code; out: lock row */
16 /* RECIPE_CUST_EXISTS in: recipe_id, customer_id; out: success or failure */
17 /* MODIFICATION HISTORY */
18 /* Sukarna Reddy dt 03/14/02. Bug 2099699. */
19 /* CHECK_ROUTING_VALIDITY :p_routing_id , p_recipe_status out: true or false */
20 /* Person Date Comments */
21 /* --------- ------ ------------------------------------------ */
22 /* LRJackson 08Nov2000 Created */
23
24 /* Standard parameters: */
25 /* IN: */
26 /* P_api_version - standard parameter */
27 /* P_init_msg_list - standard parameter (clear error msg list or not) */
28 /* P_commit - standard parameter. Should be FND_API.G_FALSE */
29 /* This procedure does no insert/update/delete */
30 /* P_validation_level - standard parameter */
31 /* OUT: */
32 /* x_return_status - standard parameter. S=success,E=expected error, */
33 /* U=unexpected error */
34 /* x_msg_count - standard parameter. Num of messages generated */
35 /* x_msg_data - standard parameter. If only1 msg, here it is */
36 /* x_return_code - num rows returned or SQLCODE (Database error number)*/
37
38 /* **************************************************************************/
39 /* NAME */
40 /* recipe_exists */
41 /* DESCRIPTION */
42 /* This procedure will check if given id or name and version exist in GMD_RECIPES. */
43 /* If name and vers provided, id will be returned. */
44 /* PARAMETERS standard + recipe_id, recipe_no, recipe_vers */
45 /* RETURN VALUES standard + recipe_id */
46 /* 24Jul2001 L.R.Jackson Added "AND recipe_no is null" clause. */
47 /**************************************************************************** */
48
49 PROCEDURE recipe_exists
50 ( p_api_version IN NUMBER,
51 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
52 p_commit IN VARCHAR2 := FND_API.G_FALSE,
53 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
54 p_recipe_id IN NUMBER,
55 p_recipe_no IN VARCHAR2,
56 p_recipe_version IN NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2,
60 x_return_code OUT NOCOPY NUMBER,
61 x_recipe_id OUT NOCOPY NUMBER)
62 IS
63 /* If recipe id alone is given */
64 /* OR */
65 /* If recipe_no and recipe_version are given. */
66 /* */
67 /* If all 3 are given, compare the recipe_id returned with the */
68 /* recipe_id given as parameter. */
69
70 CURSOR get_record_with_recipe_id(vRecipe_id NUMBER) IS
71 select recipe_id
72 from gmd_recipes_b
73 where recipe_id = vRecipe_id;
74
75 --Gjha 27-Dec-2004 Bug 4073815 . Removed the Upper of UPPER(recipe_no) to improve the performance. The uniqueness is
76 -- is to be maintained for case-sensitive Recipe_no and Recipe_version.
77 CURSOR get_record_without_recipe_id(vRecipe_no VARCHAR2
78 ,vRecipe_version NUMBER) IS
79 select recipe_id
80 from gmd_recipes_b
81 where recipe_no = vRecipe_no
82 and recipe_version = vRecipe_version;
83
84 /*** Variables ***/
85 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_EXISTS';
86 l_api_version CONSTANT NUMBER := 1.1;
87
88 BEGIN
89 /* no SAVEPOINT needed because there is no insert/update/delete */
90 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
91 l_api_name, G_PKG_NAME) THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 END IF;
94 IF FND_API.to_Boolean(p_init_msg_list) THEN
95 FND_MSG_PUB.initialize;
96 END IF;
97 x_return_status := FND_API.G_RET_STS_SUCCESS;
98
99 IF (p_recipe_id IS NOT NULL) THEN
100 OPEN get_record_with_recipe_id(p_recipe_id);
101 FETCH get_record_with_recipe_id into x_recipe_id;
102 IF get_record_with_recipe_id%NOTFOUND THEN
103 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_NOT_VALID');
104 FND_MSG_PUB.Add;
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107 CLOSE get_record_with_recipe_id;
108 ELSE
109 OPEN get_record_without_recipe_id(p_recipe_no, p_recipe_version);
110 FETCH get_record_without_recipe_id into x_recipe_id;
111 IF get_record_without_recipe_id%NOTFOUND THEN
112 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_NOT_VALID');
113 FND_MSG_PUB.Add;
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116 CLOSE get_record_without_recipe_id;
117 END IF;
118
119 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
120 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
121
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 X_return_code := SQLCODE;
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
127
128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
129 X_return_code := SQLCODE;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
132
133 WHEN OTHERS THEN
134 X_return_code := SQLCODE;
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
137
138 END recipe_exists;
139
140 /* ************************************************************************** */
141 /* NAME */
142 /* recipe_name */
143 /* DESCRIPTION */
144 /* This procedure will check if given name and version exist in GMD_RECIPES. */
145 /* If action_code = I and name+vers does not exist, success returned. */
146 /* If action_code = U and Nmae+vers exists, recipe_id will be returned */
147 /* PARAMETERS standard + recipe_no, recipe_vers, action_code=I(insert) or U(udpate) */
148 /* RETURN VALUES standard + recipe_id */
149 /**************************************************************************** */
150
151 PROCEDURE recipe_name
152 ( p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
154 p_commit IN VARCHAR2 := FND_API.G_FALSE,
155 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
156 p_recipe_no IN VARCHAR2,
157 p_recipe_version IN NUMBER,
158 p_action_code IN VARCHAR2 := 'U',
159 x_return_status OUT NOCOPY VARCHAR2,
160 x_msg_count OUT NOCOPY NUMBER,
161 x_msg_data OUT NOCOPY VARCHAR2,
162 x_return_code OUT NOCOPY NUMBER,
163 x_recipe_id OUT NOCOPY NUMBER)
164 IS
165 CURSOR get_record IS
166 select recipe_id
167 from gmd_recipes_b
168 where recipe_no = p_recipe_no
169 and recipe_version = p_recipe_version;
170
171 /*** Variables ***/
172 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_NAME';
173 l_api_version CONSTANT NUMBER := 1.0;
174
175 BEGIN
176 /* no SAVEPOINT needed because there is no insert/update/delete */
177 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
178 l_api_name, G_PKG_NAME) THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181 IF FND_API.to_Boolean(p_init_msg_list) THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185
186 OPEN get_record;
187 FETCH get_record into x_recipe_id;
188
189 IF P_action_code = 'I' THEN
190 IF get_record%FOUND THEN
191 RAISE fnd_api.g_exc_error;
192 END IF; /* end if record not found */
193 ELSIF p_action_code = 'U' THEN
194 IF get_record%NOTFOUND THEN
195 RAISE fnd_api.g_exc_error;
196 END IF;
197 ELSE
198 RAISE fnd_api.g_exc_error;
199 END IF; /* end if action code is insert or update */
200
201 CLOSE get_record;
202
203 /* no standard check of p_commit because no insert/update/delete */
204
205 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
206 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
207
208 EXCEPTION
209 WHEN FND_API.G_EXC_ERROR THEN
210 X_return_code := SQLCODE;
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
213
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 X_return_code := SQLCODE;
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
218
219 WHEN OTHERS THEN
220 X_return_code := SQLCODE;
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
223
224 END recipe_name;
225
226 /* **************************************************************************/
227 /* NAME */
228 /* get_new_id */
229 /* DESCRIPTION */
230 /* This procedure will */
231 /* */
232 /* PARAMETERS (other than standard parameters) */
233 /* */
234 /* RETURN VALUES (other than standard return values) */
235 /* recipe_id */
236 /* */
237 /* Person Date Comments */
238 /* --------- ------ ------------------------------------------ */
239 /* LRJackson 14Nov2000 Created */
240 /**************************************************************************** */
241 PROCEDURE get_new_id
242 ( p_api_version IN NUMBER,
243 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
244 p_commit IN VARCHAR2 := FND_API.G_FALSE,
245 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER,
248 x_msg_data OUT NOCOPY VARCHAR2,
249 x_return_code OUT NOCOPY NUMBER,
250 x_recipe_id OUT NOCOPY NUMBER)
251 IS
252 CURSOR get_new_id IS
253 select gmd_recipe_id_s.NEXTVAL
254 from dual;
255
256 /*** Variables ***/
257 l_api_name CONSTANT VARCHAR2(30) := 'GET_NEW_ID';
258 l_api_version CONSTANT NUMBER := 1.0;
259
260 BEGIN
261 /* no SAVEPOINT needed because there is no insert/update/delete */
262 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
263 l_api_name, G_PKG_NAME) THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266 IF FND_API.to_Boolean(p_init_msg_list) THEN
267 FND_MSG_PUB.initialize;
268 END IF;
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270
271 OPEN get_new_id;
272 FETCH get_new_id into x_recipe_id;
273 CLOSE get_new_id;
274
275 /* no standard check of p_commit because no insert/update/delete */
276 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
277 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
278
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 X_return_code := SQLCODE;
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
284
285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
286 X_return_code := SQLCODE;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
289
290 WHEN OTHERS THEN
291 X_return_code := SQLCODE;
292 x_return_status := FND_API.G_RET_STS_ERROR;
293 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
294
295 END get_new_id;
296
297
298 /* **************************************************************************/
299 /* NAME */
300 /* recipe_for_update */
301 /* DESCRIPTION */
302 /* This procedure will */
303 /* */
304 /* PARAMETERS (other than standard parameters) */
305 /* */
306 /* RETURN VALUES (other than standard return values) */
307 /*
308 /* Person Date Comments */
309 /* --------- ------ ------------------------------------------ */
310 /* LRJackson 14Nov2000 Created */
311 /* LRJackson 27Dec2000 Updated parameters */
312 /**************************************************************************** */
313 PROCEDURE recipe_for_update
314 ( p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
316 p_commit IN VARCHAR2 := FND_API.G_FALSE,
317 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
318 p_recipe_id IN NUMBER,
319 p_last_update_date IN DATE,
320 p_form_or_asynch IN VARCHAR2 := 'A',
321 x_return_status OUT NOCOPY VARCHAR2,
322 x_msg_count OUT NOCOPY NUMBER,
323 x_msg_data OUT NOCOPY VARCHAR2,
324 x_return_code OUT NOCOPY NUMBER)
325 IS
326 CURSOR get_recipe_data IS
327 select last_update_date
328 from gmd_recipes
329 where recipe_id = p_recipe_id;
330
331 /*** Variables ***/
332 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_FOR_UPDATE';
333 l_api_version CONSTANT NUMBER := 1.0;
334 l_update_date DATE;
335
336 BEGIN
337 /* no SAVEPOINT needed because there is no insert/update/delete */
338 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
339 l_api_name, G_PKG_NAME) THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342 IF FND_API.to_Boolean(p_init_msg_list) THEN
343 FND_MSG_PUB.initialize;
344 END IF;
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346
347 OPEN get_recipe_data;
348 FETCH get_recipe_data into l_update_date;
349 IF get_recipe_data%NOTFOUND THEN
350 RAISE fnd_api.g_exc_error;
351 ELSE
352 IF p_last_update_date is NULL OR l_update_date <> p_last_update_date THEN
353 RAISE fnd_api.g_exc_error;
354 ELSE
355 IF p_form_or_asynch = 'A' THEN
356 NULL;
357 /* need to lock record here */
358 END IF; /* end if this procedure called asynchronously */
359 END IF; /* end if update dates do not match */
360 END IF; /* end if record not found */
361 CLOSE get_recipe_data;
362
363 /* no standard check of p_commit because no insert/update/delete */
364 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
365 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 X_return_code := SQLCODE;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
372
373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
374 X_return_code := SQLCODE;
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
377
378 WHEN OTHERS THEN
379 X_return_code := SQLCODE;
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
382
383 END recipe_for_update;
384
385
386 /* **************************************************************************/
387 /* NAME */
388 /* recipe_description */
389 /* DESCRIPTION */
390 /* This procedure will */
391 /* */
392 /* PARAMETERS (other than standard parameters) */
393 /* */
394 /* RETURN VALUES (other than standard return values) */
395 /* */
396 /* Person Date Comments */
397 /* --------- ------ ------------------------------------------ */
398 /* LRJackson 14Nov2000 Created */
399 /**************************************************************************** */
400 PROCEDURE recipe_description
401 ( p_api_version IN NUMBER,
402 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
404 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
405 p_recipe_description IN VARCHAR2,
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2,
409 x_return_code OUT NOCOPY NUMBER)
410 IS
411
412 /*** Variables ***/
413 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_DESCRIPTION';
414 l_api_version CONSTANT NUMBER := 1.0;
415
416 BEGIN
417 /* no SAVEPOINT needed because there is no insert/update/delete */
418 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
419 l_api_name, G_PKG_NAME) THEN
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END IF;
422 IF FND_API.to_Boolean(p_init_msg_list) THEN
423 FND_MSG_PUB.initialize;
424 END IF;
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426
427 IF p_recipe_description IS NULL THEN
428 RAISE FND_API.G_EXC_ERROR;
429 END IF;
430
431 /* no standard check of p_commit because no insert/update/delete */
432 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
433 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
434
435 EXCEPTION
436 WHEN FND_API.G_EXC_ERROR THEN
437 X_return_code := SQLCODE;
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
440
441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
442 X_return_code := SQLCODE;
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
444 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
445
446 WHEN OTHERS THEN
447 X_return_code := SQLCODE;
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
450
451 END recipe_description;
452
453 /* **************************************************************************/
454 /* NAME */
455 /* recipe_orgn_code */
456 /* DESCRIPTION */
457 /* This procedure will validate that a given orgn_code is a plant or a lab */
458 /* and that it is associated with the given user */
459 /* */
460 /* PARAMETERS (other than standard parametrs) */
461 /* orgn_code, user_id, required_ind */
462 /* */
463 /* RETURN VALUES (other than standard return values) */
464 /* */
465 /* Person Date Comments */
466 /* --------- ------ ------------------------------------------ */
467 /* LRJackson 21Dec2000 Created */
468 /**************************************************************************** */
469 PROCEDURE recipe_orgn_code
470 ( p_api_version IN NUMBER,
471 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
472 p_commit IN VARCHAR2 := FND_API.G_FALSE,
473 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
474 g_orgn_id IN NUMBER,
475 g_user_id IN NUMBER,
476 p_required_ind IN VARCHAR2 := 'N',
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2,
480 x_return_code OUT NOCOPY NUMBER,
481 x_plant_ind OUT NOCOPY NUMBER,
482 x_lab_ind OUT NOCOPY NUMBER)
483 IS
484 /* do the following cursors in 2 steps (rather than combine the cursors into 1) */
485 /* so that error messages can be more specific */
486
487 l_resp_id NUMBER(15) DEFAULT FND_PROFILE.VALUE('RESP_ID');
488
489 CURSOR get_orgn_code IS
490 select plant_ind, lab_ind
491 from gmd_parameters_hdr
492 where organization_id = g_orgn_id;
493
494 CURSOR get_user_orgn (vresp_id NUMBER) IS
495 SELECT 1
496 FROM org_access_view
497 WHERE responsibility_id = vresp_id
498 AND organization_id = g_orgn_id;
499
500
501 /*** Variables ***/
502 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_ORGN_CODE';
503 l_api_version CONSTANT NUMBER := 1.0;
504 v_temp NUMBER;
505 BEGIN
506 /* no SAVEPOINT needed because there is no insert/update/delete */
507 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
508 l_api_name, G_PKG_NAME) THEN
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 IF FND_API.to_Boolean(p_init_msg_list) THEN
513 FND_MSG_PUB.initialize;
514 END IF;
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516
517 IF (p_required_ind = 'Y' and g_orgn_id is NULL) THEN
518 RAISE fnd_api.g_exc_error;
519 ELSIF (p_required_ind = 'N' and g_orgn_id is not NULL) THEN
520 /* if orgn code is null and orgn code is not required, then */
521 /* no further validation is necessary */
522 OPEN get_orgn_code;
523 FETCH get_orgn_code into x_plant_ind,x_lab_ind;
524 IF get_orgn_code%NOTFOUND THEN
525 RAISE fnd_api.g_exc_error;
526 END IF;
527 CLOSE get_orgn_code;
528
529 OPEN get_user_orgn (l_resp_id);
530 FETCH get_user_orgn into v_temp;
531 IF get_user_orgn%NOTFOUND THEN
532 RAISE fnd_api.g_exc_error;
533 /* (need an appropriate error message here for user not assoc with orgn) */
534 ELSE
535 IF (x_plant_ind <> 1 AND x_lab_ind <> 1) THEN --Krishna conv
536 RAISE FND_API.g_exc_error;
537 /* (need an appropriate error message here for not a plant or lab) */
538
539 END IF; /* end if plant ind is plant or lab */
540 END IF; /* end if user associated with this orgn */
541
542 CLOSE get_user_orgn;
543 END IF; /* end if orgn code is null or not */
544
545 /* no standard check of p_commit because no insert/update/delete */
546 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
547 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
548
549 EXCEPTION
550 WHEN FND_API.G_EXC_ERROR THEN
551 X_return_code := SQLCODE;
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
554
555 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
556 X_return_code := SQLCODE;
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
559
560 WHEN OTHERS THEN
561 X_return_code := SQLCODE;
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
564
565 END recipe_orgn_code;
566
567 /* **************************************************************************/
568 /* NAME */
569 /* process_loss_for_update */
570 /* DESCRIPTION */
571 /* This procedure will */
572 /* */
573 /* PARAMETERS (other than standard parameters) */
574 /* */
575 /* RETURN VALUES (other than standard return values) */
576 /* */
577 /* Person Date Comments */
578 /* --------- ------ ------------------------------------------ */
579 /* LRJackson 14Nov2000 Created */
580 /**************************************************************************** */
581 PROCEDURE process_loss_for_update
582 ( p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
584 p_commit IN VARCHAR2 := FND_API.G_FALSE,
585 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
586 p_recipe_id IN NUMBER,
587 p_orgn_id IN NUMBER,
588 p_last_update_date IN DATE,
589 p_form_or_asynch IN VARCHAR2 := 'A',
590 x_return_status OUT NOCOPY VARCHAR2,
591 x_msg_count OUT NOCOPY NUMBER,
592 x_msg_data OUT NOCOPY VARCHAR2,
593 x_return_code OUT NOCOPY NUMBER)
594 IS
595 CURSOR check_recipe_id IS
596 select last_update_date
597 from gmd_recipe_process_loss
598 where recipe_id = p_recipe_id
599 and organization_id = p_orgn_id;
600
601 /*** Variables ***/
602 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_LOSS_FOR_UPDATE';
603 l_api_version CONSTANT NUMBER := 1.0;
604 l_update_date DATE;
605
606 BEGIN
607 /* no SAVEPOINT needed because there is no insert/update/delete */
608 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
609 l_api_name, G_PKG_NAME) THEN
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END IF;
612 IF FND_API.to_Boolean(p_init_msg_list) THEN
613 FND_MSG_PUB.initialize;
614 END IF;
615 x_return_status := FND_API.G_RET_STS_SUCCESS;
616
617 OPEN check_recipe_id;
618 FETCH check_recipe_id into l_update_date;
619 IF check_recipe_id%NOTFOUND THEN
620 RAISE fnd_api.g_exc_error;
621 ELSE
622 IF p_last_update_date is NULL OR l_update_date <> p_last_update_date THEN
623 RAISE fnd_api.g_exc_error;
624 ELSE
625 IF p_form_or_asynch = 'A' THEN
626 NULL;
627 /* need to lock record here */
628 END IF; /* end if this procedure called asynchronously */
629 END IF; /* end if update dates do not match */
630 END IF; /* end if record not found */
631
632 CLOSE check_recipe_id;
633
634 /* no standard check of p_commit because no insert/update/delete */
635 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
636 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
637
638 EXCEPTION
639 WHEN FND_API.G_EXC_ERROR THEN
640 X_return_code := SQLCODE;
641 x_return_status := FND_API.G_RET_STS_ERROR;
642 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
643
644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
645 X_return_code := SQLCODE;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
648
649 WHEN OTHERS THEN
650 X_return_code := SQLCODE;
651 x_return_status := FND_API.G_RET_STS_ERROR;
652 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
653
654 END process_loss_for_update;
655
656 /* **************************************************************************/
657 /* NAME */
658 /* recipe_cust_exists */
659 /* DESCRIPTION */
660 /* This procedure will check if given id or name and version exist in */
661 /* GMD_RECIPE_CUSTOMERSS. */
662 /* PARAMETERS standard + recipe_id, customer_id */
663 /* RETURN VALUES standard */
664 /**************************************************************************** */
665
666 PROCEDURE recipe_cust_exists
667 ( p_api_version IN NUMBER,
668 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
669 p_commit IN VARCHAR2 := FND_API.G_FALSE,
670 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
671 p_recipe_id IN NUMBER,
672 p_customer_id IN NUMBER,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2,
676 x_return_code OUT NOCOPY NUMBER)
677 IS
678 CURSOR get_record IS
679 select recipe_id
680 from gmd_recipe_customers
681 where recipe_id = p_recipe_id
682 and customer_id = p_customer_id;
683
684 /*** Variables ***/
685 l_api_name CONSTANT VARCHAR2(30) := 'RECIPE_CUST_EXISTS';
686 l_api_version CONSTANT NUMBER := 1.0;
687 l_recipe_id gmd_recipes.recipe_id%TYPE;
688
689 BEGIN
690 /* no SAVEPOINT needed because there is no insert/update/delete */
691 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
692 l_api_name, G_PKG_NAME) THEN
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 END IF;
695 IF FND_API.to_Boolean(p_init_msg_list) THEN
696 FND_MSG_PUB.initialize;
697 END IF;
698 x_return_status := FND_API.G_RET_STS_SUCCESS;
699
700 OPEN get_record;
701 FETCH get_record into l_recipe_id;
702
703 IF get_record%NOTFOUND THEN
704 RAISE fnd_api.g_exc_error;
705 END IF; /* end if record not found */
706
707 CLOSE get_record;
708
709 /* no standard check of p_commit because no insert/update/delete */
710
711 /* standard call to get msge cnt, and if cnt is 1, get mesg info */
712 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
713
714 EXCEPTION
715 WHEN FND_API.G_EXC_ERROR THEN
716 X_return_code := SQLCODE;
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
719
720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
721 X_return_code := SQLCODE;
722 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
723 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
724
725 WHEN OTHERS THEN
726 X_return_code := SQLCODE;
727 x_return_status := FND_API.G_RET_STS_ERROR;
728 FND_MSG_PUB.Count_And_Get(p_count=>x_msg_count, p_data=>x_msg_data);
729
730 END recipe_cust_exists;
731
732 /* **************************************************************************
733 NAME
734 check_routing_validity
735 DESCRIPTION
736 This procedure will validate if routing attached to a recipe is of valid
737 status or not.
738 PARAMETERS p_routing_id
739 p_recipe_status
740 RETURN VALUES TRUE - if valid
741 FALSE - If invalid
742 author
743 Sukarna Reddy bug 2099699. dt 03/14/02.
744 Ravi S Reddy bug 2402946. dt 06/24/02
745 Deleted status type 900 so that recipe can be created with
746 frozen routings.
747 **************************************************************************** */
748
749
750 FUNCTION check_routing_validity(p_routing_id NUMBER,
751 p_recipe_status VARCHAR2) RETURN BOOLEAN IS
752 CURSOR Cur_rtstatus_vldty IS
753 SELECT COUNT(*)
754 FROM gmd_routings_b h,gmd_status s
755 WHERE h.routing_id = p_routing_id AND
756 h.routing_status = s.status_code AND
757 to_number(h.routing_status) >= to_number(p_recipe_status) AND
758 -- Begin Bug#2402946 Ravi S Reddy
759 -- Deleted Status_Type 900
760 s.status_type NOT IN ('800','1000');
761 -- End Bug#2402946
762 l_count NUMBER := 0;
763 BEGIN
764 IF (p_routing_id IS NOT NULL) THEN
765 OPEN Cur_rtstatus_vldty;
766 FETCH Cur_rtstatus_vldty INTO l_count;
767 CLOSE Cur_rtstatus_vldty;
768 IF (l_count = 0) THEN
769 RETURN FALSE;
770 ELSE
771 RETURN TRUE;
772 END IF;
773 ELSE
774 RETURN FALSE;
775 END IF;
776 END check_routing_validity;
777
778
779 /*#####################################################
780 # NAME
781 # validate_start_date
782 # SYNOPSIS
783 # Proc validate_start_date
784 # DESCRIPTION
785 # This procedure validates that start date is no earlier
786 # than any routing start date.
787 # HISTORY
788 #####################################################*/
789 PROCEDURE validate_start_date (P_disp_start_date Date,
790 P_routing_start_date Date,
791 x_return_status OUT NOCOPY VARCHAR2) IS
792 l_api_name VARCHAR2(100) := 'validate_start_date' ;
793 BEGIN
794 x_return_status := 'S';
795
796 IF P_disp_start_date < P_routing_start_date THEN
797 FND_MESSAGE.SET_NAME('GMD','GMD_VALIDITY_DATE_IN_ROUT_DATE');
798 FND_MSG_PUB.ADD;
799 x_return_status := 'E';
800 END IF;
801
802 EXCEPTION
803 WHEN OTHERS THEN
804 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
805 x_return_status := FND_API.g_ret_sts_unexp_error;
806 END validate_start_date;
807
808 /*#####################################################
809 # NAME
810 # validate_end_date
811 # SYNOPSIS
812 # Proc validate_end_date
813 # DESCRIPTION
814 # This procedure validates that end date is no later
815 # than any routing end date.
816 # Also validates date entered against sys max date.
817 # HISTORY
818 #####################################################*/
819 PROCEDURE validate_end_date (P_end_date Date,
820 P_routing_end_date Date,
821 x_return_status OUT NOCOPY VARCHAR2) IS
822 l_api_name VARCHAR2(100) := 'validate_end_date' ;
823 BEGIN
824 x_return_status := 'S';
825 IF (P_end_date IS NOT NULL) AND
826 (P_routing_end_date IS NOT NULL) AND
827 (P_end_date > P_routing_end_date) THEN
828 FND_MESSAGE.SET_NAME('GMD','GMD_VALIDITY_DATE_IN_ROUT_DATE');
829 FND_MSG_PUB.ADD;
830 x_return_status := 'E';
831 END IF;
832
833 -- Routing end date is finite but Vr end date is infinite
834 IF (P_routing_end_date IS NOT NULL) AND
835 (P_end_date IS NULL) THEN
836 FND_MESSAGE.SET_NAME('GMD','GMD_VALIDITY_DATE_IN_ROUT_DATE');
837 FND_MSG_PUB.ADD;
838 x_return_status := 'E';
839 END IF;
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
844 x_return_status := FND_API.g_ret_sts_unexp_error;
845 END validate_end_date;
846
847
848 /*#####################################################
849 # NAME
850 # effective_dates
851 # SYNOPSIS
852 # Proc effective_dates
853 # DESCRIPTION
854 # Validates dates to be within proper ranges.
855 # HISTORY
856 #####################################################*/
857 PROCEDURE effective_dates ( P_start_date DATE,
858 P_end_date DATE,
859 x_return_status OUT NOCOPY VARCHAR2) IS
860 l_api_name VARCHAR2(100) := 'effective_dates' ;
861 BEGIN
862 x_return_status := 'S';
863
864 IF (P_end_date IS NOT NULL AND P_start_date IS NOT NULL) THEN
865 IF (P_end_date < P_start_date) THEN
866 FND_MESSAGE.SET_NAME('GMD', 'QC_MIN_MAX_DATE');
867 FND_MSG_PUB.ADD;
868 x_return_status := 'E';
869 END IF;
870 END IF;
871 EXCEPTION
872 WHEN OTHERS THEN
873 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
874 x_return_status := FND_API.g_ret_sts_unexp_error;
875 END effective_dates;
876
877 /*###################################################################
878 # NAME
879 # std_qty
880 # SYNOPSIS
881 # proc std_qty
882 # Called from when-val-record trigger
883 # DESCRIPTION
884 # Checks for std_qty is in between min_qty and max_qty
885 # Std qty cannot be negative
886 #
887 ###################################################################*/
888 PROCEDURE std_qty(P_std_qty NUMBER,
889 P_min_qty NUMBER,
890 P_max_qty NUMBER,
891 x_return_status OUT NOCOPY VARCHAR2) IS
892 l_api_name VARCHAR2(100) := 'std_qty' ;
893 BEGIN
894 x_return_status := 'S';
895 IF P_std_qty IS NOT NULL THEN
896 IF (P_std_qty < P_min_qty
897 OR P_std_qty > P_max_qty)
898 OR P_std_qty <= 0 THEN
899 IF P_std_qty <= 0 THEN
900 FND_MESSAGE.SET_NAME('GMD','FM_INV_STD_QTY');
901 FND_MSG_PUB.ADD;
902 x_return_status := 'E';
903 ELSE
904 FND_MESSAGE.SET_NAME('GMD','FM_INV_STD_RANGE');
905 FND_MSG_PUB.ADD;
906 x_return_status := 'E';
907 END IF; -- end if std qty is the problem, or the range
908 END IF; -- end if std qty not within range
909 END IF; -- end if std qty is not null
910 EXCEPTION
911 WHEN OTHERS THEN
912 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
913 x_return_status := FND_API.g_ret_sts_unexp_error;
914 END std_qty;
915
916 /*#####################################################
917 # NAME
918 # max_qty
919 # SYNOPSIS
920 # proc max_qty
921 # Called from when-val-record trigger
922 # DESCRIPTION
923 # Checks for max_qty is greater than min_qty
924 #
925 #######################################################*/
926 PROCEDURE max_qty(P_min_qty NUMBER,
927 P_max_qty NUMBER,
928 x_return_status OUT NOCOPY VARCHAR2) IS
929 l_api_name VARCHAR2(100) := 'max_qty' ;
930 BEGIN
931 x_return_status := 'S';
932 IF P_max_qty IS NOT NULL THEN
933 IF (P_max_qty < P_min_qty
934 OR P_min_qty < 0) THEN
935 IF P_min_qty < 0 THEN
936 FND_MESSAGE.SET_NAME('GMD','FM_INV_MIN_QTY');
937 FND_MSG_PUB.ADD;
938 x_return_status := 'E';
939 ELSE
940 FND_MESSAGE.SET_NAME('GMD','FM_INV_MIN_MAX');
941 FND_MSG_PUB.ADD;
942 x_return_status := 'E';
943 END IF; -- end if qty is the problem, or the range
944 END IF; -- IF (P_max_qty < P_min_qty
945 END IF; -- IF P_max_qty IS NOT NULL
946 EXCEPTION
947 WHEN OTHERS THEN
948 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
949 x_return_status := FND_API.g_ret_sts_unexp_error;
950 END max_qty;
951
952
953 /*#####################################################
954 # NAME
955 # calc_inv_qtys
956 # SYNOPSIS
957 # proc calc_inv_qtys
958 # Parms
959 # DESCRIPTION
960 # Checks for item_uom with standard item UOM, if different
961 # Converts the quantity from the initial UOM to the
962 # final UOM.
963 #######################################################*/
964 PROCEDURE calc_inv_qtys (P_inv_item_um VARCHAR2,
965 P_item_um VARCHAR2,
966 P_item_id NUMBER,
967 P_min_qty NUMBER,
968 P_max_qty NUMBER,
969 X_inv_min_qty OUT NOCOPY NUMBER,
970 X_inv_max_qty OUT NOCOPY NUMBER,
971 x_return_status OUT NOCOPY VARCHAR2
972 ) IS
973 l_api_name VARCHAR2(100) := 'calc_inv_qtys' ;
974 BEGIN
975 x_return_status := 'S';
976
977 IF P_inv_item_um = P_item_um THEN
978 X_inv_min_qty := P_min_qty;
979 X_inv_max_qty := P_max_qty;
980 ELSE
981 /*########################################################
982 # Stored Procedure call made here for the UOM conversion
983 # between two different UOM's
984 #########################################################*/
985 X_inv_min_qty := INV_CONVERT.inv_um_convert(item_id => P_item_id
986 ,precision => 5
987 ,from_quantity => P_min_qty
988 ,from_unit => P_item_um
989 ,to_unit => P_inv_item_um
990 ,from_name => NULL
991 ,to_name => NULL);
992
993 X_inv_max_qty := INV_CONVERT.inv_um_convert(item_id => P_item_id
994 ,precision => 5
995 ,from_quantity => P_max_qty
996 ,from_unit => P_item_um
997 ,to_unit => P_inv_item_um
998 ,from_name => NULL
999 ,to_name => NULL);
1000
1001 END IF;
1002 X_inv_min_qty := ROUND(X_inv_min_qty,5);
1003 X_inv_max_qty := ROUND(X_inv_max_qty,5);
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
1007 x_return_status := FND_API.g_ret_sts_unexp_error;
1008 END calc_inv_qtys;
1009
1010 /*#####################################################
1011 # NAME
1012 # calculate_process_loss
1013 # SYNOPSIS
1014 # Proc calculate_process_loss
1015 # DESCRIPTION
1016 # derives theoretical and planned process loss
1017 #####################################################*/
1018 PROCEDURE calculate_process_loss( V_assign IN NUMBER DEFAULT 1
1019 ,P_vr_id IN NUMBER
1020 ,X_TPL OUT NOCOPY NUMBER
1021 ,X_PPL OUT NOCOPY NUMBER
1022 ,x_return_status OUT NOCOPY VARCHAR2) IS
1023
1024 process_loss_rec GMD_COMMON_VAL.process_loss_rec;
1025 l_process_loss GMD_PROCESS_LOSS.process_loss%TYPE;
1026 l_recipe_theo_loss GMD_PROCESS_LOSS.process_loss%TYPE;
1027 x_msg_cnt NUMBER;
1028 x_msg_dat VARCHAR2(2000);
1029
1030 l_std_qty gmd_recipe_validity_rules.std_qty%TYPE;
1031 l_detail_uom gmd_recipe_validity_rules.detail_uom%TYPE;
1032 l_inventory_item_id gmd_recipe_validity_rules.inventory_item_id%TYPE;
1033 l_organization_id gmd_recipe_validity_rules.organization_id%TYPE;
1034
1035 CURSOR get_other_vr_details(V_vr_id NUMBER) IS
1036 SELECT std_qty, inventory_item_id, detail_uom, organization_id
1037 FROM gmd_recipe_validity_rules
1038 WHERE recipe_validity_rule_id = V_vr_id;
1039
1040 l_api_name VARCHAR2(100) := 'calculate_process_loss' ;
1041
1042 BEGIN
1043 x_return_status := 'S';
1044
1045 OPEN get_other_vr_details(p_vr_id);
1046 FETCH get_other_vr_details INTO l_std_qty, l_inventory_item_id, l_detail_uom, l_organization_id;
1047 CLOSE get_other_vr_details;
1048
1049 process_loss_rec.validity_rule_id := p_vr_id;
1050 process_loss_rec.qty := l_std_qty;
1051 process_loss_rec.uom := l_detail_uom;
1052 process_loss_rec.organization_id := l_organization_id;
1053 process_loss_rec.inventory_item_id := l_inventory_item_id;
1054
1055 gmd_common_val.calculate_process_loss(process_loss => process_loss_rec,
1056 Entity_type => 'VALIDITY',
1057 x_recipe_theo_loss => X_TPL,
1058 x_process_loss => X_PPL,
1059 x_return_status => x_return_status,
1060 x_msg_count => X_msg_cnt,
1061 x_msg_data => X_msg_dat);
1062
1063 X_TPL := TRUNC(X_TPL,2);
1064 X_PPL := TRUNC(X_PPL,2);
1065
1066 IF (V_assign = 1) THEN
1067 IF X_PPL IS NULL THEN
1068 X_PPL := X_TPL;
1069 END IF;
1070 END IF;
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
1074 x_return_status := FND_API.g_ret_sts_unexp_error;
1075 END calculate_process_loss;
1076
1077 /*#####################################################
1078 # NAME
1079 # check_for_duplicate
1080 # SYNOPSIS
1081 # Proc check_for_duplicate
1082 # Parms
1083 # DESCRIPTION
1084 # check duplication of record
1085 #####################################################*/
1086 PROCEDURE check_for_duplicate(pRecipe_id NUMBER
1087 ,pitem_id NUMBER
1088 ,pOrgn_id NUMBER DEFAULT NULL
1089 ,pRecipe_Use NUMBER
1090 ,pPreference NUMBER
1091 ,pstd_qty NUMBER
1092 ,pmin_qty NUMBER
1093 ,pmax_qty NUMBER
1094 ,pinv_max_qty NUMBER
1095 ,pinv_min_qty NUMBER
1096 ,pitem_um VARCHAR2
1097 ,pValidity_Rule_Status VARCHAR2
1098 ,pstart_date DATE
1099 ,pend_date DATE DEFAULT NULL
1100 ,pPlanned_process_loss NUMBER DEFAULT NULL
1101 ,x_return_status OUT NOCOPY VARCHAR2
1102 ) IS
1103 CURSOR Cur_check_dup_upd IS
1104 SELECT recipe_validity_rule_id
1105 FROM gmd_recipe_validity_rules
1106 WHERE recipe_id = pRecipe_id
1107 AND inventory_item_id = pitem_id
1108 AND ((organization_id = pOrgn_id) OR
1109 (organization_id IS NULL AND pOrgn_id is NULL))
1110 AND recipe_use = pRecipe_Use
1111 AND preference = pPreference
1112 AND std_qty = pstd_qty
1113 AND min_qty = pmin_qty
1114 AND max_qty = pmax_qty
1115 AND inv_max_qty = pinv_max_qty
1116 AND inv_min_qty = pinv_min_qty
1117 AND detail_uom = pitem_um
1118 AND validity_rule_status = pValidity_Rule_status
1119 AND ((pPlanned_process_loss IS NULL AND Planned_process_loss IS NULL) OR
1120 (planned_process_loss = pPlanned_process_loss))
1121 AND start_date = pstart_date
1122 AND ((end_date = pend_date) OR (end_date is NULL and pend_date is NULL));
1123
1124 l_api_name VARCHAR2(100) := 'check_for_duplicate' ;
1125 BEGIN
1126 x_return_status := 'S';
1127 FOR VR_dup_rec IN Cur_check_dup_upd LOOP
1128 FND_MESSAGE.SET_NAME('GMD','GMD_DUP_VR_EXIST');
1129 FND_MSG_PUB.ADD;
1130 x_return_status := 'E';
1131 END LOOP;
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 fnd_msg_pub.add_exc_msg (m_pkg_name, l_api_name);
1135 x_return_status := FND_API.g_ret_sts_unexp_error;
1136 END check_for_duplicate;
1137
1138
1139 END; /* Package Body GMD_RECIPE_VAL */
[Home] [Help]