[Home] [Help]
PACKAGE BODY: APPS.CSM_DEBRIEF_HEADERS_PKG
Source
1 PACKAGE BODY CSM_DEBRIEF_HEADERS_PKG AS
2 /* $Header: csmudbhb.pls 120.5.12020000.2 2013/04/09 11:02:46 saradhak ship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Melvin P 08/05/03 Create
13
14 /*** Globals ***/
15 G_OBJECT_NAME CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS_PKG'; -- package name
16 G_PUB_NAME CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS'; -- publication item name
17 G_DEBUG_LEVEL NUMBER; -- debug level
18 g_processed_assignment_list ASG_DOWNLOAD.ACCESS_LIST;
19
20 CURSOR c_debrief_headers( b_user_name VARCHAR2, b_tranid NUMBER) is
21 SELECT *
22 FROM csm_debrief_headers_inq
23 WHERE tranid$$ = b_tranid
24 AND clid$$cs = b_user_name;
25
26 CURSOR c_debrief_notes(p_debrief_header_id number, b_user_name varchar2, b_tranid number)
27 IS
28 SELECT jtf_note_id, source_object_id
29 FROM csf_m_notes_inq
30 WHERE tranid$$ = b_tranid
31 AND clid$$cs = b_user_name
32 AND source_object_code = 'SD'
33 AND source_object_id = p_debrief_header_id
34 FOR UPDATE OF source_object_id NOWAIT;
35
36 CURSOR c_debrief (p_task_assignment_id number)
37 IS
38 SELECT debrief_header_id
39 FROM csf_debrief_headers
40 WHERE task_assignment_id = p_task_assignment_id;
41
42 CURSOR c_debrief_signature(p_debrief_header_id IN NUMBER, b_user_name IN VARCHAR2,
43 b_tranid IN NUMBER)
44 IS
45 SELECT file_id, pk1_value
46 FROM csf_m_lobs_inq
47 WHERE tranid$$ = b_tranid
48 AND clid$$cs = b_user_name
49 AND entity_name = 'CSF_DEBRIEF_HEADERS'
50 AND to_number(pk1_value) = p_debrief_header_id
51 FOR UPDATE OF pk1_value NOWAIT;
52
53 /***
54 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
55 ***/
56 PROCEDURE APPLY_INSERT
57 (
58 p_record IN c_debrief_headers%ROWTYPE,
59 p_error_msg out nocopy VARCHAR2,
60 x_return_status IN out nocopy VARCHAR2
61 ) IS
62
63 cursor c_deb_head
64 ( b_task_assignment_id number
65 )
66 is
67 select debrief_header_id
68 , debrief_number
69 , debrief_date
70 , debrief_status_id
71 , task_assignment_id
72 , last_updated_by
73 , last_update_date
74 , ATTRIBUTE1
75 , ATTRIBUTE2
76 , ATTRIBUTE3
77 , ATTRIBUTE4
78 , ATTRIBUTE5
79 , ATTRIBUTE6
80 , ATTRIBUTE7
81 , ATTRIBUTE8
82 , ATTRIBUTE9
83 , ATTRIBUTE10
84 , ATTRIBUTE11
85 , ATTRIBUTE12
86 , ATTRIBUTE13
87 , ATTRIBUTE14
88 , ATTRIBUTE15
89 , ATTRIBUTE_CATEGORY
90 from csf_debrief_headers
91 where task_assignment_id = b_task_assignment_id;
92
93 r_deb_head c_deb_head%rowtype;
94
95 l_deb_rec csf_debrief_pub.debrief_rec_type;
96 l_line_rec csf_debrief_pub.debrief_line_rec_type;
97 l_line_tbl csf_debrief_pub.debrief_line_tbl_type;
98
99 l_debrief_header_id number;
100 l_date date := sysdate;
101
102 l_msg_data varchar2(1024);
103 l_msg_count number;
104
105 l_transaction_id number;
106 l_transaction_header_id number;
107 l_profile_value varchar2(240);
108
109 BEGIN
110
111 CSM_UTIL_PKG.log( 'Entered APPLY_INSERT for debrief_header_id'|| p_record.debrief_header_id,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
112
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 -- Start with some initialization.
116 -- We need to know if a debrief header record has been made
117 -- form this task_assignment_id. In that case we have to
118 -- reuse it instead of creating one.
119 -- Prerequisite: at most one record exist with the
120 -- task_assignment_id we're looking for.
121 open c_deb_head
122 ( p_record.task_assignment_id
123 );
124 fetch c_deb_head into r_deb_head;
125 if c_deb_head%found
126 then
127 l_debrief_header_id := r_deb_head.debrief_header_id;
128 else
129 l_debrief_header_id := null;
130 end if;
131 close c_deb_head;
132
133 if l_debrief_header_id is null
134 THEN
135 -- Create a debrief header record.
136 l_deb_rec.debrief_date := l_date;
137 l_debrief_header_id := p_record.debrief_header_id;
138 -- l_deb_rec.debrief_number := To_Char( p_record.debrief_header_id );
139
140 l_deb_rec.task_assignment_id := p_record.task_assignment_id;
141 l_deb_rec.debrief_header_id := l_debrief_header_id;
142 l_deb_rec.debrief_status_id := NULL;
143 l_deb_rec.last_update_date := l_date;
144 l_deb_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
145 l_deb_rec.creation_date := l_date;
146 l_deb_rec.created_by := NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
147 l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
148 l_deb_rec.attribute1 := p_record.attribute1;
149 l_deb_rec.attribute2 := p_record.attribute2;
150 l_deb_rec.attribute3 := p_record.attribute3;
151 l_deb_rec.attribute4 := p_record.attribute4;
152 l_deb_rec.attribute5 := p_record.attribute5;
153 l_deb_rec.attribute6 := p_record.attribute6;
154 l_deb_rec.attribute7 := p_record.attribute7;
155 l_deb_rec.attribute8 := p_record.attribute8;
156 l_deb_rec.attribute9 := p_record.attribute9;
157 l_deb_rec.attribute10 := p_record.attribute10;
158 l_deb_rec.attribute11 := p_record.attribute11;
159 l_deb_rec.attribute12 := p_record.attribute12;
160 l_deb_rec.attribute13 := p_record.attribute13;
161 l_deb_rec.attribute14 := p_record.attribute14;
162 l_deb_rec.attribute15 := p_record.attribute15;
163 l_deb_rec.attribute_category := p_record.attribute_category;
164 --Bug 5199436
165 l_deb_rec.TRAVEL_START_TIME := p_record.TRAVEL_START_TIME;
166 l_deb_rec.TRAVEL_END_TIME := p_record.TRAVEL_END_TIME;
167 l_deb_rec.TRAVEL_DISTANCE_IN_KM := p_record.TRAVEL_DISTANCE_IN_KM;
168 -- Create a debrief header.
169 csf_debrief_pub.create_debrief
170 ( p_api_version_number => 1.0
171 , p_init_msg_list => FND_API.G_TRUE
172 , p_commit => FND_API.G_FALSE
173 , p_debrief_rec => l_deb_rec
174 , p_debrief_line_tbl => l_line_tbl
175 , x_debrief_header_id => l_debrief_header_id
176 , x_return_status => x_return_status
177 , x_msg_count => l_msg_count
178 , x_msg_data => l_msg_data
179 );
180
181 -- This could have failed, so we need to check.
182 if x_return_status <> FND_API.G_RET_STS_SUCCESS
183 then
184 /*** exception occurred in API -> return errmsg ***/
185 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
186 (
187 p_api_error => TRUE
188 );
189 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
190 || ' ROOT ERROR: csf_debrief_pub.create_debrief'
191 || ' for PK ' || p_record.DEBRIEF_HEADER_ID,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 return;
194 ELSE
195 -- successful insert...need to reject the record on client
196 -- as debrief_header_id and debrief_number are generated by the API
197 CSM_UTIL_PKG.LOG ( 'Record successfully processed, rejecting record ' || ' for PK '
198 || p_record.debrief_header_id
199 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT'
200 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
201
202 CSM_UTIL_PKG.REJECT_RECORD
203 (
204 p_record.clid$$cs,
205 p_record.tranid$$,
206 p_record.seqno$$,
207 p_record.debrief_header_id,
208 g_object_name,
209 g_pub_name,
210 l_msg_data,
211 x_return_status
212 );
213
214 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
215 /*** Reject successful ***/
216 CSM_UTIL_PKG.LOG
217 ( 'Debrief Header record rejected ' || ' for PK '
218 || p_record.debrief_header_id
219 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT'
220 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
221 ELSE
222 /*** Reject unsuccessful ***/
223 CSM_UTIL_PKG.LOG
224 ( 'Debrief Header record not rejected ' || ' for PK '
225 || p_record.debrief_header_id
226 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT'
227 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
228
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 return;
231 END IF;
232 end if;
233 ELSE
234 -- debrief header already exists at the backend
235 -- reject record on client
236 CSM_UTIL_PKG.LOG ( 'Record successfully processed, rejecting record ' || ' for PK '
237 || p_record.debrief_header_id
238 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT'
239 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
240
241 CSM_UTIL_PKG.REJECT_RECORD
242 (
243 p_record.clid$$cs,
244 p_record.tranid$$,
245 p_record.seqno$$,
246 p_record.debrief_header_id,
247 g_object_name,
248 g_pub_name,
249 l_msg_data,
250 x_return_status
251 );
252
253 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
254 /*** Reject successful ***/
255 CSM_UTIL_PKG.LOG
256 ( 'Debrief Header record rejected ' || ' for PK '
257 || p_record.debrief_header_id
258 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT'
259 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
260 ELSE
261 /*** Reject unsuccessful ***/
262 CSM_UTIL_PKG.LOG
263 ( 'Debrief Header record not rejected ' || ' for PK '
264 || p_record.debrief_header_id
265 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT'
266 ,FND_LOG.LEVEL_PROCEDURE); -- put PK column here
267
268 x_return_status := FND_API.G_RET_STS_ERROR;
269 return;
270 END IF;
271
272 -- check for conflict detection
273 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
274
275 -- SERVER_WINS profile value
276 IF(l_profile_value = csm_profile_pkg.g_SERVER_WINS) AND
277 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_pub_name, p_record.seqno$$) <> FND_API.G_TRUE THEN
278 IF(r_deb_head.last_update_date <> p_record.server_last_update_date AND r_deb_head.last_updated_by <> NVL(p_record.last_updated_by,asg_base.get_user_id(p_record.clid$$cs))) THEN --12.1
279 p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST For CSF_DEBRIEF_HEADERS: CSM_DEBRIEF_HEADERS_PKG.APPLY_UPDATE: P_KEY = '
280 || p_record.debrief_header_id;
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 csm_util_pkg.log(p_error_msg, g_object_name || '.APPLY_UPDATE', FND_LOG.LEVEL_ERROR);
283 RETURN;
284 END IF;
285 ELSE -- client wins
286 -- apply client DFF's incase they exist since the last_updated_by is the same
287 -- Update the debrief header record.
288 l_deb_rec.debrief_date := r_deb_head.debrief_date;
289 l_deb_rec.debrief_number := r_deb_head.debrief_number;
290
291 l_deb_rec.task_assignment_id := r_deb_head.task_assignment_id;
292 l_deb_rec.debrief_header_id := r_deb_head.debrief_header_id;
293 l_deb_rec.debrief_status_id := r_deb_head.debrief_status_id;
294 l_deb_rec.last_update_date := l_date;
295 l_deb_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
296 l_deb_rec.creation_date := l_date;
297 l_deb_rec.created_by := NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
298 l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
299 l_deb_rec.attribute1 := p_record.attribute1;
300 l_deb_rec.attribute2 := p_record.attribute2;
301 l_deb_rec.attribute3 := p_record.attribute3;
302 l_deb_rec.attribute4 := p_record.attribute4;
303 l_deb_rec.attribute5 := p_record.attribute5;
304 l_deb_rec.attribute6 := p_record.attribute6;
305 l_deb_rec.attribute7 := p_record.attribute7;
306 l_deb_rec.attribute8 := p_record.attribute8;
307 l_deb_rec.attribute9 := p_record.attribute9;
308 l_deb_rec.attribute10 := p_record.attribute10;
309 l_deb_rec.attribute11 := p_record.attribute11;
310 l_deb_rec.attribute12 := p_record.attribute12;
311 l_deb_rec.attribute13 := p_record.attribute13;
312 l_deb_rec.attribute14 := p_record.attribute14;
313 l_deb_rec.attribute15 := p_record.attribute15;
314 l_deb_rec.attribute_category := p_record.attribute_category;
315 --Bug 5199436
316 l_deb_rec.TRAVEL_START_TIME := p_record.TRAVEL_START_TIME;
317 l_deb_rec.TRAVEL_END_TIME := p_record.TRAVEL_END_TIME;
318 l_deb_rec.TRAVEL_DISTANCE_IN_KM := p_record.TRAVEL_DISTANCE_IN_KM;
319
320
321 -- update the debrief line
322 csf_debrief_pub.Update_debrief(
323 p_api_version_number => 1.0,
324 p_init_msg_list => FND_API.G_TRUE,
325 p_commit => FND_API.G_FALSE,
326 p_debrief_rec => l_deb_rec,
327 X_Return_Status => x_return_status,
328 X_Msg_Count => l_msg_count,
329 X_Msg_Data => l_msg_data
330 );
331
332 -- This could have failed, so we need to check.
333 if x_return_status <> FND_API.G_RET_STS_SUCCESS
334 then
335 /*** exception occurred in API -> return errmsg ***/
336 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
337 (
338 p_api_error => TRUE
339 );
340 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
341 || ' ROOT ERROR: csf_debrief_pub.create_debrief'
342 || ' for PK ' || r_deb_head.DEBRIEF_HEADER_ID,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 return;
345 end if;
346 END IF;
347 END IF;
348
349 -- success
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 exception
353 when others then
354 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
355 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
356 (
357 p_api_error => TRUE
358 );
359
360 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
361 || ' for PK ' || p_record.DEBRIEF_HEADER_ID,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION );
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 END APPLY_INSERT;
364
365
366 PROCEDURE APPLY_UPDATE
367 (
368 p_record IN c_debrief_headers%ROWTYPE,
369 p_error_msg OUT NOCOPY VARCHAR2,
370 x_return_status IN OUT NOCOPY VARCHAR2
371 ) IS
372
373 CURSOR c_debrief_header
374 ( b_debrief_header_id number,
375 b_task_assignment_id number
376 )
377 IS
378 SELECT dh.debrief_header_id
379 , dh.debrief_number
380 , dh.last_update_date
381 , dh.last_updated_by
382 FROM csf_debrief_headers dh
383 WHERE dh.debrief_header_id = b_debrief_header_id
384 UNION
385 SELECT dh.debrief_header_id
386 , dh.debrief_number
387 , dh.last_update_date
388 , dh.last_updated_by
389 FROM csf_debrief_headers dh
390 WHERE dh.task_assignment_id = b_task_assignment_id
391 ;
392
393 r_debrief_header c_debrief_header%ROWTYPE;
394 l_profile_value varchar2(240);
395 l_deb_rec csf_debrief_pub.debrief_rec_type;
396 l_date date := sysdate;
397 -- Declare OUT parameters
398 l_msg_count NUMBER;
399 l_msg_data VARCHAR2(240);
400
401 BEGIN
402 CSM_UTIL_PKG.log( 'Entered APPLY_UPDATE for debrief_header_id'|| p_record.DEBRIEF_HEADER_ID,'CSM_DEBRIEF_HEADERS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
403
404 l_profile_value := fnd_profile.value('JTM_APPL_CONFLICT_RULE');
405
406 IF l_profile_value = 'SERVER_WINS' AND
407 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_pub_name, p_record.seqno$$) <> FND_API.G_TRUE THEN
408 OPEN c_debrief_header(b_debrief_header_id => p_record.debrief_header_id, b_task_assignment_id => p_record.task_assignment_id);
409 FETCH c_debrief_header INTO r_debrief_header;
410 IF c_debrief_header%FOUND THEN
411 IF (r_debrief_header.last_update_date <> p_record.server_last_update_date AND r_debrief_header.last_updated_by <> NVL(p_record.last_updated_by,asg_base.get_user_id(p_record.clid$$cs))) THEN --12.1
412 CLOSE c_debrief_header;
413 CSM_UTIL_PKG.log( 'Record has stale data. Leaving ' || g_object_name || '.APPLY_UPDATE:'
414 || ' for PK ' || p_record.debrief_header_id,
415 g_object_name || '.APPLY_UPDATE',
416 FND_LOG.LEVEL_PROCEDURE );
417 fnd_message.set_name
418 ( 'JTM'
419 , 'JTM_STALE_DATA'
420 );
421 fnd_msg_pub.ADD;
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 RETURN;
424 END IF;
425 ELSE
426 CSM_UTIL_PKG.log( 'No record found in Apps Database in ' || g_object_name || '.APPLY_UPDATE:',
427 g_object_name || '.APPLY_UPDATE',
428 FND_LOG.LEVEL_PROCEDURE );
429 END IF;
430 CLOSE c_debrief_header;
431 ELSE--if client wins or if server wins and the record is deferred Bug 5088801
432 OPEN c_debrief_header(b_debrief_header_id => p_record.debrief_header_id, b_task_assignment_id => p_record.task_assignment_id);
433 FETCH c_debrief_header INTO r_debrief_header;
434 CLOSE c_debrief_header;
435 END IF;
436
437 -- Update the debrief header record.
438 l_deb_rec.debrief_date := p_record.debrief_date;
439 l_deb_rec.debrief_number := r_debrief_header.debrief_number;
440
441 l_deb_rec.task_assignment_id := p_record.task_assignment_id;
442 l_deb_rec.debrief_header_id := r_debrief_header.debrief_header_id;
443 l_deb_rec.debrief_status_id := p_record.debrief_status_id;
444 l_deb_rec.last_update_date := l_date;
445 l_deb_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
446 l_deb_rec.creation_date := l_date;
447 l_deb_rec.created_by := NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
448 l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
449 l_deb_rec.attribute1 := p_record.attribute1;
450 l_deb_rec.attribute2 := p_record.attribute2;
451 l_deb_rec.attribute3 := p_record.attribute3;
452 l_deb_rec.attribute4 := p_record.attribute4;
453 l_deb_rec.attribute5 := p_record.attribute5;
454 l_deb_rec.attribute6 := p_record.attribute6;
455 l_deb_rec.attribute7 := p_record.attribute7;
456 l_deb_rec.attribute8 := p_record.attribute8;
457 l_deb_rec.attribute9 := p_record.attribute9;
458 l_deb_rec.attribute10 := p_record.attribute10;
459 l_deb_rec.attribute11 := p_record.attribute11;
460 l_deb_rec.attribute12 := p_record.attribute12;
461 l_deb_rec.attribute13 := p_record.attribute13;
462 l_deb_rec.attribute14 := p_record.attribute14;
463 l_deb_rec.attribute15 := p_record.attribute15;
464 l_deb_rec.attribute_category := p_record.attribute_category;
465 --Bug 5199436
466 l_deb_rec.TRAVEL_START_TIME := p_record.TRAVEL_START_TIME;
467 l_deb_rec.TRAVEL_END_TIME := p_record.TRAVEL_END_TIME;
468 l_deb_rec.TRAVEL_DISTANCE_IN_KM := p_record.TRAVEL_DISTANCE_IN_KM;
469
470 -- update the debrief line
471 csf_debrief_pub.Update_debrief(
472 p_api_version_number => 1.0,
473 p_init_msg_list => FND_API.G_TRUE,
474 p_commit => FND_API.G_FALSE,
475 p_debrief_rec => l_deb_rec,
476 X_Return_Status => x_return_status,
477 X_Msg_Count => l_msg_count,
478 X_Msg_Data => l_msg_data
479 );
480
481 -- This could have failed, so we need to check.
482 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
483 THEN
484 /*** exception occurred in API -> return errmsg ***/
485 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
486 (
487 p_api_error => TRUE
488 );
489 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
490 || ' ROOT ERROR: csf_debrief_pub.update_debrief'
491 || ' for PK ' || p_record.DEBRIEF_HEADER_ID,'CSM_DEBRIEF_HEADERS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 RETURN;
494 END IF;
495
496 -- success
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498
499 EXCEPTION
500 WHEN others THEN
501 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
502 || ' for PK ' || p_record.debrief_header_id,
503 g_object_name || '.APPLY_UPDATE',
504 FND_LOG.LEVEL_EXCEPTION );
505
506 x_return_status := FND_API.G_RET_STS_ERROR;
507
508 END APPLY_UPDATE;
509
510 /***
511 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
512 ***/
513 PROCEDURE APPLY_RECORD
514 (
515 p_record IN c_debrief_headers%ROWTYPE,
516 p_error_msg out nocopy VARCHAR2,
517 x_return_status IN out nocopy VARCHAR2
518 ) IS
519 BEGIN
520 /*** initialize return status and message list ***/
521 x_return_status := FND_API.G_RET_STS_SUCCESS;
522 FND_MSG_PUB.INITIALIZE;
523
524 IF p_record.dmltype$$='I' THEN
525 -- Process insert
526 APPLY_INSERT
527 (
528 p_record,
529 p_error_msg,
530 x_return_status
531 );
532 ELSIF p_record.dmltype$$='U' THEN
533 -- Process update
534 APPLY_UPDATE
535 (
536 p_record,
537 p_error_msg,
538 x_return_status
539 );
540 ELSE
541 -- Process delete not supported for this entity
542 CSM_UTIL_PKG.LOG
543 ( 'Delete is not supported for this entity'
544 || ' for PK ' || p_record.debrief_header_id ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
545
546 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
547 (
548 p_message => 'CSM_DML_OPERATION'
549 , p_token_name1 => 'DML'
550 , p_token_value1 => p_record.dmltype$$
551 );
552
553 x_return_status := FND_API.G_RET_STS_ERROR;
554 END IF;
555
556 EXCEPTION WHEN OTHERS THEN
557 /*** defer record when any process exception occurs ***/
558 CSM_UTIL_PKG.LOG
559 ( 'Exception occurred in CSM_DEBRIEF_HEADERS_PKG.APPLY_RECORD:' || ' ' || sqlerrm
560 || ' for PK ' || p_record.debrief_header_id,'CSM_DEBRIEF_HEADERS_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION );
561
562 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
563 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
564 (
565 p_api_error => TRUE
566 );
567
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 END APPLY_RECORD;
570
571 /***
572 This procedure is called by CSM_UTIL_PKG when publication item <replace>
573 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
574 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
575 public APIs.
576 ***/
577 PROCEDURE APPLY_CLIENT_CHANGES
578 (
579 p_user_name IN VARCHAR2,
580 p_tranid IN NUMBER,
581 p_debug_level IN NUMBER,
582 x_return_status IN out nocopy VARCHAR2,
583 p_task_assignment_id IN NUMBER
584 ) IS
585 l_process_status VARCHAR2(1);
586 l_error_msg VARCHAR2(4000);
587 l_debrief_header_id csf_debrief_headers.debrief_header_id%TYPE;
588
589 TYPE c_curtype is ref cursor;
590 c_cur c_curtype;
591 r_debrief_headers c_debrief_headers%rowtype;
592 BEGIN
593 g_debug_level := p_debug_level;
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 csm_util_pkg.log('csm_debrief_header_pkg.apply_client_changes entered','CSM_DEBRIEF_HEADER_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR);
597
598 /*** loop through debrief parts records in inqueue ***/
599 IF p_task_assignment_id IS NULL THEN
600 OPEN c_cur FOR 'SELECT * FROM csm_debrief_headers_inq inq WHERE tranid$$ = '||p_tranid||' AND clid$$cs = '''||p_user_name
601 ||''' AND TASK_ASSIGNMENT_ID NOT IN ('||CSM_UTIL_PKG.get_String_fromList(g_processed_assignment_list)||')';
602 ELSE
603 g_processed_assignment_list(g_processed_assignment_list.COUNT+1):=p_task_assignment_id;
604 OPEN c_cur FOR 'SELECT * FROM csm_debrief_headers_inq WHERE tranid$$ = '||p_tranid||' AND clid$$cs = '''||p_user_name
605 ||''' AND TASK_ASSIGNMENT_ID='||p_task_assignment_id;
606 END IF;
607
608 LOOP
609 FETCH c_cur INTO r_debrief_headers;
610 EXIT WHEN c_cur%NOTFOUND;
611
612 SAVEPOINT save_rec;
613
614 /*** apply record ***/
615 APPLY_RECORD
616 (
617 r_debrief_headers
618 , l_error_msg
619 , l_process_status
620 );
621
622 /*** was record processed successfully? ***/
623 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
624 -- check to see if any notes exist for the debrief_header
625 -- if exists update source_object_id column to new value for debrief_header_id
626
627 OPEN c_debrief(r_debrief_headers.task_assignment_id);
628 FETCH c_debrief INTO l_debrief_header_id;
629 IF c_debrief%FOUND THEN
630 FOR r_debrief_notes IN c_debrief_notes(r_debrief_headers.debrief_header_id, r_debrief_headers.clid$$cs, r_debrief_headers.tranid$$) LOOP
631 UPDATE csf_m_notes_inq
632 SET source_object_id = l_debrief_header_id
633 WHERE CURRENT OF c_debrief_notes;
634 END LOOP;
635
636 FOR r_debrief_signature IN c_debrief_signature(r_debrief_headers.debrief_header_id, r_debrief_headers.clid$$cs, r_debrief_headers.tranid$$) LOOP
637 UPDATE csf_m_lobs_inq
638 SET pk1_value = l_debrief_header_id
639 WHERE CURRENT OF c_debrief_signature;
640 END LOOP;
641
642 END IF;
643 CLOSE c_debrief;
644
645 /*** Yes -> delete record from inqueue ***/
646
647 CSM_UTIL_PKG.DELETE_RECORD
648 (
649 p_user_name,
650 p_tranid,
651 r_debrief_headers.seqno$$,
652 r_debrief_headers.debrief_header_id,
653 g_object_name,
654 g_pub_name,
655 l_error_msg,
656 l_process_status
657 );
658
659 /*** was delete successful? ***/
660 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
661 /*** no -> rollback ***/
662 CSM_UTIL_PKG.LOG
663 ( 'Deleting from inqueue failed, rolling back to savepoint'
664 || ' for PK ' || r_debrief_headers.debrief_header_id,'CSM_DEBRIEF_HEADERS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
665 ROLLBACK TO save_rec;
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 END IF;
668 END IF;
669
670 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
671 OPEN c_debrief(r_debrief_headers.task_assignment_id);
672 FETCH c_debrief INTO l_debrief_header_id;
673 IF c_debrief%FOUND THEN
674 FOR r_debrief_notes IN c_debrief_notes(r_debrief_headers.debrief_header_id, r_debrief_headers.clid$$cs, r_debrief_headers.tranid$$) LOOP
675 UPDATE csf_m_notes_inq
676 SET source_object_id = l_debrief_header_id
677 WHERE CURRENT OF c_debrief_notes;
678 END LOOP;
679 FOR r_debrief_signature IN c_debrief_signature(r_debrief_headers.debrief_header_id, r_debrief_headers.clid$$cs, r_debrief_headers.tranid$$) LOOP
680 UPDATE csf_m_lobs_inq
681 SET pk1_value = l_debrief_header_id
682 WHERE CURRENT OF c_debrief_signature;
683 END LOOP;
684
685 END IF;
686 CLOSE c_debrief;
687
688 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
689 CSM_UTIL_PKG.LOG
690 ( 'Record not processed successfully, deferring and rejecting record'
691 || ' for PK ' || r_debrief_headers.debrief_header_id,'CSM_DEBRIEF_HEADERS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
692
693 CSM_UTIL_PKG.DEFER_RECORD
694 (
695 p_user_name
696 , p_tranid
697 , r_debrief_headers.seqno$$
698 , r_debrief_headers.debrief_header_id
699 , g_object_name
700 , g_pub_name
701 , l_error_msg
702 , l_process_status
703 , r_debrief_headers.dmltype$$
704 );
705
706 /*** Was defer successful? ***/
707 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
708 /*** no -> rollback ***/
709 CSM_UTIL_PKG.LOG
710 ( 'Defer record failed, rolling back to savepoint'
711 || ' for PK ' || r_debrief_headers.debrief_header_id ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
712 ROLLBACK TO save_rec;
713 x_return_status := FND_API.G_RET_STS_ERROR;
714 END IF;
715 END IF;
716
717 END LOOP;
718 CLOSE c_cur;
719
720 EXCEPTION WHEN OTHERS THEN
721 /*** catch and log exceptions ***/
722 CSM_UTIL_PKG.LOG
723 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || sqlerrm
724 ,'CSM_DEBRIEF_HEADERS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 END APPLY_CLIENT_CHANGES;
727
728 FUNCTION CONFLICT_RESOLUTION_METHOD (p_user_name IN VARCHAR2,
729 p_tran_id IN NUMBER,
730 p_sequence IN NUMBER)
731 RETURN VARCHAR2 IS
732 l_profile_value VARCHAR2(30) ;
733 l_user_id NUMBER ;
734 cursor get_user_id(l_tran_id in number,
735 l_user_name in varchar2,
736 l_sequence in number)
737 IS
738 SELECT b.last_updated_by
739 FROM csf_debrief_headers b,
740 csm_debrief_headers_inq a
741 WHERE a.clid$$cs = l_user_name
742 AND tranid$$ = l_tran_id
743 AND seqno$$ = l_sequence
744 AND a.debrief_header_id = b.debrief_header_id ;
745
746 BEGIN
747 CSM_UTIL_PKG.LOG('Entering CSM_DEBRIEF_HEADERS_PKG.CONFLICT_RESOLUTION_METHOD for user ' || p_user_name ,'CSM_DEBRIEF_HEADERS_PKG.CONFLICT_RESOLUTION_METHOD',FND_LOG.LEVEL_PROCEDURE);
748 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
749 OPEN get_user_id(p_tran_id, p_user_name, p_sequence) ;
750 FETCH get_user_id INTO l_user_id ;
751 CLOSE get_user_id ;
752
753 if l_profile_value = 'SERVER_WINS' AND l_user_id <> asg_base.get_user_id(p_user_name) then
754 RETURN 'S' ;
755 else
756 RETURN 'C' ;
757 END IF ;
758
759 EXCEPTION
760 WHEN OTHERS THEN
761 RETURN 'C';
762 END CONFLICT_RESOLUTION_METHOD;
763
764 END CSM_DEBRIEF_HEADERS_PKG;