[Home] [Help]
PACKAGE BODY: APPS.CSI_CTR_INTERFACE_PUB
Source
1 PACKAGE BODY CSI_CTR_INTERFACE_PUB as
2 /* $Header: csipcoib.pls 120.1 2005/08/10 17:33 srramakr noship $ */
3
4 /*-----------------------------------------------------------*/
5 /* procedure name: Execute_Open_Interface */
6 /* description : procedure used to capture */
7 /* counter readings from the Open Interface */
8 /*-----------------------------------------------------------*/
9
10 -- Process_status E => Error
11 -- Process_Status R => Ready
12 -- Process_Status P => Success
13 --
14 PROCEDURE Execute_Open_Interface
15 (
16 errbuf OUT NOCOPY VARCHAR2
17 ,retcode OUT NOCOPY NUMBER
18 ,p_batch_name IN VARCHAR2
19 ,p_src_txn_from_date IN DATE
20 ,p_src_txn_to_date IN DATE
21 ,p_purge_processed_recs IN VARCHAR2
22 ,p_reprocess_option IN VARCHAR2
23 ) IS
24 --
25 CURSOR PURGE_CUR is
26 SELECT counter_interface_id
27 FROM CSI_CTR_READINGS_INTERFACE
28 WHERE process_status = 'P';
29
30 TYPE NumTabType IS VARRAY(10000) OF NUMBER;
31 counter_intf_id_del NumTabType;
32 MAX_BUFFER_SIZE NUMBER := 9999;
33 --
34 CURSOR CTR_READINGS_CUR IS
35 SELECT
36 COUNTER_INTERFACE_ID
37 ,BATCH_NAME
38 ,SOURCE_TRANSACTION_DATE
39 ,PROCESS_STATUS
40 ,ERROR_TEXT
41 ,COUNTER_VALUE_ID
42 ,COUNTER_ID
43 ,NVL(VALUE_TIMESTAMP,SYSDATE) VALUE_TIMESTAMP
44 ,COUNTER_READING
45 ,RESET_MODE
46 ,RESET_REASON
47 ,ADJUSTMENT_TYPE
48 ,ADJUSTMENT_READING
49 ,LAST_UPDATE_DATE
50 ,LAST_UPDATED_BY
51 ,CREATION_DATE
52 ,CREATED_BY
53 ,LAST_UPDATE_LOGIN
54 ,ATTRIBUTE1
55 ,ATTRIBUTE2
56 ,ATTRIBUTE3
57 ,ATTRIBUTE4
58 ,ATTRIBUTE5
59 ,ATTRIBUTE6
60 ,ATTRIBUTE7
61 ,ATTRIBUTE8
62 ,ATTRIBUTE9
63 ,ATTRIBUTE10
64 ,ATTRIBUTE11
65 ,ATTRIBUTE12
66 ,ATTRIBUTE13
67 ,ATTRIBUTE14
68 ,ATTRIBUTE15
69 ,ATTRIBUTE16
70 ,ATTRIBUTE17
71 ,ATTRIBUTE18
72 ,ATTRIBUTE19
73 ,ATTRIBUTE20
74 ,ATTRIBUTE21
75 ,ATTRIBUTE22
76 ,ATTRIBUTE23
77 ,ATTRIBUTE24
78 ,ATTRIBUTE25
79 ,ATTRIBUTE26
80 ,ATTRIBUTE27
81 ,ATTRIBUTE28
82 ,ATTRIBUTE29
83 ,ATTRIBUTE30
84 ,ATTRIBUTE_CATEGORY
85 ,COMMENTS
86 ,SOURCE_TRANSACTION_TYPE_ID
87 ,SOURCE_TRANSACTION_ID
88 ,SOURCE_CODE
89 ,SOURCE_LINE_ID
90 ,AUTOMATIC_ROLLOVER_FLAG
91 ,INCLUDE_TARGET_RESETS
92 ,RESET_COUNTER_READING
93 ,OBJECT_VERSION_NUMBER
94 ,DISABLED_FLAG
95 ,COUNTER_NAME
96 FROM CSI_CTR_READINGS_INTERFACE
97 WHERE batch_name = p_batch_name
98 AND process_Status = 'R'
99 AND source_transaction_date between p_src_txn_from_date and p_src_txn_to_date
100 ORDER BY VALUE_TIMESTAMP asc, COUNTER_ID asc;
101 --
102 --
103 TYPE NTabType IS VARRAY(1000) OF NUMBER;
104 TYPE V1TabType IS VARRAY(1000) OF VARCHAR2(1);
105 TYPE V30TabType IS VARRAY(1000) OF VARCHAR2(30);
106 TYPE V50TabType IS VARRAY(1000) OF VARCHAR2(50);
107 TYPE V150TabType IS VARRAY(1000) OF VARCHAR2(150);
108 TYPE V240TabType IS VARRAY(1000) OF VARCHAR2(240);
109 TYPE V255TabType IS VARRAY(1000) OF VARCHAR2(255);
110 TYPE V2000TabType IS VARRAY(1000) OF VARCHAR2(2000);
111 TYPE DTabType IS VARRAY(1000) OF DATE;
112 --
113 l_COUNTER_INTERFACE_ID_mig NTabType;
114 l_BATCH_NAME_mig V30TabType;
115 l_SOURCE_TRANSACTION_DATE_mig DTabType;
116 l_PROCESS_STATUS_mig V1TabType;
117 l_ERROR_TEXT_mig V240TabType;
118 l_COUNTER_VALUE_ID_mig NTabType;
119 l_COUNTER_ID_mig NTabType;
120 l_VALUE_TIMESTAMP_mig DTabType;
121 l_COUNTER_READING_mig NTabType;
122 l_RESET_MODE_mig V30TabType;
123 l_RESET_REASON_mig V255TabType;
124 l_ADJUSTMENT_TYPE_mig V30TabType;
125 l_ADJUSTMENT_READING_mig NTabType;
126 l_LAST_UPDATE_DATE_mig DTabType;
127 l_LAST_UPDATED_BY_mig NTabType;
128 l_CREATION_DATE_mig DTabType;
129 l_CREATED_BY_mig NTabType;
130 l_LAST_UPDATE_LOGIN_mig NTabType;
131 l_ATTRIBUTE1_mig V150TabType;
132 l_ATTRIBUTE2_mig V150TabType;
133 l_ATTRIBUTE3_mig V150TabType;
134 l_ATTRIBUTE4_mig V150TabType;
135 l_ATTRIBUTE5_mig V150TabType;
136 l_ATTRIBUTE6_mig V150TabType;
137 l_ATTRIBUTE7_mig V150TabType;
138 l_ATTRIBUTE8_mig V150TabType;
139 l_ATTRIBUTE9_mig V150TabType;
140 l_ATTRIBUTE10_mig V150TabType;
141 l_ATTRIBUTE11_mig V150TabType;
142 l_ATTRIBUTE12_mig V150TabType;
143 l_ATTRIBUTE13_mig V150TabType;
144 l_ATTRIBUTE14_mig V150TabType;
145 l_ATTRIBUTE15_mig V150TabType;
146 l_ATTRIBUTE16_mig V150TabType;
147 l_ATTRIBUTE17_mig V150TabType;
148 l_ATTRIBUTE18_mig V150TabType;
149 l_ATTRIBUTE19_mig V150TabType;
150 l_ATTRIBUTE20_mig V150TabType;
151 l_ATTRIBUTE21_mig V150TabType;
152 l_ATTRIBUTE22_mig V150TabType;
153 l_ATTRIBUTE23_mig V150TabType;
154 l_ATTRIBUTE24_mig V150TabType;
155 l_ATTRIBUTE25_mig V150TabType;
156 l_ATTRIBUTE26_mig V150TabType;
157 l_ATTRIBUTE27_mig V150TabType;
158 l_ATTRIBUTE28_mig V150TabType;
159 l_ATTRIBUTE29_mig V150TabType;
160 l_ATTRIBUTE30_mig V150TabType;
161 l_ATTRIBUTE_CATEGORY_mig V30TabType;
162 l_COMMENTS_mig V2000TabType;
163 l_SRC_TRANSACTION_TYPE_ID_mig NTabType;
164 l_SRC_TRANSACTION_ID_mig NTabType;
165 l_SOURCE_CODE_mig V30TabType;
166 l_SOURCE_LINE_ID_mig NTabType;
167 l_AUTOMATIC_ROLLOVER_FLAG_mig V1TabType;
168 l_INCLUDE_TARGET_RESETS_mig V1TabType;
169 l_RESET_COUNTER_READING_mig NTabType;
170 l_OBJECT_VERSION_NUMBER_mig NTabType;
171 l_DISABLED_FLAG_mig V1TabType;
172 l_COUNTER_NAME_mig V50TabType;
173 --
174 MAX_OI_BUFFER_SIZE NUMBER := 1000;
175 --
176 l_txn_rec csi_datastructures_pub.transaction_rec;
177 l_temp_txn_rec csi_datastructures_pub.transaction_rec;
178 l_ctr_rdg_rec csi_ctr_datastructures_pub.counter_readings_rec;
179 l_derive_ctr_rec csi_ctr_datastructures_pub.counter_readings_rec;
180 l_temp_ctr_rdg_rec csi_ctr_datastructures_pub.counter_readings_rec;
181 --
182 l_dflt_ctr_prop_rec csi_ctr_datastructures_pub.ctr_property_readings_rec;
183 l_ctr_prop_rdg_rec csi_ctr_datastructures_pub.ctr_property_readings_rec;
184 l_temp_ctr_prop_rec csi_ctr_datastructures_pub.ctr_property_readings_rec;
185 --
186 l_reset_only VARCHAR2(1);
187 --
188 TYPE SRC_TXN_REC IS RECORD
189 ( src_txn_id NUMBER,
190 src_txn_type_id NUMBER,
191 value_timestamp DATE,
192 transaction_id NUMBER
193 );
194 TYPE SRC_TXN_TBL IS TABLE OF SRC_TXN_REC INDEX BY BINARY_INTEGER;
195 --
196 l_src_txn_tbl SRC_TXN_TBL;
197 l_src_txn_count NUMBER := 0;
198 l_exists VARCHAR2(1);
199 --
200 CURSOR PROP_RDG_CUR(p_ctr_intf_id IN NUMBER) IS
201 SELECT *
202 FROM CSI_CTR_READ_PROP_INTERFACE
203 WHERE counter_interface_id = p_ctr_intf_id;
204 --
205 CURSOR DFLT_PROP_RDG(p_counter_id IN NUMBER, p_value_id IN NUMBER) IS
206 select ccp.counter_property_id,ccp.default_value
207 from CSI_COUNTER_PROPERTIES_B ccp
208 where ccp.counter_id = p_counter_id
209 and ((ccp.default_value is not null) or
210 (ccp.default_value is null and ccp.is_nullable = 'N'))
211 and nvl(end_date_active,(sysdate+1)) > sysdate
212 and not exists (select 'x' from CSI_CTR_PROPERTY_READINGS cpr
213 Where cpr.counter_value_id = p_value_id
214 and cpr.counter_property_id = ccp.counter_property_id);
215 --
216 CURSOR LATER_READINGS_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
217 select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
218 ,reset_mode,adjustment_type,include_target_resets
219 from CSI_COUNTER_READINGS
220 where counter_id = p_counter_id
221 and nvl(disabled_flag,'N') = 'N'
222 and value_timestamp > p_value_timestamp
223 ORDER BY value_timestamp asc, counter_value_id asc;
224 --
225 x_msg_data VARCHAR2(2000);
226 l_msg_index NUMBER;
227 l_msg_count NUMBER;
228 x_msg_count NUMBER;
229 x_return_status VARCHAR2(1);
230 --
231 Skip_Error EXCEPTION;
232 Process_Next EXCEPTION;
233 BEGIN
234 IF NVL(p_reprocess_option,'SELECTED') = 'ALL' THEN
235 UPDATE CSI_CTR_READINGS_INTERFACE cri
236 SET process_status = 'R'
237 WHERE cri.batch_name = p_batch_name
238 AND cri.process_Status = 'E'
239 AND cri.source_transaction_date between p_src_txn_from_date and p_src_txn_to_date;
240 COMMIT;
241 END IF;
242 --
243 IF NVL(p_purge_processed_recs,'N') = 'Y' THEN
244 OPEN PURGE_CUR;
245 LOOP
246 FETCH PURGE_CUR BULK COLLECT INTO
247 counter_intf_id_del
248 LIMIT MAX_BUFFER_SIZE;
249 --
250 FORALL i1 IN 1 .. counter_intf_id_del.COUNT
251 DELETE FROM CSI_CTR_READINGS_INTERFACE
252 WHERE counter_interface_id = counter_intf_id_del(i1);
253 --
254 FORALL i1 IN 1 .. counter_intf_id_del.COUNT
255 DELETE FROM CSI_CTR_READ_PROP_INTERFACE
256 WHERE counter_interface_id = counter_intf_id_del(i1);
257 --
258 COMMIT;
259 EXIT WHEN PURGE_CUR%NOTFOUND;
260 END LOOP;
261 COMMIT;
262 CLOSE PURGE_CUR;
263 END IF;
264 --
265 UPDATE CSI_CTR_READINGS_INTERFACE cri
266 SET cri.counter_id = (select ccv.counter_id from CSI_COUNTERS_VL ccv
267 where ccv.name = cri.counter_name)
268 WHERE cri.counter_id IS NULL
269 AND cri.counter_name IS NOT NULL;
270 --
271 COMMIT;
272 --
273 l_src_txn_tbl.DELETE;
274 l_src_txn_count := 0;
275 --
276 OPEN CTR_READINGS_CUR;
277 LOOP
278 FETCH CTR_READINGS_CUR BULK COLLECT INTO
279 l_COUNTER_INTERFACE_ID_mig
280 ,l_BATCH_NAME_mig
281 ,l_SOURCE_TRANSACTION_DATE_mig
282 ,l_PROCESS_STATUS_mig
283 ,l_ERROR_TEXT_mig
284 ,l_COUNTER_VALUE_ID_mig
285 ,l_COUNTER_ID_mig
286 ,l_VALUE_TIMESTAMP_mig
287 ,l_COUNTER_READING_mig
288 ,l_RESET_MODE_mig
289 ,l_RESET_REASON_mig
290 ,l_ADJUSTMENT_TYPE_mig
291 ,l_ADJUSTMENT_READING_mig
292 ,l_LAST_UPDATE_DATE_mig
293 ,l_LAST_UPDATED_BY_mig
294 ,l_CREATION_DATE_mig
295 ,l_CREATED_BY_mig
296 ,l_LAST_UPDATE_LOGIN_mig
297 ,l_ATTRIBUTE1_mig
298 ,l_ATTRIBUTE2_mig
299 ,l_ATTRIBUTE3_mig
300 ,l_ATTRIBUTE4_mig
301 ,l_ATTRIBUTE5_mig
302 ,l_ATTRIBUTE6_mig
303 ,l_ATTRIBUTE7_mig
304 ,l_ATTRIBUTE8_mig
305 ,l_ATTRIBUTE9_mig
306 ,l_ATTRIBUTE10_mig
307 ,l_ATTRIBUTE11_mig
308 ,l_ATTRIBUTE12_mig
309 ,l_ATTRIBUTE13_mig
310 ,l_ATTRIBUTE14_mig
311 ,l_ATTRIBUTE15_mig
312 ,l_ATTRIBUTE16_mig
313 ,l_ATTRIBUTE17_mig
314 ,l_ATTRIBUTE18_mig
315 ,l_ATTRIBUTE19_mig
316 ,l_ATTRIBUTE20_mig
317 ,l_ATTRIBUTE21_mig
318 ,l_ATTRIBUTE22_mig
319 ,l_ATTRIBUTE23_mig
320 ,l_ATTRIBUTE24_mig
321 ,l_ATTRIBUTE25_mig
322 ,l_ATTRIBUTE26_mig
323 ,l_ATTRIBUTE27_mig
324 ,l_ATTRIBUTE28_mig
325 ,l_ATTRIBUTE29_mig
326 ,l_ATTRIBUTE30_mig
327 ,l_ATTRIBUTE_CATEGORY_mig
328 ,l_COMMENTS_mig
329 ,l_SRC_TRANSACTION_TYPE_ID_mig
330 ,l_SRC_TRANSACTION_ID_mig
331 ,l_SOURCE_CODE_mig
332 ,l_SOURCE_LINE_ID_mig
333 ,l_AUTOMATIC_ROLLOVER_FLAG_mig
334 ,l_INCLUDE_TARGET_RESETS_mig
335 ,l_RESET_COUNTER_READING_mig
336 ,l_OBJECT_VERSION_NUMBER_mig
337 ,l_DISABLED_FLAG_mig
338 ,l_COUNTER_NAME_mig
339 LIMIT MAX_OI_BUFFER_SIZE;
340 --
341 fnd_file.put_line(fnd_file.log,'Record count is : '||to_char(l_counter_interface_id_mig.count));
342 FOR j IN 1 .. l_counter_interface_id_mig.count LOOP
343 Begin
344 SAVEPOINT Execute_Open_Interface;
345 --
346 l_ERROR_TEXT_mig(j) := NULL;
347 l_txn_rec := l_temp_txn_rec;
348 l_ctr_rdg_rec := l_temp_ctr_rdg_rec;
349 --
350 -- Build the Counter Readings Record to Call Private API
351 --
352 l_ctr_rdg_rec.counter_value_id := l_COUNTER_VALUE_ID_mig(j);
353 l_ctr_rdg_rec.counter_id := l_COUNTER_ID_mig(j);
354 l_ctr_rdg_rec.value_timestamp := l_VALUE_TIMESTAMP_mig(j);
355 l_ctr_rdg_rec.counter_reading := l_COUNTER_READING_mig(j);
356 l_ctr_rdg_rec.reset_mode := l_RESET_MODE_mig(j);
357 l_ctr_rdg_rec.reset_reason := l_RESET_REASON_mig(j);
358 l_ctr_rdg_rec.adjustment_type := l_ADJUSTMENT_TYPE_mig(j);
359 l_ctr_rdg_rec.adjustment_reading := l_ADJUSTMENT_READING_mig(j);
360 l_ctr_rdg_rec.attribute1 := l_ATTRIBUTE1_mig(j);
361 l_ctr_rdg_rec.attribute2 := l_ATTRIBUTE2_mig(j);
362 l_ctr_rdg_rec.attribute3 := l_ATTRIBUTE3_mig(j);
363 l_ctr_rdg_rec.attribute4 := l_ATTRIBUTE4_mig(j);
364 l_ctr_rdg_rec.attribute5 := l_ATTRIBUTE5_mig(j);
365 l_ctr_rdg_rec.attribute6 := l_ATTRIBUTE6_mig(j);
366 l_ctr_rdg_rec.attribute7 := l_ATTRIBUTE7_mig(j);
367 l_ctr_rdg_rec.attribute8 := l_ATTRIBUTE8_mig(j);
368 l_ctr_rdg_rec.attribute9 := l_ATTRIBUTE9_mig(j);
369 l_ctr_rdg_rec.attribute10 := l_ATTRIBUTE10_mig(j);
370 l_ctr_rdg_rec.attribute11 := l_ATTRIBUTE11_mig(j);
371 l_ctr_rdg_rec.attribute12 := l_ATTRIBUTE12_mig(j);
372 l_ctr_rdg_rec.attribute13 := l_ATTRIBUTE13_mig(j);
373 l_ctr_rdg_rec.attribute14 := l_ATTRIBUTE14_mig(j);
374 l_ctr_rdg_rec.attribute15 := l_ATTRIBUTE15_mig(j);
375 l_ctr_rdg_rec.attribute16 := l_ATTRIBUTE16_mig(j);
376 l_ctr_rdg_rec.attribute17 := l_ATTRIBUTE17_mig(j);
377 l_ctr_rdg_rec.attribute18 := l_ATTRIBUTE18_mig(j);
378 l_ctr_rdg_rec.attribute19 := l_ATTRIBUTE19_mig(j);
379 l_ctr_rdg_rec.attribute20 := l_ATTRIBUTE20_mig(j);
380 l_ctr_rdg_rec.attribute21 := l_ATTRIBUTE21_mig(j);
381 l_ctr_rdg_rec.attribute22 := l_ATTRIBUTE22_mig(j);
382 l_ctr_rdg_rec.attribute23 := l_ATTRIBUTE23_mig(j);
383 l_ctr_rdg_rec.attribute24 := l_ATTRIBUTE24_mig(j);
384 l_ctr_rdg_rec.attribute25 := l_ATTRIBUTE25_mig(j);
385 l_ctr_rdg_rec.attribute26 := l_ATTRIBUTE26_mig(j);
386 l_ctr_rdg_rec.attribute27 := l_ATTRIBUTE27_mig(j);
387 l_ctr_rdg_rec.attribute28 := l_ATTRIBUTE28_mig(j);
388 l_ctr_rdg_rec.attribute29 := l_ATTRIBUTE29_mig(j);
389 l_ctr_rdg_rec.attribute30 := l_ATTRIBUTE30_mig(j);
390 l_ctr_rdg_rec.attribute_category := l_ATTRIBUTE_CATEGORY_mig(j);
391 l_ctr_rdg_rec.comments := l_COMMENTS_mig(j);
392 l_ctr_rdg_rec.source_code := l_SOURCE_CODE_mig(j);
393 l_ctr_rdg_rec.source_line_id := l_SOURCE_LINE_ID_mig(j);
394 l_ctr_rdg_rec.automatic_rollover_flag := l_AUTOMATIC_ROLLOVER_FLAG_mig(j);
395 l_ctr_rdg_rec.include_target_resets := l_INCLUDE_TARGET_RESETS_mig(j);
396 l_ctr_rdg_rec.reset_counter_reading := l_RESET_COUNTER_READING_mig(j);
397 l_ctr_rdg_rec.object_version_number := l_OBJECT_VERSION_NUMBER_mig(j);
398 l_ctr_rdg_rec.disabled_flag := l_DISABLED_FLAG_mig(j);
399 --
400 l_txn_rec.transaction_type_id := l_SRC_TRANSACTION_TYPE_ID_mig(j);
401 l_txn_rec.source_header_ref_id := l_SRC_TRANSACTION_ID_mig(j);
402 l_txn_rec.source_transaction_date := l_SOURCE_TRANSACTION_DATE_mig(j);
403 --
404 IF NVL(l_DISABLED_FLAG_mig(j),'N') = 'Y' THEN
405 Csi_Counter_Readings_Pvt.Update_Counter_Reading
406 (
407 p_api_version => 1.0
408 ,p_commit => fnd_api.g_false
409 ,p_init_msg_list => fnd_api.g_true
410 ,p_validation_level => fnd_api.g_valid_level_full
411 ,p_ctr_rdg_rec => l_ctr_rdg_rec
412 ,x_return_status => x_return_status
413 ,x_msg_count => x_msg_count
414 ,x_msg_data => x_msg_data
415 );
416 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
417 l_msg_index := 1;
418 l_msg_count := x_msg_count;
419 WHILE l_msg_count > 0 LOOP
420 x_msg_data := FND_MSG_PUB.GET
421 ( l_msg_index,
422 FND_API.G_FALSE
423 );
424 l_msg_index := l_msg_index + 1;
425 l_msg_count := l_msg_count - 1;
426 END LOOP;
427 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
428 RAISE Skip_Error;
429 ELSE
430 l_PROCESS_STATUS_mig(j) := 'E';
431 RAISE Process_Next; -- Process the next record
432 END IF;
433 END IF; -- Disabled_flag check
434 -- Check the existence of Transaction
435 l_exists := 'N';
436 IF l_src_txn_tbl.count > 0 THEN
437 FOR src_txn IN l_src_txn_tbl.FIRST .. l_src_txn_tbl.LAST LOOP
438 IF l_src_txn_tbl(src_txn).src_txn_id = l_SRC_TRANSACTION_ID_mig(j) AND
439 l_src_txn_tbl(src_txn).src_txn_type_id = l_SRC_TRANSACTION_TYPE_ID_mig(j) AND
440 l_src_txn_tbl(src_txn).value_timestamp = l_VALUE_TIMESTAMP_mig(j) THEN
441 l_exists := 'Y';
442 l_txn_rec.transaction_id := l_src_txn_tbl(src_txn).transaction_id;
443 exit;
444 END IF;
445 END LOOP;
446 END IF;
447 --
448 IF l_exists = 'N' THEN
449 Csi_Counter_Readings_Pvt.Create_Reading_Transaction
450 ( p_api_version => 1.0
451 ,p_commit => fnd_api.g_false
452 ,p_init_msg_list => fnd_api.g_true
453 ,p_validation_level => fnd_api.g_valid_level_full
454 ,p_txn_rec => l_txn_rec
455 ,x_return_status => x_return_status
456 ,x_msg_count => x_msg_count
457 ,x_msg_data => x_msg_data
458 );
459 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
460 l_msg_index := 1;
461 FND_MSG_PUB.Count_And_Get
462 (p_count => x_msg_count,
463 p_data => x_msg_data
464 );
465 l_msg_count := x_msg_count;
466 WHILE l_msg_count > 0 LOOP
467 x_msg_data := FND_MSG_PUB.GET
468 ( l_msg_index,
469 FND_API.G_FALSE );
470 l_msg_index := l_msg_index + 1;
471 l_msg_count := l_msg_count - 1;
472 END LOOP;
473 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
474 RAISE Skip_Error;
475 END IF;
476 END IF; -- Transaction exists check
477 --
478 --
479 -- Check whether this is Reset only reading.
480 -- This will decide whether to compute Derive counters or not
481 --
482 IF NVL(l_ctr_rdg_rec.counter_reading,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM AND
483 NVL(l_ctr_rdg_rec.reset_mode,FND_API.G_MISS_CHAR) = 'SOFT' THEN
484 l_reset_only := 'T';
485 ELSE
486 l_reset_only := 'F';
487 END IF;
488 --
489 -- Calling Capture_Counter_Reading PVT
490 Csi_Counter_Readings_Pvt.Capture_Counter_Reading
491 (
492 p_api_version => 1.0
493 ,p_commit => fnd_api.g_false
494 ,p_init_msg_list => fnd_api.g_true
495 ,p_validation_level => fnd_api.g_valid_level_full
496 ,p_txn_rec => l_txn_rec
497 ,p_ctr_rdg_rec => l_ctr_rdg_rec
498 ,x_return_status => x_return_status
499 ,x_msg_count => x_msg_count
500 ,x_msg_data => x_msg_data
501 );
502 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
503 l_msg_index := 1;
504 l_msg_count := x_msg_count;
505 WHILE l_msg_count > 0 LOOP
506 x_msg_data := FND_MSG_PUB.GET
507 ( l_msg_index,
508 FND_API.G_FALSE
509 );
510 l_msg_index := l_msg_index + 1;
511 l_msg_count := l_msg_count - 1;
512 END LOOP;
513 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
514 RAISE Skip_Error;
515 ELSE
516 l_COUNTER_VALUE_ID_mig(j) := l_ctr_rdg_rec.counter_value_id;
517 END IF;
518 --
519 FOR prop_rec IN PROP_RDG_CUR(l_COUNTER_INTERFACE_ID_mig(j)) LOOP
520 l_ctr_prop_rdg_rec := l_temp_ctr_prop_rec;
521 --
522 l_ctr_prop_rdg_rec.counter_prop_value_id := prop_rec.counter_prop_value_id;
523 l_ctr_prop_rdg_rec.counter_value_id := l_ctr_rdg_rec.counter_value_id;
524 l_ctr_prop_rdg_rec.counter_property_id := prop_rec.counter_property_id;
525 l_ctr_prop_rdg_rec.property_value := prop_rec.property_value;
526 l_ctr_prop_rdg_rec.value_timestamp := l_ctr_rdg_rec.value_timestamp;--prop_rec.value_timestamp;
527 l_ctr_prop_rdg_rec.attribute1 := prop_rec.attribute1;
528 l_ctr_prop_rdg_rec.attribute2 := prop_rec.attribute2;
529 l_ctr_prop_rdg_rec.attribute3 := prop_rec.attribute3;
530 l_ctr_prop_rdg_rec.attribute4 := prop_rec.attribute4;
531 l_ctr_prop_rdg_rec.attribute5 := prop_rec.attribute5;
532 l_ctr_prop_rdg_rec.attribute6 := prop_rec.attribute6;
533 l_ctr_prop_rdg_rec.attribute7 := prop_rec.attribute7;
534 l_ctr_prop_rdg_rec.attribute8 := prop_rec.attribute8;
535 l_ctr_prop_rdg_rec.attribute9 := prop_rec.attribute9;
536 l_ctr_prop_rdg_rec.attribute1 := prop_rec.attribute10;
537 l_ctr_prop_rdg_rec.attribute1 := prop_rec.attribute11;
538 l_ctr_prop_rdg_rec.attribute12 := prop_rec.attribute12;
539 l_ctr_prop_rdg_rec.attribute13 := prop_rec.attribute13;
540 l_ctr_prop_rdg_rec.attribute14 := prop_rec.attribute14;
541 l_ctr_prop_rdg_rec.attribute15 := prop_rec.attribute15;
542 l_ctr_prop_rdg_rec.attribute_category := prop_rec.attribute_category;
543 --
544 Csi_Counter_Readings_Pvt.Capture_Ctr_Property_Reading
545 (
546 p_api_version => 1.0
547 ,p_commit => fnd_api.g_false
548 ,p_init_msg_list => fnd_api.g_true
549 ,p_validation_level => fnd_api.g_valid_level_full
550 ,p_ctr_prop_rdg_rec => l_ctr_prop_rdg_rec
551 ,x_return_status => x_return_status
552 ,x_msg_count => x_msg_count
553 ,x_msg_data => x_msg_data
554 );
555 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
556 l_msg_index := 1;
557 l_msg_count := x_msg_count;
558 WHILE l_msg_count > 0 LOOP
559 x_msg_data := FND_MSG_PUB.GET
560 ( l_msg_index,
561 FND_API.G_FALSE
562 );
563 l_msg_index := l_msg_index + 1;
564 l_msg_count := l_msg_count - 1;
565 END LOOP;
566 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
567 RAISE Skip_Error;
568 END IF;
569 END LOOP; -- End of Property interface loop
570 --
571 -- Capture Default Property Reading
572 FOR dflt_rec IN DFLT_PROP_RDG(l_ctr_rdg_rec.counter_id,l_ctr_rdg_rec.counter_value_id)
573 LOOP
574 l_dflt_ctr_prop_rec := l_temp_ctr_prop_rec; -- Initialize
575 --
576 l_dflt_ctr_prop_rec.counter_property_id := dflt_rec.counter_property_id;
577 l_dflt_ctr_prop_rec.property_value := dflt_rec.default_value;
578 l_dflt_ctr_prop_rec.value_timestamp := l_ctr_rdg_rec.value_timestamp;
579 l_dflt_ctr_prop_rec.counter_value_id := l_ctr_rdg_rec.counter_value_id;
580 --
581 Csi_Counter_Readings_Pvt.Capture_Ctr_Property_Reading
582 (
583 p_api_version => 1.0
584 ,p_commit => fnd_api.g_false
585 ,p_init_msg_list => fnd_api.g_true
586 ,p_validation_level => fnd_api.g_valid_level_full
587 ,p_ctr_prop_rdg_rec => l_dflt_ctr_prop_rec
588 ,x_return_status => x_return_status
589 ,x_msg_count => x_msg_count
590 ,x_msg_data => x_msg_data
591 );
592 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
593 l_msg_index := 1;
594 l_msg_count := x_msg_count;
595 WHILE l_msg_count > 0 LOOP
596 x_msg_data := FND_MSG_PUB.GET
597 ( l_msg_index,
598 FND_API.G_FALSE
599 );
600 l_msg_index := l_msg_index + 1;
601 l_msg_count := l_msg_count - 1;
602 END LOOP;
603 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
604 RAISE Skip_Error;
605 END IF;
606 END LOOP; -- Default Property Loop
607 --
608 -- Call compute Derive filters only if the current capture is not a pure reset.
609 -- This is applicable for the Later readings cursor as Reset should be the last reading.
610 IF l_reset_only = 'F' THEN
611 l_derive_ctr_rec := l_temp_ctr_rdg_rec;
612 --
613 l_derive_ctr_rec.counter_value_id := l_ctr_rdg_rec.counter_value_id;
614 l_derive_ctr_rec.counter_id := l_ctr_rdg_rec.counter_id;
615 l_derive_ctr_rec.value_timestamp := l_ctr_rdg_rec.value_timestamp;
616 l_derive_ctr_rec.source_code := l_ctr_rdg_rec.source_code;
617 l_derive_ctr_rec.source_line_id := l_ctr_rdg_rec.source_line_id;
618 --
619 -- Call Compute Derive Counters
620 Csi_Counter_Readings_Pvt.Compute_Derive_Counters
621 (
622 p_api_version => 1.0
623 ,p_commit => fnd_api.g_false
624 ,p_init_msg_list => fnd_api.g_true
625 ,p_validation_level => fnd_api.g_valid_level_full
626 ,p_txn_rec => l_txn_rec
627 ,p_ctr_rdg_rec => l_derive_ctr_rec
628 ,p_mode => 'CREATE'
629 ,x_return_status => x_return_status
630 ,x_msg_count => x_msg_count
631 ,x_msg_data => x_msg_data
632 );
633 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
634 l_msg_index := 1;
635 l_msg_count := x_msg_count;
636 WHILE l_msg_count > 0 LOOP
637 x_msg_data := FND_MSG_PUB.GET
638 ( l_msg_index,
639 FND_API.G_FALSE
640 );
641 l_msg_index := l_msg_index + 1;
642 l_msg_count := l_msg_count - 1;
643 END LOOP;
644 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
645 RAISE Skip_error;
646 END IF;
647 --
648 -- Re-compute Derive comunters for the Later Readings
649 -- No need for SOFT reset as we would not have created it before.
650 FOR later_rdg IN LATER_READINGS_CUR(l_ctr_rdg_rec.counter_id,
651 l_ctr_rdg_rec.value_timestamp)
652 LOOP
653 IF NVL(later_rdg.reset_mode,'$#$') <> 'SOFT' THEN
654 l_derive_ctr_rec := l_temp_ctr_rdg_rec;
655 --
656 l_derive_ctr_rec.counter_value_id := later_rdg.counter_value_id;
657 l_derive_ctr_rec.counter_id := l_ctr_rdg_rec.counter_id;
658 l_derive_ctr_rec.value_timestamp := later_rdg.value_timestamp;
659 --
660 Csi_Counter_Readings_Pvt.Compute_Derive_Counters
661 (
662 p_api_version => 1.0
663 ,p_commit => fnd_api.g_false
664 ,p_init_msg_list => fnd_api.g_true
665 ,p_validation_level => fnd_api.g_valid_level_full
666 ,p_txn_rec => l_txn_rec
667 ,p_ctr_rdg_rec => l_derive_ctr_rec
668 ,p_mode => 'UPDATE'
669 ,x_return_status => x_return_status
670 ,x_msg_count => x_msg_count
671 ,x_msg_data => x_msg_data
672 );
673 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
674 l_msg_index := 1;
675 l_msg_count := x_msg_count;
676 WHILE l_msg_count > 0 LOOP
677 x_msg_data := FND_MSG_PUB.GET
678 ( l_msg_index,
679 FND_API.G_FALSE
680 );
681 l_msg_index := l_msg_index + 1;
682 l_msg_count := l_msg_count - 1;
683 END LOOP;
684 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
685 RAISE Skip_error;
686 END IF;
687 END IF; -- End of Derive counter call check
688 END LOOP;
689 END IF; -- l_reset_only check
690 --
691 -- Call Compute Formula
692 Csi_Counter_Readings_Pvt.Compute_Formula_Counters
693 (
694 p_api_version => 1.0
695 ,p_commit => fnd_api.g_false
696 ,p_init_msg_list => fnd_api.g_true
697 ,p_validation_level => fnd_api.g_valid_level_full
698 ,p_txn_rec => l_txn_rec
699 ,p_ctr_rdg_rec => l_ctr_rdg_rec
700 ,x_return_status => x_return_status
701 ,x_msg_count => x_msg_count
702 ,x_msg_data => x_msg_data
703 );
704 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
705 l_msg_index := 1;
706 l_msg_count := x_msg_count;
707 WHILE l_msg_count > 0 LOOP
708 x_msg_data := FND_MSG_PUB.GET
709 ( l_msg_index,
710 FND_API.G_FALSE
711 );
712 l_msg_index := l_msg_index + 1;
713 l_msg_count := l_msg_count - 1;
714 END LOOP;
715 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
716 RAISE Skip_Error;
717 END IF;
718 --
719 -- If Success then cache the Transaction info
720 l_src_txn_count := l_src_txn_count + 1;
721 l_src_txn_tbl(l_src_txn_count).src_txn_id := l_SRC_TRANSACTION_ID_mig(j);
722 l_src_txn_tbl(l_src_txn_count).src_txn_type_id := l_SRC_TRANSACTION_TYPE_ID_mig(j);
723 l_src_txn_tbl(l_src_txn_count).value_timestamp := l_VALUE_TIMESTAMP_mig(j);
724 l_src_txn_tbl(l_src_txn_count).transaction_id := l_txn_rec.transaction_id;
725 --
726 l_PROCESS_STATUS_mig(j) := 'P';
727 Exception
728 When Process_next then
729 null;
730 When Skip_Error then
731 l_PROCESS_STATUS_mig(j) := 'E';
732 Rollback to Execute_Open_Interface;
733 End;
734 END LOOP; -- l_counter_interface_id_mig.count Loop
735 --
736 -- Bulk Update error text,process_status and counter_value_id
737 FORALL j IN 1 .. l_counter_interface_id_mig.count
738 UPDATE CSI_CTR_READINGS_INTERFACE
739 SET process_status = l_PROCESS_STATUS_mig(j),
740 counter_value_id = l_COUNTER_VALUE_ID_mig(j),
741 error_text = l_ERROR_TEXT_mig(j)
742 WHERE counter_interface_id = l_counter_interface_id_mig(j);
743 --
744 COMMIT;
745 EXIT WHEN CTR_READINGS_CUR%NOTFOUND;
746 END LOOP;
747 COMMIT;
748 CLOSE CTR_READINGS_CUR;
749 END Execute_Open_Interface;
750 --
751 END CSI_CTR_INTERFACE_PUB;