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