[Home] [Help]
PACKAGE BODY: APPS.CSI_TIME_BASED_CTR_ENGINE_PKG
Source
1 PACKAGE BODY CSI_Time_Based_Ctr_Engine_PKG AS
2 /* $Header: csictimb.pls 120.3.12010000.2 2008/10/31 20:53:43 rsinn ship $ */
3
4 --
5 /*******************************
6 * Private program units *
7 *******************************/
8
9 PROCEDURE Increment_Reading
10 (
11 p_capture_date IN DATE,
12 p_ctr_id IN NUMBER,
13 p_ctr_rdg IN NUMBER,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_data OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER
17 ) IS
18
19 l_txn_rec csi_datastructures_pub.transaction_rec;
20 l_ctr_rdg_rec csi_ctr_datastructures_pub.counter_readings_rec;
21 l_msg_index NUMBER;
22 l_msg_count NUMBER;
23 --
24 BEGIN
25 SAVEPOINT Increment_Reading;
26 --
27 fnd_file.put_line(fnd_file.log, 'Inside Increment_Reading...');
28 fnd_file.put_line(fnd_file.log, 'p_ctr_id :'||p_ctr_id);
29 fnd_file.put_line(fnd_file.log, 'p_ctr_rdg :'||p_ctr_rdg);
30 fnd_file.put_line(fnd_file.log, 'p_capture_date :'||to_char(p_capture_date,'DD-MON-YYYY HH24:MI:SS'));
31 --
32 l_txn_rec.source_transaction_date := sysdate;
33 l_txn_rec.transaction_type_id := 89;
34 l_txn_rec.source_header_ref_id := 0;
35 --
36 l_ctr_rdg_rec.counter_id := p_ctr_id;
37 l_ctr_rdg_rec.value_timestamp := p_capture_date;
38 l_ctr_rdg_rec.counter_reading := p_ctr_rdg;
39 --
40 Csi_Counter_Readings_Pvt.Create_Reading_Transaction
41 ( p_api_version => 1.0
42 ,p_commit => fnd_api.g_false
43 ,p_init_msg_list => fnd_api.g_true
44 ,p_validation_level => fnd_api.g_valid_level_full
45 ,p_txn_rec => l_txn_rec
46 ,x_return_status => x_return_status
47 ,x_msg_count => x_msg_count
48 ,x_msg_data => x_msg_data
49 );
50 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
51 fnd_file.put_line(fnd_file.log,'Error from Create_Reading_Transaction...');
52 l_msg_index := 1;
53 FND_MSG_PUB.Count_And_Get
54 (p_count => x_msg_count,
55 p_data => x_msg_data
56 );
57 l_msg_count := x_msg_count;
58 WHILE l_msg_count > 0 LOOP
59 x_msg_data := FND_MSG_PUB.GET
60 ( l_msg_index,
61 FND_API.G_FALSE );
62 fnd_file.put_line(fnd_file.log,'MESSAGE DATA = '||x_msg_data);
63 l_msg_index := l_msg_index + 1;
64 l_msg_count := l_msg_count - 1;
65 END LOOP;
66 RAISE FND_API.G_EXC_ERROR;
67 END IF;
68 fnd_file.put_line(fnd_file.log, 'Inserting Counter Reading...');
69 select CSI_COUNTER_READINGS_S.nextval
70 into l_ctr_rdg_rec.counter_value_id from dual;
71 --
72 CSI_COUNTER_READINGS_PKG.Insert_Row(
73 px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
74 ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
75 ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
76 ,p_COUNTER_READING => l_ctr_rdg_rec.counter_reading
77 ,p_RESET_MODE => NULL
78 ,p_RESET_REASON => NULL
79 ,p_ADJUSTMENT_TYPE => NULL
80 ,p_ADJUSTMENT_READING => NULL
81 ,p_OBJECT_VERSION_NUMBER => 1
82 ,p_LAST_UPDATE_DATE => SYSDATE
83 ,p_LAST_UPDATED_BY => fnd_global.user_id
84 ,p_CREATION_DATE => SYSDATE
85 ,p_CREATED_BY => fnd_global.user_id
86 ,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
87 ,p_ATTRIBUTE1 => NULL
88 ,p_ATTRIBUTE2 => NULL
89 ,p_ATTRIBUTE3 => NULL
90 ,p_ATTRIBUTE4 => NULL
91 ,p_ATTRIBUTE5 => NULL
92 ,p_ATTRIBUTE6 => NULL
93 ,p_ATTRIBUTE7 => NULL
94 ,p_ATTRIBUTE8 => NULL
95 ,p_ATTRIBUTE9 => NULL
96 ,p_ATTRIBUTE10 => NULL
97 ,p_ATTRIBUTE11 => NULL
98 ,p_ATTRIBUTE12 => NULL
99 ,p_ATTRIBUTE13 => NULL
100 ,p_ATTRIBUTE14 => NULL
101 ,p_ATTRIBUTE15 => NULL
102 ,p_ATTRIBUTE16 => NULL
103 ,p_ATTRIBUTE17 => NULL
104 ,p_ATTRIBUTE18 => NULL
105 ,p_ATTRIBUTE19 => NULL
106 ,p_ATTRIBUTE20 => NULL
107 ,p_ATTRIBUTE21 => NULL
108 ,p_ATTRIBUTE22 => NULL
109 ,p_ATTRIBUTE23 => NULL
110 ,p_ATTRIBUTE24 => NULL
111 ,p_ATTRIBUTE25 => NULL
112 ,p_ATTRIBUTE26 => NULL
113 ,p_ATTRIBUTE27 => NULL
114 ,p_ATTRIBUTE28 => NULL
115 ,p_ATTRIBUTE29 => NULL
116 ,p_ATTRIBUTE30 => NULL
117 ,p_ATTRIBUTE_CATEGORY => NULL
118 ,p_MIGRATED_FLAG => 'N'
119 ,p_COMMENTS => NULL
120 ,p_LIFE_TO_DATE_READING => l_ctr_rdg_rec.counter_reading
121 ,p_TRANSACTION_ID => l_txn_rec.transaction_id
122 ,p_AUTOMATIC_ROLLOVER_FLAG => NULL
123 ,p_INCLUDE_TARGET_RESETS => NULL
124 ,p_SOURCE_COUNTER_VALUE_ID => NULL
125 ,p_NET_READING => l_ctr_rdg_rec.counter_reading
126 ,p_DISABLED_FLAG => 'N'
127 ,p_SOURCE_CODE => NULL
128 ,p_SOURCE_LINE_ID => NULL
129 ,p_INITIAL_READING_FLAG => NULL
130 );
131 -- Added for FP Tracking bug 7390758 (base bug 7374316)
132 CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
133 p_api_version => 1.0
134 ,p_commit => fnd_api.g_false
135 ,p_init_msg_list => fnd_api.g_true
136 ,p_validation_level => fnd_api.g_valid_level_full
137 ,p_counter_id => l_ctr_rdg_rec.counter_id
138 ,px_ctr_val_max_seq_no => l_ctr_rdg_rec.counter_value_id
139 ,x_return_status => x_return_status
140 ,x_msg_count => x_msg_count
141 ,x_msg_data => x_msg_data
142 );
143
144 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
145 l_msg_index := 1;
146 l_msg_count := x_msg_count;
147 WHILE l_msg_count > 0 LOOP
148 x_msg_data := FND_MSG_PUB.GET
149 (l_msg_index,
150 FND_API.G_FALSE
151 );
152 csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
153 csi_ctr_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
154 l_msg_index := l_msg_index + 1;
155 l_msg_count := l_msg_count - 1;
156 END LOOP;
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159 -- End add for FP Tracking bug 7390758 (base bug 7374316)
160 --
161 fnd_file.put_line(fnd_file.log, 'Calling OKC Assembler Event...');
162 OKC_CG_UPD_ASMBLR_PVT.Acn_Assemble
163 ( p_api_version => 1.0,
164 x_return_status => x_return_status,
165 x_msg_count => x_msg_count,
166 x_msg_data => x_msg_data,
167 p_counter_id => l_ctr_rdg_rec.counter_id
168 );
169 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
170 fnd_file.put_line(fnd_file.log,'ERROR FROM OKC_CG_UPD_ASMBLR_PVT.Acn_Assemble API ');
171 l_msg_index := 1;
172 l_msg_count := x_msg_count;
173 WHILE l_msg_count > 0 LOOP
174 x_msg_data := FND_MSG_PUB.GET
175 ( l_msg_index,
176 FND_API.G_FALSE
177 );
178 fnd_file.put_line(fnd_file.log,'MESSAGE DATA = '||x_msg_data);
179 l_msg_index := l_msg_index + 1;
180 l_msg_count := l_msg_count - 1;
181 END LOOP;
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184 EXCEPTION
185 WHEN FND_API.G_EXC_ERROR THEN
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 Rollback To Increment_Reading;
188 WHEN OTHERS THEN
189 x_return_status := FND_API.G_RET_STS_ERROR;
190 Rollback To Increment_Reading;
191 fnd_file.put_line(fnd_file.log,'Into when others error in Increment_Reading...');
192 fnd_file.put_line(fnd_file.log,sqlerrm);
193 END Increment_Reading;
194 --
195 /*******************************
196 * Public program units *
197 *******************************/
198
199 --This is the main procedure that will be called as a conc program
200 PROCEDURE Capture_Readings
201 (
202 errbuf OUT NOCOPY VARCHAR2,
203 retcode OUT NOCOPY NUMBER
204 ) IS
205 l_incr_rdg NUMBER; --incremental reading
206 l_new_rdg NUMBER; --incremental reading
207 l_run_time DATE;
208 l_ctr_rdg NUMBER;
209 l_ctr_value_id NUMBER;
210 l_timestamp DATE;
211 x_msg_data VARCHAR2(2000);
212 x_msg_count NUMBER;
213 x_return_status VARCHAR2(1);
214 l_counter NUMBER := 0;
215 l_days NUMBER;
216 l_day_uom_code VARCHAR2(30);
217 --
218 CURSOR CTR_CUR IS
219 SELECT counter_id,creation_date,uom_code
220 FROM CSI_COUNTERS_B ctr
221 WHERE counter_type = 'REGULAR'
222 AND nvl(end_date_active,(sysdate+1)) > sysdate
223 AND nvl(time_based_manual_entry, 'N') = 'N'
224 AND exists (select 'x'
225 from MTL_UNITS_OF_MEASURE_VL uom
226 where upper(uom.uom_class) = 'TIME'
227 and uom.uom_code = ctr.uom_code);
228 --
229 CURSOR RDG_CUR(p_counter_id IN NUMBER) IS
230 SELECT counter_value_id,counter_reading,value_timestamp
231 FROM CSI_COUNTER_READINGS
232 WHERE counter_id = p_counter_id
233 AND nvl(disabled_flag,'N') = 'N'
234 ORDER BY value_timestamp desc;
235 BEGIN
236 l_run_time := sysdate;
237 fnd_file.put_line(fnd_file.log, 'Start Execution : '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
238 --
239 l_day_uom_code := FND_PROFILE.VALUE('DAY_UNIT_OF_MEASURE');
240 --
241 IF l_day_uom_code IS NULL THEN
242 fnd_file.put_line(fnd_file.log,'DAY_UNIT_OF_MEASURE Profile Not Set..');
243 errbuf := 'DAY_UNIT_OF_MEASURE Profile Not Set';
244 RAISE FND_API.G_EXC_ERROR;
245 END IF;
246 --
247 FOR c_ctrs_rec IN CTR_CUR LOOP
248 fnd_file.put_line(fnd_file.log, '------------------------------------------------------');
249 fnd_file.put_line(fnd_file.log, 'Fetched ctr-id '|| to_char(c_ctrs_rec.counter_id));
250 --
251 l_ctr_value_id := NULL;
252 l_ctr_rdg := NULL;
253 l_timestamp := NULL;
254 --
255 OPEN RDG_CUR(c_ctrs_rec.counter_id);
256 FETCH RDG_CUR INTO l_ctr_value_id,l_ctr_rdg,l_timestamp;
257 CLOSE RDG_CUR;
258 --
259 l_days := l_run_time - nvl(l_timestamp,c_ctrs_rec.creation_date);
260 IF l_days = 0 THEN
261 l_incr_rdg := 0;
262 ELSE
263 l_incr_rdg := oks_time_measures_pub.get_target_qty
264 ( p_start_date => nvl(l_timestamp,c_ctrs_rec.creation_date),
265 p_source_qty => l_days,
266 p_source_uom => l_day_uom_code,
267 p_target_uom => c_ctrs_rec.uom_code,
268 p_round_dec => 1
269 );
270 END IF;
271 --
272 IF l_incr_rdg >= 1 THEN
273 IF l_ctr_value_id IS NULL THEN
274 -- This is the first rdg being captured
275 l_new_rdg := l_incr_rdg;
276 ELSE
277 l_new_rdg := l_incr_rdg + l_ctr_rdg;
278 END IF;
279 --
280 Increment_Reading
281 ( p_capture_date => l_run_time,
282 p_ctr_id => c_ctrs_rec.counter_id,
283 p_ctr_rdg => l_new_rdg,
284 x_return_status => x_return_status,
285 x_msg_data => x_msg_data,
286 x_msg_count => x_msg_count
287 );
288 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291 END IF;
292 --
293 l_counter := l_counter + 1;
294 IF l_counter = 500 THEN
295 l_counter := 0;
296 commit;
297 END IF;
298 END LOOP;
299 --
300 commit;
301 --
302 fnd_file.put_line(fnd_file.log, '------------------------------------------------------');
303 fnd_file.put_line(fnd_file.log, 'Finished Execution : '|| to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
304 --
305 -- Return 0 for successful completion, 1 for warnings, 2 for error
306 errbuf := '';
307 retcode := 0;
308 --
309 EXCEPTION
310 WHEN FND_API.G_EXC_ERROR THEN
311 retcode := 2;
312 fnd_file.put_line(fnd_file.log,'Program Aborting....');
313 WHEN OTHERS THEN
314 fnd_file.put_line(fnd_file.log, 'Into when others in Capture_Readings..');
315 fnd_file.put_line(fnd_file.log,sqlerrm);
316 fnd_file.put_line(fnd_file.log,'Program Aborting....');
317 errbuf := sqlerrm;
318 retcode := 2;
319 END Capture_Readings;
320 --
321 END CSI_Time_Based_Ctr_Engine_PKG;