1 PACKAGE BODY gmd_api_grp AS
2 /* $Header: GMDGAPIB.pls 120.35.12020000.3 2012/09/29 09:19:46 yanpewan ship $ */
3
4
5 l_package_name CONSTANT VARCHAR2 (30) := 'GMD_API_GRP';
6 l_resp_id NUMBER := FND_PROFILE.VALUE('RESP_ID');
7 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
8 --Forward declaration.
9 FUNCTION set_debug_flag RETURN VARCHAR2;
10 l_debug VARCHAR2(1) := set_debug_flag;
11
12 FUNCTION set_debug_flag RETURN VARCHAR2 IS
13 l_debug VARCHAR2(1):= 'N';
14 BEGIN
15 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
16 l_debug := 'Y';
17 END IF;
18 RETURN l_debug;
19 END set_debug_flag;
20 --Bug 3222090, NSRIVAST 20-FEB-2004, END
21
22 /*======================================================================
23 -- PROCEDURE :
24 -- log_message
25 --
26 -- DESCRIPTION:
27 -- This particular procedure is used to add messages to the stack.
28 -- REQUIREMENTS
29 --
30 -- SYNOPSIS:
31 --
32 --===================================================================== */
33
34 PROCEDURE log_message (
35 p_message_code IN VARCHAR2
36 ,p_token1_name IN VARCHAR2 := NULL
37 ,p_token1_value IN VARCHAR2 := NULL
38 ,p_token2_name IN VARCHAR2 := NULL
39 ,p_token2_value IN VARCHAR2 := NULL
40 ,p_token3_name IN VARCHAR2 := NULL
41 ,p_token3_value IN VARCHAR2 := NULL) IS
42 BEGIN
43 fnd_message.set_name ('GMD', p_message_code);
44
45 IF p_token1_name IS NOT NULL THEN
46 fnd_message.set_token (p_token1_name, p_token1_value);
47
48 IF p_token2_name IS NOT NULL THEN
49 fnd_message.set_token (p_token2_name, p_token2_value);
50
51 IF p_token3_name IS NOT NULL THEN
52 fnd_message.set_token (p_token3_name, p_token3_value);
53 END IF;
54 END IF;
55 END IF;
56
57 fnd_msg_pub.ADD;
58 EXCEPTION
59 WHEN OTHERS THEN
60 gmd_debug.put_line ('GMD_API_GRP.log_message: When others exception: '||SQLERRM);
61 END log_message;
62
63 /*======================================================================
64 -- PROCEDURE :
65 -- setup
66 --
67 -- DESCRIPTION:
68 -- This particular procedure is used to set the global package
69 -- variables.
70 -- REQUIREMENTS
71 --
72 -- SYNOPSIS:
73 --
74 --===================================================================== */
75
76 FUNCTION setup
77 RETURN BOOLEAN IS
78 missing_profile_option EXCEPTION;
79 BEGIN
80 gmd_api_grp.login_id := TO_NUMBER (fnd_profile.VALUE ('LOGIN_ID'));
81 gmd_api_grp.user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
82 gmd_api_grp.resp_id := TO_NUMBER (fnd_profile.VALUE ('RESP_ID'));
83 /*bug 14108934, change nvl value to -1 instead of 0, 0 is the userid of seeded login SYSADMIN*/
84 IF NVL (gmd_api_grp.user_id, -1) = -1 THEN
85 log_message (
86 'GMD_API_INVALID_USER_ID'
87 ,'USER_ID'
88 ,gmd_api_grp.user_id);
89 RAISE missing_profile_option;
90 END IF;
91
92 RETURN TRUE;
93 EXCEPTION
94 WHEN missing_profile_option THEN
95 RETURN FALSE;
96 WHEN NO_DATA_FOUND THEN
97 log_message ('UNABLE_TO_LOAD_UOM');
98 RETURN FALSE;
99 WHEN OTHERS THEN
100 fnd_msg_pub.add_exc_msg (l_package_name, 'SETUP');
101 RETURN FALSE;
102 END setup;
103
104 /*======================================================================
105 -- PROCEDURE :
106 -- Validate Flex Field
107 --
108 -- DESCRIPTION:
109 -- This particular procedure call validates the flex field data.
110 -- REQUIREMENTS
111 --
112 -- SYNOPSIS:
113 -- validate_flex_field ('GMD', 'FORM_DTL_FLEX', 'ATTRIBUTE1', '10',
114 -- x_field_value, x_return_status);
115 --
116 --===================================================================== */
117
118 PROCEDURE validate_flex_field (
119 p_application_short_name IN VARCHAR2
120 ,p_flex_field_name IN VARCHAR2
121 ,p_field_name IN VARCHAR2
122 ,p_field_value IN VARCHAR2
123 ,x_field_value OUT NOCOPY VARCHAR2
124 ,x_return_status OUT NOCOPY VARCHAR2) IS
125 l_display_value VARCHAR2 (240);
126 l_required_flag VARCHAR2 (1);
127 l_display_flag VARCHAR2 (1);
128 l_display BOOLEAN;
129 l_value_set_id NUMBER;
130 l_exists VARCHAR2 (1);
131
132 CURSOR cur_get_appl_id IS
133 SELECT application_id
134 FROM fnd_application
135 WHERE application_short_name = 'GMD';
136
137 CURSOR cur_get_cont_name (
138 v_flex_field_name VARCHAR2
139 ,v_application_id NUMBER) IS
140 SELECT context_column_name, context_required_flag
141 FROM fnd_descriptive_flexs
142 WHERE application_id = v_application_id AND
143 descriptive_flexfield_name = v_flex_field_name;
144
145 CURSOR cur_check_context (
146 v_application_id NUMBER
147 ,v_flex_field_name VARCHAR2
148 ,v_field_value VARCHAR2) IS
149 SELECT 1
150 FROM sys.DUAL
151 WHERE EXISTS ( SELECT 1
152 FROM fnd_descr_flex_contexts
153 WHERE application_id = v_application_id AND
154 descriptive_flexfield_name =
155 v_flex_field_name AND
156 descriptive_flex_context_code = v_field_value);
157
158 CURSOR cur_column_values (
159 v_application_id NUMBER
160 ,v_flex_field_name VARCHAR2
161 ,v_field_name VARCHAR2) IS
162 SELECT required_flag, flex_value_set_id, display_flag
163 FROM fnd_descr_flex_column_usages
164 WHERE application_id = v_application_id AND
165 descriptive_flexfield_name = v_flex_field_name AND
166 application_column_name = v_field_name AND
167 enabled_flag = 'Y';
168
169 CURSOR cur_value_set (v_value_set_id NUMBER) IS
170 SELECT flex_value_set_name, format_type, maximum_size
171 ,number_precision, alphanumeric_allowed_flag
172 ,uppercase_only_flag, minimum_value, maximum_value
173 FROM fnd_flex_value_sets
174 WHERE flex_value_set_id = v_value_set_id;
175
176 l_value_rec cur_value_set%ROWTYPE;
177 flex_not_enabled EXCEPTION;
178 column_not_defined EXCEPTION;
179 field_value_required EXCEPTION;
180 validation_failure EXCEPTION;
181 context_value_required EXCEPTION;
182 context_not_existing EXCEPTION;
183 BEGIN
184 /* Set return status to success initially */
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186
187 /* Package variables have been introduced to avoid unnecessary open */
188 /* of cursor and packages multiple times, as this pakage would be */
189 /* called in a loop in most of the scenarios */
190 IF pkg_application_short_name <> p_application_short_name THEN
191 OPEN cur_get_appl_id;
192 FETCH cur_get_appl_id INTO pkg_application_id;
193 CLOSE cur_get_appl_id;
194 pkg_application_short_name := p_application_short_name;
195 END IF;
196
197 IF pkg_flex_field_name <> p_flex_field_name THEN
198 /* Now let us set the global flag for checking the flex field */
199 fnd_flex_apis.descr_setup_or_required (
200 x_application_id => pkg_application_id
201 ,x_desc_flex_name => p_flex_field_name
202 ,enabled_flag => pkg_flex_enabled
203 ,required_flag => l_required_flag);
204 pkg_flex_field_name := p_flex_field_name;
205
206 IF pkg_flex_enabled = 'Y' THEN
207 /* Now we have to check wether the context_field is required */
208 OPEN cur_get_cont_name (p_flex_field_name, pkg_application_id);
209 FETCH cur_get_cont_name INTO pkg_context_column_name
210 ,pkg_context_required;
211 CLOSE cur_get_cont_name;
212 END IF;
213 END IF;
214
215 /* If the flex field is not enabled then we should not allow any values in the attribute column */
216 IF (pkg_flex_enabled = 'N') AND
217 (p_field_value IS NOT NULL) THEN
218 RAISE flex_not_enabled;
219 END IF;
220
221 IF p_field_name = pkg_context_column_name THEN
222 /* If the context field is required then we have to check wether a context */
223 /* value has been passed */
224 IF pkg_context_required = 'Y' THEN
225 IF p_field_value IS NULL THEN
226 RAISE context_value_required;
227 ELSE
228 /* Let us check now wether the value passed in as context is a valid value */
229 OPEN cur_check_context (
230 pkg_application_id
231 ,p_flex_field_name
232 ,p_field_value);
233 FETCH cur_check_context INTO l_exists;
234
235 IF cur_check_context%NOTFOUND THEN
236 CLOSE cur_check_context;
237 RAISE context_not_existing;
238 END IF;
239
240 CLOSE cur_check_context;
241 END IF;
242 END IF; /* IF pkg_context_required = 'Y' */
243
244 x_field_value := p_field_value;
245 ELSE
246 /* Now let us fetch the column values */
247 OPEN cur_column_values (
248 pkg_application_id
249 ,p_flex_field_name
250 ,p_field_name);
251 FETCH cur_column_values INTO l_required_flag
252 ,l_value_set_id
253 ,l_display_flag;
254
255 IF cur_column_values%NOTFOUND THEN
256 IF p_field_value IS NOT NULL THEN
257 CLOSE cur_column_values;
258 RAISE column_not_defined;
259 END IF;
260 ELSE
261 /* Check the required property of the field */
262 IF (l_required_flag = 'Y') AND
263 (p_field_value IS NULL) THEN
264 RAISE field_value_required;
265 END IF;
266
267 x_field_value := p_field_value;
268
269 IF p_field_value IS NULL THEN
270 RETURN;
271 END IF;
272
273 /* Check for any value sets attached, if any then we have to validate against the value set */
274 IF l_value_set_id IS NOT NULL THEN
275 OPEN cur_value_set (l_value_set_id);
276 FETCH cur_value_set INTO l_value_rec;
277
278 IF cur_value_set%FOUND THEN
279 IF l_display_flag = 'Y' THEN
280 l_display := TRUE;
281 ELSE
282 l_display := FALSE;
283 END IF;
284
285 /* Now its time to validate the value against the value set attached */
286 IF NOT fnd_flex_val_util.is_value_valid (
287 p_value => p_field_value
288 ,p_is_displayed => l_display
289 ,p_vset_name => l_value_rec.flex_value_set_name
290 ,p_vset_format => l_value_rec.format_type
291 ,p_max_length => l_value_rec.maximum_size
292 ,p_precision => l_value_rec.number_precision
293 ,p_alpha_allowed => l_value_rec.alphanumeric_allowed_flag
294 ,p_uppercase_only => l_value_rec.uppercase_only_flag
295 ,p_min_value => l_value_rec.minimum_value
296 ,p_max_value => l_value_rec.maximum_value
297 ,x_storage_value => x_field_value
298 ,x_display_value => l_display_value) THEN
299 RAISE validation_failure;
300 END IF;
301 END IF; /* IF Cur_value_set%FOUND */
302
303 CLOSE cur_value_set;
304 END IF;
305 END IF; /* IF Cur_column_values%NOTFOUND */
306
307 CLOSE cur_column_values;
308 END IF; /* IF p_field_name = pkg_context_column_name */
309 EXCEPTION
310 WHEN flex_not_enabled THEN
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 gmd_api_grp.log_message (
313 'GMD_FLEX_NOT_ENABLED'
314 ,'FLEX_NAME'
315 ,p_flex_field_name);
316 WHEN context_value_required THEN
317 gmd_api_grp.log_message (
318 'GMD_CONTEXT_VALUE_REQD'
319 ,'CONTEXT_NAME'
320 ,pkg_context_column_name);
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 WHEN context_not_existing THEN
323 gmd_api_grp.log_message (
324 'GMD_NON_EXISTING_CONTEXT'
325 ,'CONTEXT_VALUE'
326 ,p_field_value);
327 x_return_status := FND_API.G_RET_STS_ERROR;
328 WHEN column_not_defined THEN
329 x_return_status := FND_API.G_RET_STS_ERROR;
330 gmd_api_grp.log_message (
331 'GMD_FLEX_COL_NOT_DEF'
332 ,'FLEX_NAME'
333 ,p_flex_field_name
334 ,'COLUMN_NAME'
335 ,p_field_name);
336 WHEN field_value_required THEN
337 x_return_status := FND_API.G_RET_STS_ERROR;
338 gmd_api_grp.log_message (
339 'GMD_FIELD_VALUE_REQUIRED'
340 ,'FIELD_NAME'
341 ,p_field_name);
342 WHEN validation_failure THEN
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 END validate_flex_field;
345
346 /*************************************************************
347 Procedure
348 Validate Flex Field
349 Type
350 Private Procedure - to be called only by Proc. Validate_flex
351 Description
352 This particular procedure call validates the flex field data.
353 *************************************************************/
354
355 PROCEDURE validate_flex_field (
356 p_table_name IN VARCHAR2 ,
357 p_flex_record IN gmd_api_grp.flex,
358 p_field_name IN VARCHAR2 ,
359 x_flex_record IN OUT NOCOPY gmd_api_grp.flex,
360 x_return_status OUT NOCOPY VARCHAR2
361 ) IS
362 l_field_value VARCHAR2 (240);
363 l_storage_value VARCHAR2 (240);
364 l_flex_name FND_DESCRIPTIVE_FLEXS.descriptive_flexfield_name%TYPE;
365 l_appl_id FND_APPLICATION.application_id%TYPE;
366 l_appl_name FND_APPLICATION.application_short_name%TYPE;
367
368 /* Exception declaration */
369 validation_failure EXCEPTION;
370 flexfield_not_found_exception EXCEPTION;
371 appl_name_not_found_exception EXCEPTION;
372
373 CURSOR get_desc_flex_name(vTable_Name VARCHAR2) IS
374 SELECT descriptive_flexfield_name, application_id
375 FROM fnd_descriptive_flexs
376 WHERE application_table_name = vTable_name;
377
378 CURSOR get_appl_short_name(vAppl_id NUMBER) IS
379 SELECT application_short_name
380 FROM fnd_application
381 WHERE application_id = vAppl_id;
382
383 BEGIN
384 /* Set return status to success initially */
385 x_return_status := FND_API.G_RET_STS_SUCCESS;
386 /* Let us fetch the field value first */
387 SELECT DECODE (
388 p_field_name,
389 'ATTRIBUTE_CATEGORY', p_flex_record.attribute_category,
390 'ATTRIBUTE1', p_flex_record.attribute1,
391 'ATTRIBUTE2', p_flex_record.attribute2,
392 'ATTRIBUTE3', p_flex_record.attribute3,
393 'ATTRIBUTE4', p_flex_record.attribute4,
394 'ATTRIBUTE5', p_flex_record.attribute5,
395 'ATTRIBUTE6', p_flex_record.attribute6,
396 'ATTRIBUTE7', p_flex_record.attribute7,
397 'ATTRIBUTE8', p_flex_record.attribute8,
398 'ATTRIBUTE9', p_flex_record.attribute9,
399 'ATTRIBUTE10', p_flex_record.attribute10,
400 'ATTRIBUTE11', p_flex_record.attribute11,
401 'ATTRIBUTE12', p_flex_record.attribute12,
402 'ATTRIBUTE13', p_flex_record.attribute13,
403 'ATTRIBUTE14', p_flex_record.attribute14,
404 'ATTRIBUTE15', p_flex_record.attribute15,
405 'ATTRIBUTE16', p_flex_record.attribute16,
406 'ATTRIBUTE17', p_flex_record.attribute17,
407 'ATTRIBUTE18', p_flex_record.attribute18,
408 'ATTRIBUTE19', p_flex_record.attribute19,
409 'ATTRIBUTE20', p_flex_record.attribute20,
410 'ATTRIBUTE21', p_flex_record.attribute21,
411 'ATTRIBUTE22', p_flex_record.attribute22,
412 'ATTRIBUTE23', p_flex_record.attribute23,
413 'ATTRIBUTE24', p_flex_record.attribute24,
414 'ATTRIBUTE25', p_flex_record.attribute25,
415 'ATTRIBUTE26', p_flex_record.attribute26,
416 'ATTRIBUTE27', p_flex_record.attribute27,
417 'ATTRIBUTE28', p_flex_record.attribute28,
418 'ATTRIBUTE29', p_flex_record.attribute29,
419 'ATTRIBUTE30', p_flex_record.attribute30
420 )
421 INTO l_field_value
422 FROM sys.DUAL;
423
424 OPEN get_desc_flex_name(UPPER(p_Table_name));
425 FETCH get_desc_flex_name INTO l_flex_name, l_appl_id;
426 IF get_desc_flex_name%FOUND THEN
427 OPEN get_appl_short_name(l_appl_id);
428 FETCH get_appl_short_name INTO l_appl_name;
429 IF get_appl_short_name%NOTFOUND THEN
430 CLOSE get_appl_short_name;
431 RAISE appl_name_not_found_exception;
432 END IF;
433 CLOSE get_appl_short_name;
434 ELSE
435 CLOSE get_desc_flex_name;
436 RAISE flexfield_not_found_exception;
437 END IF;
438 CLOSE get_desc_flex_name;
439
440 gmd_api_grp.validate_flex_field (
441 p_application_short_name => l_appl_name,
442 p_flex_field_name => l_flex_name,
443 p_field_name => p_field_name,
444 p_field_value => l_field_value,
445 x_field_value => l_storage_value,
446 x_return_status => x_return_status
447 );
448
449 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
450 RAISE validation_failure;
451 END IF;
452
453 IF ( l_debug IS NOT NULL ) THEN
454 gmd_debug.put_line ('Flex:'|| p_field_name || ' Value:' || l_storage_value);
455 END IF;
456
457 /*Now let us copy back the storage value */
458 IF p_field_name = 'ATTRIBUTE1' THEN
459 x_flex_record.attribute1 := l_storage_value;
460 ELSIF p_field_name = 'ATTRIBUTE2' THEN
461 x_flex_record.attribute2 := l_storage_value;
462 ELSIF p_field_name = 'ATTRIBUTE3' THEN
463 x_flex_record.attribute3 := l_storage_value;
464 ELSIF p_field_name = 'ATTRIBUTE4' THEN
465 x_flex_record.attribute4 := l_storage_value;
466 ELSIF p_field_name = 'ATTRIBUTE5' THEN
467 x_flex_record.attribute5 := l_storage_value;
468 ELSIF p_field_name = 'ATTRIBUTE6' THEN
469 x_flex_record.attribute6 := l_storage_value;
470 ELSIF p_field_name = 'ATTRIBUTE7' THEN
471 x_flex_record.attribute7 := l_storage_value;
472 ELSIF p_field_name = 'ATTRIBUTE8' THEN
473 x_flex_record.attribute8 := l_storage_value;
474 ELSIF p_field_name = 'ATTRIBUTE9' THEN
475 x_flex_record.attribute9 := l_storage_value;
476 ELSIF p_field_name = 'ATTRIBUTE10' THEN
477 x_flex_record.attribute10 := l_storage_value;
478 ELSIF p_field_name = 'ATTRIBUTE11' THEN
479 x_flex_record.attribute11 := l_storage_value;
480 ELSIF p_field_name = 'ATTRIBUTE12' THEN
481 x_flex_record.attribute12 := l_storage_value;
482 ELSIF p_field_name = 'ATTRIBUTE13' THEN
483 x_flex_record.attribute13 := l_storage_value;
484 ELSIF p_field_name = 'ATTRIBUTE14' THEN
485 x_flex_record.attribute14 := l_storage_value;
486 ELSIF p_field_name = 'ATTRIBUTE15' THEN
487 x_flex_record.attribute15 := l_storage_value;
488 ELSIF p_field_name = 'ATTRIBUTE16' THEN
489 x_flex_record.attribute16 := l_storage_value;
490 ELSIF p_field_name = 'ATTRIBUTE17' THEN
491 x_flex_record.attribute17 := l_storage_value;
492 ELSIF p_field_name = 'ATTRIBUTE18' THEN
493 x_flex_record.attribute18 := l_storage_value;
494 ELSIF p_field_name = 'ATTRIBUTE19' THEN
495 x_flex_record.attribute19 := l_storage_value;
496 ELSIF p_field_name = 'ATTRIBUTE20' THEN
497 x_flex_record.attribute20 := l_storage_value;
498 ELSIF p_field_name = 'ATTRIBUTE21' THEN
499 x_flex_record.attribute21 := l_storage_value;
500 ELSIF p_field_name = 'ATTRIBUTE22' THEN
501 x_flex_record.attribute22 := l_storage_value;
502 ELSIF p_field_name = 'ATTRIBUTE23' THEN
503 x_flex_record.attribute23 := l_storage_value;
504 ELSIF p_field_name = 'ATTRIBUTE24' THEN
505 x_flex_record.attribute24 := l_storage_value;
506 ELSIF p_field_name = 'ATTRIBUTE25' THEN
507 x_flex_record.attribute25 := l_storage_value;
508 ELSIF p_field_name = 'ATTRIBUTE26' THEN
509 x_flex_record.attribute26 := l_storage_value;
510 ELSIF p_field_name = 'ATTRIBUTE27' THEN
511 x_flex_record.attribute27 := l_storage_value;
512 ELSIF p_field_name = 'ATTRIBUTE28' THEN
513 x_flex_record.attribute28 := l_storage_value;
514 ELSIF p_field_name = 'ATTRIBUTE29' THEN
515 x_flex_record.attribute29 := l_storage_value;
516 ELSIF p_field_name = 'ATTRIBUTE30' THEN
517 x_flex_record.attribute30 := l_storage_value;
518 ELSIF p_field_name = 'ATTRIBUTE_CATEGORY' THEN
519 x_flex_record.attribute_category := l_storage_value;
520 END IF;
521 EXCEPTION
522 WHEN flexfield_not_found_exception OR appl_name_not_found_exception THEN
523 NULL;
524 WHEN validation_failure THEN
525 NULL;
526 END validate_flex_field;
527
528
529 /*************************************************************
530 Procedure
531 Validate Flex
532 Description
533 This particular procedure call validates the flex field data.
534 *************************************************************/
535
536 PROCEDURE validate_flex (
537 p_table_name IN VARCHAR2 ,
538 p_flex_record IN gmd_api_grp.flex,
539 x_flex_record IN OUT NOCOPY gmd_api_grp.flex,
540 x_return_status OUT NOCOPY VARCHAR2
541 ) IS
542 validation_failure EXCEPTION;
543 BEGIN
544 /* Initialize the return status to success */
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546 validate_flex_field (
547 p_table_name => p_table_name,
548 p_flex_record => p_flex_record,
549 p_field_name => 'ATTRIBUTE_CATEGORY',
550 x_flex_record => x_flex_record,
551 x_return_status => x_return_status
552 );
553
554 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
555 RAISE validation_failure;
556 END IF;
557
558 FOR i IN 1 .. 30
559 LOOP
560 validate_flex_field (
561 p_table_name => p_table_name,
562 p_flex_record => p_flex_record,
563 p_field_name => 'ATTRIBUTE' || TO_CHAR (i),
564 x_flex_record => x_flex_record,
565 x_return_status => x_return_status
566 );
567
568 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
569 RAISE validation_failure;
570 END IF;
571 END LOOP;
572 EXCEPTION
573 WHEN validation_failure THEN
574 NULL;
575 END validate_flex;
576
577
578 /* *********************************************************************** *
579 * Function *
580 * Check_orgn_access *
581 * Parameter : Entity_id Number, Entity_name VARCHAR2 *
582 * Description *
583 * Checks if the user has access to the entity organization *
584 * *********************************************************************** */
585
586 FUNCTION Check_orgn_access(Entity VARCHAR2
587 ,Entity_id NUMBER)
588 RETURN BOOLEAN IS
589 l_owner_orgn_code VARCHAR2(4);
590 l_user_id NUMBER := fnd_global.user_id;
591 l_orgn_id NUMBER;
592 l_resp_name VARCHAR2(240) := fnd_global.resp_name;
593 l_dummy NUMBER := 0;
594
595 BEGIN
596 IF (Entity = 'FORMULA') THEN
597 SELECT b.organization_code, b.organization_id
598 INTO l_owner_orgn_code, l_orgn_id
599 FROM fm_form_mst_b a, mtl_parameters b
600 WHERE a.formula_id = Entity_id
601 AND a.owner_organization_id = b.organization_id;
602
603 /* Check if user has access to this formula orgn */
604 IF NOT (gmd_api_grp.OrgnAccessible(l_orgn_id)) THEN
605 FND_MESSAGE.SET_NAME('GMD','GMD_FORMULA_NOT_UPDATEABLE');
606 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
607 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_Owner_orgn_code);
608 FND_MSG_PUB.ADD;
609 Return FALSE;
610 END IF;
611
612 ELSIF (Entity = 'RECIPE') THEN
613 SELECT b.organization_code, b.organization_id
614 INTO l_owner_orgn_code, l_orgn_id
615 FROM gmd_recipes_b a, mtl_parameters b
616 WHERE recipe_id = Entity_id
617 AND a.owner_organization_id = b.organization_id;
618
619 /* Check if user has access to this Recipe orgn */
620 IF NOT (gmd_api_grp.OrgnAccessible(l_orgn_id)) THEN
621 FND_MESSAGE.SET_NAME('GMD','GMD_RECIPE_NOT_UPDATEABLE');
622 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
623 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_Owner_orgn_code);
624 FND_MSG_PUB.ADD;
625 Return FALSE;
626 END IF;
627
628 ELSIF (Entity = 'VALIDITY') THEN
629 SELECT c.organization_code , a.owner_organization_id
630 INTO l_owner_orgn_code, l_orgn_id
631 FROM gmd_recipes a , gmd_recipe_validity_rules b , mtl_parameters c
632 WHERE b.recipe_validity_rule_id = Entity_id
633 AND a.recipe_id = b.recipe_id
634 AND a.owner_organization_id = c.organization_id ;
635
636 -- Check if user has access to this Recipe orgn
637 IF (l_owner_orgn_code IS NOT NULL) THEN
638 /* Check if user resp has access to this VR orgn */
639 IF NOT (gmd_api_grp.OrgnAccessible(l_orgn_id)) THEN
640 FND_MESSAGE.SET_NAME('GMD','GMD_RECIPE_NOT_UPDATEABLE');
641 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
642 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_Owner_orgn_code);
643 FND_MSG_PUB.ADD;
644 Return FALSE;
645 END IF;
646 ELSE -- Global validity rule
647 RETURN TRUE;
648 END IF;
649
650 ELSIF (Entity = 'ROUTING') THEN
651 SELECT b.organization_code, b.organization_id
652 INTO l_Owner_orgn_code, l_orgn_id
653 FROM gmd_routings_b a, mtl_parameters b
654 WHERE a.routing_id = Entity_id
655 AND a.owner_organization_id = b.organization_id;
656
657 /* Check if user has access to this formula orgn */
658 IF NOT (gmd_api_grp.OrgnAccessible(l_orgn_id)) THEN
659 FND_MESSAGE.SET_NAME('GMD','GMD_ROUTING_NOT_UPDATEABLE');
660 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
661 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_Owner_orgn_code);
662 FND_MSG_PUB.ADD;
663 Return FALSE;
664 END IF;
665 ELSIF (Entity = 'OPERATION') THEN
666 SELECT b.organization_code, b.organization_id
667 INTO l_Owner_orgn_code, l_orgn_id
668 FROM gmd_operations_b a, mtl_parameters b
669 WHERE a.oprn_id = Entity_id
670 AND a.owner_organization_id = b.organization_id;
671
672 /* Check if user has access to this formula orgn */
673 IF NOT (gmd_api_grp.OrgnAccessible(l_orgn_id)) THEN
674 FND_MESSAGE.SET_NAME('GMD','GMD_OPERATION_NOT_UPDATEABLE');
675 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
676 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_Owner_orgn_code);
677 FND_MSG_PUB.ADD;
678 Return FALSE;
679 END IF;
680 END IF;
681
682 RETURN TRUE;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 fnd_msg_pub.add_exc_msg (l_package_name, 'CHECK_ORGN_ACCESS');
687 RETURN FALSE;
688 END Check_orgn_access;
689
690
691
692 /* *********************************************************************** *
693 * Function *
694 * OrgnAccessible() *
695 * Parameter : powner_orgn_id IN NUMBER
696 * Description *
697 * Checks if the user has access to the entity organization *
698 * during the creation of a new instance *
699 * *********************************************************************** */
700
701 FUNCTION OrgnAccessible(powner_orgn_id IN NUMBER) RETURN BOOLEAN IS
702 /* Cursor Definitions. */
703 /* =================== */
704 --Added the following line to fix the bug 14000659.
705 l_responsibility_id NUMBER := fnd_global.resp_id;
706
707 CURSOR Cur_ownerorgn_id IS
708 SELECT 1
709 FROM SYS.DUAL
710 WHERE EXISTS (SELECT 1
711 from org_access_view a, mtl_parameters b
712 where a.organization_id = b.organization_id
713 and b.organization_id = powner_orgn_id
714 and a.responsibility_id = l_responsibility_id --Bug 14000659
715 and b.process_enabled_flag = 'Y');
716
717 CURSOR Cur_get_orgn (V_organization_id NUMBER) IS
718 SELECT organization_code
719 FROM mtl_parameters
720 WHERE organization_id = V_organization_id;
721
722 /* Local variables. */
723 /* ================ */
724 l_ret NUMBER;
725 l_resp_name VARCHAR2(240) := fnd_global.resp_name;
726 l_owner_org VARCHAR2(3);
727
728 Update_not_allowed_exp EXCEPTION;
729 BEGIN
730 IF (powner_orgn_id IS NOT NULL) THEN
731 OPEN Cur_ownerorgn_id;
732 FETCH Cur_ownerorgn_id INTO l_ret;
733 IF (Cur_ownerorgn_id%NOTFOUND) THEN
734 CLOSE Cur_ownerorgn_id;
735 RAISE Update_not_allowed_exp;
736 END IF;
737 CLOSE Cur_ownerorgn_id;
738 END IF;
739
740 RETURN TRUE;
741 EXCEPTION
742 WHEN Update_not_allowed_exp THEN
743 /*Bug 4716697 - Thomas Daniel */
744 /*Added code to fetch the organization code to set the message*/
745 OPEN Cur_get_orgn (powner_orgn_id);
746 FETCH Cur_get_orgn INTO l_owner_org;
747 CLOSE Cur_get_orgn;
748 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_ORG_NOT_UPDATE');
749 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
750 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_owner_org);
751 FND_MSG_PUB.ADD;
752 RETURN FALSE;
753 WHEN OTHERS THEN
754 FND_MESSAGE.SET_NAME('GMD', 'GMD_INV_USER_ORGANIZATION');
755 FND_MSG_PUB.ADD;
756 RETURN FALSE;
757 END OrgnAccessible;
758
759 /* *********************************************************************** *
760 * Function *
761 * get_object_status_type *
762 * Parameter : Entity_id Number, Entity_name VARCHAR2 *
763 * Description *
764 * Checks if the user has access to the entity organization *
765 * *********************************************************************** */
766 FUNCTION get_object_status_type
767 ( pObject_Name VARCHAR2
768 , pObject_Id NUMBER)
769 RETURN GMD_STATUS_B.status_type%TYPE IS
770 l_status_type GMD_STATUS_B.status_type%TYPE;
771
772 CURSOR Cur_get_fsttyp(pobject_id NUMBER) IS
773 SELECT s.status_type
774 FROM fm_form_mst_b f,gmd_Status_b s
775 WHERE f.formula_id = pobject_id AND
776 f.formula_status = s.status_code;
777
778 CURSOR Cur_get_rsttyp(pobject_id NUMBER) IS
779 SELECT s.status_type
780 FROM gmd_recipes_b r,gmd_Status_b s
781 WHERE r.recipe_id = pobject_id AND
782 r.recipe_status = s.status_code;
783
784 CURSOR Cur_get_osttyp(pobject_id NUMBER) IS
785 SELECT s.status_type
786 FROM gmd_operations_b o,gmd_Status_b s
787 WHERE o.oprn_id = pobject_id AND
788 o.operation_status = s.status_code;
789
790 CURSOR Cur_get_rtsttyp(pobject_id NUMBER) IS
791 SELECT s.status_type
792 FROM gmd_routings_b r,gmd_Status_b s
793 WHERE r.routing_id = pobject_id AND
794 r.routing_status = s.status_code;
795
796 CURSOR Cur_get_vrttyp(pobject_id NUMBER) IS
797 SELECT s.status_type
798 FROM gmd_recipe_validity_rules v,gmd_Status_b s
799 WHERE v.recipe_validity_rule_id = pobject_id AND
800 v.validity_rule_status = s.status_code;
801 BEGIN
802 IF (pObject_id IS NOT NULL) THEN
803 IF (Upper(pObject_Name) = 'FORMULA') THEN
804 OPEN Cur_get_fsttyp(pObject_id);
805 FETCH Cur_get_fsttyp INTO l_Status_type;
806 CLOSE Cur_get_fsttyp;
807 ELSIF (Upper(pObject_Name) = 'RECIPE') THEN
808 OPEN Cur_get_rsttyp(pObject_id);
809 FETCH Cur_get_rsttyp INTO l_Status_type;
810 CLOSE Cur_get_rsttyp;
811 ELSIF (Upper(pObject_Name) = 'ROUTING') THEN
812 OPEN Cur_get_rtsttyp(pObject_id);
813 FETCH Cur_get_rtsttyp INTO l_Status_type;
814 CLOSE Cur_get_rtsttyp;
815 ELSIF (Upper(pObject_Name) = 'OPERATION') THEN
816 OPEN Cur_get_osttyp(pObject_id);
817 FETCH Cur_get_osttyp INTO l_Status_type;
818 CLOSE Cur_get_osttyp;
819 ELSIF (Upper(pObject_Name) = 'VALIDITY') THEN
820 OPEN Cur_get_vrttyp(pObject_id);
821 FETCH Cur_get_vrttyp INTO l_Status_type;
822 CLOSE Cur_get_vrttyp;
823 END IF;
824 END IF;
825 RETURN l_status_type ;
826 EXCEPTION
827 WHEN OTHERS THEN
828 fnd_msg_pub.add_exc_msg (l_package_name, 'GET_OBJECT_STATUS_TYPE');
829 RETURN Null;
830 END get_object_status_type;
831
832 /*======================================================================
833 # NAME
834 # Validate_with_dep_entities
835 # SYNOPSIS
836 # Proc Validate_with_dep_entities
837 # DESCRIPTION
838 ======================================================================*/
839 PROCEDURE Validate_with_dep_entities(V_type IN VARCHAR2,
840 V_entity_id IN NUMBER,
841 X_parent_check OUT NOCOPY BOOLEAN) IS
842 X_status VARCHAR2(5);
843 CURSOR Cur_get_recp_sts(entity_id NUMBER) IS
844 SELECT recipe_status
845 FROM gmd_recipes
846 WHERE recipe_id = entity_id
847 AND ((recipe_status between 700 and 799
848 OR recipe_status between 400 and 499));
849
850
851 CURSOR Cur_get_form_sts(entity_id NUMBER) IS
852 SELECT formula_status
853 FROM fm_form_mst
854 WHERE formula_id = entity_id
855 AND ((formula_status between 700 and 799
856 OR formula_status between 400 and 499));
857
858 CURSOR Cur_get_rout_sts(entity_id NUMBER) IS
859 SELECT routing_status
860 FROM fm_rout_hdr
861 WHERE routing_id = entity_id
862 AND ((routing_status between 700 and 799
863 OR routing_status between 400 and 499));
864
865 CURSOR Cur_get_oprn_sts(entity_id NUMBER) IS
866 SELECT operation_status
867 FROM gmd_operations
868 WHERE oprn_id = entity_id
869 AND ((operation_status between 700 and 799
870 OR operation_status between 400 and 499));
871 l_status gmd_status.status_code%TYPE;
872 l_parent_check BOOLEAN := FALSE;
873 BEGIN
874 IF (V_entity_id IS NULL) THEN
875 RETURN;
876 END IF;
877
878 IF (l_debug = 'Y') THEN
879 gmd_debug.put_line('In GMD_API_GRP.validate_with_dep_entities - '||
880 'v_entity_id = '||v_entity_Id||' and entity type = '||v_type);
881 END IF;
882
883 IF (v_entity_id IS NOT NULL) THEN
884 IF (V_type = 'RECIPE') THEN
885 OPEN Cur_get_recp_sts(v_entity_id);
886 FETCH Cur_get_recp_sts INTO l_status;
887 IF (l_debug = 'Y') THEN
888 gmd_debug.put_line('In GMD_API_GRP.validate_with_dep_entities - '||
889 'About to check for Recipe - Vr dep status = '||l_status);
890 END IF;
891 IF (Cur_get_recp_sts%FOUND) THEN
892 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
893 IF (l_debug = 'Y') THEN
894 gmd_debug.put_line('In GMD_API_GRP.validate_with_dep_entities - '||
895 ' There is VR dependency for this Recipe = '||v_entity_Id);
896 END IF;
897
898 l_parent_check := TRUE;
899 FND_MESSAGE.SET_NAME('GMD','GMD_RECIPE_BATCH_DEP');
900 END IF;
901 END IF;
902 CLOSE Cur_get_recp_sts;
903 ELSIF(v_type = 'ROUTING') THEN
904 OPEN Cur_get_rout_sts(v_entity_id);
905 FETCH Cur_get_rout_sts INTO l_status;
906 IF (Cur_get_rout_sts%FOUND) THEN
907 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
908 l_parent_check := TRUE;
909 FND_MESSAGE.SET_NAME('GMD','GMD_ROUTING_RECIPE_DEP');
910 END IF;
911 END IF;
912 CLOSE Cur_get_rout_sts;
913 ELSIF(V_type = 'FORMULA') THEN
914 OPEN Cur_get_form_sts(v_entity_id);
915 FETCH Cur_get_form_sts INTO l_status;
916 IF (Cur_get_form_sts%FOUND) THEN
917 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
918 l_parent_check := TRUE;
919 FND_MESSAGE.SET_NAME('GMD','GMD_FORMULA_RECIPE_DEP');
920 END IF;
921 END IF;
922 CLOSE Cur_get_form_sts;
923 ELSIF(v_type = 'OPERATION') THEN
924 OPEN Cur_get_oprn_sts(v_entity_id);
925 FETCH Cur_get_oprn_sts INTO l_status;
926 IF (Cur_get_oprn_sts%FOUND) THEN
927 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
928 l_parent_check := TRUE;
929 FND_MESSAGE.SET_NAME('GMD','GMD_OPERATION_ROUT_DEP');
930 END IF;
931 END IF;
932 CLOSE Cur_get_oprn_sts;
933 END IF;
934 END IF;
935
936 x_parent_check := l_parent_check;
937
938 END Validate_with_dep_entities;
939
940
941 /*======================================================================
942 NAME
943 get_object_name_version
944 SYNOPSIS
945 Proc get_object_name_version
946 DESCRIPTION
947 Function returns Object_no (e.g Recipe_no) when vType = 'NAME'
948 returns Object_vers (e.g Recipe_version) when vType = 'VERSION'
949 returns Object_no and version (e.g Recipe_no||' - '||version)
950 when vType = 'NAME-VERSION'
951 ======================================================================*/
952 FUNCTION get_object_name_version(vEntity VARCHAR2
953 ,vEntity_id NUMBER
954 ,vtype VARCHAR2 DEFAULT 'NAME-VERSION')
955 RETURN VARCHAR2 IS
956 l_object_name VARCHAR2(100);
957 l_object_version VARCHAR2(10);
958 l_object_name_and_version VARCHAR2(240);
959 BEGIN
960 IF (vEntity_id IS NOT NULL) THEN
961 IF (vEntity = 'FORMULA') THEN
962 Select formula_no, formula_vers, formula_no||' - '||formula_vers
963 INTO l_object_name, l_object_version, l_object_name_and_version
964 FROM fm_form_mst_b
965 WHere formula_id = vEntity_id;
966 ELSIF (vEntity = 'ROUTING') THEN
967 Select routing_no, routing_vers, routing_no||' - '||routing_vers
968 INTO l_object_name, l_object_version, l_object_name_and_version
969 FROM gmd_routings_b
970 WHere routing_id = vEntity_id;
971 ELSIF (vEntity = 'OPERATION') THEN
972 Select oprn_no, oprn_vers, oprn_no||' - '||oprn_vers
973 INTO l_object_name, l_object_version, l_object_name_and_version
974 FROM gmd_operations_b
975 WHere oprn_id = vEntity_id;
976 ELSIF (vEntity = 'RECIPE') THEN
977 Select recipe_no, recipe_version, recipe_no||' - '||recipe_version
978 INTO l_object_name, l_object_version, l_object_name_and_version
979 FROM gmd_recipes_b
980 WHere recipe_id = vEntity_id;
981 ELSIF (vEntity = 'VALIDITY') THEN
982 Select r.recipe_no, r.recipe_version, r.recipe_no||' - '||r.recipe_version
983 INTO l_object_name, l_object_version, l_object_name_and_version
984 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
985 WHere v.recipe_id = vEntity_id
986 AND v.recipe_id = r.recipe_id;
987 END IF;
988 END IF;
989
990 IF vType = 'NAME' THEN
991 Return l_object_name;
992 ELSIF vType = 'VERSION' THEN
993 Return l_object_version;
994 ELSE
995 RETURN l_object_name_and_version;
996 END IF;
997
998 END get_object_name_version;
999
1000 /********************************************************************************
1001 * Name : get_formula_acces_type
1002 *
1003 * Description: Function returns the acces type level of the user for a given formula.
1004 * Returns 'U', means user has updatable acces.
1005 * Returns 'V', means user has view acces.
1006 * Returns 'N', means no record setup - exceptional condition
1007 * Change History:
1008 * Who When What
1009 * TDANIEL 29-JUL-2005 Modified the code to handle NULL (-1) for formula id
1010 * and also modified for convergence changes.
1011 **********************************************************************************/
1012
1013 FUNCTION get_formula_access_type(p_formula_id IN PLS_INTEGER,
1014 p_owner_organization_id IN PLS_INTEGER)
1015 RETURN VARCHAR2 IS
1016
1017 /* Cursor Variables */
1018
1019 CURSOR get_vpd_flag IS
1020 SELECT active_formula_ind
1021 FROM gmd_vpd_security;
1022
1023 CURSOR Cur_check_orgn_access (V_default_user_id PLS_INTEGER) IS
1024 SELECT 1
1025 FROM sys.dual
1026 WHERE EXISTS ( SELECT 1
1027 FROM gmd_security_profiles sp
1028 WHERE sp.access_type_ind = 'U'
1029 AND nvl(responsibility_id, fnd_global.resp_id) = fnd_global.resp_id /* Bug No.9077438 */
1030 AND ( responsibility_id IN ( SELECT rg.responsibility_id
1031 FROM FND_USER_RESP_GROUPS rg
1032 WHERE rg.user_id = fnd_global.user_id
1033 AND SYSDATE BETWEEN rg.start_date
1034 AND NVL(rg.end_date, SYSDATE)
1035 )
1036 OR ( sp.user_id = V_default_user_id
1037 OR sp.user_id = fnd_global.user_id
1038 )
1039 )
1040 AND organization_id = P_owner_organization_id
1041 AND (other_organization_id IS NULL
1042 OR EXISTS ( SELECT NULL
1043 FROM org_access a3
1044 WHERE a3.organization_id = sp.other_organization_id
1045 AND NVL(a3.disable_date, SYSDATE+1) >= SYSDATE
1046 AND a3.resp_application_id = fnd_global.resp_appl_id
1047 AND a3.responsibility_id = fnd_global.resp_id
1048 )
1049 OR NOT EXISTS ( SELECT NULL
1050 FROM org_access a4
1051 WHERE a4.organization_id = sp.other_organization_id
1052 AND NVL(a4.disable_date, SYSDATE+1) >=SYSDATE
1053 )
1054 )
1055 );
1056
1057
1058 CURSOR Cur_check_formula_access (V_default_user_id PLS_INTEGER) IS
1059 SELECT sp.access_type_ind
1060 FROM gmd_security_profiles sp
1061 WHERE sp.assign_method_ind = 'A'
1062 AND NVL(responsibility_id, fnd_global.resp_id) = fnd_global.resp_id /* Bug No.9077438 */
1063 AND ( ( sp.user_id = V_default_user_id
1064 OR sp.user_id = fnd_global.user_id
1065 )
1066 OR ( EXISTS ( SELECT rg.responsibility_id
1067 FROM FND_USER_RESP_GROUPS rg
1068 WHERE rg.user_id = fnd_global.user_id
1069 AND sp.responsibility_id = rg.responsibility_id
1070 AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE)
1071 )
1072 )
1073 )
1074 AND ( EXISTS ( SELECT NULL
1075 FROM org_access a1
1076 WHERE ( ( sp.organization_id = a1.organization_id
1077 AND sp.other_organization_id IS NULL
1078 )
1079 OR sp.other_organization_id = a1.organization_id
1080 )
1081 AND NVL(a1.disable_date, SYSDATE+1) >= SYSDATE
1082 AND a1.resp_application_id = fnd_global.resp_appl_id
1083 AND a1.responsibility_id = fnd_global.resp_id
1084 )
1085 OR
1086 NOT EXISTS ( SELECT NULL
1087 FROM org_access a2
1088 WHERE ( ( sp.organization_id = a2.organization_id
1089 AND sp.other_organization_id IS NULL
1090 )
1091 OR sp.other_organization_id = a2.organization_id
1092 )
1093 AND NVL(a2.disable_date, SYSDATE+1) >=SYSDATE
1094 )
1095 )
1096 AND sp.organization_id = P_owner_organization_id
1097 UNION
1098 SELECT fs.access_type_ind
1099 FROM gmd_formula_security fs
1100 WHERE ( ( fs.user_id = V_default_user_id
1101 OR fs.user_id = fnd_global.user_id
1102 )
1103 OR ( EXISTS ( SELECT rg.responsibility_id
1104 FROM FND_USER_RESP_GROUPS rg
1105 WHERE rg.user_id = fnd_global.user_id
1106 AND fs.responsibility_id = rg.responsibility_id
1107 AND SYSDATE BETWEEN rg.start_date
1108 AND NVL(rg.end_date, SYSDATE)
1109 )
1110 )
1111 )
1112 AND nvl(responsibility_id,fnd_global.resp_id) = fnd_global.resp_id /* Bug No.9077438 */
1113 AND (EXISTS ( SELECT NULL
1114 FROM org_access ou
1115 WHERE ( ( fs.organization_id = ou.organization_id
1116 AND fs.other_organization_id IS NULL
1117 )
1118 OR fs.other_organization_id = ou.organization_id
1119 )
1120 AND NVL(ou.disable_date, SYSDATE+1) >= SYSDATE
1121 AND ou.resp_application_id = fnd_global.resp_appl_id
1122 AND ou.responsibility_id = fnd_global.resp_id
1123 )
1124 OR
1125 NOT EXISTS ( SELECT NULL
1126 FROM org_access ou1
1127 WHERE ( ( ou1.organization_id = fs.organization_id
1128 AND fs.other_organization_id IS NULL
1129 )
1130 OR ou1.organization_id = fs.other_organization_id
1131 )
1132 AND NVL(ou1.disable_date, SYSDATE+1) >=SYSDATE
1133 )
1134 )
1135 AND fs.formula_id = P_formula_id;
1136
1137
1138 /* Local Variables */
1139
1140 l_vpd_flag VARCHAR2(1) := 'N';
1141 l_access_type_ind VARCHAR2(1);
1142 l_default_user_id VARCHAR2(240) := fnd_profile.value('GMD_DEFAULT_USER');
1143 l_exists PLS_INTEGER;
1144 BEGIN
1145 /* First check if the VPD flag is set */
1146 OPEN get_vpd_flag;
1147 FETCH get_vpd_flag INTO l_vpd_flag;
1148 CLOSE get_vpd_flag;
1149
1150 IF (l_vpd_flag = 'Y') THEN
1151 /* If there is no formula associated then we are checking if the user */
1152 /* has security to create or view formula for the organization passed */
1153 IF p_formula_id = -1 THEN
1154 OPEN Cur_check_orgn_access (l_default_user_id);
1155 FETCH Cur_check_orgn_access INTO l_exists;
1156 IF Cur_check_orgn_access%FOUND THEN
1157 l_access_type_ind := 'U';
1158 ELSE
1159 l_access_type_ind := 'V';
1160 END IF;
1161 CLOSE Cur_check_orgn_access;
1162 ELSE
1163 OPEN Cur_check_formula_access (l_default_user_id);
1164 FETCH Cur_check_formula_access INTO l_access_type_ind;
1165 IF Cur_check_formula_access%NOTFOUND THEN
1166 l_access_type_ind := 'N';
1167 END IF;
1168 CLOSE Cur_check_formula_access;
1169 END IF;
1170 RETURN l_access_type_ind;
1171 ELSE
1172 RETURN 'U';
1173 END IF;
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 fnd_msg_pub.add_exc_msg(l_package_name, 'GET_FORMULA_ACCESS_TYPE');
1177 RETURN 'N';
1178 END get_formula_access_type;
1179
1180
1181 ------------------------------------------------------------------
1182 --Created by : Sriram.S
1183 --Date created: 20-JAN-2004
1184 --
1185 --Purpose: Returns description of the Status Code
1186 --Known limitations/enhancements and/or remarks:
1187 --
1188 --Change History:
1189 --Who When What
1190 --SRSRIRAN 20-FEB-2004 Created w.r.t. bug 3408799
1191 -------------------------------------------------------------------
1192 FUNCTION get_status_desc (V_entity_status IN VARCHAR2 ) RETURN VARCHAR2 IS
1193 CURSOR Cur_get_status_desc IS
1194 SELECT description
1195 FROM gmd_status
1196 WHERE status_code = V_entity_status;
1197 X_description VARCHAR2(240);
1198 BEGIN
1199 IF (V_entity_status IS NOT NULL) THEN
1200 OPEN Cur_get_status_desc;
1201 FETCH Cur_get_status_desc INTO X_description;
1202 CLOSE Cur_get_status_desc;
1203 RETURN X_description;
1204 END IF;
1205 RETURN NULL;
1206 END get_status_desc;
1207
1208
1209 ------------------------------------------------------------------
1210 --Created by : Sriram.S
1211 --Date created: 20-JAN-2004
1212 --
1213 --Purpose: Returns the default status of the entity.
1214 --Known limitations/enhancements and/or remarks:
1215 --
1216 --Change History:
1217 --Who When What
1218 --SRSRIRAN 20-FEB-2004 Created w.r.t. bug 3408799
1219 --kkiallms 01-DEC-2004 Modified w.r.t. 4004501
1220 -------------------------------------------------------------------
1221 PROCEDURE get_status_details (V_entity_type IN VARCHAR2,
1222 V_orgn_id IN NUMBER,
1223 X_entity_status OUT NOCOPY GMD_API_GRP.status_rec_type) IS
1224
1225 CURSOR cur_def_status(cp_orgn_id NUMBER,
1226 cp_parameter_name gmd_parameters_dtl.parameter_name%TYPE)IS
1227 SELECT st.status_code
1228 ,st.description
1229 ,st.status_type
1230 FROM gmd_parameters_hdr h
1231 ,gmd_parameters_dtl d
1232 ,gmd_status st
1233 WHERE (h.organization_id = cp_orgn_id OR h.organization_id IS NULL)
1234 AND h.parameter_id = d.parameter_id
1235 AND d.parameter_name = cp_parameter_name
1236 AND st.status_code = parameter_value
1237 ORDER BY h.organization_id;
1238
1239
1240 CURSOR Cur_get_def_new_status IS
1241 SELECT b.status_code, b.description,b.status_type
1242 FROM gmd_status b
1243 WHERE b.status_code = 100;
1244
1245 BEGIN
1246 IF (V_entity_type = 'ROUTING') THEN
1247 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_ROUT_STATUS');
1248 FETCH cur_def_status INTO X_entity_status;
1249 CLOSE cur_def_status;
1250 ELSIF (V_entity_type = 'OPERATION') THEN
1251 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_OPRN_STATUS');
1252 FETCH cur_def_status INTO X_entity_status;
1253 CLOSE cur_def_status;
1254 ELSIF (V_entity_type = 'RECIPE') THEN
1255 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_RECP_STATUS');
1256 FETCH cur_def_status INTO X_entity_status;
1257 CLOSE cur_def_status;
1258 ELSIF (V_entity_type = 'FORMULA') THEN
1259 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_FORM_STATUS');
1260 FETCH cur_def_status INTO X_entity_status;
1261 CLOSE cur_def_status;
1262 ELSIF (V_entity_type = 'VALIDITY') THEN
1263 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_VALR_STATUS');
1264 FETCH cur_def_status INTO X_entity_status;
1265 CLOSE cur_def_status;
1266 ELSIF (V_entity_type = 'SUBSTITUTION') THEN --Bug 4479101
1267 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_SUBS_STATUS');
1268 FETCH cur_def_status INTO X_entity_status;
1269 CLOSE cur_def_status;
1270
1271 END IF;
1272
1273 IF X_entity_status.entity_status IS NULL THEN
1274 OPEN CUR_get_def_new_status;
1275 FETCH Cur_get_def_new_status INTO X_entity_status;
1276 CLOSE Cur_get_def_new_status;
1277 END IF;
1278
1279 END get_status_details;
1280
1281
1282 ------------------------------------------------------------------
1283 --Created by : Sriram.S
1284 --Date created: 20-JAN-2004
1285 --
1286 --Purpose: Function returns the TRUE if V_entity_status status is
1287 -- valid for the given entity otherwise returns FALSE
1288 --Known limitations/enhancements and/or remarks:
1289 --
1290 --Change History:
1291 --Who When What
1292 --SRSRIRAN 20-FEB-2004 Created w.r.t. bug 3408799
1293 -------------------------------------------------------------------
1294 FUNCTION check_dependent_status(V_entity_type IN VARCHAR2,
1295 V_entity_id IN NUMBER,
1296 V_entity_status IN VARCHAR2) RETURN BOOLEAN IS
1297
1298 CURSOR Cur_get_def_oprn_status IS
1299 SELECT operation_status
1300 FROM gmd_operations
1301 WHERE oprn_id = V_entity_id
1302 AND operation_status >= V_entity_status;
1303 CURSOR Cur_get_def_formula_status IS
1304 SELECT formula_status FROM fm_form_mst
1305 WHERE formula_id = V_entity_id
1306 AND formula_status >= V_entity_status;
1307 CURSOR Cur_get_def_routing_status IS
1308 SELECT routing_status
1309 FROM fm_rout_hdr
1310 WHERE routing_id = V_entity_id
1311 AND routing_status >= V_entity_status;
1312 /* do we need this-not used*/
1313 l_operation_status gmd_operations.operation_status%TYPE;
1314 l_formula_status fm_form_mst.formula_status%TYPE;
1315 l_routing_status fm_rout_hdr.routing_status%TYPE;
1316 BEGIN
1317 /*Check all the operations inserted are of status APFLU or APFGU*/
1318 IF (V_entity_type = 'OPERATION') THEN
1319 OPEN Cur_get_def_oprn_status;
1320 FETCH Cur_get_def_oprn_status INTO l_operation_status;
1321 IF (Cur_get_def_oprn_status %NOTFOUND) THEN
1322 CLOSE Cur_get_def_oprn_status;
1323 RETURN TRUE;
1324 ELSE
1325 CLOSE Cur_get_def_oprn_status;
1326 RETURN FALSE;
1327 END IF;
1328 RETURN FALSE;
1329 END IF; --V_entity_type = 'OPERATION'
1330
1331 IF (V_entity_type = 'FORMULA') THEN
1332 OPEN Cur_get_def_formula_status;
1333 FETCH Cur_get_def_formula_status INTO l_formula_status;
1334 IF (Cur_get_def_formula_status %NOTFOUND) THEN
1335 CLOSE Cur_get_def_formula_status;
1336 RETURN TRUE;
1337 ELSE
1338 CLOSE Cur_get_def_formula_status;
1339 RETURN FALSE;
1340 END IF;
1341 RETURN FALSE;
1342 END IF; --V_entity_type = 'FORMULA'
1343
1344 IF (V_entity_type = 'ROUTING') THEN
1345 OPEN Cur_get_def_routing_status;
1346 FETCH Cur_get_def_routing_status INTO l_routing_status;
1347 IF (Cur_get_def_routing_status %NOTFOUND) THEN
1348 CLOSE Cur_get_def_routing_status;
1349 RETURN TRUE;
1350 ELSE
1351 CLOSE Cur_get_def_routing_status;
1352 RETURN FALSE;
1353 END IF; --Cur_get_def_routing_status %NOTFOUND
1354 RETURN FALSE;
1355 END IF; --V_entity_type = 'ROUTING'
1356 END check_dependent_status;
1357
1358 /*======================================================================
1359 -- PROCEDURE :
1360 -- set_activity_sequence_num
1361 --
1362 -- DESCRIPTION:
1363 -- This particular procedure is used to set the sequencing of the
1364 -- activities within an operation.
1365 -- REQUIREMENTS
1366 --
1367 -- SYNOPSIS:
1368 --
1369 --===================================================================== */
1370
1371 PROCEDURE set_activity_sequence_num(
1372 P_oprn_id IN NUMBER,
1373 P_user_id IN NUMBER,
1374 P_login_id IN NUMBER)
1375 IS
1376 /* this cursor selects the activities in the appropriate order, making the seq dep activity
1377 the first one the the others by offset then activity then the tie breaker of the surrogate
1378 key. The seq dep ind will hold the sequence numbers so we need to take those out of
1379 for validation purposes. The column seq_dep_order will make the seq dep activity first
1380 then all others will follow */
1381
1382 CURSOR Cur_get_activities (V_oprn_id IN NUMBER) IS
1383 SELECT oprn_line_id, NVL(sequence_dependent_ind,-1) sequence_dependent_ind
1384 FROM gmd_operation_activities
1385 WHERE oprn_id = v_oprn_id
1386 AND NVL(sequence_dependent_ind, 0) <> 1
1387 ORDER BY
1388 offset_interval,
1389 activity,
1390 oprn_line_id;
1391
1392 X_oprn_line_id NUMBER;
1393 X_seq_num NUMBER;
1394 X_seq_dep_ind NUMBER;
1395 BEGIN
1396 /* the seq num will always start at 100 for the first activity after the
1397 seq dep activity. */
1398 X_seq_num := 100;
1399
1400 OPEN Cur_get_activities (P_oprn_id);
1401 LOOP
1402 FETCH Cur_get_activities INTO X_oprn_line_id, X_seq_dep_ind;
1403 EXIT WHEN Cur_get_activities%NOTFOUND;
1404
1405 /* If the activity has been previously numbered and is in the right order
1406 no need to update it */
1407 IF X_seq_dep_ind <> X_seq_num THEN
1408 UPDATE gmd_operation_activities
1409 SET sequence_dependent_ind = X_seq_num,
1410 last_update_date = SYSDATE,
1411 last_updated_by = P_user_id,
1412 last_update_login = P_login_id
1413 WHERE oprn_line_id = X_oprn_line_id;
1414 END IF;
1415
1416 /* increment the seq num for each processed except the seq dep activity */
1417 X_seq_num := X_seq_num + 100;
1418 END LOOP;
1419 CLOSE Cur_get_activities;
1420 END set_activity_sequence_num;
1421
1422 /*========================================================================+
1423 ** Name : retrieve_vr
1424 ** Notes : This procedure receives as input recipe record and
1425 ** retrieves validity rules records.
1426 **
1427 ** If everything is fine then OUT parameter
1428 ** x_return_status is set to 'S' else appropriate
1429 ** error message is put on the stack and error
1430 ** is returned.
1431 **
1432 ** HISTORY
1433 ** 01-Mar-2004 B3604554 GK Created.
1434 ** 21-May-2004 B3642937 GK Called the procedure get_status_details and
1435 ** Assigned the default_status to the validity_status
1436 ** 24-May-2004 B3643405 GK Added the statement orgn IS NULL to cursor c_get_recipe_info
1437 ** 25-May-2004 B3645706 GK Removed the statement orgn IS NULL to cursor c_get_recipe_info
1438 ** and created a new cursor c_get_global_info to accomodate for global and
1439 ** so that the orgn code will pick up the correct configuration information
1440 ** 25-May-2004 B3653935 GK Changed variables login_id, user_id to be assigned to FND_PROFILE.VALUE
1441 ** 25-May-2004 B GK In the global records the start date and end date still referenced the local so changed this
1442 ** 01-dec-2004 kkillams orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
1443 ** 10-Feb-2005 4004501 Krishna Added Revision column, in the retrival information.
1444 ** 18-APR-2006 kmotupal Added check for default status while retrieving recipe VR details
1445 **+========================================================================*/
1446
1447
1448 PROCEDURE retrieve_vr(p_formula_id IN NUMBER,
1449 l_recipe_vr_tbl OUT NOCOPY GMD_RECIPE_DETAIL.recipe_vr,
1450 l_vr_flex OUT NOCOPY GMD_RECIPE_DETAIL.flex,
1451 x_return_status OUT NOCOPY VARCHAR2,
1452 p_recipe_use IN NUMBER) IS
1453
1454 CURSOR c_get_vr_id IS
1455 SELECT gmd_recipe_validity_id_s.NEXTVAL
1456 FROM FND_DUAL;
1457
1458 CURSOR c_get_formula IS
1459 SELECT creation_date, formula_status, owner_organization_id
1460 FROM fm_form_mst_b
1461 WHERE formula_id = p_formula_id;
1462 LocalDateRecord c_get_formula%ROWTYPE;
1463
1464 CURSOR c_get_formula_item IS
1465 SELECT inventory_item_id, revision, detail_uom, qty
1466 FROM fm_matl_dtl
1467 WHERE formula_id = p_formula_id
1468 AND line_type = 1
1469 ORDER BY line_no;
1470
1471 LocalFormRecord c_get_formula_item%ROWTYPE;
1472
1473 CURSOR c_get_recipe_info(l_orgn_id NUMBER) IS
1474 SELECT *
1475 FROM gmd_recipe_generation
1476 WHERE (organization_id = l_orgn_id
1477 OR organization_id IS NULL)
1478 ORDER BY organization_id;
1479
1480 LocalInfoRecord c_get_recipe_info%ROWTYPE;
1481
1482 CURSOR Cur_get_max_pref (V_item_id NUMBER, V_organization_id NUMBER,
1483 V_start_date DATE, V_end_date DATE, V_recipe_use NUMBER) IS
1484 SELECT MAX(preference)
1485 FROM gmd_recipe_validity_rules
1486 WHERE inventory_item_id = v_item_id
1487 AND organization_id = v_organization_id
1488 AND recipe_use = v_recipe_use
1489 AND NVL(end_date, v_start_date) >= v_start_date
1490 AND start_date <= NVL(v_end_date, start_date)
1491 AND inv_max_qty >= 0
1492 AND inv_min_qty <= 999999
1493 AND validity_rule_status < 800
1494 AND delete_mark = 0;
1495
1496 l_vr_id NUMBER := 0;
1497 l_item_id NUMBER(15,0);
1498 l_revision VARCHAR(3); --Krishna, NPD convergence
1499 l_user_id NUMBER;
1500 l_login_id NUMBER;
1501 l_preference NUMBER;
1502 l_recipe_use NUMBER;
1503
1504 l_orgn_id NUMBER;
1505 l_end_status VARCHAR2(30);
1506 l_detail_uom VARCHAR2(4);
1507
1508 l_start_date DATE;
1509 l_end_date DATE;
1510 x_end_date DATE;
1511
1512 l_default_vr_status GMD_API_GRP.status_rec_type;
1513 BEGIN
1514
1515 OPEN c_get_vr_id;
1516 FETCH c_get_vr_id INTO l_vr_id;
1517 CLOSE c_get_vr_id;
1518
1519 OPEN c_get_formula;
1520 FETCH c_get_formula INTO LocalDateRecord;
1521 l_orgn_id := LocalDateRecord.owner_organization_id;
1522 CLOSE c_get_formula;
1523
1524 OPEN c_get_recipe_info(l_orgn_id);
1525 FETCH c_get_recipe_info INTO LocalInfoRecord;
1526 IF c_get_recipe_info%FOUND THEN
1527 l_user_id := FND_PROFILE.VALUE('USER_ID');
1528 l_login_id := FND_PROFILE.VALUE ('LOGIN_ID');
1529
1530 IF LocalInfoRecord.start_date_type = 0 THEN
1531 OPEN c_get_formula;
1532 FETCH c_get_formula INTO LocalDateRecord;
1533 l_start_date := TRUNC(LocalDateRecord.creation_date);
1534 CLOSE c_get_formula;
1535 ELSE
1536 l_start_date := LocalInfoRecord.start_date;
1537 END IF;
1538
1539 IF LocalInfoRecord.end_date_type = 0 THEN
1540 GMD_RECIPE_GENERATE.calculate_date(l_start_date, LocalInfoRecord.Num_of_days, x_end_date);
1541 l_end_date := x_end_date;
1542 ELSE
1543 l_end_date := LocalInfoRecord.end_date;
1544 END IF;
1545 /*Bug 3735354 - Thomas Daniel */
1546 /*We need to reset the start date to be less than the end date for the setup */
1547 /*cases defined in the bug */
1548 IF l_end_date < l_start_date THEN
1549 l_start_date := TRUNC(l_end_date);
1550 END IF;
1551
1552 OPEN c_get_formula_item;
1553 FETCH c_get_formula_item INTO LocalFormRecord;
1554 l_item_id := LocalFormRecord.inventory_item_id;
1555 l_revision := LocalFormRecord.revision; --Krishna NPD Conv
1556 l_detail_uom := LocalFormRecord.detail_uom;
1557 CLOSE c_get_formula_item;
1558
1559 l_recipe_vr_tbl.recipe_validity_rule_id := l_vr_id;
1560 l_recipe_vr_tbl.recipe_id := NULL;
1561 l_recipe_vr_tbl.recipe_no := NULL;
1562 l_recipe_vr_tbl.recipe_version := NULL;
1563 l_recipe_vr_tbl.user_id := l_user_id;
1564 l_recipe_vr_tbl.organization_id := l_orgn_id;
1565 l_recipe_vr_tbl.inventory_item_id := l_item_id;
1566 l_recipe_vr_tbl.revision := l_revision;--Krishna NPD Conv
1567 l_recipe_vr_tbl.item_no := NULL;
1568 IF p_recipe_use IS NULL THEN
1569 l_recipe_vr_tbl.recipe_use := LocalInfoRecord.recipe_use_prod||
1570 LocalInfoRecord.recipe_use_plan||
1571 LocalInfoRecord.recipe_use_cost||
1572 LocalInfoRecord.recipe_use_reg||
1573 LocalInfoRecord.recipe_use_tech;
1574 ELSE
1575 l_recipe_vr_tbl.recipe_use := p_recipe_use;
1576 END IF;
1577
1578 l_recipe_vr_tbl.preference := l_preference;
1579 l_recipe_vr_tbl.start_date := l_start_date;
1580 l_recipe_vr_tbl.end_date := l_end_date;
1581 l_recipe_vr_tbl.min_qty := 0;
1582 l_recipe_vr_tbl.max_qty := 999999;
1583 l_recipe_vr_tbl.std_qty := LocalFormRecord.qty;
1584 l_recipe_vr_tbl.detail_uom := l_detail_uom;
1585 l_recipe_vr_tbl.inv_min_qty := 0;
1586 l_recipe_vr_tbl.inv_max_qty := 999999;
1587 l_recipe_vr_tbl.created_by := l_user_id;
1588 l_recipe_vr_tbl.creation_date := SYSDATE;
1589 l_recipe_vr_tbl.last_updated_by := l_user_id;
1590 l_recipe_vr_tbl.last_update_date := SYSDATE;
1591 l_recipe_vr_tbl.last_update_login := l_login_id;
1592 l_recipe_vr_tbl.delete_mark := 0;
1593
1594 -- Bug# 4504631 kmotupal
1595 -- Added check for default status while retrieving recipe VR details
1596 get_status_details (V_entity_type => 'VALIDITY',
1597 V_orgn_id => l_orgn_id,
1598 X_entity_status => l_default_vr_status);
1599
1600 l_recipe_vr_tbl.validity_rule_status := l_default_vr_status.entity_status;
1601 l_end_status := l_recipe_vr_tbl.validity_rule_status;
1602
1603 IF LocalInfoRecord.managing_validity_rules = 0 THEN
1604 IF p_recipe_use IS NOT NULL THEN
1605 l_recipe_use := p_recipe_use;
1606 ELSIF LocalInfoRecord.recipe_use_prod = 1 THEN
1607 l_recipe_use := 0;
1608 ELSIF LocalInfoRecord.recipe_use_plan = 1 THEN
1609 l_recipe_use := 1;
1610 ELSIF LocalInfoRecord.recipe_use_cost = 1 THEN
1611 l_recipe_use := 2;
1612 ELSIF LocalInfoRecord.recipe_use_reg = 1 THEN
1613 l_recipe_use := 3;
1614 ELSIF LocalInfoRecord.recipe_use_tech = 1 THEN
1615 l_recipe_use := 4;
1616 END IF;
1617
1618 OPEN Cur_get_max_pref (l_item_id, l_orgn_id,
1619 l_start_date, l_end_date, l_recipe_use);
1620 FETCH Cur_get_max_pref INTO l_preference;
1621 CLOSE Cur_get_max_pref;
1622 l_preference := NVL(l_preference,0) + 1;
1623 ELSE
1624 l_preference := 1;
1625 END IF;
1626 l_recipe_vr_tbl.preference := l_preference;
1627 END IF;
1628 CLOSE c_get_recipe_info;
1629
1630 x_return_status := FND_API.G_RET_STS_SUCCESS;
1631
1632 END retrieve_vr;
1633
1634
1635 /*+==============================================================================================================================+
1636 ** Name : retrieve_recipe
1637 ** Notes : This procedure receives as input recipe record and
1638 ** creates recipe records. |
1639 **
1640 ** If everything is fine then OUT parameter
1641 ** x_return_status is set to 'S' else appropriate
1642 ** error message is put on the stack and error
1643 ** is returned.
1644 **
1645 ** HISTORY
1646 ** 05-Apr-2004 B3604554 GK Created.
1647 ** 21-May-2004 B3642937 GK Assigned the default_status to the recipe_status
1648 ** 24-May-2004 B3643405 GK Added the statement orgn IS NULL to cursor c_get_recipe_info
1649 ** 25-May-2004 B3645706 GK Removed the statement orgn IS NULL to cursor c_get_recipe_info
1650 ** and created a new cursor c_get_global_info to accomodate for global and
1651 ** so that the orgn code will pick up the correct configuration information
1652 ** 25-May-2004 B3653935 GK Changed variables login_id, user_id to be assigned to FND_PROFILE.VALUE
1653 ** 01-dec-2004 kkillams orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
1654 ** 18-APR-2006 kmotupal Added check for default status while retrieving recipe details
1655 ** 30-MAY-2006 Kalyani Bug 5218106 Added code to default recipe_type from orgn parameters.
1656 ** 03-Jan-07 Kapil M LCF-GMO ME : Bug#5458666. Added routing_id to retrieve_recipe
1657 ** 13-FEB-2008 Uday Phadtare Bug 6758122. Retrive formula_desc1 from fm_form_mst_tl when formula_desc1 in fm_form_mst_b is NULL.
1658 **+==============================================================================================================================+*/
1659
1660
1661 PROCEDURE retrieve_recipe(p_formula_id IN NUMBER,
1662 p_routing_id IN NUMBER DEFAULT NULL,
1663 l_recipe_tbl OUT NOCOPY GMD_RECIPE_HEADER.recipe_hdr,
1664 l_recipe_flex OUT NOCOPY GMD_RECIPE_HEADER.flex,
1665 x_return_status OUT NOCOPY VARCHAR2) IS
1666
1667 -- Cursors
1668 CURSOR c_get_formula_info IS
1669 SELECT formula_no, formula_vers, formula_desc1,
1670 owner_organization_id, formula_status, owner_id
1671 FROM fm_form_mst_b
1672 WHERE formula_id = p_formula_id;
1673 LocalFormRecord c_get_formula_info%ROWTYPE;
1674
1675 -- Kapil LCF-GMO ME : Bug#5458666
1676 CURSOR c_get_routing_info IS
1677 SELECT routing_no , routing_vers
1678 FROM GMD_ROUTINGS_B
1679 WHERE routing_id = p_routing_id;
1680 LocalRoutRecord c_get_routing_info%ROWTYPE;
1681
1682 CURSOR c_get_recipe_id IS
1683 SELECT gmd_recipe_id_s.NEXTVAL
1684 FROM FND_DUAL;
1685
1686 CURSOR c_get_item_id IS
1687 SELECT inventory_item_id
1688 FROM fm_matl_dtl
1689 WHERE formula_id = p_formula_id
1690 AND line_type = 1;
1691
1692 CURSOR c_get_recipe_info(l_orgn_id NUMBER) IS
1693 SELECT recipe_naming_convention, created_by, last_update_login
1694 FROM gmd_recipe_generation
1695 WHERE (organization_id = l_orgn_id OR
1696 organization_id IS NULL)
1697 ORDER BY organization_id;
1698 LocalInfoRecord c_get_recipe_info%ROWTYPE;
1699
1700 CURSOR c_get_version(l_recipe_no VARCHAR2) IS
1701 SELECT max(recipe_version)
1702 FROM gmd_recipes_b
1703 WHERE recipe_no = l_recipe_no;
1704 LocalVersRecord c_get_version%ROWTYPE;
1705
1706 CURSOR c_check_recipe(l_recipe_no VARCHAR2) IS
1707 SELECT *
1708 FROM gmd_recipes_b
1709 WHERE recipe_no = l_recipe_no;
1710 LocalCheckRecord c_check_recipe%ROWTYPE;
1711
1712 CURSOR c_get_item(l_item_id NUMBER) IS
1713 SELECT description item_desc1, concatenated_segments item_no
1714 FROM mtl_system_items_kfv
1715 WHERE inventory_item_id = l_item_id;
1716 LocalItemRecord c_get_item%ROWTYPE;
1717
1718 --Bug 6758122
1719 CURSOR c_get_frmdesc IS
1720 SELECT formula_desc1
1721 FROM fm_form_mst_tl
1722 WHERE formula_id = p_formula_id
1723 AND language = USERENV('LANG');
1724
1725 -- Local Variables
1726 l_recipe_id NUMBER(15);
1727 l_recipe_version NUMBER(5) := 1;
1728 l_delete_mark NUMBER(5);
1729 l_recipe_name NUMBER(5);
1730 i BINARY_INTEGER := 2;
1731 l_user_id FND_USER.user_id%TYPE; --NUMBER;
1732 l_login_id NUMBER;
1733 l_recipe_no VARCHAR2(32);
1734 l_orgn_ID NUMBER;
1735 l_item_id NUMBER;
1736 l_recipe_description GMD_RECIPES_TL.RECIPE_DESCRIPTION%TYPE; -- bug14480509
1737 l_default_recipe_status gmd_api_grp.status_rec_type;
1738
1739 l_routing_no VARCHAR2(32) := NULL;
1740 l_routing_vers NUMBER := NULL;
1741 -- Exceptions
1742 create_recipe_err EXCEPTION;
1743
1744 BEGIN
1745
1746 x_return_status := FND_API.G_RET_STS_SUCCESS;
1747
1748 OPEN c_get_recipe_id;
1749 FETCH c_get_recipe_id INTO l_recipe_id;
1750 CLOSE c_get_recipe_id;
1751
1752 OPEN c_get_formula_info;
1753 FETCH c_get_formula_info INTO LocalFormRecord;
1754 IF c_get_formula_info%FOUND THEN
1755 l_orgn_id := LocalFormRecord.owner_organization_id;
1756
1757 OPEN c_get_recipe_info(l_orgn_id);
1758 FETCH c_get_recipe_info INTO LocalInfoRecord;
1759 IF c_get_recipe_info%FOUND THEN
1760 l_recipe_name := LocalInfoRecord.recipe_naming_convention;
1761 l_user_id := FND_PROFILE.VALUE('USER_ID');
1762 l_login_id := FND_PROFILE.VALUE ('LOGIN_ID');
1763 END IF;
1764 CLOSE c_get_recipe_info;
1765
1766 IF l_recipe_name = 0 THEN
1767 OPEN c_get_item_id;
1768 FETCH c_get_item_id INTO l_item_id;
1769 CLOSE c_get_item_id;
1770
1771 OPEN c_get_item(l_item_id);
1772 FETCH c_get_item INTO LocalItemRecord;
1773 IF c_get_item%FOUND THEN
1774 l_recipe_no := LocalItemRecord.item_no;
1775 l_recipe_description := LocalItemRecord.item_desc1;
1776 OPEN c_check_recipe(l_recipe_no);
1777 FETCH c_check_recipe INTO LocalCheckRecord;
1778 IF c_check_recipe%FOUND THEN
1779 OPEN c_get_version(l_recipe_no);
1780 FETCH c_get_version INTO l_recipe_version;
1781 l_recipe_version := l_recipe_version + 1;
1782 CLOSE c_get_version;
1783 ELSIF (LocalFormRecord.formula_vers = 0) THEN
1784 -- If formula vers is 0, start recipe vers with 0
1785 l_recipe_version := 0;
1786 ELSE
1787 -- Else start recipe vers with 1
1788 l_recipe_version := 1;
1789 END IF;
1790 CLOSE c_check_recipe;
1791 END IF;
1792 CLOSE c_get_item;
1793 ELSE
1794 l_recipe_no := LocalFormRecord.formula_no;
1795 OPEN c_check_recipe(l_recipe_no);
1796 FETCH c_check_recipe INTO LocalCheckRecord;
1797 IF c_check_recipe%FOUND THEN
1798 OPEN c_get_version(l_recipe_no);
1799 FETCH c_get_version INTO l_recipe_version;
1800 l_recipe_version := l_recipe_version + 1;
1801 CLOSE c_get_version;
1802 ELSIF (LocalFormRecord.formula_vers = 0) THEN
1803 -- If formula vers is 0, start recipe vers with 0
1804 l_recipe_version := 0;
1805 ELSE
1806 -- Else start recipe vers with 1
1807 l_recipe_version := 1;
1808 END IF;
1809 l_recipe_description := LocalFormRecord.formula_desc1;
1810 --Bug 6758122
1811 IF l_recipe_description IS NULL THEN
1812 OPEN c_get_frmdesc;
1813 FETCH c_get_frmdesc INTO l_recipe_description;
1814 CLOSE c_get_frmdesc;
1815 END IF;
1816 END IF;
1817
1818 l_recipe_tbl.recipe_id := l_recipe_id;
1819
1820 IF l_recipe_description IS NULL THEN
1821 l_recipe_description := l_recipe_no;
1822 END IF;
1823 -- Kapil LCF-GMO ME : Bug#5458666
1824 -- To get Routing details
1825 IF p_routing_id IS NOT NULL THEN
1826 OPEN c_get_routing_info;
1827 FETCH c_get_routing_info INTO LocalRoutRecord;
1828 l_routing_no := LocalRoutRecord.routing_no;
1829 l_routing_vers := LocalRoutRecord.routing_vers;
1830 CLOSE c_get_routing_info;
1831 END IF;
1832
1833 -- Bug# 4504631 kmotupal
1834 -- Added check for default status while retrieving recipe details
1835 get_status_details (V_entity_type => 'RECIPE',
1836 V_orgn_id => l_orgn_id,
1837 X_entity_status => l_default_recipe_status);
1838
1839 l_recipe_tbl.recipe_description := l_recipe_description;
1840 l_recipe_tbl.recipe_no := l_recipe_no;
1841 l_recipe_tbl.recipe_version := l_recipe_version;
1842 l_recipe_tbl.user_id := l_user_id;
1843 l_recipe_tbl.user_name := NULL;
1844 l_recipe_tbl.owner_organization_id := l_orgn_id;
1845 l_recipe_tbl.creation_organization_id := l_orgn_id;
1846 l_recipe_tbl.formula_id := p_formula_id;
1847 l_recipe_tbl.formula_no := LocalFormRecord.formula_no;
1848 l_recipe_tbl.formula_vers := LocalFormRecord.formula_vers;
1849 -- Kapil LCF-GMO ME : Bug#5458666 , Pass the routing details.
1850 l_recipe_tbl.routing_id := p_routing_id;
1851 l_recipe_tbl.routing_no := l_routing_no;
1852 l_recipe_tbl.routing_vers := l_routing_vers;
1853 l_recipe_tbl.project_id := NULL;
1854 l_recipe_tbl.recipe_status := l_default_recipe_status.entity_status;
1855 l_recipe_tbl.planned_process_loss := NULL;
1856 l_recipe_tbl.text_code := NULL;
1857 l_recipe_tbl.delete_mark := 0;
1858 l_recipe_tbl.creation_date := sysdate;
1859 l_recipe_tbl.created_by := l_user_id;
1860 l_recipe_tbl.last_updated_by := l_user_id;
1861 l_recipe_tbl.last_update_date := sysdate;
1862 l_recipe_tbl.last_update_login := l_login_id;
1863 l_recipe_tbl.owner_id := LocalFormRecord.owner_id;
1864 l_recipe_tbl.owner_organization_id := LocalFormRecord.owner_organization_id;
1865 l_recipe_tbl.owner_lab_type := NULL;
1866 l_recipe_tbl.calculate_step_quantity := 0;
1867 -- Bug 5218106
1868 l_recipe_tbl.recipe_type:=get_recipe_type(l_orgn_id);
1869
1870
1871 END IF; --If c_get_formula_info%found
1872
1873 x_return_status := FND_API.G_RET_STS_SUCCESS;
1874 CLOSE c_get_formula_info;
1875
1876 END retrieve_recipe;
1877
1878 /*+========================================================================+
1879 ** Name : check_orgn_status
1880 ** Created 18-OCT-2004
1881 ** Description
1882 ** Function to check the organization passed is process enabled.
1883 **+========================================================================+*/
1884
1885 FUNCTION check_orgn_status (V_organization_id IN NUMBER) RETURN BOOLEAN IS
1886 CURSOR Check_orgn_exists IS
1887 SELECT 1
1888 FROM mtl_parameters
1889 WHERE organization_id = V_organization_id
1890 AND process_enabled_flag = 'Y';
1891
1892 l_temp NUMBER;
1893 BEGIN
1894 IF (V_organization_id IS NOT NULL) THEN
1895 --Check the organization id passed is process enabled if not raise an error message
1896 OPEN check_orgn_exists;
1897 FETCH check_orgn_exists INTO l_temp;
1898 IF (check_orgn_exists%NOTFOUND) THEN
1899 CLOSE check_orgn_exists;
1900 RETURN FALSE;
1901 END IF;
1902 CLOSE check_orgn_exists;
1903 END IF;
1904 RETURN TRUE;
1905 END check_orgn_status;
1906
1907 /*###############################################################
1908 # NAME
1909 # check_item_exists
1910 # SYNOPSIS
1911 # proc check_item_exists
1912 # DESCRIPTION
1913 # Procedure used to check if the organization has access to the items
1914 # Kalyani 23-Jun-2006 B5350197 Serial control items not allowed.
1915 # Raju 29-Jan-2008 B6772070 Added AND SERIAL_NUMBER_CONTROL_CODE in (1,6) in where clause.
1916 # TO allow serial control items at sales order issue.
1917 ###############################################################*/
1918
1919 PROCEDURE check_item_exists (p_formula_id IN NUMBER,
1920 x_return_status OUT NOCOPY VARCHAR2,
1921 p_organization_id IN NUMBER DEFAULT NULL,
1922 p_orgn_code IN VARCHAR2 DEFAULT NULL,
1923 p_production_check IN BOOLEAN DEFAULT FALSE,
1924 p_costing_check IN BOOLEAN DEFAULT FALSE) IS
1925 X_ret NUMBER;
1926 X_item_no VARCHAR2(2000);
1927 X_organization_id NUMBER;
1928 X_orgn_code VARCHAR2(3);
1929 X_item_list VARCHAR2(2000);
1930 X_item_revision VARCHAR2(2000);
1931 X_item_rev_list VARCHAR2(2000);
1932
1933 CURSOR Cur_check_item (V_organization_id NUMBER) IS
1934 SELECT inventory_item_id
1935 FROM fm_matl_dtl d
1936 WHERE formula_id = p_formula_id
1937 AND NOT EXISTS (SELECT 1
1938 FROM mtl_system_items_b
1939 WHERE inventory_item_id = d.inventory_item_id
1940 AND organization_id = V_organization_id
1941 AND recipe_enabled_flag = 'Y');
1942 CURSOR Cur_item IS
1943 SELECT inventory_item_id
1944 FROM fm_matl_dtl
1945 WHERE formula_id = p_formula_id;
1946
1947 CURSOR Cur_check_item_revision (V_organization_id NUMBER) IS
1948 SELECT d.revision, b.concatenated_segments
1949 FROM fm_matl_dtl d, mtl_system_items_kfv b
1950 WHERE formula_id = p_formula_id
1951 AND b.inventory_item_id = d.inventory_item_id
1952 AND b.organization_id = V_organization_id
1953 AND revision IS NOT NULL
1954 AND NOT EXISTS (SELECT 1
1955 FROM mtl_item_revisions
1956 WHERE inventory_item_id = d.inventory_item_id
1957 AND organization_id = V_organization_id
1958 AND revision = d.revision);
1959
1960 CURSOR Cur_check_item_prod_enabled (V_organization_id NUMBER) IS
1961 SELECT inventory_item_id
1962 FROM fm_matl_dtl d
1963 WHERE formula_id = p_formula_id
1964 AND NOT EXISTS (SELECT 1
1965 FROM mtl_system_items_b
1966 WHERE inventory_item_id = d.inventory_item_id
1967 AND organization_id = V_organization_id
1968 AND process_execution_enabled_flag = 'Y');
1969
1970 CURSOR Cur_check_item_cost_enabled (V_organization_id NUMBER) IS
1971 SELECT inventory_item_id
1972 FROM fm_matl_dtl d
1973 WHERE formula_id = p_formula_id
1974 AND NOT EXISTS (SELECT 1
1975 FROM mtl_system_items_b
1976 WHERE inventory_item_id = d.inventory_item_id
1977 AND organization_id = V_organization_id
1978 AND process_costing_enabled_flag = 'Y');
1979
1980 -- Bug 5350197
1981 CURSOR Cur_check_item_serial_enabled (V_organization_id NUMBER) IS
1982 SELECT inventory_item_id
1983 FROM fm_matl_dtl d
1984 WHERE formula_id = p_formula_id
1985 AND NOT EXISTS (SELECT 1
1986 FROM mtl_system_items_b
1987 WHERE inventory_item_id = d.inventory_item_id
1988 AND organization_id = V_organization_id
1989 AND serial_number_control_code IN (1,6));
1990
1991
1992 CURSOR Cur_get_item_no (V_inventory_item_id NUMBER) IS
1993 SELECT concatenated_segments
1994 FROM mtl_system_items_kfv
1995 WHERE inventory_item_id = V_inventory_item_id;
1996
1997 CURSOR Cur_get_org_id (V_org_code VARCHAR2) IS
1998 SELECT organization_id
1999 FROM mtl_parameters
2000 WHERE organization_code = V_org_code;
2001
2002 CURSOR Cur_get_org_code (V_org_id NUMBER) IS
2003 SELECT organization_code
2004 FROM mtl_parameters
2005 WHERE organization_id = V_org_id;
2006
2007 ORGN_MISSING EXCEPTION;
2008 FORMULA_MISSING EXCEPTION;
2009 ITEM_MISSING EXCEPTION;
2010
2011 BEGIN
2012 /* Initialize the return status */
2013 X_return_status := FND_API.g_ret_sts_success;
2014 IF p_formula_id IS NULL THEN
2015 RAISE formula_missing;
2016 END IF;
2017
2018 IF p_organization_id IS NULL THEN
2019 IF p_orgn_code IS NULL THEN
2020 RAISE orgn_missing;
2021 END IF;
2022
2023 OPEN Cur_get_org_id (P_orgn_code);
2024 FETCH Cur_get_org_id INTO X_organization_id;
2025 CLOSE Cur_get_org_id;
2026 ELSE
2027 X_organization_id := p_organization_id;
2028 END IF;
2029
2030 X_item_list := NULL;
2031 X_item_rev_list := NULL;
2032 OPEN Cur_check_item (X_organization_id);
2033 LOOP
2034 FETCH Cur_check_item INTO X_ret;
2035 EXIT WHEN Cur_check_item%NOTFOUND;
2036
2037 OPEN Cur_get_item_no (X_ret);
2038 FETCH Cur_get_item_no INTO X_item_no;
2039 CLOSE Cur_get_item_no;
2040
2041 IF X_item_list IS NULL THEN
2042 X_item_list := X_item_list||X_item_no;
2043 ELSE
2044 X_item_list := X_item_list||','||X_item_no;
2045 END IF;
2046 END LOOP;
2047 CLOSE Cur_check_item;
2048
2049 IF X_item_list IS NOT NULL THEN
2050 IF p_orgn_code IS NULL THEN
2051 OPEN Cur_get_org_code (X_organization_id);
2052 FETCH Cur_get_org_code INTO X_orgn_code;
2053 CLOSE Cur_get_org_code;
2054 ELSE
2055 X_orgn_code := p_orgn_code;
2056 END IF;
2057
2058 FND_MESSAGE.SET_NAME('GMD', 'GMD_RCP_ITEMORG_NOT_FOUND');
2059 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2060 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2061 FND_MSG_PUB.ADD;
2062 X_return_status := FND_API.g_ret_sts_error;
2063 RAISE ITEM_MISSING;
2064 END IF;
2065
2066 OPEN Cur_check_item_revision (X_organization_id);
2067 LOOP
2068 FETCH Cur_check_item_revision INTO X_item_revision, X_item_no;
2069 EXIT WHEN Cur_check_item_revision%NOTFOUND;
2070
2071 IF X_item_rev_list IS NULL THEN
2072 X_item_rev_list := X_item_rev_list||X_item_no||','||X_item_revision;
2073 ELSE
2074 X_item_rev_list := X_item_rev_list||','||X_item_no||','||X_item_revision;
2075 END IF;
2076 END LOOP;
2077 CLOSE Cur_check_item_revision;
2078
2079 IF X_item_rev_list IS NOT NULL THEN
2080 IF p_orgn_code IS NULL THEN
2081 OPEN Cur_get_org_code (X_organization_id);
2082 FETCH Cur_get_org_code INTO X_orgn_code;
2083 CLOSE Cur_get_org_code;
2084 ELSE
2085 X_orgn_code := p_orgn_code;
2086 END IF;
2087
2088 FND_MESSAGE.SET_NAME('GMD', 'GMD_RCP_ITEMORG_REV_NOT_FOUND');
2089 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2090 FND_MESSAGE.SET_TOKEN('ITEM REVISION',X_item_rev_list);
2091 FND_MSG_PUB.ADD;
2092 X_return_status := FND_API.g_ret_sts_error;
2093 RAISE ITEM_MISSING;
2094 END IF;
2095
2096 IF p_production_check THEN
2097 OPEN Cur_check_item_prod_enabled (X_organization_id);
2098 LOOP
2099 FETCH Cur_check_item_prod_enabled INTO X_ret;
2100 EXIT WHEN Cur_check_item_prod_enabled%NOTFOUND;
2101
2102 OPEN Cur_get_item_no (X_ret);
2103 FETCH Cur_get_item_no INTO X_item_no;
2104 CLOSE Cur_get_item_no;
2105
2106 IF X_item_list IS NULL THEN
2107 X_item_list := X_item_list||X_item_no;
2108 ELSE
2109 X_item_list := X_item_list||','||X_item_no;
2110 END IF;
2111 END LOOP;
2112 CLOSE Cur_check_item_prod_enabled;
2113
2114 IF X_item_list IS NOT NULL THEN
2115 IF p_orgn_code IS NULL THEN
2116 OPEN Cur_get_org_code (X_organization_id);
2117 FETCH Cur_get_org_code INTO X_orgn_code;
2118 CLOSE Cur_get_org_code;
2119 ELSE
2120 X_orgn_code := p_orgn_code;
2121 END IF;
2122
2123 FND_MESSAGE.SET_NAME('GMD', 'GMD_PROD_ITEMORG_NOT_FOUND');
2124 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2125 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2126 FND_MSG_PUB.ADD;
2127 X_return_status := FND_API.g_ret_sts_error;
2128 RAISE ITEM_MISSING;
2129 END IF;
2130 END IF;
2131 IF p_costing_check THEN
2132 OPEN Cur_check_item_cost_enabled (X_organization_id);
2133 LOOP
2134 FETCH Cur_check_item_cost_enabled INTO X_ret;
2135 EXIT WHEN Cur_check_item_cost_enabled%NOTFOUND;
2136
2137 OPEN Cur_get_item_no (X_ret);
2138 FETCH Cur_get_item_no INTO X_item_no;
2139 CLOSE Cur_get_item_no;
2140
2141 IF X_item_list IS NULL THEN
2142 X_item_list := X_item_list||X_item_no;
2143 ELSE
2144 X_item_list := X_item_list||','||X_item_no;
2145 END IF;
2146 END LOOP;
2147 CLOSE Cur_check_item_cost_enabled;
2148
2149 IF X_item_list IS NOT NULL THEN
2150 IF p_orgn_code IS NULL THEN
2151 OPEN Cur_get_org_code (X_organization_id);
2152 FETCH Cur_get_org_code INTO X_orgn_code;
2153 CLOSE Cur_get_org_code;
2154 ELSE
2155 X_orgn_code := p_orgn_code;
2156 END IF;
2157
2158 FND_MESSAGE.SET_NAME('GMD', 'GMD_COST_ITEMORG_NOT_FOUND');
2159 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2160 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2161 FND_MSG_PUB.ADD;
2162 X_return_status := FND_API.g_ret_sts_error;
2163 RAISE ITEM_MISSING;
2164 END IF;
2165 END IF;
2166
2167 --Bug 5350197
2168 OPEN Cur_check_item_serial_enabled (X_organization_id);
2169 LOOP
2170 FETCH Cur_check_item_serial_enabled INTO X_ret;
2171 EXIT WHEN Cur_check_item_serial_enabled%NOTFOUND;
2172
2173 OPEN Cur_get_item_no (X_ret);
2174 FETCH Cur_get_item_no INTO X_item_no;
2175 CLOSE Cur_get_item_no;
2176
2177 IF X_item_list IS NULL THEN
2178 X_item_list := X_item_list||X_item_no;
2179 ELSE
2180 X_item_list := X_item_list||','||X_item_no;
2181 END IF;
2182 END LOOP;
2183 CLOSE Cur_check_item_serial_enabled;
2184
2185 IF X_item_list IS NOT NULL THEN
2186 IF p_orgn_code IS NULL THEN
2187 OPEN Cur_get_org_code (X_organization_id);
2188 FETCH Cur_get_org_code INTO X_orgn_code;
2189 CLOSE Cur_get_org_code;
2190 ELSE
2191 X_orgn_code := p_orgn_code;
2192 END IF;
2193
2194 FND_MESSAGE.SET_NAME('GMD', 'GMD_SERIAL_ITEMS_NOT_ALLOWED');
2195 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2196 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2197 FND_MSG_PUB.ADD;
2198 X_return_status := FND_API.g_ret_sts_error;
2199 RAISE ITEM_MISSING;
2200 END IF;
2201
2202 EXCEPTION
2203 WHEN formula_missing THEN
2204 X_return_status := FND_API.g_ret_sts_error;
2205 gmd_api_grp.log_message('GMD_MISSING', 'FORMULA_ID');
2206 WHEN item_missing THEN
2207 X_return_status := FND_API.g_ret_sts_error;
2208 WHEN orgn_missing THEN
2209 X_return_status := FND_API.g_ret_sts_error;
2210 gmd_api_grp.log_message('GMD_MISSING', 'ORGN_CODE');
2211 WHEN OTHERS THEN
2212 X_return_status := FND_API.g_ret_sts_unexp_error;
2213 fnd_msg_pub.add_exc_msg (l_package_name, 'CHECK_ITEM_EXISTS');
2214 END check_item_exists;
2215
2216 /* *********************************************************************** *
2217 * Function *
2218 * Validate_um *
2219 * Parameter : item_uom_code IN varchar2 *
2220 * Description *
2221 * Checks if the uom_code passed is valid - Return True if it exists *
2222 * *********************************************************************** */
2223 FUNCTION Validate_um(pItem_uom_code IN VARCHAR2) RETURN BOOLEAN IS
2224 Cursor Item_um_cur IS
2225 Select 1 from dual
2226 Where exists (Select 1 from mtl_units_of_measure
2227 Where uom_code = pItem_uom_code);
2228 l_dummy_cnt NUMBER;
2229 BEGIN
2230 OPEN Item_um_cur;
2231 FETCH Item_um_cur into l_dummy_cnt;
2232 CLOSE Item_um_cur;
2233 Return (l_dummy_cnt IS NOT NULL);
2234 END Validate_um;
2235
2236
2237 /*======================================================================
2238 -- PROCEDURE :
2239 -- FETCH_PARM_VALUES
2240 --
2241 -- DESCRIPTION:
2242 -- This procedure is used to fetch the parameter values for a
2243 -- particular orgn_id. If orgn_id is NULL return the Global orgn. parameters
2244 --
2245 -- HISTORY
2246 -- Sriram.S 05-NOV-2004 Created
2247 --===================================================================== */
2248
2249 PROCEDURE FETCH_PARM_VALUES (P_orgn_id IN NUMBER,
2250 X_out_rec OUT NOCOPY GMD_PARAMETERS_DTL_PKG.parameter_rec_type,
2251 X_return_status OUT NOCOPY VARCHAR2) IS
2252
2253 CURSOR Cur_get_parameters IS
2254 SELECT parameter_name, parameter_value, parameter_type
2255 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2256 WHERE h.parameter_id = d.parameter_id
2257 AND h.organization_id = P_orgn_id
2258 UNION
2259 SELECT parameter_name, parameter_value, parameter_type
2260 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2261 WHERE h.parameter_id = d.parameter_id
2262 AND h.organization_id IS NULL
2263 AND NOT EXISTS (SELECT 1
2264 FROM gmd_parameters_hdr h1, gmd_parameters_dtl d1
2265 WHERE h1.parameter_id = d1.parameter_id
2266 AND h1.organization_id = P_orgn_id
2267 AND d1.parameter_name = d.parameter_name);
2268
2269
2270 CURSOR Cur_get_lab_plant_ind IS
2271 SELECT plant_ind, lab_ind
2272 FROM gmd_parameters_hdr
2273 WHERE organization_id = P_orgn_id;
2274
2275 l_Cur_get_parameters_fetch BOOLEAN := FALSE;
2276
2277 PARM_NOT_FOUND EXCEPTION;
2278
2279 BEGIN
2280
2281 /* Set return status to success initially */
2282 x_return_status := FND_API.G_RET_STS_SUCCESS;
2283
2284 OPEN Cur_get_lab_plant_ind;
2285 FETCH Cur_get_lab_plant_ind INTO x_out_rec.plant_ind, x_out_rec.lab_ind;
2286 IF P_orgn_id IS NOT NULL AND Cur_get_lab_plant_ind%NOTFOUND THEN
2287 -- If orgn id is NOT NULL and cursor fetched no record, raise exception
2288 RAISE PARM_NOT_FOUND;
2289 END IF;
2290 CLOSE Cur_get_lab_plant_ind;
2291
2292 FOR l_rec IN Cur_get_parameters LOOP
2293 l_Cur_get_parameters_fetch := TRUE;
2294
2295 IF l_rec.parameter_type = 1 THEN
2296 IF l_rec.parameter_name = 'GMD_FORMULA_VERSION_CONTROL' THEN
2297 x_out_rec.gmd_formula_version_control := l_rec.parameter_value;
2298 ELSIF l_rec.parameter_name = 'GMD_BYPRODUCT_ACTIVE' THEN
2299 x_out_rec.gmd_byproduct_active := l_rec.parameter_value;
2300 ELSIF l_rec.parameter_name = 'GMD_ZERO_INGREDIENT_QTY' THEN
2301 x_out_rec.gmd_zero_ingredient_qty := l_rec.parameter_value;
2302 ELSIF l_rec.parameter_name = 'GMD_MASS_UM_TYPE' THEN
2303 x_out_rec.gmd_mass_um_type := l_rec.parameter_value;
2304 ELSIF l_rec.parameter_name = 'GMD_VOLUME_UM_TYPE' THEN
2305 x_out_rec.gmd_volume_um_type := l_rec.parameter_value;
2306 ELSIF l_rec.parameter_name = 'FM_YIELD_TYPE' THEN
2307 x_out_rec.fm_yield_type := l_rec.parameter_value;
2308 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_FORM_STATUS' THEN
2309 x_out_rec.gmd_default_form_status := l_rec.parameter_value;
2310 ELSIF l_rec.parameter_name = 'GMI_LOTGENE_ENABLE_FMSEC' THEN
2311 x_out_rec.gmi_lotgene_enable_fmsec := l_rec.parameter_value;
2312 ELSIF l_rec.parameter_name = 'FM$DEFAULT_RELEASE_TYPE' THEN
2313 x_out_rec.fm$default_release_type := l_rec.parameter_value;
2314 END IF;
2315 ELSIF l_rec.parameter_type = 2 THEN
2316 IF l_rec.parameter_name = 'GMD_OPERATION_VERSION_CONTROL' THEN
2317 x_out_rec.gmd_operation_version_control := l_rec.parameter_value;
2318 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_OPRN_STATUS' THEN
2319 x_out_rec.gmd_default_oprn_status := l_rec.parameter_value;
2320 END IF;
2321 ELSIF l_rec.parameter_type = 3 THEN
2322 IF l_rec.parameter_name = 'GMD_ROUTING_VERSION_CONTROL' THEN
2323 x_out_rec.gmd_routing_version_control := l_rec.parameter_value;
2324 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_ROUT_STATUS' THEN
2325 x_out_rec.gmd_default_rout_status := l_rec.parameter_value;
2326 ELSIF l_rec.parameter_name = 'STEPRELEASE_TYPE' THEN
2327 x_out_rec.steprelease_type := l_rec.parameter_value;
2328 ELSIF l_rec.parameter_name = 'GMD_ENFORCE_STEP_DEPENDENCY' THEN
2329 x_out_rec.gmd_enforce_step_dependency := l_rec.parameter_value;
2330 END IF;
2331 ELSIF l_rec.parameter_type = 4 THEN
2332 IF l_rec.parameter_name = 'GMD_RECIPE_VERSION_CONTROL' THEN
2333 x_out_rec.gmd_recipe_version_control := l_rec.parameter_value;
2334 ELSIF l_rec.parameter_name = 'GMD_PROC_INSTR_PARAGRAPH' THEN
2335 x_out_rec.gmd_proc_instr_paragraph := l_rec.parameter_value;
2336 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_RECP_STATUS' THEN
2337 x_out_rec.gmd_default_recp_status := l_rec.parameter_value;
2338 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_VALR_STATUS' THEN
2339 x_out_rec.gmd_default_valr_status := l_rec.parameter_value;
2340 ELSIF l_rec.parameter_name = 'GMD_RECIPE_TYPE' THEN
2341 x_out_rec.gmd_recipe_type := l_rec.parameter_value;
2342 END IF;
2343 ELSIF l_rec.parameter_type = 6 THEN
2344 IF l_rec.parameter_name = 'GMD_COST_SOURCE_ORGN' THEN
2345 x_out_rec.gmd_cost_source_orgn := l_rec.parameter_value;
2346 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_SPEC_STATUS' THEN
2347 x_out_rec.gmd_default_spec_status := l_rec.parameter_value;
2348 END IF;
2349 END IF;
2350 END LOOP;
2351
2352 IF NOT l_Cur_get_parameters_fetch THEN
2353 -- If Flag is not set, raise exception.
2354 RAISE PARM_NOT_FOUND;
2355 END IF;
2356
2357 EXCEPTION
2358 WHEN PARM_NOT_FOUND THEN
2359 fnd_message.set_name ('GMD', 'GMD_PARM_NOT_FOUND');
2360 fnd_msg_pub.add;
2361 x_return_status := FND_API.G_RET_STS_SUCCESS;
2362 WHEN OTHERS THEN
2363 fnd_message.set_name ('GMD', 'GMD_PARM_NOT_FOUND');
2364 fnd_msg_pub.add;
2365 x_return_status := FND_API.G_RET_STS_ERROR;
2366
2367 END FETCH_PARM_VALUES;
2368
2369
2370 /*======================================================================
2371 -- PROCEDURE :
2372 -- FETCH_PARM_VALUES
2373 --
2374 -- DESCRIPTION:
2375 -- This procedure is used to fetch the parameter value of the profile name passed for a
2376 -- particular orgn_id. If orgn_id is NULL return the parameter value for Global orgn.
2377 --
2378 -- HISTORY
2379 -- Sriram.S 23-NOV-2004 Created
2380 --===================================================================== */
2381
2382 PROCEDURE FETCH_PARM_VALUES (P_orgn_id IN NUMBER,
2383 P_parm_name IN VARCHAR2,
2384 P_parm_value OUT NOCOPY VARCHAR2,
2385 X_return_status OUT NOCOPY VARCHAR2) IS
2386
2387 CURSOR Cur_get_parameter IS
2388 SELECT parameter_value
2389 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2390 WHERE h.parameter_id = d.parameter_id
2391 AND h.organization_id = P_orgn_id
2392 AND d.parameter_name = P_parm_name
2393 UNION
2394 SELECT parameter_value
2395 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2396 WHERE h.parameter_id = d.parameter_id
2397 AND h.organization_id IS NULL
2398 AND d.parameter_name = P_parm_name
2399 AND NOT EXISTS (SELECT 1
2400 FROM gmd_parameters_hdr h1, gmd_parameters_dtl d1
2401 WHERE h1.parameter_id = d1.parameter_id
2402 AND h1.organization_id = P_orgn_id
2403 AND d1.parameter_name = d.parameter_name);
2404
2405 PARM_NOT_FOUND EXCEPTION;
2406
2407 BEGIN
2408
2409 /* Set return status to success initially */
2410 x_return_status := FND_API.G_RET_STS_SUCCESS;
2411
2412 OPEN Cur_get_parameter;
2413 FETCH Cur_get_parameter INTO P_parm_value;
2414 IF Cur_get_parameter%NOTFOUND THEN
2415 RAISE PARM_NOT_FOUND;
2416 END IF;
2417 CLOSE Cur_get_parameter;
2418
2419 EXCEPTION
2420 WHEN PARM_NOT_FOUND THEN
2421 x_return_status := FND_API.G_RET_STS_SUCCESS;
2422 WHEN OTHERS THEN
2423 fnd_msg_pub.add_exc_msg (l_package_name, 'FETCH_PARM_VALUES');
2424 x_return_status := FND_API.G_RET_STS_ERROR;
2425 END FETCH_PARM_VALUES;
2426
2427 -- Bug number 4479101
2428 FUNCTION derive_ingredent_end (P_substitution_id IN NUMBER,
2429 p_item_id IN NUMBER,
2430 p_exclude_context IN VARCHAR2) RETURN DATE IS
2431
2432 l_count NUMBER;
2433 l_ingredent_end_date DATE;
2434 l_item_id NUMBER;
2435
2436 CURSOR cur_item_id IS
2437 SELECT original_inventory_item_id FROM GMD_ITEM_SUBSTITUTION_HDR_B
2438 WHERE substitution_id = p_substitution_id;
2439 CURSOR cur_count IS
2440 SELECT count(1) FROM GMD_ITEM_SUBSTITUTION_HDR_B
2441 WHERE original_inventory_item_id = l_item_id
2442 AND ( (p_exclude_context = 'Y' AND substitution_id <> p_substitution_id)
2443 OR p_exclude_context = 'N'
2444 );
2445 CURSOR cur_s_sub_date IS
2446 SELECT start_date FROM GMD_ITEM_SUBSTITUTION_HDR_B
2447 WHERE original_inventory_item_id = l_item_id
2448 AND ( (p_exclude_context = 'Y' AND substitution_id <> p_substitution_id)
2449 OR p_exclude_context = 'N'
2450 );
2451 CURSOR cur_m_sub_date IS
2452 SELECT start_date FROM GMD_ITEM_SUBSTITUTION_HDR_B
2453 WHERE original_inventory_item_id = l_item_id
2454 AND ( (p_exclude_context = 'Y' AND substitution_id <> p_substitution_id)
2455 OR p_exclude_context = 'N'
2456 )
2457 ORDER BY START_DATE;
2458 BEGIN
2459 IF (p_substitution_id IS NULL AND p_item_id IS NULL) OR
2460 (p_substitution_id IS NOT NULL AND p_item_id IS NOT NULL) OR
2461 (p_exclude_context ='Y' AND p_substitution_id IS NULL)THEN
2462 RETURN NULL;
2463 END IF;
2464
2465 IF p_item_id IS NOT NULL THEN
2466 l_item_id := p_item_id;
2467 ELSE
2468 OPEN cur_item_id;
2469 FETCH cur_item_id INTO l_item_id;
2470 CLOSE cur_item_id;
2471 END IF;
2472
2473 OPEN cur_count;
2474 FETCH cur_count INTO l_count;
2475 CLOSE cur_count;
2476
2477 IF l_count = 0 THEN
2478 RETURN NULL;
2479 ELSIF l_count = 1 THEN
2480 OPEN cur_s_sub_date;
2481 FETCH cur_s_sub_date INTO l_ingredent_end_date;
2482 CLOSE cur_s_sub_date;
2483 RETURN l_ingredent_end_date;
2484 ELSE
2485 OPEN cur_m_sub_date;
2486 FETCH cur_m_sub_date INTO l_ingredent_end_date;
2487 CLOSE cur_m_sub_date;
2488 RETURN l_ingredent_end_date;
2489 END IF;
2490 END derive_ingredent_end;
2491
2492 /*+========================================================================+
2493 ** Name : update_end_date
2494 ** Notes : This procedure updates the material end dates based on
2495 ** the substitution start date
2496 ** If everything is fine then OUT parameter
2497 ** x_return_status is set to 'S' else appropriate
2498 ** error message is put on the stack and error
2499 ** is returned.
2500 **
2501 ** HISTORY
2502 ** 30-Apr-2005 B4479101 TDaniel Created.
2503 ** 28-Nov-2006 B5640547 akaruppa Removed the organization_id check when
2504 ** updating fm_matl_dtl with end date.
2505 **+========================================================================+*/
2506
2507 PROCEDURE update_end_date (p_substitution_id IN NUMBER) IS
2508
2509 CURSOR Cur_get_substitution IS
2510 SELECT i.original_inventory_item_id, i.start_date, i.substitution_status,
2511 f.formula_id, i.owner_organization_id
2512 FROM gmd_item_substitution_hdr_b i, gmd_formula_substitution f
2513 WHERE i.substitution_id = p_substitution_id
2514 AND i.substitution_id = f.substitution_id;
2515
2516 CURSOR Cur_check_substitute (V_formula_id NUMBER, V_item_id NUMBER, V_orgn_id NUMBER) IS
2517 SELECT MIN(i.start_date)
2518 FROM gmd_item_substitution_hdr_b i, gmd_formula_substitution f
2519 WHERE f.formula_id = V_formula_id
2520 AND i.original_inventory_item_id = V_item_id
2521 AND i.owner_organization_id = V_orgn_id
2522 AND i.substitution_id <> p_substitution_id
2523 AND i.substitution_id = f.substitution_id
2524 AND i.substitution_status BETWEEN 700 AND 799;
2525
2526 l_start_date DATE;
2527
2528 BEGIN
2529 FOR l_rec IN Cur_get_substitution LOOP
2530 /* Substitution is approved so lets update the formula line end date */
2531 IF l_rec.substitution_status BETWEEN 700 AND 799 THEN
2532 UPDATE fm_matl_dtl
2533 SET ingredient_end_date = l_rec.start_date
2534 WHERE formula_id = l_rec.formula_id
2535 AND line_type = -1
2536 AND inventory_item_id = l_rec.original_inventory_item_id
2537 -- AND organization_id = l_rec.owner_organization_id
2538 AND (ingredient_end_date IS NULL OR ingredient_end_date > l_rec.start_date);
2539 /* If the substitution is obsolete then we need to reset the end date if it was stamped */
2540 /* by the current list */
2541 ELSIF l_rec.substitution_status BETWEEN 1000 AND 1099 THEN
2542 OPEN Cur_check_substitute (l_rec.formula_id,
2543 l_rec.original_inventory_item_id,
2544 l_rec.owner_organization_id);
2545 FETCH Cur_check_substitute INTO l_start_date;
2546 CLOSE Cur_check_substitute;
2547
2548 UPDATE fm_matl_dtl
2549 SET ingredient_end_date = l_start_date
2550 WHERE formula_id = l_rec.formula_id
2551 AND line_type = -1
2552 AND inventory_item_id = l_rec.original_inventory_item_id;
2553 -- AND organization_id = l_rec.owner_organization_id
2554
2555 END IF;
2556 END LOOP;
2557 END update_end_date;
2558
2559 /*+========================================================================+
2560 ** Name : get_message()
2561 ** Notes : This function return the top most message on the stack
2562 **
2563 ** HISTORY
2564 ** 30-Aug-2005 shyam Created.
2565 **+========================================================================+*/
2566 FUNCTION get_message RETURN Varchar2 IS
2567 l_msg_txt Varchar2(2000) := '';
2568 l_msg_index Number;
2569 Begin
2570 l_msg_txt := fnd_message.get;
2571
2572 IF (l_msg_txt IS NULL) THEN
2573 FND_MSG_PUB.Get(
2574 p_msg_index => 1,
2575 p_data => l_msg_txt,
2576 p_encoded => FND_API.G_FALSE,
2577 p_msg_index_out => l_msg_index);
2578 END IF;
2579
2580 Return l_msg_txt;
2581 End get_message;
2582
2583 /*+========================================================================+
2584 ** Name : get_recipe_type
2585 ** Notes : This procedure retrieves the recipe type for an organization.
2586 **
2587 ** HISTORY
2588 ** 11-Nov-2005 B4479101 TDaniel Created.
2589 **+========================================================================+*/
2590
2591 FUNCTION get_recipe_type (p_organization_id IN NUMBER) RETURN NUMBER IS
2592 l_recipe_type PLS_INTEGER;
2593 l_return_status VARCHAR2(1);
2594 l_exists PLS_INTEGER;
2595
2596 CURSOR Cur_get_master_org IS
2597 SELECT 1
2598 FROM sys.dual
2599 WHERE EXISTS (SELECT 1
2600 FROM mtl_parameters
2601 WHERE master_organization_id = p_organization_id);
2602 BEGIN
2603 /* First lets check if there is a value setup at org parameters level */
2604 GMD_API_GRP.fetch_parm_values(p_orgn_id => p_organization_id
2605 ,p_parm_name => 'GMD_RECIPE_TYPE'
2606 ,p_parm_value => l_recipe_type
2607 ,x_return_status => l_return_status);
2608 IF l_recipe_type IS NOT NULL THEN
2609 RETURN l_recipe_type;
2610 END IF;
2611
2612 /* Lets check if the organization passed is a master organization */
2613 OPEN Cur_get_master_org;
2614 FETCH Cur_get_master_org INTO l_exists;
2615 IF Cur_get_master_org%FOUND THEN
2616 -- Sriram. Bug 4672941
2617 -- If orgn is a Master orgn, return 'General' as recipe type
2618 l_recipe_type := 0;
2619 ELSE
2620 l_recipe_type := 1;
2621 END IF;
2622 CLOSE Cur_get_master_org;
2623 RETURN l_recipe_type;
2624 END get_recipe_type;
2625
2626 /*======================================================================
2627 -- Function :
2628 -- get_def_status_code
2629 -- KSHUKLA bug 5199586
2630 -- DESCRIPTION:
2631 -- Used to return the status code for an entity.
2632 -- REQUIREMENTS
2633 --
2634 -- SYNOPSIS:
2635 --
2636 --===================================================================== */
2637 FUNCTION get_def_status_code(p_entity_type varchar2,
2638 p_orgn_id NUMBER)
2639 RETURN NUMBER is
2640 l_entity_status gmd_api_grp.status_rec_type;
2641 BEGIN
2642 get_status_details(V_entity_type => p_entity_type,
2643 V_orgn_id =>p_orgn_id,
2644 X_entity_status => l_entity_status);
2645 return l_entity_status.entity_status;
2646 END get_def_status_code;
2647
2648
2649 /*+========================================================================+
2650 ** Name : validity_revision_check
2651 ** Notes : This procedure checks if the passed in item has revision
2652 ** associated with it in the given formula. It returns "Y" if
2653 ** the item is defined as a product with revision in the formula.
2654 ** Also, it returns the revision value, if there is a single
2655 ** revision for the item in the formula.
2656 ** HISTORY
2657 ** 21-Jun-2006 B5309386 TDaniel Created.
2658 **+========================================================================+*/
2659
2660 PROCEDURE validity_revision_check (p_formula_id IN NUMBER,
2661 p_organization_id IN NUMBER,
2662 p_inventory_item_id IN NUMBER,
2663 x_enable_revision OUT NOCOPY VARCHAR2,
2664 x_revision OUT NOCOPY VARCHAR2) IS
2665 CURSOR Cur_get_prod_lines IS
2666 SELECT revision
2667 FROM fm_matl_dtl
2668 WHERE formula_id = p_formula_id
2669 AND inventory_item_id = p_inventory_item_id
2670 AND line_type = 1
2671 ORDER BY revision;
2672
2673 CURSOR Cur_check_revision (V_revision VARCHAR2) IS
2674 SELECT 1
2675 FROM sys.dual
2676 WHERE EXISTS (SELECT 1
2677 FROM mtl_item_revisions
2678 WHERE inventory_item_id = p_inventory_item_id
2679 AND organization_id = p_organization_id
2680 AND revision = V_revision);
2681
2682 l_revision_found PLS_INTEGER := 0;
2683 l_exists PLS_INTEGER;
2684 BEGIN
2685 IF p_organization_id IS NOT NULL THEN
2686 /* Lets initialize enable revision to N as default */
2687 x_enable_revision := 'N';
2688
2689 /* Lets get all the formula product lines for this item */
2690 FOR l_rec IN Cur_get_prod_lines LOOP
2691 /* If the line has a revision associated with it */
2692 IF l_rec.revision IS NOT NULL THEN
2693 /* Check if this revision has already been validated */
2694 IF NVL(x_revision, 'ZZZZ') <> l_rec.revision THEN
2695 /* Lets verify if this revision exists for this item */
2696 /* under the organization that has been passed in */
2697 OPEN Cur_check_revision (l_rec.revision);
2698 FETCH Cur_check_revision INTO l_exists;
2699 IF Cur_check_revision%FOUND THEN
2700 /* This revision exists for the organization so */
2701 /* lets set our variables properly */
2702 l_revision_found := l_revision_found + 1;
2703 x_revision := l_rec.revision;
2704 END IF;
2705 CLOSE Cur_check_revision;
2706 END IF; /* IF NVL(x_revision, 'ZZZZ') <> l_rec.revision */
2707 ELSE
2708 /* We have to increment this variable though the revison */
2709 /* is NULL to catch the case where the customer could set */
2710 /* a product with revision and without a revision in the formula */
2711 /* in this case we should not pass back a default revision */
2712 l_revision_found := l_revision_found + 1;
2713 END IF; /* IF l_rec.revision IS NOT NULL */
2714
2715 /* if we find two records with different revision then we */
2716 /* need not continue */
2717 IF l_revision_found > 1 THEN
2718 /* Since there are multiple revisions for the line */
2719 /* there will be no default value populated */
2720 X_revision := NULL;
2721 x_enable_revision := 'Y'; -- Bug 5309386 rework
2722 EXIT;
2723 END IF;
2724 END LOOP;
2725 ELSE
2726 /* If the validity rule is a global one then it does */
2727 /* does not make sense to provide a revision */
2728 x_enable_revision := 'N';
2729 END IF;
2730 END validity_revision_check;
2731
2732 END gmd_api_grp;
[Home] [Help]