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