1 PACKAGE BODY GMD_ROUT_MIGRATION AS
2 /* $Header: GMDRTMGB.pls 120.3 2005/10/13 12:54:54 kshukla noship $ pxkumar*/
3
4 PROCEDURE INSERT_ROUT_STATUS IS
5 /*Cursor to get all the routing information */
6 CURSOR Cur_routing IS
7 SELECT *
8 FROM gmd_routings_b r
9 WHERE EXISTS (Select 1
10 from gmd_routings_b
11 Where routing_id = r.routing_id AND
12 routing_status IS NULL)
13 ORDER BY routing_id;
14
15 CURSOR Cur_formula_Id(prouting_id NUMBER) IS
16 SELECT formula_Id
17 FROM fm_form_eff_bak
18 WHERE routing_Id = prouting_id;
19
20 l_orgn_code VARCHAR2(6);
21 l_routing_status GMD_STATUS.status_code%TYPE;
22 l_formula_id NUMBER;
23 l_return_val NUMBER;
24 error_msg VARCHAR2(240);
25
26 BEGIN
27 FOR rout_rec IN Cur_routing LOOP
28 BEGIN
29 l_orgn_code := fnd_profile.value_specific
30 ('GEMMS_DEFAULT_ORGN',rout_rec.created_by);
31
32 /* Function to get routing status */
33 OPEN Cur_formula_id(rout_rec.routing_id);
34 FETCH Cur_formula_Id INTO l_formula_id;
35 IF Cur_formula_ID%NOTFOUND THEN
36 l_Formula_Id := 0;
37 CLOSE Cur_formula_id;
38 END IF;
39 l_return_val := GMDFMVAL_PUB.locked_effectivity_val(l_formula_id);
40 IF (l_return_val <> 0) THEN
41 l_routing_status := '900';
42 ELSE
43 l_routing_status := '700';
44 END IF;
45 CLOSE Cur_formula_id;
46
47 /* If the routing is inactive or it is marked for purge
48 then we make it obsoleted */
49 IF ((rout_rec.inactive_ind = 1) OR (rout_rec.delete_mark = 1)) THEN
50 l_routing_status := '1000';
51 END IF;
52
53 UPDATE gmd_routings_b
54 SET process_loss = 0,
55 effective_start_date = rout_rec.creation_date ,
56 effective_end_date = NULL,
57 owner_id = rout_rec.created_by,
58 project_id = null,
59 routing_status = l_routing_status,
60 owner_orgn_code = l_orgn_code
61 WHERE routing_id = rout_rec.routing_id ;
62 EXCEPTION
63 WHEN OTHERS THEN
64 error_msg := SQLERRM;
65 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_ROUT_HDR'
66 ,p_target_table => 'GMD_ROUTINGS_B'
67 ,p_source_id => rout_rec.routing_id
68 ,p_target_id => rout_rec.routing_id
69 ,p_message => error_msg
70 ,p_error_type => 'U');
71
72 END; /* Prior to end loop */
73 END LOOP ;
74 END INSERT_ROUT_STATUS;
75
76 --BEGIN Bug#2200539 P.Raghu
77 PROCEDURE INSERT_TRANSFER_PERCENT IS
78 /* Procedure to update transfer_percent column to 100% in fm_rout_dep table */
79 BEGIN
80 UPDATE Fm_Rout_Dep
81 SET Transfer_Pct = 100
82 WHERE transfer_pct IS NULL;
83
84 update fm_rout_dep
85 set max_delay = NULL
86 where max_delay = 0;
87 END INSERT_TRANSFER_PERCENT;
88 --END Bug#2200539
89
90 END GMD_ROUT_MIGRATION;
[Home] [Help]