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