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