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