[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;