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