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