PACKAGE BODY: APPS.POR_R12_UPGRADE_PVT

Source

1 PACKAGE BODY POR_R12_UPGRADE_PVT AS
2 /* $Header: PORV12UB.pls 120.1 2006/07/14 23:20:07 dkfchan noship $*/
3 
4 PROCEDURE upgrade_por_profiles IS
5 
6   l_continue boolean;
7   l_batchsize number := 1000;
8 
9   l_old_ovr_requester_profile_id number;
10   l_new_ovr_requester_profile_id number;
11 
12   l_old_ovr_location_profile_id number;
13   l_new_ovr_location_profile_id number;
14 
15   l_old_days_need_by_profile_id number;
16   l_new_days_need_by_profile_id number;
17 
18   l_new_def_exist boolean := false;
19 
20 BEGIN
21 
22   begin
23 
24     select profile_option_id
25       into l_old_ovr_requester_profile_id
26       from fnd_profile_options
27      where profile_option_name = 'ICX_REQ_OVERRIDE_REQUESTOR_CODE';
28 
29     select profile_option_id
30       into l_new_ovr_requester_profile_id
31       from fnd_profile_options
32      where profile_option_name = 'POR_OVERRIDE_REQUESTER';
33 
34     select profile_option_id
35       into l_old_ovr_location_profile_id
36       from fnd_profile_options
37      where profile_option_name = 'ICX_REQ_OVERRIDE_LOCATION_FLAG';
38 
39     select profile_option_id
40       into l_new_ovr_location_profile_id
41       from fnd_profile_options
42      where profile_option_name = 'POR_OVERRIDE_LOCATION';
43 
44     select profile_option_id
45       into l_old_days_need_by_profile_id
46       from fnd_profile_options
47      where profile_option_name = 'ICX_DAYS_NEEDED_BY';
48 
49     select profile_option_id
50       into l_new_days_need_by_profile_id
51       from fnd_profile_options
52      where profile_option_name = 'POR_DAYS_NEEDED_BY';
53 
54     l_new_def_exist := true;
55 
56   exception
57 
58     when others then
59       l_new_def_exist := false;
60 
61   end;
62 
63   if (l_new_def_exist) then
64 
65     l_continue := TRUE;
66 
67     -- Update non site level profile values
68 
69     WHILE(l_continue) LOOP
70 
71         update fnd_profile_option_values v1
72            set v1.profile_option_id = decode(v1.profile_option_id,
73                                              l_old_ovr_requester_profile_id,
74                                              l_new_ovr_requester_profile_id,
75                                              l_old_ovr_location_profile_id,
76                                              l_new_ovr_location_profile_id,
77                                              l_old_days_need_by_profile_id,
78                                              l_new_days_need_by_profile_id, -1)
79          where v1.profile_option_id in (l_old_ovr_requester_profile_id,
80                                         l_old_ovr_location_profile_id,
81                                         l_old_days_need_by_profile_id)
82            and v1.level_id <> 10001
83            and not exists (select 1
84                              from fnd_profile_option_values v2
85                             where v2.profile_option_id =
86                                 decode(v1.profile_option_id,
87                                        l_old_ovr_requester_profile_id,
88                                        l_new_ovr_requester_profile_id,
89                                        l_old_ovr_location_profile_id,
90                                        l_new_ovr_location_profile_id,
91                                        l_old_days_need_by_profile_id,
92                                        l_new_days_need_by_profile_id, -1)
93                               and v2.level_id = v1.level_id
94                               and v2.level_value = v1.level_value)
95            and rownum <= l_batchsize;
96 
97          IF (SQL%ROWCOUNT < l_batchsize) THEN
98            l_continue := FALSE;
99          END IF;
100 
101          COMMIT;
102 
103     END LOOP;
104 
105     -- Update site level profile value
106 
107     update fnd_profile_option_values p1
108        set p1.profile_option_value =
109            (select p2.profile_option_value
110               from fnd_profile_option_values p2
111              where p2.profile_option_id = decode(p1.profile_option_id,
112                                              l_new_ovr_requester_profile_id,
113                                              l_old_ovr_requester_profile_id,
114                                              l_new_ovr_location_profile_id,
115                                              l_old_ovr_location_profile_id,
116                                              l_new_days_need_by_profile_id,
117                                              l_old_days_need_by_profile_id, -1)
118                and p2.level_id = 10001)
119      where p1.profile_option_id in (l_new_ovr_requester_profile_id,
120                                     l_new_ovr_location_profile_id,
121                                     l_new_days_need_by_profile_id)
122        and p1.level_id = 10001;
123 
124     COMMIT;
125 
126   end if;
127 
128 END upgrade_por_profiles;
129 
130 END POR_R12_UPGRADE_PVT;