[Home] [Help]
PACKAGE BODY: APPS.CSM_AD_SIMPLE_SRCH_EVENT_PKG
Source
1 PACKAGE BODY CSM_AD_SIMPLE_SRCH_EVENT_PKG AS
2 /* $Header: csmeassb.pls 120.5 2008/02/22 08:52:06 trajasek noship $ */
3 /*** Globals ***/
4 g_ad_simple_sch_acc_tab_name CONSTANT VARCHAR2(30) := 'CSM_AD_SIMPLE_SEARCH_ACC';
5 g_ad_simple_sch_table_name CONSTANT VARCHAR2(30) := 'CSM_AD_SIMPLE_SEARCH_VIEW';
6 g_ad_simple_sch_seq_name CONSTANT VARCHAR2(30) := 'CSM_AD_SIMPLE_SEARCH_ACC_S';
7 g_ad_simple_sch_pubi_name CONSTANT VARCHAR2(30) := 'CSM_AD_SIMPLE_SEARCH';
8
9 g_ad_simple_sch_pkg_name CONSTANT VARCHAR2(30) := 'CSM_AD_SIMPLE_SRCH_EVENT_PKG';
10 g_ad_simple_sch_api_name CONSTANT VARCHAR2(30) := 'REFRESH_ACC';
11
12 g_access_list asg_download.access_list;
13
14 PROCEDURE GET_ACCESS_LIST(p_access_id IN CSM_AD_SIMPLE_SEARCH_ACC.ACCESS_ID%TYPE ,
15 p_count IN NUMBER)
16 IS
17 BEGIN
18 IF g_access_list.COUNT > 0 THEN
19 g_access_list.DELETE;
20 END IF;
21
22 FOR I IN 1..p_count
23 LOOP
24 g_access_list(I):=p_access_id;
25 END LOOP;
26
27 END get_access_list;
28
29
30 PROCEDURE REFRESH_ACC(p_status OUT NOCOPY VARCHAR2,p_message OUT NOCOPY VARCHAR2 )
31 IS
32 PRAGMA AUTONOMOUS_TRANSACTION;
33
34 /*** get the last run date of the concurent program ***/
35 CURSOR c_LastRundate IS
36 SELECT NVL(LAST_RUN_DATE, to_date('1','J')) LAST_RUN_DATE
37 FROM JTM_CON_REQUEST_DATA
38 WHERE package_name = g_ad_simple_sch_pkg_name
39 AND procedure_name = g_ad_simple_sch_api_name;
40
41 CURSOR c_delete IS
42 SELECT ACC.ACCESS_ID,
43 ACC.ID
44 FROM CSM_AD_SIMPLE_SEARCH_ACC ACC
45 WHERE NOT EXISTS (SELECT 1 FROM CSM_AD_SIMPLE_SEARCH_VIEW B
46 WHERE B.ID=ACC.ID
47 AND EXISTS (SELECT 1 FROM CSM_AD_SEARCH_TITLE_VIEW C
48 WHERE C.ID= B.SEARCH_TYPE_ID));
49
50
51 CURSOR c_update(b_lastrundate DATE) IS
52 SELECT ACC.ACCESS_ID,
53 ACC.ID
54 FROM CSM_AD_SIMPLE_SEARCH_ACC ACC
55 WHERE EXISTS (SELECT 1 FROM CSM_AD_SIMPLE_SEARCH_VIEW B
56 WHERE B.ID =ACC.ID
57 AND B.LAST_UPDATE_DATE > b_lastrundate);
58
59
60 CURSOR c_insert IS
61 SELECT 1 ACCESS_ID,
62 B.ID
63 FROM CSM_AD_SIMPLE_SEARCH_VIEW B
64 WHERE NOT EXISTS (SELECT 1 FROM CSM_AD_SIMPLE_SEARCH_ACC ACC
65 WHERE ACC.ID=B.ID
66 OR NOT EXISTS(SELECT 1 FROM CSM_AD_SEARCH_TITLE_VIEW C
67 WHERE C.ID= B.SEARCH_TYPE_ID));
68
69 CURSOR c_get_access_id IS
70 SELECT CSM_AD_SIMPLE_SEARCH_ACC_S.NEXTVAL
71 FROM DUAL;
72
73 CURSOR c_get_user_list IS
74 SELECT AU.USER_ID
75 FROM ASG_USER AU
76 WHERE AU.USER_ID = AU.OWNER_ID
77 AND AU.ENABLED='Y' ;
78
79
80 TYPE l_conc_rec IS RECORD
81 (
82 ACCESS_ID CSM_AD_SIMPLE_SEARCH_ACC.ACCESS_ID%TYPE,
83 ID CSM_AD_SIMPLE_SEARCH_VIEW.ID%TYPE
84 );
85
86 TYPE l_tab_type IS TABLE OF l_conc_rec
87 INDEX BY BINARY_INTEGER;
88
89 TYPE l_user_list_type IS TABLE OF ASG_USER.USER_ID%TYPE
90 INDEX BY BINARY_INTEGER;
91
92 l_tab l_tab_type;
93 l_lastrundate c_LastRundate%ROWTYPE;
94 l_dummy BOOLEAN;
95 l_access_id NUMBER;
96 l_user_list asg_download.user_list;
97 l_sqlerrno VARCHAR2(20);
98 l_sqlerrmsg VARCHAR2(4000);
99
100 BEGIN
101
102 CSM_UTIL_PKG.LOG('Entering REFRESH_ACC: ',
103 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
104
105 OPEN c_lastrundate;
106 FETCH c_lastrundate INTO l_lastrundate;
107 CLOSE c_lastrundate;
108
109 CSM_UTIL_PKG.LOG('Got LASTRUNDATE ',
110 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
111
112 OPEN c_get_user_list;
113 FETCH c_get_user_list BULK COLLECT INTO l_user_list;
114 CLOSE c_get_user_list;
115
116 CSM_UTIL_PKG.LOG('Got USER list : ',
117 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
118
119 --delete--
120 OPEN c_delete;
121 FETCH c_delete BULK COLLECT INTO l_tab;
122 CLOSE c_delete;
123
124 CSM_UTIL_PKG.LOG('Entering DELETE to remove ' || l_tab.count||' records',
125 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
126
127 FOR I IN 1..l_tab.COUNT
128 LOOP
129 IF l_user_list.COUNT > 0 THEN
130 GET_ACCESS_LIST(l_tab(I).ACCESS_ID,l_user_list.COUNT);
131 l_dummy := asg_download.mark_dirty(g_ad_simple_sch_pubi_name,g_access_list,l_user_list, 'D', sysdate );
132 END IF;
133 DELETE FROM CSM_AD_SIMPLE_SEARCH_ACC WHERE ACCESS_ID=l_tab(I).ACCESS_ID;
134 END LOOP;
135
136 COMMIT;
137
138 CSM_UTIL_PKG.LOG('DELETION successful',
139 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
140
141 IF l_tab.COUNT > 0 THEN
142 l_tab.DELETE;
143 END IF;
144
145
146 --update--
147 OPEN c_update(l_lastrundate.Last_run_date);
148 FETCH c_update BULK COLLECT INTO l_tab;
149 CLOSE c_update;
150
151 CSM_UTIL_PKG.LOG('Entering UPDATE to update ' || l_tab.count||' records',
152 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
153
154 FOR I IN 1..l_tab.COUNT
155 LOOP
156 IF l_user_list.COUNT > 0 THEN
157 GET_ACCESS_LIST(l_tab(I).ACCESS_ID,l_user_list.COUNT);
158 l_dummy := asg_download.mark_dirty(g_ad_simple_sch_pubi_name,g_access_list,l_user_list, 'U', sysdate );
159 END IF;
160 UPDATE CSM_AD_SIMPLE_SEARCH_ACC
161 SET
162 LAST_UPDATE_DATE=SYSDATE,
163 LAST_UPDATED_BY=1,
164 LAST_UPDATE_LOGIN=1
165 WHERE ACCESS_ID=l_tab(I).ACCESS_ID;
166 END LOOP;
167
168 COMMIT;
169
170 CSM_UTIL_PKG.LOG('UPDATE Successful ',
171 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
172 IF l_tab.COUNT > 0 THEN
173 l_tab.DELETE;
174 END IF;
175
176 --insert--
177 OPEN c_insert;
178 FETCH c_insert BULK COLLECT INTO l_tab;
179 CLOSE c_insert;
180
181 CSM_UTIL_PKG.LOG('Entering INSERT to add ' || l_tab.count||' records',
182 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
183
184 FOR I IN 1..l_tab.COUNT
185 LOOP
186 OPEN C_GET_ACCESS_ID;
187 FETCH C_GET_ACCESS_ID INTO l_access_id;
188 CLOSE C_GET_ACCESS_ID;
189
190 INSERT INTO CSM_AD_SIMPLE_SEARCH_ACC
191 ( ACCESS_ID,
192 ID,
193 USER_ID,
194 COUNTER,
195 CREATION_DATE,
196 CREATED_BY,
197 LAST_UPDATE_DATE,
198 LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN
200 )
201 VALUES
202 ( l_access_id,
203 l_tab(I).ID,
204 1,
205 1,
206 SYSDATE,
207 1,
208 SYSDATE,
209 1,
210 1
211 );
212
213 IF l_user_list.COUNT > 0 THEN
214 GET_ACCESS_LIST(l_access_id,l_user_list.COUNT);
215 l_dummy:= ASG_DOWNLOAD.mark_dirty(g_ad_simple_sch_pubi_name,g_access_list,l_user_list,'I',SYSDATE);
216 END IF;
217
218 END LOOP;
219
220 COMMIT;
221 IF l_tab.COUNT > 0 THEN
222 l_tab.DELETE;
223 END IF;
224
225 CSM_UTIL_PKG.LOG('INSERTION Successful ',
226 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
227
228 UPDATE JTM_CON_REQUEST_DATA
229 SET LAST_RUN_DATE = SYSDATE
230 WHERE package_name = g_ad_simple_sch_pkg_name
231 AND procedure_name = g_ad_simple_sch_api_name;
232
233 COMMIT;
234
235 p_status := 'FINE';
236 p_message := 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC executed successfully';
237
238 CSM_UTIL_PKG.LOG('Leaving REFRESH_ACC: ',
239 'CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 l_sqlerrno := TO_CHAR(SQLCODE);
244 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
245 p_status := 'ERROR';
246 p_message := 'Error in CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
247 ROLLBACK;
248 csm_util_pkg.log('CSM_AD_SIMPLE_SRCH_EVENT_PKG.REFRESH_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
249
250 END REFRESH_ACC;
251
252
253 END CSM_AD_SIMPLE_SRCH_EVENT_PKG;