1 PACKAGE BODY CSM_MAIL_RECIPIENTS_PKG AS
2 /* $Header: csmumrcb.pls 120.1 2005/07/25 01:15:19 trajasek noship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- Anurag 06/12/02 Created
7 -- --------- ------ ------------------------------------------
8 -- Enter procedure, function bodies as shown below
9
10
11 /*** Globals ***/
12 g_object_name CONSTANT VARCHAR2(30) := 'CSM_MAIL_RECIPIENTS_PKG'; -- package name
13 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_M_MAIL_RECIPIENTS'; -- publication item name
14 g_debug_level NUMBER; -- debug level
15
16 CURSOR c_mail_recipients( b_user_name VARCHAR2, b_tranid NUMBER) is
17 SELECT *
18 FROM csf_m_mail_recipients_inq
19 WHERE tranid$$ = b_tranid
20 AND clid$$cs = b_user_name;
21
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_recipients%ROWTYPE,
29 p_error_msg OUT NOCOPY VARCHAR2,
30 x_return_status IN OUT NOCOPY VARCHAR2
31 ) IS
32
33 BEGIN
34
35 -- Insert in this table is not to be
36 -- transfered to CRM table. Therefore discard all inserts and report OK.
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39 END APPLY_INSERT;
40
41
42 /***
43 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
44 ***/
45 PROCEDURE APPLY_UPDATE
46 (
47 p_record IN c_mail_recipients%ROWTYPE,
48 p_error_msg OUT NOCOPY VARCHAR2,
49 x_return_status IN OUT NOCOPY VARCHAR2
50 ) IS
51
52 -- Make the cursor generic to avoid defining several.
53 cursor c_notification_attributes
54 ( b_notification_id number
55 , b_flag_name varchar2
56 )
57 is
58 select wna.notification_id
59 , wna.name
60 , wna.text_value
61 , wna.number_value
62 , wna.date_value
63 from wf_notification_attributes wna
64 where wna.notification_id = b_notification_id
65 and wna.name = b_flag_name;
66
67 r_notification_attributes c_notification_attributes%rowtype;
68
69 BEGIN
70
71 -- If a message is read or deleted a flag is set in csf_m_mail_recipients.
72 -- This is an update of the record and both updates can occur
73 -- seperately or combined.
74
75 -- Important. "Deleted" means on the mobile side. The notification
76 -- will *not* be deleted on the central database side.
77
78 -- The properties mentioned are stored in wf_notification_attributes.
79 -- For every update of the record a check must be done if the
80 -- attribute is allready present or that it must be inserted.
81 -- For example in the situation where both the notification is read and
82 -- deleted, it is impossible to detect if this is done in one or two
83 -- updates on the mobile side.
84
85 -- check if the value provided is non-null!
86 if p_record.read_flag is not NULL
87 then
88 -- Start with having read the notification.
89 open c_notification_attributes
90 ( p_record.notification_id
91 , 'READ_FLAG'
92 );
93 fetch c_notification_attributes into r_notification_attributes;
94 if c_notification_attributes%found
95 then
96 -- This records exists allready. We're done.
97 null;
98 else
99 wf_notification.AddAttr
100 ( p_record.notification_id
101 , 'READ_FLAG'
102 );
103
104 wf_notification.SetAttrText
105 ( p_record.notification_id
106 , 'READ_FLAG'
107 , p_record.read_flag
108 );
109 end if;
110 close c_notification_attributes;
111 end if;
112 -- Check the two possible flags seperately!
113 if p_record.delete_flag is not NULL
114 then
115 -- Check if the flag exists
116 open c_notification_attributes
117 ( p_record.notification_id
118 , 'DELETE_FLAG'
119 );
120 fetch c_notification_attributes into r_notification_attributes;
121 if c_notification_attributes%found
122 then
123 -- This records exists allready. We're done.
124 null;
125 else
126 wf_notification.AddAttr
127 ( p_record.notification_id
128 , 'DELETE_FLAG'
129 );
130
131 wf_notification.SetAttrText
132 ( p_record.notification_id
133 , 'DELETE_FLAG'
134 , p_record.delete_flag
135 );
136 end if;
137 close c_notification_attributes;
138 end if;
139
140 --success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 exception
144 when others then
145 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
146 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
147 (
148 p_api_error => TRUE
149 );
150 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
151 || ' for PK ' || p_record.NOTIFICATION_ID,'CSM_MAIL_RECIPIENTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
152
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 END APPLY_UPDATE;
155
156 /***
157 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
158 ***/
159 PROCEDURE APPLY_RECORD
160 (
161 p_record IN c_mail_recipients%ROWTYPE,
162 p_error_msg OUT NOCOPY VARCHAR2,
163 x_return_status IN OUT NOCOPY VARCHAR2
164 ) IS
165 BEGIN
166 /*** initialize return status and message list ***/
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168 FND_MSG_PUB.INITIALIZE;
169
170 IF p_record.dmltype$$='I' THEN
171 -- Process insert
172 APPLY_INSERT
173 (
174 p_record,
175 p_error_msg,
176 x_return_status
177 );
178 ELSIF p_record.dmltype$$='U' THEN
179 -- Process update
180 APPLY_UPDATE
181 (
182 p_record,
183 p_error_msg,
184 x_return_status
185 );
186 ELSE
187 -- Process delete; not supported for this entity
188 CSM_UTIL_PKG.LOG
189 ( 'Delete is not supported for this entity'
190 || ' for PK ' || p_record.notification_id,'CSM_MAIL_RECIPIENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR );
191
192 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
193 (
194 p_message => 'CSM_DML_OPERATION'
195 , p_token_name1 => 'DML'
196 , p_token_value1 => p_record.dmltype$$
197 );
198
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 END IF;
201
202 EXCEPTION WHEN OTHERS THEN
203 /*** defer record when any process exception occurs ***/
204 CSM_UTIL_PKG.LOG
205 ( 'Exception occurred in CSM_mail_recipients_PKG.APPLY_RECORD:' || ' ' || sqlerrm
206 || ' for PK ' || p_record.notification_id ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION);
207
208 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
209 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
210 (
211 p_api_error => TRUE
212 );
213
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 END APPLY_RECORD;
216
217 /***
218 This procedure is called by CSM_UTIL_PKG when publication item <replace>
219 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
220 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
221 public APIs.
222 ***/
223 PROCEDURE APPLY_CLIENT_CHANGES
224 (
225 p_user_name IN VARCHAR2,
226 p_tranid IN NUMBER,
227 p_debug_level IN NUMBER,
228 x_return_status IN OUT NOCOPY VARCHAR2
229 ) IS
230
231 l_process_status VARCHAR2(1);
232 l_error_msg VARCHAR2(4000);
233 BEGIN
234 g_debug_level := p_debug_level;
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237
238 /*** loop through debrief labor records in inqueue ***/
239 FOR r_mail_recipients IN c_mail_recipients( p_user_name, p_tranid) LOOP
240
241 SAVEPOINT save_rec;
242
243 /*** apply record ***/
244 APPLY_RECORD
245 (
246 r_mail_recipients
247 , l_error_msg
248 , l_process_status
249 );
250
251 /*** was record processed successfully? ***/
252 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
253 /*** Yes -> delete record from inqueue ***/
254
255 CSM_UTIL_PKG.DELETE_RECORD
256 (
257 p_user_name,
258 p_tranid,
259 r_mail_recipients.seqno$$,
260 r_mail_recipients.notification_id,
261 g_object_name,
262 g_pub_name,
263 l_error_msg,
264 l_process_status
265 );
266
267 /*** was delete successful? ***/
268 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
269 /*** no -> rollback ***/
270 CSM_UTIL_PKG.LOG
271 ( 'Deleting from inqueue failed, rolling back to savepoint'
272 || ' for PK ' || r_mail_recipients.notification_id ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
273 ROLLBACK TO save_rec;
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 END IF;
276 END IF;
277
278 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
279 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
280 CSM_UTIL_PKG.LOG
281 ( 'Record not processed successfully, deferring and rejecting record'
282 || ' for PK ' || r_mail_recipients.notification_id,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
283
284 CSM_UTIL_PKG.DEFER_RECORD
285 (
286 p_user_name
287 , p_tranid
288 , r_mail_recipients.seqno$$
289 , r_mail_recipients.notification_id
290 , g_object_name
291 , g_pub_name
292 , l_error_msg
293 , l_process_status
294 , r_mail_recipients.dmltype$$
295 );
296
297 /*** Was defer successful? ***/
298 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
299 /*** no -> rollback ***/
300 CSM_UTIL_PKG.LOG
301 ( 'Defer record failed, rolling back to savepoint'
302 || ' for PK ' || r_mail_recipients.notification_id,'CSM_MAIL_RECIPIENTS_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;
307
308 END LOOP;
309
310 EXCEPTION WHEN OTHERS THEN
311 /*** catch and log exceptions ***/
312 CSM_UTIL_PKG.LOG
313 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm
314 ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 END APPLY_CLIENT_CHANGES;
317
318 END CSM_MAIL_RECIPIENTS_PKG;
[Home] [Help]