[Home] [Help]
PACKAGE BODY: APPS.CSM_CUSTMZ_VIEWS_EVENT_PKG
Source
1 PACKAGE BODY CSM_CUSTMZ_VIEWS_EVENT_PKG AS
2 /* $Header: csmeczvb.pls 120.8.12010000.2 2008/10/22 12:44:36 trajasek ship $ */
3
4 /*** Globals ***/
5 g_cust_view_acc_tab_name CONSTANT VARCHAR2(30) := 'CSM_CUSTOMIZATION_VIEWS_ACC';
6 g_cust_view_table_name CONSTANT VARCHAR2(30) := 'CSM_CUSTOMIZATION_VIEWS';
7 g_cust_view_seq_name CONSTANT VARCHAR2(30) := 'CSM_CUSTOMIZATION_VIEWS_ACC_S' ;
8 g_cust_view_pk1_name CONSTANT VARCHAR2(30) := 'CUST_VIEW_ID';
9 g_cust_view_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
10 CSM_ACC_PKG.t_publication_item_list('CSM_CUSTOMIZATION_VIEWS');
11
12 g_cust_view_pkg_name CONSTANT VARCHAR2(30) := 'CSM_CUSTMZ_VIEWS_EVENT_PKG';
13 g_cust_view_api_name CONSTANT VARCHAR2(30) := 'REFRESH_ACC';
14
15
16 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
17 p_message OUT NOCOPY VARCHAR2) IS
18 PRAGMA AUTONOMOUS_TRANSACTION;
19 --CURSOR declarations
20
21 --cursor to get last run date from jtm_con_request_data
22 CURSOR c_LastRundate IS
23 SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
24 FROM JTM_CON_REQUEST_DATA
25 WHERE package_name = g_cust_view_pkg_name
26 AND procedure_name = g_cust_view_api_name;
27
28 --Cursor for delete
29 CURSOR c_delete IS
30 --Delete the records that have been removed from the base table
31 SELECT ACC.USER_ID,
32 ACC.CUST_VIEW_ID,
33 ACC.ACCESS_ID
34 FROM CSM_CUSTOMIZATION_VIEWS_ACC ACC
35 WHERE NOT EXISTS (SELECT 1
36 FROM CSM_CUSTOMIZATION_VIEWS B
37 WHERE B.CUST_VIEW_ID = ACC.CUST_VIEW_ID)
38 UNION ALL
39 --Delete the records that have been personalized at a "higher" level
40 SELECT ACC.USER_ID,
41 ACC.CUST_VIEW_ID,
42 ACC.ACCESS_ID
43 FROM CSM_CUSTOMIZATION_VIEWS_ACC ACC,
44 CSM_CUSTOMIZATION_VIEWS BACC --to get cust_view_key,level_id,level_value for that cust_view_id
45 WHERE BACC.CUST_VIEW_ID=ACC.CUST_VIEW_ID
46 AND EXISTS(SELECT 1
47 FROM CSM_CUSTOMIZATION_VIEWS B,
48 ASG_USER AU
49 WHERE B.PAGE_NAME=BACC.PAGE_NAME
50 AND B.REGION_NAME=BACC.REGION_NAME
51 AND B.CUST_VIEW_KEY=BACC.CUST_VIEW_KEY
52 AND ACC.USER_ID = AU.USER_ID
53 AND AU.USER_ID = AU.OWNER_ID
54 AND (
55 (BACC.LEVEL_ID = 10001
56 AND BACC.LEVEL_VALUE = 0
57 AND B.LEVEL_ID=10003
58 AND B.LEVEL_VALUE = AU.RESPONSIBILITY_ID) --Site to Resp
59 OR (BACC.LEVEL_ID = 10001
60 AND BACC.LEVEL_VALUE = 0
61 AND B.LEVEL_ID=10004
62 AND B.LEVEL_VALUE = AU.USER_ID) -- Site to User
63 OR (BACC.LEVEL_ID = 10003
64 AND BACC.LEVEL_VALUE = AU.RESPONSIBILITY_ID
65 AND B.LEVEL_ID=10004
66 AND B.LEVEL_VALUE = AU.USER_ID)--Resp to User
67 )
68 ) ;
69
70
71 --Cursor for update
72 CURSOR c_update(b_lastrundate DATE) IS
73 SELECT ACC.USER_ID,
74 ACC.CUST_VIEW_ID,
75 ACC.ACCESS_ID
76 FROM CSM_CUSTOMIZATION_VIEWS_ACC ACC
77 WHERE EXISTS
78 (SELECT 1 FROM CSM_CUSTOMIZATION_VIEWS B
79 WHERE B.CUST_VIEW_ID = ACC.CUST_VIEW_ID
80 AND B.LAST_UPDATE_DATE > b_lastrundate );
81
82
83 --Cursor for insert
84 CURSOR c_insert IS
85 SELECT AU.USER_ID,
86 B.CUST_VIEW_ID,
87 1 ACCESS_ID
88 FROM CSM_CUSTOMIZATION_VIEWS B,
89 ASG_USER AU
90 WHERE AU.USER_ID = AU.OWNER_ID
91 AND (
92 (B.LEVEL_ID=10004 AND B.LEVEL_VALUE = AU.USER_ID)
93 OR
94 --If perz at resp level, verify that no User level perz exists
95 (B.LEVEL_ID=10003 AND B.LEVEL_VALUE = AU.RESPONSIBILITY_ID
96 AND NOT EXISTS( SELECT 1
97 FROM CSM_CUSTOMIZATION_VIEWS B1
98 WHERE B.PAGE_NAME = B1.PAGE_NAME
99 AND B.REGION_NAME = B1.REGION_NAME
100 AND B.CUST_VIEW_KEY = B1.CUST_VIEW_KEY
101 AND B1.LEVEL_ID = 10004
102 AND B1.LEVEL_VALUE = AU.USER_ID)
103 )
104 OR
105 --If perz at site level, verify that no resp and User level perz exists
106 (B.LEVEL_ID=10001 AND B.LEVEL_VALUE=0
107 AND NOT EXISTS( SELECT 1
108 FROM CSM_CUSTOMIZATION_VIEWS B1
109 WHERE B.PAGE_NAME=B1.PAGE_NAME
110 AND B.REGION_NAME=B1.REGION_NAME
111 AND B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
112 AND B1.LEVEL_ID=10004
113 AND B1.LEVEL_VALUE=AU.USER_ID
114 )
115 AND NOT EXISTS( SELECT 1
116 FROM CSM_CUSTOMIZATION_VIEWS B1
117 WHERE B.PAGE_NAME=B1.PAGE_NAME
118 AND B.REGION_NAME=B1.REGION_NAME
119 AND B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
120 AND B1.LEVEL_ID=10003
121 AND B1.LEVEL_VALUE=AU.RESPONSIBILITY_ID)
122 )
123 )
124 AND NOT EXISTS (SELECT 1
125 FROM CSM_CUSTOMIZATION_VIEWS_ACC ACC
126 WHERE B.CUST_VIEW_ID = ACC.CUST_VIEW_ID
127 AND AU.USER_ID = ACC.USER_ID );
128
129
130 TYPE con_rec_type IS RECORD
131 (
132 USER_ID ASG_USER.USER_ID%TYPE,
133 CUST_VIEW_ID NUMBER,
134 ACCESS_ID NUMBER
135 );
136
137 TYPE l_tab_type IS TABLE OF con_rec_type
138 INDEX BY BINARY_INTEGER;
139
140 l_tab l_tab_type;
141 l_lastrundate c_LastRundate%ROWTYPE;
142 l_sqlerrno VARCHAR2(20);
143 l_sqlerrmsg VARCHAR2(4000);
144 l_dummy BOOLEAN;
145 l_resource_id NUMBER;
146 BEGIN
147 CSM_UTIL_PKG.LOG('Entering CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc Package ',
148 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
149
150 OPEN c_lastrundate;
151 FETCH c_lastrundate INTO l_lastrundate;
152 CLOSE c_lastrundate;
153
154 CSM_UTIL_PKG.LOG('Got LASTRUNDATE ','CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
155
156 --delete--
157
158 OPEN c_delete;
159 FETCH c_delete BULK COLLECT INTO l_tab;
160 CLOSE c_delete;
161
162
163 CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_tab.count||' records',
164 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
165
166 FOR I IN 1..l_tab.COUNT
167 LOOP
168 CSM_ACC_PKG.DELETE_ACC(
169 p_publication_item_names => g_cust_view_pubi_name,
170 p_acc_table_name => g_cust_view_acc_tab_name ,
171 p_user_id => l_tab(I).USER_ID,
172 p_pk1_name => g_cust_view_pk1_name,
173 p_pk1_num_value => l_tab(I).CUST_VIEW_ID);
174 END LOOP;
175
176 COMMIT;
177
178 CSM_UTIL_PKG.LOG('DELETION successful',
179 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
180
181 l_tab.DELETE;
182
183
184 --update--
185 OPEN c_update(l_lastrundate.LAST_RUN_DATE);
186 FETCH c_update BULK COLLECT INTO l_tab;
187 CLOSE c_update;
188
189 CSM_UTIL_PKG.LOG('Entering UPDATE to update ' || l_tab.count||' records',
190 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
191
192 FOR I IN 1..l_tab.COUNT
193 LOOP
194 CSM_ACC_PKG.UPDATE_ACC(
195 p_publication_item_names => g_cust_view_pubi_name,
196 p_acc_table_name => g_cust_view_acc_tab_name ,
197 p_user_id => l_tab(I).USER_ID,
198 p_access_id => l_tab(I).ACCESS_ID);
199 END LOOP;
200
201 COMMIT;
202
203 CSM_UTIL_PKG.LOG('UPDATE Successful ',
204 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
205 l_tab.DELETE;
206
207 --insert--
208 OPEN c_insert;
209 FETCH c_insert BULK COLLECT INTO l_tab;
210 CLOSE c_insert;
211
212 CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records',
213 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
214
215 FOR I IN 1..l_tab.COUNT
216 LOOP
217 CSM_ACC_PKG.INSERT_ACC(
218 p_publication_item_names => g_cust_view_pubi_name,
219 p_acc_table_name => g_cust_view_acc_tab_name ,
220 p_seq_name => g_cust_view_seq_name,
221 p_user_id => l_tab(I).USER_ID,
222 p_pk1_name => g_cust_view_pk1_name,
223 p_pk1_num_value => l_tab(I).CUST_VIEW_ID);
224 END LOOP;
225
226 COMMIT;
227
228 CSM_UTIL_PKG.LOG('INSERTION Successful ',
229 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
230
231
232 UPDATE jtm_con_request_data
233 SET last_run_date = sysdate
234 WHERE package_name = g_cust_view_pkg_name
235 AND procedure_name = g_cust_view_api_name;
236
237
238
239 COMMIT;
240 p_status := 'FINE';
241 p_message := 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc Executed successfully';
242 CSM_UTIL_PKG.LOG('Leaving CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc Package ',
243 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_PROCEDURE);
244
245 EXCEPTION
246 WHEN others THEN
247 l_sqlerrno := to_char(SQLCODE);
248 l_sqlerrmsg := substr(SQLERRM, 1,2000);
249 p_status := 'ERROR';
250 p_message := 'Error in CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc :' || l_sqlerrno || ':' || l_sqlerrmsg;
251 CSM_UTIL_PKG.LOG(p_message, 'CSM_CUSTMZ_VIEWS_EVENT_PKG.Refresh_Acc',FND_LOG.LEVEL_EXCEPTION);
252 ROLLBACK;
253
254 END Refresh_Acc;
255
256
257 --Bug 7239431
258 PROCEDURE REFRESH_USER(p_user_id NUMBER)
259 IS
260
261 --Cursor for insert
262 CURSOR c_insert(b_user_id NUMBER) IS
263 SELECT AU.USER_ID,
264 B.CUST_VIEW_ID,
265 1 ACCESS_ID
266 FROM CSM_CUSTOMIZATION_VIEWS B,
267 ASG_USER AU
268 WHERE AU.USER_ID = AU.OWNER_ID
269 AND AU.USER_ID = b_user_id
270 AND (
271 (B.LEVEL_ID=10004 AND B.LEVEL_VALUE = AU.USER_ID)
272 OR
273 --If perz at resp level, verify that no User level perz exists
274 (B.LEVEL_ID=10003 AND B.LEVEL_VALUE = AU.RESPONSIBILITY_ID
275 AND NOT EXISTS( SELECT 1
276 FROM CSM_CUSTOMIZATION_VIEWS B1
277 WHERE B.PAGE_NAME = B1.PAGE_NAME
278 AND B.REGION_NAME = B1.REGION_NAME
279 AND B.CUST_VIEW_KEY = B1.CUST_VIEW_KEY
280 AND B1.LEVEL_ID = 10004
281 AND B1.LEVEL_VALUE = AU.USER_ID)
282 )
283 OR
284 --If perz at site level, verify that no resp and User level perz exists
285 (B.LEVEL_ID=10001 AND B.LEVEL_VALUE=0
286 AND NOT EXISTS( SELECT 1
287 FROM CSM_CUSTOMIZATION_VIEWS B1
288 WHERE B.PAGE_NAME=B1.PAGE_NAME
289 AND B.REGION_NAME=B1.REGION_NAME
290 AND B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
291 AND B1.LEVEL_ID=10004
292 AND B1.LEVEL_VALUE=AU.USER_ID
293 )
294 AND NOT EXISTS( SELECT 1
295 FROM CSM_CUSTOMIZATION_VIEWS B1
296 WHERE B.PAGE_NAME=B1.PAGE_NAME
297 AND B.REGION_NAME=B1.REGION_NAME
298 AND B.CUST_VIEW_KEY=B1.CUST_VIEW_KEY
299 AND B1.LEVEL_ID=10003
300 AND B1.LEVEL_VALUE=AU.RESPONSIBILITY_ID)
301 )
302 );
303
304
305 TYPE con_rec_type IS RECORD
306 (
307 USER_ID ASG_USER.USER_ID%TYPE,
308 CUST_VIEW_ID NUMBER,
309 ACCESS_ID NUMBER
310 );
311
312 TYPE l_tab_type IS TABLE OF con_rec_type
313 INDEX BY BINARY_INTEGER;
314
315 l_tab l_tab_type;
316
317 BEGIN
318
319 CSM_UTIL_PKG.LOG('Entering api REFRESH_USER with user id- '||p_user_id,
320 'CSM_CUSTMZ_VIEWS_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
321
322 DELETE FROM CSM_CUSTOMIZATION_VIEWS_ACC WHERE USER_ID=p_user_id;
323
324 --insert--
325 OPEN c_insert(p_user_id);
326 FETCH c_insert BULK COLLECT INTO l_tab;
327 CLOSE c_insert;
328
329 CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records for user',
330 'CSM_CUSTMZ_VIEWS_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
331
332 FOR I IN 1..l_tab.COUNT
333 LOOP
334 CSM_ACC_PKG.INSERT_ACC(
335 p_publication_item_names => g_cust_view_pubi_name,
336 p_acc_table_name => g_cust_view_acc_tab_name ,
337 p_seq_name => g_cust_view_seq_name,
338 p_user_id => l_tab(I).USER_ID,
339 p_pk1_name => g_cust_view_pk1_name,
340 p_pk1_num_value => l_tab(I).CUST_VIEW_ID);
341 END LOOP;
342
343 CSM_UTIL_PKG.LOG('INSERTION Successful ',
344 'CSM_CUSTMZ_VIEWS_EVENT_PKG.REFRESH_USER',FND_LOG.LEVEL_PROCEDURE);
345
346 END REFRESH_USER;
347
348 END CSM_CUSTMZ_VIEWS_EVENT_PKG;