[Home] [Help]
PACKAGE BODY: APPS.CSL_DEBRIEF_PKG
Source
1 PACKAGE BODY CSL_DEBRIEF_PKG AS
2 /* $Header: cslvdblb.pls 120.0 2005/05/24 17:54:56 appldev noship $ */
3
4
5 error EXCEPTION;
6
7 /*** Globals ***/
8 g_object_name CONSTANT VARCHAR2(30) := 'CSL_DEBRIEF_PKG'; -- package name
9 g_pub_name CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_LINES'; -- publication item name
10 g_pub_name2 CONSTANT VARCHAR2(30) := 'CSF_DEBRIEF_HEADERS'; -- publication item name
11 g_debug_level NUMBER; -- debug level
12 g_header_id NUMBER := NULL;
13 TYPE Deferred_Line_Tbl_Type IS TABLE OF CSF_DEBRIEF_LINES.DEBRIEF_LINE_ID%TYPE INDEX BY BINARY_INTEGER;
14 g_deferred_line_id_tbl Deferred_Line_Tbl_Type;
15
16 /***
17 Cursor to retrieve all debrief lines from the debrief line inqueue that
18 have a debrief header record in the debrief header inqueue.
19 ***/
20 CURSOR c_debrief ( b_user_name VARCHAR2, b_tranid NUMBER) is
21 SELECT dbl.*
22 FROM CSL_CSF_DEBRIEF_LINES_INQ dbl
23 , CSL_CSF_DEBRIEF_HEADERS_INQ dbh
24 WHERE dbl.debrief_header_id = dbh.debrief_header_id
25 AND dbl.tranid$$ = b_tranid
26 AND dbl.clid$$cs = b_user_name
27 ORDER BY dbl.debrief_header_id;
28
29 /***
30 Cursor to retrieve all debrief lines from the debrief line inqueue that
31 have no debrief header record in the debrief header inqueue but have one in the backend.
32 This one is executed after all debrief lines with headers have been deleted from the inqueue.
33 The debrief lines without header remain then.
34 ***/
35 CURSOR c_debrief_no_headers( b_user_name VARCHAR2, b_tranid NUMBER) is
36 SELECT *
37 FROM CSL_CSF_DEBRIEF_LINES_INQ
38 WHERE tranid$$ = b_tranid
39 AND clid$$cs = b_user_name;
40
41 CURSOR c_debrief_no_lines( b_user_name VARCHAR2, b_tranid NUMBER) is
42 SELECT *
43 FROM CSL_CSF_DEBRIEF_HEADERS_INQ
44 WHERE tranid$$ = b_tranid
45 AND clid$$cs = b_user_name;
46
47 /***
48 This procedure is called from APPLY_RECORD and creates a debrief header.
49 ***/
50 PROCEDURE CREATE_DEBRIEF_HEADER
51 (
52 p_debrief_header_id IN CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE,
53 p_error_msg OUT NOCOPY VARCHAR2,
54 x_return_status IN OUT NOCOPY VARCHAR2
55 )
56 IS
57 /********************************************************
58 Name:
59 CREATE_DEBRIEF_HEADER
60
61 Purpose:
62 Insert new header record into CSF_DEBRIEF_HEADERS
63
64 Arguments:
65 p_debrief_header_id The debrief header id comes from
66 the debrief line inqueue record.
67 This is used to retrieve the data
68 from the debrief headers inqueue
69 which is used to call the API.
70 ********************************************************/
71
72 CURSOR c_csf_debrief_headers (b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) is
73 SELECT *
74 FROM CSL_CSF_DEBRIEF_HEADERS_INQ
75 WHERE debrief_header_id = b_debrief_header_id;
76
77 r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
78
79 l_deb_rec csf_debrief_pub.debrief_rec_type;
80 l_line_tbl csf_debrief_pub.debrief_line_tbl_type;
81
82 l_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE;
83
84 l_date DATE;
85 l_process_status NUMBER;
86 l_msg_count NUMBER;
87 l_msg_data VARCHAR2(240);
88 BEGIN
89
90 l_date := SYSDATE;
91
92 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
93 jtm_message_log_pkg.Log_Msg
94 ( v_object_id => r_csf_debrief_headers.debrief_header_id -- put PK column here
95 , v_object_name => g_object_name
96 , v_message => 'Entering ' || g_object_name || '.CREATE_DEBRIEF_HEADER'
97 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
98 END IF;
99
100 -- Open cursor to retrieve debrief header for debrief line
101 OPEN c_csf_debrief_headers (p_debrief_header_id);
102 FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
103 CLOSE c_csf_debrief_headers;
104
105 -- Create a new debrief header record
106 l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
107 l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
108 l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
109 l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
110 l_deb_rec.last_update_date := l_date;
111 l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
112 l_deb_rec.creation_date := l_date;
113 l_deb_rec.created_by := FND_GLOBAL.USER_ID;
114 l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
115 l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
116 l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
117 l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
118 l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
119 l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
120 l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
121 l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
122 l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
123 l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
124 l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
125 l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
126 l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
127 l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
128 l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
129 l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
130 l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
131
132 csf_debrief_pub.create_debrief
133 ( p_api_version_number => 1.0
134 , p_init_msg_list => FND_API.G_TRUE
135 , p_commit => FND_API.G_FALSE
136 , p_debrief_rec => l_deb_rec
137 , p_debrief_line_tbl => l_line_tbl
138 , x_debrief_header_id => l_debrief_header_id
139 , x_return_status => x_return_status
140 , x_msg_count => l_msg_count
141 , x_msg_data => l_msg_data
142 );
143
144 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
145 /*** exception occurred in API -> return errmsg ***/
146 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
147 (
148 p_api_error => TRUE
149 );
150 END IF;
151
152 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
153 jtm_message_log_pkg.Log_Msg
154 ( v_object_id => r_csf_debrief_headers.debrief_header_id -- put PK column here
155 , v_object_name => g_object_name
156 , v_message => 'Leaving ' || g_object_name || '.CREATE_DEBRIEF_HEADER'
157 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
158 END IF;
159
160 EXCEPTION WHEN OTHERS THEN
161 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
162 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
163 (
164 p_api_error => TRUE
165 );
166
167 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
168 jtm_message_log_pkg.Log_Msg
169 ( v_object_id => r_csf_debrief_headers.debrief_header_id -- put PK column here
170 , v_object_name => g_object_name
171 , v_message => 'Leaving ' || g_object_name || '.CREATE_DEBRIEF_HEADER : ' || p_error_msg
172 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
173 END IF;
174
175 x_return_status := FND_API.G_RET_STS_ERROR;
176
177 END CREATE_DEBRIEF_HEADER;
178
179 /***
180 This function checks whether a header id already exists in the backend or not.
181 ***/
182 FUNCTION DEBRIEF_HEADER_EXISTS
183 (
184 p_debrief_header_id IN OUT NOCOPY CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE,
185 p_task_assignment_id IN NUMBER
186 )
187 RETURN BOOLEAN
188 IS
189
190 CURSOR c_debrief_header_exists ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE ) is
191 SELECT null
192 FROM csf_debrief_headers
193 WHERE debrief_header_id = b_debrief_header_id;
194
195 CURSOR c_debrief_header_id ( b_task_assignment_id CSF_DEBRIEF_HEADERS.TASK_ASSIGNMENT_ID%TYPE ) is
196 SELECT debrief_header_id
197 FROM csf_debrief_headers
198 WHERE task_assignment_id = b_task_assignment_id;
199
200
201 r_debrief_header_exists c_debrief_header_exists%ROWTYPE;
202 r_debrief_header_id c_debrief_header_id%ROWTYPE;
203 l_header_exists BOOLEAN;
204 BEGIN
205
206 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
207 jtm_message_log_pkg.Log_Msg
208 ( v_object_id => null
209 , v_object_name => g_object_name
210 , v_message => 'Entering ' || g_object_name || '.Debrief_Header_Exists'
211 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
212 END IF;
213
214 -- Check if a header already exists in the backend or not.
215 l_header_exists := FALSE;
216
217 -- First check if there is already a header created on backend
218 OPEN c_debrief_header_id (p_task_assignment_id);
219 FETCH c_debrief_header_id INTO r_debrief_header_id;
220 IF c_debrief_header_id%FOUND THEN
221 l_header_exists := TRUE;
222 p_debrief_header_id := r_debrief_header_id.debrief_header_id;
223 CLOSE c_debrief_header_id;
224 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
225 jtm_message_log_pkg.Log_Msg
226 ( v_object_id => null
227 , v_object_name => g_object_name
228 , v_message => 'Leaving ' || g_object_name || '.Debrief_Header_Exists'
229 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
230 END IF;
231 return l_header_exists;
232 END IF;
233 CLOSE c_debrief_header_id;
234
235 -- If the header is not on a task assignment see if the header already exists
236 OPEN c_debrief_header_exists (p_debrief_header_id);
237 FETCH c_debrief_header_exists INTO r_debrief_header_exists;
238 IF c_debrief_header_exists%FOUND THEN
239 l_header_exists := TRUE;
240 END IF;
241 CLOSE c_debrief_header_exists;
242
243 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
244 jtm_message_log_pkg.Log_Msg
245 ( v_object_id => null
246 , v_object_name => g_object_name
247 , v_message => 'Leaving ' || g_object_name || '.Debrief_Header_Exists'
248 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
249 END IF;
250
251 RETURN l_header_exists;
252 END DEBRIEF_HEADER_EXISTS;
253
254 /* Just forward Declaration */
255 PROCEDURE APPLY_UPDATE_HEADER
256 ( p_user_name IN VARCHAR2,
257 p_tranid IN NUMBER,
258 p_header_id IN OUT NOCOPY NUMBER,
259 p_error_msg OUT NOCOPY VARCHAR2,
260 x_return_status IN OUT NOCOPY VARCHAR2 );
261
262 /***
263 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record
264 is to be processed.
265 ***/
266
267 PROCEDURE APPLY_INSERT_HEADER
268 (
269 p_user_name IN VARCHAR2,
270 p_tranid IN NUMBER,
271 p_header_id IN OUT NOCOPY NUMBER,
272 p_error_msg OUT NOCOPY VARCHAR2,
273 x_return_status IN OUT NOCOPY VARCHAR2
274 ) IS
275 /***
276 Name:
277 APPLY_INSERT_HEADER
278
279 Purpose:
280 First process all debrief lines that have debrief headers.
281 After processing a debrief line with debrief header delete both from
282 inqueues.
283
284 Then process all debrief lines that have no debrief header record in
285 the debrief header inqueue.
286 These are the ones coming from the backend and that have a header
287 record in the backend.
288 After processing these debrief lines, delete them from the debrief
289 line inqueue.
290 ***/
291
292 /***
293 Cursor to retrieve task_assignment_id to check if there is already
294 an record created on the backend for this header.
295 ***/
296 CURSOR c_debrief_assignment_id ( b_debrief_header_id NUMBER) is
297 SELECT task_assignment_id, seqno$$
298 FROM CSL_CSF_DEBRIEF_HEADERS_INQ dbh
299 WHERE debrief_header_id = b_debrief_header_id;
300 r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
301
302 l_return_status VARCHAR2(1);
303 l_msg_count NUMBER;
304 l_msg_data VARCHAR2(240);
305
306 l_process_status NUMBER;
307 l_header_exists BOOLEAN;
308 l_header_id NUMBER; -- p_record.debrief_header_id%TYPE;
309 l_task_assignment_id NUMBER;
310 l_seqno NUMBER;
311
312 BEGIN
313 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
314 jtm_message_log_pkg.Log_Msg
315 ( v_object_id => p_header_id
316 , v_object_name => g_object_name
317 , v_message => 'Entering ' || g_object_name ||
318 '.APPLY_INSERT_HEADER'
319 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
320 END IF;
321
322 /***
323 First check if header already exists in the backend or not. If so
324 then do not create a new one.
325 The situation could occur where multiple debrief lines have been
326 created for 1 debrief header.
327 In this case the header only has is created the first time and
328 should not be created a second time.
329 ***/
330
331 -- If the header is not on a task assignment see if the header already
332 -- exists
333 l_header_id := p_header_id; --p_record.debrief_header_id;
334 OPEN c_debrief_assignment_id (l_header_id);
335 FETCH c_debrief_assignment_id INTO r_debrief_assignment_id;
336 IF c_debrief_assignment_id%FOUND THEN
337 l_task_assignment_id := r_debrief_assignment_id.task_assignment_id;
338 ELSE
339 l_task_assignment_id := NULL;
340 END IF;
341 -- Also set seqno number because it is different from the line.
342 l_seqno := r_debrief_assignment_id.seqno$$;
343 CLOSE c_debrief_assignment_id;
344 l_header_exists := DEBRIEF_HEADER_EXISTS(l_header_id,
345 l_task_assignment_id);
346
347 IF l_header_exists THEN
348 APPLY_UPDATE_HEADER( p_user_name => p_user_name
349 , p_tranid => p_tranid
350 , p_header_id => p_header_id
351 , p_error_msg => p_error_msg
352 , x_return_status => x_return_status );
353 ELSE
354 savepoint save_rec;
355 -- Create debrief header.
356 CREATE_DEBRIEF_HEADER(l_header_id, p_error_msg, l_return_status);
357
358 -- Delete debrief header record from debrief header inqueue.
359 /*** was record processed successfully? ***/
360 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
361 /*** Yes -> delete record from inqueue ***/
362 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
363 jtm_message_log_pkg.Log_Msg
364 ( v_object_id => l_header_id -- put PK column here
365 , v_object_name => g_object_name
366 , v_message => 'Record successfully processed, deleting from
367 inqueue'
368 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
369 END IF;
370
371 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
372 (
373 p_user_name => p_user_name,
374 p_tranid => p_tranid,
375 p_seqno => l_seqno,
376 p_pk => p_header_id, -- p_record.debrief_header_id
377 p_object_name => g_object_name,
378 p_pub_name => g_pub_name2,
379 p_error_msg => p_error_msg,
380 x_return_status => l_return_status
381 );
382
383 /*** was delete successful? ***/
384 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
385 /*** no -> rollback ***/
386 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
387 jtm_message_log_pkg.Log_Msg
388 ( v_object_id => p_header_id --p_record.debrief_header_id
389 , v_object_name => g_object_name
390 , v_message => 'Deleting from inqueue failed, '||
391 'rolling back to savepoint'
392 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
393 END IF;
394 ROLLBACK to save_rec;
395 END IF;
396 ELSE
397 /*** Record was not processed successfully or delete failed
398 -> defer and reject record ***/
399 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
400 jtm_message_log_pkg.Log_Msg
401 ( v_object_id => p_header_id --p_record.debrief_header_id
402 , v_object_name => g_object_name
403 , v_message => 'Record not processed successfully, '||
404 'deferring and rejecting record'
405 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
406 END IF;
407
408 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
409 (
410 p_user_name => p_user_name
411 ,p_tranid => p_tranid
412 ,p_seqno => l_seqno
413 ,p_pk => p_header_id --p_record.debrief_header_id
414 ,p_object_name => g_object_name
415 ,p_pub_name => g_pub_name2
416 ,p_error_msg => p_error_msg
417 ,x_return_status => l_return_status
418 );
419
420 /*** Was defer successful? ***/
421 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
422 /*** no -> rollback ***/
423 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
424 jtm_message_log_pkg.Log_Msg
425 ( v_object_id => p_header_id --p_record.debrief_header_id
426 , v_object_name => g_object_name
427 , v_message => 'Defer record failed, rolling back to savepoint'
428 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
429 END IF;
430 ROLLBACK to save_rec;
431 END IF;
432 END IF;
433 END IF;
434
435 IF p_header_id <> l_header_id THEN
436
437 /*** Record was not processed successfully or delete failed ->
438 defer and reject record ***/
439 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
440 jtm_message_log_pkg.Log_Msg
441 ( v_object_id => p_header_id --p_record.debrief_header_id
442 , v_object_name => g_object_name
443 , v_message => 'Record not processed successfully, '||
444 'deferring and rejecting record'
445 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
446 END IF;
447 p_header_id := l_header_id;
448 -- If true the header is created on the backend already.
449 -- The client header needs to be removed
450 CSL_SERVICEL_WRAPPER_PKG.REJECT_RECORD(
451 P_USER_NAME => p_user_name
452 ,P_TRANID => p_tranid
453 ,P_SEQNO => l_seqno
454 ,P_PK => p_header_id
455 ,P_OBJECT_NAME => g_object_name
456 ,P_PUB_NAME => g_pub_name2
457 ,P_ERROR_MSG => p_error_msg
458 ,X_RETURN_STATUS => l_return_status
459 );
460 /*** Was defer successful? ***/
461 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
462 /*** no -> rollback ***/
463 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
464 jtm_message_log_pkg.Log_Msg
465 ( v_object_id => p_header_id --p_record.debrief_header_id
466 , v_object_name => g_object_name
467 , v_message => 'Defer record failed, rolling back to savepoint'
468 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
469 END IF;
470 ROLLBACK to save_rec;
471 END IF;
472 END IF;
473
474 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
475 jtm_message_log_pkg.Log_Msg
476 ( v_object_id => p_header_id -- p_record.DEBRIEF_LINE_ID
477 , v_object_name => g_object_name
478 , v_message => 'Leaving ' || g_object_name ||
479 '.APPLY_INSERT_HEADER'
480 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
481 END IF;
482
483 EXCEPTION WHEN OTHERS THEN
484 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
485 jtm_message_log_pkg.Log_Msg
486 ( v_object_id => p_header_id --p_record.DEBRIEF_LINE_ID
487 , v_object_name => g_object_name
488 , v_message => 'Exception occurred in APPLY_INSERT_HEADER:' ||
489 FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
490 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
491 END IF;
492
493 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT_HEADER', sqlerrm);
494 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
495 (
496 p_api_error => TRUE
497 );
498
499 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
500 jtm_message_log_pkg.Log_Msg
501 ( v_object_id => p_header_id -- p_record.DEBRIEF_LINE_ID
502 , v_object_name => g_object_name
503 , v_message => 'Leaving ' || g_object_name ||
504 '.APPLY_INSERT_HEADER'
505 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
506 END IF;
507
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 END APPLY_INSERT_HEADER;
510
511
512 /* New Procedure for ER - 3218717 */
513 PROCEDURE call_charges
514 ( p_debrief_header_id NUMBER
515 ) IS
516
517 -- Cursor to check if the Assignment Status is either of the
518 -- following rejected, on_hold, cancelled, closed or completed
519 CURSOR c_chk_task_status
520 ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
521 ) IS
522 SELECT tst.rejected_flag, tst.on_hold_flag, tst.cancelled_flag,
523 tst.closed_flag, tst.completed_flag
524 FROM csf_debrief_headers dh, jtf_task_assignments tas,
525 jtf_task_statuses_b tst
526 WHERE dh.task_assignment_id = tas.task_assignment_id
527 AND tas.assignment_status_id = tst.task_status_id
528 AND dh.debrief_header_id = b_debrief_header_id;
529
530 l_rejected_flag VARCHAR2(1);
531 l_on_hold_flag VARCHAR2(1);
532 l_cancelled_flag VARCHAR2(1);
533 l_closed_flag VARCHAR2(1);
534 l_completed_flag VARCHAR2(1);
535
536 BEGIN
537
538 -- For a given debrief header check the task Assignment status.
539 -- If it is one of the following -
540 -- rejected, on_hold, cancelled, closed or completed then call the api
541 -- csf_debrief_update_pkg.form_Call for processing charges
542
543 OPEN c_chk_task_status ( p_debrief_header_id );
544 FETCH c_chk_task_status INTO l_rejected_flag, l_on_hold_flag,
545 l_cancelled_flag, l_closed_flag, l_completed_flag;
546 CLOSE c_chk_task_status;
547
548 IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y') OR (l_cancelled_flag='Y')
549 OR (l_closed_flag='Y') OR (l_completed_flag='Y') ) THEN
550 csf_debrief_update_pkg.form_Call (1.0, p_debrief_header_id );
551 END IF;
552
553 END call_charges ;
554
555
556 PROCEDURE APPLY_INSERT_LINE
557 (
558 p_record IN c_debrief%ROWTYPE,
559 p_user_name IN VARCHAR2,
560 p_tranid IN NUMBER,
561 p_header_id IN NUMBER,
562 p_error_msg OUT NOCOPY VARCHAR2,
563 x_return_status IN OUT NOCOPY VARCHAR2
564 ) IS
565
566 -- Retrieve source_object_type_code
567 CURSOR c_task_obj_code
568 ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
569 )
570 IS
571 SELECT source_object_type_code
572 FROM jtf_tasks_b jtb
573 , jtf_task_assignments jta
574 , csf_debrief_headers dbh
575 WHERE jtb.task_id = jta.task_id
576 AND jta.task_assignment_id = dbh.task_assignment_id
577 AND dbh.debrief_header_id = b_debrief_header_id;
578
579 r_task_obj_code c_task_obj_code%ROWTYPE;
580
581
582 --Bug 3850061
583 /*
584 CURSOR c_material_transaction
585 ( p_inventory_item_id p_record.inventory_item_id%TYPE
586 , p_inv_organization_id p_record.receiving_inventory_org_id%TYPE
587 )
588 IS
589 SELECT null
590 FROM CS_BILLING_TYPE_CATEGORIES tbc
591 , MTL_SYSTEM_ITEMS_B msi
592 WHERE tbc.BILLING_CATEGORY = 'M'
593 AND msi.MATERIAL_BILLABLE_FLAG = tbc.BILLING_TYPE
594 AND msi.INVENTORY_ITEM_ID = p_inventory_item_id
595 AND msi.ORGANIZATION_ID = p_inv_organization_id;
596
597 -- For Bug Fix 3168617
598 CURSOR c_debrief_header
599 ( b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE
600 ) IS
601 SELECT debrief_number
602 FROM csf_debrief_headers
603 WHERE debrief_header_id = b_debrief_header_id;
604
605 r_material_transaction c_material_transaction%ROWTYPE;
606 */
607
608 l_return_status VARCHAR2(1);
609 l_header_id p_record.debrief_header_id%type;
610 l_msg_count NUMBER;
611 l_msg_data VARCHAR2(240);
612 l_date DATE;
613
614 l_spare_update_status p_record.spare_update_status%TYPE;
615 l_transaction_type_id NUMBER;
616 l_inventory_org_id p_record.issuing_inventory_org_id%TYPE;
617 l_sub_inventory_code p_record.issuing_sub_inventory_code%TYPE;
618 l_locator_id p_record.issuing_locator_id%TYPE;
619 l_debrief_number CSF_DEBRIEF_HEADERS.DEBRIEF_NUMBER%TYPE;
620 l_transaction_id NUMBER;
621 l_transaction_header_id NUMBER;
622
623 -- l_line_rec and l_line_tbl are record/table types from the debrief API
624 l_line_rec csf_debrief_pub.debrief_line_rec_type;
625 l_line_tbl csf_debrief_pub.debrief_line_tbl_type;
626
627 BEGIN
628
629 l_date := SYSDATE;
630 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
631 jtm_message_log_pkg.Log_Msg
632 ( v_object_id => p_record.DEBRIEF_LINE_ID
633 , v_object_name => g_object_name
634 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT_LINE'
635 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
636 END IF;
637
638 -- Check if header_id is given or not from APPLY_INSERT_HEADER
639 IF p_header_id IS NULL THEN
640 l_header_id :=p_record.debrief_header_id;
641 ELSE
642 l_header_id := p_header_id;
643 END IF;
644
645 -- ER 3218717
646
647 IF ( g_header_id IS NOT NULL ) AND ( g_header_id <> l_header_id ) THEN
648
649 call_charges ( g_header_id );
650 g_header_id := l_header_id;
651
652 ELSIF ( g_header_id IS NULL ) THEN
653 g_header_id := l_header_id;
654
655 END IF;
656
657 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
658 jtm_message_log_pkg.Log_Msg
659 ( v_object_id => p_record.DEBRIEF_LINE_ID
660 , v_object_name => g_object_name
661 , v_message => 'l_header_id = ' || l_header_id
662 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
663 END IF;
664
665 --Bug 3850061
666 /*
667 -- Verify if transaction is a non-IB material transaction and a subinventory is involved
668 OPEN c_material_transaction(p_record.inventory_item_id,
669 NVL(p_record.issuing_inventory_org_id, p_record.receiving_inventory_org_id));
670 FETCH c_material_transaction INTO r_material_transaction;
671 IF c_material_transaction%FOUND
672 AND (p_record.issuing_sub_inventory_code IS NOT NULL
673 OR p_record.receiving_sub_inventory_code IS NOT NULL) THEN
674 -- yes -> update on-hand quantity in Oracle Inventory using Spares Management API
675
676 IF ( p_record.receiving_inventory_org_id IS NULL
677 AND p_record.issuing_inventory_org_id IS NOT NULL
678 )
679 THEN
680 l_transaction_type_id := 93; -- miscellaneous issue
681 l_inventory_org_id := p_record.issuing_inventory_org_id;
682 l_sub_inventory_code := p_record.issuing_sub_inventory_code;
683 l_locator_id := p_record.issuing_locator_id;
684 ELSIF ( p_record.receiving_inventory_org_id IS NOT NULL
685 AND p_record.issuing_inventory_org_id IS NULL
686 )
687 THEN
688 l_transaction_type_id := 94; -- miscellaneous receipt
689 l_inventory_org_id := p_record.receiving_inventory_org_id;
690 l_sub_inventory_code := p_record.receiving_sub_inventory_code;
691 l_locator_id := p_record.receiving_locator_id;
692 END IF;
693
694 -- For Bug Fix 3168617. Need to pass debrief_number
695 -- to p_transaction_source_name
696 OPEN c_debrief_header (l_header_id);
697 FETCH c_debrief_header INTO l_debrief_number;
698 CLOSE c_debrief_header;
699
700 csp_transactions_pub.transact_material
701 ( p_api_version => 1.0
702 , p_init_msg_list => FND_API.G_TRUE
703 , p_commit => FND_API.G_FALSE
704 , px_transaction_id => l_transaction_id
705 , px_transaction_header_id => l_transaction_header_id
706 , p_inventory_item_id => p_record.inventory_item_id
707 , p_organization_id => l_inventory_org_id
708 , p_subinventory_code => l_sub_inventory_code
709 , p_locator_id => l_locator_id
710 , p_lot_number => p_record.item_lotnumber
711 , p_revision => p_record.item_revision
712 , p_serial_number => p_record.item_serial_number
713 , p_quantity => p_record.quantity
714 , p_uom => p_record.uom_code
715 , p_source_id => NULL
716 , p_source_line_id => NULL
717 , p_transaction_type_id => l_transaction_type_id
718 , p_transfer_to_subinventory => NULL
719 , p_transfer_to_locator => NULL
720 , p_transfer_to_organization => NULL
721 , p_transaction_source_id => NULL
722 , p_transaction_source_name => l_debrief_number
723 , p_trx_source_line_id => NULL
724 , x_return_status => l_return_status
725 , x_msg_count => l_msg_count
726 , x_msg_data => l_msg_data
727 );
728
729 -- Check whether API error occurred
730 IF l_return_status = FND_API.G_RET_STS_SUCCESS
731 THEN
732 -- Successful -> set spares update status flag in debrief line to 'SUCCEEDED',
733 -- so the quantities won't be updated again when the FSE confirms the debriefline
734 -- in connected debrief
735 l_spare_update_status := 'SUCCEEDED';
736 ELSE
737 -- spares update failed -> continue debrief line creation but set spares update status to 'FAILED'
738 l_spare_update_status := 'FAILED';
739 END IF;
740
741 END IF;
742 CLOSE c_material_transaction;
743 */
744
745 -- Create debrief line
746 l_line_rec.debrief_line_id := p_record.debrief_line_id;
747 l_line_rec.debrief_header_id := l_header_id;
748 l_line_rec.issuing_inventory_org_id := p_record.issuing_inventory_org_id;
749 l_line_rec.issuing_sub_inventory_code := p_record.issuing_sub_inventory_code;
750 l_line_rec.issuing_locator_id := p_record.issuing_locator_id;
751 l_line_rec.receiving_inventory_org_id := p_record.receiving_inventory_org_id;
752 l_line_rec.receiving_sub_inventory_code := p_record.receiving_sub_inventory_code;
753 l_line_rec.receiving_locator_id := p_record.receiving_locator_id;
754 l_line_rec.last_update_date := l_date;
755 l_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
756 l_line_rec.creation_date := l_date;
757 l_line_rec.created_by := FND_GLOBAL.USER_ID;
758 l_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
759 l_line_rec.spare_update_status := l_spare_update_status;
760 l_line_rec.inventory_item_id := p_record.inventory_item_id;
761 l_line_rec.txn_billing_type_id := p_record.txn_billing_type_id;
762 l_line_rec.service_date := p_record.service_date;
763 l_line_rec.debrief_line_number := To_Char(p_record.debrief_line_id );
764 l_line_rec.quantity := p_record.quantity;
765 l_line_rec.uom_code := p_record.uom_code;
766 l_line_rec.item_serial_number := p_record.item_serial_number;
767 l_line_rec.item_revision := p_record.item_revision;
768 l_line_rec.item_lotnumber := p_record.item_lotnumber;
769 l_line_rec.business_process_id := p_record.business_process_id;
770 l_line_rec.channel_code := 'CSF_LAPTOP';
771 l_line_rec.expense_amount := p_record.expense_amount;
772 l_line_rec.currency_code := p_record.currency_code;
773 l_line_rec.labor_start_date := p_record.labor_start_date;
774 l_line_rec.labor_end_date := p_record.labor_end_date;
775 l_line_rec.starting_mileage := p_record.starting_mileage;
776 l_line_rec.ending_mileage := p_record.ending_mileage;
777 l_line_rec.instance_id := p_record.instance_id;
778 l_line_rec.PARENT_PRODUCT_ID := p_record.PARENT_PRODUCT_ID;
779 l_line_rec.transaction_type_id := p_record.transaction_type_id;
780 /* New code for Reason */
781 l_line_rec.material_reason_code := p_record.material_reason_code;
782 l_line_rec.labor_reason_code := p_record.labor_reason_code;
783 l_line_rec.expense_reason_code := p_record.expense_reason_code;
784 l_line_rec.disposition_code := p_record.disposition_code;
785 -- ER 3223881 - Return Reason Code
786 l_line_rec.return_reason_code := p_record.return_reason_code;
787 -- ER 3746450
788 l_line_rec.removed_product_id := p_record.removed_product_id;
789 /* Support for DFF - 3737857 */
790 l_line_rec.attribute1 := p_record.attribute1;
791 l_line_rec.attribute2 := p_record.attribute2;
792 l_line_rec.attribute3 := p_record.attribute3;
793 l_line_rec.attribute4 := p_record.attribute4;
794 l_line_rec.attribute5 := p_record.attribute5;
795 l_line_rec.attribute6 := p_record.attribute6;
796 l_line_rec.attribute7 := p_record.attribute7;
797 l_line_rec.attribute8 := p_record.attribute8;
798 l_line_rec.attribute9 := p_record.attribute9;
799 l_line_rec.attribute10 := p_record.attribute10;
800 l_line_rec.attribute11 := p_record.attribute11;
801 l_line_rec.attribute12 := p_record.attribute12;
802 l_line_rec.attribute13 := p_record.attribute13;
803 l_line_rec.attribute14 := p_record.attribute14;
804 l_line_rec.attribute15 := p_record.attribute15;
805 l_line_rec.attribute_category := p_record.attribute_category;
806
807 l_line_tbl(1) := l_line_rec;
808
809 -- Fetch SOURCE_OBJECT_TYPE_CODE from task record
810 OPEN c_task_obj_code
811 ( l_header_id
812 );
813 FETCH c_task_obj_code INTO r_task_obj_code;
814 CLOSE c_task_obj_code;
815
816 csf_debrief_pub.create_debrief_lines
817 ( p_api_version_number => 1.0
818 , p_init_msg_list => FND_API.G_TRUE
819 , p_commit => FND_API.G_FALSE
820 , x_return_status => x_return_status
821 , x_msg_count => l_msg_count
822 , x_msg_data => l_msg_data
823 , p_debrief_header_id => l_header_id
824 , p_debrief_line_tbl => l_line_tbl
825 , p_source_object_type_code => r_task_obj_code.source_object_type_code
826 );
827
828 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
829 /*** exception occurred in API -> return errmsg ***/
830 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
831 (
832 p_api_error => TRUE
833 );
834 END IF;
835
836 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
837 jtm_message_log_pkg.Log_Msg
838 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
839 , v_object_name => g_object_name
840 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT_LINE'
841 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
842 END IF;
843
844 EXCEPTION WHEN OTHERS THEN
845
846 --Bug 3850061
847 /*
848 IF c_debrief_header%ISOPEN THEN
849 CLOSE c_debrief_header;
850 END IF;
851 */
852
853 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
854 jtm_message_log_pkg.Log_Msg
855 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
856 , v_object_name => g_object_name
857 , v_message => 'Exception occurred in APPLY_INSERT:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
858 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
859 END IF;
860
861 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT_LINE', sqlerrm);
862 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
863 (
864 p_api_error => TRUE
865 );
866
867 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
868 jtm_message_log_pkg.Log_Msg
869 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
870 , v_object_name => g_object_name
871 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT_LINE'
872 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
873 END IF;
874
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 END APPLY_INSERT_LINE;
877
878
879 /***
880 This procedure is called by PROCESS_DEBRIEF
881 ***/
882
883 PROCEDURE APPLY_UPDATE_HEADER
884 (
885 p_user_name IN VARCHAR2,
886 p_tranid IN NUMBER,
887 p_header_id IN OUT NOCOPY NUMBER,
888 p_error_msg OUT NOCOPY VARCHAR2,
889 x_return_status IN OUT NOCOPY VARCHAR2
890 ) IS
891 /***
892 Name:
893 APPLY_UPDATE_HEADER
894
895 Purpose:
896 If debrief header gets updated, say DFF fields got changed in client
897 application, Then process all debrief headers even if no debrief
898 lines associated with it in INQ.
899 After processing these debrief headers, delete them from the debrief
900 header inqueue.
901 ***/
902
903 /***
904 Cursor to retrieve task_assignment_id to check if there is already
905 an record created on the backend for this header.
906 ***/
907
908 CURSOR c_debrief_assignment_id ( b_debrief_header_id NUMBER) is
909 SELECT task_assignment_id, seqno$$
910 FROM CSL_CSF_DEBRIEF_HEADERS_INQ dbh
911 WHERE debrief_header_id = b_debrief_header_id;
912 r_debrief_assignment_id c_debrief_assignment_id%ROWTYPE;
913
914 CURSOR c_csf_debrief_headers (
915 b_debrief_header_id CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE) IS
916 SELECT * FROM CSL_CSF_DEBRIEF_HEADERS_INQ
917 WHERE debrief_header_id = b_debrief_header_id;
918 r_csf_debrief_headers c_csf_debrief_headers%ROWTYPE;
919
920 l_deb_rec csf_debrief_pub.debrief_rec_type;
921 l_date DATE;
922
923 l_return_status VARCHAR2(1);
924 l_msg_count NUMBER;
925 l_msg_data VARCHAR2(240);
926
927 l_process_status NUMBER;
928 l_header_exists BOOLEAN;
929 l_header_id NUMBER; -- p_record.debrief_header_id%TYPE;
930 l_task_assignment_id NUMBER;
931 l_seqno NUMBER;
932
933 BEGIN
934 l_date := SYSDATE;
935
936 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
937 jtm_message_log_pkg.Log_Msg
938 ( v_object_id => p_header_id -- p_record.DEBRIEF_LINE_ID
939 , v_object_name => g_object_name
940 , v_message => 'Entering ' || g_object_name ||
941 '.APPLY_UPDATE_HEADER'
942 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
943 END IF;
944
945
946 /***
947 First check if header already exists in the backend or not. If so
948 then do not create a new one. The situation could occur where
949 multiple debrief lines have been created for 1 debrief header.
950 In this case the header only has is created the first time and
951 should not be created a second time.
952 ***/
953
954 l_header_id := p_header_id; --p_record.debrief_header_id;
955 OPEN c_debrief_assignment_id (l_header_id);
956 FETCH c_debrief_assignment_id INTO r_debrief_assignment_id;
957 IF c_debrief_assignment_id%FOUND THEN
958 l_task_assignment_id := r_debrief_assignment_id.task_assignment_id;
959 ELSE
960 l_task_assignment_id := NULL;
961 END IF;
962 -- Also set seqno number because it is different from the line.
963 l_seqno := r_debrief_assignment_id.seqno$$;
964 CLOSE c_debrief_assignment_id;
965 l_header_exists := DEBRIEF_HEADER_EXISTS(l_header_id,
966 l_task_assignment_id);
967
968 IF NOT l_header_exists THEN
969 APPLY_INSERT_HEADER( p_user_name => p_user_name
970 , p_tranid => p_tranid
971 , p_header_id => p_header_id
972 , p_error_msg => p_error_msg
973 , x_return_status => x_return_status );
974
975 ELSE
976 SAVEPOINT save_rec;
977 -- update debrief header.
978 OPEN c_csf_debrief_headers (l_header_id);
979 FETCH c_csf_debrief_headers INTO r_csf_debrief_headers;
980 CLOSE c_csf_debrief_headers;
981
982 -- update existing debrief header record
983 l_deb_rec.debrief_header_id := r_csf_debrief_headers.debrief_header_id;
984 l_deb_rec.debrief_date := r_csf_debrief_headers.debrief_date;
985 l_deb_rec.debrief_status_id := r_csf_debrief_headers.debrief_status_id;
986 l_deb_rec.task_assignment_id := r_csf_debrief_headers.task_assignment_id;
987 l_deb_rec.last_update_date := l_date;
988 l_deb_rec.last_updated_by := FND_GLOBAL.USER_ID;
989 l_deb_rec.creation_date := l_date;
990 l_deb_rec.created_by := FND_GLOBAL.USER_ID;
991 l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
992 l_deb_rec.attribute1 := r_csf_debrief_headers.attribute1;
993 l_deb_rec.attribute2 := r_csf_debrief_headers.attribute2;
994 l_deb_rec.attribute3 := r_csf_debrief_headers.attribute3;
995 l_deb_rec.attribute4 := r_csf_debrief_headers.attribute4;
996 l_deb_rec.attribute5 := r_csf_debrief_headers.attribute5;
997 l_deb_rec.attribute6 := r_csf_debrief_headers.attribute6;
998 l_deb_rec.attribute7 := r_csf_debrief_headers.attribute7;
999 l_deb_rec.attribute8 := r_csf_debrief_headers.attribute8;
1000 l_deb_rec.attribute9 := r_csf_debrief_headers.attribute9;
1001 l_deb_rec.attribute10 := r_csf_debrief_headers.attribute10;
1002 l_deb_rec.attribute11 := r_csf_debrief_headers.attribute11;
1003 l_deb_rec.attribute12 := r_csf_debrief_headers.attribute12;
1004 l_deb_rec.attribute13 := r_csf_debrief_headers.attribute13;
1005 l_deb_rec.attribute14 := r_csf_debrief_headers.attribute14;
1006 l_deb_rec.attribute15 := r_csf_debrief_headers.attribute15;
1007 l_deb_rec.attribute_category := r_csf_debrief_headers.attribute_category;
1008
1009 csf_debrief_pub.update_debrief
1010 ( p_api_version_number => 1.0
1011 , p_init_msg_list => FND_API.G_TRUE
1012 , p_commit => FND_API.G_FALSE
1013 , p_debrief_rec => l_deb_rec
1014 , x_return_status => x_return_status
1015 , x_msg_count => l_msg_count
1016 , x_msg_data => l_msg_data
1017 );
1018
1019 -- Delete debrief header record from debrief header inqueue.
1020
1021 /*** was record processed successfully? ***/
1022 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1023 /*** Yes -> delete record from inqueue ***/
1024 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1025 jtm_message_log_pkg.Log_Msg
1026 ( v_object_id => l_header_id -- put PK column here
1027 , v_object_name => g_object_name
1028 , v_message => 'Record successfully processed, '||
1029 ' deleting from inqueue'
1030 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1031 END IF;
1032
1033 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
1034 (
1035 p_user_name => p_user_name,
1036 p_tranid => p_tranid,
1037 p_seqno => l_seqno,
1038 p_pk => p_header_id, -- p_record.debrief_header_id,
1039 p_object_name => g_object_name,
1040 p_pub_name => g_pub_name2,
1041 p_error_msg => p_error_msg,
1042 x_return_status => l_return_status
1043 );
1044
1045 /*** was delete successful? ***/
1046 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1047 /*** no -> rollback ***/
1048 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1049 jtm_message_log_pkg.Log_Msg
1050 ( v_object_id => p_header_id --p_record.debrief_header_id
1051 , v_object_name => g_object_name
1052 , v_message => 'Deleting from inqueue failed, rolling '||
1053 ' back to savepoint'
1054 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1055 END IF;
1056 ROLLBACK to save_rec;
1057 END IF; -- end if delete failed
1058
1059 ELSE -- else when record was not processed successfully
1060
1061 /*** Record was not processed successfully or delete failed
1062 -> defer and reject record ***/
1063 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1064
1065 jtm_message_log_pkg.Log_Msg
1066 ( v_object_id => p_header_id --p_record.debrief_header_id
1067 , v_object_name => g_object_name
1068 , v_message => 'Record not processed successfully, '||
1069 'deferring and rejecting record'
1070 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1071 END IF;
1072
1073 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
1074 (
1075 p_user_name => p_user_name
1076 ,p_tranid => p_tranid
1077 ,p_seqno => l_seqno
1078 ,p_pk => p_header_id --p_record.debrief_header_id
1079 ,p_object_name => g_object_name
1080 ,p_pub_name => g_pub_name2
1081 ,p_error_msg => p_error_msg
1082 ,x_return_status => l_return_status
1083 );
1084
1085 /*** Was defer successful? ***/
1086 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1087 /*** no -> rollback ***/
1088 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1089 jtm_message_log_pkg.Log_Msg
1090 ( v_object_id => p_header_id --p_record.debrief_header_id
1091 , v_object_name => g_object_name
1092 , v_message => 'Defer record failed,rolling back to savepoint'
1093 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1094 ROLLBACK to save_rec;
1095 END IF;
1096 END IF; -- end of if defer failed.
1097 END IF; -- end of checking was record processed successfully
1098 END IF; -- end of else DML_TYPE = 'U'
1099
1100 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1101 jtm_message_log_pkg.Log_Msg
1102 ( v_object_id => p_header_id -- p_record.DEBRIEF_LINE_ID
1103 , v_object_name => g_object_name
1104 , v_message => 'Leaving ' || g_object_name ||
1105 '.APPLY_UPDATE_HEADER'
1106 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1107 END IF;
1108
1109 EXCEPTION WHEN OTHERS THEN
1110
1111 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1112 jtm_message_log_pkg.Log_Msg
1113 ( v_object_id => p_header_id --p_record.DEBRIEF_LINE_ID
1114 , v_object_name => g_object_name
1115 , v_message => 'Exception occurred in APPLY_UPDATE_HEADER:' ||
1116 FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
1117 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1118 END IF;
1119
1120 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE_HEADER', sqlerrm);
1121 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
1122 (
1123 p_api_error => TRUE
1124 );
1125
1126 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1127 jtm_message_log_pkg.Log_Msg
1128 ( v_object_id => p_header_id -- p_record.DEBRIEF_LINE_ID
1129 , v_object_name => g_object_name
1130 , v_message => 'Leaving ' || g_object_name ||
1131 '.APPLY_UPDATE_HEADER'
1132 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1133 END IF;
1134
1135 x_return_status := FND_API.G_RET_STS_ERROR;
1136 END APPLY_UPDATE_HEADER;
1137
1138
1139 /***
1140 This procedure is called by APPLY_CLIENT_CHANGES when an updated record
1141 is to be processed.
1142 No update for debrief lines yet.
1143 ***/
1144 PROCEDURE APPLY_UPDATE
1145 (
1146 p_record IN c_debrief%ROWTYPE,
1147 p_user_name IN VARCHAR2,
1148 p_tranid IN NUMBER,
1149 p_error_msg OUT NOCOPY VARCHAR2,
1150 x_return_status IN OUT NOCOPY VARCHAR2
1151 ) IS
1152 BEGIN
1153 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1154 jtm_message_log_pkg.Log_Msg
1155 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
1156 , v_object_name => g_object_name
1157 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
1158 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1159 END IF;
1160
1161 -- For Debrief no update is possible, so return success
1162 x_return_status := FND_API.G_RET_STS_SUCCESS;
1163
1164 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1165 /*** exception occurred in API -> return errmsg ***/
1166 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
1167 (
1168 p_api_error => TRUE
1169 );
1170 END IF;
1171
1172 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1173 jtm_message_log_pkg.Log_Msg
1174 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
1175 , v_object_name => g_object_name
1176 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
1177 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1178 END IF;
1179
1180 EXCEPTION WHEN OTHERS THEN
1181 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1182 jtm_message_log_pkg.Log_Msg
1183 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
1184 , v_object_name => g_object_name
1185 , v_message => 'Exception occurred in APPLY_UPDATE:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
1186 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1187 END IF;
1188
1189 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
1190 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
1191 (
1192 p_api_error => TRUE
1193 );
1194
1195 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1196 jtm_message_log_pkg.Log_Msg
1197 ( v_object_id => p_record.DEBRIEF_LINE_ID -- put PK column here
1198 , v_object_name => g_object_name
1199 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
1200 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1201 END IF;
1202
1203 x_return_status := FND_API.G_RET_STS_ERROR;
1204 END APPLY_UPDATE;
1205
1206 /***
1207 This procedure is called from APPLY_CLIENT_CHANGES and processes all
1208 debrief lines that a debrief header record in the debrief header inqueue.
1209 ***/
1210 PROCEDURE PROCESS_DEBRIEF
1211 (
1212 p_user_name IN VARCHAR2,
1213 p_tranid IN NUMBER,
1214 x_return_status IN OUT NOCOPY VARCHAR2
1215 )
1216 IS
1217 l_error_msg VARCHAR2(4000);
1218 l_header_id NUMBER := 0;
1219 j NUMBER := 0;
1220 BEGIN
1221
1222 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1223 jtm_message_log_pkg.Log_Msg
1224 ( v_object_id => null
1225 , v_object_name => g_object_name
1226 , v_message => 'Entering ' || g_object_name || '.Process_Debrief'
1227 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1228 END IF;
1229
1230 /***
1231 Loop through cursor with all debrief line records in debrief line
1232 inqueue, that have a debrief header in the debrief header inqueue.
1233 ***/
1234 FOR r_debrief IN c_debrief ( p_user_name, p_tranid) LOOP
1235
1236 SAVEPOINT save_rec;
1237
1238 /*** apply record ***/
1239 IF r_debrief.dmltype$$='I' THEN
1240 l_header_id := r_debrief.DEBRIEF_HEADER_ID;
1241 APPLY_INSERT_HEADER
1242 (
1243 -- p_record => r_debrief
1244 p_user_name => p_user_name
1245 , p_tranid => p_tranid
1246 , p_header_id => l_header_id
1247 , p_error_msg => l_error_msg
1248 , x_return_status => x_return_status
1249 );
1250
1251 APPLY_INSERT_LINE
1252 (
1253 p_record => r_debrief
1254 , p_user_name => p_user_name
1255 , p_tranid => p_tranid
1256 , p_header_id => l_header_id
1257 , p_error_msg => l_error_msg
1258 , x_return_status => x_return_status
1259 );
1260
1261 /*** was record processed successfully? ***/
1262 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1263 /*** Yes -> delete record from inqueue ***/
1264 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1265 jtm_message_log_pkg.Log_Msg
1266 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1267 , v_object_name => g_object_name
1268 , v_message => 'Line record successfully processed, deleting from inqueue'
1269 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1270 END IF;
1271
1272 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
1273 (
1274 p_user_name,
1275 p_tranid,
1276 r_debrief.seqno$$,
1277 r_debrief.DEBRIEF_LINE_ID, -- put PK column here
1278 g_object_name,
1279 g_pub_name,
1280 l_error_msg,
1281 x_return_status
1282 );
1283
1284 /*** was delete successful? ***/
1285 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1286 /*** no -> rollback ***/
1287 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1288 jtm_message_log_pkg.Log_Msg
1289 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1290 , v_object_name => g_object_name
1291 , v_message => 'Deleting line record from inqueue failed, rolling back to savepoint'
1292 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1293 END IF;
1294 ROLLBACK TO save_rec;
1295 END IF;
1296 END IF;
1297
1298 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1299 /*** Record was not processed successfully or delete failed
1300 -> defer and reject record ***/
1301 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1302 jtm_message_log_pkg.Log_Msg
1303 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1304 , v_object_name => g_object_name
1305 , v_message => 'Line record not processed successfully, deferring and rejecting record'
1306 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1307 END IF;
1308
1309 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
1310 (
1311 p_user_name
1312 , p_tranid
1313 , r_debrief.seqno$$
1314 , r_debrief.DEBRIEF_LINE_ID -- put PK column here
1315 , g_object_name
1316 , g_pub_name
1317 , l_error_msg
1318 , x_return_status
1319 );
1320
1321 /*** Was defer successful? ***/
1322 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1323 /*** no -> rollback ***/
1324 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1325 jtm_message_log_pkg.Log_Msg
1326 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1327 , v_object_name => g_object_name
1328 , v_message => 'Defer line record failed, rolling back to savepoint'
1329 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1330 END IF;
1331
1332 g_deferred_line_id_tbl(j) := r_debrief.DEBRIEF_LINE_ID;
1333 j := j+1;
1334 ROLLBACK TO save_rec;
1335 END IF;
1336 END IF;
1337 ELSIF r_debrief.dmltype$$='U' THEN
1338
1339 APPLY_UPDATE
1340 (
1341 p_record => r_debrief,
1342 p_user_name => p_user_name,
1343 p_tranid => p_tranid,
1344 p_error_msg => l_error_msg,
1345 x_return_status => x_return_status);
1346
1347 END IF;
1348 END LOOP;
1349
1350 -- ER 3218717
1351 -- Calling Charges for the last Debrief in the Queue
1352 IF ( l_header_id <> 0 ) THEN
1353 call_charges ( l_header_id );
1354 END IF;
1355
1356 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1357 jtm_message_log_pkg.Log_Msg
1358 ( v_object_id => null
1359 , v_object_name => g_object_name
1360 , v_message => 'Leaving ' || g_object_name || '.Process_Debrief'
1361 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1362 END IF;
1363
1364 EXCEPTION WHEN OTHERS THEN
1365 /*** catch and log exceptions ***/
1366 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1367 jtm_message_log_pkg.Log_Msg
1368 ( v_object_id => null
1369 , v_object_name => g_object_name
1370 , v_message => 'Exception occurred in PROCESS_DEBRIEF:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
1371 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1372 END IF;
1373 x_return_status := FND_API.G_RET_STS_ERROR;
1374
1375 END PROCESS_DEBRIEF;
1376
1377 /***
1378 This procedure is called from APPLY_CLIENT_CHANGES and processes all debrief lines
1379 that have no debrief header record in the debrief header inqueue.
1380 ***/
1381 PROCEDURE PROCESS_DEBRIEF_NO_HEADERS
1382 (
1383 p_user_name IN VARCHAR2,
1384 p_tranid IN NUMBER,
1385 x_return_status IN OUT NOCOPY VARCHAR2
1386 )
1387 IS
1388 l_error_msg VARCHAR2(4000);
1389 l_header_id NUMBER := 0 ;
1390 BEGIN
1391
1392 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1393 jtm_message_log_pkg.Log_Msg
1394 ( v_object_id => null
1395 , v_object_name => g_object_name
1396 , v_message => 'Entering ' || g_object_name || '.Process_Debrief_No_Headers'
1397 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1398 END IF;
1399
1400 /***
1401 Loop through cursor with all debrief line records in debrief line inqueue,
1402 that have no debrief header in the debrief header inqueue (but have one in the backend).
1403 ***/
1404 FOR r_debrief IN c_debrief_no_headers ( p_user_name, p_tranid) LOOP
1405 SAVEPOINT save_rec;
1406
1407 -- ER 3218717
1408 l_header_id := r_debrief.DEBRIEF_HEADER_ID;
1409
1410
1411 /*** apply record ***/
1412 /*** Bug 3373654 not to handle deferred debrief line as no-header line ***/
1413 --Bug 3702875
1414 IF (g_deferred_line_id_tbl.COUNT > 0) THEN
1415 IF(g_deferred_line_id_tbl.EXISTS(r_debrief.DEBRIEF_LINE_ID)) THEN
1416 NULL;
1417 ELSE
1418 APPLY_INSERT_LINE
1419 (
1420 p_record => r_debrief
1421 , p_user_name => p_user_name
1422 , p_tranid => p_tranid
1423 , p_header_id => NULL
1424 , p_error_msg => l_error_msg
1425 , x_return_status => x_return_status
1426 );
1427 END IF;
1428 ELSE
1429 APPLY_INSERT_LINE --Bug 3702875
1430 (
1431 p_record => r_debrief
1432 , p_user_name => p_user_name
1433 , p_tranid => p_tranid
1434 , p_header_id => NULL
1435 , p_error_msg => l_error_msg
1436 , x_return_status => x_return_status
1437 );
1438 END IF;
1439
1440 /*** was record processed successfully? ***/
1441 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1442 /*** Yes -> delete record from inqueue ***/
1443 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1444 jtm_message_log_pkg.Log_Msg
1445 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1446 , v_object_name => g_object_name
1447 , v_message => 'Record successfully processed, deleting from inqueue'
1448 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1449 END IF;
1450
1451 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
1452 (
1453 p_user_name,
1454 p_tranid,
1455 r_debrief.seqno$$,
1456 r_debrief.DEBRIEF_LINE_ID, -- put PK column here
1457 g_object_name,
1458 g_pub_name,
1459 l_error_msg,
1460 x_return_status
1461 );
1462
1463 /*** was delete successful? ***/
1464 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1465 /*** no -> rollback ***/
1466 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1467 jtm_message_log_pkg.Log_Msg
1468 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1469 , v_object_name => g_object_name
1470 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
1471 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1472 END IF;
1473 ROLLBACK TO save_rec;
1474 END IF;
1475 END IF;
1476
1477 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1478 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
1479 --Since the record was not processed successfully, rollback to the point before
1480 --calling APPLY_INSERT_LINE
1481 ROLLBACK to save_rec;
1482 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1483 jtm_message_log_pkg.Log_Msg
1484 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1485 , v_object_name => g_object_name
1486 , v_message => 'Record not processed successfully, deferring and rejecting record'
1487 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1488 END IF;
1489
1490 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
1491 (
1492 p_user_name
1493 , p_tranid
1494 , r_debrief.seqno$$
1495 , r_debrief.DEBRIEF_LINE_ID -- put PK column here
1496 , g_object_name
1497 , g_pub_name
1498 , l_error_msg
1499 , x_return_status
1500 );
1501
1502 /*** Was defer successful? ***/
1503 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1504 /*** no -> rollback ***/
1505 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
1506 jtm_message_log_pkg.Log_Msg
1507 ( v_object_id => r_debrief.DEBRIEF_LINE_ID -- put PK column here
1508 , v_object_name => g_object_name
1509 , v_message => 'Defer record failed, rolling back to savepoint'
1510 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
1511 END IF;
1512 ROLLBACK TO save_rec;
1513 END IF;
1514 END IF;
1515 END LOOP;
1516
1517 -- ER 3218717
1518 -- Calling Charges for the last Debrief in the Queue
1519 IF (l_header_id <> 0) THEN
1520 call_charges ( l_header_id );
1521 END IF;
1522
1523 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1524 jtm_message_log_pkg.Log_Msg
1525 ( v_object_id => null
1526 , v_object_name => g_object_name
1527 , v_message => 'Leaving ' || g_object_name || '.Process_Debrief_No_Headers'
1528 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1529 END IF;
1530
1531 /*** clear deferred_line queue ****/
1532 IF (g_deferred_line_id_tbl.COUNT > 0 ) THEN
1533 g_deferred_line_id_tbl.DELETE;
1534 END IF;
1535
1536 EXCEPTION WHEN OTHERS THEN
1537 /*** catch and log exceptions ***/
1538 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1539 jtm_message_log_pkg.Log_Msg
1540 ( v_object_id => null
1541 , v_object_name => g_object_name
1542 , v_message => 'Exception occurred in Process_Debrief_No_Headers: ' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
1543 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1544 END IF;
1545
1546 x_return_status := FND_API.G_RET_STS_ERROR;
1547
1548 END PROCESS_DEBRIEF_NO_HEADERS;
1549
1550 /***
1551 This procedure is called from APPLY_CLIENT_CHANGES and processes all
1552 debrief lines that have no debrief header record in the debrief header
1553 inqueue.
1554 ***/
1555
1556 PROCEDURE PROCESS_DEBRIEF_NO_LINES
1557 (
1558 p_user_name IN VARCHAR2,
1559 p_tranid IN NUMBER,
1560 x_return_status IN OUT NOCOPY VARCHAR2
1561 )
1562 IS
1563 l_error_msg VARCHAR2(4000);
1564 l_header_id NUMBER;
1565 BEGIN
1566
1567 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1568 jtm_message_log_pkg.Log_Msg
1569 ( v_object_id => null
1570 , v_object_name => g_object_name
1571 , v_message => 'Entering ' || g_object_name ||
1572 '.PROCESS_DEBRIEF_NO_LINES'
1573 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1574 END IF;
1575
1576
1577 /***
1578 Loop through cursor with all debrief header records in debrief
1579 header inqueue, that have no debrief lines inqueue
1580 ***/
1581
1582 FOR r_debrief IN c_debrief_no_lines ( p_user_name, p_tranid) LOOP
1583 l_header_id := r_debrief.DEBRIEF_HEADER_ID;
1584 /*** apply record ***/
1585 IF r_debrief.dmltype$$='I' THEN
1586 APPLY_INSERT_HEADER
1587 (
1588 -- p_record => r_debrief
1589 --,
1590 p_user_name => p_user_name
1591 , p_tranid => p_tranid
1592 , p_header_id => l_header_id
1593 , p_error_msg => l_error_msg
1594 , x_return_status => x_return_status
1595 );
1596
1597 ELSIF r_debrief.dmltype$$='U' THEN
1598
1599 APPLY_UPDATE_HEADER
1600 (
1601 -- p_record => r_debrief,
1602 p_user_name => p_user_name,
1603 p_tranid => p_tranid,
1604 p_error_msg => l_error_msg,
1605 p_header_id => l_header_id,
1606 x_return_status => x_return_status);
1607
1608 END IF;
1609
1610 END LOOP;
1611
1612 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1613 jtm_message_log_pkg.Log_Msg
1614 ( v_object_id => null
1615 , v_object_name => g_object_name
1616 , v_message => 'Leaving ' || g_object_name ||
1617 '.PROCESS_DEBRIEF_NO_LINES'
1618 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1619 END IF;
1620
1621 EXCEPTION WHEN OTHERS THEN
1622
1623 /*** catch and log exceptions ***/
1624 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1625 jtm_message_log_pkg.Log_Msg
1626 ( v_object_id => null
1627 , v_object_name => g_object_name
1628 , v_message => 'Exception occurred in PROCESS_DEBRIEF_NO_LINES: '
1629 || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
1630 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1631 END IF;
1632
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634
1635 END PROCESS_DEBRIEF_NO_LINES;
1636
1637 /***
1638 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication
1639 item CSF_DEBRIEF_LINES is dirty. This happens when a mobile field service
1640 device executed DML on an updatable table and did a fast sync. This
1641 procedure will insert the data that came from mobile into the backend
1642 tables using public APIs.
1643 ***/
1644
1645 PROCEDURE APPLY_CLIENT_CHANGES
1646 (
1647 p_user_name IN VARCHAR2,
1648 p_tranid IN NUMBER,
1649 p_debug_level IN NUMBER,
1650 x_return_status IN OUT NOCOPY VARCHAR2
1651 ) IS
1652 BEGIN
1653 g_debug_level := p_debug_level;
1654 x_return_status := FND_API.G_RET_STS_SUCCESS;
1655 g_header_id := NULL;
1656 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1657 jtm_message_log_pkg.Log_Msg
1658 ( v_object_id => null
1659 , v_object_name => g_object_name
1660 , v_message => 'Entering ' || g_object_name ||
1661 '.Apply_Client_Changes'
1662 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1663 END IF;
1664
1665 -- First process all debrief lines that have a debrief header.
1666 -- Delete them after processing.
1667 PROCESS_DEBRIEF(p_user_name, p_tranid, x_return_status);
1668
1669 -- Then process all remaining debrief lines (no debrief header).
1670 -- These already have a debrief header in Apps.
1671 PROCESS_DEBRIEF_NO_HEADERS(p_user_name, p_tranid, x_return_status);
1672
1673 -- Then process the debrief headers (no debrief lines).
1674 -- For example, DFF updates on local client etc.
1675 PROCESS_DEBRIEF_NO_LINES(p_user_name, p_tranid, x_return_status);
1676
1677 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1678 jtm_message_log_pkg.Log_Msg
1679 ( v_object_id => null
1680 , v_object_name => g_object_name
1681 , v_message => 'Leaving ' || g_object_name ||
1682 '.Apply_Client_Changes'
1683 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1684 END IF;
1685
1686 END APPLY_CLIENT_CHANGES;
1687 END CSL_DEBRIEF_PKG;