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