1 PACKAGE BODY gmd_api_grp AS
2 /* $Header: GMDGAPIB.pls 120.32 2008/01/29 19:55:26 rnalla noship $ */
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
84 IF NVL (gmd_api_grp.user_id, 0) = 0 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 CURSOR Cur_ownerorgn_id IS
705 SELECT 1
706 FROM SYS.DUAL
707 WHERE EXISTS (SELECT 1
708 from org_access_view a, mtl_parameters b
709 where a.organization_id = b.organization_id
710 and b.organization_id = powner_orgn_id
711 and a.responsibility_id = l_resp_id
712 and b.process_enabled_flag = 'Y');
713
714 CURSOR Cur_get_orgn (V_organization_id NUMBER) IS
715 SELECT organization_code
716 FROM mtl_parameters
717 WHERE organization_id = V_organization_id;
718
719 /* Local variables. */
720 /* ================ */
721 l_ret NUMBER;
722 l_resp_name VARCHAR2(240) := fnd_global.resp_name;
723 l_owner_org VARCHAR2(3);
724
725 Update_not_allowed_exp EXCEPTION;
726 BEGIN
727 IF (powner_orgn_id IS NOT NULL) THEN
728 OPEN Cur_ownerorgn_id;
729 FETCH Cur_ownerorgn_id INTO l_ret;
730 IF (Cur_ownerorgn_id%NOTFOUND) THEN
731 CLOSE Cur_ownerorgn_id;
732 RAISE Update_not_allowed_exp;
733 END IF;
734 CLOSE Cur_ownerorgn_id;
735 END IF;
736
737 RETURN TRUE;
738 EXCEPTION
739 WHEN Update_not_allowed_exp THEN
740 /*Bug 4716697 - Thomas Daniel */
741 /*Added code to fetch the organization code to set the message*/
742 OPEN Cur_get_orgn (powner_orgn_id);
743 FETCH Cur_get_orgn INTO l_owner_org;
744 CLOSE Cur_get_orgn;
745 FND_MESSAGE.SET_NAME('GMD', 'GMD_USER_ORG_NOT_UPDATE');
746 FND_MESSAGE.SET_TOKEN('RESP_NAME',l_resp_name);
747 FND_MESSAGE.SET_TOKEN('ORGN_CODE',l_owner_org);
748 FND_MSG_PUB.ADD;
749 RETURN FALSE;
750 WHEN OTHERS THEN
751 FND_MESSAGE.SET_NAME('GMD', 'GMD_INV_USER_ORGANIZATION');
752 FND_MSG_PUB.ADD;
753 RETURN FALSE;
754 END OrgnAccessible;
755
756 /* *********************************************************************** *
757 * Function *
758 * get_object_status_type *
759 * Parameter : Entity_id Number, Entity_name VARCHAR2 *
760 * Description *
761 * Checks if the user has access to the entity organization *
762 * *********************************************************************** */
763 FUNCTION get_object_status_type
764 ( pObject_Name VARCHAR2
765 , pObject_Id NUMBER)
766 RETURN GMD_STATUS_B.status_type%TYPE IS
767 l_status_type GMD_STATUS_B.status_type%TYPE;
768
769 CURSOR Cur_get_fsttyp(pobject_id NUMBER) IS
770 SELECT s.status_type
771 FROM fm_form_mst_b f,gmd_Status_b s
772 WHERE f.formula_id = pobject_id AND
773 f.formula_status = s.status_code;
774
775 CURSOR Cur_get_rsttyp(pobject_id NUMBER) IS
776 SELECT s.status_type
777 FROM gmd_recipes_b r,gmd_Status_b s
778 WHERE r.recipe_id = pobject_id AND
779 r.recipe_status = s.status_code;
780
781 CURSOR Cur_get_osttyp(pobject_id NUMBER) IS
782 SELECT s.status_type
783 FROM gmd_operations_b o,gmd_Status_b s
784 WHERE o.oprn_id = pobject_id AND
785 o.operation_status = s.status_code;
786
787 CURSOR Cur_get_rtsttyp(pobject_id NUMBER) IS
788 SELECT s.status_type
789 FROM gmd_routings_b r,gmd_Status_b s
790 WHERE r.routing_id = pobject_id AND
791 r.routing_status = s.status_code;
792
793 CURSOR Cur_get_vrttyp(pobject_id NUMBER) IS
794 SELECT s.status_type
795 FROM gmd_recipe_validity_rules v,gmd_Status_b s
796 WHERE v.recipe_validity_rule_id = pobject_id AND
797 v.validity_rule_status = s.status_code;
798 BEGIN
799 IF (pObject_id IS NOT NULL) THEN
800 IF (Upper(pObject_Name) = 'FORMULA') THEN
801 OPEN Cur_get_fsttyp(pObject_id);
802 FETCH Cur_get_fsttyp INTO l_Status_type;
803 CLOSE Cur_get_fsttyp;
804 ELSIF (Upper(pObject_Name) = 'RECIPE') THEN
805 OPEN Cur_get_rsttyp(pObject_id);
806 FETCH Cur_get_rsttyp INTO l_Status_type;
807 CLOSE Cur_get_rsttyp;
808 ELSIF (Upper(pObject_Name) = 'ROUTING') THEN
809 OPEN Cur_get_rtsttyp(pObject_id);
810 FETCH Cur_get_rtsttyp INTO l_Status_type;
811 CLOSE Cur_get_rtsttyp;
812 ELSIF (Upper(pObject_Name) = 'OPERATION') THEN
813 OPEN Cur_get_osttyp(pObject_id);
814 FETCH Cur_get_osttyp INTO l_Status_type;
815 CLOSE Cur_get_osttyp;
816 ELSIF (Upper(pObject_Name) = 'VALIDITY') THEN
817 OPEN Cur_get_vrttyp(pObject_id);
818 FETCH Cur_get_vrttyp INTO l_Status_type;
819 CLOSE Cur_get_vrttyp;
820 END IF;
821 END IF;
822 RETURN l_status_type ;
823 EXCEPTION
824 WHEN OTHERS THEN
825 fnd_msg_pub.add_exc_msg (l_package_name, 'GET_OBJECT_STATUS_TYPE');
826 RETURN Null;
827 END get_object_status_type;
828
829 /*======================================================================
830 # NAME
831 # Validate_with_dep_entities
832 # SYNOPSIS
833 # Proc Validate_with_dep_entities
834 # DESCRIPTION
835 ======================================================================*/
836 PROCEDURE Validate_with_dep_entities(V_type IN VARCHAR2,
837 V_entity_id IN NUMBER,
838 X_parent_check OUT NOCOPY BOOLEAN) IS
839 X_status VARCHAR2(5);
840 CURSOR Cur_get_recp_sts(entity_id NUMBER) IS
841 SELECT recipe_status
842 FROM gmd_recipes
843 WHERE recipe_id = entity_id
844 AND ((recipe_status between 700 and 799
845 OR recipe_status between 400 and 499));
846
847
848 CURSOR Cur_get_form_sts(entity_id NUMBER) IS
849 SELECT formula_status
850 FROM fm_form_mst
851 WHERE formula_id = entity_id
852 AND ((formula_status between 700 and 799
853 OR formula_status between 400 and 499));
854
855 CURSOR Cur_get_rout_sts(entity_id NUMBER) IS
856 SELECT routing_status
857 FROM fm_rout_hdr
858 WHERE routing_id = entity_id
859 AND ((routing_status between 700 and 799
860 OR routing_status between 400 and 499));
861
862 CURSOR Cur_get_oprn_sts(entity_id NUMBER) IS
863 SELECT operation_status
864 FROM gmd_operations
865 WHERE oprn_id = entity_id
866 AND ((operation_status between 700 and 799
867 OR operation_status between 400 and 499));
868 l_status gmd_status.status_code%TYPE;
869 l_parent_check BOOLEAN := FALSE;
870 BEGIN
871 IF (V_entity_id IS NULL) THEN
872 RETURN;
873 END IF;
874
875 IF (l_debug = 'Y') THEN
876 gmd_debug.put_line('In GMD_API_GRP.validate_with_dep_entities - '||
877 'v_entity_id = '||v_entity_Id||' and entity type = '||v_type);
878 END IF;
879
880 IF (v_entity_id IS NOT NULL) THEN
881 IF (V_type = 'RECIPE') THEN
882 OPEN Cur_get_recp_sts(v_entity_id);
883 FETCH Cur_get_recp_sts INTO l_status;
884 IF (l_debug = 'Y') THEN
885 gmd_debug.put_line('In GMD_API_GRP.validate_with_dep_entities - '||
886 'About to check for Recipe - Vr dep status = '||l_status);
887 END IF;
888 IF (Cur_get_recp_sts%FOUND) THEN
889 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
890 IF (l_debug = 'Y') THEN
891 gmd_debug.put_line('In GMD_API_GRP.validate_with_dep_entities - '||
892 ' There is VR dependency for this Recipe = '||v_entity_Id);
893 END IF;
894
895 l_parent_check := TRUE;
896 FND_MESSAGE.SET_NAME('GMD','GMD_RECIPE_BATCH_DEP');
897 END IF;
898 END IF;
899 CLOSE Cur_get_recp_sts;
900 ELSIF(v_type = 'ROUTING') THEN
901 OPEN Cur_get_rout_sts(v_entity_id);
902 FETCH Cur_get_rout_sts INTO l_status;
903 IF (Cur_get_rout_sts%FOUND) THEN
904 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
905 l_parent_check := TRUE;
906 FND_MESSAGE.SET_NAME('GMD','GMD_ROUTING_RECIPE_DEP');
907 END IF;
908 END IF;
909 CLOSE Cur_get_rout_sts;
910 ELSIF(V_type = 'FORMULA') THEN
911 OPEN Cur_get_form_sts(v_entity_id);
912 FETCH Cur_get_form_sts INTO l_status;
913 IF (Cur_get_form_sts%FOUND) THEN
914 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
915 l_parent_check := TRUE;
916 FND_MESSAGE.SET_NAME('GMD','GMD_FORMULA_RECIPE_DEP');
917 END IF;
918 END IF;
919 CLOSE Cur_get_form_sts;
920 ELSIF(v_type = 'OPERATION') THEN
921 OPEN Cur_get_oprn_sts(v_entity_id);
922 FETCH Cur_get_oprn_sts INTO l_status;
923 IF (Cur_get_oprn_sts%FOUND) THEN
924 IF (NOT gmd_status_code.check_parent_status(v_type,v_entity_id)) THEN
925 l_parent_check := TRUE;
926 FND_MESSAGE.SET_NAME('GMD','GMD_OPERATION_ROUT_DEP');
927 END IF;
928 END IF;
929 CLOSE Cur_get_oprn_sts;
930 END IF;
931 END IF;
932
933 x_parent_check := l_parent_check;
934
935 END Validate_with_dep_entities;
936
937
938 /*======================================================================
939 NAME
940 get_object_name_version
941 SYNOPSIS
942 Proc get_object_name_version
943 DESCRIPTION
944 Function returns Object_no (e.g Recipe_no) when vType = 'NAME'
945 returns Object_vers (e.g Recipe_version) when vType = 'VERSION'
946 returns Object_no and version (e.g Recipe_no||' - '||version)
947 when vType = 'NAME-VERSION'
948 ======================================================================*/
949 FUNCTION get_object_name_version(vEntity VARCHAR2
950 ,vEntity_id NUMBER
951 ,vtype VARCHAR2 DEFAULT 'NAME-VERSION')
952 RETURN VARCHAR2 IS
953 l_object_name VARCHAR2(100);
954 l_object_version VARCHAR2(10);
955 l_object_name_and_version VARCHAR2(240);
956 BEGIN
957 IF (vEntity_id IS NOT NULL) THEN
958 IF (vEntity = 'FORMULA') THEN
959 Select formula_no, formula_vers, formula_no||' - '||formula_vers
960 INTO l_object_name, l_object_version, l_object_name_and_version
961 FROM fm_form_mst_b
962 WHere formula_id = vEntity_id;
963 ELSIF (vEntity = 'ROUTING') THEN
964 Select routing_no, routing_vers, routing_no||' - '||routing_vers
965 INTO l_object_name, l_object_version, l_object_name_and_version
966 FROM gmd_routings_b
967 WHere routing_id = vEntity_id;
968 ELSIF (vEntity = 'OPERATION') THEN
969 Select oprn_no, oprn_vers, oprn_no||' - '||oprn_vers
970 INTO l_object_name, l_object_version, l_object_name_and_version
971 FROM gmd_operations_b
972 WHere oprn_id = vEntity_id;
973 ELSIF (vEntity = 'RECIPE') THEN
974 Select recipe_no, recipe_version, recipe_no||' - '||recipe_version
975 INTO l_object_name, l_object_version, l_object_name_and_version
976 FROM gmd_recipes_b
977 WHere recipe_id = vEntity_id;
978 ELSIF (vEntity = 'VALIDITY') THEN
979 Select r.recipe_no, r.recipe_version, r.recipe_no||' - '||r.recipe_version
980 INTO l_object_name, l_object_version, l_object_name_and_version
981 FROM gmd_recipes_b r, gmd_recipe_validity_rules v
982 WHere v.recipe_id = vEntity_id
983 AND v.recipe_id = r.recipe_id;
984 END IF;
985 END IF;
986
987 IF vType = 'NAME' THEN
988 Return l_object_name;
989 ELSIF vType = 'VERSION' THEN
990 Return l_object_version;
991 ELSE
992 RETURN l_object_name_and_version;
993 END IF;
994
995 END get_object_name_version;
996
997 /********************************************************************************
998 * Name : get_formula_acces_type
999 *
1000 * Description: Function returns the acces type level of the user for a given formula.
1001 * Returns 'U', means user has updatable acces.
1002 * Returns 'V', means user has view acces.
1003 * Returns 'N', means no record setup - exceptional condition
1004 * Change History:
1005 * Who When What
1006 * TDANIEL 29-JUL-2005 Modified the code to handle NULL (-1) for formula id
1007 * and also modified for convergence changes.
1008 **********************************************************************************/
1009
1010 FUNCTION get_formula_access_type(p_formula_id IN PLS_INTEGER,
1011 p_owner_organization_id IN PLS_INTEGER)
1012 RETURN VARCHAR2 IS
1013
1014 /* Cursor Variables */
1015
1016 CURSOR get_vpd_flag IS
1017 SELECT active_formula_ind
1018 FROM gmd_vpd_security;
1019
1020 CURSOR Cur_check_orgn_access (V_default_user_id PLS_INTEGER) IS
1021 SELECT 1
1022 FROM sys.dual
1023 WHERE EXISTS ( SELECT 1
1024 FROM gmd_security_profiles sp
1025 WHERE sp.access_type_ind = 'U'
1026 AND ( responsibility_id IN ( SELECT rg.responsibility_id
1027 FROM FND_USER_RESP_GROUPS rg
1028 WHERE rg.user_id = fnd_global.user_id
1029 AND SYSDATE BETWEEN rg.start_date
1030 AND NVL(rg.end_date, SYSDATE)
1031 )
1032 OR ( sp.user_id = V_default_user_id
1033 OR sp.user_id = fnd_global.user_id
1034 )
1035 )
1036 AND organization_id = P_owner_organization_id
1037 AND (other_organization_id IS NULL
1038 OR EXISTS ( SELECT NULL
1039 FROM org_access a3
1040 WHERE a3.organization_id = sp.other_organization_id
1041 AND NVL(a3.disable_date, SYSDATE+1) >= SYSDATE
1042 AND a3.resp_application_id = fnd_global.resp_appl_id
1043 AND a3.responsibility_id = fnd_global.resp_id
1044 )
1045 OR NOT EXISTS ( SELECT NULL
1046 FROM org_access a4
1047 WHERE a4.organization_id = sp.other_organization_id
1048 AND NVL(a4.disable_date, SYSDATE+1) >=SYSDATE
1049 )
1050 )
1051 );
1052
1053
1054 CURSOR Cur_check_formula_access (V_default_user_id PLS_INTEGER) IS
1055 SELECT sp.access_type_ind
1056 FROM gmd_security_profiles sp
1057 WHERE sp.assign_method_ind = 'A'
1058 AND ( ( sp.user_id = V_default_user_id
1059 OR sp.user_id = fnd_global.user_id
1060 )
1061 OR ( EXISTS ( SELECT rg.responsibility_id
1062 FROM FND_USER_RESP_GROUPS rg
1063 WHERE rg.user_id = fnd_global.user_id
1064 AND sp.responsibility_id = rg.responsibility_id
1065 AND SYSDATE BETWEEN rg.start_date AND NVL(rg.end_date, SYSDATE)
1066 )
1067 )
1068 )
1069 AND ( EXISTS ( SELECT NULL
1070 FROM org_access a1
1071 WHERE ( ( sp.organization_id = a1.organization_id
1072 AND sp.other_organization_id IS NULL
1073 )
1074 OR sp.other_organization_id = a1.organization_id
1075 )
1076 AND NVL(a1.disable_date, SYSDATE+1) >= SYSDATE
1077 AND a1.resp_application_id = fnd_global.resp_appl_id
1078 AND a1.responsibility_id = fnd_global.resp_id
1079 )
1080 OR
1081 NOT EXISTS ( SELECT NULL
1082 FROM org_access a2
1083 WHERE ( ( sp.organization_id = a2.organization_id
1084 AND sp.other_organization_id IS NULL
1085 )
1086 OR sp.other_organization_id = a2.organization_id
1087 )
1088 AND NVL(a2.disable_date, SYSDATE+1) >=SYSDATE
1089 )
1090 )
1091 AND sp.organization_id = P_owner_organization_id
1092 UNION
1093 SELECT fs.access_type_ind
1094 FROM gmd_formula_security fs
1095 WHERE ( ( fs.user_id = V_default_user_id
1096 OR fs.user_id = fnd_global.user_id
1097 )
1098 OR ( EXISTS ( SELECT rg.responsibility_id
1099 FROM FND_USER_RESP_GROUPS rg
1100 WHERE rg.user_id = fnd_global.user_id
1101 AND fs.responsibility_id = rg.responsibility_id
1102 AND SYSDATE BETWEEN rg.start_date
1103 AND NVL(rg.end_date, SYSDATE)
1104 )
1105 )
1106 )
1107 AND (EXISTS ( SELECT NULL
1108 FROM org_access ou
1109 WHERE ( ( fs.organization_id = ou.organization_id
1110 AND fs.other_organization_id IS NULL
1111 )
1112 OR fs.other_organization_id = ou.organization_id
1113 )
1114 AND NVL(ou.disable_date, SYSDATE+1) >= SYSDATE
1115 AND ou.resp_application_id = fnd_global.resp_appl_id
1116 AND ou.responsibility_id = fnd_global.resp_id
1117 )
1118 OR
1119 NOT EXISTS ( SELECT NULL
1120 FROM org_access ou1
1121 WHERE ( ( ou1.organization_id = fs.organization_id
1122 AND fs.other_organization_id IS NULL
1123 )
1124 OR ou1.organization_id = fs.other_organization_id
1125 )
1126 AND NVL(ou1.disable_date, SYSDATE+1) >=SYSDATE
1127 )
1128 )
1129 AND fs.formula_id = P_formula_id;
1130
1131
1132 /* Local Variables */
1133
1134 l_vpd_flag VARCHAR2(1) := 'N';
1135 l_access_type_ind VARCHAR2(1);
1136 l_default_user_id VARCHAR2(240) := fnd_profile.value('GMD_DEFAULT_USER');
1137 l_exists PLS_INTEGER;
1138 BEGIN
1139 /* First check if the VPD flag is set */
1140 OPEN get_vpd_flag;
1141 FETCH get_vpd_flag INTO l_vpd_flag;
1142 CLOSE get_vpd_flag;
1143
1144 IF (l_vpd_flag = 'Y') THEN
1145 /* If there is no formula associated then we are checking if the user */
1146 /* has security to create or view formula for the organization passed */
1147 IF p_formula_id = -1 THEN
1148 OPEN Cur_check_orgn_access (l_default_user_id);
1149 FETCH Cur_check_orgn_access INTO l_exists;
1150 IF Cur_check_orgn_access%FOUND THEN
1151 l_access_type_ind := 'U';
1152 ELSE
1153 l_access_type_ind := 'V';
1154 END IF;
1155 CLOSE Cur_check_orgn_access;
1156 ELSE
1157 OPEN Cur_check_formula_access (l_default_user_id);
1158 FETCH Cur_check_formula_access INTO l_access_type_ind;
1159 IF Cur_check_formula_access%NOTFOUND THEN
1160 l_access_type_ind := 'N';
1161 END IF;
1162 CLOSE Cur_check_formula_access;
1163 END IF;
1164 RETURN l_access_type_ind;
1165 ELSE
1166 RETURN 'U';
1167 END IF;
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 fnd_msg_pub.add_exc_msg(l_package_name, 'GET_FORMULA_ACCESS_TYPE');
1171 RETURN 'N';
1172 END get_formula_access_type;
1173
1174
1175 ------------------------------------------------------------------
1176 --Created by : Sriram.S
1177 --Date created: 20-JAN-2004
1178 --
1179 --Purpose: Returns description of the Status Code
1180 --Known limitations/enhancements and/or remarks:
1181 --
1182 --Change History:
1183 --Who When What
1184 --SRSRIRAN 20-FEB-2004 Created w.r.t. bug 3408799
1185 -------------------------------------------------------------------
1186 FUNCTION get_status_desc (V_entity_status IN VARCHAR2 ) RETURN VARCHAR2 IS
1187 CURSOR Cur_get_status_desc IS
1188 SELECT description
1189 FROM gmd_status
1190 WHERE status_code = V_entity_status;
1191 X_description VARCHAR2(240);
1192 BEGIN
1193 IF (V_entity_status IS NOT NULL) THEN
1194 OPEN Cur_get_status_desc;
1195 FETCH Cur_get_status_desc INTO X_description;
1196 CLOSE Cur_get_status_desc;
1197 RETURN X_description;
1198 END IF;
1199 RETURN NULL;
1200 END get_status_desc;
1201
1202
1203 ------------------------------------------------------------------
1204 --Created by : Sriram.S
1205 --Date created: 20-JAN-2004
1206 --
1207 --Purpose: Returns the default status of the entity.
1208 --Known limitations/enhancements and/or remarks:
1209 --
1210 --Change History:
1211 --Who When What
1212 --SRSRIRAN 20-FEB-2004 Created w.r.t. bug 3408799
1213 --kkiallms 01-DEC-2004 Modified w.r.t. 4004501
1214 -------------------------------------------------------------------
1215 PROCEDURE get_status_details (V_entity_type IN VARCHAR2,
1216 V_orgn_id IN NUMBER,
1217 X_entity_status OUT NOCOPY GMD_API_GRP.status_rec_type) IS
1218
1219 CURSOR cur_def_status(cp_orgn_id NUMBER,
1220 cp_parameter_name gmd_parameters_dtl.parameter_name%TYPE)IS
1221 SELECT st.status_code
1222 ,st.description
1223 ,st.status_type
1224 FROM gmd_parameters_hdr h
1225 ,gmd_parameters_dtl d
1226 ,gmd_status st
1227 WHERE (h.organization_id = cp_orgn_id OR h.organization_id IS NULL)
1228 AND h.parameter_id = d.parameter_id
1229 AND d.parameter_name = cp_parameter_name
1230 AND st.status_code = parameter_value
1231 ORDER BY h.organization_id;
1232
1233
1234 CURSOR Cur_get_def_new_status IS
1235 SELECT b.status_code, b.description,b.status_type
1236 FROM gmd_status b
1237 WHERE b.status_code = 100;
1238
1239 BEGIN
1240 IF (V_entity_type = 'ROUTING') THEN
1241 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_ROUT_STATUS');
1242 FETCH cur_def_status INTO X_entity_status;
1243 CLOSE cur_def_status;
1244 ELSIF (V_entity_type = 'OPERATION') THEN
1245 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_OPRN_STATUS');
1246 FETCH cur_def_status INTO X_entity_status;
1247 CLOSE cur_def_status;
1248 ELSIF (V_entity_type = 'RECIPE') THEN
1249 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_RECP_STATUS');
1250 FETCH cur_def_status INTO X_entity_status;
1251 CLOSE cur_def_status;
1252 ELSIF (V_entity_type = 'FORMULA') THEN
1253 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_FORM_STATUS');
1254 FETCH cur_def_status INTO X_entity_status;
1255 CLOSE cur_def_status;
1256 ELSIF (V_entity_type = 'VALIDITY') THEN
1257 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_VALR_STATUS');
1258 FETCH cur_def_status INTO X_entity_status;
1259 CLOSE cur_def_status;
1260 ELSIF (V_entity_type = 'SUBSTITUTION') THEN --Bug 4479101
1261 OPEN cur_def_status(V_orgn_id,'GMD_DEFAULT_SUBS_STATUS');
1262 FETCH cur_def_status INTO X_entity_status;
1263 CLOSE cur_def_status;
1264
1265 END IF;
1266
1267 IF X_entity_status.entity_status IS NULL THEN
1268 OPEN CUR_get_def_new_status;
1269 FETCH Cur_get_def_new_status INTO X_entity_status;
1270 CLOSE Cur_get_def_new_status;
1271 END IF;
1272
1273 END get_status_details;
1274
1275
1276 ------------------------------------------------------------------
1277 --Created by : Sriram.S
1278 --Date created: 20-JAN-2004
1279 --
1280 --Purpose: Function returns the TRUE if V_entity_status status is
1281 -- valid for the given entity otherwise returns FALSE
1282 --Known limitations/enhancements and/or remarks:
1283 --
1284 --Change History:
1285 --Who When What
1286 --SRSRIRAN 20-FEB-2004 Created w.r.t. bug 3408799
1287 -------------------------------------------------------------------
1288 FUNCTION check_dependent_status(V_entity_type IN VARCHAR2,
1289 V_entity_id IN NUMBER,
1290 V_entity_status IN VARCHAR2) RETURN BOOLEAN IS
1291
1292 CURSOR Cur_get_def_oprn_status IS
1293 SELECT operation_status
1294 FROM gmd_operations
1295 WHERE oprn_id = V_entity_id
1296 AND operation_status >= V_entity_status;
1297 CURSOR Cur_get_def_formula_status IS
1298 SELECT formula_status FROM fm_form_mst
1299 WHERE formula_id = V_entity_id
1300 AND formula_status >= V_entity_status;
1301 CURSOR Cur_get_def_routing_status IS
1302 SELECT routing_status
1303 FROM fm_rout_hdr
1304 WHERE routing_id = V_entity_id
1305 AND routing_status >= V_entity_status;
1306 /* do we need this-not used*/
1307 l_operation_status gmd_operations.operation_status%TYPE;
1308 l_formula_status fm_form_mst.formula_status%TYPE;
1309 l_routing_status fm_rout_hdr.routing_status%TYPE;
1310 BEGIN
1311 /*Check all the operations inserted are of status APFLU or APFGU*/
1312 IF (V_entity_type = 'OPERATION') THEN
1313 OPEN Cur_get_def_oprn_status;
1314 FETCH Cur_get_def_oprn_status INTO l_operation_status;
1315 IF (Cur_get_def_oprn_status %NOTFOUND) THEN
1316 CLOSE Cur_get_def_oprn_status;
1317 RETURN TRUE;
1318 ELSE
1319 CLOSE Cur_get_def_oprn_status;
1320 RETURN FALSE;
1321 END IF;
1322 RETURN FALSE;
1323 END IF; --V_entity_type = 'OPERATION'
1324
1325 IF (V_entity_type = 'FORMULA') THEN
1326 OPEN Cur_get_def_formula_status;
1327 FETCH Cur_get_def_formula_status INTO l_formula_status;
1328 IF (Cur_get_def_formula_status %NOTFOUND) THEN
1329 CLOSE Cur_get_def_formula_status;
1330 RETURN TRUE;
1331 ELSE
1332 CLOSE Cur_get_def_formula_status;
1333 RETURN FALSE;
1334 END IF;
1335 RETURN FALSE;
1336 END IF; --V_entity_type = 'FORMULA'
1337
1338 IF (V_entity_type = 'ROUTING') THEN
1339 OPEN Cur_get_def_routing_status;
1340 FETCH Cur_get_def_routing_status INTO l_routing_status;
1341 IF (Cur_get_def_routing_status %NOTFOUND) THEN
1342 CLOSE Cur_get_def_routing_status;
1343 RETURN TRUE;
1344 ELSE
1345 CLOSE Cur_get_def_routing_status;
1346 RETURN FALSE;
1347 END IF; --Cur_get_def_routing_status %NOTFOUND
1348 RETURN FALSE;
1349 END IF; --V_entity_type = 'ROUTING'
1350 END check_dependent_status;
1351
1352 /*======================================================================
1353 -- PROCEDURE :
1354 -- set_activity_sequence_num
1355 --
1356 -- DESCRIPTION:
1357 -- This particular procedure is used to set the sequencing of the
1358 -- activities within an operation.
1359 -- REQUIREMENTS
1360 --
1361 -- SYNOPSIS:
1362 --
1363 --===================================================================== */
1364
1365 PROCEDURE set_activity_sequence_num(
1366 P_oprn_id IN NUMBER,
1367 P_user_id IN NUMBER,
1368 P_login_id IN NUMBER)
1369 IS
1370 /* this cursor selects the activities in the appropriate order, making the seq dep activity
1371 the first one the the others by offset then activity then the tie breaker of the surrogate
1372 key. The seq dep ind will hold the sequence numbers so we need to take those out of
1373 for validation purposes. The column seq_dep_order will make the seq dep activity first
1374 then all others will follow */
1375
1376 CURSOR Cur_get_activities (V_oprn_id IN NUMBER) IS
1377 SELECT oprn_line_id, NVL(sequence_dependent_ind,-1) sequence_dependent_ind
1378 FROM gmd_operation_activities
1379 WHERE oprn_id = v_oprn_id
1380 AND NVL(sequence_dependent_ind, 0) <> 1
1381 ORDER BY
1382 offset_interval,
1383 activity,
1384 oprn_line_id;
1385
1386 X_oprn_line_id NUMBER;
1387 X_seq_num NUMBER;
1388 X_seq_dep_ind NUMBER;
1389 BEGIN
1390 /* the seq num will always start at 100 for the first activity after the
1391 seq dep activity. */
1392 X_seq_num := 100;
1393
1394 OPEN Cur_get_activities (P_oprn_id);
1395 LOOP
1396 FETCH Cur_get_activities INTO X_oprn_line_id, X_seq_dep_ind;
1397 EXIT WHEN Cur_get_activities%NOTFOUND;
1398
1399 /* If the activity has been previously numbered and is in the right order
1400 no need to update it */
1401 IF X_seq_dep_ind <> X_seq_num THEN
1402 UPDATE gmd_operation_activities
1403 SET sequence_dependent_ind = X_seq_num,
1404 last_update_date = SYSDATE,
1405 last_updated_by = P_user_id,
1406 last_update_login = P_login_id
1407 WHERE oprn_line_id = X_oprn_line_id;
1408 END IF;
1409
1410 /* increment the seq num for each processed except the seq dep activity */
1411 X_seq_num := X_seq_num + 100;
1412 END LOOP;
1413 CLOSE Cur_get_activities;
1414 END set_activity_sequence_num;
1415
1416 /*========================================================================+
1417 ** Name : retrieve_vr
1418 ** Notes : This procedure receives as input recipe record and
1419 ** retrieves validity rules records.
1420 **
1421 ** If everything is fine then OUT parameter
1422 ** x_return_status is set to 'S' else appropriate
1423 ** error message is put on the stack and error
1424 ** is returned.
1425 **
1426 ** HISTORY
1427 ** 01-Mar-2004 B3604554 GK Created.
1428 ** 21-May-2004 B3642937 GK Called the procedure get_status_details and
1429 ** Assigned the default_status to the validity_status
1430 ** 24-May-2004 B3643405 GK Added the statement orgn IS NULL to cursor c_get_recipe_info
1431 ** 25-May-2004 B3645706 GK Removed the statement orgn IS NULL to cursor c_get_recipe_info
1432 ** and created a new cursor c_get_global_info to accomodate for global and
1433 ** so that the orgn code will pick up the correct configuration information
1434 ** 25-May-2004 B3653935 GK Changed variables login_id, user_id to be assigned to FND_PROFILE.VALUE
1435 ** 25-May-2004 B GK In the global records the start date and end date still referenced the local so changed this
1436 ** 01-dec-2004 kkillams orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
1437 ** 10-Feb-2005 4004501 Krishna Added Revision column, in the retrival information.
1438 ** 18-APR-2006 kmotupal Added check for default status while retrieving recipe VR details
1439 **+========================================================================*/
1440
1441
1442 PROCEDURE retrieve_vr(p_formula_id IN NUMBER,
1443 l_recipe_vr_tbl OUT NOCOPY GMD_RECIPE_DETAIL.recipe_vr,
1444 l_vr_flex OUT NOCOPY GMD_RECIPE_DETAIL.flex,
1445 x_return_status OUT NOCOPY VARCHAR2,
1446 p_recipe_use IN NUMBER) IS
1447
1448 CURSOR c_get_vr_id IS
1449 SELECT gmd_recipe_validity_id_s.NEXTVAL
1450 FROM FND_DUAL;
1451
1452 CURSOR c_get_formula IS
1453 SELECT creation_date, formula_status, owner_organization_id
1454 FROM fm_form_mst_b
1455 WHERE formula_id = p_formula_id;
1456 LocalDateRecord c_get_formula%ROWTYPE;
1457
1458 CURSOR c_get_formula_item IS
1459 SELECT inventory_item_id, revision, detail_uom, qty
1460 FROM fm_matl_dtl
1461 WHERE formula_id = p_formula_id
1462 AND line_type = 1
1463 ORDER BY line_no;
1464
1465 LocalFormRecord c_get_formula_item%ROWTYPE;
1466
1467 CURSOR c_get_recipe_info(l_orgn_id NUMBER) IS
1468 SELECT *
1469 FROM gmd_recipe_generation
1470 WHERE (organization_id = l_orgn_id
1471 OR organization_id IS NULL)
1472 ORDER BY organization_id;
1473
1474 LocalInfoRecord c_get_recipe_info%ROWTYPE;
1475
1476 CURSOR Cur_get_max_pref (V_item_id NUMBER, V_organization_id NUMBER,
1477 V_start_date DATE, V_end_date DATE, V_recipe_use NUMBER) IS
1478 SELECT MAX(preference)
1479 FROM gmd_recipe_validity_rules
1480 WHERE inventory_item_id = v_item_id
1481 AND organization_id = v_organization_id
1482 AND recipe_use = v_recipe_use
1483 AND NVL(end_date, v_start_date) >= v_start_date
1484 AND start_date <= NVL(v_end_date, start_date)
1485 AND inv_max_qty >= 0
1486 AND inv_min_qty <= 999999
1487 AND validity_rule_status < 800
1488 AND delete_mark = 0;
1489
1490 l_vr_id NUMBER := 0;
1491 l_item_id NUMBER(15,0);
1492 l_revision VARCHAR(3); --Krishna, NPD convergence
1493 l_user_id NUMBER;
1494 l_login_id NUMBER;
1495 l_preference NUMBER;
1496 l_recipe_use NUMBER;
1497
1498 l_orgn_id NUMBER;
1499 l_end_status VARCHAR2(30);
1500 l_detail_uom VARCHAR2(4);
1501
1502 l_start_date DATE;
1503 l_end_date DATE;
1504 x_end_date DATE;
1505
1506 l_default_vr_status GMD_API_GRP.status_rec_type;
1507 BEGIN
1508
1509 OPEN c_get_vr_id;
1510 FETCH c_get_vr_id INTO l_vr_id;
1511 CLOSE c_get_vr_id;
1512
1513 OPEN c_get_formula;
1514 FETCH c_get_formula INTO LocalDateRecord;
1515 l_orgn_id := LocalDateRecord.owner_organization_id;
1516 CLOSE c_get_formula;
1517
1518 OPEN c_get_recipe_info(l_orgn_id);
1519 FETCH c_get_recipe_info INTO LocalInfoRecord;
1520 IF c_get_recipe_info%FOUND THEN
1521 l_user_id := FND_PROFILE.VALUE('USER_ID');
1522 l_login_id := FND_PROFILE.VALUE ('LOGIN_ID');
1523
1524 IF LocalInfoRecord.start_date_type = 0 THEN
1525 OPEN c_get_formula;
1526 FETCH c_get_formula INTO LocalDateRecord;
1527 l_start_date := TRUNC(LocalDateRecord.creation_date);
1528 CLOSE c_get_formula;
1529 ELSE
1530 l_start_date := LocalInfoRecord.start_date;
1531 END IF;
1532
1533 IF LocalInfoRecord.end_date_type = 0 THEN
1534 GMD_RECIPE_GENERATE.calculate_date(l_start_date, LocalInfoRecord.Num_of_days, x_end_date);
1535 l_end_date := x_end_date;
1536 ELSE
1537 l_end_date := LocalInfoRecord.end_date;
1538 END IF;
1539 /*Bug 3735354 - Thomas Daniel */
1540 /*We need to reset the start date to be less than the end date for the setup */
1541 /*cases defined in the bug */
1542 IF l_end_date < l_start_date THEN
1543 l_start_date := TRUNC(l_end_date);
1544 END IF;
1545
1546 OPEN c_get_formula_item;
1547 FETCH c_get_formula_item INTO LocalFormRecord;
1548 l_item_id := LocalFormRecord.inventory_item_id;
1549 l_revision := LocalFormRecord.revision; --Krishna NPD Conv
1550 l_detail_uom := LocalFormRecord.detail_uom;
1551 CLOSE c_get_formula_item;
1552
1553 l_recipe_vr_tbl.recipe_validity_rule_id := l_vr_id;
1554 l_recipe_vr_tbl.recipe_id := NULL;
1555 l_recipe_vr_tbl.recipe_no := NULL;
1556 l_recipe_vr_tbl.recipe_version := NULL;
1557 l_recipe_vr_tbl.user_id := l_user_id;
1558 l_recipe_vr_tbl.organization_id := l_orgn_id;
1559 l_recipe_vr_tbl.inventory_item_id := l_item_id;
1560 l_recipe_vr_tbl.revision := l_revision;--Krishna NPD Conv
1561 l_recipe_vr_tbl.item_no := NULL;
1562 IF p_recipe_use IS NULL THEN
1563 l_recipe_vr_tbl.recipe_use := LocalInfoRecord.recipe_use_prod||
1564 LocalInfoRecord.recipe_use_plan||
1565 LocalInfoRecord.recipe_use_cost||
1566 LocalInfoRecord.recipe_use_reg||
1567 LocalInfoRecord.recipe_use_tech;
1568 ELSE
1569 l_recipe_vr_tbl.recipe_use := p_recipe_use;
1570 END IF;
1571
1572 l_recipe_vr_tbl.preference := l_preference;
1573 l_recipe_vr_tbl.start_date := l_start_date;
1574 l_recipe_vr_tbl.end_date := l_end_date;
1575 l_recipe_vr_tbl.min_qty := 0;
1576 l_recipe_vr_tbl.max_qty := 999999;
1577 l_recipe_vr_tbl.std_qty := LocalFormRecord.qty;
1578 l_recipe_vr_tbl.detail_uom := l_detail_uom;
1579 l_recipe_vr_tbl.inv_min_qty := 0;
1580 l_recipe_vr_tbl.inv_max_qty := 999999;
1581 l_recipe_vr_tbl.created_by := l_user_id;
1582 l_recipe_vr_tbl.creation_date := SYSDATE;
1583 l_recipe_vr_tbl.last_updated_by := l_user_id;
1584 l_recipe_vr_tbl.last_update_date := SYSDATE;
1585 l_recipe_vr_tbl.last_update_login := l_login_id;
1586 l_recipe_vr_tbl.delete_mark := 0;
1587
1588 -- Bug# 4504631 kmotupal
1589 -- Added check for default status while retrieving recipe VR details
1590 get_status_details (V_entity_type => 'VALIDITY',
1591 V_orgn_id => l_orgn_id,
1592 X_entity_status => l_default_vr_status);
1593
1594 l_recipe_vr_tbl.validity_rule_status := l_default_vr_status.entity_status;
1595 l_end_status := l_recipe_vr_tbl.validity_rule_status;
1596
1597 IF LocalInfoRecord.managing_validity_rules = 0 THEN
1598 IF p_recipe_use IS NOT NULL THEN
1599 l_recipe_use := p_recipe_use;
1600 ELSIF LocalInfoRecord.recipe_use_prod = 1 THEN
1601 l_recipe_use := 0;
1602 ELSIF LocalInfoRecord.recipe_use_plan = 1 THEN
1603 l_recipe_use := 1;
1604 ELSIF LocalInfoRecord.recipe_use_cost = 1 THEN
1605 l_recipe_use := 2;
1606 ELSIF LocalInfoRecord.recipe_use_reg = 1 THEN
1607 l_recipe_use := 3;
1608 ELSIF LocalInfoRecord.recipe_use_tech = 1 THEN
1609 l_recipe_use := 4;
1610 END IF;
1611
1612 OPEN Cur_get_max_pref (l_item_id, l_orgn_id,
1613 l_start_date, l_end_date, l_recipe_use);
1614 FETCH Cur_get_max_pref INTO l_preference;
1615 CLOSE Cur_get_max_pref;
1616 l_preference := NVL(l_preference,0) + 1;
1617 ELSE
1618 l_preference := 1;
1619 END IF;
1620 l_recipe_vr_tbl.preference := l_preference;
1621 END IF;
1622 CLOSE c_get_recipe_info;
1623
1624 x_return_status := FND_API.G_RET_STS_SUCCESS;
1625
1626 END retrieve_vr;
1627
1628
1629 /*+========================================================================+
1630 ** Name : retrieve_recipe
1631 ** Notes : This procedure receives as input recipe record and
1632 ** creates recipe records. |
1633 **
1634 ** If everything is fine then OUT parameter
1635 ** x_return_status is set to 'S' else appropriate
1636 ** error message is put on the stack and error
1637 ** is returned.
1638 **
1639 ** HISTORY
1640 ** 05-Apr-2004 B3604554 GK Created.
1641 ** 21-May-2004 B3642937 GK Assigned the default_status to the recipe_status
1642 ** 24-May-2004 B3643405 GK Added the statement orgn IS NULL to cursor c_get_recipe_info
1643 ** 25-May-2004 B3645706 GK Removed the statement orgn IS NULL to cursor c_get_recipe_info
1644 ** and created a new cursor c_get_global_info to accomodate for global and
1645 ** so that the orgn code will pick up the correct configuration information
1646 ** 25-May-2004 B3653935 GK Changed variables login_id, user_id to be assigned to FND_PROFILE.VALUE
1647 ** 01-dec-2004 kkillams orgn_code is replaced with organization_id/owner_organization_id w.r.t. 4004501
1648 ** 18-APR-2006 kmotupal Added check for default status while retrieving recipe details
1649 ** 30-MAY-2006 Kalyani Bug 5218106 Added code to default recipe_type from orgn parameters.
1650 ** 03-Jan-07 Kapil M LCF-GMO ME : Bug#5458666. Added routing_id to retrieve_recipe
1651 **+========================================================================+*/
1652
1653
1654 PROCEDURE retrieve_recipe(p_formula_id IN NUMBER,
1655 p_routing_id IN NUMBER DEFAULT NULL,
1656 l_recipe_tbl OUT NOCOPY GMD_RECIPE_HEADER.recipe_hdr,
1657 l_recipe_flex OUT NOCOPY GMD_RECIPE_HEADER.flex,
1658 x_return_status OUT NOCOPY VARCHAR2) IS
1659
1660 -- Cursors
1661 CURSOR c_get_formula_info IS
1662 SELECT formula_no, formula_vers, formula_desc1,
1663 owner_organization_id, formula_status, owner_id
1664 FROM fm_form_mst_b
1665 WHERE formula_id = p_formula_id;
1666 LocalFormRecord c_get_formula_info%ROWTYPE;
1667
1668 -- Kapil LCF-GMO ME : Bug#5458666
1669 CURSOR c_get_routing_info IS
1670 SELECT routing_no , routing_vers
1671 FROM GMD_ROUTINGS_B
1672 WHERE routing_id = p_routing_id;
1673 LocalRoutRecord c_get_routing_info%ROWTYPE;
1674
1675 CURSOR c_get_recipe_id IS
1676 SELECT gmd_recipe_id_s.NEXTVAL
1677 FROM FND_DUAL;
1678
1679 CURSOR c_get_item_id IS
1680 SELECT inventory_item_id
1681 FROM fm_matl_dtl
1682 WHERE formula_id = p_formula_id
1683 AND line_type = 1;
1684
1685 CURSOR c_get_recipe_info(l_orgn_id NUMBER) IS
1686 SELECT recipe_naming_convention, created_by, last_update_login
1687 FROM gmd_recipe_generation
1688 WHERE (organization_id = l_orgn_id OR
1689 organization_id IS NULL)
1690 ORDER BY organization_id;
1691 LocalInfoRecord c_get_recipe_info%ROWTYPE;
1692
1693 CURSOR c_get_version(l_recipe_no VARCHAR2) IS
1694 SELECT max(recipe_version)
1695 FROM gmd_recipes_b
1696 WHERE recipe_no = l_recipe_no;
1697 LocalVersRecord c_get_version%ROWTYPE;
1698
1699 CURSOR c_check_recipe(l_recipe_no VARCHAR2) IS
1700 SELECT *
1701 FROM gmd_recipes_b
1702 WHERE recipe_no = l_recipe_no;
1703 LocalCheckRecord c_check_recipe%ROWTYPE;
1704
1705 CURSOR c_get_item(l_item_id NUMBER) IS
1706 SELECT description item_desc1, concatenated_segments item_no
1707 FROM mtl_system_items_kfv
1708 WHERE inventory_item_id = l_item_id;
1709 LocalItemRecord c_get_item%ROWTYPE;
1710
1711 -- Local Variables
1712 l_recipe_id NUMBER(15);
1713 l_recipe_version NUMBER(5) := 1;
1714 l_delete_mark NUMBER(5);
1715 l_recipe_name NUMBER(5);
1716 i BINARY_INTEGER := 2;
1717 l_user_id FND_USER.user_id%TYPE; --NUMBER;
1718 l_login_id NUMBER;
1719 l_recipe_no VARCHAR2(32);
1720 l_orgn_ID NUMBER;
1721 l_item_id NUMBER;
1722 l_recipe_description VARCHAR2(70);
1723 l_default_recipe_status gmd_api_grp.status_rec_type;
1724
1725 l_routing_no VARCHAR2(32) := NULL;
1726 l_routing_vers NUMBER := NULL;
1727 -- Exceptions
1728 create_recipe_err EXCEPTION;
1729
1730 BEGIN
1731
1732 x_return_status := FND_API.G_RET_STS_SUCCESS;
1733
1734 OPEN c_get_recipe_id;
1735 FETCH c_get_recipe_id INTO l_recipe_id;
1736 CLOSE c_get_recipe_id;
1737
1738 OPEN c_get_formula_info;
1739 FETCH c_get_formula_info INTO LocalFormRecord;
1740 IF c_get_formula_info%FOUND THEN
1741 l_orgn_id := LocalFormRecord.owner_organization_id;
1742
1743 OPEN c_get_recipe_info(l_orgn_id);
1744 FETCH c_get_recipe_info INTO LocalInfoRecord;
1745 IF c_get_recipe_info%FOUND THEN
1746 l_recipe_name := LocalInfoRecord.recipe_naming_convention;
1747 l_user_id := FND_PROFILE.VALUE('USER_ID');
1748 l_login_id := FND_PROFILE.VALUE ('LOGIN_ID');
1749 END IF;
1750 CLOSE c_get_recipe_info;
1751
1752 IF l_recipe_name = 0 THEN
1753 OPEN c_get_item_id;
1754 FETCH c_get_item_id INTO l_item_id;
1755 CLOSE c_get_item_id;
1756
1757 OPEN c_get_item(l_item_id);
1758 FETCH c_get_item INTO LocalItemRecord;
1759 IF c_get_item%FOUND THEN
1760 l_recipe_no := LocalItemRecord.item_no;
1761 l_recipe_description := LocalItemRecord.item_desc1;
1762 OPEN c_check_recipe(l_recipe_no);
1763 FETCH c_check_recipe INTO LocalCheckRecord;
1764 IF c_check_recipe%FOUND THEN
1765 OPEN c_get_version(l_recipe_no);
1766 FETCH c_get_version INTO l_recipe_version;
1767 l_recipe_version := l_recipe_version + 1;
1768 CLOSE c_get_version;
1769 ELSIF (LocalFormRecord.formula_vers = 0) THEN
1770 -- If formula vers is 0, start recipe vers with 0
1771 l_recipe_version := 0;
1772 ELSE
1773 -- Else start recipe vers with 1
1774 l_recipe_version := 1;
1775 END IF;
1776 CLOSE c_check_recipe;
1777 END IF;
1778 CLOSE c_get_item;
1779 ELSE
1780 l_recipe_no := LocalFormRecord.formula_no;
1781 OPEN c_check_recipe(l_recipe_no);
1782 FETCH c_check_recipe INTO LocalCheckRecord;
1783 IF c_check_recipe%FOUND THEN
1784 OPEN c_get_version(l_recipe_no);
1785 FETCH c_get_version INTO l_recipe_version;
1786 l_recipe_version := l_recipe_version + 1;
1787 CLOSE c_get_version;
1788 ELSIF (LocalFormRecord.formula_vers = 0) THEN
1789 -- If formula vers is 0, start recipe vers with 0
1790 l_recipe_version := 0;
1791 ELSE
1792 -- Else start recipe vers with 1
1793 l_recipe_version := 1;
1794 END IF;
1795 l_recipe_description := LocalFormRecord.formula_desc1;
1796 END IF;
1797
1798 l_recipe_tbl.recipe_id := l_recipe_id;
1799
1800 IF l_recipe_description IS NULL THEN
1801 l_recipe_description := l_recipe_no;
1802 END IF;
1803 -- Kapil LCF-GMO ME : Bug#5458666
1804 -- To get Routing details
1805 IF p_routing_id IS NOT NULL THEN
1806 OPEN c_get_routing_info;
1807 FETCH c_get_routing_info INTO LocalRoutRecord;
1808 l_routing_no := LocalRoutRecord.routing_no;
1809 l_routing_vers := LocalRoutRecord.routing_vers;
1810 CLOSE c_get_routing_info;
1811 END IF;
1812
1813 -- Bug# 4504631 kmotupal
1814 -- Added check for default status while retrieving recipe details
1815 get_status_details (V_entity_type => 'RECIPE',
1816 V_orgn_id => l_orgn_id,
1817 X_entity_status => l_default_recipe_status);
1818
1819 l_recipe_tbl.recipe_description := l_recipe_description;
1820 l_recipe_tbl.recipe_no := l_recipe_no;
1821 l_recipe_tbl.recipe_version := l_recipe_version;
1822 l_recipe_tbl.user_id := l_user_id;
1823 l_recipe_tbl.user_name := NULL;
1824 l_recipe_tbl.owner_organization_id := l_orgn_id;
1825 l_recipe_tbl.creation_organization_id := l_orgn_id;
1826 l_recipe_tbl.formula_id := p_formula_id;
1827 l_recipe_tbl.formula_no := LocalFormRecord.formula_no;
1828 l_recipe_tbl.formula_vers := LocalFormRecord.formula_vers;
1829 -- Kapil LCF-GMO ME : Bug#5458666 , Pass the routing details.
1830 l_recipe_tbl.routing_id := p_routing_id;
1831 l_recipe_tbl.routing_no := l_routing_no;
1832 l_recipe_tbl.routing_vers := l_routing_vers;
1833 l_recipe_tbl.project_id := NULL;
1834 l_recipe_tbl.recipe_status := l_default_recipe_status.entity_status;
1835 l_recipe_tbl.planned_process_loss := NULL;
1836 l_recipe_tbl.text_code := NULL;
1837 l_recipe_tbl.delete_mark := 0;
1838 l_recipe_tbl.creation_date := sysdate;
1839 l_recipe_tbl.created_by := l_user_id;
1840 l_recipe_tbl.last_updated_by := l_user_id;
1841 l_recipe_tbl.last_update_date := sysdate;
1842 l_recipe_tbl.last_update_login := l_login_id;
1843 l_recipe_tbl.owner_id := LocalFormRecord.owner_id;
1844 l_recipe_tbl.owner_organization_id := LocalFormRecord.owner_organization_id;
1845 l_recipe_tbl.owner_lab_type := NULL;
1846 l_recipe_tbl.calculate_step_quantity := 0;
1847 -- Bug 5218106
1848 l_recipe_tbl.recipe_type:=get_recipe_type(l_orgn_id);
1849
1850
1851 END IF; --If c_get_formula_info%found
1852
1853 x_return_status := FND_API.G_RET_STS_SUCCESS;
1854 CLOSE c_get_formula_info;
1855
1856 END retrieve_recipe;
1857
1858 /*+========================================================================+
1859 ** Name : check_orgn_status
1860 ** Created 18-OCT-2004
1861 ** Description
1862 ** Function to check the organization passed is process enabled.
1863 **+========================================================================+*/
1864
1865 FUNCTION check_orgn_status (V_organization_id IN NUMBER) RETURN BOOLEAN IS
1866 CURSOR Check_orgn_exists IS
1867 SELECT 1
1868 FROM mtl_parameters
1869 WHERE organization_id = V_organization_id
1870 AND process_enabled_flag = 'Y';
1871
1872 l_temp NUMBER;
1873 BEGIN
1874 IF (V_organization_id IS NOT NULL) THEN
1875 --Check the organization id passed is process enabled if not raise an error message
1876 OPEN check_orgn_exists;
1877 FETCH check_orgn_exists INTO l_temp;
1878 IF (check_orgn_exists%NOTFOUND) THEN
1879 CLOSE check_orgn_exists;
1880 RETURN FALSE;
1881 END IF;
1882 CLOSE check_orgn_exists;
1883 END IF;
1884 RETURN TRUE;
1885 END check_orgn_status;
1886
1887 /*###############################################################
1888 # NAME
1889 # check_item_exists
1890 # SYNOPSIS
1891 # proc check_item_exists
1892 # DESCRIPTION
1893 # Procedure used to check if the organization has access to the items
1894 # Kalyani 23-Jun-2006 B5350197 Serial control items not allowed.
1895 # Raju 29-Jan-2008 B6772070 Added AND SERIAL_NUMBER_CONTROL_CODE in (1,6) in where clause.
1896 # TO allow serial control items at sales order issue.
1897 ###############################################################*/
1898
1899 PROCEDURE check_item_exists (p_formula_id IN NUMBER,
1900 x_return_status OUT NOCOPY VARCHAR2,
1901 p_organization_id IN NUMBER DEFAULT NULL,
1902 p_orgn_code IN VARCHAR2 DEFAULT NULL,
1903 p_production_check IN BOOLEAN DEFAULT FALSE,
1904 p_costing_check IN BOOLEAN DEFAULT FALSE) IS
1905 X_ret NUMBER;
1906 X_item_no VARCHAR2(2000);
1907 X_organization_id NUMBER;
1908 X_orgn_code VARCHAR2(3);
1909 X_item_list VARCHAR2(2000);
1910 X_item_revision VARCHAR2(2000);
1911 X_item_rev_list VARCHAR2(2000);
1912
1913 CURSOR Cur_check_item (V_organization_id NUMBER) IS
1914 SELECT inventory_item_id
1915 FROM fm_matl_dtl d
1916 WHERE formula_id = p_formula_id
1917 AND NOT EXISTS (SELECT 1
1918 FROM mtl_system_items_b
1919 WHERE inventory_item_id = d.inventory_item_id
1920 AND organization_id = V_organization_id
1921 AND recipe_enabled_flag = 'Y');
1922 CURSOR Cur_item IS
1923 SELECT inventory_item_id
1924 FROM fm_matl_dtl
1925 WHERE formula_id = p_formula_id;
1926
1927 CURSOR Cur_check_item_revision (V_organization_id NUMBER) IS
1928 SELECT d.revision, b.concatenated_segments
1929 FROM fm_matl_dtl d, mtl_system_items_kfv b
1930 WHERE formula_id = p_formula_id
1931 AND b.inventory_item_id = d.inventory_item_id
1932 AND b.organization_id = V_organization_id
1933 AND revision IS NOT NULL
1934 AND NOT EXISTS (SELECT 1
1935 FROM mtl_item_revisions
1936 WHERE inventory_item_id = d.inventory_item_id
1937 AND organization_id = V_organization_id
1938 AND revision = d.revision);
1939
1940 CURSOR Cur_check_item_prod_enabled (V_organization_id NUMBER) IS
1941 SELECT inventory_item_id
1942 FROM fm_matl_dtl d
1943 WHERE formula_id = p_formula_id
1944 AND NOT EXISTS (SELECT 1
1945 FROM mtl_system_items_b
1946 WHERE inventory_item_id = d.inventory_item_id
1947 AND organization_id = V_organization_id
1948 AND process_execution_enabled_flag = 'Y');
1949
1950 CURSOR Cur_check_item_cost_enabled (V_organization_id NUMBER) IS
1951 SELECT inventory_item_id
1952 FROM fm_matl_dtl d
1953 WHERE formula_id = p_formula_id
1954 AND NOT EXISTS (SELECT 1
1955 FROM mtl_system_items_b
1956 WHERE inventory_item_id = d.inventory_item_id
1957 AND organization_id = V_organization_id
1958 AND process_costing_enabled_flag = 'Y');
1959
1960 -- Bug 5350197
1961 CURSOR Cur_check_item_serial_enabled (V_organization_id NUMBER) IS
1962 SELECT inventory_item_id
1963 FROM fm_matl_dtl d
1964 WHERE formula_id = p_formula_id
1965 AND NOT EXISTS (SELECT 1
1966 FROM mtl_system_items_b
1967 WHERE inventory_item_id = d.inventory_item_id
1968 AND organization_id = V_organization_id
1969 AND serial_number_control_code IN (1,6));
1970
1971
1972 CURSOR Cur_get_item_no (V_inventory_item_id NUMBER) IS
1973 SELECT concatenated_segments
1974 FROM mtl_system_items_kfv
1975 WHERE inventory_item_id = V_inventory_item_id;
1976
1977 CURSOR Cur_get_org_id (V_org_code VARCHAR2) IS
1978 SELECT organization_id
1979 FROM mtl_parameters
1980 WHERE organization_code = V_org_code;
1981
1982 CURSOR Cur_get_org_code (V_org_id NUMBER) IS
1983 SELECT organization_code
1984 FROM mtl_parameters
1985 WHERE organization_id = V_org_id;
1986
1987 ORGN_MISSING EXCEPTION;
1988 FORMULA_MISSING EXCEPTION;
1989 ITEM_MISSING EXCEPTION;
1990
1991 BEGIN
1992 /* Initialize the return status */
1993 X_return_status := FND_API.g_ret_sts_success;
1994 IF p_formula_id IS NULL THEN
1995 RAISE formula_missing;
1996 END IF;
1997
1998 IF p_organization_id IS NULL THEN
1999 IF p_orgn_code IS NULL THEN
2000 RAISE orgn_missing;
2001 END IF;
2002
2003 OPEN Cur_get_org_id (P_orgn_code);
2004 FETCH Cur_get_org_id INTO X_organization_id;
2005 CLOSE Cur_get_org_id;
2006 ELSE
2007 X_organization_id := p_organization_id;
2008 END IF;
2009
2010 X_item_list := NULL;
2011 X_item_rev_list := NULL;
2012 OPEN Cur_check_item (X_organization_id);
2013 LOOP
2014 FETCH Cur_check_item INTO X_ret;
2015 EXIT WHEN Cur_check_item%NOTFOUND;
2016
2017 OPEN Cur_get_item_no (X_ret);
2018 FETCH Cur_get_item_no INTO X_item_no;
2019 CLOSE Cur_get_item_no;
2020
2021 IF X_item_list IS NULL THEN
2022 X_item_list := X_item_list||X_item_no;
2023 ELSE
2024 X_item_list := X_item_list||','||X_item_no;
2025 END IF;
2026 END LOOP;
2027 CLOSE Cur_check_item;
2028
2029 IF X_item_list IS NOT NULL THEN
2030 IF p_orgn_code IS NULL THEN
2031 OPEN Cur_get_org_code (X_organization_id);
2032 FETCH Cur_get_org_code INTO X_orgn_code;
2033 CLOSE Cur_get_org_code;
2034 ELSE
2035 X_orgn_code := p_orgn_code;
2036 END IF;
2037
2038 FND_MESSAGE.SET_NAME('GMD', 'GMD_RCP_ITEMORG_NOT_FOUND');
2039 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2040 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2041 FND_MSG_PUB.ADD;
2042 X_return_status := FND_API.g_ret_sts_error;
2043 RAISE ITEM_MISSING;
2044 END IF;
2045
2046 OPEN Cur_check_item_revision (X_organization_id);
2047 LOOP
2048 FETCH Cur_check_item_revision INTO X_item_revision, X_item_no;
2049 EXIT WHEN Cur_check_item_revision%NOTFOUND;
2050
2051 IF X_item_rev_list IS NULL THEN
2052 X_item_rev_list := X_item_rev_list||X_item_no||','||X_item_revision;
2053 ELSE
2054 X_item_rev_list := X_item_rev_list||','||X_item_no||','||X_item_revision;
2055 END IF;
2056 END LOOP;
2057 CLOSE Cur_check_item_revision;
2058
2059 IF X_item_rev_list IS NOT NULL THEN
2060 IF p_orgn_code IS NULL THEN
2061 OPEN Cur_get_org_code (X_organization_id);
2062 FETCH Cur_get_org_code INTO X_orgn_code;
2063 CLOSE Cur_get_org_code;
2064 ELSE
2065 X_orgn_code := p_orgn_code;
2066 END IF;
2067
2068 FND_MESSAGE.SET_NAME('GMD', 'GMD_RCP_ITEMORG_REV_NOT_FOUND');
2069 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2070 FND_MESSAGE.SET_TOKEN('ITEM REVISION',X_item_rev_list);
2071 FND_MSG_PUB.ADD;
2072 X_return_status := FND_API.g_ret_sts_error;
2073 RAISE ITEM_MISSING;
2074 END IF;
2075
2076 IF p_production_check THEN
2077 OPEN Cur_check_item_prod_enabled (X_organization_id);
2078 LOOP
2079 FETCH Cur_check_item_prod_enabled INTO X_ret;
2080 EXIT WHEN Cur_check_item_prod_enabled%NOTFOUND;
2081
2082 OPEN Cur_get_item_no (X_ret);
2083 FETCH Cur_get_item_no INTO X_item_no;
2084 CLOSE Cur_get_item_no;
2085
2086 IF X_item_list IS NULL THEN
2087 X_item_list := X_item_list||X_item_no;
2088 ELSE
2089 X_item_list := X_item_list||','||X_item_no;
2090 END IF;
2091 END LOOP;
2092 CLOSE Cur_check_item_prod_enabled;
2093
2094 IF X_item_list IS NOT NULL THEN
2095 IF p_orgn_code IS NULL THEN
2096 OPEN Cur_get_org_code (X_organization_id);
2097 FETCH Cur_get_org_code INTO X_orgn_code;
2098 CLOSE Cur_get_org_code;
2099 ELSE
2100 X_orgn_code := p_orgn_code;
2101 END IF;
2102
2103 FND_MESSAGE.SET_NAME('GMD', 'GMD_PROD_ITEMORG_NOT_FOUND');
2104 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2105 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2106 FND_MSG_PUB.ADD;
2107 X_return_status := FND_API.g_ret_sts_error;
2108 RAISE ITEM_MISSING;
2109 END IF;
2110 END IF;
2111 IF p_costing_check THEN
2112 OPEN Cur_check_item_cost_enabled (X_organization_id);
2113 LOOP
2114 FETCH Cur_check_item_cost_enabled INTO X_ret;
2115 EXIT WHEN Cur_check_item_cost_enabled%NOTFOUND;
2116
2117 OPEN Cur_get_item_no (X_ret);
2118 FETCH Cur_get_item_no INTO X_item_no;
2119 CLOSE Cur_get_item_no;
2120
2121 IF X_item_list IS NULL THEN
2122 X_item_list := X_item_list||X_item_no;
2123 ELSE
2124 X_item_list := X_item_list||','||X_item_no;
2125 END IF;
2126 END LOOP;
2127 CLOSE Cur_check_item_cost_enabled;
2128
2129 IF X_item_list IS NOT NULL THEN
2130 IF p_orgn_code IS NULL THEN
2131 OPEN Cur_get_org_code (X_organization_id);
2132 FETCH Cur_get_org_code INTO X_orgn_code;
2133 CLOSE Cur_get_org_code;
2134 ELSE
2135 X_orgn_code := p_orgn_code;
2136 END IF;
2137
2138 FND_MESSAGE.SET_NAME('GMD', 'GMD_COST_ITEMORG_NOT_FOUND');
2139 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2140 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2141 FND_MSG_PUB.ADD;
2142 X_return_status := FND_API.g_ret_sts_error;
2143 RAISE ITEM_MISSING;
2144 END IF;
2145 END IF;
2146
2147 --Bug 5350197
2148 OPEN Cur_check_item_serial_enabled (X_organization_id);
2149 LOOP
2150 FETCH Cur_check_item_serial_enabled INTO X_ret;
2151 EXIT WHEN Cur_check_item_serial_enabled%NOTFOUND;
2152
2153 OPEN Cur_get_item_no (X_ret);
2154 FETCH Cur_get_item_no INTO X_item_no;
2155 CLOSE Cur_get_item_no;
2156
2157 IF X_item_list IS NULL THEN
2158 X_item_list := X_item_list||X_item_no;
2159 ELSE
2160 X_item_list := X_item_list||','||X_item_no;
2161 END IF;
2162 END LOOP;
2163 CLOSE Cur_check_item_serial_enabled;
2164
2165 IF X_item_list IS NOT NULL THEN
2166 IF p_orgn_code IS NULL THEN
2167 OPEN Cur_get_org_code (X_organization_id);
2168 FETCH Cur_get_org_code INTO X_orgn_code;
2169 CLOSE Cur_get_org_code;
2170 ELSE
2171 X_orgn_code := p_orgn_code;
2172 END IF;
2173
2174 FND_MESSAGE.SET_NAME('GMD', 'GMD_SERIAL_ITEMS_NOT_ALLOWED');
2175 FND_MESSAGE.SET_TOKEN('ORGN',X_orgn_code);
2176 FND_MESSAGE.SET_TOKEN('ITEM',X_item_list);
2177 FND_MSG_PUB.ADD;
2178 X_return_status := FND_API.g_ret_sts_error;
2179 RAISE ITEM_MISSING;
2180 END IF;
2181
2182 EXCEPTION
2183 WHEN formula_missing THEN
2184 X_return_status := FND_API.g_ret_sts_error;
2185 gmd_api_grp.log_message('GMD_MISSING', 'FORMULA_ID');
2186 WHEN item_missing THEN
2187 X_return_status := FND_API.g_ret_sts_error;
2188 WHEN orgn_missing THEN
2189 X_return_status := FND_API.g_ret_sts_error;
2190 gmd_api_grp.log_message('GMD_MISSING', 'ORGN_CODE');
2191 WHEN OTHERS THEN
2192 X_return_status := FND_API.g_ret_sts_unexp_error;
2193 fnd_msg_pub.add_exc_msg (l_package_name, 'CHECK_ITEM_EXISTS');
2194 END check_item_exists;
2195
2196 /* *********************************************************************** *
2197 * Function *
2198 * Validate_um *
2199 * Parameter : item_uom_code IN varchar2 *
2200 * Description *
2201 * Checks if the uom_code passed is valid - Return True if it exists *
2202 * *********************************************************************** */
2203 FUNCTION Validate_um(pItem_uom_code IN VARCHAR2) RETURN BOOLEAN IS
2204 Cursor Item_um_cur IS
2205 Select 1 from dual
2206 Where exists (Select 1 from mtl_units_of_measure
2207 Where uom_code = pItem_uom_code);
2208 l_dummy_cnt NUMBER;
2209 BEGIN
2210 OPEN Item_um_cur;
2211 FETCH Item_um_cur into l_dummy_cnt;
2212 CLOSE Item_um_cur;
2213 Return (l_dummy_cnt IS NOT NULL);
2214 END Validate_um;
2215
2216
2217 /*======================================================================
2218 -- PROCEDURE :
2219 -- FETCH_PARM_VALUES
2220 --
2221 -- DESCRIPTION:
2222 -- This procedure is used to fetch the parameter values for a
2223 -- particular orgn_id. If orgn_id is NULL return the Global orgn. parameters
2224 --
2225 -- HISTORY
2226 -- Sriram.S 05-NOV-2004 Created
2227 --===================================================================== */
2228
2229 PROCEDURE FETCH_PARM_VALUES (P_orgn_id IN NUMBER,
2230 X_out_rec OUT NOCOPY GMD_PARAMETERS_DTL_PKG.parameter_rec_type,
2231 X_return_status OUT NOCOPY VARCHAR2) IS
2232
2233 CURSOR Cur_get_parameters IS
2234 SELECT parameter_name, parameter_value, parameter_type
2235 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2236 WHERE h.parameter_id = d.parameter_id
2237 AND h.organization_id = P_orgn_id
2238 UNION
2239 SELECT parameter_name, parameter_value, parameter_type
2240 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2241 WHERE h.parameter_id = d.parameter_id
2242 AND h.organization_id IS NULL
2243 AND NOT EXISTS (SELECT 1
2244 FROM gmd_parameters_hdr h1, gmd_parameters_dtl d1
2245 WHERE h1.parameter_id = d1.parameter_id
2246 AND h1.organization_id = P_orgn_id
2247 AND d1.parameter_name = d.parameter_name);
2248
2249
2250 CURSOR Cur_get_lab_plant_ind IS
2251 SELECT plant_ind, lab_ind
2252 FROM gmd_parameters_hdr
2253 WHERE organization_id = P_orgn_id;
2254
2255 l_Cur_get_parameters_fetch BOOLEAN := FALSE;
2256
2257 PARM_NOT_FOUND EXCEPTION;
2258
2259 BEGIN
2260
2261 /* Set return status to success initially */
2262 x_return_status := FND_API.G_RET_STS_SUCCESS;
2263
2264 OPEN Cur_get_lab_plant_ind;
2265 FETCH Cur_get_lab_plant_ind INTO x_out_rec.plant_ind, x_out_rec.lab_ind;
2266 IF P_orgn_id IS NOT NULL AND Cur_get_lab_plant_ind%NOTFOUND THEN
2267 -- If orgn id is NOT NULL and cursor fetched no record, raise exception
2268 RAISE PARM_NOT_FOUND;
2269 END IF;
2270 CLOSE Cur_get_lab_plant_ind;
2271
2272 FOR l_rec IN Cur_get_parameters LOOP
2273 l_Cur_get_parameters_fetch := TRUE;
2274
2275 IF l_rec.parameter_type = 1 THEN
2276 IF l_rec.parameter_name = 'GMD_FORMULA_VERSION_CONTROL' THEN
2277 x_out_rec.gmd_formula_version_control := l_rec.parameter_value;
2278 ELSIF l_rec.parameter_name = 'GMD_BYPRODUCT_ACTIVE' THEN
2279 x_out_rec.gmd_byproduct_active := l_rec.parameter_value;
2280 ELSIF l_rec.parameter_name = 'GMD_ZERO_INGREDIENT_QTY' THEN
2281 x_out_rec.gmd_zero_ingredient_qty := l_rec.parameter_value;
2282 ELSIF l_rec.parameter_name = 'GMD_MASS_UM_TYPE' THEN
2283 x_out_rec.gmd_mass_um_type := l_rec.parameter_value;
2284 ELSIF l_rec.parameter_name = 'GMD_VOLUME_UM_TYPE' THEN
2285 x_out_rec.gmd_volume_um_type := l_rec.parameter_value;
2286 ELSIF l_rec.parameter_name = 'FM_YIELD_TYPE' THEN
2287 x_out_rec.fm_yield_type := l_rec.parameter_value;
2288 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_FORM_STATUS' THEN
2289 x_out_rec.gmd_default_form_status := l_rec.parameter_value;
2290 ELSIF l_rec.parameter_name = 'GMI_LOTGENE_ENABLE_FMSEC' THEN
2291 x_out_rec.gmi_lotgene_enable_fmsec := l_rec.parameter_value;
2292 ELSIF l_rec.parameter_name = 'FM$DEFAULT_RELEASE_TYPE' THEN
2293 x_out_rec.fm$default_release_type := l_rec.parameter_value;
2294 END IF;
2295 ELSIF l_rec.parameter_type = 2 THEN
2296 IF l_rec.parameter_name = 'GMD_OPERATION_VERSION_CONTROL' THEN
2297 x_out_rec.gmd_operation_version_control := l_rec.parameter_value;
2298 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_OPRN_STATUS' THEN
2299 x_out_rec.gmd_default_oprn_status := l_rec.parameter_value;
2300 END IF;
2301 ELSIF l_rec.parameter_type = 3 THEN
2302 IF l_rec.parameter_name = 'GMD_ROUTING_VERSION_CONTROL' THEN
2303 x_out_rec.gmd_routing_version_control := l_rec.parameter_value;
2304 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_ROUT_STATUS' THEN
2305 x_out_rec.gmd_default_rout_status := l_rec.parameter_value;
2306 ELSIF l_rec.parameter_name = 'STEPRELEASE_TYPE' THEN
2307 x_out_rec.steprelease_type := l_rec.parameter_value;
2308 ELSIF l_rec.parameter_name = 'GMD_ENFORCE_STEP_DEPENDENCY' THEN
2309 x_out_rec.gmd_enforce_step_dependency := l_rec.parameter_value;
2310 END IF;
2311 ELSIF l_rec.parameter_type = 4 THEN
2312 IF l_rec.parameter_name = 'GMD_RECIPE_VERSION_CONTROL' THEN
2313 x_out_rec.gmd_recipe_version_control := l_rec.parameter_value;
2314 ELSIF l_rec.parameter_name = 'GMD_PROC_INSTR_PARAGRAPH' THEN
2315 x_out_rec.gmd_proc_instr_paragraph := l_rec.parameter_value;
2316 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_RECP_STATUS' THEN
2317 x_out_rec.gmd_default_recp_status := l_rec.parameter_value;
2318 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_VALR_STATUS' THEN
2319 x_out_rec.gmd_default_valr_status := l_rec.parameter_value;
2320 ELSIF l_rec.parameter_name = 'GMD_RECIPE_TYPE' THEN
2321 x_out_rec.gmd_recipe_type := l_rec.parameter_value;
2322 END IF;
2323 ELSIF l_rec.parameter_type = 6 THEN
2324 IF l_rec.parameter_name = 'GMD_COST_SOURCE_ORGN' THEN
2325 x_out_rec.gmd_cost_source_orgn := l_rec.parameter_value;
2326 ELSIF l_rec.parameter_name = 'GMD_DEFAULT_SPEC_STATUS' THEN
2327 x_out_rec.gmd_default_spec_status := l_rec.parameter_value;
2328 END IF;
2329 END IF;
2330 END LOOP;
2331
2332 IF NOT l_Cur_get_parameters_fetch THEN
2333 -- If Flag is not set, raise exception.
2334 RAISE PARM_NOT_FOUND;
2335 END IF;
2336
2337 EXCEPTION
2338 WHEN PARM_NOT_FOUND THEN
2339 fnd_message.set_name ('GMD', 'GMD_PARM_NOT_FOUND');
2340 fnd_msg_pub.add;
2341 x_return_status := FND_API.G_RET_STS_SUCCESS;
2342 WHEN OTHERS THEN
2343 fnd_message.set_name ('GMD', 'GMD_PARM_NOT_FOUND');
2344 fnd_msg_pub.add;
2345 x_return_status := FND_API.G_RET_STS_ERROR;
2346
2347 END FETCH_PARM_VALUES;
2348
2349
2350 /*======================================================================
2351 -- PROCEDURE :
2352 -- FETCH_PARM_VALUES
2353 --
2354 -- DESCRIPTION:
2355 -- This procedure is used to fetch the parameter value of the profile name passed for a
2356 -- particular orgn_id. If orgn_id is NULL return the parameter value for Global orgn.
2357 --
2358 -- HISTORY
2359 -- Sriram.S 23-NOV-2004 Created
2360 --===================================================================== */
2361
2362 PROCEDURE FETCH_PARM_VALUES (P_orgn_id IN NUMBER,
2363 P_parm_name IN VARCHAR2,
2364 P_parm_value OUT NOCOPY VARCHAR2,
2365 X_return_status OUT NOCOPY VARCHAR2) IS
2366
2367 CURSOR Cur_get_parameter IS
2368 SELECT parameter_value
2369 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2370 WHERE h.parameter_id = d.parameter_id
2371 AND h.organization_id = P_orgn_id
2372 AND d.parameter_name = P_parm_name
2373 UNION
2374 SELECT parameter_value
2375 FROM gmd_parameters_hdr h, gmd_parameters_dtl d
2376 WHERE h.parameter_id = d.parameter_id
2377 AND h.organization_id IS NULL
2378 AND d.parameter_name = P_parm_name
2379 AND NOT EXISTS (SELECT 1
2380 FROM gmd_parameters_hdr h1, gmd_parameters_dtl d1
2381 WHERE h1.parameter_id = d1.parameter_id
2382 AND h1.organization_id = P_orgn_id
2383 AND d1.parameter_name = d.parameter_name);
2384
2385 PARM_NOT_FOUND EXCEPTION;
2386
2387 BEGIN
2388
2389 /* Set return status to success initially */
2390 x_return_status := FND_API.G_RET_STS_SUCCESS;
2391
2392 OPEN Cur_get_parameter;
2393 FETCH Cur_get_parameter INTO P_parm_value;
2394 IF Cur_get_parameter%NOTFOUND THEN
2395 RAISE PARM_NOT_FOUND;
2396 END IF;
2397 CLOSE Cur_get_parameter;
2398
2399 EXCEPTION
2400 WHEN PARM_NOT_FOUND THEN
2401 x_return_status := FND_API.G_RET_STS_SUCCESS;
2402 WHEN OTHERS THEN
2403 fnd_msg_pub.add_exc_msg (l_package_name, 'FETCH_PARM_VALUES');
2404 x_return_status := FND_API.G_RET_STS_ERROR;
2405 END FETCH_PARM_VALUES;
2406
2407 -- Bug number 4479101
2408 FUNCTION derive_ingredent_end (P_substitution_id IN NUMBER,
2409 p_item_id IN NUMBER,
2410 p_exclude_context IN VARCHAR2) RETURN DATE IS
2411
2412 l_count NUMBER;
2413 l_ingredent_end_date DATE;
2414 l_item_id NUMBER;
2415
2416 CURSOR cur_item_id IS
2417 SELECT original_inventory_item_id FROM GMD_ITEM_SUBSTITUTION_HDR_B
2418 WHERE substitution_id = p_substitution_id;
2419 CURSOR cur_count IS
2420 SELECT count(1) FROM GMD_ITEM_SUBSTITUTION_HDR_B
2421 WHERE original_inventory_item_id = l_item_id
2422 AND ( (p_exclude_context = 'Y' AND substitution_id <> p_substitution_id)
2423 OR p_exclude_context = 'N'
2424 );
2425 CURSOR cur_s_sub_date IS
2426 SELECT start_date FROM GMD_ITEM_SUBSTITUTION_HDR_B
2427 WHERE original_inventory_item_id = l_item_id
2428 AND ( (p_exclude_context = 'Y' AND substitution_id <> p_substitution_id)
2429 OR p_exclude_context = 'N'
2430 );
2431 CURSOR cur_m_sub_date IS
2432 SELECT start_date FROM GMD_ITEM_SUBSTITUTION_HDR_B
2433 WHERE original_inventory_item_id = l_item_id
2434 AND ( (p_exclude_context = 'Y' AND substitution_id <> p_substitution_id)
2435 OR p_exclude_context = 'N'
2436 )
2437 ORDER BY START_DATE;
2438 BEGIN
2439 IF (p_substitution_id IS NULL AND p_item_id IS NULL) OR
2440 (p_substitution_id IS NOT NULL AND p_item_id IS NOT NULL) OR
2441 (p_exclude_context ='Y' AND p_substitution_id IS NULL)THEN
2442 RETURN NULL;
2443 END IF;
2444
2445 IF p_item_id IS NOT NULL THEN
2446 l_item_id := p_item_id;
2447 ELSE
2448 OPEN cur_item_id;
2449 FETCH cur_item_id INTO l_item_id;
2450 CLOSE cur_item_id;
2451 END IF;
2452
2453 OPEN cur_count;
2454 FETCH cur_count INTO l_count;
2455 CLOSE cur_count;
2456
2457 IF l_count = 0 THEN
2458 RETURN NULL;
2459 ELSIF l_count = 1 THEN
2460 OPEN cur_s_sub_date;
2461 FETCH cur_s_sub_date INTO l_ingredent_end_date;
2462 CLOSE cur_s_sub_date;
2463 RETURN l_ingredent_end_date;
2464 ELSE
2465 OPEN cur_m_sub_date;
2466 FETCH cur_m_sub_date INTO l_ingredent_end_date;
2467 CLOSE cur_m_sub_date;
2468 RETURN l_ingredent_end_date;
2469 END IF;
2470 END derive_ingredent_end;
2471
2472 /*+========================================================================+
2473 ** Name : update_end_date
2474 ** Notes : This procedure updates the material end dates based on
2475 ** the substitution start date
2476 ** If everything is fine then OUT parameter
2477 ** x_return_status is set to 'S' else appropriate
2478 ** error message is put on the stack and error
2479 ** is returned.
2480 **
2481 ** HISTORY
2482 ** 30-Apr-2005 B4479101 TDaniel Created.
2483 ** 28-Nov-2006 B5640547 akaruppa Removed the organization_id check when
2484 ** updating fm_matl_dtl with end date.
2485 **+========================================================================+*/
2486
2487 PROCEDURE update_end_date (p_substitution_id IN NUMBER) IS
2488
2489 CURSOR Cur_get_substitution IS
2490 SELECT i.original_inventory_item_id, i.start_date, i.substitution_status,
2491 f.formula_id, i.owner_organization_id
2492 FROM gmd_item_substitution_hdr_b i, gmd_formula_substitution f
2493 WHERE i.substitution_id = p_substitution_id
2494 AND i.substitution_id = f.substitution_id;
2495
2496 CURSOR Cur_check_substitute (V_formula_id NUMBER, V_item_id NUMBER, V_orgn_id NUMBER) IS
2497 SELECT MIN(i.start_date)
2498 FROM gmd_item_substitution_hdr_b i, gmd_formula_substitution f
2499 WHERE f.formula_id = V_formula_id
2500 AND i.original_inventory_item_id = V_item_id
2501 AND i.owner_organization_id = V_orgn_id
2502 AND i.substitution_id <> p_substitution_id
2503 AND i.substitution_id = f.substitution_id
2504 AND i.substitution_status BETWEEN 700 AND 799;
2505
2506 l_start_date DATE;
2507
2508 BEGIN
2509 FOR l_rec IN Cur_get_substitution LOOP
2510 /* Substitution is approved so lets update the formula line end date */
2511 IF l_rec.substitution_status BETWEEN 700 AND 799 THEN
2512 UPDATE fm_matl_dtl
2513 SET ingredient_end_date = l_rec.start_date
2514 WHERE formula_id = l_rec.formula_id
2515 AND line_type = -1
2516 AND inventory_item_id = l_rec.original_inventory_item_id
2517 -- AND organization_id = l_rec.owner_organization_id
2518 AND (ingredient_end_date IS NULL OR ingredient_end_date > l_rec.start_date);
2519 /* If the substitution is obsolete then we need to reset the end date if it was stamped */
2520 /* by the current list */
2521 ELSIF l_rec.substitution_status BETWEEN 1000 AND 1099 THEN
2522 OPEN Cur_check_substitute (l_rec.formula_id,
2523 l_rec.original_inventory_item_id,
2524 l_rec.owner_organization_id);
2525 FETCH Cur_check_substitute INTO l_start_date;
2526 CLOSE Cur_check_substitute;
2527
2528 UPDATE fm_matl_dtl
2529 SET ingredient_end_date = l_start_date
2530 WHERE formula_id = l_rec.formula_id
2531 AND line_type = -1
2532 AND inventory_item_id = l_rec.original_inventory_item_id;
2533 -- AND organization_id = l_rec.owner_organization_id
2534
2535 END IF;
2536 END LOOP;
2537 END update_end_date;
2538
2539 /*+========================================================================+
2540 ** Name : get_message()
2541 ** Notes : This function return the top most message on the stack
2542 **
2543 ** HISTORY
2544 ** 30-Aug-2005 shyam Created.
2545 **+========================================================================+*/
2546 FUNCTION get_message RETURN Varchar2 IS
2547 l_msg_txt Varchar2(2000) := '';
2548 l_msg_index Number;
2549 Begin
2550 l_msg_txt := fnd_message.get;
2551
2552 IF (l_msg_txt IS NULL) THEN
2553 FND_MSG_PUB.Get(
2554 p_msg_index => 1,
2555 p_data => l_msg_txt,
2556 p_encoded => FND_API.G_FALSE,
2557 p_msg_index_out => l_msg_index);
2558 END IF;
2559
2560 Return l_msg_txt;
2561 End get_message;
2562
2563 /*+========================================================================+
2564 ** Name : get_recipe_type
2565 ** Notes : This procedure retrieves the recipe type for an organization.
2566 **
2567 ** HISTORY
2568 ** 11-Nov-2005 B4479101 TDaniel Created.
2569 **+========================================================================+*/
2570
2571 FUNCTION get_recipe_type (p_organization_id IN NUMBER) RETURN NUMBER IS
2572 l_recipe_type PLS_INTEGER;
2573 l_return_status VARCHAR2(1);
2574 l_exists PLS_INTEGER;
2575
2576 CURSOR Cur_get_master_org IS
2577 SELECT 1
2578 FROM sys.dual
2579 WHERE EXISTS (SELECT 1
2580 FROM mtl_parameters
2581 WHERE master_organization_id = p_organization_id);
2582 BEGIN
2583 /* First lets check if there is a value setup at org parameters level */
2584 GMD_API_GRP.fetch_parm_values(p_orgn_id => p_organization_id
2585 ,p_parm_name => 'GMD_RECIPE_TYPE'
2586 ,p_parm_value => l_recipe_type
2587 ,x_return_status => l_return_status);
2588 IF l_recipe_type IS NOT NULL THEN
2589 RETURN l_recipe_type;
2590 END IF;
2591
2592 /* Lets check if the organization passed is a master organization */
2593 OPEN Cur_get_master_org;
2594 FETCH Cur_get_master_org INTO l_exists;
2595 IF Cur_get_master_org%FOUND THEN
2596 -- Sriram. Bug 4672941
2597 -- If orgn is a Master orgn, return 'General' as recipe type
2598 l_recipe_type := 0;
2599 ELSE
2600 l_recipe_type := 1;
2601 END IF;
2602 CLOSE Cur_get_master_org;
2603 RETURN l_recipe_type;
2604 END get_recipe_type;
2605
2606 /*======================================================================
2607 -- Function :
2608 -- get_def_status_code
2609 -- KSHUKLA bug 5199586
2610 -- DESCRIPTION:
2611 -- Used to return the status code for an entity.
2612 -- REQUIREMENTS
2613 --
2614 -- SYNOPSIS:
2615 --
2616 --===================================================================== */
2617 FUNCTION get_def_status_code(p_entity_type varchar2,
2618 p_orgn_id NUMBER)
2619 RETURN NUMBER is
2620 l_entity_status gmd_api_grp.status_rec_type;
2621 BEGIN
2622 get_status_details(V_entity_type => p_entity_type,
2623 V_orgn_id =>p_orgn_id,
2624 X_entity_status => l_entity_status);
2625 return l_entity_status.entity_status;
2626 END get_def_status_code;
2627
2628
2629 /*+========================================================================+
2630 ** Name : validity_revision_check
2631 ** Notes : This procedure checks if the passed in item has revision
2632 ** associated with it in the given formula. It returns "Y" if
2633 ** the item is defined as a product with revision in the formula.
2634 ** Also, it returns the revision value, if there is a single
2635 ** revision for the item in the formula.
2636 ** HISTORY
2637 ** 21-Jun-2006 B5309386 TDaniel Created.
2638 **+========================================================================+*/
2639
2640 PROCEDURE validity_revision_check (p_formula_id IN NUMBER,
2641 p_organization_id IN NUMBER,
2642 p_inventory_item_id IN NUMBER,
2643 x_enable_revision OUT NOCOPY VARCHAR2,
2644 x_revision OUT NOCOPY VARCHAR2) IS
2645 CURSOR Cur_get_prod_lines IS
2646 SELECT revision
2647 FROM fm_matl_dtl
2648 WHERE formula_id = p_formula_id
2649 AND inventory_item_id = p_inventory_item_id
2650 AND line_type = 1
2651 ORDER BY revision;
2652
2653 CURSOR Cur_check_revision (V_revision VARCHAR2) IS
2654 SELECT 1
2655 FROM sys.dual
2656 WHERE EXISTS (SELECT 1
2657 FROM mtl_item_revisions
2658 WHERE inventory_item_id = p_inventory_item_id
2659 AND organization_id = p_organization_id
2660 AND revision = V_revision);
2661
2662 l_revision_found PLS_INTEGER := 0;
2663 l_exists PLS_INTEGER;
2664 BEGIN
2665 IF p_organization_id IS NOT NULL THEN
2666 /* Lets initialize enable revision to N as default */
2667 x_enable_revision := 'N';
2668
2669 /* Lets get all the formula product lines for this item */
2670 FOR l_rec IN Cur_get_prod_lines LOOP
2671 /* If the line has a revision associated with it */
2672 IF l_rec.revision IS NOT NULL THEN
2673 /* Check if this revision has already been validated */
2674 IF NVL(x_revision, 'ZZZZ') <> l_rec.revision THEN
2675 /* Lets verify if this revision exists for this item */
2676 /* under the organization that has been passed in */
2677 OPEN Cur_check_revision (l_rec.revision);
2678 FETCH Cur_check_revision INTO l_exists;
2679 IF Cur_check_revision%FOUND THEN
2680 /* This revision exists for the organization so */
2681 /* lets set our variables properly */
2682 l_revision_found := l_revision_found + 1;
2683 x_revision := l_rec.revision;
2684 END IF;
2685 CLOSE Cur_check_revision;
2686 END IF; /* IF NVL(x_revision, 'ZZZZ') <> l_rec.revision */
2687 ELSE
2688 /* We have to increment this variable though the revison */
2689 /* is NULL to catch the case where the customer could set */
2690 /* a product with revision and without a revision in the formula */
2691 /* in this case we should not pass back a default revision */
2692 l_revision_found := l_revision_found + 1;
2693 END IF; /* IF l_rec.revision IS NOT NULL */
2694
2695 /* if we find two records with different revision then we */
2696 /* need not continue */
2697 IF l_revision_found > 1 THEN
2698 /* Since there are multiple revisions for the line */
2699 /* there will be no default value populated */
2700 X_revision := NULL;
2701 x_enable_revision := 'Y'; -- Bug 5309386 rework
2702 EXIT;
2703 END IF;
2704 END LOOP;
2705 ELSE
2706 /* If the validity rule is a global one then it does */
2707 /* does not make sense to provide a revision */
2708 x_enable_revision := 'N';
2709 END IF;
2710 END validity_revision_check;
2711
2712 END gmd_api_grp;
[Home] [Help]