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