[Home] [Help]
PACKAGE BODY: APPS.AD_MO_UTIL_PKG
Source
1 PACKAGE BODY ad_mo_util_pkg AS
2 /* $Header: admoutlb.pls 120.7 2006/08/08 23:41:39 hxue noship $ */
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
20 v_synonym varchar2(100);
21 v_sys_table varchar2(100);
22 handle INTEGER;
23 sql_stmt varchar2(2000);
24
25 object_exists EXCEPTION;
26 object_not_exists EXCEPTION;
27 synonym_not_exists EXCEPTION;
28 pragma exception_init(object_exists, -955);
29 pragma exception_init(object_not_exists, -942);
30 pragma exception_init(synonym_not_exists, -1434);
31
32 policy_flag VARCHAR2(10) := 'FALSE';
33
34 CURSOR c_policy_exists
35 ( xp_object_schema VARCHAR2
36 , xp_object_name VARCHAR2
37 , xp_policy_name VARCHAR2
38 )
39 IS
40 SELECT 'TRUE'
41 FROM sys.dual
42 WHERE EXISTS
43 (SELECT 1
44 FROM dba_policies
45 WHERE object_owner = UPPER(xp_object_schema)
46 AND object_name = UPPER(xp_object_name)
47 AND policy_name = UPPER(xp_policy_name)
48 );
49
50 BEGIN
51
52 handle := DBMS_SQL.OPEN_CURSOR;
53 v_synonym :=p_view_name ;
54
55 --
56 -- get the product name
57 --
58 v_sys_table := p_prod_schema_name ||'.'|| p_prod_tab_name;
59
60 --
61 -- Validate action passed is valid
62 --
63
64 IF (upper(p_action) NOT IN ('MOVW', 'MODFV', 'MOSYN', 'MOAOL', 'MOVPD')) THEN
65 raise_application_error(-20000, 'AD_MO_UTIL_PKG - Invalid action');
66 END IF;
67
68 --
69 -- Drop the view if action is MOVW
70 --
71
72 IF (upper(p_action) = 'MOVW') THEN
73 sql_stmt:= 'drop view ' || p_apps_user_name || '.'|| p_view_name;
74 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
75
76 --
77 -- Replace db default
78 --
79
80 ELSIF ( upper(p_action) = 'MODFV') THEN
81 sql_stmt:='alter table ' || v_sys_table ||
82 ' modify ( org_id DEFAULT NULL )';
83 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
84
85 sql_stmt:='alter synonym ' || p_apps_user_name || '.' ||
86 p_prod_tab_name ||' compile';
87 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
88
89 --
90 -- Create synonym
91 --
92
93 ELSIF (upper(p_action) = 'MOSYN') THEN
94
95 -- First drop synonym , needed to refresh VPD policy
96
97 sql_stmt:= 'drop synonym ' || p_apps_user_name || '.'||v_synonym;
98 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
99
100 -- Then create synonym
101
102 sql_stmt:='create or replace synonym ' || p_apps_user_name || '.' ||
103 v_synonym || ' for ' || v_sys_table;
104 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
105
106 --
107 -- Delete view from AOL data dictionary
108 --
109
110 ELSIF ( upper(p_action) = 'MOAOL') THEN
111
112 Begin
113
114 -- Delete from FND_VIEWS
115
116 Fnd_Dictionary_Pkg.RemoveView(p_application_short_name, p_view_name);
117 EXCEPTION
118 WHEN OTHERS THEN NULL;
119 end;
120
121 --
122 -- Add the security policy.
123 --
124
125 ELSIF (upper(p_action) = 'MOVPD') THEN
126
127 OPEN c_policy_exists(p_apps_user_name, v_synonym, p_sec_policy_name);
128 FETCH c_policy_exists INTO policy_flag;
129
130 -- if policy aleady exists then drop the policy.
131
132 IF policy_flag='TRUE' THEN
133 DBMS_RLS.DROP_POLICY( p_apps_user_name,
134 v_synonym,
135 p_sec_policy_name);
136 END IF;
137
138 IF (upper(v_synonym) in ('AP_EXPENSE_REPORTS',
139 'AP_EXPENSE_REPORT_PARAMS')) THEN
140
141 dbms_rls.add_policy (p_apps_user_name,
142 v_synonym,
143 p_sec_policy_name,
144 p_apps_user_name,
145 'AP_WEB_UTILITIES_PKG.ORGSECURITY', -- OIC policy
146 'SELECT, INSERT, UPDATE, DELETE',
147 TRUE,
148 TRUE,
149 FALSE,
150 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
151
152 ELSIF (upper(v_synonym) = 'QP_LIST_HEADERS_B') THEN
153
154 dbms_rls.add_policy (p_apps_user_name,
155 v_synonym,
156 p_sec_policy_name,
157 p_apps_user_name,
158 'QP_SECURITY.QP_ORG_SECURITY', -- QP policy
159 'SELECT, INSERT, UPDATE, DELETE',
160 TRUE,
161 TRUE,
162 FALSE,
163 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
164
165 ELSIF (upper(v_synonym) = 'AR_PAYMENT_SCHEDULES') THEN
166
167 dbms_rls.add_policy (p_apps_user_name,
168 v_synonym,
169 p_sec_policy_name,
170 p_apps_user_name,
171 'MO_GLOBAL.ORG_SECURITY_GLOBAL',
172 'SELECT, INSERT, UPDATE, DELETE',
173 TRUE,
174 TRUE,
175 FALSE,
176 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
177
178 ELSE
179 dbms_rls.add_policy (p_apps_user_name,
180 v_synonym,
181 p_sec_policy_name,
182 p_apps_user_name,
183 'MO_GLOBAL.ORG_SECURITY', -- Standard MO VPD policy
184 'SELECT, INSERT, UPDATE, DELETE',
185 TRUE,
186 TRUE,
187 FALSE,
188 DBMS_RLS.SHARED_CONTEXT_SENSITIVE);
189 END IF;
190
191 CLOSE c_policy_exists;
192
193 END IF;
194
195 EXCEPTION
196 WHEN object_exists then null;
197 WHEN object_not_exists then null;
198 WHEN synonym_not_exists then
199
200 --check for MOSYN action
201
202 IF upper(p_action) = 'MOSYN' then
203 sql_stmt:='create or replace synonym ' || p_apps_user_name || '.'||
204 v_synonym || ' for ' || v_sys_table;
205 dbms_sql.parse(handle,sql_stmt,DBMS_SQL.V7);
206 END IF;
207
208 WHEN OTHERS THEN
209
210 raise_application_error(-20000, sqlerrm ||': ' ||
211 'AD_MO_UTIL_PKG - Error in creating ' || p_view_name);
212
213
214 END r12_moac_conv;
215 END ad_mo_util_pkg;