[Home] [Help]
PACKAGE BODY: APPS.EAM_METERASSOC_PUB
Source
1 PACKAGE BODY EAM_MeterAssoc_PUB AS
2 /* $Header: EAMPAMAB.pls 120.12 2006/04/12 23:31:46 sshahid noship $ */
3 /*
4 -- API name : EAM_MeterAssoc_PUB
5 -- Type : Public
6 -- Function : Insert, update and validation of the asset meter association
7 -- Pre-reqs : None.
8 */
9
10 /* for de-bugging */
11 /* g_sr_no number ; */
12 g_object_type VARCHAR2(30) := EAM_CONSTANTS.G_OBJECT_TYPE;
13 --G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_MeterAssoc_PUB';
14
15
16 /*
17 This procedure inserts a record in the eam_asset_meters table
18 -- Parameters :
19 -- IN : P_API_VERSION IN NUMBER REQUIRED
20 -- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
21 -- DEFAULT = FND_API.G_FALSE
22 -- P_COMMIT IN VARCHAR2 OPTIONAL
23 -- DEFAULT = FND_API.G_FALSE
24 -- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
25 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
26 -- p_meter_id in not null number ,
27 -- p_organization_id in not null number ,
28 -- p_asset_group_id in not null number ,
29 -- p_asset_number in varchar2 default null,
30 -- p_maintenance_object_type in number default null,
31 -- p_maintenance_object_id in number default null,
32 --
33 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
34 -- x_msg_count OUT NOCOPY NUMBER
35 -- x_msg_data OUT NOCOPY VARCHAR2 (2000)
36 -- x_new_set_name_id OUT NOCOPY NUMBER
37 -- Version : Current version: 1.0
38 -- Initial version: 1.0
39 */
40
41 PROCEDURE Insert_AssetMeterAssoc
42 (
43 p_api_version IN Number,
44 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
45 p_commit IN VARCHAR2 := FND_API.G_FALSE,
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY Number,
49 x_msg_data OUT NOCOPY VARCHAR2,
50 p_meter_id IN Number,
51 /* The user can supply one of the following two combinations to identify the
52 maintained item / number: the (org_id, inventory_item_id, serial_number)
53 combination or (maintenance_object_type, maintenance_object_id,
54 creation_organization_id) combination. Thus all of these input parameters
55 should be default to null.*/
56 p_organization_id IN NUMBER DEFAULT NULL,
57 p_asset_group_id IN NUMBER DEFAULT NULL,
58 p_asset_number IN VARCHAR2 DEFAULT NULL,
59 p_maintenance_object_type IN NUMBER DEFAULT NULL,
60 p_maintenance_object_id IN NUMBER DEFAULT NULL,
61 p_primary_failure_flag IN VARCHAR2 DEFAULT 'N',
62 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
63 p_ATTRIBUTE1 IN VARCHAR2 default null,
64 p_ATTRIBUTE2 IN VARCHAR2 default null,
65 p_ATTRIBUTE3 IN VARCHAR2 default null,
66 p_ATTRIBUTE4 IN VARCHAR2 default null,
67 p_ATTRIBUTE5 IN VARCHAR2 default null,
68 p_ATTRIBUTE6 IN VARCHAR2 default null,
69 p_ATTRIBUTE7 IN VARCHAR2 default null,
70 p_ATTRIBUTE8 IN VARCHAR2 default null,
71 p_ATTRIBUTE9 IN VARCHAR2 default null,
72 p_ATTRIBUTE10 IN VARCHAR2 default null,
73 p_ATTRIBUTE11 IN VARCHAR2 default null,
74 p_ATTRIBUTE12 IN VARCHAR2 default null,
75 p_ATTRIBUTE13 IN VARCHAR2 default null,
76 p_ATTRIBUTE14 IN VARCHAR2 default null,
77 p_ATTRIBUTE15 IN VARCHAR2 default null,
78 p_start_date_active IN DATE default NULL,
79 p_end_date_active IN DATE default null
80 )
81 IS
82 l_api_name CONSTANT VARCHAR2(30) :='Insert_AssetMeterAssoc';
83 l_api_version CONSTANT NUMBER := 1.0;
84 l_instance_association_id Number;
85 l_maintenance_object_id NUMBER;
86 l_start_date_active DATE;
87 l_ctr_item_association_rec CSI_CTR_DATASTRUCTURES_PUB.ctr_item_associations_rec;
88 l_counter_associations_tbl CSI_CTR_DATASTRUCTURES_PUB.counter_associations_tbl;
89 l_exists_primary_flag varchar2(1);
90 l_required_flag varchar2(1) := 'N';
91
92 l_module varchar2(200);
93 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
94 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
95 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
96 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
97 l_exists NUMBER := 0;
98 l_association_id NUMBER;
99 l_object_version_number NUMBER;
100
101 BEGIN
102 /* Standard Start of API savepoint */
103 SAVEPOINT Insert_AssetMeterAssoc_PUB;
104 IF (l_ulog) THEN
105 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
106 END IF;
107
108 IF (l_plog) THEN
109 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
110 '==================== Entered EAM_MeterAssoc_PUB.Insert_AssetMeterAssoc ====================');
111 END IF;
112 /* Standard call to check for call compatibility. */
113 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
114 p_api_version ,
115 l_api_name ,
116 G_PKG_NAME
117 )
118 THEN
119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120 END IF;
121
122 /* Initialize message list if p_init_msg_list is set to TRUE. */
123 IF FND_API.to_Boolean( p_init_msg_list ) THEN
124 FND_MSG_PUB.initialize;
125 END IF;
126
127 /* Initialize API return status to success */
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 /* API body */
131
132 IF (l_plog) THEN
133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
134 'x_return_status: '||x_return_status);
135 END IF;
136
137 IF (p_start_date_active IS NULL) THEN
138 l_start_date_active := SYSDATE;
139 ELSE
140 l_start_date_active := p_start_date_active;
141 END IF;
142
143 IF (p_maintenance_object_id IS NULL AND p_asset_number IS NULL AND p_asset_group_id IS NOT NULL) THEN
144
145 begin
146 if nvl(p_primary_failure_flag, 'N') = 'Y' then
147
148 select 'Y' into l_exists_primary_flag
149 from dual
150 where exists
151 (select * from csi_ctr_item_associations
152 where inventory_item_id = p_asset_group_id
153 and (end_date_active is null or end_date_active > sysdate)
154 and nvl(primary_failure_flag, 'N') = 'Y');
155
156 if l_exists_primary_flag = 'Y' then
157 FND_MESSAGE.SET_NAME('EAM', 'EAM_PRIMARY_FLAG_EXISTS');
158 FND_MSG_PUB.ADD;
159 RAISE FND_API.G_EXC_ERROR;
160 end if;
161
162 end if;
163 exception when no_data_found then
164 null;
165 end;
166
167
168 l_ctr_item_association_rec.counter_id := p_meter_id;
169 l_ctr_item_association_rec.inventory_item_id := p_asset_group_id;
170 l_ctr_item_association_rec.start_date_active := l_start_date_active;
171 l_ctr_item_association_rec.end_date_active := p_end_date_active;
172 l_ctr_item_association_rec.primary_failure_flag := p_primary_failure_flag;
173 l_ctr_item_association_rec.attribute_category := p_attribute_category;
174 l_ctr_item_association_rec.attribute1 := p_attribute1;
175 l_ctr_item_association_rec.attribute2 := p_attribute2;
176 l_ctr_item_association_rec.attribute3 := p_attribute3;
177 l_ctr_item_association_rec.attribute4 := p_attribute4;
178 l_ctr_item_association_rec.attribute5 := p_attribute5;
179 l_ctr_item_association_rec.attribute6 := p_attribute6;
180 l_ctr_item_association_rec.attribute7 := p_attribute7;
181 l_ctr_item_association_rec.attribute8 := p_attribute8;
182 l_ctr_item_association_rec.attribute9 := p_attribute9;
183 l_ctr_item_association_rec.attribute10 := p_attribute10;
184 l_ctr_item_association_rec.attribute11 := p_attribute11;
185 l_ctr_item_association_rec.attribute12 := p_attribute12;
186 l_ctr_item_association_rec.attribute13 := p_attribute13;
187 l_ctr_item_association_rec.attribute14 := p_attribute14;
188 l_ctr_item_association_rec.attribute15 := p_attribute15;
189 -- l_ctr_item_association_rec.maint_organization_id := p_organization_id;
190
191 IF (l_plog) THEN
192 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
193 '==================== Calling csi_counter_template_pub.create_item_association ===================='
194 ||'x_return_status:'||x_return_status
195 ||' x_msg_count:'||x_msg_count
196 ||'x_msg_data:'||x_msg_data);
197 END IF;
198
199 csi_counter_template_pub.create_item_association(p_api_version,
200 p_commit,
201 p_init_msg_list,
202 p_validation_level,
203 l_ctr_item_association_rec,
204 x_return_status,
205 x_msg_count,
206 x_msg_data);
207 IF (l_plog) THEN
208 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
209 '==================== Returned from csi_counter_template_pub.create_item_association ===================='
210 ||'x_return_status:'||x_return_status
211 ||' x_msg_count:'||x_msg_count
212 ||'x_msg_data:'||x_msg_data);
213 END IF;
214
215 ELSIF (p_maintenance_object_id IS NOT NULL OR p_asset_number IS NOT NULL) THEN
216
217 IF (p_maintenance_object_type = 3 AND p_maintenance_object_id IS NOT NULL) THEN
218 l_maintenance_object_id := p_maintenance_object_id;
219 ELSIF (p_maintenance_object_id IS NULL AND p_asset_number IS NOT NULL) THEN
220 BEGIN
221 SELECT instance_id
222 INTO l_maintenance_object_id
223 FROM csi_item_instances
224 WHERE serial_number = p_asset_number
225 AND inventory_item_id = p_asset_group_id;
226
227 IF (l_plog) THEN
228 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
229 'l_maintenance_object_id:'||l_maintenance_object_id);
230 END IF;
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 raise_error('EAM_NO_ITEM_FOUND');
234 END;
235 ELSE
236 raise_error('EAM_NO_ITEM_FOUND');
237 END IF;
238
239 begin
240 if nvl(p_primary_failure_flag, 'N') = 'Y' then
241
242 select 'Y' into l_exists_primary_flag
243 from dual
244 where exists
245 (select * from csi_counter_associations
246 where source_object_id = l_maintenance_object_id
247 and (end_date_active is null or end_date_active > sysdate)
248 and nvl(primary_failure_flag, 'N') = 'Y');
249
250 if l_exists_primary_flag = 'Y' then
251 FND_MESSAGE.SET_NAME('EAM', 'EAM_PRIMARY_FLAG_EXISTS');
252 FND_MSG_PUB.ADD;
253 RAISE FND_API.G_EXC_ERROR;
254 end if;
255
256 end if;
257 exception when no_data_found then
258 null;
259 end;
260
261 l_counter_associations_tbl(1).counter_id := p_meter_id;
262 l_counter_associations_tbl(1).source_object_code := 'CP';
263 l_counter_associations_tbl(1).source_object_id := l_maintenance_object_id;
264 l_counter_associations_tbl(1).start_date_active := l_start_date_active;
265 l_counter_associations_tbl(1).end_date_active := p_end_date_active;
266 l_counter_associations_tbl(1).primary_failure_flag := p_primary_failure_flag;
267 l_counter_associations_tbl(1).attribute_category := p_attribute_category;
268 l_counter_associations_tbl(1).attribute1 := p_attribute1;
269 l_counter_associations_tbl(1).attribute2 := p_attribute2;
270 l_counter_associations_tbl(1).attribute3 := p_attribute3;
271 l_counter_associations_tbl(1).attribute4 := p_attribute4;
272 l_counter_associations_tbl(1).attribute5 := p_attribute5;
273 l_counter_associations_tbl(1).attribute6 := p_attribute6;
274 l_counter_associations_tbl(1).attribute7 := p_attribute7;
275 l_counter_associations_tbl(1).attribute8 := p_attribute8;
276 l_counter_associations_tbl(1).attribute9 := p_attribute9;
277 l_counter_associations_tbl(1).attribute10 := p_attribute10;
278 l_counter_associations_tbl(1).attribute11 := p_attribute11;
279 l_counter_associations_tbl(1).attribute12 := p_attribute12;
280 l_counter_associations_tbl(1).attribute13 := p_attribute13;
281 l_counter_associations_tbl(1).attribute14 := p_attribute14;
282 l_counter_associations_tbl(1).attribute15 := p_attribute15;
283 l_counter_associations_tbl(1).maint_organization_id := p_organization_id;
284
285 -- Code added to take care of Re-Associations
286 BEGIN
287 select 1 into l_exists
288 from csi_counter_associations
289 where counter_id = p_meter_id AND source_object_id =l_maintenance_object_id;
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 l_exists := 0;
293 END;
294
295 IF (l_plog) THEN
296 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
297 '==================== Calling csi_counter_pub.create_ctr_associations ===================='
298 ||'x_return_status:'||x_return_status
299 ||' x_msg_count:'||x_msg_count
300 ||'x_msg_data:'||x_msg_data);
301 END IF;
302
303 if (l_exists = 1) then
304
305 SELECT object_version_number, instance_association_id
306 INTO l_object_version_number, l_association_id
307 FROM csi_counter_associations
308 WHERE source_object_id=l_maintenance_object_id and counter_id=p_meter_id;
309
310 l_counter_associations_tbl(1).instance_association_id := l_association_id;
311 l_counter_associations_tbl(1).object_version_number := l_object_version_number;
312 l_counter_associations_tbl(1).end_date_active := FND_API.G_MISS_DATE;
313
314 csi_counter_pub.update_ctr_associations(p_api_version,
315 p_commit,
316 p_init_msg_list,
317 p_validation_level,
318 l_counter_associations_tbl,
319 x_return_status,
320 x_msg_count,
321 x_msg_data);
322
323 else
324 csi_counter_pub.create_ctr_associations(p_api_version,
325 p_commit,
326 p_init_msg_list,
327 p_validation_level,
328 l_counter_associations_tbl,
329 x_return_status,
330 x_msg_count,
331 x_msg_data,
332 l_instance_association_id);
333 end if;
334
335 IF (l_plog) THEN
336 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
337 '==================== Returned from csi_counter_pub.create_ctr_associations ===================='
338 ||'x_return_status:'||x_return_status
339 ||' x_msg_count:'||x_msg_count
340 ||'x_msg_data:'||x_msg_data);
341 END IF;
342 ELSE
343 raise_error('EAM_NO_ITEM_FOUND');
344 END IF;
345
346
347 /* Standard check of p_commit. */
348 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
349
350 COMMIT WORK;
351 IF (l_plog) THEN
352 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'Commiting Work');
353 END IF;
354 END IF;
355
356 IF (l_plog) THEN
357 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
358 '==================== Calling FND_MSG_PUB.get ====================');
359 END IF;
360
361 -- Standard call to get message count and if count is 1, get message info.
362 FND_MSG_PUB.get
363 ( p_msg_index_out => x_msg_count ,
364 p_data => x_msg_data
365 );
366
367 IF (l_plog) THEN
368 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
369 '==================== Returned from FND_MSG_PUB.get ====================');
370
371 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
372 '==================== Exiting EAM_MeterAssoc_PUB.Insert_AssetMeterAssoc ====================');
373 END IF;
374
375 EXCEPTION
376 WHEN FND_API.G_EXC_ERROR THEN
377 ROLLBACK TO Insert_AssetMeterAssoc_PUB;
378 x_return_status := FND_API.G_RET_STS_ERROR ;
379 IF (l_plog) THEN
380 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'ROLLBACK TO Insert_AssetMeterAssoc_PUB');
381 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
382 '===================EAM_MeterAssoc_PUB.Update_AssetMeterAssoc: EXPECTED ERROR======='||
383 '==================== Calling FND_MSG_PUB.get ====================');
384 END IF;
385 FND_MSG_PUB.get
386 ( p_msg_index_out => x_msg_count ,
387 p_data => x_msg_data
388 );
389 IF (l_plog) THEN
390 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
391 '==================== Returned from FND_MSG_PUB.get ====================');
392 END IF;
393
394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395 ROLLBACK TO Insert_AssetMeterAssoc_PUB;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
397 IF (l_plog) THEN
398 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'ROLLBACK TO Insert_AssetMeterAssoc_PUB');
399 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
400 '===================EAM_MeterAssoc_PUB.Update_AssetMeterAssoc: UNEXPECTED ERROR======='||
401 '==================== Calling FND_MSG_PUB.get ====================');
402 END IF;
403 FND_MSG_PUB.get
404 ( p_msg_index_out => x_msg_count ,
405 p_data => x_msg_data
406 );
407 IF (l_plog) THEN
408 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
409 '==================== Returned from FND_MSG_PUB.get ====================');
410 END IF;
411
412 WHEN OTHERS THEN
413 ROLLBACK TO Insert_AssetMeterAssoc_PUB;
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
415 IF (l_plog) THEN
416 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'ROLLBACK TO Update_AssetMeterAssoc_PUB');
417 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
418 '===================EAM_MeterAssoc_PUB.Update_AssetMeterAssoc: OTHERS ERROR=======');
419 END IF;
420
421 IF FND_MSG_PUB.Check_Msg_Level
422 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
423 THEN
424 IF (l_plog) THEN
425 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
426 '==================== Calling FND_MSG_PUB.Add_Exc_Msg ====================');
427 END IF;
428 FND_MSG_PUB.Add_Exc_Msg
429 ( G_PKG_NAME ,
430 l_api_name
431 );
432 IF (l_plog) THEN
433 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
434 '==================== Returned from FND_MSG_PUB.Add_Exc_Msg ====================');
435 END IF;
436
437 END IF;
438
439 IF (l_plog) THEN
440 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
441 '==================== Calling FND_MSG_PUB.get ====================');
442 END IF;
443
444 FND_MSG_PUB.get
445 ( p_msg_index_out => x_msg_count ,
446 p_data => x_msg_data
447 );
448 IF (l_plog) THEN
449 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
450 '==================== Returned from FND_MSG_PUB.get ====================');
451 END IF;
452
453 END Insert_AssetMeterAssoc;
454
455
456 /*
457 This procedure updates a record in the eam_asset_meters table
458 -- Parameters :
459 -- IN : p_api_version IN NUMBER REQUIRED
460 -- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
461 -- DEFAULT = FND_API.G_FALSE
462 -- P_COMMIT IN VARCHAR2 OPTIONAL
463 -- DEFAULT = FND_API.G_FALSE
464 -- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
465 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
466 -- p_meter_id in not null number ,
467 -- p_organization_id in not null number
468 --
469 --
470 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
471 -- x_msg_count OUT NOCOPY NUMBER
472 -- x_msg_data OUT NOCOPY VARCHAR2 (2000)
473 -- Version : Current version: 1.0
474 -- Initial version: 1.0
475 --
476 */
477 PROCEDURE Update_AssetMeterAssoc
478 (
479 p_api_version IN Number,
480 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
481 p_commit IN VARCHAR2 := FND_API.G_FALSE,
482 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY Number,
485 x_msg_data OUT NOCOPY VARCHAR2,
486 p_association_id IN Number,
487 p_primary_failure_flag IN VARCHAR2 DEFAULT 'N',
488 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
489 p_ATTRIBUTE1 IN VARCHAR2 default null,
490 p_ATTRIBUTE2 IN VARCHAR2 default null,
491 p_ATTRIBUTE3 IN VARCHAR2 default null,
492 p_ATTRIBUTE4 IN VARCHAR2 default null,
493 p_ATTRIBUTE5 IN VARCHAR2 default null,
494 p_ATTRIBUTE6 IN VARCHAR2 default null,
495 p_ATTRIBUTE7 IN VARCHAR2 default null,
496 p_ATTRIBUTE8 IN VARCHAR2 default null,
497 p_ATTRIBUTE9 IN VARCHAR2 default null,
498 p_ATTRIBUTE10 IN VARCHAR2 default null,
499 p_ATTRIBUTE11 IN VARCHAR2 default null,
500 p_ATTRIBUTE12 IN VARCHAR2 default null,
501 p_ATTRIBUTE13 IN VARCHAR2 default null,
502 p_ATTRIBUTE14 IN VARCHAR2 default null,
503 p_ATTRIBUTE15 IN VARCHAR2 default null,
504 p_end_date_active IN DATE DEFAULT NULL,
505 p_tmpl_flag IN VARCHAR2 DEFAULT 'N'
506 )
507 IS
508 p_creation_organization_id NUMBER;
509 l_api_name CONSTANT VARCHAR2(30) :='update asset meter';
510 l_api_version CONSTANT NUMBER := 1.0;
511 l_validated boolean;
512 l_ctr_item_association_rec CSI_CTR_DATASTRUCTURES_PUB.ctr_item_associations_rec;
513 l_counter_associations_tbl CSI_CTR_DATASTRUCTURES_PUB.counter_associations_tbl;
514 l_start_date_active DATE;
515 l_object_version_number Number;
516 l_asset_group_id Number;
517 l_maint_object_id Number;
518 l_exists_primary_flag varchar2(1);
519
520 l_module varchar2(200);
521 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
522 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
523 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
524 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
525 BEGIN
526 /* Standard Start of API savepoint */
527 SAVEPOINT Update_AssetMeterAssoc_PUB;
528 IF (l_ulog) THEN
529 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
530 END IF;
531
532 IF (l_plog) THEN
533 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
534 '==================== Entered EAM_MeterAssoc_PUB.Update_AssetMeterAssoc ====================');
535 END IF;
536
537 /* Standard call to check for call compatibility. */
538 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
539 p_api_version ,
540 l_api_name ,
541 G_PKG_NAME
542 )
543 THEN
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 END IF;
546
547 /* Initialize message list if p_init_msg_list is set to TRUE. */
548 IF FND_API.to_Boolean( p_init_msg_list ) THEN
549 FND_MSG_PUB.initialize;
550 END IF;
551
552 /* Initialize API return status to success */
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554 IF (l_plog) THEN
555 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
556 'x_return_status:'||x_return_status);
557 END IF;
558 /* API body */
559
560 IF p_tmpl_flag = 'Y' THEN
561
562 SELECT ccia.object_version_number, inventory_item_id
563 INTO l_object_version_number, l_asset_group_id
564 FROM csi_ctr_item_associations ccia
565 WHERE ccia.ctr_association_id = p_association_id;
566
567 IF (l_plog) THEN
568 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
569 'l_object_version_number:'||l_object_version_number);
570 END IF;
571
572 begin
573 if nvl(p_primary_failure_flag, 'N') = 'Y' then
574
575
576
577 select 'Y' into l_exists_primary_flag
578 from dual
579 where exists
580 (select * from csi_ctr_item_associations
581 where inventory_item_id = l_asset_group_id
582 and (end_date_active is null or end_date_active > sysdate)
583 and ctr_association_id <> p_association_id
584 and nvl(primary_failure_flag, 'N') = 'Y');
585
586
587 if l_exists_primary_flag = 'Y' then
588 FND_MESSAGE.SET_NAME('EAM', 'EAM_PRIMARY_FLAG_EXISTS');
589 FND_MSG_PUB.ADD;
590 RAISE FND_API.G_EXC_ERROR;
591 end if;
592
593 end if;
594 exception when no_data_found then
595 null;
596 end;
597
598
599 l_ctr_item_association_rec.ctr_association_id := p_association_id;
600 l_ctr_item_association_rec.end_date_active := p_end_date_active;
601 l_ctr_item_association_rec.primary_failure_flag := p_primary_failure_flag;
602 l_ctr_item_association_rec.attribute_category := p_attribute_category;
603 l_ctr_item_association_rec.object_version_number := l_object_version_number;
604 l_ctr_item_association_rec.attribute1 := p_attribute1;
605 l_ctr_item_association_rec.attribute2 := p_attribute2;
606 l_ctr_item_association_rec.attribute3 := p_attribute3;
607 l_ctr_item_association_rec.attribute4 := p_attribute4;
608 l_ctr_item_association_rec.attribute5 := p_attribute5;
609 l_ctr_item_association_rec.attribute6 := p_attribute6;
610 l_ctr_item_association_rec.attribute7 := p_attribute7;
611 l_ctr_item_association_rec.attribute8 := p_attribute8;
612 l_ctr_item_association_rec.attribute9 := p_attribute9;
613 l_ctr_item_association_rec.attribute10 := p_attribute10;
614 l_ctr_item_association_rec.attribute11 := p_attribute11;
615 l_ctr_item_association_rec.attribute12 := p_attribute12;
616 l_ctr_item_association_rec.attribute13 := p_attribute13;
617 l_ctr_item_association_rec.attribute14 := p_attribute14;
618 l_ctr_item_association_rec.attribute15 := p_attribute15;
619
620 IF (l_plog) THEN
621 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
622 '==================== Calling csi_counter_template_pub.update_item_association ===================='
623 ||'x_return_status:'||x_return_status
624 ||' x_msg_count:'||x_msg_count
625 ||'x_msg_data:'||x_msg_data);
626 END IF;
627 csi_counter_template_pub.update_item_association(p_api_version,
628 p_commit,
629 p_init_msg_list,
630 p_validation_level,
631 l_ctr_item_association_rec,
632 x_return_status,
633 x_msg_count,
634 x_msg_data);
635 IF (l_plog) THEN
636 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
637 '==================== Returned from csi_counter_template_pub.update_item_association ===================='
638 ||'x_return_status:'||x_return_status
639 ||' x_msg_count:'||x_msg_count
640 ||'x_msg_data:'||x_msg_data);
641 END IF;
642 ELSE
643
644 SELECT cca.object_version_number
645 INTO l_object_version_number
646 FROM csi_counter_associations cca
647 WHERE cca.instance_association_id = p_association_id;
648
649 IF (l_plog) THEN
650 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
651 'l_object_version_number:'||l_object_version_number);
652 END IF;
653
654
655 begin
656 if nvl(p_primary_failure_flag, 'N') = 'Y' then
657
658
659
660 select 'Y' into l_exists_primary_flag
661 from dual
662 where exists
663 (select * from csi_counter_associations
664 where source_object_id = l_maint_object_id
665 and (end_date_active is null or end_date_active > sysdate)
666 and instance_association_id <> p_association_id
667 and nvl(primary_failure_flag, 'N') = 'Y');
668
669
670 if l_exists_primary_flag = 'Y' then
671 FND_MESSAGE.SET_NAME('EAM', 'EAM_PRIMARY_FLAG_EXISTS');
672 FND_MSG_PUB.ADD;
673 RAISE FND_API.G_EXC_ERROR;
674 end if;
675
676 end if;
677 exception when no_data_found then
678 null;
679 end;
680
681 l_counter_associations_tbl(1).instance_association_id := p_association_id;
682 l_counter_associations_tbl(1).end_date_active := p_end_date_active;
683 l_counter_associations_tbl(1).object_version_number := l_object_version_number;
684 l_counter_associations_tbl(1).primary_failure_flag := p_primary_failure_flag;
685 l_counter_associations_tbl(1).attribute_category := p_attribute_category;
686 l_counter_associations_tbl(1).attribute1 := p_attribute1;
687 l_counter_associations_tbl(1).attribute2 := p_attribute2;
688 l_counter_associations_tbl(1).attribute3 := p_attribute3;
689 l_counter_associations_tbl(1).attribute4 := p_attribute4;
690 l_counter_associations_tbl(1).attribute5 := p_attribute5;
691 l_counter_associations_tbl(1).attribute6 := p_attribute6;
692 l_counter_associations_tbl(1).attribute7 := p_attribute7;
693 l_counter_associations_tbl(1).attribute8 := p_attribute8;
694 l_counter_associations_tbl(1).attribute9 := p_attribute9;
695 l_counter_associations_tbl(1).attribute10 := p_attribute10;
696 l_counter_associations_tbl(1).attribute11 := p_attribute11;
697 l_counter_associations_tbl(1).attribute12 := p_attribute12;
698 l_counter_associations_tbl(1).attribute13 := p_attribute13;
699 l_counter_associations_tbl(1).attribute14 := p_attribute14;
700 l_counter_associations_tbl(1).attribute15 := p_attribute15;
701
702 IF (l_plog) THEN
703 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
704 '==================== Calling csi_counter_template_pub.update_ctr_associations ===================='
705 ||'x_return_status:'||x_return_status
706 ||' x_msg_count:'||x_msg_count
707 ||'x_msg_data:'||x_msg_data);
708 END IF;
709 csi_counter_pub.update_ctr_associations(p_api_version,
710 p_commit,
711 p_init_msg_list,
712 p_validation_level,
713 l_counter_associations_tbl,
714 x_return_status,
715 x_msg_count,
716 x_msg_data);
717 IF (l_plog) THEN
718 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
719 '==================== Returned from csi_counter_template_pub.update_ctr_associations ===================='
720 ||'x_return_status:'||x_return_status
721 ||' x_msg_count:'||x_msg_count
722 ||'x_msg_data:'||x_msg_data);
723 END IF;
724
725 END IF;
726
727 /* Standard check of p_commit. */
728 IF FND_API.TO_BOOLEAN( P_COMMIT ) THEN
729 COMMIT WORK;
730 IF (l_plog) THEN
731 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'Commiting Work');
732 END IF;
733 END IF;
734
735 IF (l_plog) THEN
736 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
737 '==================== Calling FND_MSG_PUB.get ====================');
738 END IF;
739
740 -- Standard call to get message count and if count is 1, get message info.
741 FND_MSG_PUB.get
742 ( p_msg_index_out => x_msg_count ,
743 p_data => x_msg_data
744 );
745 IF (l_plog) THEN
746 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
747 '==================== Returned from FND_MSG_PUB.get ====================');
748
749 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
750 '==================== Exiting EAM_MeterAssoc_PUB.Update_AssetMeterAssoc ====================');
751 END IF;
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 ROLLBACK TO Update_AssetMeterAssoc_PUB;
755
756 x_return_status := FND_API.G_RET_STS_ERROR ;
757
758 IF (l_plog) THEN
759 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'ROLLBACK TO Update_AssetMeterAssoc_PUB');
760 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
761 '===================EAM_MeterAssoc_PUB.Update_AssetMeterAssoc: EXPECTED ERROR======='||
762 '==================== Calling FND_MSG_PUB.get ====================');
763 END IF;
764
765 FND_MSG_PUB.get
766 ( p_msg_index_out => x_msg_count ,
767 p_data => x_msg_data
768 );
769
770 IF (l_plog) THEN
771 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
772 '==================== Returned from FND_MSG_PUB.get ====================');
773 END IF;
774
775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776
777 ROLLBACK TO Update_AssetMeterAssoc_PUB;
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
779 IF (l_plog) THEN
780 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'ROLLBACK TO Update_AssetMeterAssoc_PUB');
781 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
782 '===================EAM_MeterAssoc_PUB.Update_AssetMeterAssoc: UNEXPECTED ERROR======='||
783 '==================== Calling FND_MSG_PUB.get ====================');
784 END IF;
785 FND_MSG_PUB.get
786 ( p_msg_index_out => x_msg_count ,
787 p_data => x_msg_data
788 );
789 IF (l_plog) THEN
790 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
791 '==================== Returned from FND_MSG_PUB.get ====================');
792 END IF;
793
794 WHEN OTHERS THEN
795 ROLLBACK TO Update_AssetMeterAssoc_PUB;
796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
797
798 IF (l_plog) THEN
799 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,'ROLLBACK TO Update_AssetMeterAssoc_PUB');
800 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
801 '===================EAM_MeterAssoc_PUB.Update_AssetMeterAssoc: OTHERS ERROR=======');
802 END IF;
803
804 IF FND_MSG_PUB.Check_Msg_Level
805 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
806 THEN
807 IF (l_plog) THEN
808 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
809 '==================== Calling FND_MSG_PUB.Add_Exc_Msg ====================');
810 END IF;
811
812 FND_MSG_PUB.Add_Exc_Msg
813 ( G_PKG_NAME ,
814 l_api_name
815 );
816
817 IF (l_plog) THEN
818 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
819 '==================== Returned from FND_MSG_PUB.Add_Exc_Msg ====================');
820 END IF;
821 END IF;
822 IF (l_plog) THEN
823 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
824 '==================== Calling FND_MSG_PUB.get ====================');
825 END IF;
826 FND_MSG_PUB.get
827 ( p_msg_index_out => x_msg_count ,
828 p_data => x_msg_data
829 );
830 IF (l_plog) THEN
831 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
832 '==================== Returned from FND_MSG_PUB.get ====================');
833 END IF;
834 END Update_AssetMeterAssoc;
835
836 /* private procedure for raising exceptions */
837
838 PROCEDURE RAISE_ERROR (ERROR VARCHAR2)
839 IS
840 l_api_name CONSTANT VARCHAR2(30) :='Insert_AssetMeterAssoc';
841 l_module varchar2(200);
842 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
843 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
844 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
845 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
846
847 BEGIN
848 /* debugging */
849 IF (l_ulog) THEN
850 l_module := 'eam.plsql.'||g_pkg_name|| '.' || l_api_name;
851 END IF;
852
853 IF (l_plog) THEN
854 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
855 '==================== Entered EAM_MeterAssoc_PUB.RAISE_ERROR ====================');
856 END IF;
857 FND_MESSAGE.SET_NAME ('EAM', ERROR);
858 FND_MSG_PUB.ADD;
859
860 IF (l_plog) THEN
861 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
862 '==================== Exiting EAM_MeterAssoc_PUB.RAISE_ERROR ====================');
863 END IF;
864 RAISE FND_API.G_EXC_ERROR;
865
866 END;
867
868
869 END EAM_MeterAssoc_PUB;