[Home] [Help]
PACKAGE BODY: APPS.AD_MO_UTIL_PKG
Source
1 PACKAGE BODY ad_mo_util_pkg AS
2 /* $Header: admoutlb.pls 120.11 2012/04/06 09:42:23 sstomar ship $ */
3
4 PROCEDURE r12_moac_conv
5 (p_prod_user_name in VARCHAR2,
6 p_view_name in VARCHAR2,
7 p_prod_tab_name in VARCHAR2,
8 p_prod_schema_name in VARCHAR2,
9 p_application_short_name in VARCHAR2,
10 p_apps_user_name in VARCHAR2,
11 p_sec_policy_name in VARCHAR2,
12 p_action in VARCHAR2)
13
14 IS
15
16 l_schema_owner VARCHAR2(50);
17 l_secured_object VARCHAR2(30);
18 l_base_object VARCHAR2(30);
19 l_edition_enabled VARCHAR2(1);
20 l_exist number := 0;
21 l_evname VARCHAR2(30);
22
23 v_synonym varchar2(100);
24 v_target varchar2(100);
25 v_sys_table varchar2(100);
26 handle INTEGER;
27 sql_stmt varchar2(2000);
28
29 object_exists EXCEPTION;
30 object_not_exists EXCEPTION;
31 synonym_not_exists EXCEPTION;
32 pragma exception_init(object_exists, -955);
33 pragma exception_init(object_not_exists, -942);
34 pragma exception_init(synonym_not_exists, -1434);
35
36 policy_flag VARCHAR2(10) := 'FALSE';
37
38 CURSOR c_policy_exists
39 ( xp_object_schema VARCHAR2
40 , xp_object_name VARCHAR2
41 , xp_policy_name VARCHAR2
42 )
43 IS
44 SELECT 'TRUE'
45 FROM sys.dual
46 WHERE EXISTS
47 (SELECT 1
48 FROM dba_policies
49 WHERE object_owner = UPPER(xp_object_schema)
50 AND object_name = UPPER(xp_object_name)
51 AND policy_name = UPPER(xp_policy_name)
52 );
53
54 BEGIN
55
56 handle := DBMS_SQL.OPEN_CURSOR;
57 v_synonym :=p_view_name ;
58
59 --
60 -- get the product name
61 --
62 v_sys_table := p_prod_schema_name ||'.'|| p_prod_tab_name;
63
64 --
65 -- Validate action passed is valid
66 --
67
68 IF (upper(p_action) NOT IN ('MOVW', 'MODFV', 'MOSYN', 'MOAOL', 'MOVPD')) THEN
69 raise_application_error(-20000, 'AD_MO_UTIL_PKG - Invalid action');
70 END IF;
71
72 -- See whether APPS is edition enabled or not
73 SELECT editions_enabled
74 INTO l_edition_enabled
75 FROM dba_users
76 WHERE username=upper(p_apps_user_name);
77
78
79 --
80 -- Drop the view if action is MOVW
81 --
82
83 IF (upper(p_action) = 'MOVW') THEN
84 sql_stmt:= 'drop view ' || p_apps_user_name || '.'|| p_view_name;
85 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
86
87 --
88 -- Replace db default
89 --
90
91 ELSIF ( upper(p_action) = 'MODFV') THEN
92 sql_stmt:='alter table ' || v_sys_table ||
93 ' modify ( org_id DEFAULT NULL )';
94 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
95
96 -- NOTE:
97 -- sstomar: No need to (re)generate Editioning View if we are just
98 -- changing column with DEFAULT value.
99 -- AD_ZD_TABLE.generate_ev API only needs to be called when a new
100 -- column to a table is being added.
101 --
102 sql_stmt:='alter synonym ' || p_apps_user_name || '.' ||
103 p_prod_tab_name ||' compile';
104 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
105
106 --
107 -- Create synonym
108 --
109
110 elsif (upper(p_action) = 'MOSYN') then
111
112 v_target := v_sys_table;
113 if (l_edition_enabled = 'Y') then
114 l_evname := ad_zd_table.ev_view(p_prod_tab_name);
115 SELECT count(1)
116 INTO l_exist
117 FROM dba_editioning_views
118 WHERE view_name=l_evname
119 AND owner=upper(p_prod_schema_name);
120
121 if (l_exist > 0) then
122 v_target := p_prod_schema_name||'.'||l_evname;
123 end if;
124 end if;
125
126 -- SSTOMAR: Why we want to drop synonym ???
127 --
128 -- First drop synonym , needed to refresh VPD policy
129 sql_stmt:= 'drop synonym ' || p_apps_user_name || '.'||v_synonym;
130 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
131
132 -- Then create synonym
133 sql_stmt:='create or replace synonym ' || p_apps_user_name || '.' ||
134 v_synonym || ' for ' || v_target;
135 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
136
137 --
138 -- Delete view from AOL data dictionary
139 --
140
141 ELSIF ( upper(p_action) = 'MOAOL') THEN
142
143 Begin
144
145 -- Delete from FND_VIEWS
146
147 Fnd_Dictionary_Pkg.RemoveView(p_application_short_name, p_view_name);
148 EXCEPTION
149 WHEN OTHERS THEN NULL;
150 end;
151
152 --
153 -- Add the security policy.
154 --
155
156 ELSIF (upper(p_action) = 'MOVPD') THEN
157
158 OPEN c_policy_exists(p_apps_user_name, v_synonym, p_sec_policy_name);
159 FETCH c_policy_exists INTO policy_flag;
160
161 -- if policy aleady exists then drop the policy.
162
163 IF policy_flag='TRUE' THEN
164 DBMS_RLS.DROP_POLICY( p_apps_user_name,
165 v_synonym,
166 p_sec_policy_name);
167 END IF;
168
169 IF (upper(v_synonym) in ('AP_EXPENSE_REPORTS',
170 'AP_EXPENSE_REPORT_PARAMS')) THEN
171
172 dbms_rls.add_policy (p_apps_user_name,
173 v_synonym,
174 p_sec_policy_name,
175 p_apps_user_name,
176 'AP_WEB_UTILITIES_PKG.ORGSECURITY', -- OIC policy
177 'SELECT, INSERT, UPDATE, DELETE',
178 TRUE,
179 TRUE,
180 FALSE,
181 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
182
183 ELSIF (upper(v_synonym) = 'QP_LIST_HEADERS_B') THEN
184
185 dbms_rls.add_policy (p_apps_user_name,
186 v_synonym,
187 p_sec_policy_name,
188 p_apps_user_name,
189 'QP_SECURITY.QP_ORG_SECURITY', -- QP policy
190 'SELECT, INSERT, UPDATE, DELETE',
191 TRUE,
192 TRUE,
193 FALSE,
194 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
195
196 ELSIF (upper(v_synonym) = 'AR_PAYMENT_SCHEDULES') THEN
197
198 dbms_rls.add_policy (p_apps_user_name,
199 v_synonym,
200 p_sec_policy_name,
201 p_apps_user_name,
202 'MO_GLOBAL.ORG_SECURITY_GLOBAL',
203 'SELECT, INSERT, UPDATE, DELETE',
204 TRUE,
205 TRUE,
206 FALSE,
207 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
208
209 ELSE
210 dbms_rls.add_policy (p_apps_user_name,
211 v_synonym,
212 p_sec_policy_name,
213 p_apps_user_name,
214 'MO_GLOBAL.ORG_SECURITY', -- Standard MO VPD policy
215 'SELECT, INSERT, UPDATE, DELETE',
216 TRUE,
217 TRUE,
218 FALSE,
219 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
220 END IF;
221
222 CLOSE c_policy_exists;
223
224 END IF;
225
226 EXCEPTION
227 WHEN object_exists then null;
228 WHEN object_not_exists then null;
229 WHEN synonym_not_exists then
230
231 --check for MOSYN action
232
233 IF upper(p_action) = 'MOSYN' then
234 sql_stmt:='create or replace synonym ' || p_apps_user_name || '.' ||
235 v_synonym || ' for ' || v_target;
236
237 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
238 END IF;
239
240 WHEN OTHERS THEN
241
242 raise_application_error(-20000, sqlerrm ||': ' ||
243 'AD_MO_UTIL_PKG - Error in creating ' || p_view_name);
244
245
246 END r12_moac_conv;
247 END ad_mo_util_pkg;