[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSETNUMBER_PUB
Source
1 PACKAGE BODY EAM_AssetNumber_Pub AS
2 /* $Header: EAMPASNB.pls 120.20 2010/10/18 07:48:51 srkotika ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='EAM_AssetNumber_Pub';
5
6 PROCEDURE Insert_Asset_Number
7 ( p_api_version IN NUMBER ,
8 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
10 p_validation_level IN NUMBER :=
11 FND_API.G_VALID_LEVEL_FULL ,
12 x_return_status OUT NOCOPY VARCHAR2 ,
13 x_msg_count OUT NOCOPY NUMBER ,
14 x_msg_data OUT NOCOPY VARCHAR2 ,
15 x_object_id OUT NOCOPY NUMBER,
16 p_INVENTORY_ITEM_ID IN NUMBER,
17 p_SERIAL_NUMBER IN VARCHAR2,
18 p_INSTANCE_NUMBER IN VARCHAR2,
19 --p_INITIALIZATION_DATE IN DATE:=NULL, -- always use sysdate
20 p_CURRENT_STATUS IN NUMBER:=3,
21 p_DESCRIPTIVE_TEXT IN VARCHAR2:=NULL,
22 p_CURRENT_ORGANIZATION_ID IN NUMBER,
23 p_ATTRIBUTE_CATEGORY IN VARCHAR2:=NULL,
24 p_ATTRIBUTE1 IN VARCHAR2:=NULL,
25 p_ATTRIBUTE2 IN VARCHAR2:=NULL,
26 p_ATTRIBUTE3 IN VARCHAR2:=NULL,
27 p_ATTRIBUTE4 IN VARCHAR2:=NULL,
28 p_ATTRIBUTE5 IN VARCHAR2:=NULL,
29 p_ATTRIBUTE6 IN VARCHAR2:=NULL,
30 p_ATTRIBUTE7 IN VARCHAR2:=NULL,
31 p_ATTRIBUTE8 IN VARCHAR2:=NULL,
32 p_ATTRIBUTE9 IN VARCHAR2:=NULL,
33 p_ATTRIBUTE10 IN VARCHAR2:=NULL,
34 p_ATTRIBUTE11 IN VARCHAR2:=NULL,
35 p_ATTRIBUTE12 IN VARCHAR2:=NULL,
36 p_ATTRIBUTE13 IN VARCHAR2:=NULL,
37 p_ATTRIBUTE14 IN VARCHAR2:=NULL,
38 p_ATTRIBUTE15 IN VARCHAR2:=NULL,
39 P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
40 P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
41 P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
42 P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
43 P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
44 P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
45 P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
46 P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
47 P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
48 P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
49 P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
50 P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
51 P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
52 P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
53 P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
54 --p_STATUS_ID IN NUMBER:=1,
55 --p_PREVIOUS_STATUS IN NUMBER:=NULL,
56 p_WIP_ACCOUNTING_CLASS_CODE IN VARCHAR2:=NULL,
57 p_MAINTAINABLE_FLAG IN VARCHAR2:=NULL,
58 p_OWNING_DEPARTMENT_ID IN NUMBER,
59 p_NETWORK_ASSET_FLAG IN VARCHAR2:=NULL,
60 p_FA_ASSET_ID IN NUMBER:=NULL,
61 p_FA_SYNC_FLAG IN VARCHAR2:=NULL,
62 p_PN_LOCATION_ID IN NUMBER:=NULL,
63 p_EAM_LOCATION_ID IN NUMBER:=NULL,
64 p_ASSET_CRITICALITY_CODE IN VARCHAR2:=NULL,
65 p_CATEGORY_ID IN NUMBER:=NULL,
66 p_PROD_ORGANIZATION_ID IN NUMBER:=NULL,
67 p_EQUIPMENT_ITEM_ID IN NUMBER:=NULL,
71 p_eam_linear_id IN NUMBER:=NULL
68 p_EQP_SERIAL_NUMBER IN VARCHAR2:=NULL,
69 p_EQUIPMENT_GEN_OBJECT_ID IN NUMBER,
70 p_instantiate_flag IN BOOLEAN:=FALSE,
72 ,p_active_start_date DATE
73 ,p_active_end_date DATE
74 ,p_location NUMBER
75 ,p_operational_log_flag VARCHAR2
76 ,p_checkin_status NUMBER
77 ,p_supplier_warranty_exp_date DATE
78 )
79 IS
80 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Asset_Number';
81
82 l_api_version CONSTANT NUMBER := 1.0;
83 l_validate boolean;
84 l_reason_failed varchar2(30);
85 l_gen_object_id number;
86 l_token varchar2(5000);
87 l_return_status varchar2(10);
88 errCode NUMBER;
89 errMsg VARCHAR2(4000);
90 errStmt NUMBER;
91 errCount NUMBER;
92 l_count NUMBER;
93 l_instance_id NUMBER;
94 l_x_return_status VARCHAR2(1);
95 l_x_msg_count NUMBER;
96 l_x_msg_data VARCHAR2(20000);
97 l_eam_item_type NUMBER;
98 l_current_status NUMBER;
99 l_serial_number_control_code NUMBER;
100 l_asset_meaning VARCHAR2(80);
101 l_rebuild_meaning VARCHAR2(80);
102 l_eqp_gen_obj_id NUMBER;
103 BEGIN
104 -- Standard Start of API savepoint
105 SAVEPOINT Insert_Asset_Number_Pub;
106 -- Standard call to check for call compatibility.
107 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
108 p_api_version ,
109 l_api_name ,
110 G_PKG_NAME )
111 THEN
112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
113 END IF;
114 -- Initialize message list if p_init_msg_list is set to TRUE.
115 IF FND_API.to_Boolean( p_init_msg_list ) THEN
116 FND_MSG_PUB.initialize;
117 END IF;
118 -- Initialize API return status to success
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120 -- API body
121 -- Check the item type (Asset Group or Rebuildable)
122 begin
123 select eam_item_type,serial_number_control_code
124 into l_eam_item_type, l_serial_number_control_code
125 from mtl_system_items where inventory_item_id = p_inventory_item_id
126 and organization_id = p_CURRENT_ORGANIZATION_ID;
127 exception
128 when no_data_found then
129 add_error('EAM_GEN_INVALID_ITEM_TYPE');
130 RAISE FND_API.G_EXC_ERROR;
131 end;
132
133 if (l_serial_number_control_code = 1 or l_serial_number_control_code = 6) then
134 add_error('EAM_REB_INVALID_SERIAL_CONTROL');
135 RAISE FND_API.G_EXC_ERROR;
136
137 end if;
138
139 -- select meaning for capital asset
140 select meaning into l_asset_meaning
141 from mfg_lookups
142 where lookup_type = 'MTL_EAM_ASSET_TYPE'
143 and lookup_code=1;
144
145 --select meaning for rebuild asset
146 select meaning into l_rebuild_meaning
147 from mfg_lookups
148 where lookup_type = 'MTL_EAM_ASSET_TYPE'
149 and lookup_code=3;
150
151 -- validate the inventory item
152 l_validate:= eam_common_utilities_pvt.validate_inventory_item_id
153 (p_current_organization_id,
154 p_inventory_item_id,
155 l_eam_item_type);
156 if (not l_validate) then
157 add_error('EAM_ABO_INVALID_INV_ITEM');
158 RAISE FND_API.G_EXC_ERROR;
159 end if;
160
161 -- validate that the serial number does NOT already exist
162 l_validate:=not (eam_common_utilities_pvt.validate_serial_number
163 (p_current_organization_id,
164 p_inventory_item_id,
165 p_serial_number,
166 l_eam_item_type));
167 if (not l_validate) then
168 add_error('EAM_ASSET_NUMBER_EXISTS');
169 RAISE FND_API.G_EXC_ERROR;
170 end if;
171
172 -- Validate the serial uniqueness specified in the MTL Parameters
173 eam_asset_number_pvt.serial_check(
174 p_api_version => 1.0,
175 x_return_status => l_return_status,
176 x_msg_count => errCount,
177 x_msg_data => errMsg,
178 x_errorcode => errCode,
179 x_ser_num_in_item_id => l_validate,
180 p_inventory_item_id => p_inventory_item_id,
181 p_organization_id => p_current_organization_id,
182 p_serial_number => p_serial_number
183 );
184
185 if l_return_status <> 'S' then
186 if (l_validate = TRUE) then
187 fnd_message.set_name('EAM','EAM_SER_UNIQ2');
188 fnd_message.set_token('NAME',p_serial_number);
189 fnd_msg_pub.add;
190 raise FND_API.G_EXC_ERROR;
191 else
192 fnd_message.set_name('EAM','EAM_SER_UNIQ1');
193 fnd_message.set_token('NAME',p_serial_number);
194 fnd_msg_pub.add;
195 raise FND_API.G_EXC_ERROR;
196 end if;
197 end if;
198
199 -- validate that the current status is 3
200 /*l_validate:=(p_current_status=3);
201 if (not l_validate) then
202 add_error('EAM_CURRENT_STATUS');
203 RAISE FND_API.G_EXC_ERROR;
204 end if;
205 */
206
207 -- validate all the other fields
208 l_validate:=validate_fields(
209 p_CURRENT_ORGANIZATION_ID => p_CURRENT_ORGANIZATION_ID,
210 p_INVENTORY_ITEM_ID => p_INVENTORY_ITEM_ID,
211 p_SERIAL_NUMBER => p_SERIAL_NUMBER,
212 p_WIP_ACCOUNTING_CLASS_CODE => p_WIP_ACCOUNTING_CLASS_CODE,
213 p_MAINTAINABLE_FLAG => p_MAINTAINABLE_FLAG ,
214 p_OWNING_DEPARTMENT_ID => p_OWNING_DEPARTMENT_ID,
215 p_NETWORK_ASSET_FLAG => p_NETWORK_ASSET_FLAG,
216 p_FA_ASSET_ID => p_FA_ASSET_ID ,
217 p_PN_LOCATION_ID => p_PN_LOCATION_ID ,
218 p_EAM_LOCATION_ID => p_EAM_LOCATION_ID ,
219 p_ASSET_CRITICALITY_CODE => p_ASSET_CRITICALITY_CODE,
220 p_CATEGORY_ID => p_CATEGORY_ID ,
224 p_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY,
221 p_PROD_ORGANIZATION_ID => p_PROD_ORGANIZATION_ID ,
222 p_EQUIPMENT_ITEM_ID => p_EQUIPMENT_ITEM_ID,
223 p_EQP_SERIAL_NUMBER => p_EQP_SERIAL_NUMBER,
225 p_ATTRIBUTE1 => p_ATTRIBUTE1,
226 p_ATTRIBUTE2 => p_ATTRIBUTE2,
227 p_ATTRIBUTE3 => p_ATTRIBUTE3,
228 p_ATTRIBUTE4 => p_ATTRIBUTE4,
229 p_ATTRIBUTE5 => p_ATTRIBUTE5,
230 p_ATTRIBUTE6 => p_ATTRIBUTE6,
231 p_ATTRIBUTE7 => p_ATTRIBUTE7,
232 p_ATTRIBUTE8 => p_ATTRIBUTE8,
233 p_ATTRIBUTE9 => p_ATTRIBUTE9,
234 p_ATTRIBUTE10 => p_ATTRIBUTE10,
235 p_ATTRIBUTE11 => p_ATTRIBUTE11,
236 p_ATTRIBUTE12 => p_ATTRIBUTE12,
237 p_ATTRIBUTE13 => p_ATTRIBUTE13,
238 p_ATTRIBUTE14 => p_ATTRIBUTE14,
239 p_ATTRIBUTE15 => p_ATTRIBUTE15,
240 p_EAM_LINEAR_ID => p_eam_linear_id,
241 p_equipment_object_id => p_EQUIPMENT_GEN_OBJECT_ID
242 ,p_operational_log_flag => p_operational_log_flag
243 ,p_checkin_status => p_checkin_status
244 ,p_supplier_warranty_exp_date => p_supplier_warranty_exp_date
245 ,x_reason_failed => l_reason_failed ,
246 x_token => l_token
247 );
248
249 if (not l_validate) then
250 FND_MESSAGE.SET_NAME('EAM', l_reason_failed);
251 if (l_reason_failed='EAM_INVALID_DESC_FLEX') then
252 FND_MESSAGE.SET_TOKEN('ERROR_MSG', l_token);
253 elsif (l_reason_failed = 'EAM_REB_NETWORK_INVALID') then
254 FND_MESSAGE.SET_TOKEN('ASSET',l_rebuild_meaning);
255 elsif (l_reason_failed = 'EAM_REB_INVALID_PN_LOC') then
256 FND_MESSAGE.SET_TOKEN('ASSET',l_rebuild_meaning);
257 end if;
258 FND_MSG_PUB.Add;
259 RAISE FND_API.G_EXC_ERROR;
260 end if;
261
262 -- Bug # 4770445 : Need to check if p_eam_linear_id is null or not
263
264 IF (p_eam_linear_id IS NOT NULL) THEN
265 -- Check if eam_linear_id already exists in MSN
266 SELECT count(*) INTO l_count FROM csi_item_instances
267 WHERE linear_location_id = p_eam_linear_id AND ROWNUM = 1;
268
269 IF (l_count > 0) THEN
270 FND_MESSAGE.SET_NAME('EAM', 'EAM_LINEAR_ID_EXISTS_IN_MSN');
271 FND_MSG_PUB.Add;
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274 END IF;
275
276 -- For Bug 9048751
277 IF (p_EQP_SERIAL_NUMBER IS NOT NULL AND p_EQUIPMENT_ITEM_ID IS NOT NULL AND p_PROD_ORGANIZATION_ID IS NOT NULL) THEN
278
279 SELECT gen_object_id INTO l_eqp_gen_obj_id
280 FROM mtl_serial_numbers
281 WHERE serial_number= p_EQP_SERIAL_NUMBER
282 AND inventory_item_id = p_EQUIPMENT_ITEM_ID
283 AND current_organization_id = p_PROD_ORGANIZATION_ID;
284
285 END IF;
286
287 eam_asset_number_pvt.create_asset
288 (
289 P_API_VERSION => P_API_VERSION
290 ,p_init_msg_list => p_init_msg_list
291 ,p_commit => p_commit
292 ,p_validation_level => p_validation_level
293 ,P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID
294 ,P_SERIAL_NUMBER => P_SERIAL_NUMBER
295 ,P_INSTANCE_NUMBER => nvl(p_instance_number,p_serial_number)
296 ,P_INSTANCE_DESCRIPTION => p_descriptive_text
297 ,P_ORGANIZATION_ID => P_CURRENT_ORGANIZATION_ID
298 ,P_CATEGORY_ID => P_CATEGORY_ID
299 ,P_PN_LOCATION_ID => P_PN_LOCATION_ID
300 ,P_FA_ASSET_ID => P_FA_ASSET_ID
301 ,P_FA_SYNC_FLAG => P_FA_SYNC_FLAG
302 ,P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE
303 ,P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG
304 ,P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG
305 ,P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
306 ,P_ATTRIBUTE1 => P_ATTRIBUTE1
307 ,P_ATTRIBUTE2 => P_ATTRIBUTE2
308 ,P_ATTRIBUTE3 => P_ATTRIBUTE3
309 ,P_ATTRIBUTE4 => P_ATTRIBUTE4
310 ,P_ATTRIBUTE5 => P_ATTRIBUTE5
311 ,P_ATTRIBUTE6 => P_ATTRIBUTE6
312 ,P_ATTRIBUTE7 => P_ATTRIBUTE7
313 ,P_ATTRIBUTE8 => P_ATTRIBUTE8
314 ,P_ATTRIBUTE9 => P_ATTRIBUTE9
315 ,P_ATTRIBUTE10 => P_ATTRIBUTE10
316 ,P_ATTRIBUTE11 => P_ATTRIBUTE11
317 ,P_ATTRIBUTE12 => P_ATTRIBUTE12
318 ,P_ATTRIBUTE13 => P_ATTRIBUTE13
319 ,P_ATTRIBUTE14 => P_ATTRIBUTE14
320 ,P_ATTRIBUTE15 => P_ATTRIBUTE15
321 ,P_ATTRIBUTE16 => P_ATTRIBUTE16
322 ,P_ATTRIBUTE17 => P_ATTRIBUTE17
323 ,P_ATTRIBUTE18 => P_ATTRIBUTE18
324 ,P_ATTRIBUTE19 => P_ATTRIBUTE19
325 ,P_ATTRIBUTE20 => P_ATTRIBUTE20
326 ,P_ATTRIBUTE21 => P_ATTRIBUTE21
327 ,P_ATTRIBUTE22 => P_ATTRIBUTE22
328 ,P_ATTRIBUTE23 => P_ATTRIBUTE23
329 ,P_ATTRIBUTE24 => P_ATTRIBUTE24
330 ,P_ATTRIBUTE25 => P_ATTRIBUTE25
331 ,P_ATTRIBUTE26 => P_ATTRIBUTE26
332 ,P_ATTRIBUTE27 => P_ATTRIBUTE27
333 ,P_ATTRIBUTE28 => P_ATTRIBUTE28
334 ,P_ATTRIBUTE29 => P_ATTRIBUTE29
335 ,P_ATTRIBUTE30 => P_ATTRIBUTE30
336 ,P_LAST_UPDATE_DATE => SYSDATE
340 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
337 ,P_LAST_UPDATED_BY => FND_GLOBAL.LOGIN_ID
338 ,P_CREATION_DATE => SYSDATE
339 ,P_CREATED_BY => FND_GLOBAL.USER_ID
341 ,p_active_start_date => SYSDATE
342 ,p_active_end_date => NULL
343 ,p_location => p_location /*bug 10169576*/
344 ,p_linear_location_id => p_eam_linear_id
345 ,p_operational_log_flag => p_operational_log_flag
346 ,p_checkin_status => p_checkin_status
347 ,p_supplier_warranty_exp_date => p_supplier_warranty_exp_date
348 ,p_equipment_gen_object_id => l_eqp_gen_obj_id
349 ,p_owning_department_id => p_owning_department_id
350 ,p_accounting_class_code => p_WIP_ACCOUNTING_CLASS_CODE
351 ,p_area_id => P_EAM_LOCATION_ID
352 ,X_OBJECT_ID => l_instance_id
353 ,X_RETURN_STATUS => l_X_RETURN_STATUS
354 ,X_MSG_COUNT => l_X_MSG_COUNT
355 ,X_MSG_DATA => l_X_MSG_DATA
356 );
357
358
359 /* For Bug-10076982 */
360 x_object_id := l_instance_id;
361
362 -- instantiate
363 if (l_X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS) then
364 RAISE FND_API.G_EXC_ERROR;
365 end if;
366
367 if (p_instantiate_flag = TRUE) then
368 EAM_ObjectInstantiation_Pub.Instantiate_Object
369 (
370 p_api_version => 1.0,
371 P_init_msg_list => null,
372 P_commit => null,
373 P_validation_level => null,
374 x_return_status => l_return_status,
375 x_msg_count => errCount,
376 x_msg_data => errMsg,
377 p_maintenance_object_id => l_instance_id,
378 p_maintenance_object_type => 3
379 );
380
381 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
382 FND_MESSAGE.SET_NAME('EAM', 'EAM_INSTANTIATE_OBJECT_FAILED');
383 FND_MSG_PUB.Add;
384 RAISE FND_API.G_EXC_ERROR;
385 end if;
386 end if;
387
388 -- End of API body.
389 -- Standard check of p_commit.
390 IF FND_API.To_Boolean( p_commit ) THEN
391 COMMIT WORK;
392 END IF;
393 -- Standard call to get message count and if count is 1, get message info.
394 FND_MSG_PUB.Count_And_Get
395 ( p_count => x_msg_count ,
396 p_data => x_msg_data
397 );
398 EXCEPTION
399 WHEN FND_API.G_EXC_ERROR THEN
400 ROLLBACK TO Insert_Asset_Number_Pub;
401 x_return_status := FND_API.G_RET_STS_ERROR ;
402 FND_MSG_PUB.Count_And_Get
403 ( p_count => x_msg_count ,
404 p_data => x_msg_data
405 );
406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
407 ROLLBACK TO Insert_Asset_Number_Pub;
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
409 FND_MSG_PUB.Count_And_Get
410 ( p_count => x_msg_count ,
411 p_data => x_msg_data
412 );
413 WHEN OTHERS THEN
414 ROLLBACK TO Insert_Asset_Number_Pub;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
416 IF FND_MSG_PUB.Check_Msg_Level
417 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
418 THEN
419 FND_MSG_PUB.Add_Exc_Msg
420 ( G_PKG_NAME ,
421 l_api_name
422 );
423 END IF;
424 FND_MSG_PUB.Count_And_Get
425 ( p_count => x_msg_count ,
426 p_data => x_msg_data
427 );
428 END Insert_Asset_Number;
429
430
431
432 PROCEDURE Update_Asset_Number
433 ( p_api_version IN NUMBER ,
434 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
435 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
436 p_validation_level IN NUMBER :=
437 FND_API.G_VALID_LEVEL_FULL ,
438 x_return_status OUT NOCOPY VARCHAR2 ,
439 x_msg_count OUT NOCOPY NUMBER ,
440 x_msg_data OUT NOCOPY VARCHAR2 ,
441 --p_GEN_OBJECT_ID IN NUMBER:=NULL,
442 p_INVENTORY_ITEM_ID IN NUMBER,
443 p_SERIAL_NUMBER IN VARCHAR2,
444 p_INSTANCE_NUMBER IN VARCHAR2,
445 P_INSTANCE_ID IN NUMBER,
446 --p_INITIALIZATION_DATE IN DATE:=NULL,
447 p_CURRENT_STATUS IN NUMBER:=3,
448 p_DESCRIPTIVE_TEXT IN VARCHAR2:=NULL,
449 p_CURRENT_ORGANIZATION_ID IN NUMBER,
450 p_ATTRIBUTE_CATEGORY IN VARCHAR2:=NULL,
451 p_ATTRIBUTE1 IN VARCHAR2:=NULL,
452 p_ATTRIBUTE2 IN VARCHAR2:=NULL,
453 p_ATTRIBUTE3 IN VARCHAR2:=NULL,
454 p_ATTRIBUTE4 IN VARCHAR2:=NULL,
455 p_ATTRIBUTE5 IN VARCHAR2:=NULL,
456 p_ATTRIBUTE6 IN VARCHAR2:=NULL,
457 p_ATTRIBUTE7 IN VARCHAR2:=NULL,
458 p_ATTRIBUTE8 IN VARCHAR2:=NULL,
459 p_ATTRIBUTE9 IN VARCHAR2:=NULL,
460 p_ATTRIBUTE10 IN VARCHAR2:=NULL,
461 p_ATTRIBUTE11 IN VARCHAR2:=NULL,
462 p_ATTRIBUTE12 IN VARCHAR2:=NULL,
463 p_ATTRIBUTE13 IN VARCHAR2:=NULL,
464 p_ATTRIBUTE14 IN VARCHAR2:=NULL,
465 p_ATTRIBUTE15 IN VARCHAR2:=NULL,
466 P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
467 P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
468 P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
469 P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
470 P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
471 P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
472 P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
473 P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
474 P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
475 P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
476 P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
480 P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
477 P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
478 P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
479 P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
481 --p_STATUS_ID IN NUMBER:=1,
482 --p_PREVIOUS_STATUS IN NUMBER:=NULL,
483 p_WIP_ACCOUNTING_CLASS_CODE IN VARCHAR2:=NULL,
484 p_MAINTAINABLE_FLAG IN VARCHAR2:=NULL,
485 p_OWNING_DEPARTMENT_ID IN NUMBER,
486 p_NETWORK_ASSET_FLAG IN VARCHAR2:=NULL,
487 p_FA_ASSET_ID IN NUMBER:=NULL,
488 p_FA_SYNC_FLAG IN VARCHAR2:=NULL,
489 p_PN_LOCATION_ID IN NUMBER:=NULL,
490 p_EAM_LOCATION_ID IN NUMBER:=NULL,
491 p_ASSET_CRITICALITY_CODE IN VARCHAR2:=NULL,
492 p_CATEGORY_ID IN NUMBER:=NULL,
493 p_PROD_ORGANIZATION_ID IN NUMBER:=NULL,
494 p_EQUIPMENT_ITEM_ID IN NUMBER:=NULL,
495 p_EQP_SERIAL_NUMBER IN VARCHAR2:=NULL,
496 p_EAM_LINEAR_ID IN NUMBER:=NULL
497 ,P_LOCATION_TYPE_CODE IN VARCHAR2:=NULL
498 ,P_LOCATION_ID IN NUMBER:=NULL
499 ,P_ACTIVE_END_DATE IN DATE:=NULL
500 ,P_OPERATIONAL_LOG_FLAG IN VARCHAR2
501 ,P_CHECKIN_STATUS IN NUMBER
502 ,P_SUPPLIER_WARRANTY_EXP_DATE IN DATE
503 ,P_EQUIPMENT_GEN_OBJECT_ID IN NUMBER
504 ,P_DISASSOCIATE_FA_FLAG IN VARCHAR2
505 )
506 IS
507 l_api_name CONSTANT VARCHAR2(30) := 'Update_Asset_Number';
508 l_api_version CONSTANT NUMBER := 1.0;
509 l_validate boolean;
510 l_reason_failed varchar2(30);
511 l_old_current_status number;
512 l_token varchar2(5000);
513 l_count NUMBER;
514
515 l_inventory_item_id NUMBER;
516 l_serial_number VARCHAR2(30);
517 l_organization_id NUMBER;
518 l_instance_id NUMBER;
519 l_eam_item_type NUMBER;
520 l_asset_meaning VARCHAR2(80);
521 l_rebuild_meaning VARCHAR2(80);
522 l_eqp_gen_obj_id NUMBER;
523 BEGIN
524 -- Standard Start of API savepoint
525 SAVEPOINT Update_Asset_Number_Pub;
526 -- Standard call to check for call compatibility.
527 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
528 p_api_version ,
529 l_api_name ,
530 G_PKG_NAME )
531 THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534 -- Initialize message list if p_init_msg_list is set to TRUE.
535 IF FND_API.to_Boolean( p_init_msg_list ) THEN
536 FND_MSG_PUB.initialize;
537 END IF;
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540 -- API body
541
542 -- Check the item type (Asset Group or Rebuildable)
543 begin
544 select eam_item_type into l_eam_item_type
545 from mtl_system_items where inventory_item_id = p_inventory_item_id
546 and organization_id = p_CURRENT_ORGANIZATION_ID;
547 exception
548 when no_data_found then
549 add_error('EAM_GEN_INVALID_ITEM_TYPE');
550 RAISE FND_API.G_EXC_ERROR;
551 end;
552
553 -- select meaning for capital asset
554 select meaning into l_asset_meaning
555 from mfg_lookups
556 where lookup_type = 'MTL_EAM_ASSET_TYPE'
557 and lookup_code=1;
558
559 --select meaning for rebuild asset
560 select meaning into l_rebuild_meaning
561 from mfg_lookups
562 where lookup_type = 'MTL_EAM_ASSET_TYPE'
563 and lookup_code=3;
564
565 if (p_instance_id is not null) then
566 select last_vld_organization_id,inventory_item_id,serial_number
567 into l_organization_id,l_inventory_item_id,l_serial_number
568 from csi_item_instances
569 where instance_id = p_instance_id;
570 elsif (p_instance_number is not null) then
571 select last_vld_organization_id,inventory_item_id,serial_number
572 into l_organization_id,l_inventory_item_id,l_serial_number
573 from csi_item_instances
574 where instance_number = p_instance_number;
575 end if;
576
577 if (p_inventory_item_id is not null AND p_serial_number is not null) then
578 select instance_id,last_vld_organization_id
579 into l_instance_id,l_organization_id
580 from csi_item_instances
581 where serial_number = p_serial_number
582 and inventory_item_id = p_inventory_item_id;
583 end if;
584
585 if (l_instance_id = null) then
586 add_error('EAM_ASSET_NUMBER_NOT_EXIST');
587 RAISE FND_API.G_EXC_ERROR;
588 end if;
589
590
591 -- validate all the other fields
592 l_validate:=validate_fields(
593 p_CURRENT_ORGANIZATION_ID => p_CURRENT_ORGANIZATION_ID,
594 p_INVENTORY_ITEM_ID => p_INVENTORY_ITEM_ID,
595 p_SERIAL_NUMBER => p_SERIAL_NUMBER,
596 p_WIP_ACCOUNTING_CLASS_CODE => p_WIP_ACCOUNTING_CLASS_CODE,
597 p_MAINTAINABLE_FLAG => p_MAINTAINABLE_FLAG ,
598 p_OWNING_DEPARTMENT_ID => p_OWNING_DEPARTMENT_ID,
599 p_NETWORK_ASSET_FLAG => p_NETWORK_ASSET_FLAG,
600 p_FA_ASSET_ID => p_FA_ASSET_ID ,
601 p_PN_LOCATION_ID => p_PN_LOCATION_ID ,
602 p_EAM_LOCATION_ID => p_EAM_LOCATION_ID ,
603 p_ASSET_CRITICALITY_CODE => p_ASSET_CRITICALITY_CODE,
604 p_CATEGORY_ID => p_CATEGORY_ID ,
605 p_PROD_ORGANIZATION_ID => p_PROD_ORGANIZATION_ID ,
606 p_EQUIPMENT_ITEM_ID => p_EQUIPMENT_ITEM_ID,
607 p_EQP_SERIAL_NUMBER => p_EQP_SERIAL_NUMBER,
608 p_ATTRIBUTE_CATEGORY => p_ATTRIBUTE_CATEGORY,
609 p_ATTRIBUTE1 => p_ATTRIBUTE1,
610 p_ATTRIBUTE2 => p_ATTRIBUTE2,
611 p_ATTRIBUTE3 => p_ATTRIBUTE3,
612 p_ATTRIBUTE4 => p_ATTRIBUTE4,
613 p_ATTRIBUTE5 => p_ATTRIBUTE5,
614 p_ATTRIBUTE6 => p_ATTRIBUTE6,
615 p_ATTRIBUTE7 => p_ATTRIBUTE7,
619 p_ATTRIBUTE11 => p_ATTRIBUTE11,
616 p_ATTRIBUTE8 => p_ATTRIBUTE8,
617 p_ATTRIBUTE9 => p_ATTRIBUTE9,
618 p_ATTRIBUTE10 => p_ATTRIBUTE10,
620 p_ATTRIBUTE12 => p_ATTRIBUTE12,
621 p_ATTRIBUTE13 => p_ATTRIBUTE13,
622 p_ATTRIBUTE14 => p_ATTRIBUTE14,
623 p_ATTRIBUTE15 => p_ATTRIBUTE15,
624 p_EAM_LINEAR_ID => p_eam_linear_id
625 ,p_equipment_object_id => p_EQUIPMENT_GEN_OBJECT_ID
626 ,p_operational_log_flag => p_operational_log_flag
627 ,p_checkin_status => p_checkin_status
628 ,p_supplier_warranty_exp_date => p_supplier_warranty_exp_date
629 ,x_reason_failed => l_reason_failed ,
630 x_token => l_token
631 );
632
633 if (not l_validate) then
634 --add_error(l_reason_failed);
635 FND_MESSAGE.SET_NAME('EAM', l_reason_failed);
636 if (l_reason_failed='EAM_INVALID_DESC_FLEX') then
637 FND_MESSAGE.SET_TOKEN('ERROR_MSG', l_token);
638 elsif (l_reason_failed = 'EAM_REB_NETWORK_INVALID') then
639 FND_MESSAGE.SET_TOKEN('ASSET',l_rebuild_meaning);
640 elsif (l_reason_failed = 'EAM_REB_INVALID_PN_LOC') then
641 FND_MESSAGE.SET_TOKEN('ASSET',l_rebuild_meaning);
642 end if;
643 FND_MSG_PUB.Add;
644 RAISE FND_API.G_EXC_ERROR;
645 end if;
646
647 -- Bug # 4770445 : Need to check if p_eam_linear_id is null or not
648 IF (p_eam_linear_id IS NOT NULL AND p_eam_linear_id <> fnd_api.g_miss_num) THEN
649 -- Check if eam_linear_id already exists in MSN
650 SELECT count(*) INTO l_count FROM csi_item_instances
651 WHERE linear_location_id = p_eam_linear_id
652 AND instance_id <> l_instance_id AND ROWNUM = 1;
653
654 IF (l_count > 0) THEN
655 FND_MESSAGE.SET_NAME('EAM', 'EAM_LINEAR_ID_EXISTS_IN_MSN');
656 FND_MSG_PUB.Add;
657 RAISE FND_API.G_EXC_ERROR;
658 END IF;
659 END IF;
660
661 -- check if asset is being de-activated
662 if (p_active_end_date is not null or p_active_end_date <> fnd_api.g_miss_date) then
663 begin
664 select current_status
665 into l_old_current_status
666 from mtl_serial_numbers
667 where inventory_item_id = l_inventory_item_id
668 and serial_number = l_serial_number
669 and rownum <= 1;
670
671 exception
672 when no_data_found then
673 null;
674 end;
675
676 if (l_old_current_status = 3) then
677 FND_MESSAGE.SET_NAME('EAM','EAM_ASSET_IN_INVENTORY');
678 FND_MSG_PUB.Add;
679 RAISE FND_API.G_EXC_ERROR;
680 end if;
681 end if;
682
683 -- For Bug 9048751
684 IF (p_EQP_SERIAL_NUMBER IS NOT NULL AND p_EQUIPMENT_ITEM_ID IS NOT NULL AND p_PROD_ORGANIZATION_ID IS NOT NULL) THEN
685
686 SELECT gen_object_id INTO l_eqp_gen_obj_id
687 FROM mtl_serial_numbers
688 WHERE serial_number= p_EQP_SERIAL_NUMBER
689 AND inventory_item_id = p_EQUIPMENT_ITEM_ID
690 AND current_organization_id = p_PROD_ORGANIZATION_ID;
691
692 END IF;
693
694 EAM_ASSET_NUMBER_PVT.update_asset
695 (
696 P_API_VERSION => 1.0
697 ,P_INSTANCE_ID => l_instance_id
698 ,P_INSTANCE_DESCRIPTION => P_DESCRIPTIVE_TEXT
699 ,P_INVENTORY_ITEM_ID => p_inventory_item_id
700 ,P_SERIAL_NUMBER => p_serial_number
701 ,P_ORGANIZATION_ID => l_organization_id
702 ,P_CATEGORY_ID => P_CATEGORY_ID
703 ,P_PN_LOCATION_ID => P_PN_LOCATION_ID
704 ,P_FA_ASSET_ID => P_FA_ASSET_ID
705 ,P_FA_SYNC_FLAG => P_FA_SYNC_FLAG
706 ,P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE
707 ,P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG
708 ,P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG
709 ,P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY
710 ,P_ATTRIBUTE1 => P_ATTRIBUTE1
711 ,P_ATTRIBUTE2 => P_ATTRIBUTE2
712 ,P_ATTRIBUTE3 => P_ATTRIBUTE3
713 ,P_ATTRIBUTE4 => P_ATTRIBUTE4
714 ,P_ATTRIBUTE5 => P_ATTRIBUTE5
715 ,P_ATTRIBUTE6 => P_ATTRIBUTE6
716 ,P_ATTRIBUTE7 => P_ATTRIBUTE7
717 ,P_ATTRIBUTE8 => P_ATTRIBUTE8
718 ,P_ATTRIBUTE9 => P_ATTRIBUTE9
719 ,P_ATTRIBUTE10 => P_ATTRIBUTE10
720 ,P_ATTRIBUTE11 => P_ATTRIBUTE11
721 ,P_ATTRIBUTE12 => P_ATTRIBUTE12
722 ,P_ATTRIBUTE13 => P_ATTRIBUTE13
723 ,P_ATTRIBUTE14 => P_ATTRIBUTE14
724 ,P_ATTRIBUTE15 => P_ATTRIBUTE15
725 ,P_ATTRIBUTE16 => P_ATTRIBUTE16
726 ,P_ATTRIBUTE17 => P_ATTRIBUTE17
727 ,P_ATTRIBUTE18 => P_ATTRIBUTE18
728 ,P_ATTRIBUTE19 => P_ATTRIBUTE19
729 ,P_ATTRIBUTE20 => P_ATTRIBUTE20
730 ,P_ATTRIBUTE21 => P_ATTRIBUTE21
731 ,P_ATTRIBUTE22 => P_ATTRIBUTE22
732 ,P_ATTRIBUTE23 => P_ATTRIBUTE23
733 ,P_ATTRIBUTE24 => P_ATTRIBUTE24
734 ,P_ATTRIBUTE25 => P_ATTRIBUTE25
735 ,P_ATTRIBUTE26 => P_ATTRIBUTE26
736 ,P_ATTRIBUTE27 => P_ATTRIBUTE27
737 ,P_ATTRIBUTE28 => P_ATTRIBUTE28
738 ,P_ATTRIBUTE29 => P_ATTRIBUTE29
739 ,P_ATTRIBUTE30 => P_ATTRIBUTE30
740 ,P_LAST_UPDATE_DATE => SYSDATE
741 ,P_LAST_UPDATED_BY => FND_GLOBAL.LOGIN_ID
742 ,P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
743 ,P_FROM_PUBLIC_API => 'Y'
744 ,P_INSTANCE_NUMBER => P_INSTANCE_NUMBER
745 ,P_LOCATION_TYPE_CODE => P_LOCATION_TYPE_CODE
749 ,p_operational_log_flag => P_OPERATIONAL_LOG_FLAG
746 ,P_LOCATION_ID => P_LOCATION_ID
747 ,p_active_end_date => P_ACTIVE_END_DATE
748 ,p_linear_location_id => P_EAM_LINEAR_ID
750 ,p_checkin_status => P_CHECKIN_STATUS
751 ,p_supplier_warranty_exp_date => P_SUPPLIER_WARRANTY_EXP_DATE
752 ,p_equipment_gen_object_id => l_eqp_gen_obj_id
753 ,p_owning_department_id => p_owning_department_id
754 ,p_accounting_class_code => p_wip_accounting_class_code
755 ,p_area_id => p_eam_location_id
756 ,p_disassociate_fa_flag => p_disassociate_fa_flag
757 ,X_RETURN_STATUS => x_return_status
758 ,X_MSG_COUNT => x_msg_count
759 ,X_MSG_DATA => x_msg_data
760 );
761
762
763 -- End of API body.
764 -- Standard check of p_commit.
765 IF FND_API.To_Boolean( p_commit ) THEN
766 COMMIT WORK;
767 END IF;
768 -- Standard call to get message count and if count is 1, get message info.
769 FND_MSG_PUB.Count_And_Get
770 ( p_count => x_msg_count ,
771 p_data => x_msg_data
772 );
773 EXCEPTION
774 WHEN FND_API.G_EXC_ERROR THEN
775 ROLLBACK TO Update_Asset_Number_Pub;
776 x_return_status := FND_API.G_RET_STS_ERROR ;
777 FND_MSG_PUB.Count_And_Get
778 ( p_count => x_msg_count ,
779 p_data => x_msg_data
780 );
781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
782 ROLLBACK TO Update_Asset_Number_Pub;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
784 FND_MSG_PUB.Count_And_Get
785 ( p_count => x_msg_count ,
786 p_data => x_msg_data
787 );
788 WHEN OTHERS THEN
789 ROLLBACK TO Update_Asset_Number_Pub;
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
791 IF FND_MSG_PUB.Check_Msg_Level
792 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
793 THEN
794 FND_MSG_PUB.Add_Exc_Msg
795 ( G_PKG_NAME ,
796 l_api_name
797 );
798 END IF;
799 FND_MSG_PUB.Count_And_Get
800 ( p_count => x_msg_count ,
801 p_data => x_msg_data
802 );
803 END Update_Asset_Number;
804
805
806 function validate_fields
807 (
808 p_CURRENT_ORGANIZATION_ID IN number,
809 p_INVENTORY_ITEM_ID IN number,
810 p_SERIAL_NUMBER IN varchar2,
811 p_WIP_ACCOUNTING_CLASS_CODE IN VARCHAR2:=NULL,
812 p_MAINTAINABLE_FLAG IN VARCHAR2:=NULL,
813 p_OWNING_DEPARTMENT_ID IN NUMBER,
814 p_NETWORK_ASSET_FLAG IN VARCHAR2:=NULL,
815 p_FA_ASSET_ID IN NUMBER:=NULL,
816 p_PN_LOCATION_ID IN NUMBER:=NULL,
817 p_EAM_LOCATION_ID IN NUMBER:=NULL,
818 p_ASSET_CRITICALITY_CODE IN VARCHAR2:=NULL,
819 p_CATEGORY_ID IN NUMBER:=NULL,
820 p_PROD_ORGANIZATION_ID IN NUMBER:=NULL,
821 p_EQUIPMENT_ITEM_ID IN NUMBER:=NULL,
822 p_EQP_SERIAL_NUMBER IN VARCHAR2:=NULL,
823 p_ATTRIBUTE_CATEGORY IN VARCHAR2:=NULL,
824 p_ATTRIBUTE1 IN VARCHAR2:=NULL,
825 p_ATTRIBUTE2 IN VARCHAR2:=NULL,
826 p_ATTRIBUTE3 IN VARCHAR2:=NULL,
827 p_ATTRIBUTE4 IN VARCHAR2:=NULL,
828 p_ATTRIBUTE5 IN VARCHAR2:=NULL,
829 p_ATTRIBUTE6 IN VARCHAR2:=NULL,
830 p_ATTRIBUTE7 IN VARCHAR2:=NULL,
831 p_ATTRIBUTE8 IN VARCHAR2:=NULL,
832 p_ATTRIBUTE9 IN VARCHAR2:=NULL,
833 p_ATTRIBUTE10 IN VARCHAR2:=NULL,
834 p_ATTRIBUTE11 IN VARCHAR2:=NULL,
835 p_ATTRIBUTE12 IN VARCHAR2:=NULL,
836 p_ATTRIBUTE13 IN VARCHAR2:=NULL,
837 p_ATTRIBUTE14 IN VARCHAR2:=NULL,
838 p_ATTRIBUTE15 IN VARCHAR2:=NULL,
839 p_EAM_LINEAR_ID IN NUMBER:=NULL,
840 p_equipment_object_id IN NUMBER := NULL,
841 p_operational_log_flag IN VARCHAR2 := NULL,
842 p_checkin_status IN NUMBER := NULL,
843 p_supplier_warranty_exp_date IN DATE := NULL,
844 x_reason_failed OUT NOCOPY VARCHAR2,
845 x_token OUT NOCOPY VARCHAR2
846 )
847 return boolean
848 is
849 l_validate boolean;
850 l_count number;
851 l_org number;
852 l_instance_id number;
853 l_old_maint_flag varchar2(1);
854 l_old_network_asset_flag varchar2(1);
855 l_prod_equipment_type number;
856 l_category_set_id number;
857 l_prod_null boolean;
858 l_error_segments number;
859 l_error_message varchar2(1200);
860 l_prod_organization_id NUMBER;
861 l_prod_inventory_item_id NUMBER;
862 l_prod_serial_number VARCHAR2(30);
863 l_eam_item_type number;
864
865 begin
866 select eam_item_type
867 into l_eam_item_type
868 from mtl_system_items
869 where inventory_item_id = p_INVENTORY_ITEM_ID
870 and organization_id = p_CURRENT_ORGANIZATION_ID;
871
872 BEGIN
873 SELECT cii.instance_id, nvl(cii.maintainable_flag, 'Y'), nvl(cii.network_asset_flag, 'N'), mp.maint_organization_id
874 INTO l_instance_id, l_old_maint_flag, l_old_network_asset_flag, l_org
875 FROM csi_item_instances cii, mtl_parameters mp
876 WHERE cii.serial_number = p_serial_number
877 AND cii.inventory_item_id = p_inventory_item_id
881 EXCEPTION
878 AND cii.last_vld_organization_id = p_current_organization_id
879 AND cii.last_vld_organization_id = mp.organization_id;
880
882 WHEN NO_DATA_FOUND THEN
883 l_old_network_asset_flag := p_network_asset_flag ;
884 END;
885
886 -- validate the boolean flags
887 if (p_maintainable_flag is not null and p_maintainable_flag <> fnd_api.g_miss_char) then
888 l_validate:=eam_common_utilities_pvt.validate_boolean_flag
889 (p_maintainable_flag);
890 if (not l_validate) then
891 x_reason_failed:='EAM_MAINTAINABLE_FLAG_INVALID';
892 fnd_message.set_name('EAM',x_reason_failed);
893 fnd_msg_pub.add;
894 return false;
895 end if;
896
897 /* Bug # 4768635 : Validate if Maintainable)_flag can be 'N' */
898 IF (p_maintainable_flag = 'N' AND l_old_maint_flag = 'Y') THEN
899 BEGIN
900 SELECT 1 INTO l_count
901 FROM DUAL
902 WHERE EXISTS
903 (SELECT wdj.wip_entity_id
904 FROM wip_discrete_jobs wdj
905 WHERE wdj.status_type not in (4, 5, 7, 12)
906 AND wdj.maintenance_object_id = l_instance_id
907 AND wdj.maintenance_object_type = 3
908 AND wdj.organization_id = l_org)
909 OR EXISTS
910 (SELECT wewr.asset_number
911 FROM wip_eam_work_requests wewr
912 WHERE wewr.work_request_status_id not in (4, 5, 6)
913 AND wewr.organization_id = l_org
914 AND wewr.maintenance_object_id = l_instance_id
915 AND wewr.maintenance_object_type = 3);
916 IF l_count = 1 then
917 x_reason_failed:='EAM_WO_EXIST';
918 fnd_message.set_name('EAM',x_reason_failed);
919 fnd_msg_pub.add;
920 return false;
921 END IF;
922 EXCEPTION
923 WHEN NO_DATA_FOUND THEN
924 NULL;
925 END;
926 END IF;
927
928 end if;
929
930 if (p_network_asset_flag is not null and p_network_asset_flag <> fnd_api.g_miss_char) then
931
932 if (l_eam_item_type = 1) then
933 l_validate:=eam_common_utilities_pvt.validate_boolean_flag
934 (p_network_asset_flag);
935 if ( (not l_validate) or (p_network_asset_flag <> l_old_network_asset_flag) ) then
936 x_reason_failed:='EAM_NETWORK_ASSET_INVALID';
937 fnd_message.set_name('EAM',x_reason_failed);
938 fnd_msg_pub.add;
939 return false;
940 end if;
941 else
942 if (p_network_asset_flag = 'Y') then
943 x_reason_failed:='EAM_REB_NETWORK_INVALID';
944 return false;
945 end if;
946 end if;
947 end if;
948
949
950 --validate linear id
951 if (p_EAM_LINEAR_ID is not null and p_EAM_LINEAR_ID <> fnd_api.g_miss_num) then
952 l_validate := eam_common_utilities_pvt.validate_linear_id
953 (p_EAM_LINEAR_ID);
954
955 if (not l_validate) then
956 x_reason_failed:='EAM_INVALID_EAM_LINEAR_ID';
957 fnd_message.set_name('EAM',x_reason_failed);
958 fnd_msg_pub.add;
959 return false;
960 end if;
961 end if;
962
963
964 -- validate department id
965 /*
966 if (p_owning_department_id is null) then
967 x_reason_failed:='EAM_DEPT_ID_NULL';
968 return false;
969 */
970 if (p_owning_department_id is not null and p_owning_department_id <> fnd_api.g_miss_num) then
971 l_validate:=eam_common_utilities_pvt.validate_department_id
972 (p_owning_department_id,
973 p_current_organization_id);
974 if (not l_validate) then
975 x_reason_failed:='EAM_DEPT_ID_INVALID';
976 fnd_message.set_name('EAM',x_reason_failed);
977 fnd_msg_pub.add;
978 return false;
979 end if;
980 end if;
981
982 -- validate wip_accounting_class_code
983 if (p_wip_accounting_class_code is not null and p_wip_accounting_class_code <> fnd_api.g_miss_char) then
984 l_validate:=eam_common_utilities_pvt.validate_wip_acct_class_code
985 (p_current_organization_id,
986 p_wip_accounting_class_code);
987 if (not l_validate) then
988 x_reason_failed:='EAM_WIP_ACCT_CLASS_INVALID';
989 fnd_message.set_name('EAM',x_reason_failed);
990 fnd_msg_pub.add;
991 return false;
992 end if;
993 end if;
994
995 -- validate criticality code
996 if (p_asset_criticality_code is not null and p_asset_criticality_code <> fnd_api.g_miss_char) then
997 l_validate:=eam_common_utilities_pvt.validate_mfg_lookup_code
998 ('MTL_EAM_ASSET_CRITICALITY',
999 p_asset_criticality_code);
1000 if (not l_validate) then
1001 x_reason_failed:='EAM_ASSET_CRITICALITY_INVALID';
1002 fnd_message.set_name('EAM',x_reason_failed);
1003 fnd_msg_pub.add;
1004 return false;
1005 end if;
1006 end if;
1007
1008 -- validate location_id
1009 if (p_eam_location_id is not null and p_eam_location_id <> fnd_api.g_miss_num) then
1010 l_validate:=eam_common_utilities_pvt.validate_eam_location_id_asset
1011 (p_current_organization_id,
1012 p_eam_location_id);
1013 if (not l_validate) then
1014 x_reason_failed:='EAM_LOCATION_ID_INVALID';
1015 fnd_message.set_name('EAM',x_reason_failed);
1016 fnd_msg_pub.add;
1017 return false;
1018 end if;
1019 end if;
1020
1021 -- validate category_id
1022 if (p_category_id is not null and p_category_id <> fnd_api.g_miss_num) then
1023 l_category_set_id := 1000000014;
1024
1025 SELECT count(*) into l_count
1026 FROM MTL_ITEM_CATEGORIES
1027 WHERE category_id = p_category_id
1028 AND inventory_item_id = p_inventory_item_id
1032 l_validate:=(l_count>0);
1029 AND organization_id = p_current_organization_id
1030 AND category_set_id = l_category_set_id;
1031
1033
1034 if (not l_validate) then
1035 x_reason_failed:='EAM_CATEGORY_ID_INVALID';
1036 fnd_message.set_name('EAM',x_reason_failed);
1037 fnd_msg_pub.add;
1038 return false;
1039 end if;
1040 end if;
1041
1042 -- validate fa asset
1043 if (p_fa_asset_id is not null and p_fa_asset_id <> fnd_api.g_miss_num ) then
1044 -- First, check if fa is installed on the instance.
1045 select count(*) into l_count
1046 from fnd_product_installations
1047 where application_id=140;
1048
1049 if (l_count = 0) then
1050 x_reason_failed:='EAM_FA_ASSET_ID_INVALID';
1051 fnd_message.set_name('EAM',x_reason_failed);
1052 fnd_msg_pub.add;
1053 return false;
1054 else
1055 SELECT count(*) into l_count
1056 FROM FA_ADDITIONS_B
1057 WHERE asset_id = p_fa_asset_id;
1058
1059 l_validate:=(l_count>0);
1060 if (not l_validate) then
1061 x_reason_failed:='EAM_FA_ASSET_ID_INVALID';
1062 fnd_message.set_name('EAM',x_reason_failed);
1063 fnd_msg_pub.add;
1064 return false;
1065 end if;
1066 end if;
1067 end if;
1068
1069 -- validate pn location id
1070 if (p_pn_location_id is not null and p_pn_location_id <> fnd_api.g_miss_num ) then
1071 if (l_eam_item_type = 1) then
1072
1073 -- First, check if pn is installed on the instance.
1074 select count(*) into l_count
1075 from fnd_product_installations
1076 where application_id=240;
1077
1078 if (l_count = 0) then
1079 x_reason_failed:='EAM_PN_LOCATION_ID_INVALID';
1080 fnd_message.set_name('EAM',x_reason_failed);
1081 fnd_msg_pub.add;
1082 return false;
1083 else
1084 SELECT count(*) into l_count
1085 FROM pn_locations_all
1086 WHERE location_id= p_pn_location_id;
1087
1088 l_validate:=(l_count>0);
1089 if (not l_validate) then
1090 x_reason_failed:='EAM_PN_LOCATION_ID_INVALID';
1091 fnd_message.set_name('EAM',x_reason_failed);
1092 fnd_msg_pub.add;
1093 return false;
1094 end if;
1095 end if;
1096 else
1097 x_reason_failed := 'EAM_REB_INVALID_PN_LOCATION';
1098 return false;
1099 end if;
1100 end if;
1101
1102
1103 -- validate production organization, equipment item, and equipment serial number
1104 -- The above three fields should either be all null or all not-null.
1105 -- If all not-null, the equipment item has to belong to the production organization.
1106 l_prod_null:=true;
1107 if (p_equipment_object_id is not null and p_equipment_object_id <> fnd_api.g_miss_num) then
1108 l_prod_null := false;
1109 end if;
1110 if (l_prod_null = true AND not ((p_prod_organization_id is null or p_prod_organization_id = fnd_api.g_miss_num) and
1111 (p_equipment_item_id is null or p_equipment_item_id = fnd_api.g_miss_num) and
1112 (p_eqp_serial_number is null or p_eqp_serial_number = fnd_api.g_miss_char))) then
1113 l_prod_null:=false;
1114 if (not (
1115 (p_equipment_item_id is not null) and
1116 (p_eqp_serial_number is not null))) then
1117 x_reason_failed:='EAM_PROD_EQP_INCOMPLETE';
1118 fnd_message.set_name('EAM',x_reason_failed);
1119 fnd_msg_pub.add;
1120 return false;
1121 end if;
1122 end if;
1123
1124 if (l_prod_null=false) then
1125 -- Check that the current_organization is the maintenance
1126 -- organization for the prod_organization
1127
1128 if (p_equipment_object_id is not null and p_equipment_object_id <> fnd_api.g_miss_num) then
1129 begin
1130 select current_organization_id, inventory_item_id, serial_number
1131 into l_prod_organization_id, l_prod_inventory_item_id, l_prod_serial_number
1132 from mtl_serial_numbers
1133 where gen_object_id = p_equipment_object_id;
1134 exception
1135 when others then
1136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1137 end;
1138 else
1139 l_prod_inventory_item_id := p_equipment_item_id;
1140 l_prod_serial_number := p_eqp_serial_number;
1141 l_prod_organization_id := p_prod_organization_id;
1142
1143 end if;
1144
1145 if (not (p_current_organization_id = l_prod_organization_id)) then
1146 select count(*) into l_count
1147 from mtl_parameters
1148 where organization_id=l_prod_organization_id
1149 and maint_organization_id=p_current_organization_id;
1150
1151 if (l_count=0) then
1152 x_reason_failed:='EAM_INVALID_PROD_ORG';
1153 fnd_message.set_name('EAM',x_reason_failed);
1154 fnd_msg_pub.add;
1155 return false;
1156 end if;
1157 end if;
1158
1159 -- Check that the equipment item belongs to the prod org
1160 SELECT count(*) INTO l_count
1161 FROM MTL_SYSTEM_ITEMS_B
1162 WHERE inventory_item_id = l_prod_inventory_item_id
1163 AND organization_id = l_prod_organization_id;
1164 if (l_count = 0) then
1165 x_reason_failed:='EAM_INVALID_EQP_ITEM';
1166 fnd_message.set_name('EAM',x_reason_failed);
1167 fnd_msg_pub.add;
1168 return false;
1169 end if;
1170
1171 -- Check that the equipment type of the eqp item is 1
1172 SELECT equipment_type INTO l_prod_equipment_type
1173 FROM MTL_SYSTEM_ITEMS_B
1174 WHERE inventory_item_id = l_prod_inventory_item_id
1178 x_reason_failed:='EAM_EQP_INVALID';
1175 AND organization_id = l_prod_organization_id;
1176
1177 if (l_prod_equipment_type is null) then
1179 fnd_message.set_name('EAM',x_reason_failed);
1180 fnd_msg_pub.add;
1181 return false;
1182 else
1183 IF l_prod_equipment_type <> 1 -- not equipment type
1184 THEN
1185 x_reason_failed:='EAM_EQP_WRONG_TYPE';
1186 fnd_message.set_name('EAM',x_reason_failed);
1187 fnd_msg_pub.add;
1188 return false;
1189 END IF;
1190 end if;
1191
1192 -- Check that the equipment serial number belongs to the
1193 -- equipment item
1194 select count(*) into l_count
1195 from mtl_serial_numbers
1196 where inventory_item_id = l_prod_inventory_item_id
1197 and current_organization_id = l_prod_organization_id
1198 and serial_number = l_prod_serial_number;
1199
1200 if l_count = 0 then
1201 x_reason_failed:='EAM_EQP_SERIAL_INVALID';
1202 fnd_message.set_name('EAM',x_reason_failed);
1203 fnd_msg_pub.add;
1204 return false;
1205 end if;
1206 end if;
1207 -- End of validation for prod org, equipment item, and eqp serial number
1208
1209 if (p_checkin_status is not null and p_checkin_status <> fnd_api.g_miss_num) then
1210 l_validate:=eam_common_utilities_pvt.validate_mfg_lookup_code
1211 ('EAM_ASSET_OPERATION_TXN_TYPE',
1212 p_checkin_status);
1213 if ( (not l_validate) or (p_network_asset_flag = 'Y') ) then
1214 x_reason_failed:='EAM_CHECKIN_STATUS_INVALID';
1215 fnd_message.set_name('EAM',x_reason_failed);
1216 fnd_msg_pub.add;
1217 return false;
1218 end if;
1219
1220 end if;
1221
1222 if (p_operational_log_flag is not null and p_operational_log_flag <> fnd_api.g_miss_char) then
1223 if ((nvl(p_operational_log_flag,'N') not in ('Y','N')) or
1224 (p_network_asset_flag = 'Y' and p_operational_log_flag = 'Y') ) then
1225 x_reason_failed:='EAM_OPERATION_LOG_FLAG_INVALID';
1226 fnd_message.set_name('EAM',x_reason_failed);
1227 fnd_msg_pub.add;
1228 return false;
1229 end if;
1230
1231 end if;
1232
1233 return true;
1234
1235 end validate_fields;
1236
1237
1238 procedure add_error (p_error_code IN varchar2)
1239 is
1240 begin
1241 FND_MESSAGE.SET_NAME('EAM', p_error_code);
1242 FND_MSG_PUB.Add;
1243 end;
1244
1245
1246 END;