1 PACKAGE BODY CSM_MAIL_MESSAGES_PKG AS
2 /* $Header: csmummsb.pls 120.5.12020000.2 2012/08/10 09:43:57 pramosin ship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- Anurag 06/10/02 Created
7 -- --------- ------ ------------------------------------------
8 -- Enter procedure, function bodies as shown below
9
10
11 /*** Globals ***/
12 g_object_name CONSTANT VARCHAR2(30) := 'CSM_MAIL_MESSAGES_PKG'; -- package name
13 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_M_MAIL_MESSAGES'; -- publication item name
14 g_pub_name2 CONSTANT VARCHAR2(30) := 'CSF_M_MAIL_RECIPIENTS'; -- publication item name
15 g_debug_level NUMBER; -- debug level
16
17 CURSOR c_mail_messages( b_user_name VARCHAR2, b_tranid NUMBER) is
18 SELECT *
19 FROM csf_m_mail_messages_inq
20 WHERE tranid$$ = b_tranid
21 AND clid$$cs = b_user_name;
22
23 /***
24 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
25 ***/
26 PROCEDURE APPLY_INSERT
27 (
28 p_record IN c_mail_messages%ROWTYPE,
29 p_error_msg OUT NOCOPY VARCHAR2,
30 x_return_status IN OUT NOCOPY VARCHAR2
31 ) IS
32
33 cursor c_user_name
34 ( b_user_name varchar2
35 )
36 is
37 select fur.user_name
38 , fur.start_date
39 , fur.end_date
40 from fnd_user fur
41 where fur.user_name = b_user_name;
42
43 r_user_name c_user_name%rowtype;
44
45 CURSOR l_sender_full_name_csr(p_username IN varchar2)
46 IS
47 SELECT source_name
48 FROM jtf_rs_resource_extns jtrs
49 WHERE jtrs.user_name = p_username
50 AND SYSDATE BETWEEN start_date_active AND nvl(end_date_active, sysdate);
51
52 CURSOR c_group(b_group_id NUMBER)
53 IS
54 SELECT 'y'
55 FROM JTF_RS_GROUPS_B
56 WHERE GROUP_ID=b_group_id;
57
58 l_return_status VARCHAR2(80);
59 l_notification_id number;
60 l_valid_receiver boolean;
61 l_group_id varchar2(100);
62 l_prefix varchar2(100);
63 l_valid_group boolean :=false;
64 l_num_grp number;
65 l_sender VARCHAR2(200);
66
67 l_wf_param wf_event_t;
68
69 BEGIN
70
71 --USMC changes
72 IF p_record.message_type='b' THEN
73
74 l_prefix:=substr(p_record.receiver,1,instr(p_record.receiver,':')-1);
75 l_group_id:=substr(p_record.receiver,instr(p_record.receiver,':')+1);
76 BEGIN
77 l_num_grp:=to_number(l_group_id);
78 EXCEPTION
79 WHEN Others THEN
80 l_num_grp:=-1;
81 END;
82
83 IF l_num_grp IS NOT NULL AND l_num_grp<>-1 AND l_prefix='JRES_GRP' THEN
84 OPEN c_group(l_num_grp);
85 FETCH c_group INTO l_group_id;
86 IF c_group%found THEN
87 l_valid_group := true;
88 END IF;
89 CLOSE c_group;
90 END IF;
91
92 END IF;
93
94 -- This API has no support for failing.
95 -- Therefore it is imperative to check if the receiver is correct.
96 -- The only information available for this receiver is that it must
97 -- correspond to user_name in fnd_user. The case in fnd_user is upper.
98 -- The mobile application ensures that the receiver (either chosen or
99 -- typed in manually) is always in uppercase. Thus, for comparision no
100 -- case changes is necessary.
101
102 open c_user_name
103 ( p_record.receiver
104 );
105 fetch c_user_name into r_user_name;
106 -- Note that this is only a check if the user_name exists as given.
107 -- No retrieval of additional information is necessary.
108 if c_user_name%found OR (p_record.message_type='b' AND l_valid_group)
109 then
110 l_valid_receiver := true;
111 else
112 l_valid_receiver := false;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 p_error_msg := 'The receiver '||p_record.receiver ||' of your message does not exist.';
115
116 /*
117 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
118 (
119 p_message => 'CSL_MAIL_RECEIVER_UNKNOWN'
120 , p_token_name1 => 'MAIL_RECEIVER'
121 , p_token_value1 => p_record.receiver
122 );*/
123 end if;
124 close c_user_name;
125
126 -- Send the intended message to the receiver if the receiver is valid.
127 if l_valid_receiver
128 THEN
129
130 l_sender := NVL(p_record.sender,p_record.clid$$cs);
131
132 wf_event_t.initialize(l_wf_param);
133 l_wf_param.AddParameterToList('TEMPLATE','FYI_MESSAGE');
134 l_wf_param.AddParameterToList('SUBJECT',p_record.subject);
135 l_wf_param.AddParameterToList('MESSAGE_BODY',p_record.message);
136 l_wf_param.AddParameterToList('#FROM_ROLE',l_sender);
137
138 l_notification_id:= CSM_NOTIFICATION_EVENT_PKG.invoke_WF_NotifyProcess(p_record.receiver,l_wf_param);
139
140 IF(l_notification_id = -1) THEN
141 RAISE_APPLICATION_ERROR(-20989, 'Unable to invoke CSM_MSGS- FYI_MESSAGE Workflow Process');
142 END IF;
143
144 CSM_NOTIFICATION_EVENT_PKG.DOWNLOAD_NOTIFICATION(l_notification_id,l_return_status); --return_status doesn't matter here
145
146 BEGIN -- since FROM_ROLE column is filled only when mailer parses it.
147 SELECT 1 INTO l_num_grp FROM CSM_NOTIFICATIONS_ACC WHERE NOTIFICATION_ID=l_notification_id AND USER_ID=asg_base.get_user_id(l_sender);
148 EXCEPTION
149 WHEN OTHERS THEN
150 CSM_NOTIFICATION_EVENT_PKG.INSERT_NOTIFICATIONS_ACC(l_notification_id,asg_base.get_user_id(l_sender));
151 END;
152
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154 end if;
155
156 exception
157 when others then
158 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
159 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
160 (
161 p_api_error => TRUE
162 );
163 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
164 || ' for PK ' || p_record.NOTIFICATION_ID ,'CSM_MAIL_MESSAGES_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 END APPLY_INSERT;
167
168
169 /***
170 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
171 ***/
172 PROCEDURE APPLY_RECORD
173 (
174 p_record IN c_mail_messages%ROWTYPE,
175 p_error_msg OUT NOCOPY VARCHAR2,
176 x_return_status IN OUT NOCOPY VARCHAR2
177 )
178 IS
179 BEGIN
180 /*** initialize return status and message list ***/
181 x_return_status := FND_API.G_RET_STS_SUCCESS;
182 FND_MSG_PUB.INITIALIZE;
183
184 IF p_record.dmltype$$='I' THEN
185 -- Process insert
186 APPLY_INSERT
187 (
188 p_record,
189 p_error_msg,
190 x_return_status
191 );
192 ELSE
193 -- Process delete and update; not supported for this entity
194 CSM_UTIL_PKG.LOG
195 ( 'This Operation is not supported for this entity'
196 || ' for PK ' || p_record.notification_id ,'CSM_MAIL_MESSAGES_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
197
198 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
199 (
200 p_message => 'CSM_DML_OPERATION'
201 , p_token_name1 => 'DML'
202 , p_token_value1 => p_record.dmltype$$
203 );
204
205 x_return_status := FND_API.G_RET_STS_ERROR;
206 END IF;
207
208 EXCEPTION WHEN OTHERS THEN
209 /*** defer record when any process exception occurs ***/
210 CSM_UTIL_PKG.LOG
211 ( 'Exception occurred in CSM_mail_messages_PKG.APPLY_RECORD:' || ' ' || sqlerrm
212 || ' for PK ' || p_record.notification_id,'CSM_MAIL_MESSAGES_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION );
213
214 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
215 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
216 (
217 p_api_error => TRUE
218 );
219
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 END APPLY_RECORD;
222
223 /***
224 This procedure is called by CSM_UTIL_PKG when publication item <replace>
225 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
226 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
227 public APIs.
228 ***/
229 PROCEDURE APPLY_CLIENT_CHANGES
230 (
231 p_user_name IN VARCHAR2,
232 p_tranid IN NUMBER,
233 p_debug_level IN NUMBER,
234 x_return_status IN OUT NOCOPY VARCHAR2
235 ) IS
236
237 l_process_status VARCHAR2(1);
238 l_error_msg VARCHAR2(4000);
239
240 BEGIN
241 g_debug_level := p_debug_level;
242 x_return_status := FND_API.G_RET_STS_SUCCESS;
243
244
245
246 /*** loop through debrief labor records in inqueue ***/
247 FOR r_mail_messages IN c_mail_messages( p_user_name, p_tranid) LOOP
248
249 SAVEPOINT save_rec;
250
251 /*** apply record ***/
252 APPLY_RECORD
253 (
254 r_mail_messages
255 , l_error_msg
256 , l_process_status
257 );
258
259 /*** was record processed successfully? ***/
260 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
261
262 IF r_mail_messages.dmltype$$='I' THEN
263 /*** Yes -> reject record because of changed pk ***/
264 CSM_UTIL_PKG.LOG ( 'Record successfully processed, rejecting record ' || ' for PK '
265 || r_mail_messages.notification_id
266 ,'CSM_MAIL_MESSAGES_PKG.APPLY_CLIENT_CHANGES'
267 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
268
269 CSM_UTIL_PKG.REJECT_RECORD
270 (
271 p_user_name,
272 p_tranid,
273 r_mail_messages.seqno$$,
274 r_mail_messages.notification_id,
275 g_object_name,
276 g_pub_name,
277 l_error_msg,
278 l_process_status
279 );
280 END IF; --if r_mail_messages.dmltype$$='I'
281
282 --if deferred and reapplied - reject won't delete applied record from inq
283 IF (r_mail_messages.dmltype$$<>'I') OR ( ASG_DEFER.is_deferred(p_user_name,p_tranid,g_pub_name,r_mail_messages.seqno$$)=FND_API.G_TRUE) THEN
284 /*** Yes -> delete record from inqueue ***/
285
286 CSM_UTIL_PKG.DELETE_RECORD
287 (
288 p_user_name,
289 p_tranid,
290 r_mail_messages.seqno$$,
291 r_mail_messages.notification_id,
292 g_object_name,
293 g_pub_name,
294 l_error_msg,
295 l_process_status
296 );
297 /*** was delete successful? ***/
298 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
299 /*** no -> rollback ***/
300 CSM_UTIL_PKG.LOG
301 ( 'Deleting from inqueue failed, rolling back to savepoint'
302 || ' for PK ' || r_mail_messages.notification_id ,'CSM_MAIL_MESSAGES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
303 ROLLBACK TO save_rec;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 END IF;
306 END IF; --dmltype <> 'I' or deferred
307 ELSE -- l_process_Status <> FND_API.G_RET_STS_SUCCESS
308 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
309 CSM_UTIL_PKG.LOG
310 ( 'Record not processed successfully, deferring and rejecting record'
311 || ' for PK ' || r_mail_messages.notification_id ,'CSM_MAIL_MESSAGES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
312
313 CSM_UTIL_PKG.DEFER_RECORD
314 (
315 p_user_name
316 , p_tranid
317 , r_mail_messages.seqno$$
318 , r_mail_messages.notification_id
319 , g_object_name
320 , g_pub_name
321 , l_error_msg
322 , l_process_status
323 , r_mail_messages.dmltype$$
324 );
325
326 /*** Was defer successful? ***/
327 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
328 /*** no -> rollback ***/
329 CSM_UTIL_PKG.LOG
330 ( 'Defer record failed, rolling back to savepoint'
331 || ' for PK ' || r_mail_messages.notification_id,'CSM_MAIL_MESSAGES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
332 ROLLBACK TO save_rec;
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 END IF;
335 END IF;
336
337 END LOOP;
338
339 EXCEPTION WHEN OTHERS THEN
340 /*** catch and log exceptions ***/
341 CSM_UTIL_PKG.LOG
342 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm
343 ,'CSM_MAIL_MESSAGES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 END APPLY_CLIENT_CHANGES;
346
347 END CSM_MAIL_MESSAGES_PKG;
[Home] [Help]