[Home] [Help]
PACKAGE BODY: APPS.GMD_ERES_UTILS
Source
1 PACKAGE BODY GMD_ERES_UTILS AS
2 /* $Header: GMDERESB.pls 120.8.12000000.2 2007/02/13 12:17:16 kmotupal ship $ */
3
4
5 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
6 --Forward declaration.
7 FUNCTION set_debug_flag RETURN VARCHAR2;
8 l_debug VARCHAR2(1) := set_debug_flag;
9
10 FUNCTION set_debug_flag RETURN VARCHAR2 IS
11 l_debug VARCHAR2(1):= 'N';
12 BEGIN
13 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
14 l_debug := 'Y';
15 END IF;
16 RETURN l_debug;
17 END set_debug_flag;
18 --Bug 3222090, NSRIVAST 20-FEB-2004, END
19
20 /*======================================================================
21 -- PROCEDURE :
22 -- get_operation_no
23 --
24 -- DESCRIPTION:
25 -- This PL/SQL procedure is responsible for getting the
26 -- operation no for a given operation id.
27 --
28 -- REQUIREMENTS
29 --
30 -- SYNOPSIS:
31 -- get_operation_no (100, p_oprn_no);
32 --
33 --===================================================================== */
34 PROCEDURE get_operation_no(P_oprn_id IN NUMBER, P_oprn_no OUT NOCOPY VARCHAR2) IS
35 CURSOR Cur_get_operation IS
36 SELECT oprn_no
37 FROM gmd_operations
38 WHERE oprn_id = P_oprn_id;
39 BEGIN
40 OPEN Cur_get_operation;
41 FETCH Cur_get_operation INTO P_oprn_no;
42 CLOSE Cur_get_operation;
43 END get_operation_no;
44
45 /*======================================================================
46 -- PROCEDURE :
47 -- get_operation_vers
48 --
49 -- DESCRIPTION:
50 -- This PL/SQL procedure is responsible for getting the
51 -- operation version for a given operation id.
52 --
53 -- REQUIREMENTS
54 --
55 -- SYNOPSIS:
56 -- get_operation_no (100, p_oprn_vers);
57 --
58 --===================================================================== */
59 PROCEDURE get_operation_vers(P_oprn_id IN NUMBER,P_oprn_vers OUT NOCOPY NUMBER) IS
60 CURSOR Cur_get_operation IS
61 SELECT oprn_vers
62 FROM gmd_operations
63 WHERE oprn_id = P_oprn_id;
64 BEGIN
65 OPEN Cur_get_operation;
66 FETCH Cur_get_operation INTO P_oprn_vers;
67 CLOSE Cur_get_operation;
68 END get_operation_vers;
69
70 /*======================================================================
71 -- PROCEDURE :
72 -- get_formula_no
73 --
74 -- DESCRIPTION:
75 -- This PL/SQL procedure is responsible for getting the
76 -- formula no for a given formula id.
77 --
78 -- REQUIREMENTS
79 --
80 -- SYNOPSIS:
81 -- get_formula_no (100, p_formula_no);
82 --
83 --===================================================================== */
84 PROCEDURE get_formula_no(P_formula_id IN NUMBER, P_formula_no OUT NOCOPY VARCHAR2) IS
85 CURSOR Cur_get_formula IS
86 SELECT formula_no
87 FROM fm_form_mst_b
88 WHERE formula_id = P_formula_id;
89 BEGIN
90 OPEN Cur_get_formula;
91 FETCH Cur_get_formula INTO P_formula_no;
92 CLOSE Cur_get_formula;
93 END get_formula_no;
94
95 /*======================================================================
96 -- PROCEDURE :
97 -- get_formula_vers
98 --
99 -- DESCRIPTION:
100 -- This PL/SQL procedure is responsible for getting the
101 -- formula version for a given formula id.
102 --
103 -- REQUIREMENTS
104 --
105 -- SYNOPSIS:
106 -- get_formula_vers (100, p_formula_vers);
107 --
108 --===================================================================== */
109 PROCEDURE get_formula_vers(P_formula_id IN NUMBER,P_formula_vers OUT NOCOPY NUMBER) IS
110 CURSOR Cur_get_formula IS
111 SELECT formula_vers
112 FROM fm_form_mst_b
113 WHERE formula_id = P_formula_id;
114 BEGIN
115 OPEN Cur_get_formula;
116 FETCH Cur_get_formula INTO P_formula_vers;
117 CLOSE Cur_get_formula;
118 END get_formula_vers;
119
120
121 /*======================================================================
122 -- PROCEDURE :
123 -- get_formula_desc
124 --
125 -- DESCRIPTION:
126 -- This PL/SQL procedure is responsible for getting the
127 -- formula desc for a given formula id.
128 --
129 -- REQUIREMENTS
130 --
131 -- SYNOPSIS:
132 -- get_formula_desc (100, p_formula_desc);
133 --
134 --===================================================================== */
135 PROCEDURE get_formula_desc(P_formula_id IN NUMBER, P_formula_desc OUT NOCOPY VARCHAR2) IS
136 CURSOR Cur_get_formula_desc IS
137 SELECT formula_desc1
138 FROM fm_form_mst
139 WHERE formula_id = P_formula_id;
140 BEGIN
141 OPEN Cur_get_formula_desc;
142 FETCH Cur_get_formula_desc INTO P_formula_desc;
143 CLOSE Cur_get_formula_desc;
144 END get_formula_desc;
145
146 /*======================================================================
147 -- PROCEDURE :
148 -- get_recipe_no
149 --
150 -- DESCRIPTION:
151 -- This PL/SQL procedure is responsible for getting the
152 -- recipe no for a given recipe id.
153 --
154 -- REQUIREMENTS
155 --
156 -- SYNOPSIS:
157 -- get_recipe_no (100, p_recipe_no);
158 --
159 --===================================================================== */
160 PROCEDURE get_recipe_no(P_recipe_id IN NUMBER, P_recipe_no OUT NOCOPY VARCHAR2) IS
161 CURSOR Cur_get_recipe IS
162 SELECT recipe_no
163 FROM gmd_recipes_b
164 WHERE recipe_id = P_recipe_id;
165 BEGIN
166 OPEN Cur_get_recipe;
167 FETCH Cur_get_recipe INTO P_recipe_no;
168 CLOSE Cur_get_recipe;
169 END get_recipe_no;
170
171 /*======================================================================
172 -- PROCEDURE :
173 -- get_recipe_version
174 --
175 -- DESCRIPTION:
176 -- This PL/SQL procedure is responsible for getting the
177 -- recipe version for a given recipe id.
178 --
179 -- REQUIREMENTS
180 --
181 -- SYNOPSIS:
182 -- get_recipe_version (100, p_recipe_version);
183 --
184 --===================================================================== */
185 PROCEDURE get_recipe_version (P_recipe_id IN NUMBER,P_recipe_version OUT NOCOPY NUMBER) IS
186 CURSOR Cur_get_recipe IS
187 SELECT recipe_version
188 FROM gmd_recipes_b
189 WHERE recipe_id = P_recipe_id;
190 BEGIN
191 OPEN Cur_get_recipe;
192 FETCH Cur_get_recipe INTO P_recipe_version;
193 CLOSE Cur_get_recipe;
194 END get_recipe_version;
195
196 /*======================================================================
197 -- PROCEDURE :
198 -- get_routing_no
199 --
200 -- DESCRIPTION:
201 -- This PL/SQL procedure is responsible for getting the
202 -- routing no for a given routing id.
203 --
204 -- REQUIREMENTS
205 --
206 -- SYNOPSIS:
207 -- get_routing_no (100, p_routing_no);
208 --
209 --===================================================================== */
210 PROCEDURE get_routing_no(P_routing_id IN NUMBER, P_routing_no OUT NOCOPY VARCHAR2) IS
211 CURSOR Cur_get_routing IS
212 SELECT routing_no
213 FROM gmd_routings_b
214 WHERE routing_id = P_routing_id;
215 BEGIN
216 OPEN Cur_get_routing;
217 FETCH Cur_get_routing INTO P_routing_no;
218 CLOSE Cur_get_routing;
219 END get_routing_no;
220
221 /*======================================================================
222 -- PROCEDURE :
223 -- get_routing_vers
224 --
225 -- DESCRIPTION:
226 -- This PL/SQL procedure is responsible for getting the
227 -- routing version for a given routing id.
228 --
229 -- REQUIREMENTS
230 --
231 -- SYNOPSIS:
232 -- get_routing_vers (100, p_routing_vers);
233 --
234 --===================================================================== */
235 PROCEDURE get_routing_vers (P_routing_id IN NUMBER,P_routing_vers OUT NOCOPY NUMBER) IS
236 CURSOR Cur_get_routing IS
237 SELECT routing_vers
238 FROM gmd_routings_b
239 WHERE routing_id = P_routing_id;
240 BEGIN
241 OPEN Cur_get_routing;
242 FETCH Cur_get_routing INTO P_routing_vers;
243 CLOSE Cur_get_routing;
244 END get_routing_vers;
245
246
247 /*======================================================================
248 -- PROCEDURE :
249 -- get_line_type_desc
250 --
251 -- DESCRIPTION:
252 -- This PL/SQL procedure is responsible for getting the
253 -- description of the line type for a given formula line id
254 --
255 -- REQUIREMENTS
256 --
257 -- SYNOPSIS:
258 -- get_line_type_desc (100, p_line_type_desc);
259 --
260 --===================================================================== */
261 PROCEDURE get_line_type_desc (P_formulaline_id IN NUMBER, P_line_type_desc OUT NOCOPY VARCHAR2) IS
262 CURSOR Cur_get_meaning IS
263 SELECT meaning
264 FROM gem_lookups g, fm_matl_dtl d
265 WHERE formulaline_id = P_formulaline_id
266 AND lookup_type = 'LINE_TYPE'
267 AND lookup_code = d.line_type;
268 BEGIN
269 OPEN Cur_get_meaning;
270 FETCH Cur_get_meaning INTO P_line_type_desc;
271 CLOSE Cur_get_meaning;
272 END get_line_type_desc;
273
274 /*======================================================================
275 -- PROCEDURE :
276 -- get_status_meaning
277 --
278 -- DESCRIPTION:
279 -- This PL/SQL procedure is responsible for getting the
280 -- meaning for a given status code.
281 --
282 -- REQUIREMENTS
283 --
284 -- SYNOPSIS:
285 -- get_status_meaning (100, p_meaning);
286 --
287 --===================================================================== */
288 PROCEDURE get_status_meaning(P_status_code IN NUMBER, P_meaning OUT NOCOPY VARCHAR2) IS
289 CURSOR Cur_get IS
290 SELECT meaning
291 FROM gmd_status
292 WHERE status_code = P_status_code;
293 BEGIN
294 OPEN Cur_get;
295 FETCH Cur_get INTO P_meaning;
296 CLOSE Cur_get;
297 END get_status_meaning;
298
299 /*======================================================================
300 -- PROCEDURE :
301 -- get_process_qty_um
302 --
303 -- DESCRIPTION:
304 -- This PL/SQL procedure is responsible for getting the
305 -- process qty uom for a given operation id.
306 --
307 -- REQUIREMENTS
308 --
309 -- SYNOPSIS:
310 -- get_process_qty_um (100, p_prc_qty_um);
311 -- Krishna 10-Feb-2005 Used new column PROCESS_QTY_UOM for the UOM code.
312 --===================================================================== */
313 PROCEDURE get_process_qty_um(P_oprn_id IN NUMBER,P_prc_qty_um OUT NOCOPY VARCHAR2) IS
314 CURSOR Cur_get_qty_um IS
315 SELECT process_qty_uom
316 FROM gmd_operations
317 WHERE oprn_id = P_oprn_id;
318 BEGIN
319 OPEN Cur_get_qty_um;
320 FETCH Cur_get_qty_um INTO P_prc_qty_um;
321 CLOSE Cur_get_qty_um;
322 END get_process_qty_um;
323
324 /*======================================================================
325 -- PROCEDURE :
326 -- get_activity_desc
327 --
328 -- DESCRIPTION:
329 -- This PL/SQL procedure is responsible for getting the
330 -- description for a given activity.
331 --
332 -- REQUIREMENTS
333 --
334 -- SYNOPSIS:
335 -- get_activity_desc ('SET-UP', p_activity_desc);
336 --
337 --===================================================================== */
338 PROCEDURE get_activity_desc(p_activity IN VARCHAR2, p_activity_desc OUT NOCOPY VARCHAR2) IS
339 CURSOR get_activity_desc IS
340 SELECT activity_desc
341 FROM fm_actv_mst
342 where activity = p_activity;
343 BEGIN
344 open get_activity_desc;
345 fetch get_activity_desc INTO p_activity_desc;
346 close get_activity_desc;
347 END get_activity_desc;
348
349 /*======================================================================
350 -- PROCEDURE :
351 -- get_resource_desc
352 --
353 -- DESCRIPTION:
354 -- This PL/SQL procedure is responsible for getting the
355 -- description for a given resource
356 --
357 -- REQUIREMENTS
358 --
359 -- SYNOPSIS:
360 -- get_resource_desc ('LABOUR', p_resource_desc);
361 --
362 --===================================================================== */
363 PROCEDURE get_resource_desc(p_resource IN VARCHAR2, p_resource_desc OUT NOCOPY VARCHAR2) IS
364 Cursor get_resource_desc IS
365 SELECT resource_desc
366 from cr_rsrc_mst
367 where resources = p_resource;
368 BEGIN
369 open get_resource_desc;
370 fetch get_resource_desc into p_resource_desc;
371 close get_resource_desc;
372 END get_resource_desc;
373
374 /*======================================================================
375 -- PROCEDURE :
376 -- get_proc_param_desc
377 --
378 -- DESCRIPTION:
379 -- This PL/SQL procedure is responsible for getting the
380 -- description for a given process parameter.
381 --
382 -- REQUIREMENTS
383 --
384 -- SYNOPSIS:
385 -- get_routing_no (100, x_parameter_desc);
386 --
387 --===================================================================== */
388 PROCEDURE get_proc_param_desc(p_parameter_id IN NUMBER, x_parameter_desc OUT NOCOPY VARCHAR2) IS
389 BEGIN
390 GMD_RECIPE_FETCH_PUB.get_proc_param_desc (p_parameter_id => p_parameter_id
391 ,x_parameter_desc => x_parameter_desc);
392 END get_proc_param_desc;
393
394 /*======================================================================
395 -- PROCEDURE :
396 -- get_proc_param_units
397 --
398 -- DESCRIPTION:
399 -- This PL/SQL procedure is responsible for getting the
400 -- units for a given process parameter.
401 --
402 -- REQUIREMENTS
403 --
404 -- SYNOPSIS:
405 -- get_proc_param_units (100, X_units);
406 --
407 --===================================================================== */
408 PROCEDURE get_proc_param_units(p_parameter_id IN NUMBER, x_units OUT NOCOPY VARCHAR2) IS
409 BEGIN
410 GMD_RECIPE_FETCH_PUB.get_proc_param_units (p_parameter_id => p_parameter_id
411 ,x_units => x_units);
412 END get_proc_param_units;
413
414 /*======================================================================
415 -- PROCEDURE :
416 -- set_formula_status
417 --
418 -- DESCRIPTION:
419 -- This PL/SQL procedure is responsible for setting the formula status
420 -- to a given status based on the status of the signature.
421 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
422 -- formula, also from the change status form after the event is raised.
423 -- REQUIREMENTS
424 --
425 -- SYNOPSIS:
426 -- set_formula_status (100, 400, 700, 'PENDING');
427 -- G.Kelly 07-May-04 B3604554 Added code in procedure set_formula_status for checking
428 -- for Recipe Generation Automatically
429 --===================================================================== */
430 PROCEDURE set_formula_status(p_formula_id IN NUMBER,
431 p_from_status IN VARCHAR2,
432 p_to_status IN VARCHAR2,
433 p_signature_status IN VARCHAR2) IS
434 l_pending_status gmd_status.status_code%TYPE;
435 l_rework_status gmd_status.status_code%TYPE;
436
437 /* Declare variables, cursors for recipe generation */
438 l_orgn_code VARCHAR2(4);
439 x_return_status VARCHAR2(1);
440 x_recipe_version NUMBER;
441 x_recipe_no VARCHAR2(32);
442
443
444 BEGIN
445 IF p_signature_status = 'SUCCESS' THEN
446 UPDATE fm_form_mst_b
447 SET formula_status = p_to_status
448 WHERE formula_id = p_formula_id;
449 ELSIF p_signature_status = 'PENDING' THEN
450 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
451 ,p_to_status => p_to_status);
452 IF l_pending_status IS NOT NULL THEN
453 UPDATE fm_form_mst_b
454 SET formula_status = l_pending_status
455 WHERE formula_id = p_formula_id;
456 END IF;
457 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
458 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
459 ,p_to_status => p_to_status);
460 IF l_rework_status IS NOT NULL THEN
461 UPDATE fm_form_mst_b
462 SET formula_status = l_rework_status
463 WHERE formula_id = p_formula_id;
464 END IF;
465 END IF;
466
467 END set_formula_status;
468
469 -- Bug number 4479101
470 PROCEDURE set_substitution_status (P_substitution_id IN NUMBER
471 ,p_from_status IN VARCHAR2
472 ,p_to_status IN VARCHAR2
473 ,p_signature_status IN VARCHAR2 DEFAULT NULL)AS
474 l_pending_status VARCHAR2(80);
475 l_rework_status VARCHAR2(80);
476 l_derive_end_date DATE;
477 BEGIN
478 IF p_signature_status = 'SUCCESS' THEN
479
480 UPDATE GMD_ITEM_SUBSTITUTION_HDR_B
481 SET substitution_status = p_to_status
482 WHERE substitution_id = p_substitution_id;
483 UPDATE GMD_FORMULA_SUBSTITUTION SET Associated_flag ='Y'
484 WHERE substitution_id = P_substitution_id;
485
486 GMD_API_GRP.update_end_date (p_substitution_id);
487
488 ELSIF p_signature_status = 'PENDING' THEN
489 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
490 ,p_to_status => p_to_status);
491 IF l_pending_status IS NOT NULL THEN
492 UPDATE GMD_ITEM_SUBSTITUTION_HDR_B
493 SET substitution_status = l_pending_status
494 WHERE substitution_id = p_substitution_id;
495 END IF;
496 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
497 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
498 ,p_to_status => p_to_status);
499 IF l_rework_status IS NOT NULL THEN
500 UPDATE GMD_ITEM_SUBSTITUTION_HDR_B
501 SET substitution_status = l_rework_status
502 WHERE substitution_id = p_substitution_id;
503 END IF;
504 END IF;
505 END set_substitution_status;
506
507 /*======================================================================
508 -- PROCEDURE :
509 -- set_formulation_spec_status
510 --
511 -- DESCRIPTION:
512 -- This PL/SQL procedure is responsible for setting the formulation sepc status
513 -- to a given status based on the status of the signature.
514 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
515 -- formulation spec, also from the change status form after the event is raised.
516 -- REQUIREMENTS
517 --
518 -- SYNOPSIS:
519 -- set_formulation_spec_status (100, 400, 700, 'PENDING');
520 --===================================================================== */
521 PROCEDURE set_formulation_spec_status(p_formulation_spec_id IN NUMBER,
522 p_from_status IN VARCHAR2,
523 p_to_status IN VARCHAR2,
524 p_signature_status IN VARCHAR2) IS
525 l_pending_status gmd_status.status_code%TYPE;
526 l_rework_status gmd_status.status_code%TYPE;
527 BEGIN
528
529 IF p_signature_status = 'SUCCESS' THEN
530 UPDATE gmd_formulation_specs
531 SET spec_status = p_to_status
532 WHERE formulation_spec_id = p_formulation_spec_id;
533 ELSIF p_signature_status = 'PENDING' THEN
534 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
535 ,p_to_status => p_to_status);
536 IF l_pending_status IS NOT NULL THEN
537 UPDATE gmd_formulation_specs
538 SET spec_status = l_pending_status
539 WHERE formulation_spec_id = p_formulation_spec_id;
540 END IF;
541 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
542 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
543 ,p_to_status => p_to_status);
544 IF l_rework_status IS NOT NULL THEN
545 UPDATE gmd_formulation_specs
546 SET spec_status = l_rework_status
547 WHERE formulation_spec_id = p_formulation_spec_id;
548 END IF;
549 END IF;
550 END set_formulation_spec_status;
551
552 /*======================================================================
553 -- PROCEDURE :
554 -- set_operation_status
555 --
556 -- DESCRIPTION:
557 -- This PL/SQL procedure is responsible for setting the operation status
558 -- to a given status based on the status of the signature.
559 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
560 -- operation, also from the change status form after the event is raised.
561 -- REQUIREMENTS
562 --
563 -- SYNOPSIS:
564 -- set_operation_status (100, 400, 700, 'PENDING');
565 --
566 --===================================================================== */
567 PROCEDURE set_operation_status(p_oprn_id IN NUMBER,
568 p_from_status IN VARCHAR2,
569 p_to_status IN VARCHAR2,
570 p_signature_status IN VARCHAR2) IS
571 l_pending_status gmd_status.status_code%TYPE;
572 l_rework_status gmd_status.status_code%TYPE;
573 BEGIN
574 IF p_signature_status = 'SUCCESS' THEN
575 UPDATE gmd_operations_b
576 SET operation_status = p_to_status
577 WHERE oprn_id = p_oprn_id;
578 ELSIF p_signature_status = 'PENDING' THEN
579 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
580 ,p_to_status => p_to_status);
581 IF l_pending_status IS NOT NULL THEN
582 UPDATE gmd_operations_b
583 SET operation_status = l_pending_status
584 WHERE oprn_id = p_oprn_id;
585 END IF;
586 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
587 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
588 ,p_to_status => p_to_status);
589 IF l_rework_status IS NOT NULL THEN
590 UPDATE gmd_operations_b
591 SET operation_status = l_rework_status
592 WHERE oprn_id = p_oprn_id;
593 END IF;
594 END IF;
595
596 END set_operation_status;
597
598 /*======================================================================
599 -- PROCEDURE :
600 -- set_routing_status
601 --
602 -- DESCRIPTION:
603 -- This PL/SQL procedure is responsible for setting the routing status
604 -- to a given status based on the status of the signature.
605 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
606 -- routing, also from the change status form after the event is raised.
607 -- REQUIREMENTS
608 --
609 -- SYNOPSIS:
610 -- set_routing_status (100, 400, 700, 'PENDING');
611 --
612 --===================================================================== */
613 PROCEDURE set_routing_status(p_routing_id IN NUMBER,
614 p_from_status IN VARCHAR2,
615 p_to_status IN VARCHAR2,
616 p_signature_status IN VARCHAR2) IS
617 l_pending_status gmd_status.status_code%TYPE;
618 l_rework_status gmd_status.status_code%TYPE;
619 BEGIN
620 IF p_signature_status = 'SUCCESS' THEN
621 UPDATE gmd_routings_b
622 SET routing_status = p_to_status
623 WHERE routing_id = p_routing_id;
624 ELSIF p_signature_status = 'PENDING' THEN
625 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
626 ,p_to_status => p_to_status);
627 IF l_pending_status IS NOT NULL THEN
628 UPDATE gmd_routings_b
629 SET routing_status = l_pending_status
630 WHERE routing_id = p_routing_id;
631 END IF;
632 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
633 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
634 ,p_to_status => p_to_status);
635 IF l_rework_status IS NOT NULL THEN
636 UPDATE gmd_routings_b
637 SET routing_status = l_rework_status
638 WHERE routing_id = p_routing_id;
639 END IF;
640 END IF;
641
642 END set_routing_status;
643
644 /*======================================================================
645 -- PROCEDURE :
646 -- set_recipe_status
647 --
648 -- DESCRIPTION:
649 -- This PL/SQL procedure is responsible for setting the recipe status
650 -- to a given status based on the status of the signature.
651 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
652 -- recipe, also from the change status form after the event is raised.
653 -- REQUIREMENTS
654 --
655 -- SYNOPSIS:
656 -- set_recipe_status (100, 400, 700, 'PENDING');
657 --
658 --===================================================================== */
659 PROCEDURE set_recipe_status(p_recipe_id IN NUMBER,
660 p_from_status IN VARCHAR2,
661 p_to_status IN VARCHAR2,
662 p_signature_status IN VARCHAR2,
663 p_create_validity IN NUMBER) IS
664 l_pending_status gmd_status.status_code%TYPE;
665 l_rework_status gmd_status.status_code%TYPE;
666
667 CURSOR Cur_get_formula_details IS
668 SELECT r.owner_organization_id, r.formula_id, r.recipe_no, r.recipe_version
669 FROM gmd_recipes_b r, fm_form_mst_b f
670 WHERE r.recipe_id = p_recipe_id
671 AND r.formula_id = f.formula_id;
672 LocalFormRecord Cur_get_formula_details%ROWTYPE;
673
674 CURSOR Cur_auto_recipe_enable (V_orgn_id NUMBER) IS
675 SELECT recipe_use_prod, recipe_use_plan, recipe_use_cost, recipe_use_reg, recipe_use_tech, managing_validity_rules
676 FROM gmd_recipe_generation
677 WHERE (organization_id = V_orgn_id
678 OR organization_id IS NULL)
679 ORDER BY orgn_code;
680 LocalEnableRecord Cur_auto_recipe_enable%ROWTYPE;
681 l_return_status VARCHAR2(1);
682 BEGIN
683 IF p_signature_status = 'SUCCESS' THEN
684 UPDATE gmd_recipes_b
685 SET recipe_status = p_to_status
686 WHERE recipe_id = p_recipe_id;
687
688 /* If validity rule has to be created based on the recipe approval and recipe generation setup */
689 IF p_create_validity = 1 THEN
690 OPEN Cur_get_formula_details;
691 FETCH Cur_get_formula_details INTO LocalFormRecord;
692 CLOSE Cur_get_formula_details;
693
694 OPEN Cur_auto_recipe_enable(LocalFormRecord.owner_organization_id);
695 FETCH Cur_auto_recipe_enable INTO LocalEnableRecord;
696 CLOSE Cur_auto_recipe_enable;
697
698 GMD_RECIPE_GENERATE.create_validity_rule_set(p_recipe_id => p_recipe_id,
699 p_recipe_no => LocalFormRecord.recipe_no,
700 p_recipe_version => LocalFormRecord.recipe_version,
701 p_formula_id => LocalFormRecord.formula_id,
702 p_orgn_id => LocalFormRecord.owner_organization_id,
703 p_recipe_use_prod => LocalEnableRecord.recipe_use_prod,
704 p_recipe_use_plan => LocalEnableRecord.recipe_use_plan,
705 p_recipe_use_cost => LocalEnableRecord.recipe_use_cost,
706 p_recipe_use_reg => LocalEnableRecord.recipe_use_reg,
707 p_recipe_use_tech => LocalEnableRecord.recipe_use_tech,
708 p_manage_validity_rules => LocalEnableRecord.managing_validity_rules,
709 p_event_signed => FALSE,
710 x_return_status => l_return_status);
711 END IF; /* IF p_create_validity = 1 */
712 ELSIF p_signature_status = 'PENDING' THEN
713 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
714 ,p_to_status => p_to_status);
715 IF l_pending_status IS NOT NULL THEN
716 UPDATE gmd_recipes_b
717 SET recipe_status = l_pending_status
718 WHERE recipe_id = p_recipe_id;
719 END IF;
720 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
721 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
722 ,p_to_status => p_to_status);
723 IF l_rework_status IS NOT NULL THEN
724 UPDATE gmd_recipes_b
725 SET recipe_status = l_rework_status
726 WHERE recipe_id = p_recipe_id;
727 END IF;
728 END IF;
729
730 END set_recipe_status;
731
732 /*======================================================================
733 -- PROCEDURE :
734 -- set_validity_status
735 --
736 -- DESCRIPTION:
737 -- This PL/SQL procedure is responsible for setting the validity status
738 -- to a given status based on the status of the signature.
739 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
740 -- recipe validity rule, also from the change status form after the event is raised.
741 -- REQUIREMENTS
742 --
743 -- SYNOPSIS:
744 -- set_validity_status (100, 400, 700, 'PENDING');
745 --
746 --===================================================================== */
747 PROCEDURE set_validity_status(p_validity_rule_id IN NUMBER,
748 p_from_status IN VARCHAR2,
749 p_to_status IN VARCHAR2,
750 p_signature_status IN VARCHAR2) IS
751 l_pending_status gmd_status.status_code%TYPE;
752 l_rework_status gmd_status.status_code%TYPE;
753 BEGIN
754 IF p_signature_status = 'SUCCESS' THEN
755 UPDATE gmd_recipe_validity_rules
756 SET validity_rule_status = p_to_status
757 WHERE recipe_validity_rule_id = p_validity_rule_id;
758 ELSIF p_signature_status = 'PENDING' THEN
759 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
760 ,p_to_status => p_to_status);
761 IF l_pending_status IS NOT NULL THEN
762 UPDATE gmd_recipe_validity_rules
763 SET validity_rule_status = l_pending_status
764 WHERE recipe_validity_rule_id = p_validity_rule_id;
765 END IF;
766 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
767 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
768 ,p_to_status => p_to_status);
769 IF l_rework_status IS NOT NULL THEN
770 UPDATE gmd_recipe_validity_rules
771 SET validity_rule_status = l_rework_status
772 WHERE recipe_validity_rule_id = p_validity_rule_id;
773 END IF;
774 END IF;
775
776 END set_validity_status;
777
778 /*======================================================================
779 -- PROCEDURE :
780 -- set_auto_recipe_status
781 --
782 -- DESCRIPTION:
783 -- This PL/SQL procedure is responsible for setting the formula status
784 -- to a given status based on the status of the signature.
785 -- (This is called from GMD_ERES_POST_OPERATION API for the ERES approval of a
786 -- formula, also from the change status form after the event is raised.
787 -- REQUIREMENTS
788 --
789 -- SYNOPSIS:
790 -- set_formula_status (100, 400, 700, 'PENDING');
791 -- kkillams 01-DEC-2004 set_auto_recipe_status procedure signature is changed w.r.t. 4004501
792 --===================================================================== */
793 PROCEDURE set_auto_recipe_status(p_formula_id IN NUMBER,
794 p_orgn_id IN NUMBER,
795 p_from_status IN VARCHAR2,
796 p_to_status IN VARCHAR2,
797 p_signature_status IN VARCHAR2) IS
798 l_pending_status gmd_status.status_code%TYPE;
799 l_rework_status gmd_status.status_code%TYPE;
800 l_return_status VARCHAR2(1);
801 l_recipe_no VARCHAR2(100);
802 l_recipe_version NUMBER(5);
803 BEGIN
804 IF p_signature_status = 'SUCCESS' THEN
805 UPDATE fm_form_mst_b
806 SET formula_status = p_to_status
807 WHERE formula_id = p_formula_id;
808 gmd_recipe_generate.recipe_generate (p_orgn_id => p_orgn_id
809 ,p_formula_id => p_formula_id
810 ,X_return_status => l_return_status
811 ,X_recipe_no => l_recipe_no
812 ,X_recipe_version => l_recipe_version
813 ,p_event_signed => TRUE);
814 ELSIF p_signature_status = 'PENDING' THEN
815 l_pending_status := GMD_STATUS_CODE.get_pending_status(p_from_status => p_from_status
816 ,p_to_status => p_to_status);
817 IF l_pending_status IS NOT NULL THEN
818 UPDATE fm_form_mst_b
819 SET formula_status = l_pending_status
820 WHERE formula_id = p_formula_id;
821 END IF;
822 ELSIF p_signature_status IN ('REJECTED','TIMEDOUT') THEN
823 l_rework_status := GMD_STATUS_CODE.get_rework_status(p_from_status => p_from_status
824 ,p_to_status => p_to_status);
825 IF l_rework_status IS NOT NULL THEN
826 UPDATE fm_form_mst_b
827 SET formula_status = l_rework_status
828 WHERE formula_id = p_formula_id;
829 END IF;
830 END IF;
831 END set_auto_recipe_status;
832
833 /*======================================================================
834 -- PROCEDURE :
835 -- check_recipe_validity_eres
836 --
837 -- DESCRIPTION:
838 -- This PL/SQL procedure is responsible for checking if approvals
839 -- are required for the validity rules associated with a recipe, to be
840 -- moved to a particular status.
841 -- REQUIREMENTS
842 --
843 -- SYNOPSIS:
844 -- check_recipe_validity_eres (100, 400);
845 --
846 --===================================================================== */
847 FUNCTION check_recipe_validity_eres (p_recipe_id IN NUMBER,
848 p_to_status IN VARCHAR2)
849 RETURN BOOLEAN IS
850 CURSOR Cur_get_validity IS
851 SELECT recipe_validity_rule_id
852 FROM gmd_recipe_validity_rules
853 WHERE recipe_id = p_recipe_id
854 AND validity_rule_status < p_to_status;
855 l_recipe_validity_rule_id NUMBER;
856 l_status BOOLEAN;
857 BEGIN
858 Savepoint check_vr_required;
859 OPEN Cur_get_validity;
860 FETCH Cur_get_validity INTO l_recipe_validity_rule_id;
861 WHILE Cur_get_validity%FOUND LOOP
862 UPDATE gmd_recipe_validity_rules
863 SET validity_rule_status = p_to_status
864 WHERE recipe_validity_rule_id = l_recipe_validity_rule_id;
865 GMA_STANDARD.psig_required (p_event => 'oracle.apps.gmd.validity.sts'
866 ,p_event_key => l_recipe_validity_rule_id
867 ,p_status => l_status);
868 IF l_status THEN
869 ROLLBACK to Savepoint check_vr_required;
870 CLOSE Cur_get_validity;
871 RETURN TRUE;
872 END IF;
873 FETCH Cur_get_validity INTO l_recipe_validity_rule_id;
874 END LOOP;
875 ROLLBACK to Savepoint check_vr_required;
876 CLOSE Cur_get_validity;
877 RETURN FALSE;
878 EXCEPTION
879 WHEN OTHERS THEN
880 ROLLBACK to Savepoint check_vr_required;
881 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
882 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
883 FND_MSG_PUB.ADD;
884 RETURN TRUE;
885 END check_recipe_validity_eres;
886
887 /*======================================================================
888 -- PROCEDURE :
889 -- esig_required
890 --
891 -- DESCRIPTION:
892 -- This PL/SQL procedure is responsible for returning back to the
893 -- calling routine if esignatures are enabled for moving to a particular
894 -- status.
895 -- REQUIREMENTS
896 --
897 -- SYNOPSIS:
898 -- esig_required ('oracle.apps.gmd.operation.sts', 100, 400);
899 --
900 --===================================================================== */
901 FUNCTION esig_required (p_event IN VARCHAR2,
902 p_event_key IN VARCHAR2,
903 p_to_status IN VARCHAR2)
904 RETURN BOOLEAN IS
905 l_status BOOLEAN;
906 BEGIN
907 GMA_STANDARD.psig_required (p_event => p_event
908 ,p_event_key => p_event_key
909 ,p_status => l_status);
910 IF l_status THEN
911 RETURN TRUE;
912 ELSE
913 RETURN FALSE;
914 END IF;
915 EXCEPTION
916 WHEN OTHERS THEN
917 RAISE;
918 END esig_required;
919
920 /*###############################################################
921 # NAME
922 # update_formula_status
923 # SYNOPSIS
924 # update_formula_status
925 # DESCRIPTION
926 # Performs update of the formula status and the raise of event
927 ###############################################################*/
928
929 PROCEDURE update_formula_status ( p_formula_id IN VARCHAR2,
930 p_from_status IN VARCHAR2,
931 p_to_status IN VARCHAR2,
932 p_pending_status IN VARCHAR2,
933 p_rework_status IN VARCHAR2,
934 p_object_name IN VARCHAR2,
935 p_object_version IN NUMBER,
936 p_called_from_form IN VARCHAR2,
937 x_return_status OUT NOCOPY VARCHAR2) IS
938 CURSOR Cur_get_desc IS
939 SELECT meaning
940 FROM gem_lookups
941 WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
942 AND lookup_code = 'FORMULA';
943
944 CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
945 SELECT description
946 FROM gmd_status
947 WHERE status_code = pstatus;
948
949 l_replace_type_desc VARCHAR2(240);
950 l_user_key_label VARCHAR2(2000);
951 l_object_type VARCHAR2(240);
952 l_from_status_desc VARCHAR2(240);
953 l_to_status_desc VARCHAR2(240);
954 l_version_lbl VARCHAR2(2000);
955 l_text VARCHAR2(4000);
956 l_user_id NUMBER := FND_GLOBAL.USER_ID;
957 l_status VARCHAR2(2000);
958 l_esig_reqd BOOLEAN;
959 l_erec_reqd BOOLEAN;
960
961 PENDING_STATUS_ERR EXCEPTION;
962 REWORK_STATUS_ERR EXCEPTION;
963
964 --Sriram.S Bug# 3497522 31-MAR-2004
965 --Declared a new exception to be raised for Update failure due to View access restriction
966 STATUS_UPDATE_FAILURE EXCEPTION;
967 --Local variables
968 l_access_type_ind VARCHAR2(10);
969 l_owner_organization_id NUMBER(15);
970 --New cursor to get the organization to which the formula belongs.
971 CURSOR get_orgn_code( CP_FORMULA_ID FM_FORM_MST_B.FORMULA_ID%TYPE) IS
972 SELECT OWNER_ORGANIZATION_ID
973 FROM FM_FORM_MST_B
974 WHERE FORMULA_ID = CP_FORMULA_ID;
975 --End of Declaration for Bug# 3497522
976
977 BEGIN
978 SAVEPOINT update_formula;
979 X_return_status := FND_API.g_ret_sts_success;
980
981 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORMULA');
982 l_object_type := FND_MESSAGE.GET;
983
984 SELECT 'x'
985 INTO l_text
986 FROM fm_form_mst
987 WHERE formula_id = p_formula_id
988 FOR UPDATE OF formula_status nowait;
989
990 UPDATE fm_form_mst
991 SET formula_status = p_to_status,
992 last_update_date = sysdate,
993 last_updated_by = l_user_id
994 WHERE formula_id = p_formula_id;
995
996 --Sriram.S Bug# 3497522 31-MAR-2004
997 --Checked if Update of Status is not performed because of View Restriction
998 IF (SQL%ROWCOUNT = 0) THEN
999
1000 OPEN get_orgn_code(p_formula_id);
1001 FETCH get_orgn_code INTO l_owner_organization_id;
1002 CLOSE get_orgn_code;
1003 --Get the access type of the formula
1004 l_access_type_ind := GMD_API_GRP.GET_FORMULA_ACCESS_TYPE(p_formula_id => p_formula_id,
1005 p_owner_organization_id => l_owner_organization_id);
1006 IF (l_access_type_ind ='V') THEN
1007 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORM_UPD_NO_ACCESS');
1008 FND_MSG_PUB.ADD;
1009 RAISE STATUS_UPDATE_FAILURE;
1010 END IF;
1011
1012 END IF;
1013 -- End of Bug# 3497522
1014
1015 GMA_STANDARD.PSIG_REQUIRED (p_event => 'oracle.apps.gmd.formula.sts'
1016 ,p_event_key => p_formula_id
1017 ,p_status => l_esig_reqd);
1018
1019 GMA_STANDARD.EREC_REQUIRED (p_event => 'oracle.apps.gmd.formula.sts'
1020 ,p_event_key => p_formula_id
1021 ,p_status => l_erec_reqd);
1022
1023 IF (l_esig_reqd OR l_erec_reqd) THEN
1024
1025 IF (l_esig_reqd) THEN
1026 OPEN Cur_get_status_desc (p_from_status);
1027 FETCH Cur_get_status_desc INTO l_from_status_desc;
1028 CLOSE Cur_get_status_desc;
1029
1030 OPEN Cur_get_status_desc (p_to_status);
1031 FETCH Cur_get_status_desc INTO l_to_status_desc;
1032 CLOSE Cur_get_status_desc;
1033
1034 IF p_pending_status IS NULL THEN
1035 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_PEND_STAT_REQD');
1036 RAISE PENDING_STATUS_ERR;
1037 END IF;
1038 IF p_rework_status IS NULL THEN
1039 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_REWK_STAT_REQD');
1040 RAISE REWORK_STATUS_ERR;
1041 END IF;
1042 END IF; /* IF (l_esig_reqd) */
1043
1044 END IF; /* IF (l_esig_reqd OR l_erec_reqd) */
1045
1046 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_FORM_USR_LBL');
1047 l_user_key_label := FND_MESSAGE.GET;
1048 GMD_EDR_STANDARD.raise_event (p_event_name => 'oracle.apps.gmd.formula.sts'
1049 ,p_event_key => p_formula_id
1050 ,p_parameter_name1 => 'DEFERRED'
1051 ,p_parameter_value1 => 'Y'
1052 ,p_parameter_name2 => 'POST_OPERATION_API'
1053 ,p_parameter_value2 =>'GMD_ERES_POST_OPERATION.set_formula_status('||
1054 p_formula_id||', '||
1055 p_from_status||', '||
1056 p_to_status||');'
1057 ,p_parameter_name3 => 'PSIG_USER_KEY_LABEL'
1058 ,p_parameter_value3 =>l_user_key_label
1059 ,p_parameter_name4 => 'PSIG_USER_KEY_VALUE'
1060 ,p_parameter_value4 => p_object_name||', '||p_object_version
1061 ,p_parameter_name5 => '#WF_SOURCE_APPLICATION_TYPE'
1062 ,p_parameter_value5 =>'DB'
1063 ,p_parameter_name6 => '#WF_SIGN_REQUESTER'
1064 ,p_parameter_value6 =>FND_GLOBAL.user_name
1065 ,p_parameter_name7 =>'PSIG_TRANSACTION_AUDIT_ID'
1066 ,p_parameter_value7=>-1);
1067
1068 IF l_esig_reqd THEN
1069 X_return_status := 'P';
1070 UPDATE fm_form_mst
1071 SET formula_status = p_pending_status
1072 WHERE formula_id = p_formula_id;
1073 IF p_called_from_form = 'F' THEN
1074 FND_MESSAGE.SET_NAME('GMD','GMD_ERES_PEND_STAT_UPD');
1075 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_to_status_desc);
1076 FND_FILE.PUT(FND_FILE.LOG,
1077 FND_MESSAGE.GET||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1078 ||' :'||p_object_version||')');
1079 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1080 END IF;
1081 END IF; /* IF l_esig_reqd */
1082
1083 EXCEPTION
1084 WHEN PENDING_STATUS_ERR OR
1085 REWORK_STATUS_ERR THEN
1086 ROLLBACK TO SAVEPOINT update_formula;
1087 X_return_status := FND_API.g_ret_sts_error;
1088 FND_MESSAGE.SET_TOKEN('FROM_STATUS', SUBSTR(l_from_status_desc,1, 80));
1089 FND_MESSAGE.SET_TOKEN('TO_STATUS', SUBSTR(l_to_status_desc, 1, 80));
1090 l_text := FND_MESSAGE.GET;
1091 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_VERSION');
1092 l_version_lbl := FND_MESSAGE.GET;
1093 l_text := l_text||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1094 ||' :'||p_object_version||')';
1095 IF p_called_from_form = 'F' THEN
1096 FND_FILE.PUT(FND_FILE.LOG, l_text);
1097 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1098 ELSE
1099 FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
1100 FND_MESSAGE.SET_TOKEN('MESSAGE', l_text);
1101 FND_MSG_PUB.add;
1102 END IF;
1103 WHEN app_exception.record_lock_exception THEN
1104 ROLLBACK TO SAVEPOINT update_formula;
1105 X_return_status := FND_API.g_ret_sts_error;
1106 IF (l_debug = 'Y') THEN
1107 gmd_debug.put_line ('In GMDERESB.pls - locked exception section ');
1108 END IF;
1109 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
1110 'TABLE_NAME',
1111 'FM_FORM_MST_B',
1112 'RECORD',
1113 'FORMULA_NO : FORMULA_VERS = ',
1114 'KEY',
1115 p_object_name||':'||p_object_version
1116 );
1117
1118 --Sriram.S Bug# 3497522 31-MAR-2004
1119 --Added this Exception for Update failure due to View access
1120 WHEN STATUS_UPDATE_FAILURE THEN
1121 ROLLBACK TO SAVEPOINT update_formula;
1122 X_return_status := FND_API.g_ret_sts_error;
1123 IF (l_debug = 'Y') THEN
1124 gmd_debug.put_line ('In GMDERESB.pls - Status update failure section');
1125 END IF;
1126
1127 WHEN OTHERS THEN
1128 ROLLBACK TO SAVEPOINT update_formula;
1129 X_return_status := FND_API.g_ret_sts_unexp_error;
1130 OPEN Cur_get_desc;
1131 FETCH Cur_get_desc INTO l_replace_type_desc;
1132 CLOSE Cur_get_desc;
1133
1134 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS');
1135 l_status := FND_MESSAGE.GET;
1136
1137 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1138 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',l_replace_type_desc);
1139 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',l_status);
1140 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',p_object_name);
1141 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',l_object_type);
1142 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',p_object_version);
1143 FND_MESSAGE.SET_TOKEN('ERRMSG',SQLERRM);
1144 IF p_called_from_form = 'F' THEN
1145 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1146 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1147 ELSE
1148 FND_MSG_PUB.add;
1149 END IF;
1150 END update_formula_status;
1151
1152
1153 /*###############################################################
1154 # NAME
1155 # update_operation_status
1156 # SYNOPSIS
1157 # update_operation_status
1158 # DESCRIPTION
1159 # Performs update of the operation status and the raise of event
1160 ###############################################################*/
1161
1162 PROCEDURE update_operation_status(p_oprn_id IN VARCHAR2,
1163 p_from_status IN VARCHAR2,
1164 p_to_status IN VARCHAR2,
1165 p_pending_status IN VARCHAR2,
1166 p_rework_status IN VARCHAR2,
1167 p_object_name IN VARCHAR2,
1168 p_object_version IN NUMBER,
1169 p_called_from_form IN VARCHAR2,
1170 x_return_status OUT NOCOPY VARCHAR2) IS
1171 CURSOR Cur_get_desc IS
1172 SELECT meaning
1173 FROM gem_lookups
1174 WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
1175 AND lookup_code = 'OPERATION';
1176
1177 CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
1178 SELECT description
1179 FROM gmd_status
1180 WHERE status_code = pstatus;
1181
1182 l_replace_type_desc VARCHAR2(240);
1183 l_user_key_label VARCHAR2(2000);
1184 l_object_type VARCHAR2(240);
1185 l_from_status_desc VARCHAR2(240);
1186 l_to_status_desc VARCHAR2(240);
1187 l_version_lbl VARCHAR2(2000);
1188 l_text VARCHAR2(4000);
1189 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1190 l_status VARCHAR2(2000);
1191 l_esig_reqd BOOLEAN;
1192 l_erec_reqd BOOLEAN;
1193
1194 PENDING_STATUS_ERR EXCEPTION;
1195 REWORK_STATUS_ERR EXCEPTION;
1196 BEGIN
1197 SAVEPOINT update_operation;
1198 X_return_status := FND_API.g_ret_sts_success;
1199
1200 FND_MESSAGE.SET_NAME('GMD', 'GMD_OPERATION');
1201 l_object_type := FND_MESSAGE.GET;
1202
1203 SELECT 'x'
1204 INTO l_text
1205 FROM gmd_operations_b
1206 WHERE oprn_id = p_oprn_id
1207 FOR UPDATE OF operation_status nowait;
1208
1209 UPDATE gmd_operations_b
1210 SET operation_status = p_to_status,
1211 last_update_date = sysdate,
1212 last_updated_by = l_user_id
1213 WHERE oprn_id = p_oprn_id;
1214
1215 GMA_STANDARD.PSIG_REQUIRED (p_event => 'oracle.apps.gmd.operation.sts'
1216 ,p_event_key => p_oprn_id
1217 ,p_status => l_esig_reqd);
1218
1219 GMA_STANDARD.EREC_REQUIRED (p_event => 'oracle.apps.gmd.operation.sts'
1220 ,p_event_key => p_oprn_id
1221 ,p_status => l_erec_reqd);
1222
1223 IF (l_esig_reqd OR l_erec_reqd) THEN
1224
1225 IF (l_esig_reqd) THEN
1226 OPEN Cur_get_status_desc (p_from_status);
1227 FETCH Cur_get_status_desc INTO l_from_status_desc;
1228 CLOSE Cur_get_status_desc;
1229
1230 OPEN Cur_get_status_desc (p_to_status);
1231 FETCH Cur_get_status_desc INTO l_to_status_desc;
1232 CLOSE Cur_get_status_desc;
1233
1234 IF p_pending_status IS NULL THEN
1235 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_PEND_STAT_REQD');
1236 RAISE PENDING_STATUS_ERR;
1237 END IF;
1238 IF p_rework_status IS NULL THEN
1239 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_REWK_STAT_REQD');
1240 RAISE REWORK_STATUS_ERR;
1241 END IF;
1242 END IF; /* IF (l_esig_reqd) */
1243
1244 END IF; /* IF (l_esig_reqd OR l_erec_reqd) */
1245
1246 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_OPER_USR_LBL');
1247 l_user_key_label := FND_MESSAGE.GET;
1248
1249 GMD_EDR_STANDARD.raise_event (p_event_name => 'oracle.apps.gmd.operation.sts'
1250 ,p_event_key => p_oprn_id
1251 ,p_parameter_name1 => 'DEFERRED'
1252 ,p_parameter_value1 => 'Y'
1253 ,p_parameter_name2 => 'POST_OPERATION_API'
1254 ,p_parameter_value2 =>'GMD_ERES_POST_OPERATION.set_operation_status('||
1255 p_oprn_id||', '||
1256 p_from_status||', '||
1257 p_to_status||');'
1258 ,p_parameter_name3 => 'PSIG_USER_KEY_LABEL'
1259 ,p_parameter_value3 =>l_user_key_label
1260 ,p_parameter_name4 => 'PSIG_USER_KEY_VALUE'
1261 ,p_parameter_value4 => p_object_name||', '||p_object_version
1262 ,p_parameter_name5 => '#WF_SOURCE_APPLICATION_TYPE'
1263 ,p_parameter_value5 =>'DB'
1264 ,p_parameter_name6 => '#WF_SIGN_REQUESTER'
1265 ,p_parameter_value6 =>FND_GLOBAL.user_name
1266 ,p_parameter_name7 =>'PSIG_TRANSACTION_AUDIT_ID'
1267 ,p_parameter_value7=>-1);
1268
1269 IF l_esig_reqd THEN
1270 X_return_status := 'P';
1271
1272 UPDATE gmd_operations_b
1273 SET operation_status = p_pending_status
1274 WHERE oprn_id = p_oprn_id;
1275
1276 IF p_called_from_form = 'F' THEN
1277 FND_MESSAGE.SET_NAME('GMD','GMD_ERES_PEND_STAT_UPD');
1278 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_to_status_desc);
1279 FND_FILE.PUT(FND_FILE.LOG,
1280 FND_MESSAGE.GET||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1281 ||' :'||p_object_version||')');
1282 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1283 END IF;
1284 END IF; /* IF l_esig_reqd */
1285 EXCEPTION
1286 WHEN PENDING_STATUS_ERR OR
1287 REWORK_STATUS_ERR THEN
1288 ROLLBACK TO SAVEPOINT update_operation;
1289 X_return_status := FND_API.g_ret_sts_error;
1290 FND_MESSAGE.SET_TOKEN('FROM_STATUS', SUBSTR(l_from_status_desc,1, 80));
1291 FND_MESSAGE.SET_TOKEN('TO_STATUS', SUBSTR(l_to_status_desc, 1, 80));
1292 l_text := FND_MESSAGE.GET;
1293 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_VERSION');
1294 l_version_lbl := FND_MESSAGE.GET;
1295 l_text := l_text||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1296 ||' :'||p_object_version||')';
1297 IF p_called_from_form = 'F' THEN
1298 FND_FILE.PUT(FND_FILE.LOG, l_text);
1299 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1300 ELSE
1301 FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
1302 FND_MESSAGE.SET_TOKEN('MESSAGE', l_text);
1303 FND_MSG_PUB.add;
1304 END IF;
1305 WHEN app_exception.record_lock_exception THEN
1306 ROLLBACK TO SAVEPOINT update_operation;
1307 X_return_status := FND_API.g_ret_sts_error;
1308 IF (l_debug = 'Y') THEN
1309 gmd_debug.put_line ('In GMDERESB.pls - locked exception section ');
1310 END IF;
1311 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
1312 'TABLE_NAME',
1313 'GMD_OPERATIONS_B',
1314 'RECORD',
1315 'OPRN_NO : OPRN_VERS = ',
1316 'KEY',
1317 p_object_name||':'||p_object_version
1318 );
1319 WHEN OTHERS THEN
1320 ROLLBACK TO SAVEPOINT update_operation;
1321 X_return_status := FND_API.g_ret_sts_unexp_error;
1322 OPEN Cur_get_desc;
1323 FETCH Cur_get_desc INTO l_replace_type_desc;
1324 CLOSE Cur_get_desc;
1325
1326 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS');
1327 l_status := FND_MESSAGE.GET;
1328
1329 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1330 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',l_replace_type_desc);
1331 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',l_status);
1332 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',p_object_name);
1333 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',l_object_type);
1334 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',p_object_version);
1335 FND_MESSAGE.SET_TOKEN('ERRMSG',SQLERRM);
1336 IF p_called_from_form = 'F' THEN
1337 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1338 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1339 ELSE
1340 FND_MSG_PUB.add;
1341 END IF;
1342 END update_operation_status;
1343
1344 /*###############################################################
1345 # NAME
1346 # update_routing_status
1347 # SYNOPSIS
1348 # update_routing_status
1349 # DESCRIPTION
1350 # Performs update of the routing status and the raise of event
1351 ###############################################################*/
1352
1353 PROCEDURE update_routing_status ( p_routing_id IN VARCHAR2,
1354 p_from_status IN VARCHAR2,
1355 p_to_status IN VARCHAR2,
1356 p_pending_status IN VARCHAR2,
1357 p_rework_status IN VARCHAR2,
1358 p_object_name IN VARCHAR2,
1359 p_object_version IN NUMBER,
1360 p_called_from_form IN VARCHAR2,
1361 x_return_status OUT NOCOPY VARCHAR2) IS
1362 CURSOR Cur_get_desc IS
1363 SELECT meaning
1364 FROM gem_lookups
1365 WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
1366 AND lookup_code = 'ROUTING';
1367
1368 CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
1369 SELECT description
1370 FROM gmd_status
1371 WHERE status_code = pstatus;
1372
1373 l_replace_type_desc VARCHAR2(240);
1374 l_user_key_label VARCHAR2(2000);
1375 l_object_type VARCHAR2(240);
1376 l_from_status_desc VARCHAR2(240);
1377 l_to_status_desc VARCHAR2(240);
1378 l_version_lbl VARCHAR2(2000);
1379 l_text VARCHAR2(4000);
1380 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1381 l_status VARCHAR2(2000);
1382 l_esig_reqd BOOLEAN;
1383 l_erec_reqd BOOLEAN;
1384 PENDING_STATUS_ERR EXCEPTION;
1385 REWORK_STATUS_ERR EXCEPTION;
1386 BEGIN
1387 SAVEPOINT update_routing;
1388 X_return_status := FND_API.g_ret_sts_success;
1389
1390 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING');
1391 l_object_type := FND_MESSAGE.GET;
1392
1393 SELECT 'x'
1394 INTO l_text
1395 FROM gmd_routings_b
1396 WHERE routing_id = p_routing_id
1397 FOR UPDATE OF routing_status nowait;
1398
1399 UPDATE gmd_routings_b
1400 SET routing_status = p_to_status,
1401 last_update_date = sysdate,
1402 last_updated_by = l_user_id
1403 WHERE routing_id = p_routing_id;
1404
1405 GMA_STANDARD.PSIG_REQUIRED (p_event => 'oracle.apps.gmd.routing.sts'
1406 ,p_event_key => p_routing_id
1407 ,p_status => l_esig_reqd);
1408
1409 GMA_STANDARD.EREC_REQUIRED (p_event => 'oracle.apps.gmd.routing.sts'
1410 ,p_event_key => p_routing_id
1411 ,p_status => l_erec_reqd);
1412
1413 IF (l_esig_reqd OR l_erec_reqd) THEN
1414
1415 IF (l_esig_reqd) THEN
1416 OPEN Cur_get_status_desc (p_from_status);
1417 FETCH Cur_get_status_desc INTO l_from_status_desc;
1418 CLOSE Cur_get_status_desc;
1419
1420 OPEN Cur_get_status_desc (p_to_status);
1421 FETCH Cur_get_status_desc INTO l_to_status_desc;
1422 CLOSE Cur_get_status_desc;
1423
1424 IF p_pending_status IS NULL THEN
1425 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_PEND_STAT_REQD');
1426 RAISE PENDING_STATUS_ERR;
1427 END IF;
1428 IF p_rework_status IS NULL THEN
1429 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_REWK_STAT_REQD');
1430 RAISE REWORK_STATUS_ERR;
1431 END IF;
1432 END IF; /* IF (l_esig_reqd) */
1433
1434 END IF; /* IF (l_esig_reqd OR l_erec_reqd) */
1435
1436 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_ROUT_USR_LBL');
1437 l_user_key_label := FND_MESSAGE.GET;
1438 GMD_EDR_STANDARD.raise_event (p_event_name => 'oracle.apps.gmd.routing.sts'
1439 ,p_event_key => p_routing_id
1440 ,p_parameter_name1 => 'DEFERRED'
1441 ,p_parameter_value1 => 'Y'
1442 ,p_parameter_name2 => 'POST_OPERATION_API'
1443 ,p_parameter_value2 =>'GMD_ERES_POST_OPERATION.set_routing_status('||
1444 p_routing_id||', '||
1445 p_from_status||', '||
1446 p_to_status||');'
1447 ,p_parameter_name3 => 'PSIG_USER_KEY_LABEL'
1448 ,p_parameter_value3 =>l_user_key_label
1449 ,p_parameter_name4 => 'PSIG_USER_KEY_VALUE'
1450 ,p_parameter_value4 => p_object_name||', '||p_object_version
1451 ,p_parameter_name5 => '#WF_SOURCE_APPLICATION_TYPE'
1452 ,p_parameter_value5 =>'DB'
1453 ,p_parameter_name6 => '#WF_SIGN_REQUESTER'
1454 ,p_parameter_value6 =>FND_GLOBAL.user_name
1455 ,p_parameter_name7 =>'PSIG_TRANSACTION_AUDIT_ID'
1456 ,p_parameter_value7=>-1);
1457 IF l_esig_reqd THEN
1458 X_return_status := 'P';
1459 UPDATE gmd_routings_b
1460 SET routing_status = p_pending_status
1461 WHERE routing_id = p_routing_id;
1462 IF p_called_from_form = 'F' THEN
1463 FND_MESSAGE.SET_NAME('GMD','GMD_ERES_PEND_STAT_UPD');
1464 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_to_status_desc);
1465 FND_FILE.PUT(FND_FILE.LOG,
1466 FND_MESSAGE.GET||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1467 ||' :'||p_object_version||')');
1468 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1469 END IF;
1470 END IF; /* IF l_esig_reqd */
1471 EXCEPTION
1472 WHEN PENDING_STATUS_ERR OR
1473 REWORK_STATUS_ERR THEN
1474 ROLLBACK TO SAVEPOINT update_routing;
1475 X_return_status := FND_API.g_ret_sts_error;
1476 FND_MESSAGE.SET_TOKEN('FROM_STATUS', SUBSTR(l_from_status_desc,1, 80));
1477 FND_MESSAGE.SET_TOKEN('TO_STATUS', SUBSTR(l_to_status_desc, 1, 80));
1478 l_text := FND_MESSAGE.GET;
1479 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_VERSION');
1480 l_version_lbl := FND_MESSAGE.GET;
1481 l_text := l_text||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1482 ||' :'||p_object_version||')';
1483 IF p_called_from_form = 'F' THEN
1484 FND_FILE.PUT(FND_FILE.LOG, l_text);
1485 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1486 ELSE
1487 FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
1488 FND_MESSAGE.SET_TOKEN('MESSAGE', l_text);
1489 FND_MSG_PUB.add;
1490 END IF;
1491 WHEN app_exception.record_lock_exception THEN
1492 ROLLBACK TO SAVEPOINT update_routing;
1493 X_return_status := FND_API.g_ret_sts_error;
1494 IF (l_debug = 'Y') THEN
1495 gmd_debug.put_line ('In GMDERESB.pls - locked exception section ');
1496 END IF;
1497 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
1498 'TABLE_NAME',
1499 'GMD_ROUTINGS_B',
1500 'RECORD',
1501 'ROUTING_NO : ROUTING_VERS = ',
1502 'KEY',
1503 p_object_name||':'||p_object_version
1504 );
1505 WHEN OTHERS THEN
1506 ROLLBACK TO SAVEPOINT update_routing;
1507 X_return_status := FND_API.g_ret_sts_unexp_error;
1508 OPEN Cur_get_desc;
1509 FETCH Cur_get_desc INTO l_replace_type_desc;
1510 CLOSE Cur_get_desc;
1511
1512 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS');
1513 l_status := FND_MESSAGE.GET;
1514
1515 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1516 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',l_replace_type_desc);
1517 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',l_status);
1518 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',p_object_name);
1519 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',l_object_type);
1520 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',p_object_version);
1521 FND_MESSAGE.SET_TOKEN('ERRMSG',SQLERRM);
1522 IF p_called_from_form = 'F' THEN
1523 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1524 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1525 ELSE
1526 FND_MSG_PUB.add;
1527 END IF;
1528 END update_routing_status;
1529
1530 /*###############################################################
1531 # NAME
1532 # update_recipe_status
1533 # SYNOPSIS
1534 # update_recipe_status
1535 # DESCRIPTION
1536 # Performs update of the recipe status and the raise of event
1537 ###############################################################*/
1538
1539 PROCEDURE update_recipe_status ( p_recipe_id IN VARCHAR2,
1540 p_from_status IN VARCHAR2,
1541 p_to_status IN VARCHAR2,
1542 p_pending_status IN VARCHAR2,
1543 p_rework_status IN VARCHAR2,
1544 p_object_name IN VARCHAR2,
1545 p_object_version IN NUMBER,
1546 p_called_from_form IN VARCHAR2,
1547 x_return_status OUT NOCOPY VARCHAR2) IS
1548 CURSOR Cur_get_desc IS
1549 SELECT meaning
1550 FROM gem_lookups
1551 WHERE lookup_type = 'GMD_SRCH_RPLCE_CRIT_TYPE'
1552 AND lookup_code = 'RECIPE';
1553
1554 CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
1555 SELECT description
1556 FROM gmd_status
1557 WHERE status_code = pstatus;
1558
1559 CURSOR Cur_get_status_type (pstatus VARCHAR2) IS
1560 SELECT status_type, description
1561 FROM gmd_status
1562 WHERE status_code = pstatus;
1563
1564 l_replace_type_desc VARCHAR2(240);
1565 l_user_key_label VARCHAR2(2000);
1566 l_object_type VARCHAR2(240);
1567 l_from_status_desc VARCHAR2(240);
1568 l_to_status_desc VARCHAR2(240);
1569 l_version_lbl VARCHAR2(2000);
1570 l_text VARCHAR2(4000);
1571 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1572 l_status VARCHAR2(2000);
1573 l_esig_reqd BOOLEAN;
1574 l_erec_reqd BOOLEAN;
1575 l_status_type GMD_STATUS.status_type%TYPE;
1576 l_post_operation_api VARCHAR2(4000);
1577
1578 PENDING_STATUS_ERR EXCEPTION;
1579 REWORK_STATUS_ERR EXCEPTION;
1580 VR_ERES_REQ EXCEPTION;
1581 BEGIN
1582 SAVEPOINT update_recipe;
1583 X_return_status := FND_API.g_ret_sts_success;
1584
1585 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE');
1586 l_object_type := FND_MESSAGE.GET;
1587
1588 /* Get the sttaus type */
1589 OPEN Cur_get_status_type (p_to_status);
1590 FETCH Cur_get_status_type INTO l_status_type, l_to_status_desc;
1591 IF Cur_get_status_type%NOTFOUND THEN
1592 l_status_type := p_to_status;
1593 CLOSE Cur_get_status_type;
1594 END IF;
1595 CLOSE Cur_get_status_type;
1596
1597 IF (l_debug= 'Y') THEN
1598 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1599 'The to status type = '||l_status_type);
1600 END IF;
1601
1602 /* Bug # 2353561 - Shyam Sitaram */
1603 /*ERES Implementation - If approvals are required for the */
1604 /*status change of the validity rules then the user has to */
1605 /*do them manually */
1606 IF (l_status_type IN ('800','900','1000') ) THEN
1607 IF GMD_ERES_UTILS.check_recipe_validity_eres (p_recipe_id
1608 ,p_to_status) THEN
1609 IF (l_debug= 'Y') THEN
1610 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1611 'VR eres sig is required and it has be done manually ');
1612 END IF;
1613
1614 RAISE VR_ERES_REQ;
1615 ELSE
1616 /* Based on the recipe status condition - update the Vr status */
1617 IF l_status_type = '800' THEN
1618 -- Change status to ON-HOLD for less than ON-HOLD
1619 UPDATE gmd_recipe_validity_rules
1620 SET validity_rule_status = p_to_status
1621 WHERE recipe_id = p_recipe_id
1622 AND (to_number(validity_rule_status) < to_number('800') OR
1623 to_number(validity_rule_status) between 900 and 999);
1624 ELSIF l_status_type = '900' THEN
1625 UPDATE gmd_recipe_validity_rules
1626 SET validity_rule_status = p_to_status
1627 WHERE recipe_id = p_recipe_id
1628 AND to_number(validity_rule_status) < to_number('800') ;
1629 ELSIF l_status_type = '1000' THEN
1630 UPDATE gmd_recipe_validity_rules
1631 SET validity_rule_status = p_to_status
1632 WHERE recipe_id = p_recipe_id
1633 AND to_number(validity_rule_status) < to_number('1000') ;
1634 END IF;
1635 END IF;
1636 END IF;
1637
1638 IF (l_debug= 'Y') THEN
1639 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1640 'About to lock this recipe '||p_recipe_id);
1641 END IF;
1642
1643 SELECT 'x'
1644 INTO l_text
1645 FROM gmd_recipes_b
1646 WHERE recipe_id = p_recipe_id
1647 FOR UPDATE OF recipe_status nowait;
1648
1649 IF (l_debug= 'Y') THEN
1650 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1651 'About to update recipe with status = '||p_to_status);
1652 END IF;
1653
1654 UPDATE gmd_recipes_b
1655 SET recipe_status = p_to_status,
1656 last_update_date = sysdate,
1657 last_updated_by = l_user_id
1658 WHERE recipe_id = p_recipe_id;
1659
1660 GMA_STANDARD.PSIG_REQUIRED (p_event => 'oracle.apps.gmd.recipe.sts'
1661 ,p_event_key => p_recipe_id
1662 ,p_status => l_esig_reqd);
1663
1664 GMA_STANDARD.EREC_REQUIRED (p_event => 'oracle.apps.gmd.recipe.sts'
1665 ,p_event_key => p_recipe_id
1666 ,p_status => l_erec_reqd);
1667
1668 IF (l_debug= 'Y') THEN
1669 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1670 'Checks if recipe esig is req ');
1671 END IF;
1672
1673 IF (l_esig_reqd OR l_erec_reqd) THEN
1674
1675 IF (l_esig_reqd) THEN
1676
1677 IF (l_debug= 'Y') THEN
1678 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1679 'Esig is req with pending sts = '|| p_pending_status);
1680 END IF;
1681
1682 OPEN Cur_get_status_desc (p_from_status);
1683 FETCH Cur_get_status_desc INTO l_from_status_desc;
1684 CLOSE Cur_get_status_desc;
1685
1686 OPEN Cur_get_status_desc (p_to_status);
1687 FETCH Cur_get_status_desc INTO l_to_status_desc;
1688 CLOSE Cur_get_status_desc;
1689
1690 IF p_pending_status IS NULL THEN
1691 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_PEND_STAT_REQD');
1692 RAISE PENDING_STATUS_ERR;
1693 END IF;
1694 IF p_rework_status IS NULL THEN
1695 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_REWK_STAT_REQD');
1696 RAISE REWORK_STATUS_ERR;
1697 END IF;
1698 END IF; /* IF (l_esig_reqd) */
1699
1700 END IF; /* IF (l_esig_reqd OR l_erec_reqd) */
1701
1702 IF (l_debug= 'Y') THEN
1703 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1704 'Raising Esig event ');
1705 END IF;
1706
1707 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_RECP_USR_LBL');
1708 l_user_key_label := FND_MESSAGE.GET;
1709 l_post_operation_api := 'GMD_ERES_POST_OPERATION.set_recipe_status('||
1710 p_recipe_id||', '||
1711 p_from_status||', '||
1712 p_to_status;
1713 IF GMD_RECIPE_GENERATE.create_validity THEN
1714 l_post_operation_api := l_post_operation_api||', '||1;
1715 END IF;
1716 l_post_operation_api := l_post_operation_api||');';
1717 GMD_EDR_STANDARD.raise_event (p_event_name => 'oracle.apps.gmd.recipe.sts'
1718 ,p_event_key => p_recipe_id
1719 ,p_parameter_name1 => 'DEFERRED'
1720 ,p_parameter_value1 => 'Y'
1721 ,p_parameter_name2 => 'POST_OPERATION_API'
1722 ,p_parameter_value2 => l_post_operation_api
1723 ,p_parameter_name3 => 'PSIG_USER_KEY_LABEL'
1724 ,p_parameter_value3 =>l_user_key_label
1725 ,p_parameter_name4 => 'PSIG_USER_KEY_VALUE'
1726 ,p_parameter_value4 => p_object_name||', '||p_object_version
1727 ,p_parameter_name5 => '#WF_SOURCE_APPLICATION_TYPE'
1728 ,p_parameter_value5 =>'DB'
1729 ,p_parameter_name6 => '#WF_SIGN_REQUESTER'
1730 ,p_parameter_value6 =>FND_GLOBAL.user_name
1731 ,p_parameter_name7 =>'PSIG_TRANSACTION_AUDIT_ID'
1732 ,p_parameter_value7=>-1);
1733
1734 IF (l_debug= 'Y') THEN
1735 gmd_debug.put_line('In GMD_ERES_UTILS.update_receip_status : '||
1736 'After Raise Esig event ');
1737 END IF;
1738
1739 IF l_esig_reqd THEN
1740 X_return_status := 'P';
1741 UPDATE gmd_recipes_b
1742 SET recipe_status = p_pending_status
1743 WHERE recipe_id = p_recipe_id;
1744 IF p_called_from_form = 'F' THEN
1745 FND_MESSAGE.SET_NAME('GMD','GMD_ERES_PEND_STAT_UPD');
1746 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_to_status_desc);
1747 FND_FILE.PUT(FND_FILE.LOG,
1748 FND_MESSAGE.GET||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1749 ||' :'||p_object_version||')');
1750 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1751 END IF;
1752 END IF; /* IF l_esig_reqd */
1753 EXCEPTION
1754 WHEN PENDING_STATUS_ERR OR
1755 REWORK_STATUS_ERR THEN
1756 ROLLBACK TO SAVEPOINT update_recipe;
1757 X_return_status := FND_API.g_ret_sts_error;
1758 FND_MESSAGE.SET_TOKEN('FROM_STATUS', SUBSTR(l_from_status_desc,1, 80));
1759 FND_MESSAGE.SET_TOKEN('TO_STATUS', SUBSTR(l_to_status_desc, 1, 80));
1760 l_text := FND_MESSAGE.GET;
1761 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_VERSION');
1762 l_version_lbl := FND_MESSAGE.GET;
1763 l_text := l_text||' ( '||l_object_type||' :'||p_object_name||' '||l_version_lbl
1764 ||' :'||p_object_version||')';
1765 IF p_called_from_form = 'F' THEN
1766 FND_FILE.PUT(FND_FILE.LOG, l_text);
1767 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1768 ELSE
1769 FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
1770 FND_MESSAGE.SET_TOKEN('MESSAGE', l_text);
1771 FND_MSG_PUB.add;
1772 END IF;
1773 WHEN VR_ERES_REQ THEN
1774 ROLLBACK TO SAVEPOINT update_recipe;
1775 X_return_status := FND_API.g_ret_sts_error;
1776 FND_MESSAGE.SET_NAME('GMD', 'GMD_VLDT_APPR_REQD');
1777 FND_MESSAGE.SET_TOKEN('STATUS', l_to_status_desc);
1778 l_text := FND_MESSAGE.GET;
1779
1780 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1781 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',l_replace_type_desc);
1782 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',l_status);
1783 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',p_object_name);
1784 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',l_object_type);
1785 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',p_object_version);
1786 FND_MESSAGE.SET_TOKEN('ERRMSG',l_text);
1787 IF p_called_from_form = 'F' THEN
1788 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1789 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1790 ELSE
1791 FND_MSG_PUB.add;
1792 END IF;
1793 WHEN app_exception.record_lock_exception THEN
1794 ROLLBACK TO SAVEPOINT update_recipe;
1795 X_return_status := FND_API.g_ret_sts_error;
1796 IF (l_debug = 'Y') THEN
1797 gmd_debug.put_line ('In GMDERESB.pls - locked exception section ');
1798 END IF;
1799 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
1800 'TABLE_NAME',
1801 'GMD_RECIPES_B',
1802 'RECORD',
1803 'RECIPE_NO : RECIPE_VERSION = ',
1804 'KEY',
1805 p_object_name||':'||p_object_version
1806 );
1807 WHEN OTHERS THEN
1808 ROLLBACK TO SAVEPOINT update_recipe;
1809 X_return_status := FND_API.g_ret_sts_unexp_error;
1810 OPEN Cur_get_desc;
1811 FETCH Cur_get_desc INTO l_replace_type_desc;
1812 CLOSE Cur_get_desc;
1813
1814 FND_MESSAGE.SET_NAME('GMD', 'GMD_STATUS');
1815 l_status := FND_MESSAGE.GET;
1816
1817 FND_MESSAGE.SET_NAME('GMD','GMD_CONC_UPDATE_OBJECT_FAILED');
1818 FND_MESSAGE.SET_TOKEN('REPLACE_TYPE',l_replace_type_desc);
1819 FND_MESSAGE.SET_TOKEN('REPLACE_VALUE',l_status);
1820 FND_MESSAGE.SET_TOKEN('OBJECT_NAME',p_object_name);
1821 FND_MESSAGE.SET_TOKEN('OBJECT_TYPE',l_object_type);
1822 FND_MESSAGE.SET_TOKEN('OBJECT_VERS',p_object_version);
1823 FND_MESSAGE.SET_TOKEN('ERRMSG',SQLERRM);
1824 IF p_called_from_form = 'F' THEN
1825 FND_FILE.PUT(FND_FILE.LOG,FND_MESSAGE.GET);
1826 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1827 ELSE
1828 FND_MSG_PUB.add;
1829 END IF;
1830 END update_recipe_status;
1831
1832 /*###############################################################
1833 # NAME
1834 # update_validity_rule_status
1835 # SYNOPSIS
1836 # update_validity_rule_status
1837 # DESCRIPTION
1838 # Performs update of the validity status and the raise of event
1839 ###############################################################*/
1840 PROCEDURE update_validity_rule_status ( p_validity_rule_id IN VARCHAR2,
1841 p_from_status IN VARCHAR2,
1842 p_to_status IN VARCHAR2,
1843 p_pending_status IN VARCHAR2,
1844 p_rework_status IN VARCHAR2,
1845 p_called_from_form IN VARCHAR2 DEFAULT 'F',
1846 x_return_status OUT NOCOPY VARCHAR2) IS
1847
1848 CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
1849 SELECT description
1850 FROM gmd_status
1851 WHERE status_code = pstatus;
1852
1853 CURSOR Cur_get_recipe_info(vVR_id NUMBER) IS
1854 SELECT a.recipe_no, a.recipe_version, b.recipe_use
1855 FROM gmd_recipes_b a, gmd_recipe_validity_rules b
1856 WHERE a.recipe_id = b.recipe_id
1857 AND b.recipe_validity_rule_id = vVr_id;
1858
1859 /* Get the recipe use lookup meaning */
1860 CURSOR Cur_get_lookup_meaning(vlookup_code NUMBER) IS
1861 SELECT meaning
1862 FROM gem_lookups
1863 WHERE lookup_type = 'GMD_FORMULA_USE'
1864 AND lookup_code = vlookup_code;
1865
1866 l_replace_type_desc VARCHAR2(240);
1867 l_user_key_label VARCHAR2(2000);
1868 l_object_type VARCHAR2(240);
1869 l_from_status_desc VARCHAR2(240);
1870 l_to_status_desc VARCHAR2(240);
1871 l_version_lbl VARCHAR2(2000);
1872 l_text VARCHAR2(4000);
1873 l_user_id NUMBER := FND_GLOBAL.USER_ID;
1874 l_status VARCHAR2(2000);
1875 l_esig_reqd BOOLEAN;
1876 l_erec_reqd BOOLEAN;
1877
1878 l_recipe_no gmd_recipes_b.recipe_no%TYPE;
1879 l_recipe_version gmd_recipes_b.recipe_version%TYPE;
1880 l_recipe_use_code NUMBER;
1881 l_recipe_use_meaning VARCHAR2(100);
1882
1883 l_user_key_value VARCHAR2(2000) := '';
1884 l_api_name VARCHAR2(100) := 'UPDATE_VALIDITY_RULE_STATUS';
1885
1886 PENDING_STATUS_ERR EXCEPTION;
1887 REWORK_STATUS_ERR EXCEPTION;
1888 RECIPE_IS_INVALID EXCEPTION;
1889 BEGIN
1890 SAVEPOINT update_validity;
1891 X_return_status := FND_API.g_ret_sts_success;
1892
1893 FND_MESSAGE.SET_NAME('GMD', 'GMD_VALIDITY');
1894 l_object_type := FND_MESSAGE.GET;
1895
1896 SELECT 'x'
1897 INTO l_text
1898 FROM gmd_recipe_validity_rules
1899 WHERE recipe_validity_rule_id = P_validity_rule_id
1900 FOR UPDATE OF validity_rule_status nowait;
1901
1902 UPDATE gmd_recipe_validity_rules
1903 SET validity_rule_status = p_to_status,
1904 last_update_date = sysdate,
1905 last_updated_by = l_user_id
1906 WHERE recipe_validity_rule_id = P_validity_rule_id;
1907
1908 GMA_STANDARD.PSIG_REQUIRED (p_event => 'oracle.apps.gmd.validity.sts'
1909 ,p_event_key => P_validity_rule_id
1910 ,p_status => l_esig_reqd);
1911
1912 GMA_STANDARD.EREC_REQUIRED (p_event => 'oracle.apps.gmd.validity.sts'
1913 ,p_event_key => P_validity_rule_id
1914 ,p_status => l_erec_reqd);
1915
1916 IF (l_esig_reqd OR l_erec_reqd) THEN
1917
1918 IF (l_esig_reqd) THEN
1919 OPEN Cur_get_status_desc (p_from_status);
1920 FETCH Cur_get_status_desc INTO l_from_status_desc;
1921 CLOSE Cur_get_status_desc;
1922
1923 OPEN Cur_get_status_desc (p_to_status);
1924 FETCH Cur_get_status_desc INTO l_to_status_desc;
1925 CLOSE Cur_get_status_desc;
1926
1927 IF p_pending_status IS NULL THEN
1928 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_PEND_STAT_REQD');
1929 RAISE PENDING_STATUS_ERR;
1930 END IF;
1931 IF p_rework_status IS NULL THEN
1932 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_REWK_STAT_REQD');
1933 RAISE REWORK_STATUS_ERR;
1934 END IF;
1935 END IF; /* IF (l_esig_reqd) */
1936 END IF; /* IF (l_esig_reqd OR l_erec_reqd) */
1937
1938 OPEN Cur_get_recipe_info(P_validity_rule_id);
1939 FETCH Cur_get_recipe_info INTO l_recipe_no, l_recipe_version, l_recipe_use_code;
1940 IF Cur_get_recipe_info%NOTFOUND THEN
1941 CLOSE Cur_get_recipe_info;
1942 RAISE RECIPE_IS_INVALID;
1943 ELSE
1944 /* Get the recipe use meaning */
1945 OPEN Cur_get_lookup_meaning(l_recipe_use_code);
1946 FETCH Cur_get_lookup_meaning INTO l_recipe_use_meaning;
1947 IF Cur_get_lookup_meaning%NOTFOUND THEN
1948 CLOSE Cur_get_lookup_meaning;
1949 l_recipe_use_meaning := '';
1950 END IF;
1951 CLOSE Cur_get_lookup_meaning;
1952 l_user_key_value := l_recipe_no||', '||l_recipe_version||', '||l_recipe_use_meaning;
1953 END IF;
1954 CLOSE Cur_get_recipe_info;
1955
1956 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_VLD_USR_LBL');
1957 l_user_key_label := FND_MESSAGE.GET;
1958 GMD_EDR_STANDARD.raise_event (p_event_name => 'oracle.apps.gmd.validity.sts'
1959 ,p_event_key => P_validity_rule_id
1960 ,p_parameter_name1 => 'DEFERRED'
1961 ,p_parameter_value1 => 'Y'
1962 ,p_parameter_name2 => 'POST_OPERATION_API'
1963 ,p_parameter_value2 =>'GMD_ERES_POST_OPERATION.set_validity_status('||
1964 P_validity_rule_id||', '||
1965 p_from_status||', '||
1966 p_to_status||');'
1967 ,p_parameter_name3 => 'PSIG_USER_KEY_LABEL'
1968 ,p_parameter_value3 =>l_user_key_label
1969 ,p_parameter_name4 => 'PSIG_USER_KEY_VALUE'
1970 ,p_parameter_value4 =>l_user_key_value
1971 ,p_parameter_name5 => '#WF_SOURCE_APPLICATION_TYPE'
1972 ,p_parameter_value5 =>'DB'
1973 ,p_parameter_name6 => '#WF_SIGN_REQUESTER'
1974 ,p_parameter_value6 =>FND_GLOBAL.user_name
1975 ,p_parameter_name7 =>'PSIG_TRANSACTION_AUDIT_ID'
1976 ,p_parameter_value7=>-1);
1977 IF l_esig_reqd THEN
1978 X_return_status := 'P';
1979 UPDATE gmd_recipe_validity_rules
1980 SET validity_rule_status = p_pending_status
1981 WHERE recipe_validity_rule_id = P_validity_rule_id;
1982
1983 IF p_called_from_form = 'F' THEN
1984 FND_MESSAGE.SET_NAME('GMD','GMD_ERES_PEND_STAT_UPD');
1985 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_to_status_desc);
1986 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
1987 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1988 END IF;
1989 END IF; /* IF l_esig_reqd */
1990 EXCEPTION
1991 WHEN PENDING_STATUS_ERR OR
1992 REWORK_STATUS_ERR THEN
1993 ROLLBACK TO SAVEPOINT update_validity;
1994 X_return_status := FND_API.g_ret_sts_error;
1995 FND_MESSAGE.SET_TOKEN('FROM_STATUS', SUBSTR(l_from_status_desc,1, 80));
1996 FND_MESSAGE.SET_TOKEN('TO_STATUS', SUBSTR(l_to_status_desc, 1, 80));
1997
1998 l_text := FND_MESSAGE.GET;
1999 FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
2000 FND_MESSAGE.SET_TOKEN('MESSAGE', l_text);
2001 FND_MSG_PUB.add;
2002 WHEN RECIPE_IS_INVALID THEN
2003 ROLLBACK TO SAVEPOINT update_validity;
2004 X_return_status := FND_API.g_ret_sts_error;
2005 FND_MESSAGE.SET_NAME ('GMD', 'GMD_RECIPE_INFO');
2006 FND_MSG_PUB.ADD;
2007 WHEN app_exception.record_lock_exception THEN
2008 ROLLBACK TO SAVEPOINT update_validity;
2009 X_return_status := FND_API.g_ret_sts_error;
2010 IF (l_debug = 'Y') THEN
2011 gmd_debug.put_line ('In GMDERESB.pls - locked exception section ');
2012 END IF;
2013 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
2014 'TABLE_NAME',
2015 'GMD_RECIPE_VALIDITY_RULES',
2016 'RECORD',
2017 'RECIPE_VALIDITY_RULE_ID = ',
2018 'KEY',
2019 P_validity_rule_id
2020 );
2021 WHEN OTHERS THEN
2022 ROLLBACK TO SAVEPOINT update_validity;
2023 X_return_status := FND_API.g_ret_sts_unexp_error;
2024 fnd_msg_pub.add_exc_msg ('GMD_ERES_UTILS', l_api_name);
2025 END update_validity_rule_status;
2026
2027 /*###############################################################
2028 # NAME
2029 # update_substitution_status
2030 # SYNOPSIS
2031 # update_substitution_status
2032 # DESCRIPTION
2033 # Procdure will update the substitution status based on the default status
2034 # and raise the event if required.
2035 # Added the procedure for bug#5394532.
2036 ###############################################################*/
2037
2038 PROCEDURE update_substitution_status (p_substitution_id IN NUMBER,
2039 p_from_status IN VARCHAR2,
2040 p_to_status IN VARCHAR2,
2041 p_pending_status IN VARCHAR2,
2042 p_rework_status IN VARCHAR2,
2043 p_called_from_form IN VARCHAR2 DEFAULT 'F',
2044 x_return_status OUT NOCOPY VARCHAR2) IS
2045
2046 CURSOR Cur_get_status_desc (pstatus VARCHAR2) IS
2047 SELECT description
2048 FROM gmd_status
2049 WHERE status_code = pstatus;
2050
2051
2052 CURSOR Cur_subs_details IS
2053 SELECT owner_organization_id,
2054 substitution_name,
2055 substitution_version
2056 FROM gmd_item_substitution_hdr
2057 WHERE substitution_id = p_substitution_id;
2058
2059 l_subs_details Cur_subs_details%rowtype;
2060 l_esig_reqd BOOLEAN;
2061 l_erec_reqd BOOLEAN;
2062 l_user_key_label VARCHAR2(2000);
2063 l_text VARCHAR2(1);
2064 l_from_status_desc VARCHAR2(240);
2065 l_to_status_desc VARCHAR2(240);
2066 l_api_name VARCHAR2(100) := 'UPDATE_SUBSTITUTION_STATUS';
2067
2068 PENDING_STATUS_ERR EXCEPTION;
2069 REWORK_STATUS_ERR EXCEPTION;
2070
2071 BEGIN
2072 SAVEPOINT update_substitution;
2073 X_return_status := FND_API.g_ret_sts_success;
2074
2075 SELECT 'x'
2076 INTO l_text
2077 FROM gmd_item_substitution_hdr_b
2078 WHERE substitution_id = p_substitution_id
2079 FOR UPDATE OF substitution_status nowait;
2080
2081 UPDATE gmd_item_substitution_hdr_b
2082 SET substitution_status = p_to_status,
2083 last_update_date = sysdate,
2084 last_updated_by = fnd_global.user_id
2085 WHERE substitution_id = p_substitution_id;
2086
2087 GMA_STANDARD.PSIG_REQUIRED (p_event => 'oracle.apps.gmd.itemsub.sts'
2088 ,p_event_key => p_substitution_id
2089 ,p_status => l_esig_reqd);
2090
2091 GMA_STANDARD.EREC_REQUIRED (p_event => 'oracle.apps.gmd.itemsub.sts'
2092 ,p_event_key => p_substitution_id
2093 ,p_status => l_erec_reqd);
2094
2095 IF (l_esig_reqd OR l_erec_reqd) THEN
2096 IF (l_esig_reqd) THEN
2097 OPEN Cur_get_status_desc (p_from_status);
2098 FETCH Cur_get_status_desc INTO l_from_status_desc;
2099 CLOSE Cur_get_status_desc;
2100
2101 OPEN Cur_get_status_desc (p_to_status);
2102 FETCH Cur_get_status_desc INTO l_to_status_desc;
2103 CLOSE Cur_get_status_desc;
2104
2105 IF p_pending_status IS NULL THEN
2106 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_PEND_STAT_REQD');
2107 RAISE PENDING_STATUS_ERR;
2108 END IF;
2109 IF p_rework_status IS NULL THEN
2110 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_REWK_STAT_REQD');
2111 RAISE REWORK_STATUS_ERR;
2112 END IF;
2113 END IF; /* IF (l_esig_reqd) */
2114 END IF; /* IF (l_esig_reqd OR l_erec_reqd) */
2115
2116 OPEN Cur_subs_details;
2117 FETCH Cur_subs_details INTO l_subs_details;
2118 CLOSE Cur_subs_details;
2119
2120 FND_MESSAGE.SET_NAME('GMD', 'GMD_ERES_SUBSTITUTION_USR_LBL');
2121 l_user_key_label := FND_MESSAGE.GET;
2122
2123 GMD_EDR_STANDARD.raise_event (p_event_name => 'oracle.apps.gmd.itemsub.sts'
2124 ,p_event_key => p_substitution_id
2125 ,p_parameter_name1 => 'DEFERRED'
2126 ,p_parameter_value1 => 'Y'
2127 ,p_parameter_name2 => 'POST_OPERATION_API'
2128 ,p_parameter_value2 =>'GMD_ERES_POST_OPERATION.set_substition_status('||
2129 p_substitution_id||', '||
2130 p_from_status||', '||
2131 p_to_status||');'
2132 ,p_parameter_name3 => 'PSIG_USER_KEY_LABEL'
2133 ,p_parameter_value3 =>l_user_key_label
2134 ,p_parameter_name4 => 'PSIG_USER_KEY_VALUE'
2135 ,p_parameter_value4 => l_subs_details.substitution_name||', '||l_subs_details.substitution_version
2136 ,p_parameter_name5 => '#WF_SOURCE_APPLICATION_TYPE'
2137 ,p_parameter_value5 =>'DB'
2138 ,p_parameter_name6 => '#WF_SIGN_REQUESTER'
2139 ,p_parameter_value6 =>FND_GLOBAL.user_name
2140 ,p_parameter_name7 =>'PSIG_TRANSACTION_AUDIT_ID'
2141 ,p_parameter_value7=>-1);
2142
2143 IF l_esig_reqd THEN
2144 X_return_status := 'P';
2145 UPDATE gmd_item_substitution_hdr_b
2146 SET substitution_status = p_pending_status
2147 WHERE substitution_id = p_substitution_id;
2148
2149 IF p_called_from_form = 'F' THEN
2150 FND_MESSAGE.SET_NAME('GMD','GMD_ERES_PEND_STAT_UPD');
2151 FND_MESSAGE.SET_TOKEN('TO_STATUS', l_to_status_desc);
2152 FND_FILE.PUT(FND_FILE.LOG, FND_MESSAGE.GET);
2153 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
2154 END IF;
2155 END IF; /* IF l_esig_reqd */
2156 EXCEPTION
2157 WHEN PENDING_STATUS_ERR OR
2158 REWORK_STATUS_ERR THEN
2159 ROLLBACK TO SAVEPOINT update_substitution;
2160 X_return_status := FND_API.g_ret_sts_error;
2161 FND_MESSAGE.SET_TOKEN('FROM_STATUS', SUBSTR(l_from_status_desc,1, 80));
2162 FND_MESSAGE.SET_TOKEN('TO_STATUS', SUBSTR(l_to_status_desc, 1, 80));
2163
2164 l_text := FND_MESSAGE.GET;
2165 FND_MESSAGE.SET_NAME ('FND', 'FND_GENERIC_MESSAGE');
2166 FND_MESSAGE.SET_TOKEN('MESSAGE', l_text);
2167 FND_MSG_PUB.add;
2168 WHEN app_exception.record_lock_exception THEN
2169 ROLLBACK TO SAVEPOINT update_substitution;
2170 X_return_status := FND_API.g_ret_sts_error;
2171 IF (l_debug = 'Y') THEN
2172 gmd_debug.put_line ('In GMDERESB.pls - locked exception section ');
2173 END IF;
2174 gmd_api_grp.log_message('GMD_RECORD_LOCKED',
2175 'TABLE_NAME',
2176 'GMD_ITEM_SUBSTITUTION_HDR_B',
2177 'RECORD',
2178 'SUBSTITUTION_ID = ',
2179 'KEY',
2180 P_substitution_id
2181 );
2182 WHEN OTHERS THEN
2183 ROLLBACK TO SAVEPOINT update_substitution;
2184 X_return_status := FND_API.g_ret_sts_unexp_error;
2185 fnd_msg_pub.add_exc_msg ('GMD_ERES_UTILS', l_api_name);
2186
2187
2188 END update_substitution_status;
2189
2190 /*###############################################################
2191 # NAME
2192 # get_recipe_details
2193 # SYNOPSIS
2194 # get_recipe_details
2195 # DESCRIPTION
2196 # Procdure will fetch the recipe info based on the formula
2197 ###############################################################*/
2198
2199 PROCEDURE get_recipe_details (
2200 P_formula_id IN NUMBER,
2201 P_recipe_no OUT NOCOPY VARCHAR2,
2202 P_recipe_vers OUT NOCOPY NUMBER,
2203 P_recipe_desc OUT NOCOPY VARCHAR2,
2204 P_recipe_status OUT NOCOPY VARCHAR2,
2205 P_recipe_type OUT NOCOPY NUMBER )
2206 IS
2207 X_recipe_tbl GMD_RECIPE_HEADER.recipe_hdr;
2208 X_recipe_flex GMD_RECIPE_HEADER.flex;
2209 l_return_status VARCHAR2(1);
2210 BEGIN
2211 l_return_status := FND_API.G_RET_STS_SUCCESS;
2212
2213 gmd_api_grp.retrieve_recipe (
2214 p_formula_id => p_formula_id,
2215 l_recipe_tbl => X_recipe_tbl,
2216 l_recipe_flex => X_recipe_flex,
2217 x_return_status => l_return_status);
2218
2219 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2220 p_recipe_no := X_recipe_tbl.recipe_no;
2221 p_recipe_vers := X_recipe_tbl.recipe_version;
2222 p_recipe_desc := X_recipe_tbl.recipe_description;
2223 p_recipe_status := X_recipe_tbl.recipe_status;
2224 p_recipe_type := X_recipe_tbl.recipe_type;
2225 END IF;
2226 END get_recipe_details;
2227
2228 /*###############################################################
2229 # NAME
2230 # get_validity_details
2231 # SYNOPSIS
2232 # get_validity_details
2233 # DESCRIPTION
2234 # Procdure will fetch the validity rule info based on the formula
2235 # 10-Feb-2005 Krishna
2236 # Added additional parameters p_orgn_id, p_revision
2237 ###############################################################*/
2238
2239 PROCEDURE get_validity_details (
2240 P_formula_id IN NUMBER,
2241 p_orgn_id OUT NOCOPY NUMBER, -- Krishna NPD Conv
2242 P_item_id OUT NOCOPY NUMBER,
2243 --p_revision OUT NOCOPY NUMBER, -- Krishna NPD Conv
2244 p_revision OUT NOCOPY VARCHAR2,
2245 P_item_um OUT NOCOPY VARCHAR2,
2246 P_min_qty OUT NOCOPY NUMBER,
2247 P_max_qty OUT NOCOPY NUMBER,
2248 P_std_qty OUT NOCOPY NUMBER,
2249 P_inv_min_qty OUT NOCOPY NUMBER,
2250 P_inv_max_qty OUT NOCOPY NUMBER,
2251 P_min_eff_date OUT NOCOPY DATE,
2252 P_max_eff_date OUT NOCOPY DATE,
2253 P_recipe_use OUT NOCOPY VARCHAR2,
2254 P_preference OUT NOCOPY NUMBER,
2255 P_validity_rule_status OUT NOCOPY VARCHAR2)
2256 IS
2257 X_recipe_vr GMD_RECIPE_DETAIL.recipe_vr;
2258 X_vr_flex GMD_RECIPE_DETAIL.flex;
2259
2260 l_return_status VARCHAR2(1);
2261 l_recipe_use VARCHAR2(2000);
2262 l_recipe_use_temp VARCHAR2(40);
2263
2264 CURSOR Cur_recipe_use(V_lookup_code VARCHAR2) IS
2265 SELECT meaning
2266 FROM gem_lookups
2267 WHERE lookup_type = 'GMD_FORMULA_USE'
2268 and lookup_code = V_lookup_code;
2269
2270 BEGIN
2271 l_return_status := FND_API.G_RET_STS_SUCCESS;
2272
2273 gmd_api_grp.retrieve_vr (
2274 p_formula_id => p_formula_id,
2275 l_recipe_vr_tbl => X_recipe_vr,
2276 l_vr_flex => X_vr_flex,
2277 x_return_status => l_return_status );
2278
2279 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2280 p_item_id := X_recipe_vr.inventory_item_id;
2281 p_revision := X_recipe_vr.revision; -- NPD Conv
2282 p_orgn_id := X_recipe_vr.organization_id; -- NPD Conv
2283 p_item_um := X_recipe_vr.detail_uom;
2284 p_min_qty := X_recipe_vr.min_qty;
2285 p_max_qty := X_recipe_vr.max_qty;
2286 p_std_qty := X_recipe_vr.std_qty;
2287 p_inv_min_qty := X_recipe_vr.inv_min_qty;
2288 p_inv_max_qty := X_recipe_vr.inv_max_qty;
2289 p_min_eff_date := X_recipe_vr.start_date;
2290 p_max_eff_date := X_recipe_vr.end_date;
2291 l_recipe_use_temp := SUBSTR(X_recipe_vr.recipe_use, 1,1);
2292
2293 IF l_recipe_use_temp = 1 THEN
2294 OPEN CUR_recipe_use (0);
2295 FETCH Cur_recipe_use INTO l_recipe_use_temp;
2296 CLOSE Cur_recipe_use;
2297 l_recipe_use := l_recipe_use_temp;
2298 END IF;
2299
2300 l_recipe_use_temp := SUBSTR(X_recipe_vr.recipe_use, 2,1);
2301
2302 IF l_recipe_use_temp = 1 THEN
2303 OPEN CUR_recipe_use (1);
2304 FETCH Cur_recipe_use INTO l_recipe_use_temp;
2305 CLOSE Cur_recipe_use;
2306
2307 IF l_recipe_use IS NOT NULL THEN
2308 l_recipe_use := l_recipe_use||',';
2309 END IF;
2310 l_recipe_use := l_recipe_use||l_recipe_use_temp;
2311 END IF;
2312
2313 l_recipe_use_temp := SUBSTR(X_recipe_vr.recipe_use, 3,1);
2314
2315 IF l_recipe_use_temp = 1 THEN
2316 OPEN CUR_recipe_use (2);
2317 FETCH Cur_recipe_use INTO l_recipe_use_temp;
2318 CLOSE Cur_recipe_use;
2319
2320 IF l_recipe_use IS NOT NULL THEN
2321 l_recipe_use := l_recipe_use||',';
2322 END IF;
2323
2324 l_recipe_use := l_recipe_use||l_recipe_use_temp;
2325 END IF;
2326
2327 l_recipe_use_temp := SUBSTR(X_recipe_vr.recipe_use, 4,1);
2328
2329 IF l_recipe_use_temp = 1 THEN
2330 OPEN CUR_recipe_use (3);
2331 FETCH Cur_recipe_use INTO l_recipe_use_temp;
2332 CLOSE Cur_recipe_use;
2333
2334 IF l_recipe_use IS NOT NULL THEN
2335 l_recipe_use := l_recipe_use||',';
2336 END IF;
2337 l_recipe_use := l_recipe_use||l_recipe_use_temp;
2338 END IF;
2339
2340 l_recipe_use_temp := SUBSTR(X_recipe_vr.recipe_use, 5,1);
2341
2342 IF l_recipe_use_temp = 1 THEN
2343 OPEN CUR_recipe_use (4);
2344 FETCH Cur_recipe_use INTO l_recipe_use_temp;
2345 CLOSE Cur_recipe_use;
2346
2347 IF l_recipe_use IS NOT NULL THEN
2348 l_recipe_use := l_recipe_use||',';
2349 END IF;
2350 l_recipe_use := l_recipe_use||l_recipe_use_temp;
2351 END IF;
2352
2353 p_recipe_use := l_recipe_use;
2354 p_preference := X_recipe_vr.preference;
2355 p_validity_rule_status := X_recipe_vr.validity_rule_status;
2356 END IF;
2357 END get_validity_details;
2358
2359 /*###############################################################
2360 # NAME
2361 # GET_ITEM_NO_DESC
2362 # SYNOPSIS
2363 # GET_ITEM_NO_DESC(l_item_id, l_orgn_code, l_item_no, l_item_desc);
2364 # DESCRIPTION
2365 # Procdure will fetch the item no, description based on organization_id, item_id
2366 ###############################################################*/
2367
2368 PROCEDURE get_item_no_desc (
2369 pitem_id IN NUMBER,
2370 porgn_id IN NUMBER,
2371 pitem_no OUT NOCOPY VARCHAR2,
2372 pitem_desc OUT NOCOPY VARCHAR2 )IS
2373 CURSOR get_item_no_desc IS
2374 SELECT concatenated_segments, description
2375 FROM mtl_system_items_kfv
2376 WHERE inventory_item_id = pitem_id
2377 AND organization_id = porgn_id;
2378 BEGIN
2379 OPEN get_item_no_desc;
2380 FETCH get_item_no_desc
2381 INTO pitem_no, pitem_desc;
2382 IF (get_item_no_desc%NOTFOUND) THEN
2383 pitem_no := ' ';
2384 pitem_desc := ' ';
2385 END IF;
2386 CLOSE get_item_no_desc;
2387 END get_item_no_desc;
2388
2389 /*###############################################################
2390 # NAME
2391 # GET_ORGANIZATION_CODE
2392 # SYNOPSIS
2393 # GET_ORGANIZATION_CODE(1381,l_orgn_code);
2394 # DESCRIPTION
2395 # Procdure will fetch the organization code based on organization_id
2396 ###############################################################*/
2397
2398 PROCEDURE get_organization_code
2399 (p_orgn_id IN NUMBER,
2400 p_orgn_code OUT NOCOPY VARCHAR2) IS
2401 CURSOR Cur_get_orgn_code IS
2402 SELECT organization_code
2403 FROM ORG_ORGANIZATION_DEFINITIONS
2404 WHERE organization_id = p_orgn_id;
2405 BEGIN OPEN Cur_get_orgn_code;
2406 FETCH Cur_get_orgn_code
2407 INTO p_orgn_code;
2408 CLOSE Cur_get_orgn_code;
2409 END get_organization_code;
2410
2411 /*###############################################################
2412 # NAME
2413 # GET_LOOKUP_VALUE
2414 # SYNOPSIS
2415 # GET_LOOKUP_VALUE(l_lookup_type, l_lookup_code, l_meaning);
2416 # DESCRIPTION
2417 # Procdure will fetch the item no, description based on organization_id, item_id
2418 ###############################################################*/
2419
2420 PROCEDURE get_lookup_value (
2421 plookup_type IN VARCHAR2,
2422 plookup_code IN VARCHAR2,
2423 pmeaning OUT NOCOPY VARCHAR2) IS
2424
2425 CURSOR get_lookup IS
2426 SELECT meaning
2427 FROM fnd_lookup_values_vl
2428 WHERE lookup_type = plookup_type and
2429 lookup_code = plookup_code;
2430
2431 BEGIN
2432 OPEN get_lookup;
2433 FETCH get_lookup into pmeaning;
2434 IF (get_lookup%NOTFOUND) THEN
2435 pmeaning := ' ';
2436 END IF;
2437 CLOSE get_lookup;
2438
2439 END get_lookup_value;
2440
2441 /*======================================================================
2442 -- PROCEDURE :
2443 -- get_tech_parm_name
2444 --
2445 -- DESCRIPTION:
2446 -- This PL/SQL procedure is responsible for getting the
2447 -- technical parameter name and unit code for a given tech_parm_id.
2448 --
2449 -- REQUIREMENTS
2450 --
2451 -- SYNOPSIS:
2452 -- get_tech_parm_name (100, P_tech_parm_name,P_unit_code);
2453 --
2454 --===================================================================== */
2455 PROCEDURE get_tech_parm_name(P_tech_parm_id IN NUMBER, P_tech_parm_name OUT NOCOPY VARCHAR2,
2456 P_unit_code OUT NOCOPY VARCHAR2) IS
2457 CURSOR Cur_get_param IS
2458 SELECT tech_parm_name, lm_unit_code
2459 FROM gmd_tech_parameters_b
2460 WHERE tech_parm_id = P_tech_parm_id;
2461 BEGIN
2462 OPEN Cur_get_param;
2463 FETCH Cur_get_param INTO P_tech_parm_name,P_unit_code;
2464 CLOSE Cur_get_param;
2465 END get_tech_parm_name;
2466
2467 /*======================================================================
2468 -- PROCEDURE :
2469 -- get_category_name
2470 --
2471 -- DESCRIPTION:
2472 -- This PL/SQL procedure is responsible for getting the
2473 -- category name for a given category_id.
2474 --
2475 -- REQUIREMENTS
2476 --
2477 -- SYNOPSIS:
2478 -- get_category_name (100, p_category_name);
2479 --
2480 --===================================================================== */
2481 PROCEDURE get_category_name(P_category_id IN NUMBER, P_category_name OUT NOCOPY VARCHAR2) IS
2482 CURSOR Cur_get_category IS
2483 SELECT concatenated_segments
2484 FROM mtl_categories_kfv
2485 WHERE category_id = P_category_id;
2486 BEGIN
2487 OPEN Cur_get_category;
2488 FETCH Cur_get_category INTO P_category_name;
2489 CLOSE Cur_get_category;
2490 END get_category_name;
2491
2492 /*======================================================================
2493 -- PROCEDURE :
2494 -- get_category_set_name
2495 --
2496 -- DESCRIPTION:
2497 -- This PL/SQL procedure is responsible for getting the
2498 -- category set name for a given category_set_id.
2499 --
2500 -- REQUIREMENTS
2501 --
2502 -- SYNOPSIS:
2503 -- get_category_set_name (100, p_category_set_name);
2504 --
2505 --===================================================================== */
2506 PROCEDURE get_category_set_name(P_category_set_id IN NUMBER, P_category_set_name OUT NOCOPY VARCHAR2) IS
2507 CURSOR Cur_get_category_set IS
2508 SELECT category_set_name
2509 FROM mtl_category_sets
2510 WHERE category_set_id = P_category_set_id;
2511 BEGIN
2512 OPEN Cur_get_category_set;
2513 FETCH Cur_get_category_set INTO P_category_set_name;
2514 CLOSE Cur_get_category_set;
2515 END get_category_set_name;
2516
2517
2518 /*======================================================================
2519 -- PROCEDURE :
2520 -- get_formula_line_no
2521 --
2522 -- DESCRIPTION:
2523 -- This PL/SQL procedure is responsible for getting the
2524 -- formula_line_no corresponding to formula_line_id
2525 --
2526 -- REQUIREMENTS
2527 --
2528 -- SYNOPSIS:
2529 -- get_formula_line_no (100, P_formulaline_no);
2530 --
2531 --===================================================================== */
2532 PROCEDURE get_formula_line_no(P_formulaline_id IN NUMBER,P_formulaline_no OUT NOCOPY NUMBER) IS
2533 CURSOR Cur_get_lineno IS
2534 SELECT LINE_NO
2535 FROM fm_matl_dtl
2536 WHERE formulaline_id = P_formulaline_id;
2537 BEGIN
2538 OPEN Cur_get_lineno;
2539 FETCH Cur_get_lineno INTO P_formulaline_no;
2540 CLOSE Cur_get_lineno;
2541 END get_formula_line_no;
2542
2543
2544 /*======================================================================
2545 -- PROCEDURE :
2546 -- get_routing_details_eres
2547 --
2548 -- DESCRIPTION:
2549 -- This PL/SQL procedure is responsible for getting the
2550 -- Routing Details for Auto Recipe ERES
2551 --
2552 -- HISTORY
2553 -- Kapil M 03-JAN-07 Created for Bug# 5458666
2554 --
2555 --===================================================================== */
2556 PROCEDURE get_routing_details_eres (
2557 P_doc_id IN VARCHAR2,
2558 P_routing_no OUT NOCOPY VARCHAR2,
2559 P_routing_vers OUT NOCOPY NUMBER,
2560 P_routing_desc OUT NOCOPY VARCHAR2,
2561 P_routing_status OUT NOCOPY VARCHAR2,
2562 P_enhancd_PI_ind OUT NOCOPY VARCHAR2 )IS
2563
2564 p_routing_id NUMBER;
2565 CURSOR Cur_get_routing_details(V_routing_id NUMBER) IS
2566 SELECT ROUTING_NO, ROUTING_VERS ,ROUTING_DESC , b.MEANING
2567 FROM gmd_routings_vl a , gmd_status b
2568 WHERE a.routing_id = V_routing_id
2569 AND a.routing_status = b.status_code;
2570
2571 CURSOR Cur_get_value (vLookup_code VARCHAR2) IS
2572 SELECT MEANING
2573 FROM GEM_LOOKUPS
2574 WHERE LOOKUP_TYPE = 'GME_YES'
2575 AND LOOKUP_CODE = vLookup_code;
2576
2577 l_temp_id VARCHAR2(240);
2578
2579 BEGIN
2580 -- Get the routing_id from the document_id
2581 l_temp_id := substr(P_doc_id,instr(P_doc_id,'$')+1,length(P_doc_id));
2582 P_enhancd_PI_ind :=substr(l_temp_id,instr(l_temp_id,'$')+1,length(l_temp_id));
2583 p_routing_id := substr(l_temp_id,1,instr(l_temp_id,'$')-1);
2584
2585 IF p_routing_id IS NOT NULL THEN
2586 OPEN Cur_get_routing_details(p_routing_id);
2587 FETCH Cur_get_routing_details INTO P_routing_no, P_routing_vers ,P_routing_desc , P_routing_status;
2588 CLOSE Cur_get_routing_details;
2589
2590 IF (P_enhancd_PI_ind = NULL) THEN
2591 P_enhancd_PI_ind := 'N';
2592 END IF;
2593 ELSE
2594 P_enhancd_PI_ind := 'N';
2595 END IF;
2596
2597 OPEN Cur_get_value (P_enhancd_PI_ind);
2598 FETCH Cur_get_value INTO P_enhancd_PI_ind;
2599 CLOSE Cur_get_value;
2600 END get_routing_details_eres;
2601
2602 /*======================================================================
2603 -- PROCEDURE :
2604 -- get_yes_no_value
2605 --
2606 -- DESCRIPTION:
2607 -- This PL/SQL procedure is responsible for getting the
2608 -- Yes/No value for Y/N Flags
2609 --
2610 -- HISTORY
2611 -- Kapil M 12-FEB-07 Created for Bug# 5716318
2612 -- Used for Calculate Product Qty flag
2613 --
2614 --===================================================================== */
2615 PROCEDURE get_yes_no_value (
2616 plookup_code IN VARCHAR2,
2617 pmeaning OUT NOCOPY VARCHAR2) IS
2618
2619 CURSOR get_lookup IS
2620 SELECT meaning
2621 FROM fnd_lookup_values_vl
2622 WHERE lookup_type = 'GME_YES' and
2623 lookup_code = plookup_code;
2624
2625 BEGIN
2626 OPEN get_lookup;
2627 FETCH get_lookup into pmeaning;
2628 IF (get_lookup%NOTFOUND) THEN
2629 pmeaning := ' ';
2630 END IF;
2631 CLOSE get_lookup;
2632
2633 END get_yes_no_value ;
2634
2635 end GMD_ERES_UTILS;