[Home] [Help]
PACKAGE BODY: APPS.OKE_K_SECURED_VIEWS_PKG
Source
1 PACKAGE BODY OKE_K_SECURED_VIEWS_PKG AS
2 /* $Header: OKEKSVB.pls 120.2.12000000.2 2007/02/12 11:47:47 nnadahal ship $ */
3
4 --
5 -- Private functions and procedures
6 --
7 PROCEDURE Print_Text_Buffer
8 ( X_Target IN NUMBER
9 , X_Text_Buffer IN VARCHAR2
10 , X_Wrap_Text IN BOOLEAN
11 ) IS
12
13 i NUMBER;
14 j NUMBER;
15 LineWidth NUMBER := 77;
16
17 BEGIN
18
19 FND_FILE.NEW_LINE( X_Target , 1 );
20
21 IF ( X_Wrap_Text ) THEN
22 i := 1;
23 j := LENGTH( X_Text_Buffer );
24
25 LOOP
26 FND_FILE.PUT_LINE( X_Target
27 , SUBSTR( X_Text_Buffer , i , LineWidth ) );
28 i := i + LineWidth;
29 EXIT WHEN i >= j;
30 END LOOP;
31 ELSE
32 FND_FILE.PUT_LINE( X_Target , X_Text_Buffer );
33 END IF;
34
35 FND_FILE.NEW_LINE( X_Target , 1 );
36
37 END;
38
39
40 PROCEDURE Generate_Secured_View
41 ( X_Object_Name IN VARCHAR2
42 , X_Header_ID_Col IN VARCHAR2
43 , X_Create_History_View IN BOOLEAN
44 , X_Error_Buf IN OUT NOCOPY VARCHAR2
45 ) IS
46
47 RCSHeader VARCHAR2(240) := '$Header: OKEKSVB.pls 120.2.12000000.2 2007/02/12 11:47:47 nnadahal ship $';
48 ColumnList VARCHAR2(10000);
49 SelectList VARCHAR2(30000);
50 DecodePair VARCHAR2(10000);
51 SecuredValue VARCHAR2(80);
52 ViewSyntax VARCHAR2(32500);
53 UnsecViewName VARCHAR2(30);
54 SecViewName VARCHAR2(30);
55 ApplsysSchema VARCHAR2(30);
56
57 CURSOR ObjAttr
58 ( C_Object_Name VARCHAR2
59 ) IS
60 SELECT oa.attribute_code
61 , oa.datatype
62 , oap.securable_flag
63 FROM oke_object_attributes_b oa
64 , oke_object_attributes_b oap
65 WHERE oa.database_object_name = C_Object_Name
66 AND oap.database_object_name = oa.database_object_name
67 AND oa.view_column_flag = 'Y'
68 AND oap.attribute_code = nvl( oa.parent_attribute_code
69 , oa.attribute_code )
70 ORDER BY oa.attribute_code;
71
72 CURSOR SecRole
73 ( C_Object_Name VARCHAR2
74 , C_Attribute_Code VARCHAR2
75 ) IS
76 SELECT Role_ID
77 FROM oke_compiled_access_rules
78 WHERE secured_object_name = C_Object_Name
79 AND attribute_code = C_attribute_code
80 AND access_level = OKE_K_SECURITY_PKG.G_NO_ACCESS
81 ORDER BY Role_ID;
82
83 BEGIN
84
85 SELECT MIN(ou.Oracle_Username)
86 INTO ApplsysSchema
87 FROM fnd_product_installations pi
88 , fnd_oracle_userid ou
89 WHERE ou.Oracle_ID = pi.Oracle_ID
90 AND Application_ID = 0;
91
92 ColumnList := 'ROW_ID , CREATION_DATE , CREATED_BY , LAST_UPDATE_DATE , ' ||
93 'LAST_UPDATED_BY , LAST_UPDATE_LOGIN , ATTRIBUTE_CATEGORY , ' ||
94 'ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ' ||
95 'ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ' ||
96 'ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ' ||
97 'ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ' ||
98 'ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 , ' ||
99 'MAJOR_VERSION , MINOR_VERSION , VERSION_DISP ';
100 SelectList := 'K.ROW_ID , K.CREATION_DATE , K.CREATED_BY , K.LAST_UPDATE_DATE , ' ||
101 'K.LAST_UPDATED_BY , K.LAST_UPDATE_LOGIN , K.ATTRIBUTE_CATEGORY , ' ||
102 'K.ATTRIBUTE1 , K.ATTRIBUTE2 , K.ATTRIBUTE3 , ' ||
103 'K.ATTRIBUTE4 , K.ATTRIBUTE5 , K.ATTRIBUTE6 , ' ||
104 'K.ATTRIBUTE7 , K.ATTRIBUTE8 , K.ATTRIBUTE9 , ' ||
105 'K.ATTRIBUTE10 , K.ATTRIBUTE11 , K.ATTRIBUTE12 , ' ||
106 'K.ATTRIBUTE13 , K.ATTRIBUTE14 , K.ATTRIBUTE15 , ' ||
107 'K.MAJOR_VERSION , K.MINOR_VERSION , K.VERSION_DISP ';
108
109 FOR ObjAttrRec IN ObjAttr( X_Object_Name ) LOOP
110
111 ColumnList := ColumnList || ' , ' || ObjAttrRec.Attribute_Code;
112
113 IF ( ObjAttrRec.Securable_Flag = 'N' ) THEN
114
115 SelectList := SelectList || ' , K.' || ObjAttrRec.Attribute_Code;
116
117 ELSE
118
119 IF ( ObjAttrRec.DataType = 'NUMBER' ) THEN
120 SecuredValue := 'TO_NUMBER(NULL)';
121 ELSIF ( ObjAttrRec.DataType = 'DATE' ) THEN
122 SecuredValue := 'TO_DATE(NULL)';
123 ELSE
124 SecuredValue := 'NULL';
125 END IF;
126
127 DecodePair := NULL;
128
129 FOR SecRoleRec IN SecRole( X_Object_Name
130 , ObjAttrRec.Attribute_Code ) LOOP
131
132 DecodePair := DecodePair || TO_CHAR(SecRoleRec.Role_ID) ||
133 ' , ' || SecuredValue || ' , ';
134
135 END LOOP;
136
137 IF ( DecodePair IS NOT NULL ) THEN
138
139 SelectList := SelectList ||
140 ' , DECODE( OKE_K_SECURITY_PKG.GET_K_ROLE( K.' || X_Header_ID_Col ||
141 ' ) , ' || DecodePair || 'K.' || ObjAttrRec.Attribute_Code || ' ) ';
142
143 ELSE
144
145 SelectList := SelectList || ' , K.' || ObjAttrRec.Attribute_Code;
146
147 END IF;
148
149 END IF;
150
151 END LOOP;
152
153 UnsecViewName := X_Object_Name || '_FULL_V';
154 SecViewName := X_Object_Name || '_SECURE_V';
155
156 ViewSyntax :=
157 'CREATE OR REPLACE FORCE VIEW ' || SecViewName || ' ( ' ||
158 ColumnList || ' ) AS SELECT /* ' || RCSHeader || ' */ ' || SelectList ||
159 ' FROM ' || UnsecViewName || ' K ,' ||
160 ' ( SELECT ID K_HDR_ID ,' ||
161 ' OKE_K_SECURITY_PKG.GET_K_ACCESS( ID ) K_ACCESS ' ||
162 ' FROM OKC_K_HEADERS_B ) ACC' ||
163 ' WHERE ACC.K_HDR_ID = K.' || X_Header_ID_Col ||
164 ' AND ACC.K_ACCESS IN ( ''EDIT'' , ''VIEW'' )' ;
165
166 -- ||
167 -- ' AND ( OKE_UTILS.CROSS_ORG_ACCESS = ''Y''' ||
168 -- ' OR NVL(K.AUTHORING_ORG_ID , -99) = OKE_UTILS.ORG_ID )';
169
170 Print_Text_Buffer( FND_FILE.OUTPUT , ViewSyntax , TRUE );
171
172 AD_DDL.DO_DDL( ApplsysSchema
173 , 'OKE'
174 , AD_DDL.CREATE_VIEW
175 , ViewSyntax
176 , SecViewName
177 );
178
179 FND_MESSAGE.SET_NAME('OKE' , 'OKE_SEC_GEN_VIEW_SUCC');
180 FND_MESSAGE.SET_TOKEN('VIEW' , SecViewName);
181 Print_Text_Buffer( FND_FILE.LOG , FND_MESSAGE.GET , FALSE );
182
183 IF ( X_Create_History_View ) THEN
184
185 UnsecViewName := X_Object_Name || '_FULL_HV';
186 SecViewName := X_Object_Name || '_SECURE_HV';
187
188 ViewSyntax :=
189 'CREATE OR REPLACE FORCE VIEW ' || SecViewName || ' ( ' ||
190 ColumnList || ' ) AS SELECT /* ' || RCSHeader || ' */ ' || SelectList ||
191 ' FROM ' || UnsecViewName || ' K ,' ||
192 ' ( SELECT ID K_HDR_ID ,' ||
193 ' OKE_K_SECURITY_PKG.GET_K_ACCESS( ID ) K_ACCESS ' ||
194 ' FROM OKC_K_HEADERS_B ) ACC' ||
195 ' WHERE ACC.K_HDR_ID = K.' || X_Header_ID_Col ||
196 ' AND ACC.K_ACCESS IN ( ''EDIT'' , ''VIEW'' )';
197
198 -- ||
199 -- ' AND ( OKE_UTILS.CROSS_ORG_ACCESS = ''Y''' ||
200 -- ' OR NVL(K.AUTHORING_ORG_ID , -99) = OKE_UTILS.ORG_ID )';
201
202 Print_Text_Buffer( FND_FILE.OUTPUT , ViewSyntax , TRUE );
203
204 AD_DDL.DO_DDL( ApplsysSchema
205 , 'OKE'
206 , AD_DDL.CREATE_VIEW
207 , ViewSyntax
208 , SecViewName
209 );
210
211 FND_MESSAGE.SET_NAME('OKE' , 'OKE_SEC_GEN_VIEW_SUCC');
212 FND_MESSAGE.SET_TOKEN('VIEW' , SecViewName);
213 Print_Text_Buffer( FND_FILE.LOG , FND_MESSAGE.GET , FALSE );
214
215 END IF;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 FND_MESSAGE.SET_NAME('OKE' , 'OKE_SEC_GEN_VIEW_FAILED');
220 FND_MESSAGE.SET_TOKEN('VIEW' , SecViewName);
221 Print_Text_Buffer( FND_FILE.LOG , FND_MESSAGE.GET , FALSE );
222 Print_Text_Buffer( FND_FILE.LOG , sqlerrm , TRUE );
223 IF ( AD_DDL.ERROR_BUF IS NOT NULL ) THEN
224 X_Error_Buf := AD_DDL.Error_Buf;
225 Print_Text_Buffer( FND_FILE.LOG , AD_DDL.Error_Buf , TRUE );
226 ELSE
227 X_Error_Buf := sqlerrm;
228 END IF;
229 RAISE;
230
231 END Generate_Secured_View;
232
233
234 --
235 -- Public Procedures
236 --
237 PROCEDURE Generate_Secured_Views
238 ( ERRBUF OUT NOCOPY VARCHAR2
239 , RETCODE OUT NOCOPY NUMBER
240 ) IS
241
242 L_Error_Buf VARCHAR2(4000);
243
244 BEGIN
245
246 Generate_Secured_View ( 'OKE_K_HEADERS'
247 , 'K_HEADER_ID'
248 , TRUE
249 , L_Error_Buf );
250 Generate_Secured_View ( 'OKE_K_LINES'
251 , 'HEADER_ID'
252 , TRUE
253 , L_Error_Buf );
254
255 RETCODE := 0;
256
257 EXCEPTION
258 WHEN OTHERS THEN
259 ERRBUF := L_Error_Buf;
260 RETCODE := 2;
261
262 END Generate_Secured_Views;
263
264 END OKE_K_SECURED_VIEWS_PKG;