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