1 PACKAGE BODY CSM_CURRENCY_EVENT_PKG AS
2 /* $Header: csmecurb.pls 120.1 2005/07/22 09:29:28 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 g_all_palm_res_list asg_download.user_list;
15
16
17 /**
18 Makes corresponding entries in to SDQ for all deletes, updates and inserts
19 Refreshes for all the users
20 */
21 procedure Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
22 p_message OUT NOCOPY VARCHAR2)
23 IS
24 PRAGMA AUTONOMOUS_TRANSACTION;
25 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
26 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
27 l_dsql varchar2(2048);
28 l_pub_item varchar2(30) := 'CSF_M_CURRENCIES';
29 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
30 l_sqlerrno VARCHAR2(20);
31 l_sqlerrmsg varchar2(2000);
32
33 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
34 IS
35 SELECT nvl(last_run_date, (sysdate - 365*50))
36 FROM jtm_con_request_data
37 WHERE package_name = 'CSM_CURRENCY_EVENT_PKG'
38 AND procedure_name = 'REFRESH_ACC';
39
40
41 CURSOR l_deletes_csr(p_last_upd_date date)
42 IS
43 SELECT acc.access_id, curr.currency_code, curr_tl.language
44 FROM fnd_currencies curr,
45 fnd_currencies_tl curr_tl,
46 csm_currencies_acc acc
47 WHERE curr.currency_code = curr_tl.currency_code
48 AND curr.currency_code = acc.currency_code
49 AND ((SYSDATE not BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate))
50 OR curr.enabled_flag <> 'Y')
51 AND ((curr.creation_date < p_last_upd_date AND curr.last_update_date > p_last_upd_date)
52 OR (curr_tl.creation_date < p_last_upd_date AND curr_tl.last_update_date > p_last_upd_date)
53 );
54
55 CURSOR l_access_id_csr IS
56 select csm_currencies_acc_s.NEXTVAL from dual;
57
58 l_mark_dirty boolean;
59 l_curr_last_update_date fnd_currencies.LAST_UPDATE_DATE%TYPE;
60 l_curr_tl_last_update_date fnd_currencies.LAST_UPDATE_DATE%TYPE;
61 l_max_update_date fnd_currencies.LAST_UPDATE_DATE%TYPE;
62 l_language fnd_currencies_tl.language%TYPE;
63 l_currency_code fnd_currencies.currency_code%TYPE;
64 l_access_id CSM_currencies_ACC.ACCESS_ID%TYPE;
65 l_tl_omfs_palm_resource_list asg_download.user_list;
66 l_single_access_id_list asg_download.access_list;
67 --a null list
68 l_null_access_list asg_download.access_list;
69 l_run_date date;
70
71 BEGIN
72 -- get last conc program update date
73 OPEN l_last_run_date_csr(l_pub_item);
74 FETCH l_last_run_date_csr INTO l_prog_update_date;
75 CLOSE l_last_run_date_csr;
76
77 -- conc program run date
78 l_run_date := SYSDATE;
79
80 --get all the OMFS Palm users
81 g_all_palm_res_list := CSM_UTIL_PKG.get_all_omfs_palm_res_list;
82
83 /****** DELETES **********/
84 --open the cursor
85 open l_deletes_csr(l_prog_update_date);
86 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
87 LOOP
88 FETCH l_deletes_csr INTO l_access_id, l_currency_code, l_language;
89 EXIT WHEN l_deletes_csr%NOTFOUND;
90
91 --get the users with this language
92 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(l_language);
93
94 --nullify the access list
95 l_single_access_id_list := l_null_access_list;
96 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
97 l_single_access_id_list(i) := l_access_id;
98 END LOOP;
99
100 --mark dirty the SDQ for all users
101 IF l_single_access_id_list.count > 0 THEN
102 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_CURRENCIES',
103 l_single_access_id_list,
104 l_tl_omfs_palm_resource_list,
105 ASG_DOWNLOAD.DEL,
106 SYSDATE);
107 END IF;
108
109 --remove from ACC
110 DELETE FROM CSM_CURRENCIES_ACC
111 WHERE ACCESS_ID = l_access_id;
112
113 END LOOP;
114
115 --close the cursor
116 close l_deletes_csr;
117
118 /******* UPDATES **********/
119 --generate sql for updates
120 l_dsql :=
121 'select acc.access_id, curr_tl.language, curr.last_update_date, curr_tl.last_update_date
122 from csm_currencies_acc acc,
123 fnd_currencies curr,
124 fnd_currencies_tl curr_tl
125 where acc.currency_code = curr.currency_code
126 and curr_tl.currency_code = curr.currency_code
127 AND SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
128 AND curr.enabled_flag = ''Y''
129 AND (curr.last_update_date > :1
130 or curr_tl.last_update_date > :2
131 )';
132
133 -- AND (curr.last_update_date > ' || '''' || l_prog_update_date || '''' ||
134 -- ' or curr_tl.last_update_date > ' || '''' || l_prog_update_date || '''' ||
135 -- ')';
136
137
138 --open the cursor
139 open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date;
140 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
141 LOOP
142 FETCH l_updates_cur INTO l_access_id, l_language, l_curr_last_update_date, l_curr_tl_last_update_date;
143 EXIT WHEN l_updates_cur%NOTFOUND;
144
145 --get the users with this language
146 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(l_language);
147
148 --nullify the access list
149 l_single_access_id_list := l_null_access_list;
150 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
151 l_single_access_id_list(i) := l_access_id;
152 END LOOP;
153
154 --mark dirty the SDQ for all users
155 IF l_single_access_id_list.count > 0 THEN
156 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_CURRENCIES',
157 l_single_access_id_list, l_tl_omfs_palm_resource_list,
158 ASG_DOWNLOAD.UPD, sysdate);
159 END IF;
160
161 --get the max update date
162 IF (l_curr_last_update_date > l_curr_tl_last_update_date) THEN
163 l_max_update_date := l_curr_last_update_date;
164 ELSE
165 l_max_update_date := l_curr_tl_last_update_date;
166 END IF;
167
168 --update ACC
169 UPDATE CSM_BUS_PROCESS_TXNS_ACC
170 SET LAST_UPDATE_DATE = l_run_date --l_max_update_date
171 WHERE ACCESS_ID = l_access_id;
172
173 END LOOP;
174
175 --close the cursor
176 close l_updates_cur;
177
178 /****** INSERTS **********/
179 --generate sql for inserts
180
181 l_dsql :=
182 'SELECT curr.currency_code, curr.last_update_date
183 FROM fnd_currencies curr
184 WHERE SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
185 AND curr.enabled_flag = ''Y''
186 AND NOT EXISTS
187 (SELECT 1
188 FROM CSM_CURRENCIES_ACC acc
189 WHERE acc.currency_code = curr.currency_code
190 )';
191
192 --open the cursor
193 open l_inserts_cur for l_dsql;
194 --loop over cursor entries, and update in the acc table, as well as mark dirty the SDQ
195 LOOP
196 FETCH l_inserts_cur INTO l_currency_code, l_curr_last_update_date;
197 EXIT WHEN l_inserts_cur%NOTFOUND;
198
199 --generate access_id
200 -- select csm_currencies_acc_s.NEXTVAL into l_access_id from dual;
201 OPEN l_access_id_csr;
202 FETCH l_access_id_csr INTO l_access_id;
203 CLOSE l_access_id_csr;
204 --mark dirty the SDQ for all users
205 FOR i IN 1 .. g_all_palm_res_list.COUNT LOOP
206 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_CURRENCIES',
207 l_access_id, g_all_palm_res_list(i),
208 ASG_DOWNLOAD.INS, sysdate);
209 END LOOP;
210
211 --insert into
212 INSERT INTO csm_currencies_acc(currency_code, access_id, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
213 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
214 VALUES (l_currency_code, l_access_id, fnd_global.user_id, sysdate, fnd_global.user_id,
215 l_curr_last_update_date, fnd_global.login_id);
216
217 END LOOP;
218 --close the cursor
219 close l_inserts_cur;
220
221 -- set the program update date in asg_pub_item to sysdate
222 UPDATE jtm_con_request_data
223 SET last_run_date = l_run_date
224 WHERE package_name = 'CSM_CURRENCY_EVENT_PKG'
225 AND procedure_name = 'REFRESH_ACC';
226
227 commit;
228
229 p_status := 'FINE';
230 p_message := 'CSM_CURRENCY_EVENT_PKG.REFRESH_ACC Executed successfully';
231
232 EXCEPTION
233 WHEN others THEN
234 l_sqlerrno := to_char(SQLCODE);
235 l_sqlerrmsg := substr(SQLERRM, 1,2000);
236 p_status := 'ERROR';
237 p_message := 'Error in CSM_CURRENCY_EVENT_PKG.REFRESH_ACC :' ||l_sqlerrno || ':' ||l_sqlerrmsg;
238 ROLLBACK;
239 fnd_file.put_line(fnd_file.log, p_message);
240 END refresh_acc;
241
242 /*
243 PROCEDURE refresh_acc
244 IS
245 l_pub_item varchar2(30) := 'CSF_M_CURRENCIES';
246 l_prog_update_date asg_pub_item.last_run_date%TYPE;
247 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
248 l_mark_dirty boolean;
249 l_tl_omfs_palm_resource_list asg_download.user_list;
250 l_null_resource_list asg_download.user_list;
251 l_single_access_id_list asg_download.access_list;
252 --a null list
253 l_null_access_list asg_download.access_list;
254 l_run_date date;
255
256 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
257 IS
258 SELECT nvl(last_run_date, (sysdate - 365*50))
259 FROM asg_pub_item
260 WHERE name = p_pub_item
261 AND pub_name = 'SERVICEP';
262
263 CURSOR l_currency_ins_csr(p_last_upd_date date)
264 IS
265 SELECT curr.currency_code, curr_tl.language, curr.last_update_date
266 FROM fnd_currencies curr,
267 fnd_currencies_tl curr_tl
268 WHERE curr.currency_code = curr_tl.currency_code
269 AND SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
270 AND curr.enabled_flag = 'Y'
271 AND (curr.creation_date > p_last_upd_date
272 OR curr_tl.creation_date > p_last_upd_date);
273
274 CURSOR l_currency_upd_csr(p_last_upd_date date)
275 IS
276 SELECT curr.currency_code, curr_tl.language
277 FROM fnd_currencies curr,
278 fnd_currencies_tl curr_tl
279 WHERE curr.currency_code = curr_tl.currency_code
280 AND SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
281 AND curr.enabled_flag = 'Y'
282 AND ((curr.creation_date < p_last_upd_date AND curr.last_update_date > p_last_upd_date)
283 OR (curr_tl.creation_date < p_last_upd_date AND curr_tl.last_update_date > p_last_upd_date)
284 );
285
286 CURSOR l_currency_del_csr(p_last_upd_date date)
287 IS
288 SELECT curr.currency_code, curr_tl.language
289 FROM fnd_currencies curr,
290 fnd_currencies_tl curr_tl
291 WHERE curr.currency_code = curr_tl.currency_code
292 AND ((SYSDATE not BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate))
293 OR curr.enabled_flag <> 'Y')
294 AND ((curr.creation_date < p_last_upd_date AND curr.last_update_date > p_last_upd_date)
295 OR (curr_tl.creation_date < p_last_upd_date AND curr_tl.last_update_date > p_last_upd_date)
296 );
297
298 BEGIN
299 -- get last conc program update date
300 OPEN l_last_run_date_csr(l_pub_item);
301 FETCH l_last_run_date_csr INTO l_prog_update_date;
302 CLOSE l_last_run_date_csr;
303
304 -- conc program run date
305 l_run_date := SYSDATE;
306
307 -- process all inserts
308 FOR r_currency_ins_rec IN l_currency_ins_csr(l_prog_update_date) LOOP
309
310 --get the users with this language
311 l_tl_omfs_palm_resource_list := l_null_resource_list;
312 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_currency_ins_rec.language);
313
314 -- get the access_id
315 l_access_id := csm_util_pkg.generate_NumPK_FromStr(r_currency_ins_rec.currency_code || '.'
316 || r_currency_ins_rec.language);
317
318 --nullify the access list
319 l_single_access_id_list := l_null_access_list;
320 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
321 l_single_access_id_list(i) := l_access_id;
322 END LOOP;
323
324 --mark dirty the SDQ for all users
325 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_CURRENCIES',
326 l_single_access_id_list, l_tl_omfs_palm_resource_list,
327 ASG_DOWNLOAD.INS, sysdate);
328 END LOOP;
329
330 -- process all updates
331 FOR r_currency_upd_rec IN l_currency_upd_csr(l_prog_update_date) LOOP
332 --get the users with this language
333 l_tl_omfs_palm_resource_list := l_null_resource_list;
334 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_currency_upd_rec.language);
335
336 -- get the access_id
337 l_access_id := csm_util_pkg.generate_NumPK_FromStr(r_currency_upd_rec.currency_code || '.'
338 || r_currency_upd_rec.language);
339
340 --nullify the access list
341 l_single_access_id_list := l_null_access_list;
342 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
343 l_single_access_id_list(i) := l_access_id;
344 END LOOP;
345
346 --mark dirty the SDQ for all users
347 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_CURRENCIES',
348 l_single_access_id_list, l_tl_omfs_palm_resource_list,
349 ASG_DOWNLOAD.UPD, sysdate);
350
351 END LOOP;
352
353 -- process all deletes
354 FOR r_currency_del_rec IN l_currency_del_csr(l_prog_update_date) LOOP
355 --get the users with this language
356 l_tl_omfs_palm_resource_list := l_null_resource_list;
357 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_currency_del_rec.language);
358
359 -- get the access_id
360 l_access_id := csm_util_pkg.generate_NumPK_FromStr(r_currency_del_rec.currency_code || '.'
361 || r_currency_del_rec.language);
362
363 --nullify the access list
364 l_single_access_id_list := l_null_access_list;
365 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
366 l_single_access_id_list(i) := l_access_id;
367 END LOOP;
368
369 --mark dirty the SDQ for all users
370 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_CURRENCIES',
371 l_single_access_id_list, l_tl_omfs_palm_resource_list,
372 ASG_DOWNLOAD.DEL, sysdate);
373
374 END LOOP;
375
376 -- set the program update date in asg_pub_item to sysdate
377 UPDATE asg_pub_item
378 SET last_run_date = l_run_date
379 WHERE name = l_pub_item
380 AND pub_name = 'SERVICEP';
381
382 END refresh_acc;
383 */
384
385 END CSM_CURRENCY_EVENT_PKG;
[Home] [Help]