DBA Data[Home] [Help]

PACKAGE BODY: APPS.MSC_ORG_SECURITY

Source


1 PACKAGE BODY MSC_org_security AS
2 /* $Header: MSCORGSB.pls 120.3 2006/10/05 20:35:55 pabram noship $  */
3 PROCEDURE set_org_security(
4                       p_selected_orgs      IN  varchar2
5                      ,p_resp_id            IN  varchar2
6                      ) IS
7 i number := 1;
8 v_len number;
9 one_len number;
10 org_id number;
11 inst_id number;
12 org_id1 number;
13 inst_id1 number;
14 org_inst_id varchar2(100); --5515434 bugfix
15 org_assigned number :=0;
16 record_exists number := 0;
17 
18 -- the other columns have to be null for the record to be considered a
19 -- org security record
20 
21      CURSOR current_orgs (p_resp_id varchar2) is
22      select organization_id, sr_instance_id
23      from msc_selected_orgs_v mso
24       where mso.responsibility_id = p_resp_id;
25 
26      CURSOR org_assigned_c(
27                                    lorg_id     number,
28                                    linst_id    number) IS
29      select organization_id
30        from msc_selected_orgs_v mso
31       where mso.responsibility_id   = trim(p_resp_id)
32         and mso.organization_id     = lorg_id
33         and mso.sr_instance_id      = linst_id;
34 
35 
36      CURSOR record_exists_c(
37                                    lorg_id1     number,
38                                    linst_id1    number) IS
39      select 1
40        from msc_org_access
41       where responsibility_id     = to_number(trim(p_resp_id))
42         and organization_id     = lorg_id1
43         and sr_instance_id      = linst_id1;
44 BEGIN
45     -- terminate organizations that are in the table but have not been selected
46     -- to be assigned
47     OPEN current_orgs (p_resp_id);
48     LOOP
49       --dbms_output.put_line('starting loop');
50       FETCH current_orgs INTO org_id,
51                             inst_id;
52       EXIT WHEN current_orgs%NOTFOUND;
53        --dbms_output.put_line('before creating org_inst');
54        org_inst_id := to_char(org_id) || ':' || to_char(inst_id);
55        --dbms_output.put_line('org_id =' || org_id);
56        --dbms_output.put_line('inst_id =' || inst_id);
57        --dbms_output.put_line('instr value ' ||
58        --                     instr(p_selected_orgs, org_inst_id,1));
59        if instr(p_selected_orgs, org_inst_id,1) = 0 then
60             update_row(org_id,
61                        inst_id,
62                        to_number(p_resp_id),
63                        724,
64                        sysdate,
65                        sysdate-1,
66                        'REMOVE');
67        end if;
68      end loop;
69      close current_orgs;
70      --dbms_output.put_line('past current orgs cursor');
71      -- parse the p_selected_orgs, the format is '201:207;201:208;'
72     -- assign orgs that have been selected
73     v_len := length(p_selected_orgs);
74     while v_len > 1 loop
75 
76       one_len := instr(p_selected_orgs,';',1,i+1)-
77                           instr(p_selected_orgs,';',1 ,i)-1;
78 
79       org_inst_id := trim(substr(p_selected_orgs,
80                            instr(p_selected_orgs,';',1,i)+1,one_len));
81 
82       org_id := to_number(
83                       trim(substr(org_inst_id,1,
84                              instr(org_inst_id,':',1)-1)));
85       org_id1 := org_id;
86       inst_id := to_number( trim(substr(org_inst_id,instr(
87                             org_inst_id,':',1)+1)));
88       inst_id1 := inst_id;
89 
90       i := i+1;
91       v_len := v_len - one_len-1;
92       --dbms_output.put_line('p_resp_id' || p_resp_id);
93       --dbms_output.put_line('org_id' || org_id);
94       --dbms_output.put_line('inst_id' || inst_id);
95       OPEN org_assigned_c(org_id, inst_id);
96       FETCH  org_assigned_c into org_assigned;
97       --dbms_output.put_line('rule id is ' || org_assigned);
98       if ( org_assigned_c%notfound) then
99       --dbms_output.put_line('org not found ' || org_id || '  '|| inst_id);
100       -- determine if the record exists
101       -- if record exists then update the record
102       -- else insert record
103          OPEN record_exists_c(org_id, inst_id);
104          FETCH  record_exists_c into record_exists;
105          --dbms_output.put_line('rule id is ' || org_assigned);
106          if ( record_exists_c%notfound) then
107            --dbms_output.put_line('record not found ' || org_id || '  '||
108            --                     inst_id);
109            insert_row(org_id,
110                       inst_id,
111                       to_number(p_resp_id),
112                       724,
113                       sysdate,
114                       to_date(null)
115                      );
116         else
117            --dbms_output.put_line('record found ' || org_id || '  '|| inst_id);
118            update_row( org_id1,
119                        inst_id1,
120                        to_number(p_resp_id),
121                        724,
122                        sysdate,
123                        to_date(null),
124                        'ADD'
125                       );
126         end if;
127        CLOSE  record_exists_c;
128         --else
129         --dbms_output.put_line('found ' || org_id || '  '|| inst_id);
130       end if;
131       CLOSE  org_assigned_c;
132     end loop ;
133 
134 -- need to add exception clause
135 Exception when others then
136       raise_application_error(-20000,sqlerrm||':'||
137                               'parameters passed: ' ||' ' ||
138                               'p_selected_orgs =>' || p_selected_orgs ||'  ' ||
139                               'p_resp_id=>' || p_resp_id);
140 
141 END set_org_security;
142 
143 
144 procedure get_resp_id   (p_resp            IN  varchar2,
145                          p_resp_id         OUT NoCopy varchar2) is
146 -- this procedure gets the responsibility id given the responsibility name
147 -- it returns '-1' if no responsibility exists with the given name in MSC
148 lquery_id number ;
149 cursor resp_c is
150 select resp.responsibility_id
151 from fnd_responsibility_tl resp
152 where resp.responsibility_name = p_resp;
153 begin
154   open resp_c;
155   fetch resp_c into p_resp_id;
156 
157   if resp_c%notfound then
158     p_resp_id := '-1';
159   end if;
160   close resp_c;
161 Exception when others then
162       raise_application_error(-20000,sqlerrm||':'||
163                               'parameter passed: ' ||' ' ||
164                               p_resp);
165 end get_resp_id;
166 
167 
168 procedure insert_row (p_organization_id in number,
169                       p_sr_instance_id  in number,
170                       p_responsibility_id in number,
171                       p_resp_appl_id in number,
172                       p_eff_from_date in date,
173                       p_eff_to_date in date)
174 
175 is
176 v_statement varchar2(4000);
177 
178 cursor c_resp_appl_id is
179 select application_id
180 from fnd_responsibility
181 where responsibility_id = p_responsibility_id;
182 
183 l_resp_appl_id number;
184 begin
185   open c_resp_appl_id;
186   fetch c_resp_appl_id into l_resp_appl_id;
187   close c_resp_appl_id;
188 
189   if (l_resp_appl_id is null) then
190     l_resp_appl_id := p_resp_appl_id;
191   end if;
192 
193  v_statement :=  'insert into msc_org_access(' ||
194             'ORGANIZATION_ID' ||
195            ',SR_INSTANCE_ID' ||
196            ',RESPONSIBILITY_ID' ||
197            ',RESP_APPLICATION_ID' ||
198            ',EFFECTIVE_FROM_DATE' ||
199            ',EFFECTIVE_TO_DATE' ||
200            ',LAST_UPDATE_DATE' ||
201            ',LAST_UPDATED_BY' ||
202            ',CREATION_DATE' ||
203            ',CREATED_BY' ||
204            ',LAST_UPDATE_LOGIN' ||
205            ')'  ||
206            'values ( ' ||
207            ' :1' ||
208            ',:2' ||
209            ',:3' ||
210            ',:4' ||
211            ',:5' ||
212            ',:6' ||
213            ',:7' ||
214            ',:8' ||
215            ',:9' ||
216            ',:10' ||
217            ',:11' ||
218            ')';
219 EXECUTE IMMEDIATE v_statement USING
220         p_organization_id,
221         p_sr_instance_id,
222         p_responsibility_id,
223         l_resp_appl_id,
224         p_eff_from_date,
225         p_eff_to_date,
226         sysdate,
227         fnd_global.user_id,
228         sysdate,
229         fnd_global.user_id,
230         fnd_global.user_id;
231 
232 EXCEPTION
233     when others then
234       raise_application_error(-20000,sqlerrm||':'||v_statement  ||
235         'p_organization_id'  ||p_organization_id ||' ' ||
236         'p_sr_instance_id'    ||p_sr_instance_id ||' ' ||
237         'p_responsibility_id' ||p_responsibility_id ||' ' ||
238         'p_resp_application_id' ||p_resp_appl_id ||' ' ||
239         'p_effective_from_date' ||p_eff_from_date ||' ' ||
240         'p_effective_to_date' || p_eff_to_date);
241 end insert_row;
242 
243 procedure update_row (p_organization_id in number,
244                       p_sr_instance_id  in number,
245                       p_responsibility_id in number,
246                       p_resp_appl_id in number,
247                       p_eff_from_date in date,
248                       p_eff_to_date in date,
249                       p_action varchar2)
250 
251 is
252 v_statement varchar2(2000);
253 
254 cursor c_resp_appl_id is
255 select application_id
256 from fnd_responsibility
257 where responsibility_id = p_responsibility_id;
258 
259 l_resp_appl_id number;
260 begin
261 
262   open c_resp_appl_id;
263   fetch c_resp_appl_id into l_resp_appl_id;
264   close c_resp_appl_id;
265 
266   if (l_resp_appl_id is null) then
267     l_resp_appl_id := p_resp_appl_id;
268   end if;
269 
270  v_statement :=  'update msc_org_access set' ;
271  if p_action = 'ADD' then
272    v_statement := v_statement ||' effective_from_date= :1,';
273  end if;
274 
275    v_statement := v_statement ||
276            '    effective_to_date= :2,' ||
277            '    LAST_UPDATE_DATE = :3,' ||
278            '    LAST_UPDATED_BY  = :4,' ||
279            '    LAST_UPDATE_LOGIN= :5 ' ||
280            'where responsibility_id = :6 and ' ||
281                  'organization_id   = :7 and ' ||
282                  'sr_instance_id    = :8 and ' ||
283                  'resp_application_id = :9';
284 if p_action='ADD' then
285      EXECUTE IMMEDIATE v_statement USING
286                   p_eff_from_date,
287                   p_eff_to_date,
288                   sysdate,
289                   fnd_global.user_id,
290                   fnd_global.user_id,
291                   p_responsibility_id,
292                   p_organization_id,
293                   p_sr_instance_id,
294                   l_resp_appl_id;
295 else
296      EXECUTE IMMEDIATE v_statement USING
297                   p_eff_to_date,
298                   sysdate,
299                   fnd_global.user_id,
300                   fnd_global.user_id,
301                   p_responsibility_id,
302                   p_organization_id,
303                   p_sr_instance_id,
304                   l_resp_appl_id;
305 end if;
306 EXCEPTION
307     when others then
308       raise_application_error(-20000,sqlerrm||':'||v_statement||
309         'p_organization_id'  ||p_organization_id ||' ' ||
310         'p_sr_instance_id'    ||p_sr_instance_id ||' ' ||
311         'p_responsibility_id' ||p_responsibility_id ||' ' ||
312         'p_resp_application_id' ||p_resp_appl_id ||' ' ||
313         'p_effective_from_date' ||p_eff_from_date ||' ' ||
314         'p_effective_to_date' || p_eff_to_date);
315 end update_row;
316 
317 
318 
319 end msc_org_security;