[Home] [Help]
PACKAGE BODY: APPS.CSM_MESSAGES_EVENT_PKG
Source
1 PACKAGE BODY CSM_MESSAGES_EVENT_PKG AS
2 /* $Header: csmemsgb.pls 120.1 2005/07/25 00:12:41 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_Acc (p_status OUT NOCOPY VARCHAR2,
15 p_message OUT NOCOPY VARCHAR2)
16 IS
17 PRAGMA AUTONOMOUS_TRANSACTION;
18 l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
19 l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;
20 l_dsql varchar2(2048);
21 l_pub_item varchar2(30) := 'CSF_M_MESSAGES';
22 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
23 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
24 l_mark_dirty boolean;
25 l_tl_omfs_palm_resource_list asg_download.user_list;
26 l_null_resource_list asg_download.user_list;
27 l_single_access_id_list asg_download.access_list;
28 --a null list
29 l_null_access_list asg_download.access_list;
30 l_run_date date;
31 l_sqlerrno varchar2(20);
32 l_sqlerrmsg varchar2(2000);
33
34 CURSOR l_last_run_date_csr(p_pub_item IN varchar2)
35 IS
36 SELECT nvl(last_run_date, (sysdate - 365*50))
37 FROM jtm_con_request_data
38 WHERE package_name = 'CSM_MESSAGES_EVENT_PKG'
39 AND procedure_name = 'REFRESH_ACC';
40
41 -- inserts cur
42 CURSOR l_messages_ins_csr(p_last_upd_date date)
43 IS
44 SELECT msg.application_id, msg.message_name, msg.language_code
45 FROM fnd_new_messages msg
46 WHERE
47 ((msg.application_id = 513 -- CSF application
48 AND msg.message_name like 'CSF_M_%')
49 OR msg.application_id = 883) -- CSM application
50 AND NOT EXISTS
51 (SELECT 1
52 FROM csm_messages_acc acc
53 WHERE acc.application_id = msg.application_id
54 AND acc.message_name = msg.message_name
55 AND acc.language_code = msg.language_code
56 );
57
58 -- updates cur
59 CURSOR l_messages_upd_csr(p_last_upd_date date)
60 IS
61 SELECT acc.access_id, msg.application_id, msg.message_name, msg.language_code
62 FROM fnd_new_messages msg,
63 csm_messages_acc acc
64 WHERE (msg.creation_date < p_last_upd_date AND msg.last_update_date > p_last_upd_date)
65
66 AND ((msg.application_id = 513 -- CSF application
67 AND msg.message_name like 'CSF_M_%')
68 OR msg.application_id = 883) -- CSM application
69 AND acc.application_id = msg.application_id
70 AND acc.message_name = msg.message_name
71 AND acc.language_code = msg.language_code;
72
73 -- deletes cur
74 CURSOR l_messages_del_csr
75 IS
76 SELECT acc.access_id, acc.language_code
77 FROM csm_messages_acc acc
78 WHERE NOT EXISTS
79 (SELECT 1
80 FROM fnd_new_messages msg
81 WHERE ((msg.application_id = 513 -- CSF application
82 AND msg.message_name like 'CSF_M_%')
83 OR msg.application_id = 883) -- CSM application
84 AND acc.application_id = msg.application_id
85 AND acc.message_name = msg.message_name
86 AND acc.language_code = msg.language_code
87 );
88
89 BEGIN
90 -- data program is run
91 l_run_date := SYSDATE;
92
93 -- get last conc program update date
94 OPEN l_last_run_date_csr(l_pub_item);
95 FETCH l_last_run_date_csr INTO l_prog_update_date;
96 CLOSE l_last_run_date_csr;
97
98 -- process all deletes
99 FOR r_messages_del_rec IN l_messages_del_csr LOOP
100
101 --get the users with this language
102 l_tl_omfs_palm_resource_list := l_null_resource_list;
103 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_messages_del_rec.language_code);
104
105 l_access_id := r_messages_del_rec.access_id;
106
107 --nullify the access list
108 l_single_access_id_list := l_null_access_list;
109 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
110 l_single_access_id_list(i) := l_access_id;
111 END LOOP;
112
113 --mark dirty the SDQ for all users
114 IF l_single_access_id_list.count > 0 THEN
115 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_MESSAGES',
116 l_single_access_id_list, l_tl_omfs_palm_resource_list,
117 ASG_DOWNLOAD.DEL, sysdate);
118 END IF;
119
120 -- delete from acc table
121 DELETE FROM csm_messages_acc WHERE access_id = l_access_id;
122 END LOOP;
123
124 -- process all updates
125 FOR r_messages_upd_rec IN l_messages_upd_csr(l_prog_update_date) LOOP
126
127 --get the users with this language
128 l_tl_omfs_palm_resource_list := l_null_resource_list;
129 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_messages_upd_rec.language_code);
130
131 l_access_id := r_messages_upd_rec.access_id;
132
133 --nullify the access list
134 l_single_access_id_list := l_null_access_list;
135 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
136 l_single_access_id_list(i) := l_access_id;
137 END LOOP;
138
139 --mark dirty the SDQ for all users
140 IF l_single_access_id_list.count > 0 THEN
141 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_MESSAGES',
142 l_single_access_id_list, l_tl_omfs_palm_resource_list,
143 ASG_DOWNLOAD.UPD, sysdate);
144 END IF;
145
146 END LOOP;
147
148 -- process all inserts
149 FOR r_messages_ins_rec IN l_messages_ins_csr(l_prog_update_date) LOOP
150
151 --get the users with this language
152 l_tl_omfs_palm_resource_list := l_null_resource_list;
153 l_tl_omfs_palm_resource_list := csm_util_pkg.get_tl_omfs_palm_resources(r_messages_ins_rec.language_code);
154
155 SELECT csm_messages_acc_s.nextval
156 INTO l_access_id
157 FROM dual;
158
159 --nullify the access list
160 l_single_access_id_list := l_null_access_list;
161 FOR i in 1 .. l_tl_omfs_palm_resource_list.COUNT LOOP
162 l_single_access_id_list(i) := l_access_id;
163 END LOOP;
164
165 --mark dirty the SDQ for all users
166 IF l_single_access_id_list.count > 0 THEN
167 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSF_M_MESSAGES',
168 l_single_access_id_list, l_tl_omfs_palm_resource_list,
169 ASG_DOWNLOAD.INS, sysdate);
170 END IF;
171
172 INSERT INTO csm_messages_acc (access_id,
173 application_id,
174 message_name,
175 language_code,
176 created_by,
177 creation_date,
178 last_updated_by,
179 last_update_date,
180 last_update_login
181 )
182 VALUES (l_access_id,
183 r_messages_ins_rec.application_id,
184 r_messages_ins_rec.message_name,
185 r_messages_ins_rec.language_code,
186 fnd_global.user_id,
187 l_run_date,
188 fnd_global.user_id,
189 l_run_date,
190 fnd_global.login_id
191 );
192
193 END LOOP;
194
195 -- set the program update date in jtm_con_request_data to sysdate
196 UPDATE jtm_con_request_data
197 SET last_run_date = l_run_date
198 WHERE package_name = 'CSM_MESSAGES_EVENT_PKG'
199 AND procedure_name = 'REFRESH_ACC';
200
201 COMMIT;
202
203 p_status := 'FINE';
204 p_message := 'CSM_MESSAGES_EVENT_PKG.Refresh_Acc Executed successfully';
205
206 EXCEPTION
207 WHEN others THEN
208 l_sqlerrno := to_char(SQLCODE);
209 l_sqlerrmsg := substr(SQLERRM, 1,2000);
210 p_status := 'ERROR';
211 p_message := 'Error in CSM_MESSAGES_EVENT_PKG.Refresh_Acc : ' || l_sqlerrno || ':' || l_sqlerrmsg;
212 fnd_file.put_line(fnd_file.log, p_message);
213 ROLLBACK;
214 END Refresh_Acc;
215 END CSM_MESSAGES_EVENT_PKG;