[Home] [Help]
PACKAGE BODY: APPS.EAM_PM_LAST_SERVICE_PUB
Source
1 PACKAGE BODY EAM_PM_LAST_SERVICE_PUB AS
2 /* $Header: EAMPPLSB.pls 120.2 2005/11/28 04:55:57 kmurthy noship $ */
3 -- Start of comments
4 -- API name : EAM_PM_LAST_SERVICE_PUB
5 -- Type : Public
6 -- Function : insert_pm_last_service, update_pm_last_service
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN : p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional
11 -- Default = FND_API.G_FALSE
12 -- p_commit IN VARCHAR2 Optional
13 -- Default = FND_API.G_FALSE
14 -- p_validation_level IN NUMBER Optional
15 -- Default = FND_API.G_VALID_LEVEL_FULL
16 -- parameter1
17 -- parameter2
18 -- .
19 -- .
20 -- OUT : x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- parameter1
24 -- parameter2
25 -- .
26 -- .
27 -- Version : Current version x.x
28 -- Changed....
29 -- previous version y.y
30 -- Changed....
31 -- .
32 -- .
33 -- previous version 2.0
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes : Note text
38 --
39 -- End of comments
40
41 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_PM_LAST_SERVICE_PUB';
42
43
44
45 procedure validate_pm_last_service(p_meter_id in number, p_actv_assoc_id in number)
46 is
47
48 CURSOR c_val_meter_id(p_meter_id number)
49 IS
50 select 'X' from dual where exists
51 (select COUNTER_ID from CSI_COUNTERS_B where counter_id = p_meter_id
52 union
53 select COUNTER_ID from CSI_COUNTER_TEMPLATE_B where counter_id = p_meter_id) ;
54 --commnetd for perf issues
55 /* SELECT 'X'
56 from eam_counters_v where meter_id = p_meter_id;*/
57
58 CURSOR c_val_act_assoc_id(P_ACTIVITY_ASSOCIATION_ID number)
59 IS
60 SELECT 'X'
61 from mtl_eam_asset_activities where activity_association_id = p_activity_association_id;
62
63 CURSOR c_meter_act_assoc(P_ACTIVITY_ASSOCIATION_ID number, p_meter_id number)
64 IS
65 SELECT 'X' from mtl_eam_asset_activities meaa, CSI_COUNTER_ASSOCIATIONS eam,
66 CSI_COUNTERS_B em where meaa.activity_association_id
67 = p_activity_association_id
68 and meaa.maintenance_object_id = eam.SOURCE_OBJECT_ID and eam.COUNTER_id =
69 em.COUNTER_id and eam.COUNTER_id = p_meter_id and em.used_in_scheduling = 'Y' ;
70 --commnetd for perf issues
71 /* SELECT 'X'
72 from mtl_eam_asset_activities meaa, eam_asset_meters_v eam, eam_counters_v em
73 where meaa.activity_association_id = p_activity_association_id
74 and meaa.maintenance_object_id = eam.maintenance_object_id
75 and eam.meter_id = em.meter_id
76 and eam.meter_id = p_meter_id
77 and em.used_in_scheduling = 'Y';*/
78
79
80 l_dummy VARCHAR2(1);
81 l_return_status VARCHAR2(1);
82 l_msg_count NUMBER;
83 l_msg_data VARCHAR2(30);
84
85 begin
86 if P_meter_id is null or p_actv_assoc_id is null
87 then
88 FND_MESSAGE.SET_NAME('EAM','EAM_IAA_ID_MISSING');
89 fnd_msg_pub.add;
90 RAISE fnd_api.g_exc_error;
91 end if;
92
93 open c_val_meter_id(p_meter_id);
94 fetch c_val_meter_id into l_dummy;
95 IF c_val_meter_id%NOTFOUND
96 THEN
97 FND_MESSAGE.SET_NAME('EAM','EAM_IAA_INV_METER_ID');
98 fnd_msg_pub.add;
99 RAISE fnd_api.g_exc_error;
100 END IF;
101 CLOSE c_val_meter_id;
102
103 open c_val_act_assoc_id(p_actv_assoc_id);
104 fetch c_val_act_assoc_id into l_dummy;
105 IF c_val_act_assoc_id%NOTFOUND
106 THEN
107 FND_MESSAGE.SET_NAME('EAM','EAM_IAA_INV_ACTIVITY_ASSOC_ID');
108 fnd_msg_pub.add;
109 RAISE fnd_api.g_exc_error;
110 END IF;
111 CLOSE c_val_act_assoc_id;
112
113 open c_meter_act_assoc(p_actv_assoc_id, p_meter_id);
114 fetch c_meter_act_assoc into l_dummy;
115 IF c_meter_act_assoc %NOTFOUND
116 THEN
117 FND_MESSAGE.SET_NAME('EAM','EAM_IAA_INV_METER_ID');
118 fnd_msg_pub.add;
119 RAISE fnd_api.g_exc_error;
120 END IF;
121 CLOSE c_meter_act_assoc;
122
123
124
125 end validate_pm_last_service;
126
127 PROCEDURE process_pm_last_service
128 (
129 p_api_version IN NUMBER ,
130 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE ,
131 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
132 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
133 x_return_status OUT NOCOPY VARCHAR2 ,
134 x_msg_count OUT NOCOPY NUMBER ,
135 x_msg_data OUT NOCOPY VARCHAR2 ,
136
137 p_pm_last_service_tbl IN pm_last_service_tbl,
138 p_actv_assoc_id in number
139 )
140 IS
141 l_api_name CONSTANT VARCHAR2(30) := 'APIname';
142 l_api_version CONSTANT NUMBER := 1.0;
143 l_boolean number;
144 l_return_status VARCHAR2(1);
145 l_msg_count NUMBER;
146 l_msg_data VARCHAR2(30);
147 l_dummy VARCHAR2(1);
148
149
150 CURSOR c_check_update(p_meter_id number, p_actv_assoc_id number)
151 IS
152 SELECT 'X'
153 from EAM_PM_LAST_SERVICE where meter_id = p_meter_id and activity_association_id = p_actv_assoc_id;
154
155
156 BEGIN
157 -- Standard Start of API savepoint
158 SAVEPOINT process_pm_last_service;
159 -- Standard call to check for call compatibility.
160 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
161 p_api_version ,
162 l_api_name ,
163 G_PKG_NAME )
164 THEN
165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
166 END IF;
167 -- Initialize message list if p_init_msg_list is set to TRUE.
168 IF FND_API.to_Boolean( p_init_msg_list ) THEN
169 FND_MSG_PUB.initialize;
170 END IF;
171 -- Initialize API return status to success
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 -- API body
175 IF p_pm_last_service_tbl.count >0
176 THEN
177 FOR i IN p_pm_last_service_tbl.FIRST..p_pm_last_service_tbl.LAST
178 LOOP
179
180 validate_pm_last_service(p_pm_last_service_tbl(i).meter_id, p_actv_assoc_id );
181
182 IF (p_pm_last_service_tbl(i).last_service_reading is null /*or
183 p_pm_last_service_tbl(i).prev_service_reading is null*/ ) THEN
184
185 FND_MESSAGE.SET_NAME('EAM','EAM_NULL_METER_READING');
186 fnd_msg_pub.add;
187 RAISE fnd_api.g_exc_error;
188
189 END IF;
190
191 open c_check_update(p_pm_last_service_tbl(i).meter_id,p_actv_assoc_id);
192 fetch c_check_update into l_dummy;
193 if c_check_update%NOTFOUND
194 THEN
195 INSERT INTO EAM_PM_LAST_SERVICE
196 (
197 METER_ID ,
198 ACTIVITY_ASSOCIATION_ID ,
199 LAST_SERVICE_READING ,
200 PREV_SERVICE_READING ,
201 --WIP_ENTITY_ID ,
202
203 CREATED_BY ,
204 CREATION_DATE ,
205 LAST_UPDATE_LOGIN ,
206 LAST_UPDATE_DATE ,
207 LAST_UPDATED_BY
208 )
209 VALUES
210 (
211 p_pm_last_service_tbl(i).METER_ID ,
212 p_actv_assoc_id,
213 p_pm_last_service_tbl(i).LAST_SERVICE_READING ,
214 p_pm_last_service_tbl(i).PREV_SERVICE_READING ,
215 --p_WIP_ENTITY_ID ,
216
217 fnd_global.user_id,
218 sysdate,
219 fnd_global.login_id,
220 sysdate ,
221 fnd_global.user_id
222 );
223
224 ELSE
225 UPDATE EAM_PM_LAST_SERVICE
226 SET
227 METER_ID = p_pm_last_service_tbl(i).METER_ID,
228 ACTIVITY_ASSOCIATION_ID = p_actv_assoc_id,
229 LAST_SERVICE_READING = p_pm_last_service_tbl(i).LAST_SERVICE_READING,
230 PREV_SERVICE_READING = p_pm_last_service_tbl(i).PREV_SERVICE_READING,
231 --WIP_ENTITY_ID = p_WIP_ENTITY_ID ,
232
233 LAST_UPDATE_LOGIN = fnd_global.login_id ,
234 LAST_UPDATE_DATE = sysdate ,
235 LAST_UPDATED_BY = fnd_global.user_id
236 WHERE METER_ID = p_pm_last_service_tbl(i).METER_ID AND
237 ACTIVITY_ASSOCIATION_ID = p_actv_assoc_id;
238 END IF;
239 CLOSE c_check_update;
240
241 END LOOP;
242 END IF;
243
244
245
246 -- End of API body.
247 -- Standard check of p_commit.
248 IF FND_API.To_Boolean( p_commit ) THEN
249 COMMIT WORK;
250 END IF;
251 -- Standard call to get message count and if count is 1, get message info.
252 FND_MSG_PUB.Count_And_Get
253 ( p_count => x_msg_count ,
254 p_data => x_msg_data
255 );
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR THEN
258 ROLLBACK TO process_pm_last_service;
259 x_return_status := FND_API.G_RET_STS_ERROR ;
260 FND_MSG_PUB.Count_And_Get
261 ( p_count => x_msg_count ,
262 p_data => x_msg_data ,
263 P_ENCODED => FND_API.G_FALSE
264 );
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 ROLLBACK TO process_pm_last_service;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
268
269 FND_MSG_PUB.Count_And_Get
270 ( p_count => x_msg_count ,
271 p_data => x_msg_data ,
272 P_ENCODED => FND_API.G_FALSE
273 );
274 WHEN OTHERS THEN
275 ROLLBACK TO process_pm_last_service;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
277 IF FND_MSG_PUB.Check_Msg_Level
278 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
279 THEN
280 FND_MSG_PUB.Add_Exc_Msg
281 ( G_PKG_NAME ,
282 l_api_name
283 );
284 END IF;
285 FND_MSG_PUB.Count_And_Get
286 ( p_count => x_msg_count ,
287 p_data => x_msg_data ,
288 P_ENCODED => FND_API.G_FALSE
289 );
290 END process_pm_last_service;
291
292
293 END EAM_PM_LAST_SERVICE_PUB;