[Home] [Help]
PACKAGE BODY: APPS.CSM_DEBRIEF_EXPENSES_PKG
Source
1 PACKAGE BODY CSM_DEBRIEF_EXPENSES_PKG AS
2 /* $Header: csmudbeb.pls 120.5.12020000.2 2013/04/09 11:02:20 saradhak ship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- Anurag 06/10/02 Created
7 -- --------- ------ ------------------------------------------
8 -- Enter procedure, function bodies as shown below
9
10
11 /*** Globals ***/
12 g_object_name CONSTANT VARCHAR2(30) := 'CSM_DEBRIEF_EXPENSES_PKG'; -- package name
13 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_M_DEBRIEF_EXPENSES'; -- publication item name
14 g_debug_level NUMBER; -- debug level
15 g_processed_assignment_list ASG_DOWNLOAD.ACCESS_LIST;
16
17 CURSOR c_debrief_expenses( b_user_name VARCHAR2, b_tranid NUMBER) is
18 SELECT *
19 FROM csf_m_debrief_expenses_inq
20 WHERE tranid$$ = b_tranid
21 AND clid$$cs = b_user_name;
22
23 /***
24 This procedure is called by APPLY_RECORD when an inserted record is to be processed.
25 ***/
26 PROCEDURE APPLY_INSERT
27 (
28 p_record IN c_debrief_expenses%ROWTYPE,
29 p_error_msg OUT NOCOPY VARCHAR2,
30 x_return_status IN OUT NOCOPY VARCHAR2
31 ) IS
32
33 cursor c_deb_head
34 ( b_task_assignment_id number
35 )
36 is
37 select debrief_header_id
38 , task_assignment_id
39 from csf_debrief_headers
40 where task_assignment_id = b_task_assignment_id;
41
42 cursor c_task_obj_code
43 ( b_task_assignment_id number
44 )
45 is
46 select source_object_type_code
47 from jtf_tasks_b jtb
48 , jtf_task_assignments jta
49 where jtb.task_id = jta.task_id
50 and jta.task_assignment_id = b_task_assignment_id;
51
52 r_deb_head c_deb_head%rowtype;
53 r_task_obj_code c_task_obj_code%rowtype;
54
55 -- Cursor to check if the Assignment Status is either of the
56 -- following rejected, on_hold, cancelled, closed or completed
57 CURSOR c_chk_task_status
58 ( p_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
59 ) IS
60 SELECT tst.rejected_flag, tst.on_hold_flag, tst.cancelled_flag,
61 tst.closed_flag, tst.completed_flag
62 FROM csf_debrief_headers dh, jtf_task_assignments tas,
63 jtf_task_statuses_b tst
64 WHERE dh.task_assignment_id = tas.task_assignment_id
65 AND tas.assignment_status_id = tst.task_status_id
66 AND dh.debrief_header_id = p_debrief_header_id;
67
68 l_rejected_flag VARCHAR2(1);
69 l_on_hold_flag VARCHAR2(1);
70 l_cancelled_flag VARCHAR2(1);
71 l_closed_flag VARCHAR2(1);
72 l_completed_flag VARCHAR2(1);
73
74 l_deb_rec csf_debrief_pub.debrief_rec_type;
75
76 l_line_rec csf_debrief_pub.debrief_line_rec_type;
77 l_line_tbl csf_debrief_pub.debrief_line_tbl_type;
78
79
80 l_debrief_header_id number;
81 l_date date := sysdate;
82
83 l_issuing_inventory_org_id csf_debrief_lines.issuing_inventory_org_id%TYPE;
84 l_receiving_inventory_org_id csf_debrief_lines.receiving_inventory_org_id%TYPE;
85
86 l_msg_data varchar2(1024);
87 l_msg_count number;
88
89 BEGIN
90
91 CSM_UTIL_PKG.log( 'Entered APPLY_INSERT for debrief_line_id'|| p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
92
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 -- Start with some initialization.
96 -- We need to know if a debrief header record has been made
97 -- form this task_assignment_id. In that case we have to
98 -- reuse it instead of creating one.
99 -- Prerequisite: at most one record exist with the
100 -- task_assignment_id we're looking for.
101 open c_deb_head
102 ( p_record.task_assignment_id
103 );
104 fetch c_deb_head into r_deb_head;
105 if c_deb_head%found
106 then
107 l_debrief_header_id := r_deb_head.debrief_header_id;
108 else
109 l_debrief_header_id := null;
110 end if;
111 close c_deb_head;
112
113
114
115 -- Create a debrief header record.
116 l_deb_rec.debrief_date := l_date;
117 --l_deb_rec.debrief_number := To_Char( l_debrief_header_id );
118
119 l_deb_rec.task_assignment_id := p_record.task_assignment_id;
120 l_deb_rec.debrief_header_id := l_debrief_header_id;
121 l_deb_rec.debrief_status_id := NULL;
122 l_deb_rec.last_update_date := l_date;
123 l_deb_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
124 l_deb_rec.creation_date := l_date;
125 l_deb_rec.created_by := NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
126 l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
127
128 if l_debrief_header_id is null
129 then
130 -- Create a debrief header.
131 l_deb_rec.debrief_number := null ;
132
133 csf_debrief_pub.create_debrief
134 ( p_api_version_number => 1.0
135 , p_init_msg_list => FND_API.G_TRUE
136 , p_commit => FND_API.G_FALSE
137 , p_debrief_rec => l_deb_rec
138 , p_debrief_line_tbl => l_line_tbl
139 , x_debrief_header_id => l_debrief_header_id
140 , x_return_status => x_return_status
141 , x_msg_count => l_msg_count
142 , x_msg_data => l_msg_data
143 );
144
145 -- This could have failed, so we need to check.
146 if x_return_status <> FND_API.G_RET_STS_SUCCESS
147 THEN
148 csm_util_pkg.log('ABCD Test 03');
149 /*** exception occurred in API -> return errmsg ***/
150 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
151 (
152 p_api_error => TRUE
153 );
154 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
155 || ' ROOT ERROR: csf_debrief_pub.create_debrief'
156 || ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 return;
159 end if;
160 end if;
161
162 -- Make the debrief line.
163
164 -- Retrieve the issuing organization id.
165 -- We may have to replace this with another master_organization_id.
166
167 l_line_rec.debrief_line_id := p_record.debrief_line_id;
168 l_line_rec.debrief_header_id := l_debrief_header_id;
169 l_line_rec.issuing_inventory_org_id := l_issuing_inventory_org_id;
170 l_line_rec.receiving_inventory_org_id := l_receiving_inventory_org_id;
171 l_line_rec.last_update_date := l_date;
172 l_line_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
173 l_line_rec.creation_date := l_date;
174 l_line_rec.created_by := NVL(p_record.created_by,FND_GLOBAL.USER_ID); --12.1
175 l_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
176 l_line_rec.inventory_item_id := p_record.inventory_item_id;
177 l_line_rec.txn_billing_type_id := p_record.txn_billing_type_id;
178 --l_line_rec.debrief_line_number := To_Char( p_record.debrief_line_id );
179 l_line_rec.uom_code := p_record.uom_code;
180 l_line_rec.quantity := p_record.quantity;
181 l_line_rec.expense_amount := p_record.expense_amount;
182 l_line_rec.currency_code := p_record.currency_code;
183 l_line_rec.service_date := nvl(p_record.service_date,l_date);
184 l_line_rec.business_process_id := p_record.business_process_id;
185 l_line_rec.channel_code := 'CSF_MFS';
186 l_line_rec.transaction_type_id := p_record.transaction_type_id;
187 l_line_rec.expense_reason_code := p_record.expense_reason_code;
188 l_line_rec.attribute1 := p_record.attribute1;
189 l_line_rec.attribute2 := p_record.attribute2;
190 l_line_rec.attribute3 := p_record.attribute3;
191 l_line_rec.attribute4 := p_record.attribute4;
192 l_line_rec.attribute5 := p_record.attribute5;
193 l_line_rec.attribute6 := p_record.attribute6;
194 l_line_rec.attribute7 := p_record.attribute7;
195 l_line_rec.attribute8 := p_record.attribute8;
196 l_line_rec.attribute9 := p_record.attribute9;
197 l_line_rec.attribute10 := p_record.attribute10;
198 l_line_rec.attribute11 := p_record.attribute11;
199 l_line_rec.attribute12 := p_record.attribute12;
200 l_line_rec.attribute13 := p_record.attribute13;
201 l_line_rec.attribute14 := p_record.attribute14;
202 l_line_rec.attribute15 := p_record.attribute15;
203 l_line_rec.attribute_category := p_record.attribute_category;
204
205 l_line_tbl(1) := l_line_rec;
206
207 -- Fetch SOURCE_OBJECT_TYPE_CODE from task record
208 open c_task_obj_code
209 ( p_record.task_assignment_id
210 );
211 fetch c_task_obj_code into r_task_obj_code;
212 close c_task_obj_code;
213
214 csf_debrief_pub.create_debrief_lines
215 ( p_api_version_number => 1.0
216 , p_init_msg_list => FND_API.G_TRUE
217 , p_commit => FND_API.G_FALSE
218 , x_return_status => x_return_status
219 , x_msg_count => l_msg_count
220 , x_msg_data => l_msg_data
221 , p_debrief_header_id => l_debrief_header_id
222 , p_debrief_line_tbl => l_line_tbl
223 , p_source_object_type_code => r_task_obj_code.source_object_type_code
224 );
225 if x_return_status <> FND_API.G_RET_STS_SUCCESS
226 then
227 /*** exception occurred in API -> return errmsg ***/
228 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
229 (
230 p_api_error => TRUE
231 );
232 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
233 || ' ROOT ERROR: csf_debrief_pub.create_debrief_lines'
234 || ' for PK ' || p_record.DEBRIEF_LINE_ID ,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 return;
237 end if;
238
239 -- For a given debrief header check the task Assignment status.
240 -- If it is one of the following -
241 -- rejected, on_hold, cancelled, closed or completed then call the api
242 -- csf_debrief_update_pkg.form_Call for processing charges
243
244 OPEN c_chk_task_status ( l_debrief_header_id );
245 FETCH c_chk_task_status INTO l_rejected_flag, l_on_hold_flag,
246 l_cancelled_flag, l_closed_flag, l_completed_flag;
247
248 IF c_chk_task_status%FOUND THEN
249 IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y') OR (l_cancelled_flag='Y')
250 OR (l_closed_flag='Y') OR (l_completed_flag='Y') ) THEN
251 csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );
252 END IF;
253 END IF;
254
255 CLOSE c_chk_task_status;
256
257 exception
258 when others then
259 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
260 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
261 (
262 p_api_error => TRUE
263 );
264 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
265 || ' for PK ' || p_record.DEBRIEF_LINE_ID ,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
266 x_return_status := FND_API.G_RET_STS_ERROR;
267 END APPLY_INSERT;
268
269 /***
270 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
271 ***/
272 PROCEDURE APPLY_UPDATE
273 (
274 p_record IN c_debrief_expenses%ROWTYPE,
275 p_error_msg OUT NOCOPY VARCHAR2,
276 x_return_status IN OUT NOCOPY VARCHAR2
277 ) IS
278
279 CURSOR c_cdl
280 ( b_debrief_line_id csf_debrief_lines.debrief_line_id%TYPE
281 )
282 IS
283 SELECT cdl.debrief_header_id
284 , cdl.debrief_line_id
285 , cdl.last_update_date
286 , cdl.issuing_inventory_org_id
287 FROM csf_debrief_lines cdl
288 WHERE cdl.debrief_line_id = b_debrief_line_id;
289
290 r_cdl c_cdl%ROWTYPE;
291
292 l_line_rec csf_debrief_pub.debrief_line_rec_type;
293
294 l_debrief_header_id number;
295 l_date date := sysdate;
296
297 l_msg_data varchar2(1024);
298 l_msg_count number;
299
300
301 BEGIN
302
303 CSM_UTIL_PKG.log( 'Entered APPLY_UPDATE for debrief_line_id'|| p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
304
305 x_return_status := FND_API.G_RET_STS_SUCCESS;
306
307 -- Lookup the debrief_header id. It must be there as this is an update
308 -- of a line.
309 OPEN c_cdl
310 (b_debrief_line_id => p_record.debrief_line_id
311 );
312 FETCH c_cdl
313 INTO r_cdl;
314 IF c_cdl%found
315 THEN
316 l_debrief_header_id := r_cdl.debrief_header_id;
317 ELSE
318 -- Let the API complain about it.
319 l_debrief_header_id := NULL;
320 END IF;
321 CLOSE c_cdl;
322
323 -- Make the debrief line.
324 l_line_rec.issuing_inventory_org_id := r_cdl.issuing_inventory_org_id;
325 l_line_rec.debrief_line_id := p_record.debrief_line_id;
326 l_line_rec.debrief_header_id := l_debrief_header_id;
327 l_line_rec.last_update_date := l_date;
328 l_line_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
329 l_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
330 l_line_rec.inventory_item_id := p_record.inventory_item_id;
331 l_line_rec.txn_billing_type_id := p_record.txn_billing_type_id;
332 l_line_rec.service_date := nvl(p_record.service_date,l_date);
333 --l_line_rec.debrief_line_number := To_Char( p_record.debrief_line_id );
334 l_line_rec.uom_code := p_record.uom_code;
335 l_line_rec.quantity := p_record.quantity;
336 l_line_rec.expense_amount := p_record.expense_amount;
337 l_line_rec.currency_code := p_record.currency_code;
338 l_line_rec.business_process_id := p_record.business_process_id;
339 l_line_rec.channel_code := 'CSF_MFS';
340 l_line_rec.transaction_type_id := p_record.transaction_type_id;
341 l_line_rec.expense_reason_code := p_record.expense_reason_code;
342 l_line_rec.attribute1 := p_record.attribute1;
343 l_line_rec.attribute2 := p_record.attribute2;
344 l_line_rec.attribute3 := p_record.attribute3;
345 l_line_rec.attribute4 := p_record.attribute4;
346 l_line_rec.attribute5 := p_record.attribute5;
347 l_line_rec.attribute6 := p_record.attribute6;
348 l_line_rec.attribute7 := p_record.attribute7;
349 l_line_rec.attribute8 := p_record.attribute8;
350 l_line_rec.attribute9 := p_record.attribute9;
351 l_line_rec.attribute10 := p_record.attribute10;
352 l_line_rec.attribute11 := p_record.attribute11;
353 l_line_rec.attribute12 := p_record.attribute12;
354 l_line_rec.attribute13 := p_record.attribute13;
355 l_line_rec.attribute14 := p_record.attribute14;
356 l_line_rec.attribute15 := p_record.attribute15;
357 l_line_rec.attribute_category := p_record.attribute_category;
358
359 --check for the stale data
360 -- SERVER_WINS profile value
361 if(fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE)
362 = csm_profile_pkg.g_SERVER_WINS) then
363 if(r_cdl.last_update_date <> p_record.server_last_update_date) then
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_DEBRIEF_EXPENSES_PKG.APPLY_UPDATE: P_KEY = '
366 || p_record.debrief_line_id;
367 csm_util_pkg.log(p_error_msg,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
368 return;
369 end if;
370 end if;
371
372 --CLIENT_WINS (or client is allowd to update the record)
373
374 -- Update the debrief line
375 csf_debrief_pub.update_debrief_line
376 ( p_api_version_number => 1.0
377 , p_init_msg_list => FND_API.G_TRUE
378 , p_commit => FND_API.G_FALSE
379 , x_return_status => x_return_status
380 , x_msg_count => l_msg_count
381 , x_msg_data => l_msg_data
382 , p_debrief_line_rec => l_line_rec
383 );
384 if x_return_status <> FND_API.G_RET_STS_SUCCESS
385 then
386 /*** exception occurred in API -> return errmsg ***/
387 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
388 (
389 p_api_error => TRUE
390 );
391
392 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
393 || ' ROOT ERROR: csf_debrief_pub.update_debrief_lines'
394 || ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
395
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 return;
398 end if;
399
400 exception
401 when others then
402 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
403 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
404 (
405 p_api_error => TRUE
406 );
407
408 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
409 || ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 END APPLY_UPDATE;
412
413
414
415 /***
416 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
417 ***/
418 PROCEDURE APPLY_RECORD
419 (
420 p_record IN c_debrief_expenses%ROWTYPE,
421 p_error_msg OUT NOCOPY VARCHAR2,
422 x_return_status IN OUT NOCOPY VARCHAR2
423 ) IS
424 BEGIN
425 /*** initialize return status and message list ***/
426 x_return_status := FND_API.G_RET_STS_SUCCESS;
427 FND_MSG_PUB.INITIALIZE;
428
429 IF p_record.dmltype$$='I' THEN
430 -- Process insert
431 APPLY_INSERT
432 (
433 p_record,
434 p_error_msg,
435 x_return_status
436 );
437 ELSIF p_record.dmltype$$='U' THEN
438 -- Process update
439 APPLY_UPDATE
440 (
441 p_record,
442 p_error_msg,
443 x_return_status
444 );
445 ELSE
446 -- Process delete; not supported for this entity
447 CSM_UTIL_PKG.LOG
448 ( 'Delete is not supported for this entity'
449 || ' for PK ' || p_record.debrief_line_id ,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
450
451 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
452 (
453 p_message => 'CSM_DML_OPERATION'
454 , p_token_name1 => 'DML'
455 , p_token_value1 => p_record.dmltype$$
456 );
457
458 x_return_status := FND_API.G_RET_STS_SUCCESS;
459 END IF;
460
461 EXCEPTION WHEN OTHERS THEN
462 /*** defer record when any process exception occurs ***/
463 CSM_UTIL_PKG.LOG
464 ( 'Exception occurred in CSM_DEBRIEF_EXPENSES_PKG.APPLY_RECORD:' || ' ' || sqlerrm
465 || ' for PK ' || p_record.debrief_line_id ,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION );
466
467 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
468 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
469 (
470 p_api_error => TRUE
471 );
472
473 x_return_status := FND_API.G_RET_STS_ERROR;
474 END APPLY_RECORD;
475
476 /***
477 This procedure is called by CSM_UTIL_PKG when publication item <replace>
478 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
479 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
480 public APIs.
481 ***/
482 PROCEDURE APPLY_CLIENT_CHANGES
483 (
484 p_user_name IN VARCHAR2,
485 p_tranid IN NUMBER,
486 p_debug_level IN NUMBER,
487 x_return_status IN OUT NOCOPY VARCHAR2,
488 p_task_assignment_id IN NUMBER
489 ) IS
490
491 l_process_status VARCHAR2(1);
492 l_error_msg VARCHAR2(4000);
493 TYPE c_curtype is ref cursor;
494 c_cur c_curtype;
495 r_debrief_expenses c_debrief_expenses%rowtype;
496 BEGIN
497 g_debug_level := p_debug_level;
498 x_return_status := FND_API.G_RET_STS_SUCCESS;
499
500 csm_util_pkg.log('csm_debrief_expenses_pkg.apply_client_changes entered','CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR);
501
502 /*** loop through debrief expenses records in inqueue ***/
503 IF p_task_assignment_id IS NULL THEN
504 OPEN c_cur FOR 'SELECT * FROM csf_m_debrief_expenses_inq inq WHERE tranid$$ = '||p_tranid||' AND clid$$cs = '''||p_user_name
505 ||''' AND TASK_ASSIGNMENT_ID NOT IN ('||CSM_UTIL_PKG.get_String_fromList(g_processed_assignment_list)||')';
506 ELSE
507 g_processed_assignment_list(g_processed_assignment_list.COUNT+1):=p_task_assignment_id;
508 OPEN c_cur FOR 'SELECT * FROM csf_m_debrief_expenses_inq WHERE tranid$$ = '||p_tranid||' AND clid$$cs = '''||p_user_name
509 ||''' AND TASK_ASSIGNMENT_ID='||p_task_assignment_id;
510 END IF;
511
512 LOOP
513 FETCH c_cur INTO r_debrief_expenses;
514 EXIT WHEN c_cur%NOTFOUND;
515
516 SAVEPOINT save_rec;
517
518 /*** apply record ***/
519 APPLY_RECORD
520 (
521 r_debrief_expenses
522 , l_error_msg
523 , l_process_status
524 );
525
526 /*** was record processed successfully? ***/
527 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
528 /*** Yes -> delete record from inqueue ***/
529
530 CSM_UTIL_PKG.DELETE_RECORD
531 (
532 p_user_name,
533 p_tranid,
534 r_debrief_expenses.seqno$$,
535 r_debrief_expenses.debrief_line_id,
536 g_object_name,
537 g_pub_name,
538 l_error_msg,
539 l_process_status
540 );
541
542 /*** was delete successful? ***/
543 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
544 /*** no -> rollback ***/
545 CSM_UTIL_PKG.LOG
546 ( 'Deleting from inqueue failed, rolling back to savepoint'
547 || ' for PK ' || r_debrief_expenses.debrief_line_id ,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
548 ROLLBACK TO save_rec;
549 x_return_status := FND_API.G_RET_STS_ERROR;
550 END IF;
551 END IF;
552
553 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
554 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
555 CSM_UTIL_PKG.LOG
556 ( 'Record not processed successfully, deferring and rejecting record'
557 || ' for PK ' || r_debrief_expenses.debrief_line_id,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
558
559 CSM_UTIL_PKG.DEFER_RECORD
560 (
561 p_user_name
562 , p_tranid
563 , r_debrief_expenses.seqno$$
564 , r_debrief_expenses.debrief_line_id
565 , g_object_name
566 , g_pub_name
567 , l_error_msg
568 , l_process_status
569 , r_debrief_expenses.dmltype$$
570 );
571
572 /*** Was defer successful? ***/
573 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
574 /*** no -> rollback ***/
575 CSM_UTIL_PKG.LOG
576 ( 'Defer record failed, rolling back to savepoint'
577 || ' for PK ' || r_debrief_expenses.debrief_line_id,'CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
578 ROLLBACK TO save_rec;
579 x_return_status := FND_API.G_RET_STS_ERROR;
580 END IF;
581 END IF;
582
583 END LOOP;
584 CLOSE c_cur;
585
586 EXCEPTION WHEN OTHERS THEN
587 /*** catch and log exceptions ***/
588 CSM_UTIL_PKG.LOG
589 ( 'Exception occurred in APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
590 'CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION );
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 END APPLY_CLIENT_CHANGES;
593
594 END CSM_DEBRIEF_EXPENSES_PKG;