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