[Home] [Help]
PACKAGE BODY: APPS.CSI_CTR_INTERFACE_PUB
Source
1 PACKAGE BODY CSI_CTR_INTERFACE_PUB as
2 /* $Header: csipcoib.pls 120.2.12020000.2 2012/07/04 10:52:32 sjawaji ship $ */
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 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_CTR_INTERFACE_PUB';
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 VARCHAR2
20 ,p_src_txn_to_date IN VARCHAR2
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(l_src_txn_from_date DATE, l_src_txn_to_date DATE) IS --Changed for Bug 13262205
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 l_src_txn_from_date and l_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 --bug 13262205
189 l_src_txn_from_date DATE;
190 l_src_txn_to_date DATE;
191
192 --
193 TYPE SRC_TXN_REC IS RECORD
194 ( src_txn_id NUMBER,
195 src_txn_type_id NUMBER,
196 value_timestamp DATE,
197 transaction_id NUMBER
198 );
199 TYPE SRC_TXN_TBL IS TABLE OF SRC_TXN_REC INDEX BY BINARY_INTEGER;
200 --
201 l_src_txn_tbl SRC_TXN_TBL;
202 l_src_txn_count NUMBER := 0;
203 l_exists VARCHAR2(1);
204 --
205 CURSOR PROP_RDG_CUR(p_ctr_intf_id IN NUMBER) IS
206 SELECT *
207 FROM CSI_CTR_READ_PROP_INTERFACE
208 WHERE counter_interface_id = p_ctr_intf_id;
209 --
210 CURSOR DFLT_PROP_RDG(p_counter_id IN NUMBER, p_value_id IN NUMBER) IS
211 select ccp.counter_property_id,ccp.default_value
212 from CSI_COUNTER_PROPERTIES_B ccp
213 where ccp.counter_id = p_counter_id
214 and ((ccp.default_value is not null) or
215 (ccp.default_value is null and ccp.is_nullable = 'N'))
216 and nvl(end_date_active,(sysdate+1)) > sysdate
217 and not exists (select 'x' from CSI_CTR_PROPERTY_READINGS cpr
218 Where cpr.counter_value_id = p_value_id
219 and cpr.counter_property_id = ccp.counter_property_id);
220 --
221 CURSOR LATER_READINGS_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
222 select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
223 ,reset_mode,adjustment_type,include_target_resets
224 from CSI_COUNTER_READINGS
225 where counter_id = p_counter_id
226 and nvl(disabled_flag,'N') = 'N'
227 and value_timestamp > p_value_timestamp
228 ORDER BY value_timestamp asc, counter_value_id asc;
229 --
230 x_msg_data VARCHAR2(2000);
231 l_msg_index NUMBER;
232 l_msg_count NUMBER;
233 x_msg_count NUMBER;
234 x_return_status VARCHAR2(1);
235 --
236 Skip_Error EXCEPTION;
237 Process_Next EXCEPTION;
238 --added for 13866801 mvaradam
239 l_api_name CONSTANT VARCHAR2(30) := 'EXECUTE_OPEN_INTERFACE';
240 p_api_version CONSTANT NUMBER := 1.0;
241 p_commit VARCHAR2(30) ;
242 p_init_msg_list VARCHAR2(1) ;
243 p_validation_level NUMBER;
244 p_txn_tbl csi_datastructures_pub.transaction_tbl;
245 p_ctr_rdg_tbl csi_ctr_datastructures_pub.counter_readings_tbl;
246 p_ctr_prop_rdg_tbl csi_ctr_datastructures_pub.ctr_property_readings_tbl;
247 --Added for 13866801 mvaradam
248 BEGIN
249 --Added for Bug 13262205
250 l_src_txn_from_date := fnd_date.canonical_to_date(p_src_txn_from_date);
251 l_src_txn_to_date := fnd_date.canonical_to_date(p_src_txn_to_date);
252
253 IF NVL(p_reprocess_option,'SELECTED') = 'ALL' THEN
254 UPDATE CSI_CTR_READINGS_INTERFACE cri
255 SET process_status = 'R'
256 WHERE cri.batch_name = p_batch_name
257 AND cri.process_Status = 'E'
258 AND cri.source_transaction_date between l_src_txn_from_date and l_src_txn_to_date; --Changed for Bug 13262205
259 COMMIT;
260 END IF;
261 --
262 IF NVL(p_purge_processed_recs,'N') = 'Y' THEN
263 OPEN PURGE_CUR;
264 LOOP
265 FETCH PURGE_CUR BULK COLLECT INTO
266 counter_intf_id_del
267 LIMIT MAX_BUFFER_SIZE;
268 --
269 FORALL i1 IN 1 .. counter_intf_id_del.COUNT
270 DELETE FROM CSI_CTR_READINGS_INTERFACE
271 WHERE counter_interface_id = counter_intf_id_del(i1);
272 --
273 FORALL i1 IN 1 .. counter_intf_id_del.COUNT
274 DELETE FROM CSI_CTR_READ_PROP_INTERFACE
275 WHERE counter_interface_id = counter_intf_id_del(i1);
276 --
277 COMMIT;
278 EXIT WHEN PURGE_CUR%NOTFOUND;
279 END LOOP;
280 COMMIT;
281 CLOSE PURGE_CUR;
282 END IF;
283 --
284 UPDATE CSI_CTR_READINGS_INTERFACE cri
285 SET cri.counter_id = (select ccv.counter_id from CSI_COUNTERS_VL ccv
286 where ccv.name = cri.counter_name)
287 WHERE cri.counter_id IS NULL
288 AND cri.counter_name IS NOT NULL;
289 --
290 COMMIT;
291 --
292 l_src_txn_tbl.DELETE;
293 l_src_txn_count := 0;
294 --
295 OPEN CTR_READINGS_CUR(l_src_txn_from_date,l_src_txn_to_date); --Changed for Bug 13262205
296 LOOP
297 FETCH CTR_READINGS_CUR BULK COLLECT INTO
298 l_COUNTER_INTERFACE_ID_mig
299 ,l_BATCH_NAME_mig
300 ,l_SOURCE_TRANSACTION_DATE_mig
301 ,l_PROCESS_STATUS_mig
302 ,l_ERROR_TEXT_mig
303 ,l_COUNTER_VALUE_ID_mig
304 ,l_COUNTER_ID_mig
305 ,l_VALUE_TIMESTAMP_mig
306 ,l_COUNTER_READING_mig
307 ,l_RESET_MODE_mig
308 ,l_RESET_REASON_mig
309 ,l_ADJUSTMENT_TYPE_mig
310 ,l_ADJUSTMENT_READING_mig
311 ,l_LAST_UPDATE_DATE_mig
312 ,l_LAST_UPDATED_BY_mig
313 ,l_CREATION_DATE_mig
314 ,l_CREATED_BY_mig
315 ,l_LAST_UPDATE_LOGIN_mig
316 ,l_ATTRIBUTE1_mig
317 ,l_ATTRIBUTE2_mig
318 ,l_ATTRIBUTE3_mig
319 ,l_ATTRIBUTE4_mig
320 ,l_ATTRIBUTE5_mig
321 ,l_ATTRIBUTE6_mig
322 ,l_ATTRIBUTE7_mig
323 ,l_ATTRIBUTE8_mig
324 ,l_ATTRIBUTE9_mig
325 ,l_ATTRIBUTE10_mig
326 ,l_ATTRIBUTE11_mig
327 ,l_ATTRIBUTE12_mig
328 ,l_ATTRIBUTE13_mig
329 ,l_ATTRIBUTE14_mig
330 ,l_ATTRIBUTE15_mig
331 ,l_ATTRIBUTE16_mig
332 ,l_ATTRIBUTE17_mig
333 ,l_ATTRIBUTE18_mig
334 ,l_ATTRIBUTE19_mig
335 ,l_ATTRIBUTE20_mig
336 ,l_ATTRIBUTE21_mig
337 ,l_ATTRIBUTE22_mig
338 ,l_ATTRIBUTE23_mig
339 ,l_ATTRIBUTE24_mig
340 ,l_ATTRIBUTE25_mig
341 ,l_ATTRIBUTE26_mig
342 ,l_ATTRIBUTE27_mig
343 ,l_ATTRIBUTE28_mig
344 ,l_ATTRIBUTE29_mig
345 ,l_ATTRIBUTE30_mig
346 ,l_ATTRIBUTE_CATEGORY_mig
347 ,l_COMMENTS_mig
348 ,l_SRC_TRANSACTION_TYPE_ID_mig
349 ,l_SRC_TRANSACTION_ID_mig
350 ,l_SOURCE_CODE_mig
351 ,l_SOURCE_LINE_ID_mig
352 ,l_AUTOMATIC_ROLLOVER_FLAG_mig
353 ,l_INCLUDE_TARGET_RESETS_mig
354 ,l_RESET_COUNTER_READING_mig
355 ,l_OBJECT_VERSION_NUMBER_mig
356 ,l_DISABLED_FLAG_mig
357 ,l_COUNTER_NAME_mig
358 LIMIT MAX_OI_BUFFER_SIZE;
359 --
360 fnd_file.put_line(fnd_file.log,'Record count is : '||to_char(l_counter_interface_id_mig.count));
361 FOR j IN 1 .. l_counter_interface_id_mig.count LOOP
362 Begin
363 SAVEPOINT Execute_Open_Interface;
364 --
365 l_ERROR_TEXT_mig(j) := NULL;
366 l_txn_rec := l_temp_txn_rec;
367 l_ctr_rdg_rec := l_temp_ctr_rdg_rec;
368 --
369 -- Build the Counter Readings Record to Call Private API
370 --
371 l_ctr_rdg_rec.counter_value_id := l_COUNTER_VALUE_ID_mig(j);
372 l_ctr_rdg_rec.counter_id := l_COUNTER_ID_mig(j);
373 l_ctr_rdg_rec.value_timestamp := l_VALUE_TIMESTAMP_mig(j);
374 l_ctr_rdg_rec.counter_reading := l_COUNTER_READING_mig(j);
375 l_ctr_rdg_rec.reset_mode := l_RESET_MODE_mig(j);
376 l_ctr_rdg_rec.reset_reason := l_RESET_REASON_mig(j);
377 l_ctr_rdg_rec.adjustment_type := l_ADJUSTMENT_TYPE_mig(j);
378 l_ctr_rdg_rec.adjustment_reading := l_ADJUSTMENT_READING_mig(j);
379 l_ctr_rdg_rec.attribute1 := l_ATTRIBUTE1_mig(j);
380 l_ctr_rdg_rec.attribute2 := l_ATTRIBUTE2_mig(j);
381 l_ctr_rdg_rec.attribute3 := l_ATTRIBUTE3_mig(j);
382 l_ctr_rdg_rec.attribute4 := l_ATTRIBUTE4_mig(j);
383 l_ctr_rdg_rec.attribute5 := l_ATTRIBUTE5_mig(j);
384 l_ctr_rdg_rec.attribute6 := l_ATTRIBUTE6_mig(j);
385 l_ctr_rdg_rec.attribute7 := l_ATTRIBUTE7_mig(j);
386 l_ctr_rdg_rec.attribute8 := l_ATTRIBUTE8_mig(j);
387 l_ctr_rdg_rec.attribute9 := l_ATTRIBUTE9_mig(j);
388 l_ctr_rdg_rec.attribute10 := l_ATTRIBUTE10_mig(j);
389 l_ctr_rdg_rec.attribute11 := l_ATTRIBUTE11_mig(j);
390 l_ctr_rdg_rec.attribute12 := l_ATTRIBUTE12_mig(j);
391 l_ctr_rdg_rec.attribute13 := l_ATTRIBUTE13_mig(j);
392 l_ctr_rdg_rec.attribute14 := l_ATTRIBUTE14_mig(j);
393 l_ctr_rdg_rec.attribute15 := l_ATTRIBUTE15_mig(j);
394 l_ctr_rdg_rec.attribute16 := l_ATTRIBUTE16_mig(j);
395 l_ctr_rdg_rec.attribute17 := l_ATTRIBUTE17_mig(j);
396 l_ctr_rdg_rec.attribute18 := l_ATTRIBUTE18_mig(j);
397 l_ctr_rdg_rec.attribute19 := l_ATTRIBUTE19_mig(j);
398 l_ctr_rdg_rec.attribute20 := l_ATTRIBUTE20_mig(j);
399 l_ctr_rdg_rec.attribute21 := l_ATTRIBUTE21_mig(j);
400 l_ctr_rdg_rec.attribute22 := l_ATTRIBUTE22_mig(j);
401 l_ctr_rdg_rec.attribute23 := l_ATTRIBUTE23_mig(j);
402 l_ctr_rdg_rec.attribute24 := l_ATTRIBUTE24_mig(j);
403 l_ctr_rdg_rec.attribute25 := l_ATTRIBUTE25_mig(j);
404 l_ctr_rdg_rec.attribute26 := l_ATTRIBUTE26_mig(j);
405 l_ctr_rdg_rec.attribute27 := l_ATTRIBUTE27_mig(j);
406 l_ctr_rdg_rec.attribute28 := l_ATTRIBUTE28_mig(j);
407 l_ctr_rdg_rec.attribute29 := l_ATTRIBUTE29_mig(j);
408 l_ctr_rdg_rec.attribute30 := l_ATTRIBUTE30_mig(j);
409 l_ctr_rdg_rec.attribute_category := l_ATTRIBUTE_CATEGORY_mig(j);
410 l_ctr_rdg_rec.comments := l_COMMENTS_mig(j);
411 l_ctr_rdg_rec.source_code := l_SOURCE_CODE_mig(j);
412 l_ctr_rdg_rec.source_line_id := l_SOURCE_LINE_ID_mig(j);
413 l_ctr_rdg_rec.automatic_rollover_flag := l_AUTOMATIC_ROLLOVER_FLAG_mig(j);
414 l_ctr_rdg_rec.include_target_resets := l_INCLUDE_TARGET_RESETS_mig(j);
415 l_ctr_rdg_rec.reset_counter_reading := l_RESET_COUNTER_READING_mig(j);
416 l_ctr_rdg_rec.object_version_number := l_OBJECT_VERSION_NUMBER_mig(j);
417 l_ctr_rdg_rec.disabled_flag := l_DISABLED_FLAG_mig(j);
418 --
419 l_txn_rec.transaction_type_id := l_SRC_TRANSACTION_TYPE_ID_mig(j);
420 l_txn_rec.source_header_ref_id := l_SRC_TRANSACTION_ID_mig(j);
421 l_txn_rec.source_transaction_date := l_SOURCE_TRANSACTION_DATE_mig(j);
422 --
423 -- Calling Customer Pre-processing Hook
424 IF ( JTF_USR_HKS.Ok_to_execute( G_PKG_NAME, l_api_name, 'B', 'C' ) ) then
425
426 p_ctr_rdg_tbl(1) := l_ctr_rdg_rec;
427 p_ctr_prop_rdg_tbl(1) := l_dflt_ctr_prop_rec;
428
429 CSI_COUNTER_READINGS_CUHK.CAPTURE_COUNTER_READING_PRE
430 (
431 p_api_version => p_api_version
432 ,p_commit => p_commit
433 ,p_init_msg_list => p_init_msg_list
434 ,p_validation_level => p_validation_level
435 ,p_txn_tbl => p_txn_tbl
436 ,p_ctr_rdg_tbl => p_ctr_rdg_tbl
437 ,p_ctr_prop_rdg_tbl => p_ctr_prop_rdg_tbl
438 ,x_return_status => x_return_status
439 ,x_msg_count => x_msg_count
440 ,x_msg_data => x_msg_data
441 );
442 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
443 csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_READINGS_CUHK.Capture_Counter_Reading_Pre API ');
444 l_msg_index := 1;
445 l_msg_count := x_msg_count;
446 WHILE l_msg_count > 0 LOOP
447 x_msg_data := FND_MSG_PUB.GET
448 ( l_msg_index,
449 FND_API.G_FALSE
450 );
451 csi_ctr_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
452 l_msg_index := l_msg_index + 1;
453 l_msg_count := l_msg_count - 1;
454 END LOOP;
455 RAISE FND_API.G_EXC_ERROR;
456 END IF;
457 END IF;
458
459 -- Calling Vertical Pre-processing Hook
460 IF ( JTF_USR_HKS.Ok_to_execute( G_PKG_NAME, l_api_name, 'B', 'V' ) ) then
461 CSI_COUNTER_READINGS_VUHK.CAPTURE_COUNTER_READING_PRE
462 (
463 p_api_version => p_api_version
464 ,p_commit => p_commit
465 ,p_init_msg_list => p_init_msg_list
466 ,p_validation_level => p_validation_level
467 ,p_txn_tbl => p_txn_tbl
468 ,p_ctr_rdg_tbl => p_ctr_rdg_tbl
469 ,p_ctr_prop_rdg_tbl => p_ctr_prop_rdg_tbl
470 ,x_return_status => x_return_status
471 ,x_msg_count => x_msg_count
472 ,x_msg_data => x_msg_data
473 );
474 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
475 csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_READINGS_VUHK.Capture_Counter_Reading_Pre API ');
476 l_msg_index := 1;
477 l_msg_count := x_msg_count;
478 WHILE l_msg_count > 0 LOOP
479 x_msg_data := FND_MSG_PUB.GET
480 ( l_msg_index,
481 FND_API.G_FALSE
482 );
483 csi_ctr_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
484 l_msg_index := l_msg_index + 1;
485 l_msg_count := l_msg_count - 1;
486 END LOOP;
487 RAISE FND_API.G_EXC_ERROR;
488 END IF;
489 END IF;
490
491 IF NVL(l_DISABLED_FLAG_mig(j),'N') = 'Y' THEN
492 Csi_Counter_Readings_Pvt.Update_Counter_Reading
493 (
494 p_api_version => 1.0
495 ,p_commit => fnd_api.g_false
496 ,p_init_msg_list => fnd_api.g_true
497 ,p_validation_level => fnd_api.g_valid_level_full
498 ,p_ctr_rdg_rec => l_ctr_rdg_rec
499 ,x_return_status => x_return_status
500 ,x_msg_count => x_msg_count
501 ,x_msg_data => x_msg_data
502 );
503 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
504 l_msg_index := 1;
505 l_msg_count := x_msg_count;
506 WHILE l_msg_count > 0 LOOP
507 x_msg_data := FND_MSG_PUB.GET
508 ( l_msg_index,
509 FND_API.G_FALSE
510 );
511 l_msg_index := l_msg_index + 1;
512 l_msg_count := l_msg_count - 1;
513 END LOOP;
514 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
515 RAISE Skip_Error;
516 ELSE
517 l_PROCESS_STATUS_mig(j) := 'E';
518 RAISE Process_Next; -- Process the next record
519 END IF;
520 END IF; -- Disabled_flag check
521 -- Check the existence of Transaction
522 l_exists := 'N';
523 IF l_src_txn_tbl.count > 0 THEN
524 FOR src_txn IN l_src_txn_tbl.FIRST .. l_src_txn_tbl.LAST LOOP
525 IF l_src_txn_tbl(src_txn).src_txn_id = l_SRC_TRANSACTION_ID_mig(j) AND
526 l_src_txn_tbl(src_txn).src_txn_type_id = l_SRC_TRANSACTION_TYPE_ID_mig(j) AND
527 l_src_txn_tbl(src_txn).value_timestamp = l_VALUE_TIMESTAMP_mig(j) THEN
528 l_exists := 'Y';
529 l_txn_rec.transaction_id := l_src_txn_tbl(src_txn).transaction_id;
530 exit;
531 END IF;
532 END LOOP;
533 END IF;
534 --
535 IF l_exists = 'N' THEN
536 Csi_Counter_Readings_Pvt.Create_Reading_Transaction
537 ( p_api_version => 1.0
538 ,p_commit => fnd_api.g_false
539 ,p_init_msg_list => fnd_api.g_true
540 ,p_validation_level => fnd_api.g_valid_level_full
541 ,p_txn_rec => l_txn_rec
542 ,x_return_status => x_return_status
543 ,x_msg_count => x_msg_count
544 ,x_msg_data => x_msg_data
545 );
546 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
547 l_msg_index := 1;
548 FND_MSG_PUB.Count_And_Get
549 (p_count => x_msg_count,
550 p_data => x_msg_data
551 );
552 l_msg_count := x_msg_count;
553 WHILE l_msg_count > 0 LOOP
554 x_msg_data := FND_MSG_PUB.GET
555 ( l_msg_index,
556 FND_API.G_FALSE );
557 l_msg_index := l_msg_index + 1;
558 l_msg_count := l_msg_count - 1;
559 END LOOP;
560 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
561 RAISE Skip_Error;
562 END IF;
563 END IF; -- Transaction exists check
564 --
565 --
566 -- Check whether this is Reset only reading.
567 -- This will decide whether to compute Derive counters or not
568 --
569 IF NVL(l_ctr_rdg_rec.counter_reading,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM AND
570 NVL(l_ctr_rdg_rec.reset_mode,FND_API.G_MISS_CHAR) = 'SOFT' THEN
571 l_reset_only := 'T';
572 ELSE
573 l_reset_only := 'F';
574 END IF;
575 --
576 -- Calling Capture_Counter_Reading PVT
577 Csi_Counter_Readings_Pvt.Capture_Counter_Reading
578 (
579 p_api_version => 1.0
580 ,p_commit => fnd_api.g_false
581 ,p_init_msg_list => fnd_api.g_true
582 ,p_validation_level => fnd_api.g_valid_level_full
583 ,p_txn_rec => l_txn_rec
584 ,p_ctr_rdg_rec => l_ctr_rdg_rec
585 ,x_return_status => x_return_status
586 ,x_msg_count => x_msg_count
587 ,x_msg_data => x_msg_data
588 );
589 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
590 l_msg_index := 1;
591 l_msg_count := x_msg_count;
592 WHILE l_msg_count > 0 LOOP
593 x_msg_data := FND_MSG_PUB.GET
594 ( l_msg_index,
595 FND_API.G_FALSE
596 );
597 l_msg_index := l_msg_index + 1;
598 l_msg_count := l_msg_count - 1;
599 END LOOP;
600 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
601 RAISE Skip_Error;
602 ELSE
603 l_COUNTER_VALUE_ID_mig(j) := l_ctr_rdg_rec.counter_value_id;
604 END IF;
605 --
606 FOR prop_rec IN PROP_RDG_CUR(l_COUNTER_INTERFACE_ID_mig(j)) LOOP
607 l_ctr_prop_rdg_rec := l_temp_ctr_prop_rec;
608 --
609 l_ctr_prop_rdg_rec.counter_prop_value_id := prop_rec.counter_prop_value_id;
610 l_ctr_prop_rdg_rec.counter_value_id := l_ctr_rdg_rec.counter_value_id;
611 l_ctr_prop_rdg_rec.counter_property_id := prop_rec.counter_property_id;
612 l_ctr_prop_rdg_rec.property_value := prop_rec.property_value;
613 l_ctr_prop_rdg_rec.value_timestamp := l_ctr_rdg_rec.value_timestamp;--prop_rec.value_timestamp;
614 l_ctr_prop_rdg_rec.attribute1 := prop_rec.attribute1;
615 l_ctr_prop_rdg_rec.attribute2 := prop_rec.attribute2;
616 l_ctr_prop_rdg_rec.attribute3 := prop_rec.attribute3;
617 l_ctr_prop_rdg_rec.attribute4 := prop_rec.attribute4;
618 l_ctr_prop_rdg_rec.attribute5 := prop_rec.attribute5;
619 l_ctr_prop_rdg_rec.attribute6 := prop_rec.attribute6;
620 l_ctr_prop_rdg_rec.attribute7 := prop_rec.attribute7;
621 l_ctr_prop_rdg_rec.attribute8 := prop_rec.attribute8;
622 l_ctr_prop_rdg_rec.attribute9 := prop_rec.attribute9;
623 l_ctr_prop_rdg_rec.attribute1 := prop_rec.attribute10;
624 l_ctr_prop_rdg_rec.attribute1 := prop_rec.attribute11;
625 l_ctr_prop_rdg_rec.attribute12 := prop_rec.attribute12;
626 l_ctr_prop_rdg_rec.attribute13 := prop_rec.attribute13;
627 l_ctr_prop_rdg_rec.attribute14 := prop_rec.attribute14;
628 l_ctr_prop_rdg_rec.attribute15 := prop_rec.attribute15;
629 l_ctr_prop_rdg_rec.attribute_category := prop_rec.attribute_category;
630 --
631 Csi_Counter_Readings_Pvt.Capture_Ctr_Property_Reading
632 (
633 p_api_version => 1.0
634 ,p_commit => fnd_api.g_false
635 ,p_init_msg_list => fnd_api.g_true
636 ,p_validation_level => fnd_api.g_valid_level_full
637 ,p_ctr_prop_rdg_rec => l_ctr_prop_rdg_rec
638 ,x_return_status => x_return_status
639 ,x_msg_count => x_msg_count
640 ,x_msg_data => x_msg_data
641 );
642 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
643 l_msg_index := 1;
644 l_msg_count := x_msg_count;
645 WHILE l_msg_count > 0 LOOP
646 x_msg_data := FND_MSG_PUB.GET
647 ( l_msg_index,
648 FND_API.G_FALSE
649 );
650 l_msg_index := l_msg_index + 1;
651 l_msg_count := l_msg_count - 1;
652 END LOOP;
653 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
654 RAISE Skip_Error;
655 END IF;
656 END LOOP; -- End of Property interface loop
657 --
658 -- Capture Default Property Reading
659 FOR dflt_rec IN DFLT_PROP_RDG(l_ctr_rdg_rec.counter_id,l_ctr_rdg_rec.counter_value_id)
660 LOOP
661 l_dflt_ctr_prop_rec := l_temp_ctr_prop_rec; -- Initialize
662 --
663 l_dflt_ctr_prop_rec.counter_property_id := dflt_rec.counter_property_id;
664 l_dflt_ctr_prop_rec.property_value := dflt_rec.default_value;
665 l_dflt_ctr_prop_rec.value_timestamp := l_ctr_rdg_rec.value_timestamp;
666 l_dflt_ctr_prop_rec.counter_value_id := l_ctr_rdg_rec.counter_value_id;
667 --
668 Csi_Counter_Readings_Pvt.Capture_Ctr_Property_Reading
669 (
670 p_api_version => 1.0
671 ,p_commit => fnd_api.g_false
672 ,p_init_msg_list => fnd_api.g_true
673 ,p_validation_level => fnd_api.g_valid_level_full
674 ,p_ctr_prop_rdg_rec => l_dflt_ctr_prop_rec
675 ,x_return_status => x_return_status
676 ,x_msg_count => x_msg_count
677 ,x_msg_data => x_msg_data
678 );
679 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
680 l_msg_index := 1;
681 l_msg_count := x_msg_count;
682 WHILE l_msg_count > 0 LOOP
683 x_msg_data := FND_MSG_PUB.GET
684 ( l_msg_index,
685 FND_API.G_FALSE
686 );
687 l_msg_index := l_msg_index + 1;
688 l_msg_count := l_msg_count - 1;
689 END LOOP;
690 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
691 RAISE Skip_Error;
692 END IF;
693 END LOOP; -- Default Property Loop
694 --
695 -- Call compute Derive filters only if the current capture is not a pure reset.
696 -- This is applicable for the Later readings cursor as Reset should be the last reading.
697 IF l_reset_only = 'F' THEN
698 l_derive_ctr_rec := l_temp_ctr_rdg_rec;
699 --
700 l_derive_ctr_rec.counter_value_id := l_ctr_rdg_rec.counter_value_id;
701 l_derive_ctr_rec.counter_id := l_ctr_rdg_rec.counter_id;
702 l_derive_ctr_rec.value_timestamp := l_ctr_rdg_rec.value_timestamp;
703 l_derive_ctr_rec.source_code := l_ctr_rdg_rec.source_code;
704 l_derive_ctr_rec.source_line_id := l_ctr_rdg_rec.source_line_id;
705 --
706 -- Call Compute Derive Counters
707 Csi_Counter_Readings_Pvt.Compute_Derive_Counters
708 (
709 p_api_version => 1.0
710 ,p_commit => fnd_api.g_false
711 ,p_init_msg_list => fnd_api.g_true
712 ,p_validation_level => fnd_api.g_valid_level_full
713 ,p_txn_rec => l_txn_rec
714 ,p_ctr_rdg_rec => l_derive_ctr_rec
715 ,p_mode => 'CREATE'
716 ,x_return_status => x_return_status
717 ,x_msg_count => x_msg_count
718 ,x_msg_data => x_msg_data
719 );
720 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
721 l_msg_index := 1;
722 l_msg_count := x_msg_count;
723 WHILE l_msg_count > 0 LOOP
724 x_msg_data := FND_MSG_PUB.GET
725 ( l_msg_index,
726 FND_API.G_FALSE
727 );
728 l_msg_index := l_msg_index + 1;
729 l_msg_count := l_msg_count - 1;
730 END LOOP;
731 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
732 RAISE Skip_error;
733 END IF;
734 --
735 -- Re-compute Derive comunters for the Later Readings
736 -- No need for SOFT reset as we would not have created it before.
737 FOR later_rdg IN LATER_READINGS_CUR(l_ctr_rdg_rec.counter_id,
738 l_ctr_rdg_rec.value_timestamp)
739 LOOP
740 IF NVL(later_rdg.reset_mode,'$#$') <> 'SOFT' THEN
741 l_derive_ctr_rec := l_temp_ctr_rdg_rec;
742 --
743 l_derive_ctr_rec.counter_value_id := later_rdg.counter_value_id;
744 l_derive_ctr_rec.counter_id := l_ctr_rdg_rec.counter_id;
745 l_derive_ctr_rec.value_timestamp := later_rdg.value_timestamp;
746 --
747 Csi_Counter_Readings_Pvt.Compute_Derive_Counters
748 (
749 p_api_version => 1.0
750 ,p_commit => fnd_api.g_false
751 ,p_init_msg_list => fnd_api.g_true
752 ,p_validation_level => fnd_api.g_valid_level_full
753 ,p_txn_rec => l_txn_rec
754 ,p_ctr_rdg_rec => l_derive_ctr_rec
755 ,p_mode => 'UPDATE'
756 ,x_return_status => x_return_status
757 ,x_msg_count => x_msg_count
758 ,x_msg_data => x_msg_data
759 );
760 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
761 l_msg_index := 1;
762 l_msg_count := x_msg_count;
763 WHILE l_msg_count > 0 LOOP
764 x_msg_data := FND_MSG_PUB.GET
765 ( l_msg_index,
766 FND_API.G_FALSE
767 );
768 l_msg_index := l_msg_index + 1;
769 l_msg_count := l_msg_count - 1;
770 END LOOP;
771 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
772 RAISE Skip_error;
773 END IF;
774 END IF; -- End of Derive counter call check
775 END LOOP;
776 END IF; -- l_reset_only check
777 --
778 -- Call Compute Formula
779 Csi_Counter_Readings_Pvt.Compute_Formula_Counters
780 (
781 p_api_version => 1.0
782 ,p_commit => fnd_api.g_false
783 ,p_init_msg_list => fnd_api.g_true
784 ,p_validation_level => fnd_api.g_valid_level_full
785 ,p_txn_rec => l_txn_rec
786 ,p_ctr_rdg_rec => l_ctr_rdg_rec
787 ,x_return_status => x_return_status
788 ,x_msg_count => x_msg_count
789 ,x_msg_data => x_msg_data
790 );
791 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
792 l_msg_index := 1;
793 l_msg_count := x_msg_count;
794 WHILE l_msg_count > 0 LOOP
795 x_msg_data := FND_MSG_PUB.GET
796 ( l_msg_index,
797 FND_API.G_FALSE
798 );
799 l_msg_index := l_msg_index + 1;
800 l_msg_count := l_msg_count - 1;
801 END LOOP;
802 l_ERROR_TEXT_mig(j) := substr(x_msg_data,1,2000);
803 RAISE Skip_Error;
804 END IF;
805 --
806 -- If Success then cache the Transaction info
807 l_src_txn_count := l_src_txn_count + 1;
808 l_src_txn_tbl(l_src_txn_count).src_txn_id := l_SRC_TRANSACTION_ID_mig(j);
809 l_src_txn_tbl(l_src_txn_count).src_txn_type_id := l_SRC_TRANSACTION_TYPE_ID_mig(j);
810 l_src_txn_tbl(l_src_txn_count).value_timestamp := l_VALUE_TIMESTAMP_mig(j);
811 l_src_txn_tbl(l_src_txn_count).transaction_id := l_txn_rec.transaction_id;
812 --
813 l_PROCESS_STATUS_mig(j) := 'P';
814 Exception
815 When Process_next then
816 null;
817 When Skip_Error then
818 l_PROCESS_STATUS_mig(j) := 'E';
819 Rollback to Execute_Open_Interface;
820 End;
821
822 -- Calling Customer Post-processing Hook
823 IF ( JTF_USR_HKS.Ok_to_execute( G_PKG_NAME, l_api_name, 'A', 'C' ) ) then
824
825 p_ctr_rdg_tbl(1) := l_ctr_rdg_rec;
826 p_ctr_prop_rdg_tbl(1) := l_dflt_ctr_prop_rec;
827
828 CSI_COUNTER_READINGS_CUHK.CAPTURE_COUNTER_READING_POST
829 (
830 p_api_version => p_api_version
831 ,p_commit => p_commit
832 ,p_init_msg_list => p_init_msg_list
833 ,p_validation_level => p_validation_level
834 ,p_txn_tbl => p_txn_tbl
835 ,p_ctr_rdg_tbl => p_ctr_rdg_tbl
836 ,p_ctr_prop_rdg_tbl => p_ctr_prop_rdg_tbl
837 ,x_return_status => x_return_status
838 ,x_msg_count => x_msg_count
839 ,x_msg_data => x_msg_data
840 );
841 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
842 csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_READINGS_CUHK.Capture_Counter_Reading_Post API ');
843 l_msg_index := 1;
844 l_msg_count := x_msg_count;
845 WHILE l_msg_count > 0 LOOP
846 x_msg_data := FND_MSG_PUB.GET
847 ( l_msg_index,
848 FND_API.G_FALSE
849 );
850 csi_ctr_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
851 l_msg_index := l_msg_index + 1;
852 l_msg_count := l_msg_count - 1;
853 END LOOP;
854 RAISE FND_API.G_EXC_ERROR;
855 END IF;
856 END IF;
857
858 -- Calling Vertical Post-processing Hook
859 IF ( JTF_USR_HKS.Ok_to_execute( G_PKG_NAME, l_api_name, 'A', 'V' ) ) then
860 CSI_COUNTER_READINGS_VUHK.CAPTURE_COUNTER_READING_POST
861 (
862 p_api_version => p_api_version
863 ,p_commit => p_commit
864 ,p_init_msg_list => p_init_msg_list
865 ,p_validation_level => p_validation_level
866 ,p_txn_tbl => p_txn_tbl
867 ,p_ctr_rdg_tbl => p_ctr_rdg_tbl
868 ,p_ctr_prop_rdg_tbl => p_ctr_prop_rdg_tbl
869 ,x_return_status => x_return_status
870 ,x_msg_count => x_msg_count
871 ,x_msg_data => x_msg_data
872 );
873 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
874 csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_READINGS_VUHK.Capture_Counter_Reading_Post API ');
875 l_msg_index := 1;
876 l_msg_count := x_msg_count;
877 WHILE l_msg_count > 0 LOOP
878 x_msg_data := FND_MSG_PUB.GET
879 ( l_msg_index,
880 FND_API.G_FALSE
881 );
882 csi_ctr_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
883 l_msg_index := l_msg_index + 1;
884 l_msg_count := l_msg_count - 1;
885 END LOOP;
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888 END IF;
889
890 END LOOP; -- l_counter_interface_id_mig.count Loop
891 --
892 -- Bulk Update error text,process_status and counter_value_id
893 FORALL j IN 1 .. l_counter_interface_id_mig.count
894 UPDATE CSI_CTR_READINGS_INTERFACE
895 SET process_status = l_PROCESS_STATUS_mig(j),
896 counter_value_id = l_COUNTER_VALUE_ID_mig(j),
897 error_text = l_ERROR_TEXT_mig(j)
898 WHERE counter_interface_id = l_counter_interface_id_mig(j);
899 --
900 COMMIT;
901 EXIT WHEN CTR_READINGS_CUR%NOTFOUND;
902 END LOOP;
903 COMMIT;
904 CLOSE CTR_READINGS_CUR;
905 END Execute_Open_Interface;
906 --
907 END CSI_CTR_INTERFACE_PUB;