[Home] [Help]
PACKAGE BODY: APPS.CSM_SR_TYPE_MAP_EVENT_PKG
Source
1 PACKAGE BODY CSM_SR_TYPE_MAP_EVENT_PKG AS
2 /* $Header: csmeitmb.pls 120.2 2008/02/07 07:45:35 anaraman ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14
15
16 PROCEDURE Refresh_acc(p_status OUT NOCOPY VARCHAR2,
17 p_message OUT NOCOPY VARCHAR2)
18 IS
19 PRAGMA AUTONOMOUS_TRANSACTION;
20 --variable declarations
21 TYPE incident_type_id_tbl_typ IS TABLE OF CS_SR_TYPE_MAPPING.incident_type_id%TYPE INDEX BY BINARY_INTEGER;
22 TYPE responsibility_id_tbl_typ IS TABLE OF CS_SR_TYPE_MAPPING.responsibility_id%TYPE INDEX BY BINARY_INTEGER;
23
24 l_sqlerrno VARCHAR2(20);
25 l_sqlerrmsg varchar2(2000);
26 l_mark_dirty boolean;
27 l_pub_item varchar2(30) := 'CSM_SR_TYPE_MAPPING';
28 l_map_access_list asg_download.access_list;
29 l_all_omfs_resource_list asg_download.user_list;
30 l_null_omfs_resource_list asg_download.user_list;
31 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
32 l_sr_type_id_tbl incident_type_id_tbl_typ;
33 l_responsibility_id_tbl responsibility_id_tbl_typ;
34 l_user_list asg_download.user_list;
35
36 --Cursor Declarations
37 --Insert Cursor
38 CURSOR csr_sr_type_map_ins
39 IS
40 SELECT CSM_SR_TYPE_MAPPING_ACC_S.nextval,
41 usr.user_id,
42 b.incident_type_id,
43 b.responsibility_id
44 FROM CS_SR_TYPE_MAPPING b,
45 ASG_USER usr
46 WHERE usr.responsibility_id = b.responsibility_id
47 AND usr.user_id = usr.owner_id
48 AND NOT EXISTS
49 (
50 SELECT 1
51 FROM CSM_SR_TYPE_MAPPING_ACC acc
52 WHERE acc.incident_type_id = b.incident_type_id
53 AND acc.responsibility_id = b.responsibility_id
54 AND acc.user_id = usr.user_id
55 );
56 --update cursor
57 CURSOR csr_sr_type_map_upd(p_lastrundate IN date)
58 IS
59 SELECT access_id,user_id
60 FROM CSM_SR_TYPE_MAPPING_ACC acc,
61 CS_SR_TYPE_MAPPING b
62 WHERE acc.incident_type_id = b.incident_type_id
63 AND acc.responsibility_id = b.responsibility_id
64 AND b.last_update_Date >= p_lastrundate;
65
66 --Delete cursor
67 CURSOR csr_sr_type_map_del
68 IS
69 SELECT access_id,user_id
70 FROM CSM_SR_TYPE_MAPPING_ACC acc
71 WHERE NOT EXISTS
72 (SELECT 1
73 FROM CS_SR_TYPE_MAPPING b
74 WHERE b.incident_type_id = acc.incident_type_id
75 AND b.responsibility_id = acc.responsibility_id
76 );
77 --Cursor to get last run dateR12 change
78 CURSOR l_last_run_date_csr
79 IS
80 SELECT nvl(last_run_date, (sysdate - 365*50))
81 FROM jtm_con_request_data
82 WHERE package_name = 'CSM_SR_TYPE_MAP_EVENT_PKG'
83 AND procedure_name = 'REFRESH_ACC';
84
85 BEGIN
86
87 CSM_UTIL_PKG.LOG('Entering CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC ',
88 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_PROCEDURE);
89
90 -- get last conc program update date
91 OPEN l_last_run_date_csr;
92 FETCH l_last_run_date_csr INTO l_prog_update_date;
93 CLOSE l_last_run_date_csr;
94
95 IF l_map_access_list.count > 0 THEN
96 l_map_access_list.delete;
97 END IF;
98
99 -- get resource list of all omfs users R12 change
100 l_user_list := l_null_omfs_resource_list;
101
102 CSM_UTIL_PKG.LOG('Entering delete ', 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_STATEMENT);
103
104 -- process all deletes
105 OPEN csr_sr_type_map_del;
106 FETCH csr_sr_type_map_del BULK COLLECT INTO l_map_access_list,l_user_list;
107 CLOSE csr_sr_type_map_del;
108
109 --mark dirty for delete
110 IF l_map_access_list.count > 0 THEN
111 FOR i IN 1..l_map_access_list.count LOOP
112 l_mark_dirty := csm_util_pkg.MakeDirtyForUser (l_pub_item,
113 l_map_access_list(i),
114 l_user_list(i),
115 asg_download.del,
116 sysdate);
117
118 END LOOP;
119
120 -- bulk delete from acc table
121 FORALL i IN 1..l_map_access_list.count
122 DELETE FROM CSM_SR_TYPE_MAPPING_ACC WHERE access_id = l_map_access_list(i) AND user_id = l_user_list(i) ;
123
124 l_map_access_list.delete;
125 l_user_list.delete;
126 END IF; -- end of process deletes
127
128 CSM_UTIL_PKG.LOG('Leaving deletes and entering updates', 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
129
130
131 -- process all updates
132 OPEN csr_sr_type_map_upd(l_prog_update_date);
133 FETCH csr_sr_type_map_upd BULK COLLECT INTO l_map_access_list,l_user_list;
134 CLOSE csr_sr_type_map_upd;
135
136 IF l_map_access_list.count > 0 THEN
137 FOR i IN 1..l_map_access_list.count LOOP
138 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
139 l_map_access_list(i),
140 l_user_list(i),
141 asg_download.upd,
142 sysdate);
143
144 END LOOP;
145
146 l_map_access_list.delete;
147 l_user_list.delete;
148 END IF; -- end of process updates
149
150 IF l_sr_type_id_tbl.count > 0 THEN
151 l_sr_type_id_tbl.delete;
152 END IF;
153
154 IF l_responsibility_id_tbl.count > 0 THEN
155 l_responsibility_id_tbl.delete;
156 END IF;
157
158 CSM_UTIL_PKG.LOG('Leaving updates and entering inserts', 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
159
160 --process all inserts
161 OPEN csr_sr_type_map_ins;
162 FETCH csr_sr_type_map_ins BULK COLLECT INTO l_map_access_list,l_user_list, l_sr_type_id_tbl, l_responsibility_id_tbl;
163 CLOSE csr_sr_type_map_ins;
164
165 IF l_map_access_list.count > 0 THEN
166 FOR i IN 1..l_map_access_list.count LOOP
167 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(l_pub_item,
168 l_map_access_list(i),
169 l_user_list(i),
170 asg_download.ins,
171 sysdate);
172
173 END LOOP;
174
175 FORALL i IN 1..l_map_access_list.count
176 INSERT INTO CSM_SR_TYPE_MAPPING_ACC
177 (access_id,
178 user_id,
179 incident_type_id,
180 responsibility_id,
181 created_by,
182 creation_date,
183 last_updated_by,
184 last_update_date,
185 last_update_login)
186 VALUES (l_map_access_list(i),
187 l_user_list(i),
188 l_sr_type_id_tbl(i),
189 l_responsibility_id_tbl(i),
190 fnd_global.user_id,
191 sysdate,
192 fnd_global.user_id,
193 sysdate,
194 fnd_global.login_id);
195
196 l_map_access_list.delete;
197 l_user_list.delete;
198 END IF; -- end of process inserts
199
200 -- update last_run_date
201 UPDATE jtm_con_request_data
202 SET last_run_date = sysdate
203 WHERE package_name = 'CSM_SR_TYPE_MAP_EVENT_PKG'
204 AND procedure_name= 'REFRESH_ACC';
205
206 COMMIT;
207
208 p_status := 'FINE';
209 p_message := 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC Executed successfully';
210
211 CSM_UTIL_PKG.LOG('Leaving CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC ',
212 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
213
214 EXCEPTION
215 WHEN others THEN
216 l_sqlerrno := to_char(SQLCODE);
217 l_sqlerrmsg := substr(SQLERRM, 1,2000);
218 p_status := 'Error';
219 p_message := 'Error in CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC :' || l_sqlerrno || ':' || l_sqlerrmsg;
220 ROLLBACK;
221 CSM_UTIL_PKG.LOG('Exception in CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
222 'CSM_SR_TYPE_MAP_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
223 END REFRESH_ACC;
224
225 END CSM_SR_TYPE_MAP_EVENT_PKG;