[Home] [Help]
PACKAGE BODY: APPS.CSM_RESCODE_MAPPING_EVENT_PKG
Source
1 PACKAGE BODY CSM_RESCODE_MAPPING_EVENT_PKG AS
2 /* $Header: csmercmb.pls 120.0 2005/11/14 09:11:21 trajasek noship $*/
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 res_map_id_tbl_typ IS TABLE OF CS_SR_RES_CODE_MAPPING_DETAIL.resolution_map_detail_id%TYPE INDEX BY BINARY_INTEGER;
22 l_sqlerrno VARCHAR2(20);
23 l_sqlerrmsg varchar2(2000);
24 l_mark_dirty boolean;
25 l_pub_item varchar2(30) := 'CSM_RESCODE_MAPPING';
26 l_map_access_list asg_download.access_list;
27 l_all_omfs_resource_list asg_download.user_list;
28 l_null_omfs_resource_list asg_download.user_list;
29 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
30 l_resol_map_id_tbl res_map_id_tbl_typ;
31
32 --Cursor Declarations
33 --Insert Cursor
34 CURSOR csr_rescode_map_ins
35 IS
36 SELECT csm_rescode_mapping_acc_s.nextval,
37 b.resolution_map_detail_id
38 FROM CS_SR_RES_CODE_MAPPING_DETAIL b
39 WHERE NOT EXISTS
40 (
41 SELECT 1
42 FROM CSM_RESCODE_MAPPING_ACC acc
43 WHERE acc.resolution_map_detail_id = b.resolution_map_detail_id
44 );
45 --update cursor
46 CURSOR csr_rescode_map_upd(p_lastrundate IN date)
47 IS
48 SELECT access_id
49 FROM CSM_RESCODE_MAPPING_ACC acc,
50 CS_SR_RES_CODE_MAPPING_DETAIL b
51 WHERE b.resolution_map_detail_id = acc.resolution_map_detail_id
52 AND b.last_update_Date >= p_lastrundate;
53 --Delete cursor
54 CURSOR csr_rescode_map_del
55 IS
56 SELECT access_id
57 FROM CSM_RESCODE_MAPPING_ACC acc
58 WHERE NOT EXISTS
59 (SELECT 1
60 FROM CS_SR_RES_CODE_MAPPING_DETAIL b
61 WHERE b.resolution_map_detail_id = acc.resolution_map_detail_id
62 );
63 --Cursor to get last run date
64 CURSOR l_last_run_date_csr
65 IS
66 SELECT nvl(last_run_date, (sysdate - 365*50))
67 FROM jtm_con_request_data
68 WHERE package_name = 'CSM_RESCODE_MAPPING_EVENT_PKG'
69 AND procedure_name = 'REFRESH_ACC';
70
71 BEGIN
72
73 CSM_UTIL_PKG.LOG('Entering CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC ',
74 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC', FND_LOG.LEVEL_PROCEDURE);
75
76 -- get last conc program update date
77 OPEN l_last_run_date_csr;
78 FETCH l_last_run_date_csr INTO l_prog_update_date;
79 CLOSE l_last_run_date_csr;
80
81 IF l_map_access_list.count > 0 THEN
82 l_map_access_list.delete;
83 END IF;
84
85 -- get resource list of all omfs users
86 l_all_omfs_resource_list := l_null_omfs_resource_list;
87 l_all_omfs_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
88
89 CSM_UTIL_PKG.LOG('Entering delete ', 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_STATEMENT);
90
91 -- process all deletes
92 OPEN csr_rescode_map_del;
93 FETCH csr_rescode_map_del BULK COLLECT INTO l_map_access_list;
94 CLOSE csr_rescode_map_del;
95
96 --mark dirty for delete
97 IF l_map_access_list.count > 0 THEN
98 FOR i IN 1..l_map_access_list.count LOOP
99 FOR j IN 1..l_all_omfs_resource_list.count LOOP
100 l_mark_dirty := csm_util_pkg.MakeDirtyForResource(l_pub_item,
101 l_map_access_list(i),
102 l_all_omfs_resource_list(j),
103 asg_download.del,
104 sysdate);
105
106 END LOOP;
107 END LOOP;
108
109 -- bulk delete from acc table
110 FORALL i IN 1..l_map_access_list.count
111 DELETE FROM csm_rescode_mapping_acc WHERE access_id = l_map_access_list(i);
112
113 l_map_access_list.delete;
114 END IF; -- end of process deletes
115
116 CSM_UTIL_PKG.LOG('Leaving deletes and entering updates', 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
117
118
119 -- process all updates
120 OPEN csr_rescode_map_upd(l_prog_update_date);
121 FETCH csr_rescode_map_upd BULK COLLECT INTO l_map_access_list;
122 CLOSE csr_rescode_map_upd;
123
124 IF l_map_access_list.count > 0 THEN
125 FOR i IN 1..l_map_access_list.count LOOP
126 FOR j IN 1..l_all_omfs_resource_list.count LOOP
127 l_mark_dirty := csm_util_pkg.MakeDirtyForResource(l_pub_item,
128 l_map_access_list(i),
129 l_all_omfs_resource_list(j),
130 asg_download.upd,
131 sysdate);
132
133 END LOOP;
134 END LOOP;
135
136 l_map_access_list.delete;
137 END IF; -- end of process updates
138
139 IF l_resol_map_id_tbl.count > 0 THEN
140 l_resol_map_id_tbl.delete;
141 END IF;
142
143 CSM_UTIL_PKG.LOG('Leaving updates and entering inserts', 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
144
145 --process all inserts
146 OPEN csr_rescode_map_ins;
147 FETCH csr_rescode_map_ins BULK COLLECT INTO l_map_access_list, l_resol_map_id_tbl;
148 CLOSE csr_rescode_map_ins;
149
150 IF l_map_access_list.count > 0 THEN
151 FOR i IN 1..l_map_access_list.count LOOP
152 FOR j IN 1..l_all_omfs_resource_list.count LOOP
153 l_mark_dirty := csm_util_pkg.MakeDirtyForResource(l_pub_item,
154 l_map_access_list(i),
155 l_all_omfs_resource_list(j),
156 asg_download.ins,
157 sysdate);
158
159 END LOOP;
160 END LOOP;
161
162 FORALL i IN 1..l_map_access_list.count
163 INSERT INTO CSM_RESCODE_MAPPING_ACC
164 (access_id,
165 resolution_map_detail_id,
166 created_by,
167 creation_date,
168 last_updated_by,
169 last_update_date,
170 last_update_login)
171 VALUES (l_map_access_list(i),
172 l_resol_map_id_tbl(i),
173 fnd_global.user_id,
174 sysdate,
175 fnd_global.user_id,
176 sysdate,
177 fnd_global.login_id);
178
179 l_map_access_list.delete;
180 END IF; -- end of process inserts
181
182 -- update last_run_date
183 UPDATE jtm_con_request_data
184 SET last_run_date = sysdate
185 WHERE package_name = 'CSM_RESCODE_MAPPING_EVENT_PKG'
186 AND procedure_name= 'REFRESH_ACC';
187
188 COMMIT;
189
190 p_status := 'FINE';
191 p_message := 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC Executed successfully';
192
193 CSM_UTIL_PKG.LOG('Leaving CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC ',
194 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_PROCEDURE);
195
196 EXCEPTION
197 WHEN others THEN
198 l_sqlerrno := to_char(SQLCODE);
199 l_sqlerrmsg := substr(SQLERRM, 1,2000);
200 p_status := 'Error';
201 p_message := 'Error in CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC :' || l_sqlerrno || ':' || l_sqlerrmsg;
202 ROLLBACK;
203 CSM_UTIL_PKG.LOG('Exception in CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg,
204 'CSM_RESCODE_MAPPING_EVENT_PKG.REFRESH_ACC',FND_LOG.LEVEL_EXCEPTION);
205 END REFRESH_ACC;
206
207 END CSM_RESCODE_MAPPING_EVENT_PKG;