1 PACKAGE BODY CSM_ACCESS_PURGE_PKG AS
2 /* $Header: csmeacpb.pls 120.0.12010000.1 2008/07/28 16:12:46 appldev ship $ */
3
4 --
5 -- Purpose: Procedures to purge Invalid Acc Records
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- TRAJASEK 28MAy07 Initial Revision
9 -- --------- ------ ------------------------------------------
10
11 /*** Globals ***/
12 /**/
13 PROCEDURE DELETE_NON_ACC_MFS_TABLE
14 IS
15 CURSOR c_csm_inv_org
16 IS
17 SELECT DISTINCT USER_ID
18 FROM CSM_USER_INVENTORY_ORG ACC
19 WHERE USER_ID IS NOT NULL
20 AND NOT EXISTS (SELECT 1 FROM ASG_USER AU WHERE AU.USER_ID=ACC.USER_ID
21 AND AU.ENABLED= 'Y');
22
23 CURSOR c_csl_inv_org
24 IS
25 SELECT DISTINCT RESOURCE_ID
26 FROM CSL_RESOURCE_INVENTORY_ORG ACC
27 WHERE RESOURCE_ID IS NOT NULL
28 AND NOT EXISTS (SELECT 1 FROM ASG_USER AU WHERE AU.RESOURCE_ID=ACC.RESOURCE_ID
29 AND AU.ENABLED= 'Y');
30
31 TYPE USERID_TABLE_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
32 l_user_list USERID_TABLE_TYPE;
33
34 BEGIN
35 OPEN c_csm_inv_org;
36 FETCH c_csm_inv_org BULK COLLECT INTO l_user_list;
37 CLOSE c_csm_inv_org;
38
39 IF l_user_list.COUNT >0 THEN
40 FOR i IN 1..l_user_list.COUNT
41 LOOP
42 BEGIN
43 DELETE FROM CSM_USER_INVENTORY_ORG WHERE USER_ID = l_user_list(i);
44 EXCEPTION
45 WHEN OTHERS THEN
46 NULL;
47 END;
48
49 END LOOP;
50 l_user_list.DELETE;
51 END IF;
52
53 OPEN c_csl_inv_org;
54 FETCH c_csl_inv_org BULK COLLECT INTO l_user_list;
55 CLOSE c_csl_inv_org;
56
57 IF l_user_list.COUNT >0 THEN
58 FOR i IN 1..l_user_list.COUNT
59 LOOP
60 BEGIN
61 DELETE FROM CSL_RESOURCE_INVENTORY_ORG WHERE RESOURCE_ID = l_user_list(i);
62 EXCEPTION
63 WHEN OTHERS THEN
64 NULL;
65 END;
66 END LOOP;
67 l_user_list.DELETE;
68 END IF;
69
70 END DELETE_NON_ACC_MFS_TABLE;
71
72 PROCEDURE DELETE_ACC_FOR_USER
73 ( p_acc_table_name IN VARCHAR2
74 , p_user_id IN NUMBER
75 ) IS
76 l_stmt VARCHAR2(1000);
77 BEGIN
78 l_stmt := 'DELETE ' || p_acc_table_name ||' WHERE USER_ID = :P1';
79
80 EXECUTE IMMEDIATE l_stmt USING p_user_id;
81
82 END DELETE_ACC_FOR_USER;
83
84 PROCEDURE DELETE_ACC_FOR_RESOURCE
85 ( p_acc_table_name IN VARCHAR2
86 , p_resource_id IN NUMBER
87 ) IS
88 l_stmt VARCHAR2(1000);
89 BEGIN
90 l_stmt := 'DELETE ' || p_acc_table_name ||' WHERE RESOURCE_ID = :P1';
91
92 EXECUTE IMMEDIATE l_stmt USING p_resource_id;
93
94 END DELETE_ACC_FOR_RESOURCE;
95
96 PROCEDURE PURGE_INVALID_ACC_DATA(p_status OUT NOCOPY VARCHAR2,
97 p_message OUT NOCOPY VARCHAR2)
98 IS
99 PRAGMA AUTONOMOUS_TRANSACTION;
100
101 CURSOR c_csm_acc_list IS
102 SELECT ITEM_ID,
103 ACCESS_OWNER,
104 ACCESS_NAME
105 FROM ASG_PUB_ITEM
106 WHERE PUB_NAME IN ('SERVICEP','JTM_HANDHELD')
107 AND ACCESS_NAME IS NOT NULL
108 AND STATUS = 'Y'
109 AND ENABLED = 'Y';
110
111 CURSOR c_csl_acc_list IS
112 SELECT ITEM_ID,
113 ACCESS_OWNER,
114 ACCESS_NAME
115 FROM ASG_PUB_ITEM
116 WHERE PUB_NAME IN ('JTM','SERVICEL')
117 AND ACCESS_NAME IS NOT NULL
118 AND STATUS = 'Y'
119 AND ENABLED = 'Y';
120
121 TYPE acc_table_type IS TABLE OF c_csm_acc_list%ROWTYPE;
122 TYPE USERID_TABLE_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
123
124 acc_tab_list ACC_TABLE_TYPE;
125 l_user_list USERID_TABLE_TYPE;
126 l_sql_stmt VARCHAR2(1000);
127 l_sqlerrno VARCHAR2(20);
128 l_sqlerrmsg VARCHAR2(4000);
129 l_error_msg VARCHAR2(4000);
130 BEGIN
131
132 --Purge records for CSM
133 OPEN c_csm_acc_list;
134 FETCH c_csm_acc_list BULK COLLECT INTO acc_tab_list;
135 CLOSE c_csm_acc_list;
136 CSM_UTIL_PKG.LOG('Entering CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA', 'CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA',FND_LOG.LEVEL_EVENT);
137 FOR I IN 1..acc_tab_list.COUNT
138 LOOP
139 l_sql_stmt:= 'SELECT DISTINCT USER_ID FROM '||acc_tab_list(I).ACCESS_NAME ||
140 ' ACC WHERE USER_ID IS NOT NULL
141 AND NOT EXISTS (SELECT 1 FROM ASG_USER AU WHERE AU.USER_ID=ACC.USER_ID AND AU.ENABLED= ''Y'')';
142 BEGIN
143 EXECUTE IMMEDIATE l_sql_stmt BULK COLLECT INTO l_user_list;
144 EXCEPTION
145 WHEN OTHERS THEN
146 NULL;
147 END;
148 CSM_UTIL_PKG.LOG('Processing Access list', 'CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA',FND_LOG.LEVEL_EVENT);
149 IF l_user_list.COUNT >0 THEN
150 FOR J IN 1..l_user_list.COUNT
151 LOOP
152 DELETE_ACC_FOR_USER(acc_tab_list(I).ACCESS_NAME,l_user_list(J));
153 END LOOP;
154 COMMIT;
155 l_user_list.DELETE;
156 END IF;
157 END LOOP; --Access list loop
158
159 CSM_UTIL_PKG.LOG('Completed processing Access list', 'CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA',FND_LOG.LEVEL_EVENT);
160 IF acc_tab_list.COUNT > 0 THEN
161 acc_tab_list.DELETE;
162 END IF;
163
164
165 --Purge records for CSL
166 OPEN c_csl_acc_list;
167 FETCH c_csl_acc_list BULK COLLECT INTO acc_tab_list;
168 CLOSE c_csl_acc_list;
169 CSM_UTIL_PKG.LOG('Entering CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA', 'CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA',FND_LOG.LEVEL_EVENT);
170
171 FOR I IN 1..acc_tab_list.COUNT
172 LOOP
173 l_sql_stmt:= 'SELECT DISTINCT RESOURCE_ID FROM '||acc_tab_list(I).ACCESS_NAME ||
174 ' ACC WHERE RESOURCE_ID IS NOT NULL
175 AND NOT EXISTS (SELECT 1 FROM ASG_USER AU WHERE AU.RESOURCE_ID=ACC.RESOURCE_ID AND AU.ENABLED= ''Y'')';
176 BEGIN
177 EXECUTE IMMEDIATE l_sql_stmt BULK COLLECT INTO l_user_list;
178 EXCEPTION
179 WHEN OTHERS THEN
180 NULL;
181 END;
182 CSM_UTIL_PKG.LOG('Processing Access list', 'CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA',FND_LOG.LEVEL_EVENT);
183
184 IF l_user_list.COUNT >0 THEN
185 FOR J IN 1..l_user_list.COUNT
186 LOOP
187 DELETE_ACC_FOR_RESOURCE(acc_tab_list(I).ACCESS_NAME,l_user_list(J));
188 END LOOP;
189 COMMIT;
190 l_user_list.DELETE;
191 END IF;
192 END LOOP; --Access list loop
193
194 CSM_UTIL_PKG.LOG('Completed processing Access list', 'CSM_COUNTER_EVENT_PKG.COUNTER_PURGE_DATA',FND_LOG.LEVEL_EVENT);
195 IF acc_tab_list.COUNT > 0 THEN
196 acc_tab_list.DELETE;
197 END IF;
198 --call to delete non acc mfs tables
199 DELETE_NON_ACC_MFS_TABLE;
200
201 COMMIT;
202
203 p_status := 'SUCCESS';
204 p_message := 'CSM_ACCESS_PURGE_PKG.PURGE_INVALID_ACC_DATA Executed successfully';
205
206 EXCEPTION
207 --log the error
208 WHEN OTHERS THEN
209 l_sqlerrno := TO_CHAR(SQLCODE);
210 l_sqlerrmsg:= SUBSTR(SQLERRM,1,2000);
211 ROLLBACK;
212 l_error_msg := ' Exception in PURGE_INVALID_ACC_DATA'
213 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
214 p_status := 'ERROR';
215 p_message := 'Error in CSM_ACCESS_PURGE_PKG.PURGE_INVALID_ACC_DATA: ' || l_error_msg;
216 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_ACCESS_PURGE_PKG.PURGE_INVALID_ACC_DATA',FND_LOG.LEVEL_EVENT);
217
218 END PURGE_INVALID_ACC_DATA;
219
220 END CSM_ACCESS_PURGE_PKG; -- of package csm_counter_event_pkg
[Home] [Help]