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