[Home] [Help]
PACKAGE BODY: APPS.EAM_MAINT_ATTRIBUTES_PUB
Source
1 PACKAGE BODY eam_maint_attributes_pub AS
2 /* $Header: EAMPMATB.pls 120.4 2006/04/17 04:31:03 yjhabak noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_maint_attributes_pub';
5
6
7 -- Start of comments
8 -- API name : eam_maint_attributes_pub
9 -- Type : Public.
10 -- Function :
11 -- Pre-reqs : None.
12 -- Parameters :
13 -- IN p_api_version IN NUMBER Required
14 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
15 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
16 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
17 -- parameter1
18 -- parameter2
19 -- .
20 -- .
21 -- OUT x_return_status OUT VARCHAR2(1)
22 -- x_msg_count OUT NUMBER
23 -- x_msg_data OUT VARCHAR2(2000)
24 -- parameter1
25 -- parameter2
26 -- .
27 -- .
28 -- Version Current version x.x
29 -- Changed....
30 -- previous version y.y
31 -- Changed....
32 -- .
33 -- .
34 -- previous version 2.0
35 -- Changed....
36 -- Initial version 1.0
37 --
38 -- Notes : Note text
39 --
40 -- End of comments
41
42 PROCEDURE create_maint_attributes
43 (
44 p_api_version IN NUMBER
45 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
46 ,p_commit IN VARCHAR2 := fnd_api.g_false
47 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
48 ,p_instance_id IN NUMBER
49 ,p_owning_department_id IN NUMBER
50 ,p_accounting_class_code IN VARCHAR2
51 ,p_area_id IN NUMBER
52 ,p_parent_instance_id IN NUMBER
53 ,x_return_status OUT NOCOPY VARCHAR2
54 ,x_msg_count OUT NOCOPY NUMBER
55 ,x_msg_data OUT NOCOPY VARCHAR2
56 ) IS
57
58 l_api_name CONSTANT VARCHAR2(30) := 'create_maint_attributes';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
61
62 l_org_id NUMBER;
63
64 l_sn_1 VARCHAR2(30);
65 l_inv_id_1 NUMBER;
66 l_org_id_1 NUMBER;
67 l_sn_2 VARCHAR2(30);
68 l_inv_id_2 NUMBER;
69 l_org_id_2 NUMBER;
70
71 BEGIN
72 -- Standard Start of API savepoint
73 SAVEPOINT eam_maint_attributes_pub;
74
75 -- Standard call to check for call compatibility.
76 IF NOT fnd_api.compatible_api_call(
77 l_api_version
78 ,p_api_version
79 ,l_api_name
80 ,g_pkg_name) THEN
81 RAISE fnd_api.g_exc_unexpected_error;
82 END IF;
83
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF fnd_api.to_boolean(p_init_msg_list) THEN
86 fnd_msg_pub.initialize;
87 END IF;
88
89 -- Initialize API return status to success
90 x_return_status := fnd_api.g_ret_sts_success;
91
92 -- API body
93
94 -- Call validate procedure for validating the maint attributes
95
96 validate_maint_defaults
97 (
98 p_api_version => 1.0
99 ,p_instance_id => p_instance_id
100 ,p_owning_department_id => p_owning_department_id
101 ,p_accounting_class_code => p_accounting_class_code
102 ,p_area_id => p_area_id
103 ,p_mode => 1
104 ,x_org_id => l_org_id
105 ,x_return_status => x_return_status
106 ,x_msg_count => x_msg_count
107 ,x_msg_data => x_msg_data
108 );
109
110 IF x_return_status <> fnd_api.g_ret_sts_success THEN
111 RAISE fnd_api.g_exc_error;
112 END IF;
113
114 eam_org_maint_defaults_pvt.insert_row
115 (
116 p_api_version => 1.0
117 ,p_object_type => 50
118 ,p_object_id => p_instance_id
119 ,p_organization_id => l_org_id
120 ,p_owning_department_id => p_owning_department_id
121 ,p_accounting_class_code => p_accounting_class_code
122 ,p_area_id => p_area_id
123 ,x_return_status => x_return_status
124 ,x_msg_count => x_msg_count
125 ,x_msg_data => x_msg_data
126 );
127
128 IF x_return_status <> fnd_api.g_ret_sts_success THEN
129 RAISE fnd_api.g_exc_error;
130 END IF;
131
132 -- Find serial number and inventory_item_id to be passed to the geneloagy API
133 IF p_parent_instance_id IS NOT NULL THEN
134 BEGIN
135 SELECT cii.serial_number, cii.inventory_item_id, cii.last_vld_organization_id
136 INTO l_sn_1, l_inv_id_1, l_org_id_1
137 FROM csi_item_instances cii, mtl_system_items msi
138 WHERE cii.instance_id = p_instance_id AND cii.inventory_item_id = msi.inventory_item_id
139 AND cii.last_vld_organization_id = msi.organization_id AND msi.eam_item_type in (1,3)
140 AND msi.serial_number_control_code <> 1;
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 fnd_message.set_name('EAM', 'EAM_INVALID_INSTANCE_ID');
144 fnd_msg_pub.add;
145 RAISE fnd_api.g_exc_error;
146 END;
147 BEGIN
148 SELECT cii.serial_number, cii.inventory_item_id, cii.last_vld_organization_id
149 INTO l_sn_2, l_inv_id_2, l_org_id_2
150 FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
151 WHERE cii.instance_id = p_parent_instance_id AND cii.inventory_item_id = msi.inventory_item_id
152 AND cii.last_vld_organization_id = msi.organization_id AND msi.eam_item_type in (1,3)
153 AND msi.serial_number_control_code <> 1 AND msi.organization_id = mp.organization_id
154 AND mp.maint_organization_id = l_org_id;
155 EXCEPTION
156 WHEN NO_DATA_FOUND THEN
157 fnd_message.set_name('EAM', 'EAM_INVLD_PARENT_INST_ID');
158 fnd_msg_pub.add;
159 RAISE fnd_api.g_exc_error;
160 END;
161
162 wip_eam_genealogy_pvt.create_eam_genealogy
163 (
164 p_api_version => 1.0,
165 p_serial_number => l_sn_1,
166 p_organization_id => l_org_id_1,
167 p_inventory_item_id => l_inv_id_1,
168 p_parent_serial_number => l_sn_2,
169 p_parent_inventory_item_id => l_inv_id_2,
170 p_parent_organization_id => l_org_id_2,
171 p_from_eam => FND_API.G_TRUE,
172 x_return_status => x_return_status,
173 x_msg_count => x_msg_count,
174 x_msg_data => x_msg_data
175 );
176
177 IF x_return_status <> fnd_api.g_ret_sts_success THEN
178 RAISE fnd_api.g_exc_error;
179 END IF;
180
181 END IF;
182
183 -- End of API body.
184
185 -- Standard check of p_commit.
186 IF fnd_api.to_boolean(p_commit) THEN
187 COMMIT WORK;
188 END IF;
189
190 -- Standard call to get message count and if count is 1, get message info.
191 fnd_msg_pub.count_and_get(p_count => x_msg_count
192 ,p_data => x_msg_data);
193 EXCEPTION
194 WHEN fnd_api.g_exc_error THEN
195 ROLLBACK TO eam_maint_attributes_pub;
196 x_return_status := fnd_api.g_ret_sts_error;
197 fnd_msg_pub.count_and_get(p_count => x_msg_count
198 ,p_data => x_msg_data);
199 WHEN fnd_api.g_exc_unexpected_error THEN
200 ROLLBACK TO eam_maint_attributes_pub;
201 x_return_status := fnd_api.g_ret_sts_unexp_error;
202 fnd_msg_pub.count_and_get(p_count => x_msg_count
203 ,p_data => x_msg_data);
204 WHEN OTHERS THEN
205 ROLLBACK TO eam_maint_attributes_pub;
206 x_return_status := fnd_api.g_ret_sts_unexp_error;
207 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
208 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
209 END IF;
210 fnd_msg_pub.count_and_get(p_count => x_msg_count
211 ,p_data => x_msg_data);
212 END create_maint_attributes;
213
214
215 PROCEDURE update_maint_attributes
216 (
217 p_api_version IN NUMBER
218 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
219 ,p_commit IN VARCHAR2 := fnd_api.g_false
220 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
221 ,p_instance_id IN NUMBER
222 ,p_owning_department_id IN NUMBER
223 ,p_accounting_class_code IN VARCHAR2
224 ,p_area_id IN NUMBER
225 ,x_return_status OUT NOCOPY VARCHAR2
226 ,x_msg_count OUT NOCOPY NUMBER
227 ,x_msg_data OUT NOCOPY VARCHAR2
228 ) IS
229
230 l_api_name CONSTANT VARCHAR2(30) := 'update_maint_attributes';
231 l_api_version CONSTANT NUMBER := 1.0;
232 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
233
234 l_org_id NUMBER;
235
236 l_sn_1 NUMBER;
237 l_inv_id_1 NUMBER;
238 l_org_id_1 NUMBER;
239 l_sn_2 NUMBER;
240 l_inv_id_2 NUMBER;
241 l_org_id_2 NUMBER;
242
243 BEGIN
244 -- Standard Start of API savepoint
245 SAVEPOINT eam_maint_attributes_pub;
246
247 -- Standard call to check for call compatibility.
248 IF NOT fnd_api.compatible_api_call(
249 l_api_version
250 ,p_api_version
251 ,l_api_name
252 ,g_pkg_name) THEN
253 RAISE fnd_api.g_exc_unexpected_error;
254 END IF;
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF fnd_api.to_boolean(p_init_msg_list) THEN
258 fnd_msg_pub.initialize;
259 END IF;
260
261 -- Initialize API return status to success
262 x_return_status := fnd_api.g_ret_sts_success;
263
264 -- API body
265
266 -- Call validate procedure for validating the maint attributes
267
268 validate_maint_defaults
269 (
270 p_api_version => 1.0
271 ,p_instance_id => p_instance_id
272 ,p_owning_department_id => p_owning_department_id
273 ,p_accounting_class_code => p_accounting_class_code
274 ,p_area_id => p_area_id
275 ,p_mode => 2
276 ,x_org_id => l_org_id
277 ,x_return_status => x_return_status
278 ,x_msg_count => x_msg_count
279 ,x_msg_data => x_msg_data
280 );
281
282 IF x_return_status <> fnd_api.g_ret_sts_success THEN
283 RAISE fnd_api.g_exc_error;
284 END IF;
285
286 eam_org_maint_defaults_pvt.update_insert_row
287 (
288 p_api_version => 1.0
289 ,p_object_type => 50
290 ,p_object_id => p_instance_id
291 ,p_organization_id => l_org_id
292 ,p_owning_department_id => p_owning_department_id
293 ,p_accounting_class_code => p_accounting_class_code
294 ,p_area_id => p_area_id
295 ,x_return_status => x_return_status
296 ,x_msg_count => x_msg_count
297 ,x_msg_data => x_msg_data
298 );
299
300 IF x_return_status <> fnd_api.g_ret_sts_success THEN
301 RAISE fnd_api.g_exc_error;
302 END IF;
303
304 -- End of API body.
305
306 -- Standard check of p_commit.
307 IF fnd_api.to_boolean(p_commit) THEN
308 COMMIT WORK;
309 END IF;
310
311 -- Standard call to get message count and if count is 1, get message info.
312 fnd_msg_pub.count_and_get(p_count => x_msg_count
313 ,p_data => x_msg_data);
314 EXCEPTION
315 WHEN fnd_api.g_exc_error THEN
316 ROLLBACK TO eam_maint_attributes_pub;
317 x_return_status := fnd_api.g_ret_sts_error;
318 fnd_msg_pub.count_and_get(p_count => x_msg_count
319 ,p_data => x_msg_data);
320 WHEN fnd_api.g_exc_unexpected_error THEN
321 ROLLBACK TO eam_maint_attributes_pub;
322 x_return_status := fnd_api.g_ret_sts_unexp_error;
323 fnd_msg_pub.count_and_get(p_count => x_msg_count
324 ,p_data => x_msg_data);
325 WHEN OTHERS THEN
326 ROLLBACK TO eam_maint_attributes_pub;
327 x_return_status := fnd_api.g_ret_sts_unexp_error;
328 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
329 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
330 END IF;
331 fnd_msg_pub.count_and_get(p_count => x_msg_count
332 ,p_data => x_msg_data);
333 END update_maint_attributes;
334
335
336
337 PROCEDURE validate_maint_defaults
338 (
339 p_api_version IN NUMBER
340 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
341 ,p_commit IN VARCHAR2 := fnd_api.g_false
342 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
343 ,p_instance_id IN NUMBER
344 ,p_owning_department_id IN NUMBER := NULL
345 ,p_accounting_class_code IN VARCHAR2 := NULL
346 ,p_area_id IN NUMBER := NULL
347 ,p_mode IN NUMBER
348 ,x_org_id OUT NOCOPY NUMBER
349 ,x_return_status OUT NOCOPY VARCHAR2
350 ,x_msg_count OUT NOCOPY NUMBER
351 ,x_msg_data OUT NOCOPY VARCHAR2
352 ) IS
353
354 l_api_name CONSTANT VARCHAR2(30) := 'validate_maint_defaults';
355 l_api_version CONSTANT NUMBER := 1.0;
356 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
357 l_count NUMBER;
358
359 BEGIN
360 -- Standard Start of API savepoint
361 SAVEPOINT eam_maint_attributes_pub;
362
363 -- Standard call to check for call compatibility.
364 IF NOT fnd_api.compatible_api_call(
365 l_api_version
366 ,p_api_version
367 ,l_api_name
368 ,g_pkg_name) THEN
369 RAISE fnd_api.g_exc_unexpected_error;
370 END IF;
371
372 -- Initialize message list if p_init_msg_list is set to TRUE.
373 IF fnd_api.to_boolean(p_init_msg_list) THEN
374 fnd_msg_pub.initialize;
375 END IF;
376
377 -- Initialize API return status to success
378 x_return_status := fnd_api.g_ret_sts_success;
379
380 -- API body
381
382 -- Validate instance id
383 SELECT count(instance_id) INTO l_count FROM csi_item_instances cii, mtl_system_items msi
384 WHERE cii.instance_id = p_instance_id AND cii.inventory_item_id = msi.inventory_item_id
385 AND cii.last_vld_organization_id = msi.organization_id AND msi.eam_item_type in (1,3)
386 AND msi.serial_number_control_code <> 1;
387 IF l_count = 0 THEN
388 fnd_message.set_name('EAM', 'EAM_INVALID_INSTANCE_ID');
389 fnd_msg_pub.add;
390 RAISE fnd_api.g_exc_error;
391 END IF;
392
393 -- Select the maintenance organization id
394 SELECT mp.maint_organization_id INTO x_org_id FROM csi_item_instances cii, mtl_parameters mp
395 WHERE cii.instance_id = p_instance_id AND cii.last_vld_organization_id = mp.organization_id;
396
397 IF x_org_id is NULL THEN
398 fnd_message.set_name('EAM', 'EAM_MAINT_ORG_MISSING');
399 fnd_msg_pub.add;
400 RAISE fnd_api.g_exc_error;
401 END IF;
402
403 -- Validate dept id
404 IF p_owning_department_id IS NOT NULL THEN
405 IF ( NOT(p_mode = 2 AND p_owning_department_id = fnd_api.g_miss_num)) THEN
406 SELECT count(department_id) INTO l_count FROM bom_departments
407 WHERE department_id = p_owning_department_id AND organization_id = x_org_id
408 AND nvl(disable_date, sysdate+1) >= sysdate;
409
410 IF l_count = 0 THEN
411 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_OWN_DEPT_ID');
412 fnd_msg_pub.add;
413 RAISE fnd_api.g_exc_error;
414 END IF;
415 END IF;
416 END IF;
417
418 -- Validate WIP Accounting class
419 IF p_accounting_class_code IS NOT NULL THEN
420 IF ( NOT(p_mode = 2 AND p_accounting_class_code = fnd_api.g_miss_char)) THEN
421 SELECT count(*) INTO l_count FROM wip_accounting_classes
422 WHERE class_code = p_accounting_class_code AND class_type = 6
423 AND organization_id = x_org_id;
424
425 IF l_count = 0 THEN
426 fnd_message.set_name('EAM', 'EAM_ABO_INVALID_CLASS_CODE');
427 fnd_msg_pub.add;
428 RAISE fnd_api.g_exc_error;
429 END IF;
430 END IF;
431 END IF;
432
433 -- Validate Area
434 IF p_area_id IS NOT NULL THEN
435 IF ( NOT(p_mode = 2 AND p_area_id = fnd_api.g_miss_num)) THEN
436 SELECT count(*) INTO l_count FROM mtl_eam_locations
437 WHERE location_id = p_area_id AND organization_id = x_org_id
438 AND sysdate BETWEEN nvl(start_date, sysdate-1) AND nvl(end_date, sysdate+1);
439
440 IF l_count = 0 THEN
441 fnd_message.set_name('EAM', 'EAM_LOCATION_ID_INVALID');
442 fnd_msg_pub.add;
443 RAISE fnd_api.g_exc_error;
444 END IF;
445 END IF;
446 END IF;
447
448 -- End of API body.
449
450 -- Standard check of p_commit.
451 IF fnd_api.to_boolean(p_commit) THEN
452 COMMIT WORK;
453 END IF;
454
455 -- Standard call to get message count and if count is 1, get message info.
456 fnd_msg_pub.count_and_get(p_count => x_msg_count
457 ,p_data => x_msg_data);
458 EXCEPTION
459 WHEN fnd_api.g_exc_error THEN
460 ROLLBACK TO eam_maint_attributes_pub;
461 x_return_status := fnd_api.g_ret_sts_error;
462 fnd_msg_pub.count_and_get(p_count => x_msg_count
463 ,p_data => x_msg_data);
464 WHEN fnd_api.g_exc_unexpected_error THEN
465 ROLLBACK TO eam_maint_attributes_pub;
466 x_return_status := fnd_api.g_ret_sts_unexp_error;
467 fnd_msg_pub.count_and_get(p_count => x_msg_count
468 ,p_data => x_msg_data);
469 WHEN OTHERS THEN
470 ROLLBACK TO eam_maint_attributes_pub;
471 x_return_status := fnd_api.g_ret_sts_unexp_error;
472 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
473 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
474 END IF;
475 fnd_msg_pub.count_and_get(p_count => x_msg_count
476 ,p_data => x_msg_data);
477 END validate_maint_defaults;
478
479 END eam_maint_attributes_pub ;