[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_NUMBER_PVT
Source
1 PACKAGE BODY EAM_ASSET_NUMBER_PVT as
2 /* $Header: EAMVASNB.pls 120.25.12010000.2 2008/12/11 11:39:46 dsingire 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 select instance_asset_id, object_version_number
639 into l_asset_assignment_tbl(1).instance_asset_id,l_asset_assignment_tbl(1).object_version_number
640 from csi_i_assets
641 where instance_id = p_instance_id
642 and fa_asset_id = fa_asset_id;
643 exception
644 when no_data_found then
645 null;
646 end;
647
648 if P_FA_SYNC_FLAG = 'Y' then
649 l_asset_assignment_tbl(1).fa_sync_flag := 'Y';
650 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_TRUE;
651 else
652 l_asset_assignment_tbl(1).fa_sync_flag := 'N';
653 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_FALSE;
654 end if;
655
656 select fb.book_type_code
657 into l_asset_assignment_tbl(1).fa_book_type_code
658 from fa_books fb,
659 fa_book_controls fbc
660 where fb.asset_id = P_FA_ASSET_ID
661 and fb.book_type_code = fbc.book_type_code
662 and fbc.book_class = 'CORPORATE'
663 and rownum = 1;
664
665 select fdh.location_id
666 into l_asset_assignment_tbl(1).fa_location_id
667 from fa_distribution_history fdh
668 where asset_id = P_FA_ASSET_ID
669 and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
670 and date_ineffective is null
671 and rownum = 1;
672
673 l_asset_assignment_tbl(1).asset_quantity := 1;
674 l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
675 l_asset_assignment_tbl(1).active_end_date := null;
676 l_asset_assignment_tbl(1).parent_tbl_index := 1;
677 END IF;
678
679 --5474749 condition added
680 if nvl(p_disassociate_fa_flag,'N') = 'Y' then
681
682 select fa_asset_id into l_asset_assignment_tbl(1).fa_asset_id
683 from csi_i_assets where instance_id = p_instance_id;
684
685 begin
686 select instance_asset_id, object_version_number
687 into l_asset_assignment_tbl(1).instance_asset_id,l_asset_assignment_tbl(1).object_version_number
688 from csi_i_assets
689 where instance_id = p_instance_id
690 and fa_asset_id = l_asset_assignment_tbl(1).fa_asset_id;
691 exception
692 when no_data_found then
693 null;
694 end;
695
696 if P_FA_SYNC_FLAG = 'Y' then
697 l_asset_assignment_tbl(1).fa_sync_flag := 'Y';
698 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_TRUE;
699 else
700 l_asset_assignment_tbl(1).fa_sync_flag := 'N';
701 l_asset_assignment_tbl(1).fa_sync_validation_reqd := FND_API.G_FALSE;
702 end if;
703
704 select fb.book_type_code
705 into l_asset_assignment_tbl(1).fa_book_type_code
706 from fa_books fb,
707 fa_book_controls fbc
708 where fb.asset_id = l_asset_assignment_tbl(1).fa_asset_id
709 and fb.book_type_code = fbc.book_type_code
710 and fbc.book_class = 'CORPORATE'
711 and rownum = 1;
712
713 select fdh.location_id
714 into l_asset_assignment_tbl(1).fa_location_id
715 from fa_distribution_history fdh
716 where asset_id = l_asset_assignment_tbl(1).fa_asset_id
717 and book_type_code = l_asset_assignment_tbl(1).fa_book_type_code
718 and date_ineffective is null
719 and rownum = 1;
720
721 l_asset_assignment_tbl(1).asset_quantity := 1;
722 l_asset_assignment_tbl(1).update_status := 'IN_SERVICE';
723
724 l_asset_assignment_tbl(1).parent_tbl_index := 1;
725
726 l_asset_assignment_tbl(1).instance_id := p_instance_id;
727 l_asset_assignment_tbl(1).active_end_date := sysdate;
728
729 end if; --end if for disassociate fa flag
730
731 if (P_LOCATION_TYPE_CODE is not NULL AND P_LOCATION_TYPE_CODE <> FND_API.G_MISS_CHAR) then
732
733
734 if (p_location_type_code <> 'INVENTORY') then
735
736 select location_type_code
737 into l_old_location_type_code
738 from csi_item_instances
739 where instance_id = p_instance_id;
740
741 if (l_old_location_type_code = 'INVENTORY') then
742 FND_MESSAGE.SET_NAME('EAM', 'EAM_INVALID_LOCATION_UPDATE');
743 FND_MSG_PUB.Add;
744 RAISE FND_API.G_EXC_ERROR;
745 end if;
746 end if;
747 end if;
748
749 --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'));
750 -- Now call the stored program
751 csi_item_instance_pub.update_item_instance(
752 p_api_version => 1.0,
753 p_commit => p_commit,
754 p_init_msg_list => p_init_msg_list,
755 p_validation_level => 100,
756 p_instance_rec => l_instance_rec,
757 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
758 p_party_tbl => l_party_tbl,
759 p_account_tbl => l_party_account_tbl,
760 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
761 p_org_assignments_tbl => l_org_assignments_tbl,
762 p_asset_assignment_tbl => l_asset_assignment_tbl,
763 p_txn_rec => l_txn_rec,
764 x_instance_id_lst => l_x_instance_id_lst,
765 x_return_status => l_x_return_status,
766 x_msg_count => l_x_msg_count,
767 x_msg_data => l_x_msg_data
768 );
769 x_return_status := l_x_return_status;
770 x_msg_count := l_x_msg_count;
771 x_msg_data := l_x_msg_data;
772 IF NOT(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
773 l_msg_index := 1;
774 l_msg_count := x_msg_count;
775 WHILE l_msg_count > 0 LOOP
776 x_msg_data := FND_MSG_PUB.GET
777 ( l_msg_index,
778 FND_API.G_FALSE );
779 -- csi_gen_utility_pvt.put_line('ERROR FROM CSI_ITEM_INSTANCE_CUHK.Create_Item_Instance_Post API ');
780 -- csi_gen_utility_pvt.put_line('MESSAGE DATA = '||x_msg_data);
781 l_msg_index := l_msg_index + 1;
782 l_msg_count := l_msg_count - 1;
783 END LOOP;
784 RAISE FND_API.G_EXC_ERROR;
785 END IF;
786
787 /* Bug # 5339642 : Call Text index procedure for inserting the row in EAT */
788 eam_text_util.process_asset_update_event
789 (
790 p_event => 'UPDATE'
791 ,p_instance_id => l_instance_rec.instance_id
792 );
793
794 -- End of API body.
795 -- Standard check of p_commit.
796 IF fnd_api.to_boolean(p_commit) THEN
797 COMMIT WORK;
798 END IF;
799
800 -- Standard call to get message count and if count is 1, get message info.
801 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
802 EXCEPTION
803 WHEN fnd_api.g_exc_error THEN
804 ROLLBACK TO update_row;
805 x_return_status := fnd_api.g_ret_sts_error;
806 /*fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);*/
807 WHEN fnd_api.g_exc_unexpected_error THEN
808 ROLLBACK TO update_row;
809 x_return_status := fnd_api.g_ret_sts_unexp_error;
810 /*fnd_msg_pub.count_and_get(
811 p_count => x_msg_count
812 ,p_data => x_msg_data);*/
813 WHEN OTHERS THEN
814 ROLLBACK TO update_row;
815 x_return_status := fnd_api.g_ret_sts_unexp_error;
816
817 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
818 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
819 END IF;
820
821 /*fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data); */
822
823 end update_row;
824
825 PROCEDURE LOCK_ROW(
826
827 P_API_VERSION IN NUMBER,
828 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
829 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
830 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
831 P_ROWID VARCHAR2,
832 P_INSTANCE_ID IN NUMBER,
833 P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL,
834 P_INSTANCE_DESCRIPTION VARCHAR2,
835 P_CATEGORY_ID NUMBER,
836 P_PN_LOCATION_ID NUMBER,
837 P_FA_ASSET_ID NUMBER,
838 P_ASSET_CRITICALITY_CODE VARCHAR2,
839 P_MAINTAINABLE_FLAG VARCHAR2,
840 P_NETWORK_ASSET_FLAG VARCHAR2,
841 P_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL,
842 P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL,
843 P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL,
844 P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL,
845 P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL,
846 P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL,
847 P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL,
848 P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL,
849 P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL,
850 P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL,
851 P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL,
852 P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL,
853 P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL,
854 P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL,
855 P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL,
856 P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL,
857 P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL,
858 P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL,
859 P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL,
860 P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL,
861 P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL,
862 P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL,
863 P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL,
864 P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL,
865 P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL,
866 P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL,
867 P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL,
868 P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL,
869 P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL,
870 P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL,
871 P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL,
872 P_REQUEST_ID NUMBER DEFAULT NULL,
873 P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL,
874 P_PROGRAM_ID NUMBER DEFAULT NULL,
875 P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL,
876 P_LAST_UPDATE_DATE DATE,
877 P_LAST_UPDATED_BY NUMBER,
878 P_LAST_UPDATE_LOGIN NUMBER,
879 P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL,
880 P_LOCATION_ID NUMBER DEFAULT NULL,
881 p_linear_location_id NUMBER DEFAULT NULL,
882 p_operational_log_flag VARCHAR2 DEFAULT NULL,
883 P_checkin_status NUMBER DEFAULT NULL,
884 p_supplier_warranty_exp_date DATE DEFAULT NULL,
885 p_equipment_gen_object_id NUMBER DEFAULT NULL,
886 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
887 X_MSG_COUNT OUT NOCOPY NUMBER,
888 X_MSG_DATA OUT NOCOPY VARCHAR2
889 )
890 IS
891 CURSOR C IS
892 SELECT *
893 FROM CSI_ITEM_INSTANCES
894 WHERE INSTANCE_ID = p_INSTANCE_ID
895 FOR UPDATE of INSTANCE_ID NOWAIT;
896 Recinfo C%ROWTYPE;
897 BEGIN
898 OPEN C;
899 FETCH C INTO Recinfo;
900 IF (C%NOTFOUND) THEN
901 CLOSE C;
902 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
903 APP_EXCEPTION.RAISE_EXCEPTION;
904 END IF;
905 CLOSE C;
906
907 IF (
908 ( Recinfo.INSTANCE_ID = p_INSTANCE_ID)
909 AND ( ( Recinfo.INSTANCE_NUMBER = p_INSTANCE_NUMBER)
910 OR ( ( Recinfo.INSTANCE_NUMBER IS NULL )
911 AND ( p_INSTANCE_NUMBER IS NULL )))
912 /*
913 AND ( ( Recinfo.LOCATION_TYPE_CODE = p_LOCATION_TYPE_CODE)
914 OR ( ( Recinfo.LOCATION_TYPE_CODE IS NULL )
915 AND ( p_LOCATION_TYPE_CODE IS NULL )))
916 AND ( ( Recinfo.LOCATION_ID = p_LOCATION_ID)
917 OR ( ( Recinfo.LOCATION_ID IS NULL )
918 AND ( p_LOCATION_ID IS NULL )))
919 AND ( ( Recinfo.CONTEXT = p_ATTRIBUTE_CATEGORY)
920 OR ( ( Recinfo.CONTEXT IS NULL )
921 AND ( p_ATTRIBUTE_CATEGORY IS NULL )))
922 AND ( ( Recinfo.ATTRIBUTE1 = p_ATTRIBUTE1)
923 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
924 AND ( p_ATTRIBUTE1 IS NULL )))
925 AND ( ( Recinfo.ATTRIBUTE2 = p_ATTRIBUTE2)
926 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
927 AND ( p_ATTRIBUTE2 IS NULL )))
928 AND ( ( Recinfo.ATTRIBUTE3 = p_ATTRIBUTE3)
929 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
930 AND ( p_ATTRIBUTE3 IS NULL )))
931 AND ( ( Recinfo.ATTRIBUTE4 = p_ATTRIBUTE4)
932 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
933 AND ( p_ATTRIBUTE4 IS NULL )))
934 AND ( ( Recinfo.ATTRIBUTE5 = p_ATTRIBUTE5)
935 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
936 AND ( p_ATTRIBUTE5 IS NULL )))
937 AND ( ( Recinfo.ATTRIBUTE6 = p_ATTRIBUTE6)
938 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
939 AND ( p_ATTRIBUTE6 IS NULL )))
940 AND ( ( Recinfo.ATTRIBUTE7 = p_ATTRIBUTE7)
941 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
942 AND ( p_ATTRIBUTE7 IS NULL )))
943 AND ( ( Recinfo.ATTRIBUTE8 = p_ATTRIBUTE8)
944 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
945 AND ( p_ATTRIBUTE8 IS NULL )))
946 AND ( ( Recinfo.ATTRIBUTE9 = p_ATTRIBUTE9)
947 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
948 AND ( p_ATTRIBUTE9 IS NULL )))
949 AND ( ( Recinfo.ATTRIBUTE10 = p_ATTRIBUTE10)
950 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
951 AND ( p_ATTRIBUTE10 IS NULL )))
952 AND ( ( Recinfo.ATTRIBUTE11 = p_ATTRIBUTE11)
953 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
954 AND ( p_ATTRIBUTE11 IS NULL )))
955 AND ( ( Recinfo.ATTRIBUTE12 = p_ATTRIBUTE12)
956 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
957 AND ( p_ATTRIBUTE12 IS NULL )))
958 AND ( ( Recinfo.ATTRIBUTE13 = p_ATTRIBUTE13)
959 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
960 AND ( p_ATTRIBUTE13 IS NULL )))
961 AND ( ( Recinfo.ATTRIBUTE14 = p_ATTRIBUTE14)
962 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
963 AND ( p_ATTRIBUTE14 IS NULL )))
964 AND ( ( Recinfo.ATTRIBUTE15 = p_ATTRIBUTE15)
965 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
966 AND ( p_ATTRIBUTE15 IS NULL )))
967 AND ( ( Recinfo.ATTRIBUTE16 = p_ATTRIBUTE16)
968 OR ( ( Recinfo.ATTRIBUTE16 IS NULL )
969 AND ( p_ATTRIBUTE16 IS NULL )))
970 AND ( ( Recinfo.ATTRIBUTE17 = p_ATTRIBUTE17)
971 OR ( ( Recinfo.ATTRIBUTE17 IS NULL )
972 AND ( p_ATTRIBUTE17 IS NULL )))
973 AND ( ( Recinfo.ATTRIBUTE18 = p_ATTRIBUTE18)
974 OR ( ( Recinfo.ATTRIBUTE18 IS NULL )
975 AND ( p_ATTRIBUTE18 IS NULL )))
976 AND ( ( Recinfo.ATTRIBUTE19 = p_ATTRIBUTE19)
977 OR ( ( Recinfo.ATTRIBUTE19 IS NULL )
978 AND ( p_ATTRIBUTE19 IS NULL )))
979 AND ( ( Recinfo.ATTRIBUTE20 = p_ATTRIBUTE20)
980 OR ( ( Recinfo.ATTRIBUTE20 IS NULL )
981 AND ( p_ATTRIBUTE20 IS NULL )))
982 AND ( ( Recinfo.ATTRIBUTE21 = p_ATTRIBUTE21)
983 OR ( ( Recinfo.ATTRIBUTE21 IS NULL )
984 AND ( p_ATTRIBUTE21 IS NULL )))
985 AND ( ( Recinfo.ATTRIBUTE22 = p_ATTRIBUTE22)
986 OR ( ( Recinfo.ATTRIBUTE22 IS NULL )
987 AND ( p_ATTRIBUTE22 IS NULL )))
988 AND ( ( Recinfo.ATTRIBUTE23 = p_ATTRIBUTE23)
989 OR ( ( Recinfo.ATTRIBUTE23 IS NULL )
990 AND ( p_ATTRIBUTE23 IS NULL )))
991 AND ( ( Recinfo.ATTRIBUTE24 = p_ATTRIBUTE24)
992 OR ( ( Recinfo.ATTRIBUTE24 IS NULL )
993 AND ( p_ATTRIBUTE24 IS NULL )))
994 AND ( ( Recinfo.ATTRIBUTE25 = p_ATTRIBUTE25)
995 OR ( ( Recinfo.ATTRIBUTE25 IS NULL )
996 AND ( p_ATTRIBUTE25 IS NULL )))
997 AND ( ( Recinfo.ATTRIBUTE26 = p_ATTRIBUTE26)
998 OR ( ( Recinfo.ATTRIBUTE26 IS NULL )
999 AND ( p_ATTRIBUTE26 IS NULL )))
1000 AND ( ( Recinfo.ATTRIBUTE27 = p_ATTRIBUTE27)
1001 OR ( ( Recinfo.ATTRIBUTE27 IS NULL )
1002 AND ( p_ATTRIBUTE27 IS NULL )))
1003 AND ( ( Recinfo.ATTRIBUTE28 = p_ATTRIBUTE28)
1004 OR ( ( Recinfo.ATTRIBUTE28 IS NULL )
1005 AND ( p_ATTRIBUTE28 IS NULL )))
1006 AND ( ( Recinfo.ATTRIBUTE29 = p_ATTRIBUTE29)
1007 OR ( ( Recinfo.ATTRIBUTE29 IS NULL )
1008 AND ( p_ATTRIBUTE29 IS NULL )))
1009 AND ( ( Recinfo.ATTRIBUTE30 = p_ATTRIBUTE30)
1010 OR ( ( Recinfo.ATTRIBUTE30 IS NULL )
1011 AND ( p_ATTRIBUTE30 IS NULL )))
1012 AND ( ( Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
1013 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
1014 AND ( p_LAST_UPDATED_BY IS NULL )))
1015 AND ( ( Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
1016 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
1017 AND ( p_LAST_UPDATE_DATE IS NULL )))
1018 AND ( ( Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
1019 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
1020 AND ( p_LAST_UPDATE_LOGIN IS NULL )))
1021 AND ( ( Recinfo.INSTANCE_DESCRIPTION = p_INSTANCE_DESCRIPTION)
1022 OR ( ( Recinfo.INSTANCE_DESCRIPTION IS NULL )
1023 AND ( p_INSTANCE_DESCRIPTION IS NULL )))
1024 AND ( ( Recinfo.CATEGORY_ID = p_CATEGORY_ID)
1025 OR ( ( Recinfo.CATEGORY_ID IS NULL )
1026 AND ( p_CATEGORY_ID IS NULL )))
1027 AND ( ( Recinfo.PN_LOCATION_ID = p_PN_LOCATION_ID)
1028 OR ( ( Recinfo.PN_LOCATION_ID IS NULL )
1029 AND ( p_PN_LOCATION_ID IS NULL )))
1030 AND ( ( Recinfo.ASSET_CRITICALITY_CODE = p_ASSET_CRITICALITY_CODE)
1031 OR ( ( Recinfo.ASSET_CRITICALITY_CODE IS NULL )
1032 AND ( p_ASSET_CRITICALITY_CODE IS NULL )))
1033 AND ( ( Recinfo.MAINTAINABLE_FLAG = p_MAINTAINABLE_FLAG)
1034 OR ( ( Recinfo.MAINTAINABLE_FLAG IS NULL )
1035 AND ( p_MAINTAINABLE_FLAG IS NULL )))
1036 AND ( ( Recinfo.NETWORK_ASSET_FLAG = p_NETWORK_ASSET_FLAG)
1037 OR ( ( Recinfo.NETWORK_ASSET_FLAG IS NULL )
1038 AND ( p_NETWORK_ASSET_FLAG IS NULL )))
1039 AND ( ( Recinfo.LINEAR_LOCATION_ID = p_LINEAR_LOCATION_ID)
1040 OR ( ( Recinfo.LINEAR_LOCATION_ID IS NULL )
1041 AND ( p_LINEAR_LOCATION_ID IS NULL )))
1042 AND ( ( Recinfo.OPERATIONAL_LOG_FLAG = p_OPERATIONAL_LOG_FLAG)
1043 OR ( ( Recinfo.OPERATIONAL_LOG_FLAG IS NULL )
1044 AND ( p_OPERATIONAL_LOG_FLAG IS NULL )))
1045 AND ( ( Recinfo.CHECKIN_STATUS = p_CHECKIN_STATUS)
1046 OR ( ( Recinfo.CHECKIN_STATUS IS NULL )
1047 AND ( p_CHECKIN_STATUS IS NULL )))
1048 AND ( ( Recinfo.SUPPLIER_WARRANTY_EXP_DATE = p_SUPPLIER_WARRANTY_EXP_DATE)
1049 OR ( ( Recinfo.SUPPLIER_WARRANTY_EXP_DATE IS NULL )
1050 AND ( p_SUPPLIER_WARRANTY_EXP_DATE IS NULL )))
1051 AND ( ( Recinfo.EQUIPMENT_GEN_OBJECT_ID = p_EQUIPMENT_GEN_OBJECT_ID)
1052 OR ( ( Recinfo.EQUIPMENT_GEN_OBJECT_ID IS NULL )
1053 AND ( p_EQUIPMENT_GEN_OBJECT_ID IS NULL )))
1054 */
1055 ) THEN
1056 RETURN;
1057 ELSE
1058 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
1059 APP_EXCEPTION.RAISE_EXCEPTION;
1060 END IF;
1061 end lock_row;
1062
1063 PROCEDURE CREATE_ASSET(
1064 P_API_VERSION IN NUMBER
1065 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
1066 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
1067 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1068 ,P_INVENTORY_ITEM_ID NUMBER
1069 ,P_SERIAL_NUMBER VARCHAR2
1070 ,P_INSTANCE_NUMBER VARCHAR2
1071 ,P_INSTANCE_DESCRIPTION VARCHAR2
1072 ,P_ORGANIZATION_ID NUMBER
1073 ,P_CATEGORY_ID NUMBER DEFAULT NULL
1074 ,P_PN_LOCATION_ID NUMBER DEFAULT NULL
1075 ,P_FA_ASSET_ID NUMBER DEFAULT NULL
1076 ,P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL
1077 ,P_ASSET_CRITICALITY_CODE VARCHAR2 DEFAULT NULL
1078 ,P_MAINTAINABLE_FLAG VARCHAR2 DEFAULT NULL
1079 ,P_NETWORK_ASSET_FLAG VARCHAR2 DEFAULT NULL
1080 ,P_ATTRIBUTE_CATEGORY VARCHAR2 DEFAULT NULL
1081 ,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
1082 ,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
1083 ,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
1084 ,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
1085 ,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
1086 ,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
1087 ,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
1088 ,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
1089 ,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
1090 ,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
1091 ,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
1092 ,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
1093 ,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
1094 ,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
1095 ,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL
1096 ,P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL
1097 ,P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL
1098 ,P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL
1099 ,P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL
1100 ,P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL
1101 ,P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL
1102 ,P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL
1103 ,P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL
1104 ,P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL
1105 ,P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL
1106 ,P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL
1107 ,P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL
1108 ,P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL
1109 ,P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL
1110 ,P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL
1111 ,P_REQUEST_ID NUMBER DEFAULT NULL
1112 ,P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
1113 ,P_PROGRAM_ID NUMBER DEFAULT NULL
1114 ,P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
1115 ,P_LAST_UPDATE_DATE DATE
1116 ,P_LAST_UPDATED_BY NUMBER
1117 ,P_CREATION_DATE DATE
1118 ,P_CREATED_BY NUMBER
1119 ,P_LAST_UPDATE_LOGIN NUMBER
1120 ,p_active_start_date DATE DEFAULT NULL
1121 ,p_active_end_date DATE DEFAULT NULL
1122 ,p_location NUMBER DEFAULT NULL
1123 ,p_linear_location_id NUMBER DEFAULT NULL
1124 ,p_operational_log_flag VARCHAR2 DEFAULT NULL
1125 ,p_checkin_status NUMBER DEFAULT NULL
1126 ,p_supplier_warranty_exp_date DATE DEFAULT NULL
1127 ,p_equipment_gen_object_id NUMBER DEFAULT NULL
1128 ,p_owning_department_id NUMBER DEFAULT NULL
1129 ,p_accounting_class_code VARCHAR2 DEFAULT NULL
1130 ,p_area_id NUMBER DEFAULT NULL
1131 ,X_OBJECT_ID OUT NOCOPY NUMBER
1132 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
1133 ,X_MSG_COUNT OUT NOCOPY NUMBER
1134 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1135 )
1136 is
1137 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
1138 l_api_version CONSTANT NUMBER := 1.0;
1139 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1140 l_count number := 0;
1141 l_x_asset_return_status varchar2(3);
1142 l_x_asset_msg_count number;
1143 l_x_asset_msg_data varchar2(20000);
1144 l_x_maint_return_status varchar2(3);
1145 l_x_maint_msg_count number;
1146 l_x_maint_msg_data varchar2(20000);
1147 l_mfg_serial_number_flag varchar2(1);
1148 l_instance_id number;
1149 begin
1150 -- Standard Start of API savepoint
1151 SAVEPOINT create_asset;
1152
1153 -- Standard call to check for call compatibility.
1154 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1155 RAISE fnd_api.g_exc_unexpected_error;
1156 END IF;
1157
1158 -- Initialize message list if p_init_msg_list is set to TRUE.
1159 IF fnd_api.to_boolean(p_init_msg_list) THEN
1160 fnd_msg_pub.initialize;
1161 END IF;
1162
1163 -- Initialize API return status to success
1164 x_return_status := fnd_api.g_ret_sts_success;
1165
1166 -- API body
1167 begin
1168 select 1
1169 into l_count
1170 from mtl_serial_numbers
1171 where inventory_item_id = p_inventory_item_id
1172 and serial_number = p_serial_number
1173 and rownum <= 1;
1174
1175 exception
1176 when no_data_found then
1177 l_count := 0;
1178 end;
1179
1180 if l_count = 0 then
1181 l_mfg_serial_number_flag := 'N';
1182 else
1183 l_mfg_serial_number_flag := 'Y';
1184 end if;
1185
1186 insert_row(
1187 P_API_VERSION => P_API_VERSION,
1188 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1189 P_SERIAL_NUMBER => P_SERIAL_NUMBER,
1190 P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
1191 P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
1192 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
1193 P_CATEGORY_ID => P_CATEGORY_ID,
1194 P_PN_LOCATION_ID => P_PN_LOCATION_ID,
1195 P_FA_ASSET_ID => P_FA_ASSET_ID,
1196 P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
1197 P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
1198 P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
1199 P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
1200 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1201 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1202 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1203 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1204 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1205 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1206 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1207 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1208 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1209 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1210 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1211 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1212 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1213 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1214 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1215 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1216 P_ATTRIBUTE16 => P_ATTRIBUTE16,
1217 P_ATTRIBUTE17 => P_ATTRIBUTE17,
1218 P_ATTRIBUTE18 => P_ATTRIBUTE18,
1219 P_ATTRIBUTE19 => P_ATTRIBUTE19,
1220 P_ATTRIBUTE20 => P_ATTRIBUTE20,
1221 P_ATTRIBUTE21 => P_ATTRIBUTE21,
1222 P_ATTRIBUTE22 => P_ATTRIBUTE22,
1223 P_ATTRIBUTE23 => P_ATTRIBUTE23,
1224 P_ATTRIBUTE24 => P_ATTRIBUTE24,
1225 P_ATTRIBUTE25 => P_ATTRIBUTE25,
1226 P_ATTRIBUTE26 => P_ATTRIBUTE26,
1227 P_ATTRIBUTE27 => P_ATTRIBUTE27,
1228 P_ATTRIBUTE28 => P_ATTRIBUTE28,
1229 P_ATTRIBUTE29 => P_ATTRIBUTE29,
1230 P_ATTRIBUTE30 => P_ATTRIBUTE30,
1231 P_REQUEST_ID => P_REQUEST_ID,
1232 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
1233 P_PROGRAM_ID => P_PROGRAM_ID,
1234 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
1235 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
1236 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
1237 P_CREATION_DATE => P_CREATION_DATE,
1238 P_CREATED_BY => P_CREATED_BY,
1239 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1240 p_active_start_date => p_active_start_date,
1241 p_active_end_date => p_active_end_date,
1242 p_location => p_location,
1243 p_linear_location_id => p_linear_location_id,
1244 p_operational_log_flag => p_operational_log_flag,
1245 p_checkin_status => p_checkin_status,
1246 p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
1247 p_equipment_gen_object_id => p_equipment_gen_object_id,
1248 p_mfg_serial_number_flag => l_mfg_serial_number_flag,
1249 X_OBJECT_ID => l_instance_id
1250 ,X_RETURN_STATUS => l_x_asset_return_status
1251 ,X_MSG_COUNT => l_x_asset_msg_count
1252 ,X_MSG_DATA => l_x_asset_msg_data
1253 );
1254 x_return_status := l_x_asset_return_status;
1255 x_msg_count := l_x_asset_msg_count;
1256 x_msg_data := l_x_asset_msg_data;
1257 if (l_x_asset_return_status <> FND_API.G_RET_STS_SUCCESS) then
1258 RAISE FND_API.G_EXC_ERROR ;
1259 else
1260 if (p_owning_department_id is not null OR p_accounting_class_code is not null OR p_area_id is not null) then
1261 EAM_MAINT_ATTRIBUTES_PUB.create_maint_attributes(
1262 p_api_version => p_api_version
1263 ,p_instance_id => l_instance_id
1264 ,p_owning_department_id => p_owning_department_id
1265 ,p_accounting_class_code => p_accounting_class_code
1266 ,p_area_id => p_area_id
1267 ,p_parent_instance_id => null
1268 ,x_return_status => l_x_maint_return_status
1269 ,x_msg_count => l_x_maint_msg_count
1270 ,x_msg_data => l_x_maint_msg_data
1271 );
1272 x_return_status := l_x_maint_return_status;
1273 x_msg_count := l_x_maint_msg_count;
1274 x_msg_data := l_x_maint_msg_data;
1275 end if;
1276
1277 end if;
1278
1279 x_object_id := l_instance_id;
1280
1281 -- End of API body.
1282 -- Standard check of p_commit.
1283 IF fnd_api.to_boolean(p_commit) THEN
1284 COMMIT WORK;
1285 END IF;
1286
1287 -- Standard call to get message count and if count is 1, get message info.
1288 fnd_msg_pub.count_and_get(
1289 p_count => x_msg_count
1290 ,p_data => x_msg_data);
1291
1292
1293 EXCEPTION
1294 WHEN fnd_api.g_exc_error THEN
1295 ROLLBACK TO create_asset;
1296 x_return_status := fnd_api.g_ret_sts_error;
1297 /*fnd_msg_pub.count_and_get(
1298 p_count => x_msg_count
1299 ,p_data => x_msg_data);*/
1300 WHEN fnd_api.g_exc_unexpected_error THEN
1301 ROLLBACK TO create_asset;
1302 x_return_status := fnd_api.g_ret_sts_unexp_error;
1303 /*fnd_msg_pub.count_and_get(
1304 p_count => x_msg_count
1305 ,p_data => x_msg_data);*/
1306 WHEN OTHERS THEN
1307 ROLLBACK TO create_asset;
1308 x_return_status := fnd_api.g_ret_sts_unexp_error;
1309
1310 IF fnd_msg_pub.check_msg_level(
1311 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1312 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1313 END IF;
1314
1315 /*fnd_msg_pub.count_and_get(
1316 p_count => x_msg_count
1317 ,p_data => x_msg_data);*/
1318
1319 end create_asset;
1320
1321 procedure update_asset(
1322 P_API_VERSION IN NUMBER
1323 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
1324 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
1325 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1326 ,p_instance_id IN NUMBER
1327 ,P_INSTANCE_DESCRIPTION VARCHAR2
1328 ,P_INVENTORY_ITEM_ID NUMBER
1329 ,P_SERIAL_NUMBER VARCHAR2
1330 ,P_ORGANIZATION_ID NUMBER
1331 ,P_CATEGORY_ID NUMBER
1332 ,P_PN_LOCATION_ID NUMBER
1333 ,P_FA_ASSET_ID NUMBER
1334 ,P_FA_SYNC_FLAG VARCHAR2 DEFAULT NULL
1335 ,P_ASSET_CRITICALITY_CODE VARCHAR2
1336 ,P_MAINTAINABLE_FLAG VARCHAR2
1337 ,P_NETWORK_ASSET_FLAG VARCHAR2
1338 ,P_ATTRIBUTE_CATEGORY VARCHAR2
1339 ,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
1340 ,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
1341 ,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
1342 ,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
1343 ,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
1344 ,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
1345 ,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
1346 ,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
1347 ,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
1348 ,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
1349 ,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
1350 ,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
1351 ,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
1352 ,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
1353 ,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL
1354 ,P_ATTRIBUTE16 VARCHAR2 DEFAULT NULL
1355 ,P_ATTRIBUTE17 VARCHAR2 DEFAULT NULL
1356 ,P_ATTRIBUTE18 VARCHAR2 DEFAULT NULL
1357 ,P_ATTRIBUTE19 VARCHAR2 DEFAULT NULL
1358 ,P_ATTRIBUTE20 VARCHAR2 DEFAULT NULL
1359 ,P_ATTRIBUTE21 VARCHAR2 DEFAULT NULL
1360 ,P_ATTRIBUTE22 VARCHAR2 DEFAULT NULL
1361 ,P_ATTRIBUTE23 VARCHAR2 DEFAULT NULL
1362 ,P_ATTRIBUTE24 VARCHAR2 DEFAULT NULL
1363 ,P_ATTRIBUTE25 VARCHAR2 DEFAULT NULL
1364 ,P_ATTRIBUTE26 VARCHAR2 DEFAULT NULL
1365 ,P_ATTRIBUTE27 VARCHAR2 DEFAULT NULL
1366 ,P_ATTRIBUTE28 VARCHAR2 DEFAULT NULL
1367 ,P_ATTRIBUTE29 VARCHAR2 DEFAULT NULL
1368 ,P_ATTRIBUTE30 VARCHAR2 DEFAULT NULL
1369 ,P_REQUEST_ID NUMBER DEFAULT NULL
1370 ,P_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
1371 ,P_PROGRAM_ID NUMBER DEFAULT NULL
1372 ,P_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
1373 ,P_LAST_UPDATE_DATE DATE
1374 ,P_LAST_UPDATED_BY NUMBER
1375 ,P_LAST_UPDATE_LOGIN NUMBER
1376 ,P_FROM_PUBLIC_API VARCHAR2 DEFAULT 'Y'
1377 ,P_INSTANCE_NUMBER VARCHAR2 DEFAULT NULL
1378 ,P_LOCATION_TYPE_CODE VARCHAR2 DEFAULT NULL
1379 ,P_LOCATION_ID NUMBER DEFAULT NULL
1380 ,p_active_end_date DATE DEFAULT NULL
1381 ,p_linear_location_id NUMBER DEFAULT NULL
1382 ,p_operational_log_flag VARCHAR2 DEFAULT NULL
1383 ,p_checkin_status NUMBER DEFAULT NULL
1384 ,p_supplier_warranty_exp_date DATE DEFAULT NULL
1385 ,p_equipment_gen_object_id NUMBER DEFAULT NULL
1386 ,p_owning_department_id NUMBER DEFAULT NULL
1387 ,p_accounting_class_code VARCHAR2 DEFAULT NULL
1388 ,p_area_id NUMBER DEFAULT NULL
1389 ,p_reactivate_asset VARCHAR2 DEFAULT 'N'
1390 ,p_disassociate_fa_flag VARCHAR2 DEFAULT 'N'
1391 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
1392 ,X_MSG_COUNT OUT NOCOPY NUMBER
1393 ,X_MSG_DATA OUT NOCOPY VARCHAR2
1394 )
1395 IS
1396 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
1397 l_api_version CONSTANT NUMBER := 1.0;
1398 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1399 l_count number := 0;
1400 l_x_asset_return_status varchar2(3);
1401 l_x_asset_msg_count number;
1402 l_x_asset_msg_data varchar2(20000);
1403 l_x_maint_return_status varchar2(3);
1404 l_x_maint_msg_count number;
1405 l_x_maint_msg_data varchar2(20000);
1406 l_mfg_serial_number_flag varchar2(1);
1407 l_instance_id number;
1408 l_current_status number;
1409 l_owning_department_id number;
1410 l_accounting_class_code varchar2(10);
1411 l_area_id number;
1412 BEGIN
1413 -- Standard Start of API savepoint
1414 SAVEPOINT update_asset;
1415
1416 -- Standard call to check for call compatibility.
1417 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1418 RAISE fnd_api.g_exc_unexpected_error;
1419 END IF;
1420
1421 -- Initialize message list if p_init_msg_list is set to TRUE.
1422 IF fnd_api.to_boolean(p_init_msg_list) THEN
1423 fnd_msg_pub.initialize;
1424 END IF;
1425
1426 -- Initialize API return status to success
1427 x_return_status := fnd_api.g_ret_sts_success;
1428
1429 -- API body
1430 begin
1431 select 1,current_status
1432 into l_count,l_current_status
1433 from mtl_serial_numbers
1434 where inventory_item_id = p_inventory_item_id
1435 and serial_number = p_serial_number
1436 and rownum <= 1;
1437
1438 exception
1439 when no_data_found then
1440 l_count := 0;
1441 end;
1442
1443 if (l_count = 0 OR (l_count = 1 AND nvl(l_current_status,1) = 1))then
1444 l_mfg_serial_number_flag := 'N';
1445
1446 insert_row(
1447 P_API_VERSION => P_API_VERSION,
1448 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1449 P_SERIAL_NUMBER => P_SERIAL_NUMBER,
1450 P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
1451 P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
1452 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
1453 P_CATEGORY_ID => P_CATEGORY_ID,
1454 P_PN_LOCATION_ID => P_PN_LOCATION_ID,
1455 P_FA_ASSET_ID => P_FA_ASSET_ID,
1456 P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
1457 P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
1458 P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
1459 P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
1460 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1461 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1462 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1463 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1464 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1465 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1466 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1467 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1468 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1469 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1470 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1471 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1472 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1473 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1474 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1475 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1476 P_ATTRIBUTE16 => P_ATTRIBUTE16,
1477 P_ATTRIBUTE17 => P_ATTRIBUTE17,
1478 P_ATTRIBUTE18 => P_ATTRIBUTE18,
1479 P_ATTRIBUTE19 => P_ATTRIBUTE19,
1480 P_ATTRIBUTE20 => P_ATTRIBUTE20,
1481 P_ATTRIBUTE21 => P_ATTRIBUTE21,
1482 P_ATTRIBUTE22 => P_ATTRIBUTE22,
1483 P_ATTRIBUTE23 => P_ATTRIBUTE23,
1484 P_ATTRIBUTE24 => P_ATTRIBUTE24,
1485 P_ATTRIBUTE25 => P_ATTRIBUTE25,
1486 P_ATTRIBUTE26 => P_ATTRIBUTE26,
1487 P_ATTRIBUTE27 => P_ATTRIBUTE27,
1488 P_ATTRIBUTE28 => P_ATTRIBUTE28,
1489 P_ATTRIBUTE29 => P_ATTRIBUTE29,
1490 P_ATTRIBUTE30 => P_ATTRIBUTE30,
1491 P_REQUEST_ID => P_REQUEST_ID,
1492 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
1493 P_PROGRAM_ID => P_PROGRAM_ID,
1494 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
1495 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
1496 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
1497 P_CREATION_DATE => P_LAST_UPDATE_DATE,
1498 P_CREATED_BY => P_LAST_UPDATED_BY,
1499 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1500 p_active_start_date => sysdate,
1501 p_active_end_date => p_active_end_date,
1502 p_location => p_location_id,
1503 p_linear_location_id => p_linear_location_id,
1504 p_operational_log_flag => p_operational_log_flag,
1505 p_checkin_status => p_checkin_status,
1506 p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
1507 p_equipment_gen_object_id => p_equipment_gen_object_id,
1508 p_mfg_serial_number_flag => l_mfg_serial_number_flag,
1509 X_OBJECT_ID => l_instance_id
1510 ,X_RETURN_STATUS => l_x_asset_return_status
1511 ,X_MSG_COUNT => l_x_asset_msg_count
1512 ,X_MSG_DATA => l_x_asset_msg_data
1513 );
1514 else
1515 l_mfg_serial_number_flag := 'Y';
1516 l_instance_id := p_instance_id;
1517 update_row
1518 (
1519 P_API_VERSION => P_API_VERSION,
1520 p_instance_id => P_INSTANCE_ID,
1521 P_INSTANCE_DESCRIPTION => P_INSTANCE_DESCRIPTION,
1522 P_CATEGORY_ID => P_CATEGORY_ID,
1523 P_PN_LOCATION_ID => P_PN_LOCATION_ID,
1524 P_FA_ASSET_ID => P_FA_ASSET_ID,
1525 P_FA_SYNC_FLAG => P_FA_SYNC_FLAG,
1526 P_ASSET_CRITICALITY_CODE => P_ASSET_CRITICALITY_CODE,
1527 P_MAINTAINABLE_FLAG => P_MAINTAINABLE_FLAG,
1528 P_NETWORK_ASSET_FLAG => P_NETWORK_ASSET_FLAG,
1529 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1530 P_ATTRIBUTE1 => P_ATTRIBUTE1,
1531 P_ATTRIBUTE2 => P_ATTRIBUTE2,
1532 P_ATTRIBUTE3 => P_ATTRIBUTE3,
1533 P_ATTRIBUTE4 => P_ATTRIBUTE4,
1534 P_ATTRIBUTE5 => P_ATTRIBUTE5,
1535 P_ATTRIBUTE6 => P_ATTRIBUTE6,
1536 P_ATTRIBUTE7 => P_ATTRIBUTE7,
1537 P_ATTRIBUTE8 => P_ATTRIBUTE8,
1538 P_ATTRIBUTE9 => P_ATTRIBUTE9,
1539 P_ATTRIBUTE10 => P_ATTRIBUTE10,
1540 P_ATTRIBUTE11 => P_ATTRIBUTE11,
1541 P_ATTRIBUTE12 => P_ATTRIBUTE12,
1542 P_ATTRIBUTE13 => P_ATTRIBUTE13,
1543 P_ATTRIBUTE14 => P_ATTRIBUTE14,
1544 P_ATTRIBUTE15 => P_ATTRIBUTE15,
1545 P_ATTRIBUTE16 => P_ATTRIBUTE16,
1546 P_ATTRIBUTE17 => P_ATTRIBUTE17,
1547 P_ATTRIBUTE18 => P_ATTRIBUTE18,
1548 P_ATTRIBUTE19 => P_ATTRIBUTE19,
1549 P_ATTRIBUTE20 => P_ATTRIBUTE20,
1550 P_ATTRIBUTE21 => P_ATTRIBUTE21,
1551 P_ATTRIBUTE22 => P_ATTRIBUTE22,
1552 P_ATTRIBUTE23 => P_ATTRIBUTE23,
1553 P_ATTRIBUTE24 => P_ATTRIBUTE24,
1554 P_ATTRIBUTE25 => P_ATTRIBUTE25,
1555 P_ATTRIBUTE26 => P_ATTRIBUTE26,
1556 P_ATTRIBUTE27 => P_ATTRIBUTE27,
1557 P_ATTRIBUTE28 => P_ATTRIBUTE28,
1558 P_ATTRIBUTE29 => P_ATTRIBUTE29,
1559 P_ATTRIBUTE30 => P_ATTRIBUTE30,
1560 P_REQUEST_ID => P_REQUEST_ID,
1561 P_PROGRAM_APPLICATION_ID => P_PROGRAM_APPLICATION_ID,
1562 P_PROGRAM_ID => P_PROGRAM_ID,
1563 P_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
1564 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
1565 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
1566 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
1567 P_FROM_PUBLIC_API => P_FROM_PUBLIC_API ,
1568 P_INSTANCE_NUMBER => P_INSTANCE_NUMBER,
1569 P_LOCATION_TYPE_CODE => P_LOCATION_TYPE_CODE,
1570 P_LOCATION_ID => P_LOCATION_ID,
1571 p_active_end_date => p_active_end_date,
1572 p_linear_location_id => p_linear_location_id,
1573 p_operational_log_flag => p_operational_log_flag,
1574 p_checkin_status => p_checkin_status,
1575 p_supplier_warranty_exp_date => p_supplier_warranty_exp_date,
1576 p_equipment_gen_object_id => p_equipment_gen_object_id
1577 ,p_reactivate_asset => p_reactivate_asset
1578 ,p_disassociate_fa_flag => p_disassociate_fa_flag
1579 ,X_RETURN_STATUS => l_x_asset_return_status
1580 ,X_MSG_COUNT => l_x_asset_msg_count
1581 ,X_MSG_DATA => l_x_asset_msg_data
1582 );
1583 end if;
1584
1585 x_return_status := l_x_asset_return_status;
1586 x_msg_count := l_x_asset_msg_count;
1587 x_msg_data := l_x_asset_msg_data;
1588
1589 if (l_x_asset_return_status <> FND_API.G_RET_STS_SUCCESS) then
1590 RAISE FND_API.G_EXC_ERROR ;
1591 else
1592 --if (p_owning_department_id is not null OR p_accounting_class_code is not null OR p_area_id is not null) then
1593
1594 /* bug 5177526 : Need to pass maint org id */
1595 SELECT maint_organization_id INTO l_count
1596 FROM mtl_parameters
1597 WHERE organization_id = p_organization_id;
1598
1599 if (p_from_public_api = 'N') then
1600 l_owning_department_id := nvl(p_owning_department_id,FND_API.G_MISS_NUM);
1601 l_accounting_class_code := nvl(p_accounting_class_code,FND_API.G_MISS_CHAR);
1602 l_area_id := nvl(p_area_id,FND_API.G_MISS_NUM);
1603 else
1604 l_owning_department_id := p_owning_department_id;
1605 l_accounting_class_code := p_accounting_class_code;
1606 l_area_id := p_area_id;
1607 end if;
1608
1609 EAM_ORG_MAINT_DEFAULTS_PVT.update_insert_row(
1610 p_api_version => p_api_version
1611 ,p_object_type => 50
1612 ,p_object_id => l_instance_id
1613 ,p_organization_id => l_count
1614 ,p_owning_department_id => l_owning_department_id
1615 ,p_accounting_class_code => l_accounting_class_code
1616 ,p_area_id => l_area_id
1617 ,x_return_status => l_x_maint_return_status
1618 ,x_msg_count => l_x_maint_msg_count
1619 ,x_msg_data => l_x_maint_msg_data
1620 );
1621 x_return_status := l_x_maint_return_status;
1622 x_msg_count := l_x_maint_msg_count;
1623 x_msg_data := l_x_maint_msg_data;
1624 --end if;
1625
1626 end if;
1627
1628 -- End of API body.
1629 -- Standard check of p_commit.
1630 IF fnd_api.to_boolean(p_commit) THEN
1631 COMMIT WORK;
1632 END IF;
1633
1634
1635 -- Standard call to get message count and if count is 1, get message info.
1636 fnd_msg_pub.count_and_get(
1637 p_count => x_msg_count
1638 ,p_data => x_msg_data);
1639
1640
1641 EXCEPTION
1642 WHEN fnd_api.g_exc_error THEN
1643 ROLLBACK TO update_asset;
1644 x_return_status := fnd_api.g_ret_sts_error;
1645 /*fnd_msg_pub.count_and_get(
1646 p_count => x_msg_count
1647 ,p_data => x_msg_data);*/
1648 WHEN fnd_api.g_exc_unexpected_error THEN
1649 ROLLBACK TO update_asset;
1650 x_return_status := fnd_api.g_ret_sts_unexp_error;
1651 /*fnd_msg_pub.count_and_get(
1652 p_count => x_msg_count
1653 ,p_data => x_msg_data);*/
1654 WHEN OTHERS THEN
1655 ROLLBACK TO update_asset;
1656 x_return_status := fnd_api.g_ret_sts_unexp_error;
1657
1658 IF fnd_msg_pub.check_msg_level(
1659 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1660 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1661 END IF;
1662
1663 /*fnd_msg_pub.count_and_get(
1664 p_count => x_msg_count
1665 ,p_data => x_msg_data);*/
1666
1667
1668 end update_asset;
1669
1670 PROCEDURE SERIAL_CHECK
1671 ( p_api_version IN NUMBER,
1672 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1673 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1674 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1675 x_return_status OUT NOCOPY VARCHAR2,
1676 x_msg_count OUT NOCOPY NUMBER,
1677 x_msg_data OUT NOCOPY VARCHAR2,
1678 x_errorcode OUT NOCOPY NUMBER,
1679 x_ser_num_in_item_id OUT NOCOPY boolean,
1680 p_INVENTORY_ITEM_ID IN NUMBER,
1681 p_SERIAL_NUMBER IN VARCHAR2,
1682 p_ORGANIZATION_ID IN NUMBER
1683 ) IS
1684 l_api_name CONSTANT VARCHAR2(30) := 'serial_check';
1685 l_api_version CONSTANT NUMBER := 1.0;
1686 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1687 l_serial_number_type number;
1688 l_count number;
1689 BEGIN
1690
1691 -- Standard Start of API savepoint
1692 SAVEPOINT serial_check;
1693
1694 -- Standard call to check for call compatibility.
1695 IF NOT fnd_api.compatible_api_call(
1696 l_api_version
1697 ,p_api_version
1698 ,l_api_name
1699 ,g_pkg_name) THEN
1700 RAISE fnd_api.g_exc_unexpected_error;
1701 END IF;
1702
1703 -- Initialize message list if p_init_msg_list is set to TRUE.
1704 IF fnd_api.to_boolean(p_init_msg_list) THEN
1705 fnd_msg_pub.initialize;
1706 END IF;
1707
1708 -- Initialize API return status to success
1709 x_return_status := fnd_api.g_ret_sts_success;
1710
1711 -- API body
1712
1713 -- added to fix bug 2446341
1714 -- get serial_number_type and pass it to mtl_serial_check.SNUniqueCheck
1715
1716 x_ser_num_in_item_id := FALSE;
1717 select serial_number_type
1718 into l_serial_number_type
1719 from mtl_parameters
1720 where organization_id = p_organization_id;
1721
1722 mtl_serial_check.SNUniqueCheck(
1723 p_api_version => 0.9,
1724 x_return_status => x_return_status,
1725 x_errorcode => x_errorcode,
1726 x_msg_count => x_msg_count,
1727 x_msg_data => x_msg_data,
1728 p_org_id => p_organization_id,
1729 p_serial_number_type => l_serial_number_type,
1730 p_serial_number => p_Serial_number);
1731
1732
1733 /*IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1734 RAISE FND_API.G_EXC_ERROR ;
1735 END IF;
1736 */
1737
1738 -- check if serial number exists within asset group
1739 select count(1) into l_count
1740 from mtl_serial_numbers
1741 where inventory_item_id = p_inventory_item_id
1742 and serial_number = p_serial_number;
1743
1744 if l_count > 0 then
1745 x_return_status := FND_API.G_RET_STS_ERROR;
1746 x_ser_num_in_item_id := TRUE;
1747 else
1748 x_ser_num_in_item_id := FALSE;
1749 end if;
1750
1751
1752
1753 -- End of API body.
1754 -- Standard check of p_commit.
1755 IF fnd_api.to_boolean(p_commit) THEN
1756 COMMIT WORK;
1757 END IF;
1758
1759 -- Standard call to get message count and if count is 1, get message info.
1760 fnd_msg_pub.count_and_get(
1761 p_count => x_msg_count
1762 ,p_data => x_msg_data);
1763
1764
1765 EXCEPTION
1766 WHEN fnd_api.g_exc_error THEN
1767 ROLLBACK TO serial_check;
1768 x_return_status := fnd_api.g_ret_sts_error;
1769 fnd_msg_pub.count_and_get(
1770 p_count => x_msg_count
1771 ,p_data => x_msg_data);
1772 WHEN fnd_api.g_exc_unexpected_error THEN
1773 ROLLBACK TO serial_check;
1774 x_return_status := fnd_api.g_ret_sts_unexp_error;
1775 fnd_msg_pub.count_and_get(
1776 p_count => x_msg_count
1777 ,p_data => x_msg_data);
1778 WHEN OTHERS THEN
1779 ROLLBACK TO serial_check;
1780 x_return_status := fnd_api.g_ret_sts_unexp_error;
1781
1782 IF fnd_msg_pub.check_msg_level(
1783 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1784 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1785 END IF;
1786
1787 fnd_msg_pub.count_and_get(
1788 p_count => x_msg_count
1789 ,p_data => x_msg_data);
1790
1791 END SERIAL_CHECK;
1792
1793 procedure find_assets(
1794 p_organization_id number
1795 ,p_inventory_item_id number
1796 ,p_instance_id number
1797 ,p_category_id number
1798 ,P_PN_LOCATION_ID NUMBER,
1799 P_EAM_LOCATION_ID NUMBER,
1800 P_FA_ASSET_ID NUMBER,
1801 P_ASSET_CRITICALITY_CODE VARCHAR2,
1802 P_WIP_ACCOUNTING_CLASS_CODE VARCHAR2,
1803 P_MAINTAINABLE_FLAG VARCHAR2,
1804 P_OWNING_DEPARTMENT_ID NUMBER,
1805 P_PROD_ORGANIZATION_ID NUMBER,
1806 P_EQUIPMENT_ITEM_ID NUMBER,
1807 P_EQP_SERIAL_NUMBER VARCHAR2
1808 ,p_eam_item_type NUMBER
1809 ,p_asset_category_id NUMBER
1810 ) is
1811 cursor asset_cur is
1812 select cii.serial_number,
1813 cii.instance_number,
1814 cii.inventory_item_id,
1815 msn.gen_object_id
1816 from csi_item_instances cii,
1817 mtl_serial_numbers msn,
1818 fa_additions fa,
1819 csi_i_assets cia,
1820 mtl_system_items msi,
1821 mtl_parameters mp,
1822 eam_org_maint_defaults eomd,
1823 mtl_system_items msi_prod,
1824 mtl_serial_numbers msn_prod,
1825 mtl_parameters mp_prod
1826 where cii.last_vld_organization_id = msn.current_organization_id
1827 and cii.inventory_item_id=msn.inventory_item_id
1828 and cii.serial_number=msn.serial_number
1829 and cii.last_vld_organization_id = p_organization_id
1830 and nvl(cii.network_asset_flag,'N') = 'N'
1831 and msi.eam_item_type=p_eam_item_type
1832 and msi.inventory_item_id = cii.inventory_item_id
1833 and msi.organization_id = cii.last_vld_organization_id
1834 and cii.instance_id = cia.instance_id(+)
1835 and cia.fa_asset_id = fa.asset_id(+)
1836 and mp.organization_id = cii.last_vld_organization_id
1837 and cii.instance_id = eomd.object_id (+)
1838 and eomd.object_type(+) = 50
1839 and eomd.organization_id(+) = cii.last_vld_organization_id --mp.maint_organization_id
1840 and cii.equipment_gen_object_id = msn_prod.gen_object_id(+)
1841 and msn_prod.current_organization_id = msi_prod.organization_id(+)
1842 and msn_prod.inventory_item_id = msi_prod.inventory_item_id(+)
1843 and msi_prod.organization_id = mp_prod.organization_id(+)
1844 and msi_prod.equipment_type(+) = 1
1845 and (p_inventory_item_id is null or p_inventory_item_id = cii.inventory_item_id)
1846 and (p_instance_id is null or p_instance_id = cii.instance_id)
1847 and (p_category_id is null or p_category_id = cii.category_id)
1848 and (P_PN_LOCATION_ID is null or P_PN_LOCATION_ID = cii.pn_location_id)
1849 and (P_EAM_LOCATION_ID is null or P_EAM_LOCATION_ID= eomd.area_id)
1850 and (P_FA_ASSET_ID is null or P_FA_ASSET_ID = fa.asset_id)
1851 and (P_ASSET_CRITICALITY_CODE is null or P_ASSET_CRITICALITY_CODE = cii.asset_criticality_code)
1852 and (P_WIP_ACCOUNTING_CLASS_CODE is null or P_WIP_ACCOUNTING_CLASS_CODE = eomd.accounting_class_code)
1853 and (P_MAINTAINABLE_FLAG is null or P_MAINTAINABLE_FLAG = cii.maintainable_flag)
1854 and (P_OWNING_DEPARTMENT_ID is null or P_OWNING_DEPARTMENT_ID = eomd.OWNING_DEPARTMENT_ID)
1855 and (P_PROD_ORGANIZATION_ID is null or P_PROD_ORGANIZATION_ID = msn_prod.CURRENT_ORGANIZATION_ID)
1856 and (P_EQUIPMENT_ITEM_ID is null or P_EQUIPMENT_ITEM_ID = msi_prod.INVENTORY_ITEM_ID)
1857 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))
1858 and (P_ASSET_CATEGORY_ID is null or p_ASSET_CATEGORY_ID = fa.asset_category_id)
1859 ;
1860 begin
1861 commit;
1862 for asset in asset_cur
1863 loop
1864 begin
1865 INSERT INTO EAM_ASSET_RESULTS_GTT(gen_object_id)
1866 VALUES (asset.gen_object_id);
1867 exception
1868
1869 when DUP_VAL_ON_INDEX then
1870 null;
1871 when others then
1872 RAISE;
1873 end;
1874
1875 begin
1876 insert into EAM_ASSET_RESULTS_GTT (gen_object_id)
1877 (select object_id from mtl_object_genealogy
1878 where genealogy_type = 5
1879 and (sysdate between NVL(start_date_active,sysdate-1) and NVL(end_date_active,sysdate+1))
1880 start with parent_object_id = asset.gen_object_id
1881 connect by parent_object_id = prior object_id
1882 and prior genealogy_type = 5
1883 and sysdate between NVL(prior start_date_active,sysdate-1) and NVL(prior end_date_active,sysdate+1)
1884 );
1885 exception
1886 when DUP_VAL_ON_INDEX then
1887 null;
1888 when others then
1889 RAISE;
1890 end;
1891
1892 end loop;
1893 end find_assets;
1894 END EAM_ASSET_NUMBER_PVT;