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