[Home] [Help]
PACKAGE BODY: APPS.CSL_COUNTER_VALUES_PKG
Source
1 PACKAGE BODY CSL_COUNTER_VALUES_PKG AS
2 /* $Header: cslvcvab.pls 120.1 2005/08/30 01:32:17 utekumal noship $*/
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_COUNTER_VALUES_PKG'; -- package name
8 g_pub_name CONSTANT VARCHAR2(30) := 'CS_COUNTER_VALUES'; -- publication item name
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR c_CS_COUNTER_VALUES( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_CS_COUNTER_VALUES_inq
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 /***
18 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
19 ***/
20 PROCEDURE APPLY_INSERT
21 (
22 p_record IN c_CS_COUNTER_VALUES%ROWTYPE,
23 p_error_msg OUT NOCOPY VARCHAR2,
24 x_return_status IN OUT NOCOPY VARCHAR2
25 ) IS
26
27 -- Variables needed for public API
28
29 -- Rewrite for Sql Performance - Venky
30 cursor c_counter_group
31 ( b_counter_id number
32 )
33 is
34 select c.counter_id
35 , c.counter_group_id
36 , g.source_object_id
37 from cs_counters c, cs_counter_groups g
38 where c.counter_id = b_counter_id
39 AND c.counter_group_id = g.counter_group_id
40 AND g.source_object_code = 'CP' ;
41
42 r_counter_group c_counter_group%rowtype;
43
44 -- Variables needed for public API
45 l_ctr_grp_log_rec_pub Cs_Ctr_Capture_Reading_Pub.Ctr_Grp_Log_Rec_Type;
46 l_ctr_rdg_rec_pub Cs_Ctr_Capture_Reading_Pub.Ctr_Rdg_Rec_Type;
47 l_ctr_rdg_tbl_pub Cs_Ctr_Capture_Reading_Pub.Ctr_Rdg_Tbl_Type;
48
49 -- Variables needed for private API
50 --Bug 4496299
51 /*
52 l_ctr_grp_log_rec_pvt Cs_Ctr_Capture_Reading_Pvt.Ctr_Grp_Log_Rec_Type;
53 l_ctr_rdg_rec_pvt Cs_Ctr_Capture_Reading_Pvt.Ctr_Rdg_Rec_Type;
54 */
55
56 l_ctr_grp_id number;
57 l_ctr_grp_log_id number;
58 l_customer_product_id number;
59 l_prop_val_cnt number;
60
61 l_msg_count NUMBER;
62 l_msg_data VARCHAR2(240);
63
64 BEGIN
65
66 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
67 jtm_message_log_pkg.Log_Msg
68 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
69 , v_object_name => g_object_name
70 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
71 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
72 END IF;
73
74 --Bug 4496299
75
76 -- For mobile Field Service we know that a counter_group_id is not
77 -- known, but the counter_id is known. Therefore we need to retrieve
78 -- the counter_group_id, using the counter_id.
79
80 /*
81
82 open c_counter_group
83 ( b_counter_id => p_record.counter_id
84 );
85 fetch c_counter_group
86 into r_counter_group;
87 if c_counter_group%found
88 then
89 l_ctr_grp_id := r_counter_group.counter_group_id;
90 l_customer_product_id := r_counter_group.source_object_id;
91 else
92 l_ctr_grp_id := FND_API.G_MISS_NUM;
93 l_customer_product_id := FND_API.G_MISS_NUM;
94 end if;
95 close c_counter_group;
96
97 -- The public API closes a counter log after inserting a counter value. After
98 -- that it's not possible to insert new counter property values for this
99 -- counter value.
100 -- Therefore the public API may only be called when there are no counter
101 -- property values. Otherwise the private API, which doesn't close a
102 -- counter log, should be called.
103
104 SELECT COUNT(1) INTO l_prop_val_cnt
105 FROM CSL_cs_counter_prop_vals_inq
106 WHERE COUNTER_VALUE_ID = P_Record.COUNTER_VALUE_ID;
107
108 if l_prop_val_cnt = 0
109 then
110 -- There are no counter_property_values for this counter_value.
111 -- Fill the counter_group_log record and the counter_reading record needed
112 -- for the public API and then call the public API.
113 -- Fill counter_group_log record
114
115 l_ctr_grp_log_rec_pub.counter_group_id := l_ctr_grp_id;
116 l_ctr_grp_log_rec_pub.value_timestamp := p_record.value_timestamp;
117 l_ctr_grp_log_rec_pub.source_transaction_id := l_customer_product_id; -- p_record.customer_product_id;
118 l_ctr_grp_log_rec_pub.source_transaction_code := 'FS';
119
120 -- Fill counter_reading record
121
122 l_ctr_rdg_rec_pub.counter_value_id:= p_record.counter_value_id;
123 l_ctr_rdg_rec_pub.counter_id := p_record.counter_id;
124 l_ctr_rdg_rec_pub.value_timestamp := p_record.value_timestamp;
125 l_ctr_rdg_rec_pub.counter_reading := p_record.counter_reading;
126 l_ctr_rdg_rec_pub.reset_flag := p_record.reset_flag;
127 l_ctr_rdg_rec_pub.reset_reason := p_record.reset_reason;
128 l_ctr_rdg_rec_pub.pre_reset_last_rdg := p_record.pre_reset_last_rdg;
129 l_ctr_rdg_rec_pub.post_reset_first_rdg := p_record.post_reset_first_rdg;
130 l_ctr_rdg_rec_pub.misc_reading_type := p_record.misc_reading_type;
131 l_ctr_rdg_rec_pub.misc_reading := p_record.misc_reading;
132
133 l_ctr_rdg_tbl_pub(1) := l_ctr_rdg_rec_pub;
134
135 -- Call the public API
136
137 Cs_Ctr_Capture_Reading_Pub.Capture_Counter_Reading
138 ( p_api_version_number => 1.0
139 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
140 , p_init_msg_list => FND_API.G_TRUE
141 , p_commit => FND_API.G_FALSE
142 , p_ctr_rdg_tbl => l_ctr_rdg_tbl_pub
143 , p_ctr_grp_log_rec => l_ctr_grp_log_rec_pub
144 , x_return_status => x_return_status
145 , x_msg_count => l_msg_count
146 , x_msg_data => l_msg_data
147 );
148 else
149 -- There are counter property values for this counter value.
150 -- Fill the counter_group_log record and the counter_reading record needed
151 -- for the private API and then call the private API.
152
153 -- Fill counter_group_log record
154
155 l_ctr_grp_log_rec_pvt.counter_group_id := l_ctr_grp_id;
156 l_ctr_grp_log_rec_pvt.value_timestamp := p_record.value_timestamp;
157 l_ctr_grp_log_rec_pvt.source_transaction_id := l_customer_product_id;
158 l_ctr_grp_log_rec_pvt.source_transaction_code := 'FS';
159
160 -- Call the (first of the) private API(s)
161
162 Cs_Ctr_Capture_Reading_Pvt.Pre_Capture_Counter_Reading
163 ( p_api_version_number => 1.0
164 , p_init_msg_list => FND_API.G_TRUE
165 , p_commit => FND_API.G_FALSE
166 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
167 , p_ctr_grp_log_rec => l_ctr_grp_log_rec_pvt
168 , x_counter_grp_log_id => l_ctr_grp_log_id
169 , x_return_status => x_return_status
170 , x_msg_count => l_msg_count
171 , x_msg_data => l_msg_data
172 );
173
174 if x_return_status = FND_API.G_RET_STS_SUCCESS
175 then
176 -- Call the (second of the) private API(s)
177
178 l_ctr_rdg_rec_pvt.counter_value_id := p_record.counter_value_id;
179 l_ctr_rdg_rec_pvt.counter_id := p_record.counter_id;
180 l_ctr_rdg_rec_pvt.value_timestamp := p_record.value_timestamp;
181 l_ctr_rdg_rec_pvt.counter_reading := p_record.counter_reading;
182 l_ctr_rdg_rec_pvt.reset_flag := p_record.reset_flag;
183 l_ctr_rdg_rec_pvt.reset_reason := p_record.reset_reason;
184 l_ctr_rdg_rec_pvt.pre_reset_last_rdg := p_record.pre_reset_last_rdg;
185 l_ctr_rdg_rec_pvt.post_reset_first_rdg := p_record.post_reset_first_rdg;
186 l_ctr_rdg_rec_pvt.misc_reading_type := p_record.misc_reading_type;
187 l_ctr_rdg_rec_pvt.misc_reading := p_record.misc_reading;
188
189 Cs_Ctr_Capture_Reading_Pvt.Capture_Counter_Reading
190 ( p_api_version_number => 1.0
191 , p_init_msg_list => FND_API.G_TRUE
192 , p_commit => FND_API.G_FALSE
193 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
194 , p_ctr_rdg_rec => l_ctr_rdg_rec_pvt
195 , p_counter_grp_log_id => l_ctr_grp_log_id
196 , x_return_status => x_return_status
197 , x_msg_count => l_msg_count
198 , x_msg_data => l_msg_data
199 );
200 end if;
201 end if;
202 */
203
204 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
205 /*** exception occurred in API -> return errmsg ***/
206 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
207 (
208 p_api_error => TRUE
209 );
210 END IF;
211
212 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
213 jtm_message_log_pkg.Log_Msg
214 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
215 , v_object_name => g_object_name
216 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
217 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
218 END IF;
219
220 EXCEPTION WHEN OTHERS THEN
221 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
222 jtm_message_log_pkg.Log_Msg
223 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
224 , v_object_name => g_object_name
225 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
226 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
227 END IF;
228
229 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
230 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
231 (
232 p_api_error => TRUE
233 );
234
235 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
236 jtm_message_log_pkg.Log_Msg
237 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
238 , v_object_name => g_object_name
239 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
240 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
241 END IF;
242
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 END APPLY_INSERT;
245
246 /***
247 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
248 ***/
249 PROCEDURE APPLY_UPDATE
250 (
251 p_record IN c_CS_COUNTER_VALUES%ROWTYPE,
252 p_error_msg OUT NOCOPY VARCHAR2,
253 x_return_status IN OUT NOCOPY VARCHAR2
254 ) IS
255
256 -- Rewrite for Sql Performance - Venky
257 cursor c_counter_group
258 ( b_counter_id number
259 )
260 is
261 select c.counter_id
262 , c.counter_group_id
263 , g.source_object_id
264 from cs_counters c, cs_counter_groups g
265 where c.counter_id = b_counter_id
266 AND c.counter_group_id = g.counter_group_id
267 AND g.source_object_code = 'CP' ;
268
269 cursor c_counter_value
270 ( b_counter_value_id number
271 )
272 is
273 select counter_id
274 , counter_value_id
275 , counter_grp_log_id
276 , object_version_number
277 from cs_ctr_counter_values_v
278 where counter_value_id = b_counter_value_id;
279
280 r_counter_group c_counter_group%rowtype;
281 r_counter_value c_counter_value%rowtype;
282
283 --Bug 4496299
284 /*
285 l_ctr_grp_log_rec CS_CTR_Capture_Reading_pub.CTR_Grp_Log_Rec_Type;
286 l_ctr_rdg_rec CS_CTR_Capture_Reading_pub.CTR_Rdg_Rec_Type;
287 */
288 l_ctr_rdg_tbl CS_CTR_Capture_Reading_pub.CTR_Rdg_Tbl_Type;
289 l_ctr_grp_id number;
290 l_ctr_grp_log_id number;
291 l_customer_product_id number;
292
293 l_msg_count NUMBER;
294 l_msg_data VARCHAR2(240);
295 BEGIN
296 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
297 jtm_message_log_pkg.Log_Msg
298 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
299 , v_object_name => g_object_name
300 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
301 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
302 END IF;
303
304 --Bug 4496299
305 /*
306 -- For mobile Field Service we know that a counter_group_id is not
307 -- known, but the counter_id is known. Therefore we need to retrieve
308 -- the counter_group_id, using the counter_id.
309
310 open c_counter_group
311 ( b_counter_id => p_record.counter_id
312 );
313 fetch c_counter_group
314 into r_counter_group;
315 if c_counter_group%found
316 then
317 l_ctr_grp_id := r_counter_group.counter_group_id;
318 l_customer_product_id := r_counter_group.source_object_id;
319 else
320 l_ctr_grp_id := FND_API.G_MISS_NUM;
321 l_customer_product_id := FND_API.G_MISS_NUM;
322 end if;
323 close c_counter_group;
324
325 l_ctr_grp_log_rec.counter_group_id := l_ctr_grp_id;
326 l_ctr_grp_log_rec.value_timestamp := p_record.value_timestamp;
327 l_ctr_grp_log_rec.source_transaction_id := l_customer_product_id;
328 l_ctr_grp_log_rec.source_transaction_code := 'FS';
329
330 l_ctr_rdg_rec.counter_value_id := p_record.counter_value_id;
331 l_ctr_rdg_rec.counter_id := p_record.counter_id;
332 l_ctr_rdg_rec.value_timestamp := p_record.value_timestamp;
333 l_ctr_rdg_rec.counter_reading := p_record.counter_reading;
334 l_ctr_rdg_rec.reset_flag := p_record.reset_flag;
335 l_ctr_rdg_rec.reset_reason := p_record.reset_reason;
336 l_ctr_rdg_rec.pre_reset_last_rdg := p_record.pre_reset_last_rdg;
337 l_ctr_rdg_rec.post_reset_first_rdg := p_record.post_reset_first_rdg;
338 l_ctr_rdg_rec.misc_reading_type := p_record.misc_reading_type;
339 l_ctr_rdg_rec.misc_reading := p_record.misc_reading;
340
341 open c_counter_value
342 ( b_counter_value_id => p_record.counter_value_id
343 );
344 fetch c_counter_value
345 into r_counter_value;
346 if c_counter_value%found
347 then
348 l_ctr_grp_log_id := r_counter_value.counter_grp_log_id;
349 -- Make sure we have the right object_version_number
350 l_ctr_rdg_rec.object_version_number := r_counter_value.object_version_number;
351 else
352 -- Let the API complain with a good message.
353 l_ctr_grp_log_id := FND_API.G_MISS_NUM;
354 l_ctr_rdg_rec.object_version_number := FND_API.G_MISS_NUM;
355 end if;
356 close c_counter_value;
357
358 l_ctr_rdg_tbl(1) := l_ctr_rdg_rec;
359
360 -- Make sure we have the right object_version_number
361 l_ctr_rdg_tbl(1).object_version_number := r_counter_value.object_version_number;
362
363 Cs_Ctr_Capture_Reading_pub.Update_Counter_Reading
364 ( p_api_version_number => 1.0
365 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
366 , p_init_msg_list => FND_API.G_TRUE
367 , p_commit => FND_API.G_FALSE
368 , p_ctr_rdg_tbl => l_ctr_rdg_tbl
369 , p_ctr_grp_log_id => l_ctr_grp_log_id
370 , x_return_status => x_return_status
371 , x_msg_count => l_msg_count
372 , x_msg_data => l_msg_data
373 );
374
375 */
376
377 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
378 /*** exception occurred in API -> return errmsg ***/
379 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
380 (
381 p_api_error => TRUE
382 );
383 END IF;
384
385 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
386 jtm_message_log_pkg.Log_Msg
387 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
388 , v_object_name => g_object_name
389 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
390 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
391 END IF;
392
393 EXCEPTION WHEN OTHERS THEN
394 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
395 jtm_message_log_pkg.Log_Msg
396 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
397 , v_object_name => g_object_name
398 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
399 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
400 END IF;
401
402 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
403 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
404 (
405 p_api_error => TRUE
406 );
407
408 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
409 jtm_message_log_pkg.Log_Msg
410 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
411 , v_object_name => g_object_name
412 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
413 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
414 END IF;
415
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 END APPLY_UPDATE;
418
419 /***
420 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
421 ***/
422 PROCEDURE APPLY_RECORD
423 (
424 p_record IN c_CS_COUNTER_VALUES%ROWTYPE,
425 p_error_msg OUT NOCOPY VARCHAR2,
426 x_return_status IN OUT NOCOPY VARCHAR2
427 ) IS
428 BEGIN
429 /*** initialize return status and message list ***/
430 x_return_status := FND_API.G_RET_STS_SUCCESS;
431 FND_MSG_PUB.INITIALIZE;
432
433 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
434 jtm_message_log_pkg.Log_Msg
435 ( v_object_id => p_record.COUNTER_VALUE_ID-- put PK column here
436 , v_object_name => g_object_name
437 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
438 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
439 END IF;
440
441 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
442 jtm_message_log_pkg.Log_Msg
443 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
444 , v_object_name => g_object_name
445 , v_message => 'Processing CS_COUNTER_VALUE = ' || p_record.COUNTER_VALUE_ID /* put PK column here */ || fnd_global.local_chr(10) ||
446 'DMLTYPE = ' || p_record.dmltype$$
447 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
448 END IF;
449
450 IF p_record.dmltype$$='I' THEN
451 -- Process insert
452 APPLY_INSERT
453 (
454 p_record,
455 p_error_msg,
456 x_return_status
457 );
458 ELSIF p_record.dmltype$$='U' THEN
459 -- Process update
460 APPLY_UPDATE
461 (
462 p_record,
463 p_error_msg,
464 x_return_status
465 );
466 ELSIF p_record.dmltype$$='D' THEN
467 -- Process delete; not supported for this entity
468 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
469 jtm_message_log_pkg.Log_Msg
470 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
471 , v_object_name => g_object_name
472 , v_message => 'Delete is not supported for this entity'
473 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
474 END IF;
475
476 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
477 (
478 p_message => 'CSL_DML_OPERATION'
479 , p_token_name1 => 'DML'
480 , p_token_value1 => p_record.dmltype$$
481 );
482
483 x_return_status := FND_API.G_RET_STS_ERROR;
484 ELSE
485 -- invalid dml type
486 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
487 jtm_message_log_pkg.Log_Msg
488 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
489 , v_object_name => g_object_name
490 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
491 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
492 END IF;
493
494 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
495 (
496 p_message => 'CSL_DML_OPERATION'
497 , p_token_name1 => 'DML'
498 , p_token_value1 => p_record.dmltype$$
499 );
500
501 x_return_status := FND_API.G_RET_STS_ERROR;
502 END IF;
503
504 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
505 jtm_message_log_pkg.Log_Msg
506 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
507 , v_object_name => g_object_name
508 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
509 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
510 END IF;
511 EXCEPTION WHEN OTHERS THEN
512 /*** defer record when any process exception occurs ***/
513 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
514 jtm_message_log_pkg.Log_Msg
515 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
516 , v_object_name => g_object_name
517 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
518 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
519 END IF;
520
521 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
522 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
523 (
524 p_api_error => TRUE
525 );
526
527 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
528 jtm_message_log_pkg.Log_Msg
529 ( v_object_id => p_record.COUNTER_VALUE_ID -- put PK column here
530 , v_object_name => g_object_name
531 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
532 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
533 END IF;
534
535 x_return_status := FND_API.G_RET_STS_ERROR;
536 END APPLY_RECORD;
537
538 /***
539 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item CS_COUNTER_VALUES
540 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
541 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
542 public APIs.
543 ***/
544 PROCEDURE APPLY_CLIENT_CHANGES
545 (
546 p_user_name IN VARCHAR2,
547 p_tranid IN NUMBER,
548 p_debug_level IN NUMBER,
549 x_return_status IN OUT NOCOPY VARCHAR2
550 ) IS
551
552 l_process_status VARCHAR2(1);
553 l_error_msg VARCHAR2(4000);
554 BEGIN
555 g_debug_level := p_debug_level;
556 x_return_status := FND_API.G_RET_STS_SUCCESS;
557
558 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
559 jtm_message_log_pkg.Log_Msg
560 ( v_object_id => null
561 , v_object_name => g_object_name
562 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
563 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
564 END IF;
565
566 /*** loop through CS_COUNTER_VALUES records in inqueue ***/
567 FOR r_CS_COUNTER_VALUES IN c_CS_COUNTER_VALUES( p_user_name, p_tranid) LOOP
568
569 SAVEPOINT save_rec;
570
571 /*** apply record ***/
572 APPLY_RECORD
573 (
574 r_CS_COUNTER_VALUES
575 , l_error_msg
576 , l_process_status
577 );
578
579 /*** was record processed successfully? ***/
580 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
581 /*** Yes -> delete record from inqueue ***/
582 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
583 jtm_message_log_pkg.Log_Msg
584 ( v_object_id => r_CS_COUNTER_VALUES.COUNTER_VALUE_ID -- put PK column here
585 , v_object_name => g_object_name
586 , v_message => 'Record successfully processed, deleting from inqueue'
587 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
588 END IF;
589
590 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
591 (
592 p_user_name,
593 p_tranid,
594 r_CS_COUNTER_VALUES.seqno$$,
595 r_CS_COUNTER_VALUES.COUNTER_VALUE_ID, -- put PK column here
596 g_object_name,
597 g_pub_name,
598 l_error_msg,
599 l_process_status
600 );
601
602 /*** was delete successful? ***/
603 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
604 /*** no -> rollback ***/
605 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
606 jtm_message_log_pkg.Log_Msg
607 ( v_object_id => r_CS_COUNTER_VALUES.COUNTER_VALUE_ID -- put PK column here
608 , v_object_name => g_object_name
609 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
610 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
611 END IF;
612 ROLLBACK TO save_rec;
613 END IF;
614 END IF;
615
616 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
617 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
618 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
619 jtm_message_log_pkg.Log_Msg
620 ( v_object_id => r_CS_COUNTER_VALUES.COUNTER_VALUE_ID -- put PK column here
621 , v_object_name => g_object_name
622 , v_message => 'Record not processed successfully, deferring and rejecting record'
623 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
624 END IF;
625
626 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
627 (
628 p_user_name
629 , p_tranid
630 , r_CS_COUNTER_VALUES.seqno$$
631 , r_CS_COUNTER_VALUES.COUNTER_VALUE_ID -- put PK column here
632 , g_object_name
633 , g_pub_name
634 , l_error_msg
635 , l_process_status
636 );
637
638 /*** Was defer successful? ***/
639 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
640 /*** no -> rollback ***/
641 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
642 jtm_message_log_pkg.Log_Msg
643 ( v_object_id => r_CS_COUNTER_VALUES.COUNTER_VALUE_ID -- put PK column here
644 , v_object_name => g_object_name
645 , v_message => 'Defer record failed, rolling back to savepoint'
646 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
647 END IF;
648 ROLLBACK TO save_rec;
649 END IF;
650 END IF;
651
652 END LOOP;
653
654 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
655 jtm_message_log_pkg.Log_Msg
656 ( v_object_id => null
657 , v_object_name => g_object_name
658 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
659 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
660 END IF;
661
662 EXCEPTION WHEN OTHERS THEN
663 /*** catch and log exceptions ***/
664 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
665 jtm_message_log_pkg.Log_Msg
666 ( v_object_id => null
667 , v_object_name => g_object_name
668 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
669 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
670 END IF;
671 x_return_status := FND_API.G_RET_STS_ERROR;
672 END APPLY_CLIENT_CHANGES;
673
674 END CSL_COUNTER_VALUES_PKG;