[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_AREAS_PUB
Source
1 PACKAGE BODY EAM_ASSET_AREAS_PUB AS
2 /* $Header: EAMPASAB.pls 120.1 2005/11/25 10:24:24 sshahid noship $ */
3 -- Start of comments
4 -- API name : EAM_ASSET_AREAS_PUB
5 -- Type : Public
6 -- Function : insert_asset_areas, update_asset_areas
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_ASSET_AREAS_PUB';
42
43 /* private procedure for raising exceptions */
44 PROCEDURE RAISE_ERROR (ERROR VARCHAR2)
45 IS
46 BEGIN
47 /* debugging */
48
49 FND_MESSAGE.SET_NAME ('EAM', ERROR);
50 FND_MSG_PUB.ADD;
51 RAISE FND_API.G_EXC_ERROR;
52 END;
53
54 /*
55 functions for validation
56 */
57 --location code should be unique. return success on code not found
58 /*not reqd as validate_location_codes already check for the same*/
59 /*procedure validate_location_codes( p_location_codes IN varchar2, p_organization_id in number,
60 x_return_status OUT VARCHAR2)
61 is
62 l_count number;
63 BEGIN
64 SELECT count(*) INTO l_count
65 FROM MTL_EAM_LOCATIONS
66 WHERE location_codes = p_location_codes
67 and creation_organization_id = p_organization_id;
68
69 if l_count > 0
70 then
71 fnd_message.set_name('EAM', 'EAM_AA_DUP_LOCATION_CODE');
72 fnd_msg_pub.add;
73 RAISE FND_API.G_EXC_ERROR;
74 end if;
75
76 END;
77 */
78
79 PROCEDURE VALIDATE_DATES(
80 P_START_DATE IN DATE ,
81 P_END_DATE IN DATE ,
82 x_return_status OUT NOCOPY VARCHAR2
83 )
84 is
85 BEGIN
86 if p_start_date = null
87 then
88 fnd_message.set_name('EAM', 'EAM_NULL_START_DATE');
89 fnd_msg_pub.add;
90 RAISE FND_API.G_EXC_ERROR;
91 end if;
92
93
94 if (p_end_date is not null and (trunc(p_start_date)>trunc(p_end_date))) then
95 fnd_message.set_name('EAM', 'EAM_INVALID_START_DATE');
96 fnd_msg_pub.add;
97 RAISE FND_API.G_EXC_ERROR;
98 end if;
99
100 /*
101 IF TRUNC(P_START_DATE) > TRUNC(SYSDATE)
102 THEN
103 fnd_message.set_name('EAM', 'EAM_INVALID_START_DATE');
104 fnd_msg_pub.add;
105 RAISE FND_API.G_EXC_ERROR;
106 ELSIF TRUNC(P_START_DATE) > TRUNC(NVL(P_END_DATE,SYSDATE))
107 THEN
108 fnd_message.set_name('EAM', 'EAM_INVALID_START_DATE');
109 fnd_msg_pub.add;
110 RAISE FND_API.G_EXC_ERROR;
111 ELSIF TRUNC(NVL(P_END_DATE, SYSDATE)) > TRUNC(SYSDATE)
112 THEN
113 fnd_message.set_name('EAM', 'EAM_INVALID_END_DATE');
114 fnd_msg_pub.add;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF ;
117 */
118 END;
119
120
121 procedure VALIDATE_ROW_EXISTS(P_LOCATION_CODES IN VARCHAR2 ,
122 P_CREATION_ORGANIZATION_ID IN NUMBER,
123 p_create_flag in boolean)
124 is
125 l_count number;
126 BEGIN
127 SELECT COUNT(*) INTO l_count
128 FROM MTL_EAM_LOCATIONS
129 WHERE LOCATION_CODES = P_LOCATION_CODES AND
130 CREATION_ORGANIZATION_ID = P_CREATION_ORGANIZATION_ID;
131
132 if l_count = 0
133 then
134 if NOT p_create_flag
135 then
136 fnd_message.set_name('EAM', 'EAM_LOCATION_REC_NOT_FOUND');
137 fnd_msg_pub.add;
138 RAISE fnd_api.g_exc_error;
139 END IF;
140 else
141 if p_create_flag
142 then
143 fnd_message.set_name('EAM', 'EAM_LOCATION_REC_EXISTS');
144 fnd_msg_pub.add;
145 RAISE fnd_api.g_exc_error;
146 END IF;
147 end if;
148 END;
149
150
151 PROCEDURE insert_asset_areas
152 ( p_api_version IN NUMBER ,
153 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
154 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
155 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
156
157 x_return_status OUT NOCOPY VARCHAR2 ,
158 x_msg_count OUT NOCOPY NUMBER ,
159 x_msg_data OUT NOCOPY VARCHAR2 ,
160
161 p_location_codes IN varchar2,
162 p_start_date IN date:=null,
163 p_end_date IN date:=null,
164 p_organization_id IN number,
165 p_description IN varchar2:=null,
166 p_creation_organization_id IN number
167 )
168 IS
169 l_api_name CONSTANT VARCHAR2(30) := 'insert_asset_areas';
170 l_api_version CONSTANT NUMBER := 1.0;
171 l_boolean number;
172 l_return_status VARCHAR2(1);
173 l_msg_count NUMBER;
174 l_msg_data VARCHAR2(30);
175 L_LOCATION_ID NUMBER;
176 BEGIN
177 -- Standard Start of API savepoint
178 SAVEPOINT insert_asset_areas;
179 -- Standard call to check for call compatibility.
180 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
181 p_api_version ,
182 l_api_name ,
183 G_PKG_NAME )
184 THEN
185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
186 END IF;
187 -- Initialize message list if p_init_msg_list is set to TRUE.
188 IF FND_API.to_Boolean( p_init_msg_list ) THEN
189 FND_MSG_PUB.initialize;
190 END IF;
191 -- Initialize API return status to success
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193 -- API body
194
195 /* Bug 3668992: effective from date is mandated. */
196 if p_start_date IS NULL then
197 RAISE_ERROR ('EAM_EFFECTIVE_DATE_NULL');
198 end if;
199
200 EAM_COMMON_UTILITIES_PVT.verify_org(
201 p_resp_id => NULL,
202 p_resp_app_id => 401,
203 p_org_id => P_CREATION_ORGANIZATION_ID,
204 x_boolean => l_boolean,
205 x_return_status => l_return_status,
206 x_msg_count => l_msg_count ,
207 x_msg_data => l_msg_data);
208 if l_boolean = 0
209 then
210 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
211 fnd_msg_pub.add;
212 RAISE fnd_api.g_exc_error;
213 end if;
214
215
216 /*not reqd as validate_location_codes already check for the same*/
217 /*validate_location_codes(p_locatIon_codes,
218 p_creation_organization_id,
219 l_return_status);
220 */
221
222 IF p_location_codes IS NULL THEN
223 RAISE_ERROR ('EAM_LOCATION_CODE_NULL');
224 END IF;
225
226 VALIDATE_DATES(
227 P_START_DATE =>P_START_DATE,
228 P_END_DATE =>P_END_DATE,
229 x_return_status=>l_return_status
230 );
231
232 if (p_organization_id is not null and p_creation_organization_id is not null and p_organization_id <> p_creation_organization_id) then
233 fnd_message.set_name('EAM', 'EAM_ORG_ID_INCONSISTENT');
234 fnd_msg_pub.add;
235 RAISE fnd_api.g_exc_error;
236 end if;
237
238 VALIDATE_ROW_EXISTS(P_LOCATION_CODES, P_CREATION_ORGANIZATION_ID , TRUE);
239
240 INSERT INTO MTL_EAM_LOCATIONS
241 (
242 LOCATION_ID,
243 LOCATION_CODES,
244 START_DATE ,
245 END_DATE ,
246 ORGANIZATION_ID,
247 DESCRIPTION ,
248 CREATION_ORGANIZATION_ID,
249
250 CREATED_BY ,
251 CREATION_DATE ,
252 LAST_UPDATE_LOGIN ,
253 LAST_UPDATE_DATE ,
254 LAST_UPDATED_BY
255 )
256 VALUES
257 (
258 WIP_EAM_LOCATIONS_S.NEXTVAL,
259 P_LOCATION_CODES ,
260 P_START_DATE ,
261 P_END_DATE ,
262 P_ORGANIZATION_ID,
263 P_DESCRIPTION ,
264 P_ORGANIZATION_ID,
265
266 fnd_global.user_id,
267 sysdate,
268 fnd_global.login_id,
269 sysdate ,
270 fnd_global.user_id
271 );
272
273 -- End of API body.
274 -- Standard check of p_commit.
275 IF FND_API.To_Boolean( p_commit ) THEN
276 COMMIT WORK;
277 END IF;
278 -- Standard call to get message count and if count is 1, get message info.
279 FND_MSG_PUB.get
280 ( p_msg_index_out => x_msg_count ,
281 p_data => x_msg_data
282 );
283 EXCEPTION
284 WHEN FND_API.G_EXC_ERROR THEN
285 ROLLBACK TO insert_asset_areas;
286 x_return_status := FND_API.G_RET_STS_ERROR ;
287 FND_MSG_PUB.get
288 ( p_msg_index_out => x_msg_count ,
289 p_data => x_msg_data
290 );
291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292 ROLLBACK TO insert_asset_areas;
293 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
294 FND_MSG_PUB.get
295 ( p_msg_index_out => x_msg_count ,
296 p_data => x_msg_data
297 );
298 WHEN OTHERS THEN
299 ROLLBACK TO insert_asset_areas;
300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
301 IF FND_MSG_PUB.Check_Msg_Level
302 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
303 THEN
304 FND_MSG_PUB.Add_Exc_Msg
305 ( G_PKG_NAME ,
306 l_api_name
307 );
308 END IF;
309 FND_MSG_PUB.get
310 ( p_msg_index_out => x_msg_count ,
311 p_data => x_msg_data
312 );
313 END insert_asset_areas;
314
315
316 PROCEDURE update_asset_areas
317 ( p_api_version IN NUMBER ,
318 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
319 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
320 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
321
322 x_return_status OUT NOCOPY VARCHAR2 ,
323 x_msg_count OUT NOCOPY NUMBER ,
324 x_msg_data OUT NOCOPY VARCHAR2 ,
325
326 p_location_id IN number,
327 p_location_codes IN varchar2,
328 p_start_date IN date:=null,
329 p_end_date IN date:=null,
330 p_organization_id IN number,
331 p_description IN varchar2:=null,
332 p_creation_organization_id IN number
333 )
334 IS
335 l_api_name CONSTANT VARCHAR2(30) := 'update_asset_areas';
336 l_api_version CONSTANT NUMBER := 1.0;
337 l_boolean number;
338 l_return_status VARCHAR2(1);
339 l_msg_count NUMBER;
340 l_msg_data VARCHAR2(30);
341 l_count NUMBER;
342
343
344 BEGIN
345 -- Standard Start of API savepoint
346 SAVEPOINT update_asset_areas;
347 -- Standard call to check for call compatibility.
348 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
349 p_api_version ,
350 l_api_name ,
351 G_PKG_NAME )
352 THEN
353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
354 END IF;
355 -- Initialize message list if p_init_msg_list is set to TRUE.
356 IF FND_API.to_Boolean( p_init_msg_list ) THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359 -- Initialize API return status to success
360 x_return_status := FND_API.G_RET_STS_SUCCESS;
361 -- API body
362 /* Bug 3668992: effective from date is mandated. */
363 if p_start_date IS NULL then
364 RAISE_ERROR ('EAM_EFFECTIVE_DATE_NULL');
365 end if;
366 EAM_COMMON_UTILITIES_PVT.verify_org(
367 p_resp_id => NULL,
368 p_resp_app_id => 401,
369 p_org_id => P_CREATION_ORGANIZATION_ID,
370 x_boolean => l_boolean,
371 x_return_status => l_return_status,
372 x_msg_count => l_msg_count ,
373 x_msg_data => l_msg_data);
374 if l_boolean = 0
375 then
376 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_ORG_ID');
377 fnd_msg_pub.add;
378 RAISE fnd_api.g_exc_error;
379 end if;
380
381 VALIDATE_DATES(
382 P_START_DATE =>P_START_DATE,
383 P_END_DATE =>P_END_DATE,
384 x_return_status=>l_return_status
385 );
386
387 IF p_location_id IS NULL THEN
388 RAISE_ERROR ('EAM_LOCATION_ID_NULL');
389 END IF;
390
391 IF p_location_codes IS NULL THEN
392 RAISE_ERROR ('EAM_LOCATION_CODE_NULL');
393 END IF;
394
395 -- Bug # 3518888 : To check if location id exist for given org.
396 --VALIDATE_ROW_EXISTS(P_LOCATION_CODES, P_CREATION_ORGANIZATION_ID , FALSE);
397
398 SELECT COUNT(*) INTO l_count
399 FROM MTL_EAM_LOCATIONS
400 WHERE CREATION_ORGANIZATION_ID = P_CREATION_ORGANIZATION_ID
401 AND LOCATION_ID = P_LOCATION_ID;
402
403 IF(l_count = 0) THEN
404 RAISE_ERROR('EAM_LOCATION_REC_NOT_FOUND');
405 END IF;
406
407 -- To check if new location_code does not exist for other record.
408 SELECT COUNT(*) INTO l_count
409 FROM MTL_EAM_LOCATIONS
410 WHERE CREATION_ORGANIZATION_ID = P_CREATION_ORGANIZATION_ID
411 AND LOCATION_ID <> P_LOCATION_ID
412 AND LOCATION_CODES = P_LOCATION_CODES;
413
417
414 IF(l_count > 0) THEN
415 RAISE_ERROR('EAM_AA_DUP_LOCATION_CODE');
416 END IF;
418 UPDATE MTL_EAM_LOCATIONS
419 SET
420 LOCATION_CODES = P_LOCATION_CODES ,
421 START_DATE = P_START_DATE ,
422 END_DATE = P_END_DATE ,
423 --ORGANIZATION_ID = P_ORGANIZATION_ID , -- not for update
424 DESCRIPTION = P_DESCRIPTION ,
425 --CREATION_ORGANIZATION_ID = P_CREATION_ORGANIZATION_ID, --not to be updated as it is pk
426
427 LAST_UPDATE_LOGIN = fnd_global.login_id ,
428 LAST_UPDATE_DATE = sysdate,
429 LAST_UPDATED_BY = fnd_global.user_id
430 where
431 LOCATION_ID = P_LOCATION_ID;
432
433
434
435
436 -- End of API body.
437 -- Standard check of p_commit.
438 IF FND_API.To_Boolean( p_commit ) THEN
439 COMMIT WORK;
440 END IF;
441 -- Standard call to get message count and if count is 1, get message info.
442 FND_MSG_PUB.get
443 ( p_msg_index_out => x_msg_count ,
444 p_data => x_msg_data
445 );
446 EXCEPTION
447 WHEN FND_API.G_EXC_ERROR THEN
448 ROLLBACK TO update_asset_areas;
449 x_return_status := FND_API.G_RET_STS_ERROR ;
450 FND_MSG_PUB.get
451 ( p_msg_index_out => x_msg_count ,
452 p_data => x_msg_data
453 );
454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
455 ROLLBACK TO update_asset_areas;
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 FND_MSG_PUB.get
458 ( p_msg_index_out => x_msg_count ,
459 p_data => x_msg_data
460 );
461 WHEN OTHERS THEN
462 ROLLBACK TO update_asset_areas;
463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
464 IF FND_MSG_PUB.Check_Msg_Level
465 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
466 THEN
467 FND_MSG_PUB.Add_Exc_Msg
468 ( G_PKG_NAME ,
469 l_api_name
470 );
471 END IF;
472 FND_MSG_PUB.get
473 ( p_msg_index_out => x_msg_count ,
474 p_data => x_msg_data
475 );
476 END update_asset_areas;
477
478
479 END EAM_ASSET_AREAS_PUB;