1 PACKAGE BODY GMD_STATUS_PUB AS
2 /* $Header: GMDPSTSB.pls 120.6.12000000.2 2007/02/19 19:14:38 rajreddy ship $ */
3
4 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
5 --Forward declaration.
6 FUNCTION set_debug_flag RETURN VARCHAR2;
7 l_debug VARCHAR2(1) := set_debug_flag;
8
9 FUNCTION set_debug_flag RETURN VARCHAR2 IS
10 l_debug VARCHAR2(1):= 'N';
11 BEGIN
12 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
13 l_debug := 'Y';
14 END IF;
15 RETURN l_debug;
16 END set_debug_flag;
17 --Bug 3222090, NSRIVAST 20-FEB-2004, END
18
19 /*###############################################################
20 # NAME
21 # Validate Operation
22 # SYNOPSIS
23 # proc Validate_operation
24 # DESCRIPTION
25 # Validates operation if all the resources are attached to
26 # every activity.
27 ###############################################################*/
28 PROCEDURE validate_operation(oprn_id IN NUMBER,
29 x_return_status OUT NOCOPY VARCHAR2)IS
30
31 CURSOR Cur_get_activities(poprn_id NUMBER) IS
32 SELECT activity
33 FROM gmd_operation_activities a
34 WHERE NOT EXISTS (select 'X' from gmd_operation_resources r
35 where a.oprn_line_id = r.oprn_line_id)
36 AND a.oprn_id = poprn_id;
37
38 x_count NUMBER;
39 x_temp_rec Cur_get_activities%ROWTYPE;
40 x_activity LONG;
41 x_o_res_act_cnt NUMBER := 0;
42 BEGIN
43 x_return_status := FND_API.G_RET_STS_SUCCESS;
44 FOR oprn_rec IN Cur_get_activities(oprn_id) LOOP
45 x_o_res_act_cnt := x_o_res_act_cnt + 1;
46 X_activity := x_activity||oprn_rec.activity||', ';
47 END LOOP;
48 IF (x_o_res_act_cnt > 0) THEN
49 FND_MESSAGE.SET_NAME('GMD','GMD_ATTACH_RESOURCES');
50 FND_MESSAGE.SET_TOKEN('ACTIVITY',x_activity);
51 FND_MSG_PUB.ADD;
52 x_return_status := FND_API.G_RET_STS_ERROR;
53 END IF;
54 END validate_operation;
55
56 /* =============================================================== */
57 /* Procedure: */
58 /* modify_status */
59 /* */
60 /* DESCRIPTION: */
61 /* */
62 /* API returns (x_return_code) = 'S' if the update of status code */
63 /* is successful. */
64 /* */
65 /* History : */
66 /* Shyam 07/29/2002 Initial implementation */
67 /* Shyam 05/30/03 Bug 2985443 Cannot change formula status */
68 /* to approved for general use or Lab use for */
69 /* formulas created with total output qty =0 */
70 /* Jeff Baird 02/11/2004 Changed gmd_api_pub to gmd_api_grp. */
71 /* Kalyani 07/03/2006 Bug 5347418 Fetched recipe_use and checked */
72 /* if items are costing enabled if recipe use */
73 /* is for costing. */
74 /* kalyani 08/23/2006 Bug 5394532 Added code for substitution */
75 /* kalyani 09/19/2006 Bug 5534373 Removed code to check if */
76 /* product is GME enabled */
77 /* =============================================================== */
78 PROCEDURE modify_status
79 ( p_api_version IN NUMBER := 1
80 , p_init_msg_list IN BOOLEAN := TRUE
81 , p_entity_name IN VARCHAR2
82 , p_entity_id IN NUMBER := NULL
83 , p_entity_no IN VARCHAR2 := NULL
84 , p_entity_version IN NUMBER := NULL
85 , p_to_status IN VARCHAR2
86 , p_ignore_flag IN BOOLEAN := FALSE
87 , x_message_count OUT NOCOPY NUMBER
88 , x_message_list OUT NOCOPY VARCHAR2
89 , x_return_status OUT NOCOPY VARCHAR2
90 ) IS
91
92 /* Local variable section */
93 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_STATUS';
94 l_mesg_count NUMBER;
95 l_mesg_data VARCHAR2(2000);
96 l_return_status VARCHAR2(1);
97 l_entity_id NUMBER;
98 l_entity_no VARCHAR2(32);
99 l_entity_version NUMBER;
100 l_entity_name VARCHAR2(100);
101 l_entity_value NUMBER;
102 l_form_id NUMBER;
103 l_org_id NUMBER;
104 l_prod_exec_enabled VARCHAR2(1);
105
106 l_from_status gmd_status_b.status_code%TYPE;
107 l_from_status_type gmd_status_b.status_type%TYPE;
108 l_from_status_desc gmd_status.description%TYPE;
109 l_to_status_type gmd_status_b.status_type%TYPE;
110 l_to_status_desc gmd_status.description%TYPE;
111
112 l_target_status gmd_status_next.target_status%TYPE;
113 l_rework_status gmd_status_next.rework_status%TYPE;
114 l_pending_status gmd_status_next.pending_status%TYPE;
115 l_mesg_text VARCHAR2(1000);
116
117 l_eSignature_status VARCHAR2(10);
118 l_check_vr NUMBER;
119 l_toq NUMBER;
120 l_expr_items_found NUMBER;
121
122 l_table_name VARCHAR2(30);
123
124 l_recipe_use NUMBER; -- Bug 5347418
125
126 /* Cursor section */
127
128 -- Checks if there is a target status for current status */
129 CURSOR validate_To_status(vStatus_to VARCHAR2
130 ,vStatus_from VARCHAR2) IS
131 SELECT a.status_type, a.description, b.target_status
132 FROM gmd_status a, gmd_status_next b
133 WHERE a.status_code = vStatus_from
134 AND b.target_status = vStatus_to
135 AND a.status_code = b.current_status;
136
137 CURSOR get_To_status_details(vStatus_to VARCHAR2) IS
138 SELECT status_type, description
139 FROM gmd_status
140 WHERE status_code = vStatus_to;
141
142 -- Gets the recipe details and its current status code
143 CURSOR get_curr_recipe_status(vRecipe_id NUMBER
144 ,vRecipe_no VARCHAR2
145 ,vRecipe_vers NUMBER) IS
146 SELECT recipe_id, recipe_no, recipe_version, recipe_status
147 FROM gmd_recipes_b
148 WHERE ((vRecipe_no IS NULL AND vRecipe_vers IS NULL) AND
149 (recipe_id = vRecipe_id)) OR
150 ((vRecipe_id IS NULL) AND
151 (recipe_no = vRecipe_no AND recipe_version = vRecipe_vers));
152
153 -- Gets the formula details and its current status code
154 CURSOR get_curr_formula_status(vformula_id NUMBER
155 ,vformula_no VARCHAR2
156 ,vformula_vers NUMBER) IS
157 SELECT formula_id, formula_no, formula_vers, formula_status
158 FROM fm_form_mst_b
159 WHERE ((vformula_no IS NULL AND vformula_vers IS NULL) AND
160 (formula_id = vformula_id)) OR
161 ((vformula_id IS NULL) AND
162 (formula_no = vformula_no AND formula_vers = vformula_vers));
163
164 -- Gets the routing details and its current status code
165 CURSOR get_curr_routing_status(vrouting_id NUMBER
166 ,vrouting_no VARCHAR2
167 ,vrouting_vers NUMBER) IS
168 SELECT routing_id, routing_no, routing_vers, routing_status
169 FROM gmd_routings_b
170 WHERE ((vrouting_no IS NULL AND vrouting_vers IS NULL) AND
171 (routing_id = vrouting_id)) OR
172 ((vrouting_id IS NULL) AND
173 (routing_no = vrouting_no AND routing_vers = vrouting_vers));
174
175 -- Gets the operation details and its current status code
176 CURSOR get_curr_operation_status(voperation_id NUMBER
177 ,voperation_no VARCHAR2
178 ,voperation_vers NUMBER) IS
179 SELECT oprn_id, oprn_no, oprn_vers, operation_status
180 FROM gmd_operations_b
181 WHERE ((voperation_no IS NULL AND voperation_vers IS NULL) AND
182 (oprn_id = voperation_id)) OR
183 ((voperation_id IS NULL) AND
184 (oprn_no = voperation_no AND oprn_vers = voperation_vers));
185
186 -- Gets the validity rule details and its current status code
187 -- Bug 5347418 added recipe_use
188 CURSOR get_curr_vr_status(vVR_id NUMBER) IS
189 SELECT recipe_validity_rule_id, validity_rule_status, recipe_use
190 FROM gmd_recipe_validity_rules
191 WHERE recipe_validity_rule_id = vVR_id;
192
193 -- Bug 5394532
194 -- Gets the substitution details and its current status code
195 CURSOR get_curr_subst_status(vSubs_id NUMBER
196 ,vSubs_no VARCHAR2
197 ,vSubs_vers NUMBER) IS
198 SELECT substitution_id, substitution_name, substitution_version, substitution_status
199 FROM gmd_item_substitution_hdr_b
200 WHERE ((vSubs_no IS NULL AND vSubs_vers IS NULL) AND
201 (substitution_id = vSubs_id)) OR
202 ((vSubs_id IS NULL) AND
203 (substitution_name = vSubs_no AND substitution_version = vSubs_vers));
204
205 -- Gets the formula associated with the val rule
206 CURSOR get_vr_dets(vVR_id NUMBER) IS
207 SELECT r.formula_id, vr.organization_id
208 FROM gmd_recipes r, gmd_recipe_validity_rules vr
209 WHERE r.recipe_id = vr.recipe_id
210 AND vr.recipe_validity_rule_id = vVR_id;
211
212 -- Recipe being changed to ON-HOLD - check for less than ON-HOLD and FROZEN
213 Cursor check_val_rules_800(vEntity_id NUMBER) IS
214 SELECT 1 from sys.dual
215 WHERE EXISTS (
216 SELECT recipe_validity_rule_id
217 FROM gmd_status s, gmd_recipe_validity_rules v
218 WHERE recipe_id = vEntity_id
219 AND v.validity_rule_status = s.status_code
220 AND (to_number(s.status_type) < to_number('800')
221 OR s.status_type = '900') );
222
223 -- Recipe being FROZEN - check for less than ON-HOLD (as on-hold stays on-hold )
224 Cursor check_val_rules_900(vEntity_id NUMBER) IS
225 SELECT 1 from sys.dual
226 WHERE EXISTS (
227 SELECT recipe_validity_rule_id
228 FROM gmd_status s, gmd_recipe_validity_rules v
229 WHERE recipe_id = vEntity_id
230 AND v.validity_rule_status = s.status_code
231 AND to_number(s.status_type) < to_number('800') );
232
233 -- Recipe being OBSOLETED - thus check for less than obsolete
234 Cursor check_val_rules_1000(vEntity_id NUMBER) IS
235 SELECT 1 from sys.dual
236 WHERE EXISTS (
237 SELECT recipe_validity_rule_id
238 FROM gmd_status s, gmd_recipe_validity_rules v
239 WHERE recipe_id = vEntity_id
240 AND v.validity_rule_status = s.status_code
241 AND to_number(s.status_type) < to_number('1000') );
242
243 Cursor get_fm_toq(vEntity_id NUMBER) IS
244 SELECT SUM(qty)
245 FROM fm_matl_dtl
246 WHERE formula_id = vEntity_id
247 AND line_type IN (1,2);
248
249
250 -- Bug 4499534. Cursor to chk if product is process enabled
251 CURSOR Cur_chk_prod_exec_enabled (V_val_rule_id NUMBER) IS
252 -- Get process ececution enabled setting for the val rule orgn
253 -- (Local val rules)
254 SELECT i.process_execution_enabled_flag
255 FROM mtl_system_items i, gmd_recipe_validity_rules v
256 WHERE v.recipe_validity_rule_id = V_val_rule_id
257 AND i.inventory_item_id = v.inventory_item_id
258 AND i.organization_id = v.organization_id
259 AND v.organization_id IS NOT NULL
260 UNION
261 -- Get process ececution enabled setting for the recipe owning orgn
262 -- for Global recipes (Global val rules)
263 SELECT i.process_execution_enabled_flag
264 FROM mtl_system_items i, gmd_recipe_validity_rules v, gmd_recipes_b re
265 WHERE v.recipe_validity_rule_id = V_val_rule_id
266 AND i.inventory_item_id = v.inventory_item_id
267 AND v.recipe_id = re.recipe_id
268 AND i.organization_id = re.owner_organization_id
269 AND v.organization_id IS NULL;
270
271 CURSOR Cur_experimental_items(V_form_id NUMBER) IS
272 SELECT COUNT(f.inventory_item_id)
273 FROM fm_matl_dtl f, mtl_system_items i
274 WHERE f.formula_id = V_form_id
275 AND f.inventory_item_id = i.inventory_item_id
276 AND f.organization_id = i.organization_id
277 AND i.eng_item_flag = 'Y';
278
279 /* Define Exceptions */
280 setup_failure EXCEPTION;
281 status_update_failure EXCEPTION;
282 invalid_version EXCEPTION;
283
284 -- defining internal functions
285 FUNCTION get_status_meaning(P_status_code IN VARCHAR2) RETURN VARCHAR2 IS
286 CURSOR Cur_get IS
287 SELECT meaning
288 FROM gmd_status
289 WHERE status_code = P_status_code;
290
291 l_meaning gmd_status.meaning%TYPE;
292 BEGIN
293 OPEN Cur_get;
294 FETCH Cur_get INTO l_meaning;
295 CLOSE Cur_get;
296
297 Return l_meaning;
298 END get_status_meaning;
299
300 BEGIN
301
302 SAVEPOINT modify_status;
303
304 IF l_debug = 'Y' THEN
305 gmd_debug.put_line('Begin of modify_status() ');
306 END IF;
307
308 /* Set the return status to success initially */
309 x_return_status := FND_API.G_RET_STS_SUCCESS;
310
311 /* Initialize message list and count if needed */
312 IF p_init_msg_list THEN
313 fnd_msg_pub.initialize;
314 END IF;
315
316 /* Initialize the setup fields */
317 IF NOT gmd_api_grp.setup_done THEN
318 gmd_api_grp.setup_done := gmd_api_grp.setup;
319 END IF;
320 IF NOT gmd_api_grp.setup_done THEN
321 RAISE setup_failure;
322 END IF;
323
324 /* Make sure we have call compatibility */
325 IF NOT FND_API.compatible_api_call ( GMD_STATUS_PUB.m_api_version
326 ,p_api_version
327 ,l_api_name
328 ,GMD_STATUS_PUB.m_pkg_name) THEN
329 x_return_status := FND_API.G_RET_STS_ERROR;
330 RAISE invalid_version;
331 END IF;
332
333 /* Get the TO status type and description */
334 OPEN get_To_status_details(P_to_status);
335 FETCH get_To_status_details INTO l_to_status_type, l_to_status_desc;
336 IF get_To_status_details%NOTFOUND THEN
337 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
338 FND_MESSAGE.SET_TOKEN ('MISSING', FND_MESSAGE.GET);
339 FND_MSG_PUB.ADD;
340 x_return_status := FND_API.g_ret_sts_error;
341 CLOSE get_To_status_details;
342 RAISE status_update_failure;
343 END IF;
344 CLOSE get_To_status_details;
345
346 /* If the P_entity_id value is not passed - then verify if the users have
347 passed in the entity_no and entity_version */
348 IF (UPPER(P_entity_name) like '%RECIPE%') THEN
349 l_entity_name := 'RECIPE';
350 OPEN get_curr_recipe_status(p_entity_id
351 ,P_entity_no
352 ,p_entity_version);
353 FETCH get_curr_recipe_status INTO l_entity_id, l_entity_no,
354 l_entity_version, l_from_status;
355 IF get_curr_recipe_status%NOTFOUND THEN
356 FND_MESSAGE.SET_NAME ('GMD', 'GMD_MISSING');
357 FND_MESSAGE.SET_TOKEN ('MISSING', l_entity_name);
358 FND_MESSAGE.SET_TOKEN ('ID',P_entity_id);
359 FND_MESSAGE.SET_TOKEN ('NO',P_entity_no);
360 FND_MESSAGE.SET_TOKEN ('VERS',P_entity_version);
361 FND_MSG_PUB.ADD;
362 x_return_status := FND_API.g_ret_sts_error;
363 CLOSE get_curr_recipe_status;
364 RAISE status_update_failure;
365 END IF;
366 CLOSE get_curr_recipe_status;
367 ELSIF (UPPER(P_entity_name) like '%FORMULA%') THEN
368 l_entity_name := 'FORMULA';
369 OPEN get_curr_formula_status(p_entity_id
370 ,P_entity_no
371 ,p_entity_version);
372 FETCH get_curr_formula_status INTO l_entity_id, l_entity_no,
373 l_entity_version, l_from_status;
374 IF get_curr_formula_status%NOTFOUND THEN
375 FND_MESSAGE.SET_NAME ('GMD', 'GMD_MISSING');
376 FND_MESSAGE.SET_TOKEN ('MISSING', l_entity_name);
377 FND_MESSAGE.SET_TOKEN ('ID',P_entity_id);
378 FND_MESSAGE.SET_TOKEN ('NO',P_entity_no);
379 FND_MESSAGE.SET_TOKEN ('VERS',P_entity_version);
380 FND_MSG_PUB.ADD;
381 x_return_status := FND_API.g_ret_sts_error;
382 CLOSE get_curr_formula_status;
383 RAISE status_update_failure;
384 END IF;
385 CLOSE get_curr_formula_status;
386
387 -- Bug 2985443 Cannot change of the formula status to approved for general
388 -- use or Lab use for formulas created with a total output qty of zero
389 IF (l_to_status_type IN (400,700)) THEN
390
391 IF l_debug = 'Y' THEN
392 gmd_debug.put_line('For TOQ - P_entity_id is '||l_entity_id);
393 END IF;
394
395 OPEN get_fm_toq(l_Entity_id);
396 FETCH get_fm_toq INTO l_toq;
397 CLOSE get_fm_toq;
398
399 IF l_debug = 'Y' THEN
400 gmd_debug.put_line('TOQ value is '||l_toq);
401 END IF;
402
403 IF (l_toq = 0) THEN
404 FND_MESSAGE.SET_NAME('GMD','GMD_TOTAL_OUTPUT_ZERO');
405 FND_MSG_PUB.ADD;
406 x_return_status := FND_API.g_ret_sts_error;
407 RAISE status_update_failure;
408 END IF;
409
410 -- Sriram - Bug 5035818
411 -- If formula To Status is apfgu, chk for experimental items
412 IF l_to_status_type = 700 THEN
413 OPEN Cur_experimental_items(p_entity_id);
414 FETCH Cur_experimental_items INTO l_expr_items_found;
415 CLOSE Cur_experimental_items;
416 IF l_expr_items_found > 0 THEN
417 FND_MESSAGE.SET_NAME('GMD', 'GMD_EXPR_ITEMS_FOUND');
418 FND_MSG_PUB.ADD;
419 x_return_status := FND_API.g_ret_sts_error;
420 RAISE status_update_failure;
421 END IF;
422 END IF;
423
424 END IF;
425
426 ELSIF (UPPER(P_entity_name) like '%ROUTING%') THEN
427 l_entity_name := 'ROUTING';
428 OPEN get_curr_routing_status(p_entity_id
429 ,P_entity_no
430 ,p_entity_version);
431 FETCH get_curr_routing_status INTO l_entity_id, l_entity_no,
432 l_entity_version, l_from_status;
433 IF get_curr_routing_status%NOTFOUND THEN
434 FND_MESSAGE.SET_NAME ('GMD', 'GMD_MISSING');
435 FND_MESSAGE.SET_TOKEN ('MISSING', l_entity_name);
436 FND_MESSAGE.SET_TOKEN ('ID',P_entity_id);
437 FND_MESSAGE.SET_TOKEN ('NO',P_entity_no);
438 FND_MESSAGE.SET_TOKEN ('VERS',P_entity_version);
439 FND_MSG_PUB.ADD;
440 x_return_status := FND_API.g_ret_sts_error;
441 CLOSE get_curr_routing_status;
442 RAISE status_update_failure;
443 END IF;
444 CLOSE get_curr_routing_status;
445 ELSIF (UPPER(P_entity_name) like '%OPERATION%') THEN
446 l_entity_name := 'OPERATION';
447 OPEN get_curr_operation_status(p_entity_id
448 ,P_entity_no
449 ,p_entity_version);
450 FETCH get_curr_operation_status INTO l_entity_id, l_entity_no,
451 l_entity_version, l_from_status;
452 IF get_curr_operation_status%NOTFOUND THEN
453 FND_MESSAGE.SET_NAME ('GMD', 'GMD_MISSING');
454 FND_MESSAGE.SET_TOKEN ('MISSING', l_entity_name);
455 FND_MESSAGE.SET_TOKEN ('ID',P_entity_id);
456 FND_MESSAGE.SET_TOKEN ('NO',P_entity_no);
457 FND_MESSAGE.SET_TOKEN ('VERS',P_entity_version);
458 FND_MSG_PUB.ADD;
459 x_return_status := FND_API.g_ret_sts_error;
460 CLOSE get_curr_operation_status;
461 RAISE status_update_failure;
462 END IF;
463 CLOSE get_curr_operation_status;
464
465 /* For each Operation, check if its activities, and
466 check if there at least one resource attached to the activities.
467 If none of the activities for this operation have
468 at least one resource then we prevent the change of
469 status for this operation to 700 or 400. */
470 IF (to_number(l_to_status_type) IN (400,700)) THEN
471 validate_operation(l_entity_id, x_return_status);
472 IF l_debug = 'Y' THEN
473 gmd_debug.put_line('The return status after calling val operation is '||
474 x_return_status);
475 END IF;
476 IF (x_return_status = FND_API.g_ret_sts_error) THEN
477 RAISE status_update_failure;
478 END IF;
479 END IF;
480 ELSIF (UPPER(P_entity_name) like '%VALIDITY%') THEN
481 l_entity_name := 'VALIDITY';
482 OPEN get_curr_vr_status(p_entity_id);
483 -- Bug 53487418 added l_recipe_use
484 FETCH get_curr_vr_status INTO l_entity_id, l_from_status, l_recipe_use;
485 IF get_curr_vr_status%NOTFOUND THEN
486 FND_MESSAGE.SET_NAME ('GMI', 'GMI_MISSING');
487 FND_MESSAGE.SET_TOKEN ('MISSING','P_ENTITY_ID');
488 FND_MSG_PUB.ADD;
489 x_return_status := FND_API.g_ret_sts_error;
490 CLOSE get_curr_vr_status;
491 RAISE status_update_failure;
492 END IF;
493 CLOSE get_curr_vr_status;
494 IF (TO_NUMBER(l_to_status_type) IN (400,700)) THEN
495 -- Bug# 5331823 Added the validation for Approved for Lab Use also
496
497 -- Get formula id associated with the Val rule
498 OPEN get_vr_dets(p_entity_id);
499 FETCH get_vr_dets INTO l_form_id, l_org_id;
500 CLOSE get_vr_dets;
501
502 -- If not global val rule, chk if formula items are production enabled in
503 -- validity rule orgn
504 IF l_org_id IS NOT NULL THEN
505 -- Bug 5347418 Validate for prod or costing based on recipe_use
506 IF l_recipe_use IN (0,1) THEN
507 GMD_API_GRP.check_item_exists( p_formula_id => l_form_id,
508 x_return_status => l_return_status,
509 p_organization_id => l_org_id,
510 p_production_check => TRUE);
511
512 IF l_return_status <> FND_API.g_ret_sts_success THEN
513 FND_MSG_PUB.GET (p_msg_index => 1,
514 p_data => l_mesg_data,
515 p_encoded => 'F',
516 p_msg_index_out => l_mesg_count);
517 --FND_MSG_PUB.ADD;
518 x_return_status := FND_API.g_ret_sts_error;
519 RAISE status_update_failure;
520 END IF;
521 ELSIF l_recipe_use = 2 THEN
522 GMD_API_GRP.check_item_exists( p_formula_id => l_form_id,
523 x_return_status => l_return_status,
524 p_organization_id => l_org_id,
525 p_costing_check => TRUE);
526
527 IF l_return_status <> FND_API.g_ret_sts_success THEN
528 FND_MSG_PUB.GET (p_msg_index => 1,
529 p_data => l_mesg_data,
530 p_encoded => 'F',
531 p_msg_index_out => l_mesg_count);
532 --FND_MSG_PUB.ADD;
533 x_return_status := FND_API.g_ret_sts_error;
534 RAISE status_update_failure;
535 END IF;
536 END IF;
537
538 END IF;
539
540 -- Bug 4499534. Chk if product is process execution enabled
541 -- Bug 5534376/5534373 Removed code added for 4499534 as the above code
542 -- for check_item_exists check for it.
543 END IF;
544 -- Bug 5394532
545 ELSIF (UPPER(P_entity_name) like '%SUBSTITUTION%') THEN
546 l_entity_name := 'SUBSTITUTION';
547 OPEN get_curr_subst_status(p_entity_id
548 ,P_entity_no
549 ,p_entity_version);
550 FETCH get_curr_subst_status INTO l_entity_id, l_entity_no,
551 l_entity_version, l_from_status;
552 IF get_curr_subst_status%NOTFOUND THEN
553 FND_MESSAGE.SET_NAME ('GMD', 'GMD_MISSING');
554 FND_MESSAGE.SET_TOKEN ('MISSING', l_entity_name);
555 FND_MESSAGE.SET_TOKEN ('ID',P_entity_id);
556 FND_MESSAGE.SET_TOKEN ('NO',P_entity_no);
557 FND_MESSAGE.SET_TOKEN ('VERS',P_entity_version);
558 FND_MSG_PUB.ADD;
559 x_return_status := FND_API.g_ret_sts_error;
560 CLOSE get_curr_subst_status;
561 RAISE status_update_failure;
562 END IF;
563 CLOSE get_curr_subst_status;
564 ELSE -- not able to recognize the entity name
565 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNKNOWN_ENTITY');
566 FND_MESSAGE.SET_TOKEN('ENTITY_NAME', P_entity_name);
567 FND_MSG_PUB.ADD;
568 x_return_status := FND_API.g_ret_sts_error;
569 RAISE status_update_failure;
570 END IF;
571
572 IF (l_debug = 'Y') THEN
573 gmd_debug.put_line('The entity details are Entity_name = '||l_entity_name
574 ||', Entity Id = '||l_entity_id||', Entity no is '||l_entity_no
575 ||', Entity version is '||l_entity_version
576 ||' and its status is '||l_from_status);
577
578 gmd_debug.put_line('About to verify if the To status is valid '||
579 'the From status is '||l_from_status||' and To status is '||P_to_status);
580 END IF;
581
582
583 /* Validate if this Entity can be modified by this user */
584 IF NOT GMD_API_GRP.Check_orgn_access(Entity => P_entity_name
585 ,Entity_id => l_entity_id) THEN
586 RAISE status_update_failure;
587 END IF;
588
589 /* Verify if P_to_status is valid for the current status code */
590 IF (P_to_status <> l_from_status) THEN
591 OPEN validate_To_status(P_to_status ,l_from_status);
592 FETCH validate_To_status INTO l_from_status_type, l_from_status_desc, l_target_status;
593 IF validate_To_status%NOTFOUND THEN
594 FND_MESSAGE.SET_NAME ('GMD', 'GMD_INV_TARGET_STATUS');
595 FND_MESSAGE.SET_TOKEN ('TO_STATUS', l_to_status_desc);
596 FND_MSG_PUB.ADD;
597 x_return_status := FND_API.g_ret_sts_error;
598 CLOSE validate_To_status;
599 RAISE status_update_failure;
600 END IF;
601 CLOSE validate_To_status;
602 ELSE
603 FND_MESSAGE.SET_NAME ('GMD', 'GMD_STS_SAME');
604 FND_MESSAGE.SET_TOKEN ('NAME', P_entity_name);
605 FND_MSG_PUB.ADD;
606 RETURN;
607 END IF;
608
609 IF (l_debug = 'Y') THEN
610 gmd_debug.put_line('From status type = '||l_from_status_type
611 ||', From Status desc = '||l_from_status_desc
612 ||' and target status = '||l_target_status);
613 END IF;
614
615 /* Check parent status */
616 -- Check if entity is already in use before putting it on hold
617 -- or obsoleting it.
618 IF (l_debug = 'Y') THEN
619 gmd_debug.put_line('Checking the Parent status dependency for '||l_entity_name);
620 END IF;
621 IF (to_number(l_to_status_type) IN (800, 1000)) THEN
622 IF (NOT gmd_status_code.check_parent_status(l_entity_name
623 ,l_entity_id)) THEN
624 SELECT DECODE(l_entity_name,
625 'FORMULA','GMD_FORMULA_INUSE',
626 'RECIPE','GMD_RECIPE_BTCH_DEP',
627 'OPERATION','GMD_OPERATION_INUSE',
628 'ROUTING','GMD_ROUTING_INUSE',
629 'VALIDITY','GMD_VR_BTCH_DEP') INTO l_mesg_text
630 FROM sys.dual;
631
632 IF l_entity_name IN ('FORMULA','OPERATION','ROUTING') THEN
633 FND_MESSAGE.SET_NAME('GMD',l_mesg_text);
634 FND_MSG_PUB.ADD;
635 x_return_status := FND_API.g_ret_sts_error;
636 RAISE status_update_failure;
637 ELSIF l_entity_name IN ('RECIPE','VALIDITY') THEN
638 IF NOT P_ignore_flag THEN
639 FND_MESSAGE.SET_NAME('GMD',l_mesg_text);
640 FND_MSG_PUB.ADD;
641 x_return_status := FND_API.g_ret_sts_error;
642 RAISE status_update_failure;
643 END IF; -- p_ignore_flag is false
644 END IF; -- entity type check
645 END IF; -- Checking parent status
646 END IF;
647
648 IF (l_debug = 'Y') THEN
649 gmd_debug.put_line('Checking the Dependent status, From status = '||l_from_status
650 ||' To Status '||p_to_status);
651 END IF;
652
653 /* Check Dependent Status */
654 SELECT DECODE(l_entity_name,
655 'FORMULA',1,
656 'RECIPE',3,
657 'OPERATION',2,
658 'ROUTING',4,
659 'VALIDITY',5) INTO l_entity_value
660 FROM sys.dual;
661
662 IF NOT ( GMD_STATUS_CODE.CHECK_DEPENDENT_STATUS
663 (l_entity_value,
664 l_entity_id,
665 l_from_status,
666 P_to_status) ) THEN
667 /* if function from stored package returns FALSE, then
668 * dependent entities do not have proper status, and this
669 * entity's status cannot be changed. Ex. routing has at
670 * least one operation which is not approved. The function
671 * returns TRUE if entity does not have dependents (formula,
672 * operation) or if to_status does not require the check
673 * (frozen, on-hold, obsolete, some version of New). */
674 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS_DEPEND_NOT_APPROVED');
675 FND_MSG_PUB.ADD;
676 x_return_status := FND_API.g_ret_sts_error;
677 RAISE status_update_failure;
678 ELSE
679
680 IF (l_debug = 'Y') THEN
681 gmd_debug.put_line('Dependent status are valid, about to check if recipes'||
682 ' have any VRs , The from status type = '||l_from_status_type);
683 END IF;
684
685 -- Only when entity is 'RECIPE'
686 IF (l_entity_name = 'RECIPE') THEN
687 IF (to_number(l_to_status_type) = 800) THEN
688 OPEN check_val_rules_800(l_entity_id) ;
689 FETCH check_val_rules_800 into l_check_vr ;
690 CLOSE check_val_rules_800 ;
691 ELSIF (to_number(l_to_status_type) = 900 ) THEN
692 OPEN check_val_rules_900(l_entity_id) ;
693 FETCH check_val_rules_900 into l_check_vr ;
694 CLOSE check_val_rules_900 ;
695 ELSIF (to_number(l_to_status_type) = 1000) THEN
696 IF (l_debug = 'Y') THEN
697 gmd_debug.put_line('about to derive l_chk_vr, l_entity_id is '||l_entity_id);
698 END IF;
699 OPEN check_val_rules_1000(l_entity_id) ;
700 FETCH check_val_rules_1000 into l_check_vr ;
701 CLOSE check_val_rules_1000 ;
702 END IF ;
703
704 IF (l_debug = 'Y') THEN
705 gmd_debug.put_line('Dependent status are valid and check VR = '||l_check_vr);
706 END IF;
707
708 IF l_check_vr = 1 THEN
709 /*ERES Implementation - If approvals are required for the */
710 /*status change of the validity rules then the user has to */
711 /*do them manually */
712 IF GMD_ERES_UTILS.check_recipe_validity_eres (p_recipe_id => l_entity_id
713 ,p_to_status => P_to_status) THEN
714 FND_MESSAGE.SET_NAME('GMD', 'GMD_VLDT_APPR_REQD');
715 FND_MESSAGE.SET_TOKEN('STATUS', l_to_status_desc);
716 FND_MSG_PUB.ADD;
717 x_return_status := FND_API.g_ret_sts_error;
718 RAISE status_update_failure;
719 END IF;
720
721 /* Check if the VR for this recipe needs to be updated */
722 IF (p_ignore_flag) THEN
723 -- Now update the VR according to recipe status change
724 IF (to_number(l_to_status_type) = 800) THEN
725 -- Change status to ON-HOLD for less than ON-HOLD
726 UPDATE gmd_recipe_validity_rules
727 SET validity_rule_status = P_to_status
728 WHERE recipe_id = l_entity_id
729 AND (to_number(validity_rule_status) < to_number('800') OR
730 to_number(validity_rule_status) between 900 and 999);
731 ELSIF (to_number(l_to_status_type) = 900) THEN
732 UPDATE gmd_recipe_validity_rules
733 SET validity_rule_status = P_to_status
734 WHERE recipe_id = l_entity_id
735 AND to_number(validity_rule_status) < to_number('800') ;
736 ELSIF (to_number(l_to_status_type) = 1000) THEN
737 IF (l_debug = 'Y') THEN
738 gmd_debug.put_line('Ignore flag was true and we are about update VR ');
739 END IF;
740 UPDATE gmd_recipe_validity_rules
741 SET validity_rule_status = P_to_status
742 WHERE recipe_id = l_entity_id
743 AND to_number(validity_rule_status) < to_number('1000') ;
744 END IF;
745 ELSE
746 FND_MESSAGE.SET_NAME('GMD', 'GMD_RCP_VR_STATUS');
747 FND_MESSAGE.SET_TOKEN('TO_STATUS',P_to_status);
748 FND_MSG_PUB.ADD;
749 x_return_status := FND_API.g_ret_sts_error;
750 RAISE status_update_failure;
751 END IF; /* if feedback is OK */
752 END IF ; /* if validity rules exists , l_check_vr=1 */
753 END IF; /* IF (l_entity_name = 'RECIPE') */
754 END IF; /* Check Dependent Status */
755
756 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
757 IF (l_from_status <> P_to_status) THEN
758 IF (l_debug = 'Y') THEN
759 gmd_debug.put_line('About to get the pending and rework status ');
760 END IF;
761 /* Added the following code as part of ERES Implementation */
762 l_pending_status := GMD_STATUS_CODE.get_pending_status
763 (p_from_status => l_from_status
764 ,p_to_status => P_to_status);
765
766 l_rework_status := GMD_STATUS_CODE.get_rework_status
767 (p_from_status => l_from_status
768 ,p_to_status => P_to_status);
769
770 IF (l_debug = 'Y') THEN
771 gmd_debug.put_line('Pending and Rework status is '||l_pending_status
772 ||' and '||l_rework_status);
773
774 gmd_debug.put_line('About to impement ERES for '||l_entity_name||
775 ' Entity id is '||l_entity_id||' the To status is '||P_to_status||
776 ' the entity no is '||l_entity_no||' the version is '||l_entity_version);
777 END IF;
778
779 IF (l_entity_name = 'FORMULA') THEN
780
781 GMD_ERES_UTILS.update_formula_status
782 (p_formula_id => l_entity_id
783 ,p_from_status => l_from_status
784 ,p_to_status => P_to_status
785 ,p_pending_status => l_pending_status
786 ,p_rework_status => l_rework_status
787 ,p_object_name => l_entity_no
788 ,p_object_version => l_entity_version
789 ,p_called_from_form => 'T'
790 ,x_return_status => l_eSignature_status);
791
792 ELSIF (l_entity_name = 'RECIPE') THEN
793
794 IF l_debug = 'Y' THEN
795 gmd_debug.put_line('In GMD Status Pub - About call ERES Util for update Recipe ');
796 END IF;
797
798 GMD_ERES_UTILS.update_recipe_status
799 (p_recipe_id => l_entity_id
800 ,p_from_status => l_from_status
801 ,p_to_status => P_to_status
802 ,p_pending_status => l_pending_status
803 ,p_rework_status => l_rework_status
804 ,p_object_name => l_entity_no
805 ,p_object_version => l_entity_version
806 ,p_called_from_form => 'T'
807 ,x_return_status => l_eSignature_status);
808
809 IF l_debug = 'Y' THEN
810 gmd_debug.put_line('In GMD Status Pub - After call ERES Util for update Recipe ');
811 END IF;
812
813 ELSIF(l_entity_name = 'OPERATION') THEN
814
815 GMD_ERES_UTILS.update_operation_status
816 (p_oprn_id => l_entity_id
817 ,p_from_status => l_from_status
818 ,p_to_status => P_to_status
819 ,p_pending_status => l_pending_status
820 ,p_rework_status => l_rework_status
821 ,p_object_name => l_entity_no
822 ,p_object_version => l_entity_version
823 ,p_called_from_form => 'T'
824 ,x_return_status => l_eSignature_status);
825
826 ELSIF(l_entity_name = 'ROUTING') THEN
827
828 GMD_ERES_UTILS.update_routing_status
829 (p_routing_id => l_entity_id
830 ,p_from_status => l_from_status
831 ,p_to_status => P_to_status
832 ,p_pending_status => l_pending_status
833 ,p_rework_status => l_rework_status
834 ,p_object_name => l_entity_no
835 ,p_object_version => l_entity_version
836 ,p_called_from_form => 'T'
837 ,x_return_status => l_eSignature_status);
838
839 ELSIF(l_entity_name = 'VALIDITY') THEN
840
841 GMD_ERES_UTILS.update_validity_rule_status
842 ( p_validity_rule_id => l_entity_id
843 ,p_from_status => l_from_status
844 ,p_to_status => P_to_status
845 ,p_pending_status => l_pending_status
846 ,p_rework_status => l_rework_status
847 ,p_called_from_form => 'T'
848 ,x_return_status => l_eSignature_status);
849 -- Bug 5394532
850 ELSIF (l_entity_name = 'SUBSTITUTION') THEN
851
852 IF l_debug = 'Y' THEN
853 gmd_debug.put_line('In GMD Status Pub - About call ERES Util for update substitution ');
854 END IF;
855
856 GMD_ERES_UTILS.update_substitution_status
857 (p_substitution_id => l_entity_id
858 ,p_from_status => l_from_status
859 ,p_to_status => P_to_status
860 ,p_pending_status => l_pending_status
861 ,p_rework_status => l_rework_status
862 ,p_called_from_form => 'T'
863 ,x_return_status => l_eSignature_status);
864
865 IF l_debug = 'Y' THEN
866 gmd_debug.put_line('In GMD Status Pub - After call ERES Util for update substitution ');
867 END IF;
868 END IF;
869
870 IF (l_debug = 'Y') THEN
871 gmd_debug.put_line('After ERES implementation');
872 END IF;
873
874 IF (l_eSignature_status IN ('S','P') ) THEN
875 x_return_status := l_eSignature_status;
876 -- Commit your changes
877 Commit;
878
879 IF (x_return_status = 'P') THEN
880 fnd_message.set_name('GMD','GMD_CONC_PEND_STATUS');
881
882 fnd_message.set_token('OBJECT_TYPE',l_entity_name );
883 fnd_message.set_token('OBJECT_NAME',l_entity_no );
884 fnd_message.set_token('OBJECT_VERSION',l_entity_version);
885 fnd_message.set_token('FROM_STATUS',get_status_meaning(l_from_status) );
886 fnd_message.set_token('TO_STATUS',get_status_meaning(P_to_status) );
887 fnd_message.set_token('PENDING_STATUS',get_status_meaning(l_pending_status) );
888 END IF;
889
890 ELSE
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 RAISE status_update_failure;
893 END IF;
894
895 END IF; --IF (l_from_status <> P_to_status)
896 END IF;
897
898 fnd_msg_pub.count_and_get (
899 p_count => x_message_count
900 ,p_encoded => FND_API.g_false
901 ,p_data => x_message_list);
902
903 IF (l_debug = 'Y') THEN
904 gmd_debug.put_line('Status was updated successfullly');
905 END IF;
906
907 IF (l_debug = 'Y') THEN
908 gmd_debug.put_line('Completed '||l_api_name ||' at '
909 ||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
910 END IF;
911
912 EXCEPTION
913 WHEN status_update_failure OR invalid_version THEN
914 ROLLBACK TO SAVEPOINT modify_status;
915 IF (l_debug = 'Y') THEN
916 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
917 END IF;
918 fnd_msg_pub.count_and_get (
919 p_count => x_message_count
920 ,p_encoded => FND_API.g_false
921 ,p_data => x_message_list);
922 x_return_status := FND_API.G_RET_STS_ERROR;
923 WHEN setup_failure THEN
924 ROLLBACK TO SAVEPOINT modify_status;
925 x_return_status := FND_API.G_RET_STS_ERROR;
926 fnd_msg_pub.count_and_get (
927 p_count => x_message_count
928 ,p_encoded => FND_API.g_false
929 ,p_data => x_message_list);
930 WHEN app_exception.record_lock_exception THEN
931 ROLLBACK TO SAVEPOINT modify_status;
932 x_return_status := FND_API.G_RET_STS_ERROR;
933
934 IF (l_debug = 'Y') THEN
935 gmd_debug.put_line ('In locked exception section ');
936 END IF;
937 SELECT DECODE(P_entity_name,
938 'FORMULA','FM_FORM_MST_B',
939 'RECIPE','GMD_RECIPES_B',
940 'OPERATION','GMD_OPERATIONS_B',
941 'ROUTING','GMD_ROUTINGS_B',
942 'VALIDITY','GMD_RECIPE_VALIDITY_RULES') INTO l_table_name
943 FROM sys.dual;
944 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
945 'TABLE_NAME',l_table_name,
946 'KEY',NVL(p_entity_id, l_entity_id)
947 );
948 -- Bug #3437582 (JKB) Changed gmd_api_pub to gmd_api_grp above.
949 fnd_msg_pub.count_and_get (
950 p_count => x_message_count
951 ,p_encoded => FND_API.g_false
952 ,p_data => x_message_list);
953 WHEN OTHERS THEN
954 ROLLBACK TO SAVEPOINT modify_status;
955 fnd_msg_pub.add_exc_msg (gmd_status_pub.m_pkg_name, l_api_name);
956 IF (l_debug = 'Y') THEN
957 gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'When others exception:'||SQLERRM);
958 END IF;
959 fnd_msg_pub.count_and_get (
960 p_count => x_message_count
961 ,p_encoded => FND_API.g_false
962 ,p_data => x_message_list);
963 x_return_status := FND_API.g_ret_sts_unexp_error;
964 END Modify_status;
965
966 END GMD_STATUS_PUB;
[Home] [Help]