1 PACKAGE BODY CSM_MAIL_RECIPIENTS_PKG AS
2 /* $Header: csmumrcb.pls 120.1.12020000.3 2013/04/09 11:04:11 saradhak ship $ */
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 g_notification_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_NOTIFICATIONS_ACC';
17 g_notification_pk1_name CONSTANT VARCHAR2(30) := 'NOTIFICATION_ID';
18 g_notification_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
19 CSM_ACC_PKG.t_publication_item_list('CSF_M_MAIL_MESSAGES', 'CSF_M_MAIL_RECIPIENTS');
20
21 CURSOR c_mail_recipients( b_user_name VARCHAR2, b_tranid NUMBER) is
22 SELECT *
23 FROM csf_m_mail_recipients_inq
24 WHERE tranid$$ = b_tranid
25 AND clid$$cs = b_user_name;
26
27
28 /***
29 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
30 ***/
31 PROCEDURE APPLY_INSERT
32 (
33 p_record IN c_mail_recipients%ROWTYPE,
34 p_error_msg OUT NOCOPY VARCHAR2,
35 x_return_status IN OUT NOCOPY VARCHAR2
36 ) IS
37
38 BEGIN
39
40 -- Insert in this table is not to be
41 -- transfered to CRM table. Therefore discard all inserts and report OK.
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43
44 END APPLY_INSERT;
45
46
47 /***
48 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
49 ***/
50 PROCEDURE APPLY_UPDATE
51 (
52 p_record IN c_mail_recipients%ROWTYPE,
53 p_error_msg OUT NOCOPY VARCHAR2,
54 x_return_status IN OUT NOCOPY VARCHAR2
55 ) IS
56
57 l_dummy NUMBER;
58
59 BEGIN
60
61 CSM_UTIL_PKG.LOG ( 'Entering Update for '|| p_record.notification_id
62 ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
63
64 if p_record.delete_flag IS NOT NULL AND substr(p_record.delete_flag,1,1) = 'Y' then
65
66 CSM_UTIL_PKG.LOG ( 'Delete flag set for notification '|| p_record.notification_id
67 ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
68
69 begin
70 wf_notification.respond(nid=>p_record.NOTIFICATION_ID,responder=>p_record.clid$$cs);
71 exception
72 when others then
73 null;
74 end;
75
76 CSM_ACC_PKG.Delete_Acc
77 ( P_PUBLICATION_ITEM_NAMES => g_notification_pubi_name
78 ,P_ACC_TABLE_NAME => g_notification_acc_table_name
79 ,P_PK1_NAME => g_notification_pk1_name
80 ,P_PK1_NUM_VALUE => p_record.notification_id
81 ,P_USER_ID => asg_base.get_user_id(p_record.clid$$cs)
82 );
83 else -- any other update - means this msg is read
84 CSM_UTIL_PKG.LOG ( 'Read flag is being set for notification '|| p_record.notification_id
85 ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
86 begin
87 select 1 into l_dummy
88 from wf_notification_attributes wna
89 where wna.notification_id = p_record.notification_id
90 and wna.name = 'READ_FLAG';
91 exception
92 when others then
93 wf_notification.AddAttr( p_record.notification_id, 'READ_FLAG');
94 end;
95 wf_notification.SetAttrText( p_record.notification_id, 'READ_FLAG', NVL(substr(p_record.read_flag,1,1),'T'));
96 end if;
97
98
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 CSM_UTIL_PKG.LOG ( 'Leaving Update for '|| p_record.notification_id
102 ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
103
104 exception
105 when others then
106 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
107 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
108 (
109 p_api_error => TRUE
110 );
111 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
112 || ' for PK ' || p_record.NOTIFICATION_ID,'CSM_MAIL_RECIPIENTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
113
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 END APPLY_UPDATE;
116
117 /***
118 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
119 ***/
120 PROCEDURE APPLY_RECORD
121 (
122 p_record IN c_mail_recipients%ROWTYPE,
123 p_error_msg OUT NOCOPY VARCHAR2,
124 x_return_status IN OUT NOCOPY VARCHAR2
125 ) IS
126 BEGIN
127 /*** initialize return status and message list ***/
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129 FND_MSG_PUB.INITIALIZE;
130
131 IF p_record.dmltype$$='I' THEN
132 -- Process insert
133 APPLY_INSERT
134 (
135 p_record,
136 p_error_msg,
137 x_return_status
138 );
139 ELSIF p_record.dmltype$$='U' THEN
140 -- Process update
141 APPLY_UPDATE
142 (
143 p_record,
144 p_error_msg,
145 x_return_status
146 );
147 ELSE
148 -- Process delete; not supported for this entity
149 CSM_UTIL_PKG.LOG
150 ( 'Delete is not supported for this entity'
151 || ' for PK ' || p_record.notification_id,'CSM_MAIL_RECIPIENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR );
152
153 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
154 (
155 p_message => 'CSM_DML_OPERATION'
156 , p_token_name1 => 'DML'
157 , p_token_value1 => p_record.dmltype$$
158 );
159
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 END IF;
162
163 EXCEPTION WHEN OTHERS THEN
164 /*** defer record when any process exception occurs ***/
165 CSM_UTIL_PKG.LOG
166 ( 'Exception occurred in CSM_mail_recipients_PKG.APPLY_RECORD:' || ' ' || sqlerrm
167 || ' for PK ' || p_record.notification_id ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION);
168
169 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
170 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
171 (
172 p_api_error => TRUE
173 );
174
175 x_return_status := FND_API.G_RET_STS_ERROR;
176 END APPLY_RECORD;
177
178 /***
179 This procedure is called by CSM_UTIL_PKG when publication item <replace>
180 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
181 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
182 public APIs.
183 ***/
184 PROCEDURE APPLY_CLIENT_CHANGES
185 (
186 p_user_name IN VARCHAR2,
187 p_tranid IN NUMBER,
188 p_debug_level IN NUMBER,
189 x_return_status IN OUT NOCOPY VARCHAR2
190 ) IS
191
192 l_process_status VARCHAR2(1);
193 l_error_msg VARCHAR2(4000);
194 BEGIN
195 g_debug_level := p_debug_level;
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197
198
199 /*** loop through debrief labor records in inqueue ***/
200 FOR r_mail_recipients IN c_mail_recipients( p_user_name, p_tranid) LOOP
201
202 SAVEPOINT save_rec;
203
204 /*** apply record ***/
205 APPLY_RECORD
206 (
207 r_mail_recipients
208 , l_error_msg
209 , l_process_status
210 );
211
212 /*** was record processed successfully? ***/
213 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
214 /*** Yes -> delete record from inqueue ***/
215
216 CSM_UTIL_PKG.DELETE_RECORD
217 (
218 p_user_name,
219 p_tranid,
220 r_mail_recipients.seqno$$,
221 r_mail_recipients.notification_id,
222 g_object_name,
223 g_pub_name,
224 l_error_msg,
225 l_process_status
226 );
227
228 /*** was delete successful? ***/
229 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
230 /*** no -> rollback ***/
231 CSM_UTIL_PKG.LOG
232 ( 'Deleting from inqueue failed, rolling back to savepoint'
233 || ' for PK ' || r_mail_recipients.notification_id ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
234 ROLLBACK TO save_rec;
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 END IF;
237
238 ELSE --l_process_Status <> FND_API.G_RET_STS_SUCCESS
239 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
240 CSM_UTIL_PKG.LOG
241 ( 'Record not processed successfully, deferring and rejecting record'
242 || ' for PK ' || r_mail_recipients.notification_id,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
243
244 CSM_UTIL_PKG.DEFER_RECORD
245 (
246 p_user_name
247 , p_tranid
248 , r_mail_recipients.seqno$$
249 , r_mail_recipients.notification_id
250 , g_object_name
251 , g_pub_name
252 , l_error_msg
253 , l_process_status
254 , r_mail_recipients.dmltype$$
255 );
256
257 /*** Was defer successful? ***/
258 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
259 /*** no -> rollback ***/
260 CSM_UTIL_PKG.LOG
261 ( 'Defer record failed, rolling back to savepoint'
262 || ' for PK ' || r_mail_recipients.notification_id,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
263 ROLLBACK TO save_rec;
264 x_return_status := FND_API.G_RET_STS_ERROR;
265 END IF;
266 END IF;
267
268 END LOOP;
269
270 EXCEPTION WHEN OTHERS THEN
271 /*** catch and log exceptions ***/
272 CSM_UTIL_PKG.LOG
273 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm
274 ,'CSM_MAIL_RECIPIENTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 END APPLY_CLIENT_CHANGES;
277
278 END CSM_MAIL_RECIPIENTS_PKG;
[Home] [Help]