[Home] [Help]
PACKAGE BODY: APPS.CSM_NOTES_PKG
Source
1 PACKAGE BODY CSM_NOTES_PKG AS
2 /* $Header: csmunotb.pls 120.4 2008/03/24 09:00:13 ptomar 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_NOTES_PKG'; -- package name
13 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_M_NOTES'; -- publication item name
14 g_debug_level NUMBER; -- debug level
15
16 CURSOR c_notes( b_user_name VARCHAR2, b_tranid NUMBER) is
17 SELECT *
18 FROM csf_m_notes_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_notes%ROWTYPE,
29 p_error_msg OUT NOCOPY VARCHAR2,
30 x_return_status IN OUT NOCOPY VARCHAR2
31 ) IS
32
33 l_msg_count number;
34 l_msg_data varchar2(1024);
35
36 l_jtf_note_id number;
37
38 BEGIN
39
40 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_INSERT:'
41 || ' for PK ' || p_record.jtf_note_id ,
42 'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
43
44 --create the note
45 CSM_UTIL_PKG.log( 'Creating Note ' || g_object_name || '.APPLY_INSERT:'
46 || ' for PK ' || p_record.jtf_note_id ,
47 'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_EVENT);
48 jtf_notes_pub.Create_note
49 ( p_api_version => 1.0
50 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
51 , p_init_msg_list => FND_API.G_TRUE
52 , p_commit => FND_API.G_FALSE
53 , x_return_status => x_return_status
54 , x_msg_count => l_msg_count
55 , x_msg_data => l_msg_data
56 , p_jtf_note_id => p_record.jtf_note_id
57 , p_source_object_id => p_record.source_object_id
58 , p_source_object_code => p_record.source_object_code
59 , p_notes => p_record.notes
60 , p_note_status => p_record.note_status
61 , p_note_type => p_record.note_type
62 , p_entered_by => p_record.entered_by
63 , p_entered_date => p_record.entered_date
64 , p_created_by => NVL(p_record.created_by,FND_GLOBAL.USER_ID) --12.1
65 , p_creation_date => SYSDATE
66 , p_last_updated_by => NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
67 , p_last_update_date => SYSDATE
68 , p_last_update_login => FND_GLOBAL.LOGIN_ID
69 , x_jtf_note_id => l_jtf_note_id
70 );
71
72 if x_return_status <> FND_API.G_RET_STS_SUCCESS
73 then
74 /*** exception occurred in API -> return errmsg ***/
75 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
76 (
77 p_api_error => TRUE
78 );
79 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
80 || ' ROOT ERROR: jtf_notes_pub.create_note'
81 || ' for PK ' || p_record.JTF_NOTE_ID ,'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
82
83 x_return_status := FND_API.G_RET_STS_ERROR;
84 else
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86 end if;
87
88 exception
89 when others then
90 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
91 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
92 (
93 p_api_error => TRUE
94 );
95 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
96 || ' for PK ' || p_record.jtf_note_id , 'CSM_NOTES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
97 x_return_status := FND_API.G_RET_STS_ERROR;
98
99 END APPLY_INSERT;
100
101
102 /***
103 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
104 ***/
105 PROCEDURE APPLY_UPDATE
106 (
107 p_record IN c_notes%ROWTYPE,
108 p_error_msg OUT NOCOPY VARCHAR2,
109 x_return_status IN OUT NOCOPY VARCHAR2
110 ) IS
111
112 l_msg_count number;
113 l_msg_data varchar2(1024);
114
115 l_server_last_update_date date;
116 CURSOR l_server_last_update_date_csr(p_jtf_note_id jtf_notes_b.jtf_note_id%TYPE)
117 IS
118 select last_update_date from jtf_notes_b
119 where jtf_note_id = p_jtf_note_id;
120
121 BEGIN
122 CSM_UTIL_PKG.log( 'Entering ' || g_object_name || '.APPLY_UPDATE:'
123 || ' for PK ' || p_record.jtf_note_id,
124 'CSM_NOTES_PKG.APPLY_UPDATE',
125 FND_LOG.LEVEL_PROCEDURE );
126 --get the last update date of the note
127
128 --select last_update_date into l_server_last_update_date
129 --from jtf_notes_b
130 --where jtf_note_id = p_record.jtf_note_id;
131
132 OPEN l_server_last_update_date_csr(p_record.jtf_note_id);
133 FETCH l_server_last_update_date_csr INTO l_server_last_update_date;
134 CLOSE l_server_last_update_date_csr;
135
136 --check for the stale data
137 -- SERVER_WINS profile value
138 if(fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE)
139 = csm_profile_pkg.g_SERVER_WINS) then
140 if(l_server_last_update_date <> p_record.server_last_update_date) then
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_NOTES_PKG.APPLY_UPDATE: P_KEY = '
143 || p_record.jtf_note_id;
144 csm_util_pkg.log(p_error_msg,'CSM_NOTES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EVENT );
145 return;
146 end if;
147 end if;
148
149 --CLIENT_WINS (or client is allowd to update the record)
150
151 --update the note
152 jtf_notes_pub.Update_note
153 ( p_api_version => 1.0
154 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
155 , p_init_msg_list => FND_API.G_TRUE
156 , p_commit => FND_API.G_FALSE
157 , x_return_status => x_return_status
158 , x_msg_count => l_msg_count
159 , x_msg_data => l_msg_data
160 , p_jtf_note_id => p_record.jtf_note_id
161 , p_notes => p_record.notes
162 , p_note_status => p_record.note_status
163 , p_note_type => p_record.note_type
164 , p_entered_by => p_record.entered_by
165 , p_last_updated_by => NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
166 , p_last_update_date => SYSDATE
167 , p_last_update_login => FND_GLOBAL.LOGIN_ID
168 );
169
170 if x_return_status <> FND_API.G_RET_STS_SUCCESS
171 then
172 /*** exception occurred in API -> return errmsg ***/
173 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
174 (
175 p_api_error => TRUE
176 );
177 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
178 || ' ROOT ERROR: jtf_notes_pub.update_note'
179 || ' for PK ' || p_record.JTF_NOTE_ID ,'CSM_NOTES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
180
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 else
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184 end if;
185
186 CSM_UTIL_PKG.log( 'Exiting ' || g_object_name || '.APPLY_UPDATE:'
187 || ' for PK ' || p_record.jtf_note_id,
188 'CSM_NOTES_PKG.APPLY_UPDATE',
189 FND_LOG.LEVEL_PROCEDURE );
190 exception
191 when others then
192 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
193 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
194 (
195 p_api_error => TRUE
196 );
197
198 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
199 || ' for PK ' || p_record.jtf_note_id,
200 'CSM_NOTES_PKG.APPLY_UPDATE',
201 FND_LOG.LEVEL_ERROR );
202
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 END APPLY_UPDATE;
205
206 /***
207 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
208 ***/
209 PROCEDURE APPLY_RECORD
210 (
211 p_record IN c_notes%ROWTYPE,
212 p_error_msg OUT NOCOPY VARCHAR2,
213 x_return_status IN OUT NOCOPY VARCHAR2
214 ) IS
215 BEGIN
216 /*** initialize return status and message list ***/
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218 FND_MSG_PUB.INITIALIZE;
219
220 IF p_record.dmltype$$='I' THEN
221 -- Process insert
222 APPLY_INSERT
223 (
224 p_record,
225 p_error_msg,
226 x_return_status
227 );
228 ELSE /*IF p_record.dmltype$$='U' THEN
229 -- Process update
230 APPLY_UPDATE
231 (
232 p_record,
233 p_error_msg,
234 x_return_status
235 );
236 ELSE*/
237 -- Process delete; not supported for this entity
238 CSM_UTIL_PKG.LOG
239 ( 'Update and Delete is not supported for this entity'
240 || ' for PK ' || p_record.jtf_note_id,
241 'CSM_NOTES_PKG.APPLY_RECORD',
242 FND_LOG.LEVEL_EVENT );
243
244 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
245 (
246 p_message => 'CSM_DML_OPERATION'
247 , p_token_name1 => 'DML'
248 , p_token_value1 => p_record.dmltype$$
249 );
250
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 END IF;
253
254 EXCEPTION WHEN OTHERS THEN
255 /*** defer record when any process exception occurs ***/
256 CSM_UTIL_PKG.LOG
257 ( 'Exception occurred in CSM_notes_PKG.APPLY_RECORD:' || ' ' || sqlerrm
258 || ' for PK ' || p_record.jtf_note_id,
259 'CSM_NOTES_PKG.APPLY_RECORD',
260 FND_LOG.LEVEL_ERROR );
261
262 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
263 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
264 (
265 p_api_error => TRUE
266 );
267
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 END APPLY_RECORD;
270
271 /***
272 This procedure is called by CSM_UTIL_PKG when publication item <replace>
273 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
274 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
275 public APIs.
276 ***/
277 PROCEDURE APPLY_CLIENT_CHANGES
278 (
279 p_user_name IN VARCHAR2,
280 p_tranid IN NUMBER,
281 p_debug_level IN NUMBER,
282 x_return_status IN OUT NOCOPY VARCHAR2
283 ) IS
284
285 l_process_status VARCHAR2(1);
286 l_error_msg VARCHAR2(4000);
287 BEGIN
288 g_debug_level := p_debug_level;
289 x_return_status := FND_API.G_RET_STS_SUCCESS;
290
291
292
293 /*** loop through debrief labor records in inqueue ***/
294 FOR r_notes IN c_notes( p_user_name, p_tranid) LOOP
295
296 SAVEPOINT save_rec;
297
298 /*** apply record ***/
299 APPLY_RECORD
300 (
301 r_notes
302 , l_error_msg
303 , l_process_status
304 );
305
306 /*** was record processed successfully? ***/
307 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
308 /*** Yes -> delete record from inqueue ***/
309
310 CSM_UTIL_PKG.DELETE_RECORD
311 (
312 p_user_name,
313 p_tranid,
314 r_notes.seqno$$,
315 r_notes.jtf_note_id,
316 g_object_name,
317 g_pub_name,
318 l_error_msg,
319 l_process_status
320 );
321
322 /*** was delete successful? ***/
323 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
324 /*** no -> rollback ***/
325 CSM_UTIL_PKG.LOG
326 ( 'Deleting from inqueue failed, rolling back to savepoint'
327 || ' for PK ' || r_notes.jtf_note_id,
328 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
329 FND_LOG.LEVEL_EVENT ); -- put PK column here
330 ROLLBACK TO save_rec;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 END IF;
333 END IF;
334
335 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
336 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
337 CSM_UTIL_PKG.LOG
338 ( 'Record not processed successfully, deferring and rejecting record'
339 || ' for PK ' || r_notes.jtf_note_id,
340 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
341 FND_LOG.LEVEL_EVENT); -- put PK column here
342
343 CSM_UTIL_PKG.DEFER_RECORD
344 (
345 p_user_name
346 , p_tranid
347 , r_notes.seqno$$
348 , r_notes.jtf_note_id
349 , g_object_name
350 , g_pub_name
351 , l_error_msg
352 , l_process_status
353 , r_notes.dmltype$$
354 );
355
356 /*** Was defer successful? ***/
357 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
358 /*** no -> rollback ***/
359 CSM_UTIL_PKG.LOG
360 ( 'Defer record failed, rolling back to savepoint'
361 || ' for PK ' || r_notes.jtf_note_id,
362 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
363 FND_LOG.LEVEL_EVENT ); -- put PK column here
364 ROLLBACK TO save_rec;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 END IF;
367 END IF;
368
369 END LOOP;
370
371 EXCEPTION WHEN OTHERS THEN
372 /*** catch and log exceptions ***/
373 CSM_UTIL_PKG.LOG
374 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm ,
375 'CSM_NOTES_PKG.APPLY_CLIENT_CHANGES',
376 FND_LOG.LEVEL_ERROR
377 );
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 END APPLY_CLIENT_CHANGES;
380
381 END CSM_NOTES_PKG;