[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_NUMBER_PVT
Source
1 PACKAGE BODY EAM_ASSET_NUMBER_PVT as
2 /* $Header: EAMVASNB.pls 120.30 2011/12/08 20:28:33 devijay ship $ */
3 -- Start of comments
4 -- API name : EAM_ASSET_NUMBER_PVT
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN P_API_VERSION IN NUMBER REQUIRED
10 -- P_INIT_MSG_LIST IN VARCHAR2 OPTIONAL
11 -- DEFAULT = FND_API.G_FALSE
12 -- P_COMMIT IN VARCHAR2 OPTIONAL
13 -- DEFAULT = FND_API.G_FALSE
14 -- P_VALIDATION_LEVEL IN NUMBER OPTIONAL
15 -- DEFAULT = FND_API.G_VALID_LEVEL_FULL
16 -- P_ROWID IN OUT VARCHAR2 REQUIRED
17 -- P_INVENTORY_ITEM_ID IN NUMBER
18 -- P_SERIAL_NUMBER IN VARCHAR2
19 -- P_START_DATE_ACTIVE IN DATE
20 -- P_DESCRIPTIVE_TEXT IN VARCHAR2
21 -- P_ORGANIZATION_ID IN NUMBER
22 -- P_CATEGORY_ID IN NUMBER
23 -- P_PN_LOCATION_ID IN NUMBER
24 -- P_EAM_LOCATION_ID IN NUMBER
25 -- P_FA_ASSET_ID IN NUMBER
26 -- P_ASSET_STATUS_CODE IN VARCHAR2
27 -- P_ASSET_CRITICALITY_CODE IN VARCHAR2
28 -- P_WIP_ACCOUNTING_CLASS_CODE IN VARCHAR2
29 -- P_MAINTAINABLE_FLAG IN VARCHAR2
30 -- P_NETWORK_ASSET_FLAG IN VARCHAR2
31 -- P_OWNING_DEPARTMENT_ID IN NUMBER
32 -- P_ATTRIBUTE_CATEGORY IN VARCHAR2 OPTIONAL
33 -- P_ATTRIBUTE1 IN VARCHAR2 OPTIONAL
34 -- P_ATTRIBUTE2 IN VARCHAR2 OPTIONAL
35 -- P_ATTRIBUTE3 IN VARCHAR2 OPTIONAL
36 -- P_ATTRIBUTE4 IN VARCHAR2 OPTIONAL
37 -- P_ATTRIBUTE5 IN VARCHAR2 OPTIONAL
38 -- P_ATTRIBUTE6 IN VARCHAR2 OPTIONAL
39 -- P_ATTRIBUTE7 IN VARCHAR2 OPTIONAL
40 -- P_ATTRIBUTE8 IN VARCHAR2 OPTIONAL
41 -- P_ATTRIBUTE9 IN VARCHAR2 OPTIONAL
42 -- P_ATTRIBUTE10 IN VARCHAR2 OPTIONAL
43 -- P_ATTRIBUTE11 IN VARCHAR2 OPTIONAL
44 -- P_ATTRIBUTE12 IN VARCHAR2 OPTIONAL
45 -- P_ATTRIBUTE13 IN VARCHAR2 OPTIONAL
46 -- P_ATTRIBUTE14 IN VARCHAR2 OPTIONAL
47 -- P_ATTRIBUTE15 IN VARCHAR2 OPTIONAL
48 -- P_LAST_UPDATE_DATE IN DATE REQUIRED
49 -- P_LAST_UPDATED_BY IN NUMBER REQUIRED
50 -- P_CREATION_DATE IN DATE REQUIRED
51 -- P_CREATED_BY IN NUMBER REQUIRED
52 -- P_LAST_UPDATE_LOGIN IN NUMBER REQUIRED
53 -- P_REQUEST_ID IN NUMBER DEFAULT NULL OPTIONAL
54 -- P_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL OPTIONAL
55 -- P_PROGRAM_ID IN NUMBER DEFAULT NULL OPTIONAL
56 -- P_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL
57 -- OUT X_OBJECT_ID OUT NUMBER
58 -- X_RETURN_STATUS OUT VARCHAR2(1)
59 -- X_MSG_COUNT OUT NUMBER
60 -- X_MSG_DATA OUT VARCHAR2(2000)
61 --
62 -- Version Current version 1.0
63 --
64 -- Notes : Note text
65 --
66 -- End of comments
67
68 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_asset_number_pvt';
69
70
71 FUNCTION actual_value_char(p_from_public_api VARCHAR2, p_new_value VARCHAR2, p_old_value VARCHAR2) RETURN VARCHAR2 is
72 result VARCHAR2(240);
73 BEGIN
74 result := null;
75 IF (p_from_public_api = 'N') THEN
76 result := p_new_value;
77 ELSE
78 IF (p_new_value is null) THEN
79 result := p_old_value;
80 ELSIF (p_new_value = fnd_api.g_miss_char) THEN
81 result := null;
82 ELSE
83 result := p_new_value;
84 END IF;
85 END IF;
86 RETURN(result);
87 END;
88
89 FUNCTION actual_value_date(p_from_public_api VARCHAR2, p_new_value date, p_old_value date) RETURN date is
90 result date;
91 BEGIN
92 result := null;
93 IF (p_from_public_api = 'N') THEN
94 result := p_new_value;
95 ELSE
96 IF (p_new_value is null) THEN
97 result := p_old_value;
98 ELSIF (p_new_value = fnd_api.g_miss_date) THEN
99 result := null;
100 ELSE
101 result := p_new_value;
102 END IF;
103 END IF;
104 RETURN(result);
105 END;
106
107
108 PROCEDURE INSERT_ROW(
109 P_API_VERSION IN NUMBER,
110 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
111 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
112 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
113 P_INVENTORY_ITEM_ID NUMBER,
114 P_SERIAL_NUMBER VARCHAR2,
115 P_INSTANCE_NUMBER VARCHAR2,
116 P_INSTANCE_DESCRIPTION VARCHAR2,
117 P_ORGANIZATION_ID NUMBER,
118 P_CATEGORY_ID NUMBER,
119 P_PN_LOCATION_ID NUMBER,
120 P_FA_ASSET_ID NUMBER,
121 P_FA_SYNC_FLAG VARCHAR2,
122 P_ASSET_CRITICALITY_CODE VARCHAR2,
123 P_MAINTAINABLE_FLAG VARCHAR2,
124 P_NETWORK_ASSET_FLAG VARCHAR2,
125 P_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL,
126 P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
127 P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
128 P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
129 P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
130 P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
131 P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
132 P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
133 P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
134 P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
135 P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
136 P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
137 P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
138 P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
139 P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
140 P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
141 P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
142 P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
143 P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
144 P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
145 P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
146 P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
147 P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
148 P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
149 P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
150 P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
151 P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
152 P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
153 P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
154 P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
155 P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
156 P_REQUEST_ID NUMBER DEFAULT NULL,
157 P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL,
158 P_PROGRAM_ID NUMBER DEFAULT NULL,
159 P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
160 P_LAST_UPDATE_DATE DATE,
161 P_LAST_UPDATED_BY NUMBER,
162 P_CREATION_DATE DATE,
163 P_CREATED_BY NUMBER,
164 P_LAST_UPDATE_LOGIN NUMBER,
165 p_active_start_date DATE DEFAULT NULL,
166 p_active_end_date DATE DEFAULT NULL,
167 p_location NUMBER DEFAULT NULL,
168 p_linear_location_id NUMBER DEFAULT NULL,
169 p_operational_log_flag VARCHAR2 DEFAULT NULL,
170 p_checkin_status NUMBER DEFAULT NULL,
171 p_supplier_warranty_exp_date DATE DEFAULT NULL,
172 p_equipment_gen_object_id NUMBER DEFAULT NULL,
173 p_mfg_serial_number_flag VARCHAR2,
174 X_OBJECT_ID OUT NOCOPY NUMBER,
175 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
176 X_MSG_COUNT OUT NOCOPY NUMBER,
177 X_MSG_DATA OUT NOCOPY VARCHAR2
178 )
179
180 is
181 l_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
182 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
183 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
184 l_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
185 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
186 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
187 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
188 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
189 l_instance_asset_rec csi_datastructures_pub.instance_asset_rec;
190 l_x_return_status VARCHAR2(2000);
191 l_x_msg_count NUMBER;
192 l_x_msg_data VARCHAR2(2000);
193 l_x_msg_index_out NUMBER;
194 t_output VARCHAR2(2000);
195 t_msg_dummy NUMBER;
196 l_master_organization_id NUMBER;
197 l_primary_uom_code MTL_SYSTEM_ITEMS.primary_uom_code%type;
198 l_internal_party_id NUMBER;
199 l_fa_x_return_status VARCHAR2(2000);
200 l_fa_x_msg_count NUMBER;
201 l_fa_x_msg_data VARCHAR2(2000);
202
203 l_msg_index NUMBER;
204 l_msg_count NUMBER;
205
206 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
207 l_api_version CONSTANT NUMBER := 1.0;
208 begin
209 -- Standard Start of API savepoint
210 SAVEPOINT insert_row;
211
212 -- Standard call to check for call compatibility.
213 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
214 RAISE fnd_api.g_exc_unexpected_error;
215 END IF;
216
217 -- Initialize message list if p_init_msg_list is set to TRUE.
218 IF fnd_api.to_boolean(p_init_msg_list) THEN
219 fnd_msg_pub.initialize;
220 END IF;
221
222 -- Initialize API return status to success
223 x_return_status := fnd_api.g_ret_sts_success;
224
225 -- API body
226
227 l_instance_rec.instance_number := p_instance_number;
228 l_instance_rec.inventory_item_id := p_inventory_item_id; -- replace
229 l_instance_rec.vld_organization_id := p_organization_id; -- replace
230
231 select master_organization_id
232 into l_master_organization_id
233 from mtl_parameters
234 where organization_id = p_organization_id;
235
236 l_instance_rec.inv_master_organization_id := l_master_organization_id; -- replace
237 l_instance_rec.serial_number := p_serial_number; -- replace
238 l_instance_rec.mfg_serial_number_flag := p_mfg_serial_number_flag;
239 l_instance_rec.quantity := 1;
240
241 select primary_uom_code into l_primary_uom_code
242 from mtl_system_items
243 where organization_id = p_organization_id
244 and inventory_item_id = p_inventory_item_id;
245
246 l_instance_rec.unit_of_measure := l_primary_uom_code;
247 l_instance_rec.instance_condition_id := 1;
248
249 l_instance_rec.active_start_date := p_active_start_date;
250 l_instance_rec.active_end_date := p_active_end_date;
251
252 if (p_location is null) then
253 l_instance_rec.location_type_code := 'INTERNAL_SITE';
254
255 else
256 l_instance_rec.location_type_code := 'HZ_LOCATIONS';
257
258 end if;
259 l_instance_rec.location_id := p_location;
260
261 l_instance_rec.instance_description := p_instance_description;
262
263 l_instance_rec.context := P_ATTRIBUTE_CATEGORY;
264 l_instance_rec.attribute1 := P_ATTRIBUTE1;
265 l_instance_rec.attribute2 := P_ATTRIBUTE2;
266 l_instance_rec.attribute3 := P_ATTRIBUTE3;
267 l_instance_rec.attribute4 := P_ATTRIBUTE4;
268 l_instance_rec.attribute5 := P_ATTRIBUTE5;
269 l_instance_rec.attribute6 := P_ATTRIBUTE6;
270 l_instance_rec.attribute7 := P_ATTRIBUTE7;
271 l_instance_rec.attribute8 := P_ATTRIBUTE8;
272 l_instance_rec.attribute9 := P_ATTRIBUTE9;
273 l_instance_rec.attribute10 := P_ATTRIBUTE10;
274 l_instance_rec.attribute11 := P_ATTRIBUTE11;
275 l_instance_rec.attribute12 := P_ATTRIBUTE12;
276 l_instance_rec.attribute13 := P_ATTRIBUTE13;
277 l_instance_rec.attribute14 := P_ATTRIBUTE14;
278 l_instance_rec.attribute15 := P_ATTRIBUTE15;
279 l_instance_rec.attribute16 := P_ATTRIBUTE16;
280 l_instance_rec.attribute17 := P_ATTRIBUTE17;
281 l_instance_rec.attribute18 := P_ATTRIBUTE18;
282 l_instance_rec.attribute19 := P_ATTRIBUTE19;
283 l_instance_rec.attribute20 := P_ATTRIBUTE20;
284 l_instance_rec.attribute21 := P_ATTRIBUTE21;
285 l_instance_rec.attribute22 := P_ATTRIBUTE22;
286 l_instance_rec.attribute23 := P_ATTRIBUTE23;
287 l_instance_rec.attribute24 := P_ATTRIBUTE24;
288 l_instance_rec.attribute25 := P_ATTRIBUTE25;
289 l_instance_rec.attribute26 := P_ATTRIBUTE26;
290 l_instance_rec.attribute27 := P_ATTRIBUTE27;
291 l_instance_rec.attribute28 := P_ATTRIBUTE28;
292 l_instance_rec.attribute29 := P_ATTRIBUTE29;
293 l_instance_rec.attribute30 := P_ATTRIBUTE30;
294
295 l_instance_rec.instance_usage_code := 'IN_SERVICE';
296
297 l_instance_rec.network_asset_flag := p_network_asset_flag;
298 l_instance_rec.maintainable_flag := p_maintainable_flag;
299 l_instance_rec.pn_location_id := p_pn_location_id;
300 l_instance_rec.asset_criticality_code := p_asset_criticality_code;
301 l_instance_rec.category_id := p_category_id;
302 l_instance_rec.equipment_gen_object_id := p_equipment_gen_object_id;
303 l_instance_rec.linear_location_id := p_linear_location_id;
304 l_instance_rec.active_start_date := p_active_start_date;
305 l_instance_rec.active_end_date := p_active_end_date;
306 l_instance_rec.operational_log_flag := p_operational_log_flag;
307 l_instance_rec.checkin_status := p_checkin_status;
308 l_instance_rec.supplier_warranty_exp_date := p_supplier_warranty_exp_date;
309
310 select internal_party_id
311 into l_internal_party_id
312 from csi_install_parameters;
313
314 l_party_tbl(1).party_source_table := 'HZ_PARTIES';
315 l_party_tbl(1).party_id := l_internal_party_id;
316 l_party_tbl(1).relationship_type_code := 'OWNER';
317 l_party_tbl(1).contact_flag := 'N';
318
319 l_txn_rec.transaction_date := sysdate;
320 l_txn_rec.source_transaction_date := sysdate;
321 l_txn_rec.transaction_type_id := 91;
322 l_txn_rec.transaction_status_code := 'COMPLETE';
323
324 if P_FA_ASSET_ID is not null then
325
326 l_asset_assignment_tbl(1).instance_id := null;
327 l_asset_assignment_tbl(1).fa_asset_id := P_FA_ASSET_ID;
328
329
330 if P_FA_SYNC_FLAG = 'Y' then
331 l_asset_assignment_tbl(1).fa_sync_flag := 'Y';
332 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_TRUE;
333 else
334 l_asset_assignment_tbl(1).fa_sync_flag := 'N';
335 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_FALSE;
336 end if;
337
338 select fb.book_type_code
339 into l_asset_assignment_tbl(1).fa_book_type_code
340 from fa_books fb,
341 fa_book_controls fbc
342 where fb.asset_id = P_FA_ASSET_ID
343 and fb.book_type_code = fbc.book_type_code
344 and fbc.book_class = 'CORPORATE'
345 and rownum = 1;
346
347 select fdh.location_id
348 into l_asset_assignment_tbl(1).fa_location_id
349 from fa_distribution_history fdh
350 where asset_id = P_FA_ASSET_ID
351 and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
352 and date_ineffective is null
353 and rownum = 1;
354
355 l_asset_assignment_tbl(1).asset_quantity := 1;
356 l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
357
358 l_asset_assignment_tbl(1).parent_tbl_index := 1;
359 end if;
360
361
362 -- Now call the stored program
363
364 csi_item_instance_pub.create_item_instance
365 (
366 p_api_version => 1.0
367 ,p_commit => fnd_api.g_false
368 ,p_init_msg_list => fnd_api.g_false
369 ,p_validation_level => fnd_api.g_valid_level_full
370 ,p_instance_rec => l_instance_rec
371 ,p_ext_attrib_values_tbl => l_ext_attrib_values_tbl
372 ,p_party_tbl => l_party_tbl
373 ,p_account_tbl => l_account_tbl
374 ,p_pricing_attrib_tbl => l_pricing_attrib_tbl
375 ,p_org_assignments_tbl => l_org_assignments_tbl
376 ,p_asset_assignment_tbl => l_asset_assignment_tbl
377 ,p_txn_rec => l_txn_rec
378 ,x_return_status => x_return_status
379 ,x_msg_count => x_msg_count
380 ,x_msg_data => x_msg_data
381 );
382
383 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
384 l_msg_index := 1;
385 l_msg_count := x_msg_count;
386 WHILE l_msg_count > 0 LOOP
387 x_msg_data := FND_MSG_PUB.GET
388 ( l_msg_index,
389 FND_API.G_FALSE );
390 -- csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_CUHK.Create_Item_Instance_Post API ');
391 -- csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
392 l_msg_index := l_msg_index + 1;
393 l_msg_count := l_msg_count - 1;
394 END LOOP;
395
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398
399 x_object_id := l_instance_rec.instance_id;
400
401 /* Bug # 5339642 : Call Text index procedure for inserting the row in EAT */
402 eam_text_util.process_asset_update_event
403 (
404 p_event => 'INSERT'
405 ,p_instance_id => l_instance_rec.instance_id
406 );
407
408 -- Standard check of p_commit.
409 IF fnd_api.to_boolean(p_commit) THEN
410 COMMIT WORK;
411 END IF;
412
413 -- Standard call to get message count and if count is 1, get message info.
414 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
415 EXCEPTION
416 WHEN fnd_api.g_exc_error THEN
417 ROLLBACK TO insert_row;
418 x_return_status := fnd_api.g_ret_sts_error;
419 /*fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);*/
420 WHEN fnd_api.g_exc_unexpected_error THEN
421 ROLLBACK TO insert_row;
422 x_return_status := fnd_api.g_ret_sts_unexp_error;
423 /*fnd_msg_pub.count_and_get(
424 p_count => x_msg_count
425 ,p_data => x_msg_data);*/
426 WHEN OTHERS THEN
427 ROLLBACK TO insert_row;
428 x_return_status := fnd_api.g_ret_sts_unexp_error;
429
430 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
431 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
432 END IF;
433
434 /*fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data); */
435
436
437 end insert_row;
438
439
440
441 PROCEDURE UPDATE_ROW(
442 P_API_VERSION IN NUMBER,
443 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
444 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
445 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
446 p_instance_id IN NUMBER,
447 P_INSTANCE_DESCRIPTION VARCHAR2,
448 P_CATEGORY_ID NUMBER,
449 P_PN_LOCATION_ID NUMBER,
450 P_FA_ASSET_ID NUMBER,
451 P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL,
452 P_ASSET_CRITICALITY_CODE VARCHAR2,
453 P_MAINTAINABLE_FLAG VARCHAR2,
454 P_NETWORK_ASSET_FLAG VARCHAR2,
455 P_ATTRIBUTE_CATEGORY VARCHAR2,
456 P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
457 P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
458 P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
459 P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
460 P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
461 P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
462 P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
463 P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
464 P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
465 P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
466 P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
467 P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
468 P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
469 P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
470 P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
471 P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
472 P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
473 P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
474 P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
475 P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
476 P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
477 P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
478 P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
479 P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
480 P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
481 P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
482 P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
483 P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
484 P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
485 P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
486 P_REQUEST_ID NUMBER DEFAULT NULL,
487 P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL,
488 P_PROGRAM_ID NUMBER DEFAULT NULL,
489 P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
490 P_LAST_UPDATE_DATE DATE,
491 P_LAST_UPDATED_BY NUMBER,
492 P_LAST_UPDATE_LOGIN NUMBER,
493 P_FROM_PUBLIC_API VARCHAR2 DEFAULT 'Y',
494 P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL,
495 P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL,
496 P_LOCATION_ID NUMBER DEFAULT NULL,
497 p_active_end_date DATE DEFAULT NULL,
498 p_linear_location_id NUMBER DEFAULT NULL,
499 p_operational_log_flag VARCHAR2 DEFAULT NULL,
500 p_checkin_status NUMBER DEFAULT NULL,
501 p_supplier_warranty_exp_date DATE DEFAULT NULL,
502 p_equipment_gen_object_id NUMBER DEFAULT NULL,
503 p_reactivate_asset VARCHAR2 DEFAULT 'N',
504 p_disassociate_fa_flag VARCHAR2 DEFAULT 'N', --5474749
505 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
506 X_MSG_COUNT OUT NOCOPY NUMBER,
507 X_MSG_DATA OUT NOCOPY VARCHAR2
508 )
509 is
510
511 l_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
512 l_ext_attrib_values_tbl CSI_DATASTRUCTURES_PUB.EXTEND_ATTRIB_VALUES_TBL;
513 l_party_tbl CSI_DATASTRUCTURES_PUB.PARTY_TBL;
514 l_party_account_tbl CSI_DATASTRUCTURES_PUB.PARTY_ACCOUNT_TBL;
515 l_pricing_attrib_tbl CSI_DATASTRUCTURES_PUB.PRICING_ATTRIBS_TBL;
516 l_org_assignments_tbl CSI_DATASTRUCTURES_PUB.ORGANIZATION_UNITS_TBL;
517 l_asset_assignment_tbl CSI_DATASTRUCTURES_PUB.INSTANCE_ASSET_TBL;
518 l_txn_rec CSI_DATASTRUCTURES_PUB.TRANSACTION_REC;
519 l_x_instance_id_lst CSI_DATASTRUCTURES_PUB.ID_TBL;
520
521 t_output varchar2(2000);
522 t_msg_dummy number;
523 l_object_version_number number;
524
525 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
526 l_api_version CONSTANT NUMBER := 1.0;
527 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
528 l_x_return_status varchar2(100);
529 l_x_msg_count number;
530 l_x_msg_data varchar2(20000);
531 l_msg_index NUMBER;
532 l_msg_count NUMBER;
533 l_old_location_type_code varchar2(80);
534 begin
535 -- Standard Start of API savepoint
536 SAVEPOINT update_row;
537
538 -- Standard call to check for call compatibility.
539 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
540 RAISE fnd_api.g_exc_unexpected_error;
541 END IF;
542
543 -- Initialize message list if p_init_msg_list is set to TRUE.
544 IF fnd_api.to_boolean(p_init_msg_list) THEN
545 fnd_msg_pub.initialize;
546 END IF;
547
548 -- Initialize API return status to success
549 x_return_status := fnd_api.g_ret_sts_success;
550
551 -- API body
552
553 --csi_gen_utility_pvt.put_line('EAM: Start of update_row, Active End date is : '||to_char(p_active_end_date,'dd-mon-yy hh24:mi:ss'));
554
555
556 l_instance_rec.instance_id := P_INSTANCE_ID; --replace
557
558 select object_version_number
559 into l_object_version_number
560 from csi_item_instances
561 where instance_id = p_instance_id;
562
563 l_instance_rec.object_version_number := l_object_version_number; --replace
564
565
566 l_instance_rec.location_type_code := actual_value_char(p_from_public_api, p_location_type_code, FND_API.G_MISS_CHAR);
567 l_instance_rec.location_id := actual_value_char(p_from_public_api, p_location_id,FND_API.G_MISS_NUM);
568 l_instance_rec.instance_description := actual_value_char(p_from_public_api, p_instance_description,FND_API.G_MISS_CHAR);
569
570 l_instance_rec.instance_number := actual_value_char(p_from_public_api, p_instance_number,FND_API.G_MISS_CHAR);
571 l_instance_rec.context := actual_value_char(p_from_public_api, P_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR);
572 l_instance_rec.attribute1 := actual_value_char(p_from_public_api, P_ATTRIBUTE1,FND_API.G_MISS_CHAR);
573 l_instance_rec.attribute2 := actual_value_char(p_from_public_api, P_ATTRIBUTE2,FND_API.G_MISS_CHAR);
574 l_instance_rec.attribute3 := actual_value_char(p_from_public_api, P_ATTRIBUTE3,FND_API.G_MISS_CHAR);
575 l_instance_rec.attribute4 := actual_value_char(p_from_public_api, P_ATTRIBUTE4,FND_API.G_MISS_CHAR);
576 l_instance_rec.attribute5 := actual_value_char(p_from_public_api, P_ATTRIBUTE5,FND_API.G_MISS_CHAR);
577 l_instance_rec.attribute6 := actual_value_char(p_from_public_api, P_ATTRIBUTE6,FND_API.G_MISS_CHAR);
578 l_instance_rec.attribute7 := actual_value_char(p_from_public_api, P_ATTRIBUTE7,FND_API.G_MISS_CHAR);
579 l_instance_rec.attribute8 := actual_value_char(p_from_public_api, P_ATTRIBUTE8,FND_API.G_MISS_CHAR);
580 l_instance_rec.attribute9 := actual_value_char(p_from_public_api, P_ATTRIBUTE9,FND_API.G_MISS_CHAR);
581 l_instance_rec.attribute10 := actual_value_char(p_from_public_api, P_ATTRIBUTE10,FND_API.G_MISS_CHAR);
582 l_instance_rec.attribute11 := actual_value_char(p_from_public_api, P_ATTRIBUTE11,FND_API.G_MISS_CHAR);
583 l_instance_rec.attribute12 := actual_value_char(p_from_public_api, P_ATTRIBUTE12,FND_API.G_MISS_CHAR);
584 l_instance_rec.attribute13 := actual_value_char(p_from_public_api, P_ATTRIBUTE13,FND_API.G_MISS_CHAR);
585 l_instance_rec.attribute14 := actual_value_char(p_from_public_api, P_ATTRIBUTE14,FND_API.G_MISS_CHAR);
586 l_instance_rec.attribute15 := actual_value_char(p_from_public_api, P_ATTRIBUTE15,FND_API.G_MISS_CHAR);
587 l_instance_rec.attribute16 := actual_value_char(p_from_public_api, P_ATTRIBUTE16,FND_API.G_MISS_CHAR);
588 l_instance_rec.attribute17 := actual_value_char(p_from_public_api, P_ATTRIBUTE17,FND_API.G_MISS_CHAR);
589 l_instance_rec.attribute18 := actual_value_char(p_from_public_api, P_ATTRIBUTE18,FND_API.G_MISS_CHAR);
590 l_instance_rec.attribute19 := actual_value_char(p_from_public_api, P_ATTRIBUTE19,FND_API.G_MISS_CHAR);
591 l_instance_rec.attribute20 := actual_value_char(p_from_public_api, P_ATTRIBUTE20,FND_API.G_MISS_CHAR);
592 l_instance_rec.attribute21 := actual_value_char(p_from_public_api, P_ATTRIBUTE21,FND_API.G_MISS_CHAR);
593 l_instance_rec.attribute22 := actual_value_char(p_from_public_api, P_ATTRIBUTE22,FND_API.G_MISS_CHAR);
594 l_instance_rec.attribute23 := actual_value_char(p_from_public_api, P_ATTRIBUTE23,FND_API.G_MISS_CHAR);
595 l_instance_rec.attribute24 := actual_value_char(p_from_public_api, P_ATTRIBUTE24,FND_API.G_MISS_CHAR);
596 l_instance_rec.attribute25 := actual_value_char(p_from_public_api, P_ATTRIBUTE25,FND_API.G_MISS_CHAR);
597 l_instance_rec.attribute26 := actual_value_char(p_from_public_api, P_ATTRIBUTE26,FND_API.G_MISS_CHAR);
598 l_instance_rec.attribute27 := actual_value_char(p_from_public_api, P_ATTRIBUTE27,FND_API.G_MISS_CHAR);
599 l_instance_rec.attribute28 := actual_value_char(p_from_public_api, P_ATTRIBUTE28,FND_API.G_MISS_CHAR);
600 l_instance_rec.attribute29 := actual_value_char(p_from_public_api, P_ATTRIBUTE29,FND_API.G_MISS_CHAR);
601 l_instance_rec.attribute30 := actual_value_char(p_from_public_api, P_ATTRIBUTE30,FND_API.G_MISS_CHAR);
602
603 l_instance_rec.maintainable_flag := actual_value_char(p_from_public_api, p_maintainable_flag,FND_API.G_MISS_CHAR);
604 l_instance_rec.network_asset_flag := actual_value_char(p_from_public_api, p_network_asset_flag,FND_API.G_MISS_CHAR);
605 l_instance_rec.pn_location_id := actual_value_char(p_from_public_api, p_pn_location_id,FND_API.G_MISS_NUM);
606 l_instance_rec.asset_criticality_code := actual_value_char(p_from_public_api, p_asset_criticality_code,FND_API.G_MISS_CHAR);
607 l_instance_rec.category_id := actual_value_char(p_from_public_api, p_category_id,FND_API.G_MISS_NUM);
608
609 l_instance_rec.equipment_gen_object_id := actual_value_char(p_from_public_api, p_equipment_gen_object_id,FND_API.G_MISS_NUM);
610 l_instance_rec.linear_location_id := actual_value_char(p_from_public_api, p_linear_location_id,FND_API.G_MISS_NUM);
611 --p_instance_rec.start_date_active := actual_value_date(p_from_public_api, p_start_date_active,FND_API.G_MISS_DATE);
612
613 if p_reactivate_asset = 'Y' then
614 l_instance_rec.active_end_date := null;
615 else
616 l_instance_rec.active_end_date := actual_value_date(p_from_public_api, p_active_end_date,FND_API.G_MISS_DATE);
617 end if;
618
619 --csi_gen_utility_pvt.put_line('EAM: After conversion, end date : '||to_char(l_instance_rec.active_end_date,'dd-mon-yy hh24:mi:ss'));
620
621 --l_instance_rec.linear_location_id := actual_value_char(p_from_public_api, p_linear_location_id,FND_API.G_MISS_NUM);
622 l_instance_rec.operational_log_flag := actual_value_char(p_from_public_api, p_operational_log_flag,FND_API.G_MISS_CHAR);
623 l_instance_rec.checkin_status := actual_value_char(p_from_public_api, p_checkin_status,FND_API.G_MISS_NUM);
624 l_instance_rec.supplier_warranty_exp_date := actual_value_date(p_from_public_api, p_supplier_warranty_exp_date,FND_API.G_MISS_DATE);
625
626 l_txn_rec.transaction_id := NULL;
627 l_txn_rec.transaction_date := sysdate;
628 l_txn_rec.source_transaction_date := sysdate;
629 l_txn_rec.transaction_type_id := 91;
630 l_txn_rec.transaction_status_code := 'COMPLETE';
631
632 if P_FA_ASSET_ID is not null then
633
634 l_asset_assignment_tbl(1).instance_id := p_instance_id;
635 l_asset_assignment_tbl(1).fa_asset_id := P_FA_ASSET_ID;
636
637 begin
638 -- Bug 13387261 (Fixing in both R12 and 12.1)
639 -- Selecting only active instance asset ids
640 select instance_asset_id, object_version_number
641 into l_asset_assignment_tbl(1).instance_asset_id,l_asset_assignment_tbl(1).object_version_number
642 from csi_i_assets
643 where instance_id = p_instance_id
644 and fa_asset_id = fa_asset_id
645 and (active_end_date IS NULL OR active_end_date>sysdate);
646 exception
647 when no_data_found then
648 null;
649 end;
650
651 if P_FA_SYNC_FLAG = 'Y' then
652 l_asset_assignment_tbl(1).fa_sync_flag := 'Y';
653 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_TRUE;
654 else
655 l_asset_assignment_tbl(1).fa_sync_flag := 'N';
656 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_FALSE;
657 end if;
658
659 select fb.book_type_code
660 into l_asset_assignment_tbl(1).fa_book_type_code
661 from fa_books fb,
662 fa_book_controls fbc
663 where fb.asset_id = P_FA_ASSET_ID
664 and fb.book_type_code = fbc.book_type_code
665 and fbc.book_class = 'CORPORATE'
666 and rownum = 1;
667
668 select fdh.location_id
669 into l_asset_assignment_tbl(1).fa_location_id
670 from fa_distribution_history fdh
671 where asset_id = P_FA_ASSET_ID
672 and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
673 and date_ineffective is null
674 and rownum = 1;
675
676 l_asset_assignment_tbl(1).asset_quantity := 1;
677 l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
678 l_asset_assignment_tbl(1).active_end_date := null;
679 l_asset_assignment_tbl(1).parent_tbl_index := 1;
680 END IF;
681
682 --5474749 condition added
683 if nvl(p_disassociate_fa_flag,'N') = 'Y' then
684
685 -- Bug 13387261 (Fixing in both R12 and 12.1)
686 -- Selecting only active instance asset ids
687 select fa_asset_id into l_asset_assignment_tbl(1).fa_asset_id
688 from csi_i_assets where instance_id = p_instance_id and (active_end_date IS NULL OR active_end_date>sysdate);
689
690 begin
691 select instance_asset_id, object_version_number
692 into l_asset_assignment_tbl(1).instance_asset_id,l_asset_assignment_tbl(1).object_version_number
693 from csi_i_assets
694 where instance_id = p_instance_id
695 and fa_asset_id = l_asset_assignment_tbl(1).fa_asset_id
696 and (active_end_date IS NULL OR active_end_date>sysdate);
697 exception
698 when no_data_found then
699 null;
700 end;
701
702 if P_FA_SYNC_FLAG = 'Y' then
703 l_asset_assignment_tbl(1).fa_sync_flag := 'Y';
704 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_TRUE;
705 else
706 l_asset_assignment_tbl(1).fa_sync_flag := 'N';
707 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_FALSE;
708 end if;
709
710 select fb.book_type_code
711 into l_asset_assignment_tbl(1).fa_book_type_code
712 from fa_books fb,
713 fa_book_controls fbc
714 where fb.asset_id = l_asset_assignment_tbl(1).fa_asset_id
715 and fb.book_type_code = fbc.book_type_code
716 and fbc.book_class = 'CORPORATE'
717 and rownum = 1;
718
719 select fdh.location_id
720 into l_asset_assignment_tbl(1).fa_location_id
721 from fa_distribution_history fdh
722 where asset_id = l_asset_assignment_tbl(1).fa_asset_id
723 and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
724 and date_ineffective is null
725 and rownum = 1;
726
727 l_asset_assignment_tbl(1).asset_quantity := 1;
728 l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
729
730 l_asset_assignment_tbl(1).parent_tbl_index := 1;
731
732 l_asset_assignment_tbl(1).instance_id := p_instance_id;
733 l_asset_assignment_tbl(1).active_end_date := sysdate;
734
735 end if; --end if for disassociate fa flag
736
737 if (P_LOCATION_TYPE_CODE is not NULL AND P_LOCATION_TYPE_CODE <> FND_API.G_MISS_CHAR) then
738
739
740 if (p_location_type_code <> 'INVENTORY') then
741
742 select location_type_code
743 into l_old_location_type_code
744 from csi_item_instances
745 where instance_id = p_instance_id;
746
747 if (l_old_location_type_code = 'INVENTORY') then
748 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_LOCATION_UPDATE');
749 FND_MSG_PUB.Add;
750 RAISE FND_API.G_EXC_ERROR;
751 end if;
752 end if;
753 end if;
754
755 --csi_gen_utility_pvt.put_line('End date passed to IB API : '||to_char(l_instance_rec.active_end_date,'dd-mon-yy hh24:mi:ss'));
756 -- Now call the stored program
757 csi_item_instance_pub.update_item_instance(
758 p_api_version => 1.0,
759 p_commit => p_commit,
760 p_init_msg_list => p_init_msg_list,
761 p_validation_level => 100,
762 p_instance_rec => l_instance_rec,
763 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
764 p_party_tbl => l_party_tbl,
765 p_account_tbl => l_party_account_tbl,
766 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
767 p_org_assignments_tbl => l_org_assignments_tbl,
768 p_asset_assignment_tbl => l_asset_assignment_tbl,
769 p_txn_rec => l_txn_rec,
770 x_instance_id_lst => l_x_instance_id_lst,
771 x_return_status => l_x_return_status,
772 x_msg_count => l_x_msg_count,
773 x_msg_data => l_x_msg_data
774 );
775 x_return_status := l_x_return_status;
776 x_msg_count := l_x_msg_count;
777 x_msg_data := l_x_msg_data;
778 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
779 l_msg_index := 1;
780 l_msg_count := x_msg_count;
781 WHILE l_msg_count > 0 LOOP
782 x_msg_data := FND_MSG_PUB.GET
783 ( l_msg_index,
784 FND_API.G_FALSE );
785 -- csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_CUHK.Create_Item_Instance_Post API ');
786 -- csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
787 l_msg_index := l_msg_index + 1;
788 l_msg_count := l_msg_count - 1;
789 END LOOP;
790 RAISE FND_API.G_EXC_ERROR;
791 END IF;
792
793 /* Bug # 5339642 : Call Text index procedure for inserting the row in EAT */
794 eam_text_util.process_asset_update_event
795 (
796 p_event => 'UPDATE'
797 ,p_instance_id => l_instance_rec.instance_id
798 );
799
800 -- End of API body.
801 -- Standard check of p_commit.
802 IF fnd_api.to_boolean(p_commit) THEN
803 COMMIT WORK;
804 END IF;
805
806 -- Standard call to get message count and if count is 1, get message info.
807 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
808 EXCEPTION
809 WHEN fnd_api.g_exc_error THEN
810 ROLLBACK TO update_row;
811 x_return_status := fnd_api.g_ret_sts_error;
812 /*fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);*/
813 WHEN fnd_api.g_exc_unexpected_error THEN
814 ROLLBACK TO update_row;
815 x_return_status := fnd_api.g_ret_sts_unexp_error;
816 /*fnd_msg_pub.count_and_get(
817 p_count => x_msg_count
818 ,p_data => x_msg_data);*/
819 WHEN OTHERS THEN
820 ROLLBACK TO update_row;
821 x_return_status := fnd_api.g_ret_sts_unexp_error;
822
823 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
824 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
825 END IF;
826
827 /*fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data); */
828
829 end update_row;
830
831 PROCEDURE LOCK_ROW(
832
833 P_API_VERSION IN NUMBER,
834 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
835 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
836 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
837 P_ROWID VARCHAR2,
838 P_INSTANCE_ID IN NUMBER,
839 P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL,
840 P_INSTANCE_DESCRIPTION VARCHAR2,
841 P_CATEGORY_ID NUMBER,
842 P_PN_LOCATION_ID NUMBER,
843 P_FA_ASSET_ID NUMBER,
844 P_ASSET_CRITICALITY_CODE VARCHAR2,
845 P_MAINTAINABLE_FLAG VARCHAR2,
846 P_NETWORK_ASSET_FLAG VARCHAR2,
847 P_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL,
848 P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
849 P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
850 P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
851 P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
852 P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
853 P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
854 P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
855 P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
856 P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
857 P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
858 P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
859 P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
860 P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
861 P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
862 P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
863 P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
864 P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
865 P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
866 P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
867 P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
868 P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
869 P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
870 P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
871 P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
872 P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
873 P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
874 P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
875 P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
876 P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
877 P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
878 P_REQUEST_ID NUMBER DEFAULT NULL,
879 P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL,
880 P_PROGRAM_ID NUMBER DEFAULT NULL,
881 P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
882 P_LAST_UPDATE_DATE DATE,
883 P_LAST_UPDATED_BY NUMBER,
884 P_LAST_UPDATE_LOGIN NUMBER,
885 P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL,
886 P_LOCATION_ID NUMBER DEFAULT NULL,
887 p_linear_location_id NUMBER DEFAULT NULL,
888 p_operational_log_flag VARCHAR2 DEFAULT NULL,
889 P_checkin_status NUMBER DEFAULT NULL,
890 p_supplier_warranty_exp_date DATE DEFAULT NULL,
891 p_equipment_gen_object_id NUMBER DEFAULT NULL,
892 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
893 X_MSG_COUNT OUT NOCOPY NUMBER,
894 X_MSG_DATA OUT NOCOPY VARCHAR2
895 )
896 IS
897 CURSOR C IS
898 SELECT *
899 FROM CSI_ITEM_INSTANCES
900 WHERE INSTANCE_ID = p_INSTANCE_ID
901 FOR UPDATE of INSTANCE_ID NOWAIT;
902 Recinfo C%ROWTYPE;
903 BEGIN
904 OPEN C;
905 FETCH C INTO Recinfo;
906 IF (C%NOTFOUND) THEN
907 CLOSE C;
908 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
909 APP_EXCEPTION.RAISE_EXCEPTION;
910 END IF;
911 CLOSE C;
912
913 IF (
914 ( Recinfo.INSTANCE_ID = p_INSTANCE_ID)
915 AND ( ( Recinfo.INSTANCE_NUMBER = p_INSTANCE_NUMBER)
916 OR ( ( Recinfo.INSTANCE_NUMBER IS NULL )
917 AND ( p_INSTANCE_NUMBER IS NULL )))
918 /*
919 AND ( ( Recinfo.LOCATION_TYPE_CODE = p_LOCATION_TYPE_CODE)
920 OR ( ( Recinfo.LOCATION_TYPE_CODE IS NULL )
921 AND ( p_LOCATION_TYPE_CODE IS NULL )))
922 AND ( ( Recinfo.LOCATION_ID = p_LOCATION_ID)
923 OR ( ( Recinfo.LOCATION_ID IS NULL )
924 AND ( p_LOCATION_ID IS NULL )))
925 AND ( ( Recinfo.CONTEXT = p_ATTRIBUTE_CATEGORY)
926 OR ( ( Recinfo.CONTEXT IS NULL )
927 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
928 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
929 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
930 AND ( p_ATTRIBUTE1 IS NULL )))
931 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
932 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
933 AND ( p_ATTRIBUTE2 IS NULL )))
934 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
935 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
936 AND ( p_ATTRIBUTE3 IS NULL )))
937 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
938 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
939 AND ( p_ATTRIBUTE4 IS NULL )))
940 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
941 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
942 AND ( p_ATTRIBUTE5 IS NULL )))
943 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
944 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
945 AND ( p_ATTRIBUTE6 IS NULL )))
946 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
947 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
948 AND ( p_ATTRIBUTE7 IS NULL )))
949 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
950 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
951 AND ( p_ATTRIBUTE8 IS NULL )))
952 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
953 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
954 AND ( p_ATTRIBUTE9 IS NULL )))
955 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
956 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
957 AND ( p_ATTRIBUTE10 IS NULL )))
958 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
959 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
960 AND ( p_ATTRIBUTE11 IS NULL )))
961 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
962 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
963 AND ( p_ATTRIBUTE12 IS NULL )))
964 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
965 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
966 AND ( p_ATTRIBUTE13 IS NULL )))
967 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
968 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
969 AND ( p_ATTRIBUTE14 IS NULL )))
970 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
971 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
972 AND ( p_ATTRIBUTE15 IS NULL )))
973 AND ( ( Recinfo.ATTRIBUTE16 = p_ATTRIBUTE16)
974 OR ( ( Recinfo.ATTRIBUTE16 IS NULL )
975 AND ( p_ATTRIBUTE16 IS NULL )))
976 AND ( ( Recinfo.ATTRIBUTE17 = p_ATTRIBUTE17)
977 OR ( ( Recinfo.ATTRIBUTE17 IS NULL )
978 AND ( p_ATTRIBUTE17 IS NULL )))
979 AND ( ( Recinfo.ATTRIBUTE18 = p_ATTRIBUTE18)
980 OR ( ( Recinfo.ATTRIBUTE18 IS NULL )
981 AND ( p_ATTRIBUTE18 IS NULL )))
982 AND ( ( Recinfo.ATTRIBUTE19 = p_ATTRIBUTE19)
983 OR ( ( Recinfo.ATTRIBUTE19 IS NULL )
984 AND ( p_ATTRIBUTE19 IS NULL )))
985 AND ( ( Recinfo.ATTRIBUTE20 = p_ATTRIBUTE20)
986 OR ( ( Recinfo.ATTRIBUTE20 IS NULL )
987 AND ( p_ATTRIBUTE20 IS NULL )))
988 AND ( ( Recinfo.ATTRIBUTE21 = p_ATTRIBUTE21)
989 OR ( ( Recinfo.ATTRIBUTE21 IS NULL )
990 AND ( p_ATTRIBUTE21 IS NULL )))
991 AND ( ( Recinfo.ATTRIBUTE22 = p_ATTRIBUTE22)
992 OR ( ( Recinfo.ATTRIBUTE22 IS NULL )
993 AND ( p_ATTRIBUTE22 IS NULL )))
994 AND ( ( Recinfo.ATTRIBUTE23 = p_ATTRIBUTE23)
995 OR ( ( Recinfo.ATTRIBUTE23 IS NULL )
996 AND ( p_ATTRIBUTE23 IS NULL )))
997 AND ( ( Recinfo.ATTRIBUTE24 = p_ATTRIBUTE24)
998 OR ( ( Recinfo.ATTRIBUTE24 IS NULL )
999 AND ( p_ATTRIBUTE24 IS NULL )))
1000 AND ( ( Recinfo.ATTRIBUTE25 = p_ATTRIBUTE25)
1001 OR ( ( Recinfo.ATTRIBUTE25 IS NULL )
1002 AND ( p_ATTRIBUTE25 IS NULL )))
1003 AND ( ( Recinfo.ATTRIBUTE26 = p_ATTRIBUTE26)
1004 OR ( ( Recinfo.ATTRIBUTE26 IS NULL )
1005 AND ( p_ATTRIBUTE26 IS NULL )))
1006 AND ( ( Recinfo.ATTRIBUTE27 = p_ATTRIBUTE27)
1007 OR ( ( Recinfo.ATTRIBUTE27 IS NULL )
1008 AND ( p_ATTRIBUTE27 IS NULL )))
1009 AND ( ( Recinfo.ATTRIBUTE28 = p_ATTRIBUTE28)
1010 OR ( ( Recinfo.ATTRIBUTE28 IS NULL )
1011 AND ( p_ATTRIBUTE28 IS NULL )))
1012 AND ( ( Recinfo.ATTRIBUTE29 = p_ATTRIBUTE29)
1013 OR ( ( Recinfo.ATTRIBUTE29 IS NULL )
1014 AND ( p_ATTRIBUTE29 IS NULL )))
1015 AND ( ( Recinfo.ATTRIBUTE30 = p_ATTRIBUTE30)
1016 OR ( ( Recinfo.ATTRIBUTE30 IS NULL )
1017 AND ( p_ATTRIBUTE30 IS NULL )))
1018 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
1019 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
1020 AND ( p_LAST_UPDATED_BY IS NULL )))
1021 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
1022 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
1023 AND ( p_LAST_UPDATE_DATE IS NULL )))
1024 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
1025 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
1026 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
1027 AND ( ( Recinfo.INSTANCE_DESCRIPTION = p_INSTANCE_DESCRIPTION)
1028 OR ( ( Recinfo.INSTANCE_DESCRIPTION IS NULL )
1029 AND ( p_INSTANCE_DESCRIPTION IS NULL )))
1030 AND ( ( Recinfo.CATEGORY_ID = p_CATEGORY_ID)
1031 OR ( ( Recinfo.CATEGORY_ID IS NULL )
1032 AND ( p_CATEGORY_ID IS NULL )))
1033 AND ( ( Recinfo.PN_LOCATION_ID = p_PN_LOCATION_ID)
1034 OR ( ( Recinfo.PN_LOCATION_ID IS NULL )
1035 AND ( p_PN_LOCATION_ID IS NULL )))
1036 AND ( ( Recinfo.ASSET_CRITICALITY_CODE = p_ASSET_CRITICALITY_CODE)
1037 OR ( ( Recinfo.ASSET_CRITICALITY_CODE IS NULL )
1038 AND ( p_ASSET_CRITICALITY_CODE IS NULL )))
1039 AND ( ( Recinfo.MAINTAINABLE_FLAG = p_MAINTAINABLE_FLAG)
1040 OR ( ( Recinfo.MAINTAINABLE_FLAG IS NULL )
1041 AND ( p_MAINTAINABLE_FLAG IS NULL )))
1042 AND ( ( Recinfo.NETWORK_ASSET_FLAG = p_NETWORK_ASSET_FLAG)
1043 OR ( ( Recinfo.NETWORK_ASSET_FLAG IS NULL )
1044 AND ( p_NETWORK_ASSET_FLAG IS NULL )))
1045 AND ( ( Recinfo.LINEAR_LOCATION_ID = p_LINEAR_LOCATION_ID)
1046 OR ( ( Recinfo.LINEAR_LOCATION_ID IS NULL )
1047 AND ( p_LINEAR_LOCATION_ID IS NULL )))
1048 AND ( ( Recinfo.OPERATIONAL_LOG_FLAG = p_OPERATIONAL_LOG_FLAG)
1049 OR ( ( Recinfo.OPERATIONAL_LOG_FLAG IS NULL )
1050 AND ( p_OPERATIONAL_LOG_FLAG IS NULL )))
1051 AND ( ( Recinfo.CHECKIN_STATUS = p_CHECKIN_STATUS)
1052 OR ( ( Recinfo.CHECKIN_STATUS IS NULL )
1053 AND ( p_CHECKIN_STATUS IS NULL )))
1054 AND ( ( Recinfo.SUPPLIER_WARRANTY_EXP_DATE = p_SUPPLIER_WARRANTY_EXP_DATE)
1055 OR ( ( Recinfo.SUPPLIER_WARRANTY_EXP_DATE IS NULL )
1056 AND ( p_SUPPLIER_WARRANTY_EXP_DATE IS NULL )))
1057 AND ( ( Recinfo.EQUIPMENT_GEN_OBJECT_ID = p_EQUIPMENT_GEN_OBJECT_ID)
1058 OR ( ( Recinfo.EQUIPMENT_GEN_OBJECT_ID IS NULL )
1059 AND ( p_EQUIPMENT_GEN_OBJECT_ID IS NULL )))
1060 */
1061 ) THEN
1062 RETURN;
1063 ELSE
1064 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
1065 APP_EXCEPTION.RAISE_EXCEPTION;
1066 END IF;
1067 end lock_row;
1068
1069 PROCEDURE CREATE_ASSET(
1070 P_API_VERSION IN NUMBER
1071 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
1072 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
1073 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1074 ,P_INVENTORY_ITEM_ID NUMBER
1075 ,P_SERIAL_NUMBER VARCHAR2
1076 ,P_INSTANCE_NUMBER VARCHAR2
1077 ,P_INSTANCE_DESCRIPTION VARCHAR2
1078 ,P_ORGANIZATION_ID NUMBER
1079 ,P_CATEGORY_ID NUMBER DEFAULT NULL
1080 ,P_PN_LOCATION_ID NUMBER DEFAULT NULL
1081 ,P_FA_ASSET_ID NUMBER DEFAULT NULL
1082 ,P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL
1083 ,P_ASSET_CRITICALITY_CODE VARCHAR2 DEFAULT NULL
1084 ,P_MAINTAINABLE_FLAG VARCHAR2 DEFAULT NULL
1085 ,P_NETWORK_ASSET_FLAG VARCHAR2 DEFAULT NULL
1086 ,P_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL
1087 ,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
1088 ,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
1089 ,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
1090 ,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
1091 ,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
1092 ,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
1093 ,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
1094 ,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
1095 ,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
1096 ,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
1097 ,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
1098 ,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
1099 ,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
1100 ,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
1101 ,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL
1102 ,P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL
1103 ,P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL
1104 ,P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL
1105 ,P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL
1106 ,P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL
1107 ,P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL
1108 ,P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL
1109 ,P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL
1110 ,P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL
1111 ,P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL
1112 ,P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL
1113 ,P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL
1114 ,P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL
1115 ,P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL
1116 ,P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL
1117 ,P_REQUEST_ID NUMBER DEFAULT NULL
1118 ,P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
1119 ,P_PROGRAM_ID NUMBER DEFAULT NULL
1120 ,P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
1121 ,P_LAST_UPDATE_DATE DATE
1122 ,P_LAST_UPDATED_BY NUMBER
1123 ,P_CREATION_DATE DATE
1124 ,P_CREATED_BY NUMBER
1125 ,P_LAST_UPDATE_LOGIN NUMBER
1126 ,p_active_start_date DATE DEFAULT NULL
1127 ,p_active_end_date DATE DEFAULT NULL
1128 ,p_location NUMBER DEFAULT NULL
1129 ,p_linear_location_id NUMBER DEFAULT NULL
1130 ,p_operational_log_flag VARCHAR2 DEFAULT NULL
1131 ,p_checkin_status NUMBER DEFAULT NULL
1132 ,p_supplier_warranty_exp_date DATE DEFAULT NULL
1133 ,p_equipment_gen_object_id NUMBER DEFAULT NULL
1134 ,p_owning_department_id NUMBER DEFAULT NULL
1135 ,p_accounting_class_code VARCHAR2 DEFAULT NULL
1136 ,p_area_id NUMBER DEFAULT NULL
1137 ,X_OBJECT_ID OUT NOCOPY NUMBER
1138 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
1139 ,X_MSG_COUNT OUT NOCOPY NUMBER
1140 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1141 )
1142 is
1143 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
1144 l_api_version CONSTANT NUMBER := 1.0;
1145 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1146 l_count number := 0;
1147 l_x_asset_return_status varchar2(3);
1148 l_x_asset_msg_count number;
1149 l_x_asset_msg_data varchar2(20000);
1150 l_x_maint_return_status varchar2(3);
1151 l_x_maint_msg_count number;
1152 l_x_maint_msg_data varchar2(20000);
1153 l_mfg_serial_number_flag varchar2(1);
1154 l_instance_id number;
1155 begin
1156 -- Standard Start of API savepoint
1157 SAVEPOINT create_asset;
1158
1159 -- Standard call to check for call compatibility.
1160 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1161 RAISE fnd_api.g_exc_unexpected_error;
1162 END IF;
1163
1164 -- Initialize message list if p_init_msg_list is set to TRUE.
1165 IF fnd_api.to_boolean(p_init_msg_list) THEN
1166 fnd_msg_pub.initialize;
1167 END IF;
1168
1169 -- Initialize API return status to success
1170 x_return_status := fnd_api.g_ret_sts_success;
1171
1172 -- API body
1173 begin
1174 select 1
1175 into l_count
1176 from mtl_serial_numbers
1177 where inventory_item_id = p_inventory_item_id
1178 and serial_number = p_serial_number
1179 and rownum <= 1;
1180
1181 exception
1182 when no_data_found then
1183 l_count := 0;
1184 end;
1185
1186 if l_count = 0 then
1187 l_mfg_serial_number_flag := 'N';
1188 else
1189 l_mfg_serial_number_flag := 'Y';
1190 end if;
1191
1192 insert_row(
1193 P_API_VERSION => P_API_VERSION,
1194 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1195 P_SERIAL_NUMBER => P_SERIAL_NUMBER,
1196 P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
1197 P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
1198 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
1199 P_CATEGORY_ID => P_CATEGORY_ID,
1200 P_PN_LOCATION_ID => P_PN_LOCATION_ID,
1201 P_FA_ASSET_ID => P_FA_ASSET_ID,
1202 P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
1203 P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
1204 P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
1205 P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
1206 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1207 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1208 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1209 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1210 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1211 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1212 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1213 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1214 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1215 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1216 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1217 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1218 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1219 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1220 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1221 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1222 P_ATTRIBUTE16 => P_ATTRIBUTE16,
1223 P_ATTRIBUTE17 => P_ATTRIBUTE17,
1224 P_ATTRIBUTE18 => P_ATTRIBUTE18,
1225 P_ATTRIBUTE19 => P_ATTRIBUTE19,
1226 P_ATTRIBUTE20 => P_ATTRIBUTE20,
1227 P_ATTRIBUTE21 => P_ATTRIBUTE21,
1228 P_ATTRIBUTE22 => P_ATTRIBUTE22,
1229 P_ATTRIBUTE23 => P_ATTRIBUTE23,
1230 P_ATTRIBUTE24 => P_ATTRIBUTE24,
1231 P_ATTRIBUTE25 => P_ATTRIBUTE25,
1232 P_ATTRIBUTE26 => P_ATTRIBUTE26,
1233 P_ATTRIBUTE27 => P_ATTRIBUTE27,
1234 P_ATTRIBUTE28 => P_ATTRIBUTE28,
1235 P_ATTRIBUTE29 => P_ATTRIBUTE29,
1236 P_ATTRIBUTE30 => P_ATTRIBUTE30,
1237 P_REQUEST_ID => P_REQUEST_ID,
1238 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
1239 P_PROGRAM_ID => P_PROGRAM_ID,
1240 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
1241 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
1242 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
1243 P_CREATION_DATE => P_CREATION_DATE,
1244 P_CREATED_BY => P_CREATED_BY,
1245 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1246 p_active_start_date => p_active_start_date,
1247 p_active_end_date => p_active_end_date,
1248 p_location => p_location,
1249 p_linear_location_id => p_linear_location_id,
1250 p_operational_log_flag => p_operational_log_flag,
1251 p_checkin_status => p_checkin_status,
1252 p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
1253 p_equipment_gen_object_id => p_equipment_gen_object_id,
1254 p_mfg_serial_number_flag => l_mfg_serial_number_flag,
1255 X_OBJECT_ID => l_instance_id
1256 ,X_RETURN_STATUS => l_x_asset_return_status
1257 ,X_MSG_COUNT => l_x_asset_msg_count
1258 ,X_MSG_DATA => l_x_asset_msg_data
1259 );
1260 x_return_status := l_x_asset_return_status;
1261 x_msg_count := l_x_asset_msg_count;
1262 x_msg_data := l_x_asset_msg_data;
1263 if (l_x_asset_return_status <> FND_API.G_RET_STS_SUCCESS) then
1264 RAISE FND_API.G_EXC_ERROR ;
1265 else
1266 if (p_owning_department_id is not null OR p_accounting_class_code is not null OR p_area_id is not null) then
1267 EAM_MAINT_ATTRIBUTES_PUB.create_maint_attributes(
1268 p_api_version => p_api_version
1269 ,p_instance_id => l_instance_id
1270 ,p_owning_department_id => p_owning_department_id
1271 ,p_accounting_class_code => p_accounting_class_code
1272 ,p_area_id => p_area_id
1273 ,p_parent_instance_id => null
1274 ,x_return_status => l_x_maint_return_status
1275 ,x_msg_count => l_x_maint_msg_count
1276 ,x_msg_data => l_x_maint_msg_data
1277 );
1278 x_return_status := l_x_maint_return_status;
1279 x_msg_count := l_x_maint_msg_count;
1280 x_msg_data := l_x_maint_msg_data;
1281 end if;
1282
1283 end if;
1284
1285 x_object_id := l_instance_id;
1286
1287 -- End of API body.
1288 -- Standard check of p_commit.
1289 IF fnd_api.to_boolean(p_commit) THEN
1290 COMMIT WORK;
1291 END IF;
1292
1293 -- Standard call to get message count and if count is 1, get message info.
1294 fnd_msg_pub.count_and_get(
1295 p_count => x_msg_count
1296 ,p_data => x_msg_data);
1297
1298
1299 EXCEPTION
1300 WHEN fnd_api.g_exc_error THEN
1301 ROLLBACK TO create_asset;
1302 x_return_status := fnd_api.g_ret_sts_error;
1303 /*fnd_msg_pub.count_and_get(
1304 p_count => x_msg_count
1305 ,p_data => x_msg_data);*/
1306 WHEN fnd_api.g_exc_unexpected_error THEN
1307 ROLLBACK TO create_asset;
1308 x_return_status := fnd_api.g_ret_sts_unexp_error;
1309 /*fnd_msg_pub.count_and_get(
1310 p_count => x_msg_count
1311 ,p_data => x_msg_data);*/
1312 WHEN OTHERS THEN
1313 ROLLBACK TO create_asset;
1314 x_return_status := fnd_api.g_ret_sts_unexp_error;
1315
1316 IF fnd_msg_pub.check_msg_level(
1317 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1318 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1319 END IF;
1320
1321 /*fnd_msg_pub.count_and_get(
1322 p_count => x_msg_count
1323 ,p_data => x_msg_data);*/
1324
1325 end create_asset;
1326
1327 procedure update_asset(
1328 P_API_VERSION IN NUMBER
1329 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
1330 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
1331 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1332 ,p_instance_id IN NUMBER
1333 ,P_INSTANCE_DESCRIPTION VARCHAR2
1334 ,P_INVENTORY_ITEM_ID NUMBER
1335 ,P_SERIAL_NUMBER VARCHAR2
1336 ,P_ORGANIZATION_ID NUMBER
1337 ,P_CATEGORY_ID NUMBER
1338 ,P_PN_LOCATION_ID NUMBER
1339 ,P_FA_ASSET_ID NUMBER
1340 ,P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL
1341 ,P_ASSET_CRITICALITY_CODE VARCHAR2
1342 ,P_MAINTAINABLE_FLAG VARCHAR2
1343 ,P_NETWORK_ASSET_FLAG VARCHAR2
1344 ,P_ATTRIBUTE_CATEGORY VARCHAR2
1345 ,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
1346 ,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
1347 ,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
1348 ,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
1349 ,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
1350 ,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
1351 ,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
1352 ,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
1353 ,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
1354 ,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
1355 ,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
1356 ,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
1357 ,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
1358 ,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
1359 ,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL
1360 ,P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL
1361 ,P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL
1362 ,P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL
1363 ,P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL
1364 ,P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL
1365 ,P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL
1366 ,P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL
1367 ,P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL
1368 ,P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL
1369 ,P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL
1370 ,P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL
1371 ,P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL
1372 ,P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL
1373 ,P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL
1374 ,P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL
1375 ,P_REQUEST_ID NUMBER DEFAULT NULL
1376 ,P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
1377 ,P_PROGRAM_ID NUMBER DEFAULT NULL
1378 ,P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
1379 ,P_LAST_UPDATE_DATE DATE
1380 ,P_LAST_UPDATED_BY NUMBER
1381 ,P_LAST_UPDATE_LOGIN NUMBER
1382 ,P_FROM_PUBLIC_API VARCHAR2 DEFAULT 'Y'
1383 ,P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL
1384 ,P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL
1385 ,P_LOCATION_ID NUMBER DEFAULT NULL
1386 ,p_active_end_date DATE DEFAULT NULL
1387 ,p_linear_location_id NUMBER DEFAULT NULL
1388 ,p_operational_log_flag VARCHAR2 DEFAULT NULL
1389 ,p_checkin_status NUMBER DEFAULT NULL
1390 ,p_supplier_warranty_exp_date DATE DEFAULT NULL
1391 ,p_equipment_gen_object_id NUMBER DEFAULT NULL
1392 ,p_owning_department_id NUMBER DEFAULT NULL
1393 ,p_accounting_class_code VARCHAR2 DEFAULT NULL
1394 ,p_area_id NUMBER DEFAULT NULL
1395 ,p_reactivate_asset VARCHAR2 DEFAULT 'N'
1396 ,p_disassociate_fa_flag VARCHAR2 DEFAULT 'N'
1397 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
1398 ,X_MSG_COUNT OUT NOCOPY NUMBER
1399 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1400 )
1401 IS
1402 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
1403 l_api_version CONSTANT NUMBER := 1.0;
1404 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1405 l_count number := 0;
1406 l_x_asset_return_status varchar2(3);
1407 l_x_asset_msg_count number;
1408 l_x_asset_msg_data varchar2(20000);
1409 l_x_maint_return_status varchar2(3);
1410 l_x_maint_msg_count number;
1411 l_x_maint_msg_data varchar2(20000);
1412 l_mfg_serial_number_flag varchar2(1);
1413 l_instance_id number;
1414 l_current_status number;
1415 l_owning_department_id number;
1416 l_accounting_class_code varchar2(10);
1417 l_area_id number;
1418 cursor associations is
1419 select activity_association_id from mtl_eam_asset_activities WHERE maintenance_object_id=l_instance_id; -- bug 10093296
1420 BEGIN
1421 -- Standard Start of API savepoint
1422 SAVEPOINT update_asset;
1423
1424 -- Standard call to check for call compatibility.
1425 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1426 RAISE fnd_api.g_exc_unexpected_error;
1427 END IF;
1428
1429 -- Initialize message list if p_init_msg_list is set to TRUE.
1430 IF fnd_api.to_boolean(p_init_msg_list) THEN
1431 fnd_msg_pub.initialize;
1432 END IF;
1433
1434 -- Initialize API return status to success
1435 x_return_status := fnd_api.g_ret_sts_success;
1436
1437 -- API body
1438 begin
1439 select 1,current_status
1440 into l_count,l_current_status
1441 from mtl_serial_numbers
1442 where inventory_item_id = p_inventory_item_id
1443 and serial_number = p_serial_number
1444 and rownum <= 1;
1445
1446 exception
1447 when no_data_found then
1448 l_count := 0;
1449 end;
1450
1451 if (l_count = 0 OR (l_count = 1 AND nvl(l_current_status,1) = 1))then
1452 l_mfg_serial_number_flag := 'N';
1453
1454 insert_row(
1455 P_API_VERSION => P_API_VERSION,
1456 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1457 P_SERIAL_NUMBER => P_SERIAL_NUMBER,
1458 P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
1459 P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
1460 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
1461 P_CATEGORY_ID => P_CATEGORY_ID,
1462 P_PN_LOCATION_ID => P_PN_LOCATION_ID,
1463 P_FA_ASSET_ID => P_FA_ASSET_ID,
1464 P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
1465 P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
1466 P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
1467 P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
1468 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1469 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1470 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1471 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1472 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1473 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1474 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1475 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1476 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1477 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1478 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1479 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1480 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1481 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1482 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1483 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1484 P_ATTRIBUTE16 => P_ATTRIBUTE16,
1485 P_ATTRIBUTE17 => P_ATTRIBUTE17,
1486 P_ATTRIBUTE18 => P_ATTRIBUTE18,
1487 P_ATTRIBUTE19 => P_ATTRIBUTE19,
1488 P_ATTRIBUTE20 => P_ATTRIBUTE20,
1489 P_ATTRIBUTE21 => P_ATTRIBUTE21,
1490 P_ATTRIBUTE22 => P_ATTRIBUTE22,
1491 P_ATTRIBUTE23 => P_ATTRIBUTE23,
1492 P_ATTRIBUTE24 => P_ATTRIBUTE24,
1493 P_ATTRIBUTE25 => P_ATTRIBUTE25,
1494 P_ATTRIBUTE26 => P_ATTRIBUTE26,
1495 P_ATTRIBUTE27 => P_ATTRIBUTE27,
1496 P_ATTRIBUTE28 => P_ATTRIBUTE28,
1497 P_ATTRIBUTE29 => P_ATTRIBUTE29,
1498 P_ATTRIBUTE30 => P_ATTRIBUTE30,
1499 P_REQUEST_ID => P_REQUEST_ID,
1500 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
1501 P_PROGRAM_ID => P_PROGRAM_ID,
1502 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
1503 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
1504 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
1505 P_CREATION_DATE => P_LAST_UPDATE_DATE,
1506 P_CREATED_BY => P_LAST_UPDATED_BY,
1507 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1508 p_active_start_date => sysdate,
1509 p_active_end_date => p_active_end_date,
1510 p_location => p_location_id,
1511 p_linear_location_id => p_linear_location_id,
1512 p_operational_log_flag => p_operational_log_flag,
1513 p_checkin_status => p_checkin_status,
1514 p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
1515 p_equipment_gen_object_id => p_equipment_gen_object_id,
1516 p_mfg_serial_number_flag => l_mfg_serial_number_flag,
1517 X_OBJECT_ID => l_instance_id
1518 ,X_RETURN_STATUS => l_x_asset_return_status
1519 ,X_MSG_COUNT => l_x_asset_msg_count
1520 ,X_MSG_DATA => l_x_asset_msg_data
1521 );
1522 else
1523 l_mfg_serial_number_flag := 'Y';
1524 l_instance_id := p_instance_id;
1525 update_row
1526 (
1527 P_API_VERSION => P_API_VERSION,
1528 p_instance_id => P_INSTANCE_ID,
1529 P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
1530 P_CATEGORY_ID => P_CATEGORY_ID,
1531 P_PN_LOCATION_ID => P_PN_LOCATION_ID,
1532 P_FA_ASSET_ID => P_FA_ASSET_ID,
1533 P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
1534 P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
1535 P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
1536 P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
1537 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1538 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1539 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1540 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1541 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1542 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1543 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1544 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1545 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1546 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1547 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1548 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1549 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1550 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1551 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1552 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1553 P_ATTRIBUTE16 => P_ATTRIBUTE16,
1554 P_ATTRIBUTE17 => P_ATTRIBUTE17,
1555 P_ATTRIBUTE18 => P_ATTRIBUTE18,
1556 P_ATTRIBUTE19 => P_ATTRIBUTE19,
1557 P_ATTRIBUTE20 => P_ATTRIBUTE20,
1558 P_ATTRIBUTE21 => P_ATTRIBUTE21,
1559 P_ATTRIBUTE22 => P_ATTRIBUTE22,
1560 P_ATTRIBUTE23 => P_ATTRIBUTE23,
1561 P_ATTRIBUTE24 => P_ATTRIBUTE24,
1562 P_ATTRIBUTE25 => P_ATTRIBUTE25,
1563 P_ATTRIBUTE26 => P_ATTRIBUTE26,
1564 P_ATTRIBUTE27 => P_ATTRIBUTE27,
1565 P_ATTRIBUTE28 => P_ATTRIBUTE28,
1566 P_ATTRIBUTE29 => P_ATTRIBUTE29,
1567 P_ATTRIBUTE30 => P_ATTRIBUTE30,
1568 P_REQUEST_ID => P_REQUEST_ID,
1569 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
1570 P_PROGRAM_ID => P_PROGRAM_ID,
1571 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
1572 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
1573 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
1574 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1575 P_FROM_PUBLIC_API => P_FROM_PUBLIC_API ,
1576 P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
1577 P_LOCATION_TYPE_CODE => P_LOCATION_TYPE_CODE,
1578 P_LOCATION_ID => P_LOCATION_ID,
1579 p_active_end_date => p_active_end_date,
1580 p_linear_location_id => p_linear_location_id,
1581 p_operational_log_flag => p_operational_log_flag,
1582 p_checkin_status => p_checkin_status,
1583 p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
1584 p_equipment_gen_object_id => p_equipment_gen_object_id
1585 ,p_reactivate_asset => p_reactivate_asset
1586 ,p_disassociate_fa_flag => p_disassociate_fa_flag
1587 ,X_RETURN_STATUS => l_x_asset_return_status
1588 ,X_MSG_COUNT => l_x_asset_msg_count
1589 ,X_MSG_DATA => l_x_asset_msg_data
1590 );
1591 end if;
1592
1593 x_return_status := l_x_asset_return_status;
1594 x_msg_count := l_x_asset_msg_count;
1595 x_msg_data := l_x_asset_msg_data;
1596
1597 if (l_x_asset_return_status <> FND_API.G_RET_STS_SUCCESS) then
1598 RAISE FND_API.G_EXC_ERROR ;
1599 else
1600 --if (p_owning_department_id is not null OR p_accounting_class_code is not null OR p_area_id is not null) then
1601
1602 /* bug 5177526 : Need to pass maint org id */
1603 SELECT maint_organization_id INTO l_count
1604 FROM mtl_parameters
1605 WHERE organization_id = p_organization_id;
1606
1607 if (p_from_public_api = 'N') then
1608 l_owning_department_id := nvl(p_owning_department_id,FND_API.G_MISS_NUM);
1609 l_accounting_class_code := nvl(p_accounting_class_code,FND_API.G_MISS_CHAR);
1610 l_area_id := nvl(p_area_id,FND_API.G_MISS_NUM);
1611 else
1612 l_owning_department_id := p_owning_department_id;
1613 l_accounting_class_code := p_accounting_class_code;
1614 l_area_id := p_area_id;
1615 end if;
1616
1617 EAM_ORG_MAINT_DEFAULTS_PVT.update_insert_row(
1618 p_api_version => p_api_version
1619 ,p_object_type => 50
1620 ,p_object_id => l_instance_id
1621 ,p_organization_id => l_count
1622 ,p_owning_department_id => l_owning_department_id
1623 ,p_accounting_class_code => l_accounting_class_code
1624 ,p_area_id => l_area_id
1625 ,x_return_status => l_x_maint_return_status
1626 ,x_msg_count => l_x_maint_msg_count
1627 ,x_msg_data => l_x_maint_msg_data
1628 );
1629 x_return_status := l_x_maint_return_status;
1630 x_msg_count := l_x_maint_msg_count;
1631 x_msg_data := l_x_maint_msg_data;
1632
1633 /*bug 10093296-update owning department for activity associations... start*/
1634 if (l_x_maint_return_status = FND_API.G_RET_STS_SUCCESS) then
1635 for association in associations loop
1636 EAM_ORG_MAINT_DEFAULTS_PVT.update_insert_row(
1637 p_api_version => p_api_version
1638 ,p_object_type => 60
1639 ,p_object_id => association.activity_association_id
1640 ,p_organization_id => l_count
1641 ,p_owning_department_id => l_owning_department_id
1642 ,x_return_status => l_x_maint_return_status
1643 ,x_msg_count => l_x_maint_msg_count
1644 ,x_msg_data => l_x_maint_msg_data
1645 );
1646 x_return_status := l_x_maint_return_status;
1647 x_msg_count := l_x_maint_msg_count;
1648 x_msg_data := l_x_maint_msg_data;
1649 end loop;
1650 end if;
1651 /*bug 10093296 ... end*/
1652
1653 end if;
1654
1655 -- End of API body.
1656 -- Standard check of p_commit.
1657 IF fnd_api.to_boolean(p_commit) THEN
1658 COMMIT WORK;
1659 END IF;
1660
1661
1662 -- Standard call to get message count and if count is 1, get message info.
1663 fnd_msg_pub.count_and_get(
1664 p_count => x_msg_count
1665 ,p_data => x_msg_data);
1666
1667
1668 EXCEPTION
1669 WHEN fnd_api.g_exc_error THEN
1670 ROLLBACK TO update_asset;
1671 x_return_status := fnd_api.g_ret_sts_error;
1672 /*fnd_msg_pub.count_and_get(
1673 p_count => x_msg_count
1674 ,p_data => x_msg_data);*/
1675 WHEN fnd_api.g_exc_unexpected_error THEN
1676 ROLLBACK TO update_asset;
1677 x_return_status := fnd_api.g_ret_sts_unexp_error;
1678 /*fnd_msg_pub.count_and_get(
1679 p_count => x_msg_count
1680 ,p_data => x_msg_data);*/
1681 WHEN OTHERS THEN
1682 ROLLBACK TO update_asset;
1683 x_return_status := fnd_api.g_ret_sts_unexp_error;
1684
1685 IF fnd_msg_pub.check_msg_level(
1686 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1687 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1688 END IF;
1689
1690 /*fnd_msg_pub.count_and_get(
1691 p_count => x_msg_count
1692 ,p_data => x_msg_data);*/
1693
1694
1695 end update_asset;
1696
1697 PROCEDURE SERIAL_CHECK
1698 ( p_api_version IN NUMBER,
1699 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1700 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1701 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1702 x_return_status OUT NOCOPY VARCHAR2,
1703 x_msg_count OUT NOCOPY NUMBER,
1704 x_msg_data OUT NOCOPY VARCHAR2,
1705 x_errorcode OUT NOCOPY NUMBER,
1706 x_ser_num_in_item_id OUT NOCOPY boolean,
1707 p_INVENTORY_ITEM_ID IN NUMBER,
1708 p_SERIAL_NUMBER IN VARCHAR2,
1709 p_ORGANIZATION_ID IN NUMBER
1710 ) IS
1711 l_api_name CONSTANT VARCHAR2(30) := 'serial_check';
1712 l_api_version CONSTANT NUMBER := 1.0;
1713 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1714 l_serial_number_type number;
1715 l_count number;
1716 BEGIN
1717
1718 -- Standard Start of API savepoint
1719 SAVEPOINT serial_check;
1720
1721 -- Standard call to check for call compatibility.
1722 IF NOT fnd_api.compatible_api_call(
1723 l_api_version
1724 ,p_api_version
1725 ,l_api_name
1726 ,g_pkg_name) THEN
1727 RAISE fnd_api.g_exc_unexpected_error;
1728 END IF;
1729
1730 -- Initialize message list if p_init_msg_list is set to TRUE.
1731 IF fnd_api.to_boolean(p_init_msg_list) THEN
1732 fnd_msg_pub.initialize;
1733 END IF;
1734
1735 -- Initialize API return status to success
1736 x_return_status := fnd_api.g_ret_sts_success;
1737
1738 -- API body
1739
1740 -- added to fix bug 2446341
1741 -- get serial_number_type and pass it to mtl_serial_check.SNUniqueCheck
1742
1743 x_ser_num_in_item_id := FALSE;
1744 select serial_number_type
1745 into l_serial_number_type
1746 from mtl_parameters
1747 where organization_id = p_organization_id;
1748
1749 mtl_serial_check.SNUniqueCheck(
1750 p_api_version => 0.9,
1751 x_return_status => x_return_status,
1752 x_errorcode => x_errorcode,
1753 x_msg_count => x_msg_count,
1754 x_msg_data => x_msg_data,
1755 p_org_id => p_organization_id,
1756 p_serial_number_type => l_serial_number_type,
1757 p_serial_number => p_Serial_number);
1758
1759
1760 /*IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1761 RAISE FND_API.G_EXC_ERROR ;
1762 END IF;
1763 */
1764
1765 -- check if serial number exists within asset group
1766 select count(1) into l_count
1767 from mtl_serial_numbers
1768 where inventory_item_id = p_inventory_item_id
1769 and serial_number = p_serial_number;
1770
1771 if l_count > 0 then
1772 x_return_status := FND_API.G_RET_STS_ERROR;
1773 x_ser_num_in_item_id := TRUE;
1774 else
1775 x_ser_num_in_item_id := FALSE;
1776 end if;
1777
1778
1779
1780 -- End of API body.
1781 -- Standard check of p_commit.
1782 IF fnd_api.to_boolean(p_commit) THEN
1783 COMMIT WORK;
1784 END IF;
1785
1786 -- Standard call to get message count and if count is 1, get message info.
1787 fnd_msg_pub.count_and_get(
1788 p_count => x_msg_count
1789 ,p_data => x_msg_data);
1790
1791
1792 EXCEPTION
1793 WHEN fnd_api.g_exc_error THEN
1794 ROLLBACK TO serial_check;
1795 x_return_status := fnd_api.g_ret_sts_error;
1796 fnd_msg_pub.count_and_get(
1797 p_count => x_msg_count
1798 ,p_data => x_msg_data);
1799 WHEN fnd_api.g_exc_unexpected_error THEN
1800 ROLLBACK TO serial_check;
1801 x_return_status := fnd_api.g_ret_sts_unexp_error;
1802 fnd_msg_pub.count_and_get(
1803 p_count => x_msg_count
1804 ,p_data => x_msg_data);
1805 WHEN OTHERS THEN
1806 ROLLBACK TO serial_check;
1807 x_return_status := fnd_api.g_ret_sts_unexp_error;
1808
1809 IF fnd_msg_pub.check_msg_level(
1810 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1811 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1812 END IF;
1813
1814 fnd_msg_pub.count_and_get(
1815 p_count => x_msg_count
1816 ,p_data => x_msg_data);
1817
1818 END SERIAL_CHECK;
1819
1820 procedure find_assets(
1821 p_organization_id number
1822 ,p_inventory_item_id number
1823 ,p_instance_id number
1824 ,p_category_id number
1825 ,P_PN_LOCATION_ID NUMBER,
1826 P_EAM_LOCATION_ID NUMBER,
1827 P_FA_ASSET_ID NUMBER,
1828 P_ASSET_CRITICALITY_CODE VARCHAR2,
1829 P_WIP_ACCOUNTING_CLASS_CODE VARCHAR2,
1830 P_MAINTAINABLE_FLAG VARCHAR2,
1831 P_OWNING_DEPARTMENT_ID NUMBER,
1832 P_PROD_ORGANIZATION_ID NUMBER,
1833 P_EQUIPMENT_ITEM_ID NUMBER,
1834 P_EQP_SERIAL_NUMBER VARCHAR2
1835 ,p_eam_item_type NUMBER
1836 ,p_asset_category_id NUMBER
1837 ) is
1838 cursor asset_cur is
1839 select cii.serial_number,
1840 cii.instance_number,
1841 cii.inventory_item_id,
1842 msn.gen_object_id
1843 from csi_item_instances cii,
1844 mtl_serial_numbers msn,
1845 fa_additions fa,
1846 csi_i_assets cia,
1847 mtl_system_items msi,
1848 mtl_parameters mp,
1849 eam_org_maint_defaults eomd,
1850 mtl_system_items msi_prod,
1851 mtl_serial_numbers msn_prod,
1852 mtl_parameters mp_prod
1853 where cii.last_vld_organization_id = msn.current_organization_id
1854 and cii.inventory_item_id=msn.inventory_item_id
1855 and cii.serial_number=msn.serial_number
1856 and cii.last_vld_organization_id = p_organization_id
1857 and nvl(cii.network_asset_flag,'N') = 'N'
1858 and msi.eam_item_type=p_eam_item_type
1859 and msi.inventory_item_id = cii.inventory_item_id
1860 and msi.organization_id = cii.last_vld_organization_id
1861 and cii.instance_id = cia.instance_id(+)
1862 and cia.fa_asset_id = fa.asset_id(+)
1863 and mp.organization_id = cii.last_vld_organization_id
1864 and cii.instance_id = eomd.object_id (+)
1865 and eomd.object_type(+) = 50
1866 and eomd.organization_id(+) = cii.last_vld_organization_id --mp.maint_organization_id
1867 and cii.equipment_gen_object_id = msn_prod.gen_object_id(+)
1868 and msn_prod.current_organization_id = msi_prod.organization_id(+)
1869 and msn_prod.inventory_item_id = msi_prod.inventory_item_id(+)
1870 and msi_prod.organization_id = mp_prod.organization_id(+)
1871 and msi_prod.equipment_type(+) = 1
1872 and (p_inventory_item_id is null or p_inventory_item_id = cii.inventory_item_id)
1873 and (p_instance_id is null or p_instance_id = cii.instance_id)
1874 and (p_category_id is null or p_category_id = cii.category_id)
1875 and (P_PN_LOCATION_ID is null or P_PN_LOCATION_ID = cii.pn_location_id)
1876 and (P_EAM_LOCATION_ID is null or P_EAM_LOCATION_ID= eomd.area_id)
1877 and (P_FA_ASSET_ID is null or P_FA_ASSET_ID = fa.asset_id)
1878 and (P_ASSET_CRITICALITY_CODE is null or P_ASSET_CRITICALITY_CODE = cii.asset_criticality_code)
1879 and (P_WIP_ACCOUNTING_CLASS_CODE is null or P_WIP_ACCOUNTING_CLASS_CODE = eomd.accounting_class_code)
1880 and (P_MAINTAINABLE_FLAG is null or P_MAINTAINABLE_FLAG = cii.maintainable_flag)
1881 and (P_OWNING_DEPARTMENT_ID is null or P_OWNING_DEPARTMENT_ID = eomd.OWNING_DEPARTMENT_ID)
1882 and (P_PROD_ORGANIZATION_ID is null or P_PROD_ORGANIZATION_ID = msn_prod.CURRENT_ORGANIZATION_ID)
1883 and (P_EQUIPMENT_ITEM_ID is null or P_EQUIPMENT_ITEM_ID = msi_prod.INVENTORY_ITEM_ID)
1884 and (P_EQP_SERIAL_NUMBER is null or P_EQP_SERIAL_NUMBER = decode(msi_prod.equipment_type,null,null,1,msn_prod.serial_number,null))
1885 and (P_ASSET_CATEGORY_ID is null or p_ASSET_CATEGORY_ID = fa.asset_category_id)
1886 ;
1887 begin
1888 commit;
1889 for asset in asset_cur
1890 loop
1891 begin
1892 INSERT INTO EAM_ASSET_RESULTS_GTT(gen_object_id)
1893 VALUES (asset.gen_object_id);
1894 exception
1895
1896 when DUP_VAL_ON_INDEX then
1897 null;
1898 when others then
1899 RAISE;
1900 end;
1901
1902 begin
1903 insert into EAM_ASSET_RESULTS_GTT (gen_object_id)
1904 (select object_id from mtl_object_genealogy
1905 where genealogy_type = 5
1906 and (sysdate between NVL(start_date_active,sysdate-1) and NVL(end_date_active,sysdate+1))
1907 start with parent_object_id = asset.gen_object_id
1908 connect by parent_object_id = prior object_id
1909 and prior genealogy_type = 5
1910 and sysdate between NVL(prior start_date_active,sysdate-1) and NVL(prior end_date_active,sysdate+1)
1911 );
1912 exception
1913 when DUP_VAL_ON_INDEX then
1914 null;
1915 when others then
1916 RAISE;
1917 end;
1918
1919 end loop;
1920 end find_assets;
1921 END EAM_ASSET_NUMBER_PVT;