[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_ATTR_PVT
Source
1 PACKAGE BODY EAM_ASSET_ATTR_PVT as
2 /* $Header: EAMVAATB.pls 120.5.12010000.2 2008/10/23 11:01:45 vboddapa ship $ */
3 -- Start of comments
4 -- API name : EAM_ASSET_ATTR_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_DEPENDENT_ASSET_FLAG IN VARCHAR2
33 -- P_ATTRIBUTE_CATEGORY IN VARCHAR2 OPTIONAL
34 -- P_ATTRIBUTE1 IN VARCHAR2 OPTIONAL
35 -- P_ATTRIBUTE2 IN VARCHAR2 OPTIONAL
36 -- P_ATTRIBUTE3 IN VARCHAR2 OPTIONAL
37 -- P_ATTRIBUTE4 IN VARCHAR2 OPTIONAL
38 -- P_ATTRIBUTE5 IN VARCHAR2 OPTIONAL
39 -- P_ATTRIBUTE6 IN VARCHAR2 OPTIONAL
40 -- P_ATTRIBUTE7 IN VARCHAR2 OPTIONAL
41 -- P_ATTRIBUTE8 IN VARCHAR2 OPTIONAL
42 -- P_ATTRIBUTE9 IN VARCHAR2 OPTIONAL
43 -- P_ATTRIBUTE10 IN VARCHAR2 OPTIONAL
44 -- P_ATTRIBUTE11 IN VARCHAR2 OPTIONAL
45 -- P_ATTRIBUTE12 IN VARCHAR2 OPTIONAL
46 -- P_ATTRIBUTE13 IN VARCHAR2 OPTIONAL
47 -- P_ATTRIBUTE14 IN VARCHAR2 OPTIONAL
48 -- P_ATTRIBUTE15 IN VARCHAR2 OPTIONAL
49 -- P_LAST_UPDATE_DATE IN DATE REQUIRED
50 -- P_LAST_UPDATED_BY IN NUMBER REQUIRED
51 -- P_CREATION_DATE IN DATE REQUIRED
52 -- P_CREATED_BY IN NUMBER REQUIRED
53 -- P_LAST_UPDATE_LOGIN IN NUMBER REQUIRED
54 -- P_REQUEST_ID IN NUMBER DEFAULT NULL OPTIONAL
55 -- P_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL OPTIONAL
56 -- P_PROGRAM_ID IN NUMBER DEFAULT NULL OPTIONAL
57 -- P_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL
58 -- OUT X_OBJECT_ID OUT NUMBER
59 -- X_RETURN_STATUS OUT VARCHAR2(1)
60 -- X_MSG_COUNT OUT NUMBER
61 -- X_MSG_DATA OUT VARCHAR2(2000)
62 --
63 -- Version Current version 115.0
64 --
65 -- Notes : Note text
66 --
67 -- End of comments
68
69 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_asset_attr_pvt';
70
71
72 PROCEDURE INSERT_ROW(
73 P_API_VERSION IN NUMBER,
74 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
75 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
76 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
77 P_ROWID IN OUT NOCOPY VARCHAR2,
78 P_ASSOCIATION_ID NUMBER,
79 P_APPLICATION_ID NUMBER,
80 P_DESCRIPTIVE_FLEXFIELD_NAME VARCHAR2,
81 P_INVENTORY_ITEM_ID NUMBER,
82 P_SERIAL_NUMBER VARCHAR2,
83 P_ORGANIZATION_ID NUMBER,
84 P_ATTRIBUTE_CATEGORY VARCHAR2,
85 P_C_ATTRIBUTE1 VARCHAR2,
86 P_C_ATTRIBUTE2 VARCHAR2,
87 P_C_ATTRIBUTE3 VARCHAR2,
88 P_C_ATTRIBUTE4 VARCHAR2,
89 P_C_ATTRIBUTE5 VARCHAR2,
90 P_C_ATTRIBUTE6 VARCHAR2,
91 P_C_ATTRIBUTE7 VARCHAR2,
92 P_C_ATTRIBUTE8 VARCHAR2,
93 P_C_ATTRIBUTE9 VARCHAR2,
94 P_C_ATTRIBUTE10 VARCHAR2,
95 P_C_ATTRIBUTE11 VARCHAR2,
96 P_C_ATTRIBUTE12 VARCHAR2,
97 P_C_ATTRIBUTE13 VARCHAR2,
98 P_C_ATTRIBUTE14 VARCHAR2,
99 P_C_ATTRIBUTE15 VARCHAR2,
100 P_C_ATTRIBUTE16 VARCHAR2,
101 P_C_ATTRIBUTE17 VARCHAR2,
102 P_C_ATTRIBUTE18 VARCHAR2,
103 P_C_ATTRIBUTE19 VARCHAR2,
104 P_C_ATTRIBUTE20 VARCHAR2,
105 P_D_ATTRIBUTE1 DATE,
106 P_D_ATTRIBUTE2 DATE,
107 P_D_ATTRIBUTE3 DATE,
108 P_D_ATTRIBUTE4 DATE,
109 P_D_ATTRIBUTE5 DATE,
110 P_D_ATTRIBUTE6 DATE,
111 P_D_ATTRIBUTE7 DATE,
112 P_D_ATTRIBUTE8 DATE,
113 P_D_ATTRIBUTE9 DATE,
114 P_D_ATTRIBUTE10 DATE,
115 P_N_ATTRIBUTE1 NUMBER,
116 P_N_ATTRIBUTE2 NUMBER,
117 P_N_ATTRIBUTE3 NUMBER,
118 P_N_ATTRIBUTE4 NUMBER,
119 P_N_ATTRIBUTE5 NUMBER,
120 P_N_ATTRIBUTE6 NUMBER,
121 P_N_ATTRIBUTE7 NUMBER,
122 P_N_ATTRIBUTE8 NUMBER,
123 P_N_ATTRIBUTE9 NUMBER,
124 P_N_ATTRIBUTE10 NUMBER,
125 P_REQUEST_ID NUMBER ,
126 P_PROGRAM_APPLICATION_ID NUMBER ,
127 P_PROGRAM_ID NUMBER ,
128 P_PROGRAM_UPDATE_DATE DATE ,
129 P_MAINTENANCE_OBJECT_TYPE NUMBER,
130 P_MAINTENANCE_OBJECT_ID NUMBER,
131 P_CREATION_ORGANIZATION_ID NUMBER,
132 P_LAST_UPDATE_DATE DATE,
133 P_LAST_UPDATED_BY NUMBER,
134 P_CREATION_DATE DATE,
135 P_CREATED_BY NUMBER,
136 P_LAST_UPDATE_LOGIN NUMBER,
137 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
138 X_MSG_COUNT OUT NOCOPY NUMBER,
139 X_MSG_DATA OUT NOCOPY VARCHAR2
140 ) IS
141 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
142 l_api_version CONSTANT NUMBER := 1.0;
143 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
144 l_serial_number varchar2(30);
145 l_inventory_item_id number;
146
147 CURSOR C IS SELECT rowid FROM MTL_EAM_ASSET_ATTR_VALUES
148 WHERE attribute_category = p_attribute_category
149 AND maintenance_object_type = P_maintenance_object_type
150 AND maintenance_object_id = P_maintenance_object_id
151 ;
152
153 begin
154
155 -- Standard Start of API savepoint
156 SAVEPOINT eam_asset_attr;
157
158 -- Standard call to check for call compatibility.
159 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
160 RAISE fnd_api.g_exc_unexpected_error;
161 END IF;
162
163 -- Initialize message list if p_init_msg_list is set to TRUE.
164 IF fnd_api.to_boolean(p_init_msg_list) THEN
165 fnd_msg_pub.initialize;
166 END IF;
167
168 -- Initialize API return status to success
169 x_return_status := fnd_api.g_ret_sts_success;
170
171 -- API body
172
173 if (p_maintenance_object_id is not null) then
174 select serial_number,inventory_item_id
175 into l_serial_number,l_inventory_item_id
176 from csi_item_instances
177 where instance_id = p_maintenance_object_id;
178 else
179 l_inventory_item_id := p_inventory_item_id;
180 l_serial_number := p_serial_number;
181 end if;
182
183 INSERT INTO MTL_EAM_ASSET_ATTR_VALUES(
184 association_id,
185 application_id,
186 descriptive_flexfield_name,
187 inventory_item_id,
188 serial_number,
189 organization_id,
190 attribute_category,
191 c_attribute1,
192 c_attribute2,
193 c_attribute3,
194 c_attribute4,
195 c_attribute5,
196 c_attribute6,
197 c_attribute7,
198 c_attribute8,
199 c_attribute9,
200 c_attribute10,
201 c_attribute11,
202 c_attribute12,
203 c_attribute13,
204 c_attribute14,
205 c_attribute15,
206 c_attribute16,
207 c_attribute17,
208 c_attribute18,
209 c_attribute19,
210 c_attribute20,
211 d_attribute1,
212 d_attribute2,
213 d_attribute3,
214 d_attribute4,
215 d_attribute5,
216 d_attribute6,
217 d_attribute7,
218 d_attribute8,
219 d_attribute9,
220 d_attribute10,
221 n_attribute1,
222 n_attribute2,
223 n_attribute3,
224 n_attribute4,
225 n_attribute5,
226 n_attribute6,
227 n_attribute7,
228 n_attribute8,
229 n_attribute9,
230 n_attribute10,
231 last_update_date,
232 last_updated_by,
233 creation_date,
234 created_by,
235 last_update_login,
236 request_id,
237 program_application_id,
238 program_id,
239 program_update_date,
240 maintenance_object_id,
241 maintenance_object_type,
242 creation_organization_id
243 ) values (
244 p_association_id,
245 p_application_id,
246 p_descriptive_flexfield_name,
247 l_inventory_item_id,
248 l_serial_number,
249 p_organization_id,
250 p_attribute_category,
251 p_c_attribute1,
252 p_c_attribute2,
253 p_c_attribute3,
254 p_c_attribute4,
255 p_c_attribute5,
256 p_c_attribute6,
257 p_c_attribute7,
258 p_c_attribute8,
259 p_c_attribute9,
260 p_c_attribute10,
261 p_c_attribute11,
262 p_c_attribute12,
263 p_c_attribute13,
264 p_c_attribute14,
265 p_c_attribute15,
266 p_c_attribute16,
267 p_c_attribute17,
268 p_c_attribute18,
269 p_c_attribute19,
270 p_c_attribute20,
271 p_d_attribute1,
272 p_d_attribute2,
273 p_d_attribute3,
274 p_d_attribute4,
275 p_d_attribute5,
276 p_d_attribute6,
277 p_d_attribute7,
278 p_d_attribute8,
279 p_d_attribute9,
280 p_d_attribute10,
281 p_n_attribute1,
282 p_n_attribute2,
283 p_n_attribute3,
284 p_n_attribute4,
285 p_n_attribute5,
286 p_n_attribute6,
287 p_n_attribute7,
288 p_n_attribute8,
289 p_n_attribute9,
290 p_n_attribute10,
291 p_last_update_date,
292 p_last_updated_by,
293 p_creation_date,
294 p_created_by,
295 p_last_update_login,
296 p_request_id,
297 p_program_application_id,
298 p_program_id,
299 p_program_update_date,
300 p_maintenance_object_id,
301 p_maintenance_object_type,
302 p_creation_organization_id
303 );
304
305 OPEN C;
306 FETCH C INTO P_Rowid;
307 if (C%NOTFOUND) then
308 CLOSE C;
309 Raise NO_DATA_FOUND;
310 end if;
311 CLOSE C;
312
313 eam_text_util.process_asset_update_event
314 (
315 p_event => 'UPDATE'
316 ,p_instance_id => p_maintenance_object_id
317 ,p_commit => p_commit
318 );
319
320 -- End of API body.
321 -- Standard check of p_commit.
322 IF fnd_api.to_boolean(p_commit) THEN
323 COMMIT WORK;
324 END IF;
325
326 -- Standard call to get message count and if count is 1, get message info.
327 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
328 EXCEPTION
329 WHEN fnd_api.g_exc_error THEN
330 ROLLBACK TO eam_asset_attr;
331 x_return_status := fnd_api.g_ret_sts_error;
332 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
333 WHEN fnd_api.g_exc_unexpected_error THEN
334 ROLLBACK TO eam_asset_attr;
335 x_return_status := fnd_api.g_ret_sts_unexp_error;
336 fnd_msg_pub.count_and_get(
337 p_count => x_msg_count
338 ,p_data => x_msg_data);
339 WHEN OTHERS THEN
340 ROLLBACK TO eam_asset_attr;
341 x_return_status := fnd_api.g_ret_sts_unexp_error;
342
343 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
344 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
345 END IF;
346
347 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
348
349 END Insert_Row;
350
351
352
353 PROCEDURE LOCK_ROW(
354 P_API_VERSION IN NUMBER,
355 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
356 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
357 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
358 P_ROWID IN OUT NOCOPY VARCHAR2,
359 P_ASSOCIATION_ID NUMBER,
360 P_APPLICATION_ID NUMBER,
361 P_DESCRIPTIVE_FLEXFIELD_NAME VARCHAR2,
362 P_INVENTORY_ITEM_ID NUMBER,
363 P_SERIAL_NUMBER VARCHAR2,
364 P_ORGANIZATION_ID NUMBER,
365 P_ATTRIBUTE_CATEGORY VARCHAR2,
366 P_C_ATTRIBUTE1 VARCHAR2,
367 P_C_ATTRIBUTE2 VARCHAR2,
371 P_C_ATTRIBUTE6 VARCHAR2,
368 P_C_ATTRIBUTE3 VARCHAR2,
369 P_C_ATTRIBUTE4 VARCHAR2,
370 P_C_ATTRIBUTE5 VARCHAR2,
372 P_C_ATTRIBUTE7 VARCHAR2,
373 P_C_ATTRIBUTE8 VARCHAR2,
374 P_C_ATTRIBUTE9 VARCHAR2,
375 P_C_ATTRIBUTE10 VARCHAR2,
376 P_C_ATTRIBUTE11 VARCHAR2,
377 P_C_ATTRIBUTE12 VARCHAR2,
378 P_C_ATTRIBUTE13 VARCHAR2,
379 P_C_ATTRIBUTE14 VARCHAR2,
380 P_C_ATTRIBUTE15 VARCHAR2,
381 P_C_ATTRIBUTE16 VARCHAR2,
382 P_C_ATTRIBUTE17 VARCHAR2,
383 P_C_ATTRIBUTE18 VARCHAR2,
384 P_C_ATTRIBUTE19 VARCHAR2,
385 P_C_ATTRIBUTE20 VARCHAR2,
386 P_D_ATTRIBUTE1 DATE,
387 P_D_ATTRIBUTE2 DATE,
388 P_D_ATTRIBUTE3 DATE,
389 P_D_ATTRIBUTE4 DATE,
390 P_D_ATTRIBUTE5 DATE,
391 P_D_ATTRIBUTE6 DATE,
392 P_D_ATTRIBUTE7 DATE,
393 P_D_ATTRIBUTE8 DATE,
394 P_D_ATTRIBUTE9 DATE,
395 P_D_ATTRIBUTE10 DATE,
396 P_N_ATTRIBUTE1 NUMBER,
397 P_N_ATTRIBUTE2 NUMBER,
398 P_N_ATTRIBUTE3 NUMBER,
399 P_N_ATTRIBUTE4 NUMBER,
400 P_N_ATTRIBUTE5 NUMBER,
401 P_N_ATTRIBUTE6 NUMBER,
402 P_N_ATTRIBUTE7 NUMBER,
403 P_N_ATTRIBUTE8 NUMBER,
404 P_N_ATTRIBUTE9 NUMBER,
405 P_N_ATTRIBUTE10 NUMBER,
406 P_REQUEST_ID NUMBER ,
407 P_PROGRAM_APPLICATION_ID NUMBER ,
408 P_PROGRAM_ID NUMBER ,
409 P_PROGRAM_UPDATE_DATE DATE ,
410 P_MAINTENANCE_OBJECT_TYPE NUMBER,
411 P_MAINTENANCE_OBJECT_ID NUMBER,
412 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
413 X_MSG_COUNT OUT NOCOPY NUMBER,
414 X_MSG_DATA OUT NOCOPY VARCHAR2
415 ) IS
416 l_api_name CONSTANT VARCHAR2(30) := 'lock_row';
417 l_api_version CONSTANT NUMBER := 1.0;
418 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
419
420 CURSOR C IS
421 SELECT *
422 FROM MTL_EAM_ASSET_ATTR_VALUES
423 WHERE rowid = P_Rowid
424 FOR UPDATE of ATTRIBUTE_CATEGORY NOWAIT;
425 Recinfo C%ROWTYPE;
426
427 BEGIN
428
429 -- Standard Start of API savepoint
430 SAVEPOINT eam_asset_attr;
431
432 -- Standard call to check for call compatibility.
433 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
434 RAISE fnd_api.g_exc_unexpected_error;
435 END IF;
436
437 -- Initialize message list if p_init_msg_list is set to TRUE.
438 IF fnd_api.to_boolean(p_init_msg_list) THEN
439 fnd_msg_pub.initialize;
440 END IF;
441
442 -- Initialize API return status to success
443 x_return_status := fnd_api.g_ret_sts_success;
444
445 -- API body
446
447 OPEN C;
448 FETCH C INTO Recinfo;
449 if (C%NOTFOUND) then
450 CLOSE C;
451 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
452 APP_EXCEPTION.Raise_Exception;
453 end if;
454 CLOSE C;
455
456 if (
457 (Recinfo.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID)
458 AND (Recinfo.SERIAL_NUMBER = P_SERIAL_NUMBER)
459 AND (Recinfo.ASSOCIATION_ID = P_ASSOCIATION_ID)
460 AND (Recinfo.APPLICATION_ID = P_APPLICATION_ID)
461 AND (Recinfo.DESCRIPTIVE_FLEXFIELD_NAME = P_DESCRIPTIVE_FLEXFIELD_NAME)
462 AND ( (Recinfo.attribute_category = P_Attribute_Category)
463 OR ( (Recinfo.attribute_category IS NULL)
464 AND (P_Attribute_Category IS NULL)))
465 AND ( (Recinfo.c_attribute1 = p_c_attribute1)
466 OR ( (Recinfo.c_attribute1 IS NULL)
467 AND (p_c_attribute1 IS NULL)))
468 AND ( (Recinfo.c_attribute2 = p_c_attribute2)
469 OR ( (Recinfo.c_attribute2 IS NULL)
470 AND (p_c_attribute2 IS NULL)))
471 AND ( (Recinfo.c_attribute3 = p_c_attribute3)
472 OR ( (Recinfo.c_attribute3 IS NULL)
473 AND (p_c_attribute3 IS NULL)))
474 AND ( (Recinfo.c_attribute4 = p_c_attribute4)
475 OR ( (Recinfo.c_attribute4 IS NULL)
476 AND (p_c_attribute4 IS NULL)))
477 AND ( (Recinfo.c_attribute5 = p_c_attribute5)
478 OR ( (Recinfo.c_attribute5 IS NULL)
479 AND (p_c_attribute5 IS NULL)))
480 AND ( (Recinfo.c_attribute6 = p_c_attribute6)
481 OR ( (Recinfo.c_attribute6 IS NULL)
482 AND (p_c_attribute6 IS NULL)))
483 AND ( (Recinfo.c_attribute7 = p_c_attribute7)
484 OR ( (Recinfo.c_attribute7 IS NULL)
485 AND (p_c_attribute7 IS NULL)))
486 AND ( (Recinfo.c_attribute8 = p_c_attribute8)
487 OR ( (Recinfo.c_attribute8 IS NULL)
488 AND (p_c_attribute8 IS NULL)))
489 AND ( (Recinfo.c_attribute9 = p_c_attribute9)
490 OR ( (Recinfo.c_attribute9 IS NULL)
491 AND (p_c_attribute9 IS NULL)))
492 AND ( (Recinfo.c_attribute10 = p_c_attribute10)
496 OR ( (Recinfo.c_attribute11 IS NULL)
493 OR ( (Recinfo.c_attribute10 IS NULL)
494 AND (p_c_attribute10 IS NULL)))
495 AND ( (Recinfo.c_attribute11 = p_c_attribute11)
497 AND (p_c_attribute11 IS NULL)))
498 AND ( (Recinfo.c_attribute12 = p_c_attribute12)
499 OR ( (Recinfo.c_attribute12 IS NULL)
500 AND (p_c_attribute12 IS NULL)))
501 AND ( (Recinfo.c_attribute13 = p_c_attribute13)
502 OR ( (Recinfo.c_attribute13 IS NULL)
503 AND (p_c_attribute13 IS NULL)))
504 AND ( (Recinfo.c_attribute14 = p_c_attribute14)
505 OR ( (Recinfo.c_attribute14 IS NULL)
506 AND (p_c_attribute14 IS NULL)))
507 AND ( (Recinfo.c_attribute15 = p_c_attribute15)
508 OR ( (Recinfo.c_attribute15 IS NULL)
509 AND (p_c_attribute15 IS NULL)))
510 AND ( (Recinfo.c_attribute16 = p_c_attribute16)
511 OR ( (Recinfo.c_attribute16 IS NULL)
512 AND (p_c_attribute16 IS NULL)))
513 AND ( (Recinfo.c_attribute17 = p_c_attribute17)
514 OR ( (Recinfo.c_attribute17 IS NULL)
515 AND (p_c_attribute17 IS NULL)))
516 AND ( (Recinfo.c_attribute18 = p_c_attribute18)
517 OR ( (Recinfo.c_attribute18 IS NULL)
518 AND (p_c_attribute18 IS NULL)))
519 AND ( (Recinfo.c_attribute19 = p_c_attribute19)
520 OR ( (Recinfo.c_attribute19 IS NULL)
521 AND (p_c_attribute19 IS NULL)))
522 AND ( (Recinfo.c_attribute20 = p_c_attribute20)
523 OR ( (Recinfo.c_attribute20 IS NULL)
524 AND (p_c_attribute20 IS NULL)))
525 AND ( (Recinfo.n_attribute1 = p_n_attribute1)
526 OR ( (Recinfo.n_attribute1 IS NULL)
527 AND (p_n_attribute1 IS NULL)))
528 AND ( (Recinfo.n_attribute2 = p_n_attribute2)
529 OR ( (Recinfo.n_attribute2 IS NULL)
530 AND (p_n_attribute2 IS NULL)))
531 AND ( (Recinfo.n_attribute3 = p_n_attribute3)
532 OR ( (Recinfo.n_attribute3 IS NULL)
533 AND (p_n_attribute3 IS NULL)))
534 AND ( (Recinfo.n_attribute4 = p_n_attribute4)
535 OR ( (Recinfo.n_attribute4 IS NULL)
536 AND (p_n_attribute4 IS NULL)))
537 AND ( (Recinfo.n_attribute5 = p_n_attribute5)
538 OR ( (Recinfo.n_attribute5 IS NULL)
539 AND (p_n_attribute5 IS NULL)))
540 AND ( (Recinfo.n_attribute6 = p_n_attribute6)
541 OR ( (Recinfo.n_attribute6 IS NULL)
542 AND (p_n_attribute6 IS NULL)))
543 AND ( (Recinfo.n_attribute7 = p_n_attribute7)
544 OR ( (Recinfo.n_attribute7 IS NULL)
545 AND (p_n_attribute7 IS NULL)))
546 AND ( (Recinfo.n_attribute8 = p_n_attribute8)
547 OR ( (Recinfo.n_attribute8 IS NULL)
548 AND (p_n_attribute8 IS NULL)))
549 AND ( (Recinfo.n_attribute9 = p_n_attribute9)
550 OR ( (Recinfo.n_attribute9 IS NULL)
551 AND (p_n_attribute9 IS NULL)))
552 AND ( (Recinfo.n_attribute10 = p_n_attribute10)
553 OR ( (Recinfo.n_attribute10 IS NULL)
554 AND (p_n_attribute10 IS NULL)))
555 AND ( (Recinfo.d_attribute1 = p_d_attribute1)
556 OR ( (Recinfo.d_attribute1 IS NULL)
557 AND (p_d_attribute1 IS NULL)))
558 AND ( (Recinfo.d_attribute2 = p_d_attribute2)
559 OR ( (Recinfo.d_attribute2 IS NULL)
560 AND (p_d_attribute2 IS NULL)))
561 AND ( (Recinfo.d_attribute3 = p_d_attribute3)
562 OR ( (Recinfo.d_attribute3 IS NULL)
563 AND (p_d_attribute3 IS NULL)))
564 AND ( (Recinfo.d_attribute4 = p_d_attribute4)
565 OR ( (Recinfo.d_attribute4 IS NULL)
566 AND (p_d_attribute4 IS NULL)))
567 AND ( (Recinfo.d_attribute5 = p_d_attribute5)
568 OR ( (Recinfo.d_attribute5 IS NULL)
569 AND (p_d_attribute5 IS NULL)))
570 AND ( (Recinfo.d_attribute6 = p_d_attribute6)
571 OR ( (Recinfo.d_attribute6 IS NULL)
572 AND (p_d_attribute6 IS NULL)))
573 AND ( (Recinfo.d_attribute7 = p_d_attribute7)
574 OR ( (Recinfo.d_attribute7 IS NULL)
575 AND (p_d_attribute7 IS NULL)))
576 AND ( (Recinfo.d_attribute8 = p_d_attribute8)
577 OR ( (Recinfo.d_attribute8 IS NULL)
578 AND (p_d_attribute8 IS NULL)))
579 AND ( (Recinfo.d_attribute9 = p_d_attribute9)
580 OR ( (Recinfo.d_attribute9 IS NULL)
581 AND (p_d_attribute9 IS NULL)))
582 AND ( (Recinfo.d_attribute10 = p_d_attribute10)
583 OR ( (Recinfo.d_attribute10 IS NULL)
584 AND (p_d_attribute10 IS NULL)))
585 AND ( (Recinfo.REQUEST_ID = P_REQUEST_ID)
586 OR ( (Recinfo.REQUEST_ID IS NULL)
587 AND (P_REQUEST_ID IS NULL)))
588 AND ( (Recinfo.PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID)
589 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
590 AND (P_PROGRAM_APPLICATION_ID IS NULL)))
591 AND ( (Recinfo.PROGRAM_ID = P_PROGRAM_ID)
592 OR ( (Recinfo.PROGRAM_ID IS NULL)
593 AND (P_PROGRAM_ID IS NULL)))
597 AND ( (Recinfo.MAINTENANCE_OBJECT_TYPE = P_MAINTENANCE_OBJECT_TYPE)
594 AND ( (Recinfo.PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE)
595 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
596 AND (P_PROGRAM_UPDATE_DATE IS NULL)))
598 OR ( (Recinfo.MAINTENANCE_OBJECT_TYPE IS NULL)
599 AND (P_MAINTENANCE_OBJECT_TYPE IS NULL)))
600 AND ( (Recinfo.MAINTENANCE_OBJECT_ID = P_MAINTENANCE_OBJECT_ID)
601 OR ( (Recinfo.MAINTENANCE_OBJECT_ID IS NULL)
602 AND (P_MAINTENANCE_OBJECT_ID IS NULL)))
603 ) then
604 return;
605 else
606 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
607 APP_EXCEPTION.Raise_Exception;
608 end if;
609
610 -- End of API body.
611 -- Standard check of p_commit.
612 IF fnd_api.to_boolean(p_commit) THEN
613 COMMIT WORK;
614 END IF;
615
616 -- Standard call to get message count and if count is 1, get message info.
617 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
618 EXCEPTION
619 WHEN fnd_api.g_exc_error THEN
620 ROLLBACK TO eam_asset_attr;
621 x_return_status := fnd_api.g_ret_sts_error;
622 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
623 WHEN fnd_api.g_exc_unexpected_error THEN
624 ROLLBACK TO eam_asset_attr;
625 x_return_status := fnd_api.g_ret_sts_unexp_error;
626 fnd_msg_pub.count_and_get(
627 p_count => x_msg_count
628 ,p_data => x_msg_data);
629 WHEN OTHERS THEN
630 ROLLBACK TO eam_asset_attr;
631 x_return_status := fnd_api.g_ret_sts_unexp_error;
632
633 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
634 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
635 END IF;
636
637 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
638
639 END Lock_Row;
640
641 PROCEDURE UPDATE_ROW(
642 P_API_VERSION IN NUMBER,
643 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
644 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
645 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
646 P_ROWID IN OUT NOCOPY VARCHAR2,
647 P_C_ATTRIBUTE1 VARCHAR2,
648 P_C_ATTRIBUTE2 VARCHAR2,
649 P_C_ATTRIBUTE3 VARCHAR2,
650 P_C_ATTRIBUTE4 VARCHAR2,
651 P_C_ATTRIBUTE5 VARCHAR2,
652 P_C_ATTRIBUTE6 VARCHAR2,
653 P_C_ATTRIBUTE7 VARCHAR2,
654 P_C_ATTRIBUTE8 VARCHAR2,
655 P_C_ATTRIBUTE9 VARCHAR2,
656 P_C_ATTRIBUTE10 VARCHAR2,
657 P_C_ATTRIBUTE11 VARCHAR2,
658 P_C_ATTRIBUTE12 VARCHAR2,
659 P_C_ATTRIBUTE13 VARCHAR2,
660 P_C_ATTRIBUTE14 VARCHAR2,
661 P_C_ATTRIBUTE15 VARCHAR2,
662 P_C_ATTRIBUTE16 VARCHAR2,
663 P_C_ATTRIBUTE17 VARCHAR2,
664 P_C_ATTRIBUTE18 VARCHAR2,
665 P_C_ATTRIBUTE19 VARCHAR2,
666 P_C_ATTRIBUTE20 VARCHAR2,
667 P_D_ATTRIBUTE1 DATE,
668 P_D_ATTRIBUTE2 DATE,
669 P_D_ATTRIBUTE3 DATE,
670 P_D_ATTRIBUTE4 DATE,
671 P_D_ATTRIBUTE5 DATE,
672 P_D_ATTRIBUTE6 DATE,
673 P_D_ATTRIBUTE7 DATE,
674 P_D_ATTRIBUTE8 DATE,
675 P_D_ATTRIBUTE9 DATE,
676 P_D_ATTRIBUTE10 DATE,
677 P_N_ATTRIBUTE1 NUMBER,
678 P_N_ATTRIBUTE2 NUMBER,
679 P_N_ATTRIBUTE3 NUMBER,
680 P_N_ATTRIBUTE4 NUMBER,
681 P_N_ATTRIBUTE5 NUMBER,
682 P_N_ATTRIBUTE6 NUMBER,
683 P_N_ATTRIBUTE7 NUMBER,
684 P_N_ATTRIBUTE8 NUMBER,
685 P_N_ATTRIBUTE9 NUMBER,
686 P_N_ATTRIBUTE10 NUMBER,
687 P_REQUEST_ID NUMBER ,
688 P_PROGRAM_APPLICATION_ID NUMBER ,
689 P_PROGRAM_ID NUMBER ,
690 P_PROGRAM_UPDATE_DATE DATE ,
691 P_MAINTENANCE_OBJECT_TYPE NUMBER,
692 P_MAINTENANCE_OBJECT_ID NUMBER,
693 P_LAST_UPDATE_DATE DATE,
694 P_LAST_UPDATED_BY NUMBER,
695 P_LAST_UPDATE_LOGIN NUMBER,
696 /* Bug 3371507 */
697 P_FROM_PUBLIC_API VARCHAR2 DEFAULT 'Y',
698 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
699 X_MSG_COUNT OUT NOCOPY NUMBER,
700 X_MSG_DATA OUT NOCOPY VARCHAR2
701 ) IS
702 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
703 l_api_version CONSTANT NUMBER := 1.0;
704 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
705 BEGIN
706 -- Standard Start of API savepoint
707 SAVEPOINT eam_asset_attr;
708
709 -- Standard call to check for call compatibility.
710 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
711 RAISE fnd_api.g_exc_unexpected_error;
715 IF fnd_api.to_boolean(p_init_msg_list) THEN
712 END IF;
713
714 -- Initialize message list if p_init_msg_list is set to TRUE.
716 fnd_msg_pub.initialize;
717 END IF;
718
719 -- Initialize API return status to success
720 x_return_status := fnd_api.g_ret_sts_success;
721
722 -- API body
723 /* Bug 3371507: used 'decode' function*/
724 UPDATE MTL_EAM_ASSET_ATTR_VALUES
725 set
726 c_attribute1 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE1, DECODE(p_c_attribute1,NULL,c_attribute1,FND_API.G_MISS_CHAR,NULL,p_c_attribute1)),
727 c_attribute2 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE2, DECODE(p_c_attribute2,NULL,c_attribute2,FND_API.G_MISS_CHAR,NULL,p_c_attribute2)),
728 c_attribute3 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE3, DECODE(p_c_attribute3,NULL,c_attribute3,FND_API.G_MISS_CHAR,NULL,p_c_attribute3)),
729 c_attribute4 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE4, DECODE(p_c_attribute4,NULL,c_attribute4,FND_API.G_MISS_CHAR,NULL,p_c_attribute4)),
730 c_attribute5 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE5, DECODE(p_c_attribute5,NULL,c_attribute5,FND_API.G_MISS_CHAR,NULL,p_c_attribute5)),
731 c_attribute6 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE6, DECODE(p_c_attribute6,NULL,c_attribute6,FND_API.G_MISS_CHAR,NULL,p_c_attribute6)),
732 c_attribute7 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE7, DECODE(p_c_attribute7,NULL,c_attribute7,FND_API.G_MISS_CHAR,NULL,p_c_attribute7)),
733 c_attribute8 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE8, DECODE(p_c_attribute8,NULL,c_attribute8,FND_API.G_MISS_CHAR,NULL,p_c_attribute8)),
734 c_attribute9 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE9, DECODE(p_c_attribute9,NULL,c_attribute9,FND_API.G_MISS_CHAR,NULL,p_c_attribute9)),
735 c_attribute10 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE10, DECODE(p_c_attribute10,NULL,c_attribute10,FND_API.G_MISS_CHAR,NULL,p_c_attribute10)),
736 c_attribute11 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE11, DECODE(p_c_attribute11,NULL,c_attribute11,FND_API.G_MISS_CHAR,NULL,p_c_attribute11)),
737 c_attribute12 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE12, DECODE(p_c_attribute12,NULL,c_attribute12,FND_API.G_MISS_CHAR,NULL,p_c_attribute12)),
738 c_attribute13 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE13, DECODE(p_c_attribute13,NULL,c_attribute13,FND_API.G_MISS_CHAR,NULL,p_c_attribute13)),
739 c_attribute14 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE14, DECODE(p_c_attribute14,NULL,c_attribute14,FND_API.G_MISS_CHAR,NULL,p_c_attribute14)),
740 c_attribute15 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE15, DECODE(p_c_attribute15,NULL,c_attribute15,FND_API.G_MISS_CHAR,NULL,p_c_attribute15)),
741 c_attribute16 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE16, DECODE(p_c_attribute16,NULL,c_attribute16,FND_API.G_MISS_CHAR,NULL,p_c_attribute16)),
742 c_attribute17 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE17, DECODE(p_c_attribute17,NULL,c_attribute17,FND_API.G_MISS_CHAR,NULL,p_c_attribute17)),
743 c_attribute18 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE18, DECODE(p_c_attribute18,NULL,c_attribute18,FND_API.G_MISS_CHAR,NULL,p_c_attribute18)),
744 c_attribute19 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE19, DECODE(p_c_attribute19,NULL,c_attribute19,FND_API.G_MISS_CHAR,NULL,p_c_attribute19)),
745 c_attribute20 = decode(P_FROM_PUBLIC_API, 'N', P_C_ATTRIBUTE20, DECODE(p_c_attribute20,NULL,c_attribute20,FND_API.G_MISS_CHAR,NULL,p_c_attribute20)),
746 d_attribute1 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE1, DECODE(p_d_attribute1,NULL,d_attribute1,FND_API.G_MISS_DATE,NULL,p_d_attribute1)),
747 d_attribute2 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE2, DECODE(p_d_attribute2,NULL,d_attribute2,FND_API.G_MISS_DATE,NULL,p_d_attribute2)),
748 d_attribute3 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE3, DECODE(p_d_attribute3,NULL,d_attribute3,FND_API.G_MISS_DATE,NULL,p_d_attribute3)),
749 d_attribute4 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE4, DECODE(p_d_attribute4,NULL,d_attribute4,FND_API.G_MISS_DATE,NULL,p_d_attribute4)),
750 d_attribute5 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE5, DECODE(p_d_attribute5,NULL,d_attribute5,FND_API.G_MISS_DATE,NULL,p_d_attribute5)),
751 d_attribute6 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE6, DECODE(p_d_attribute6,NULL,d_attribute6,FND_API.G_MISS_DATE,NULL,p_d_attribute6)),
752 d_attribute7 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE7, DECODE(p_d_attribute7,NULL,d_attribute7,FND_API.G_MISS_DATE,NULL,p_d_attribute7)),
753 d_attribute8 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE8, DECODE(p_d_attribute8,NULL,d_attribute8,FND_API.G_MISS_DATE,NULL,p_d_attribute8)),
754 d_attribute9 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE9, DECODE(p_d_attribute9,NULL,d_attribute9,FND_API.G_MISS_DATE,NULL,p_d_attribute9)),
755 d_attribute10 = decode(P_FROM_PUBLIC_API, 'N', P_D_ATTRIBUTE10, DECODE(p_d_attribute10,NULL,d_attribute10,FND_API.G_MISS_DATE,NULL,p_d_attribute10)),
756 n_attribute1 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE1, DECODE(p_n_attribute1,NULL,n_attribute1,FND_API.G_MISS_NUM,NULL,p_n_attribute1)),
757 n_attribute2 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE2, DECODE(p_n_attribute2,NULL,n_attribute2,FND_API.G_MISS_NUM,NULL,p_n_attribute2)),
758 n_attribute3 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE3, DECODE(p_n_attribute3,NULL,n_attribute3,FND_API.G_MISS_NUM,NULL,p_n_attribute3)),
759 n_attribute4 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE4, DECODE(p_n_attribute4,NULL,n_attribute4,FND_API.G_MISS_NUM,NULL,p_n_attribute4)),
763 n_attribute8 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE8, DECODE(p_n_attribute8,NULL,n_attribute8,FND_API.G_MISS_NUM,NULL,p_n_attribute8)),
760 n_attribute5 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE5, DECODE(p_n_attribute5,NULL,n_attribute5,FND_API.G_MISS_NUM,NULL,p_n_attribute5)),
761 n_attribute6 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE6, DECODE(p_n_attribute6,NULL,n_attribute6,FND_API.G_MISS_NUM,NULL,p_n_attribute6)),
762 n_attribute7 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE7, DECODE(p_n_attribute7,NULL,n_attribute7,FND_API.G_MISS_NUM,NULL,p_n_attribute7)),
764 n_attribute9 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE9, DECODE(p_n_attribute9,NULL,n_attribute9,FND_API.G_MISS_NUM,NULL,p_n_attribute9)),
765 n_attribute10 = decode(P_FROM_PUBLIC_API, 'N', P_N_ATTRIBUTE10, DECODE(p_n_attribute10,NULL,n_attribute10,FND_API.G_MISS_NUM,NULL,p_n_attribute10)),
766 last_update_date = p_last_update_date,
767 last_updated_by = p_last_updated_by,
768 last_update_login = p_last_update_login,
769 request_id = p_request_id,
770 program_application_id = p_program_application_id,
771 program_id = p_program_id,
772 program_update_date = p_program_update_date,
773 maintenance_object_type= p_maintenance_object_type,
774 maintenance_object_id = p_maintenance_object_id
775 where rowid = p_rowid;
776
777 if (SQL%NOTFOUND) then
778 Raise NO_DATA_FOUND;
779 end if;
780
781 eam_text_util.process_asset_update_event
782 (
783 p_event => 'UPDATE'
784 ,p_instance_id => p_maintenance_object_id
785 ,p_commit => p_commit
786 );
787
788
789 -- End of API body.
790 -- Standard check of p_commit.
791 IF fnd_api.to_boolean(p_commit) THEN
792 COMMIT WORK;
793 END IF;
794
795 -- Standard call to get message count and if count is 1, get message info.
796 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
797 EXCEPTION
798 WHEN fnd_api.g_exc_error THEN
799 ROLLBACK TO eam_asset_attr;
800 x_return_status := fnd_api.g_ret_sts_error;
801 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
802 WHEN fnd_api.g_exc_unexpected_error THEN
803 ROLLBACK TO eam_asset_attr;
804 x_return_status := fnd_api.g_ret_sts_unexp_error;
805 fnd_msg_pub.count_and_get(
806 p_count => x_msg_count
807 ,p_data => x_msg_data);
808 WHEN OTHERS THEN
809 ROLLBACK TO eam_asset_attr;
810 x_return_status := fnd_api.g_ret_sts_unexp_error;
811
812 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
813 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
814 END IF;
815
816 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
817
818 END Update_Row;
819
820
821
822
823
824 PROCEDURE DELETE_ROW(
825 P_API_VERSION IN NUMBER,
826 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
827 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
828 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
829 P_ROWID IN OUT NOCOPY VARCHAR2,
830 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
831 X_MSG_COUNT OUT NOCOPY NUMBER,
832 X_MSG_DATA OUT NOCOPY VARCHAR2
833 ) IS
834 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
835 l_api_version CONSTANT NUMBER := 1.0;
836 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
837 l_object_id NUMBER;
838 BEGIN
839 -- Standard Start of API savepoint
840 SAVEPOINT eam_asset_attr;
841
842 -- Standard call to check for call compatibility.
843 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
844 RAISE fnd_api.g_exc_unexpected_error;
845 END IF;
846
847 -- Initialize message list if p_init_msg_list is set to TRUE.
848 IF fnd_api.to_boolean(p_init_msg_list) THEN
849 fnd_msg_pub.initialize;
850 END IF;
851
852 -- Initialize API return status to success
853 x_return_status := fnd_api.g_ret_sts_success;
854
855 -- API body
856 BEGIN
857 SELECT maintenance_object_id into l_object_id
858 FROM mtl_eam_asset_attr_values
859 WHERE rowid = p_rowid;
860
861 Delete from mtl_eam_asset_attr_values
862 where rowid = p_rowid;
863
864
865 eam_text_util.process_asset_update_event
866 (
867 p_event => 'UPDATE'
868 ,p_instance_id => l_object_id
869 ,p_commit => p_commit
870 );
871 EXCEPTION
872 WHEN NO_DATA_FOUND THEN
873 null;
874 END;
875 -- End of API body.
876 -- Standard check of p_commit.
877 IF fnd_api.to_boolean(p_commit) THEN
878 COMMIT WORK;
879 END IF;
880
881 -- Standard call to get message count and if count is 1, get message info.
885 ROLLBACK TO eam_asset_attr;
882 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
883 EXCEPTION
884 WHEN fnd_api.g_exc_error THEN
886 x_return_status := fnd_api.g_ret_sts_error;
887 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
888 WHEN fnd_api.g_exc_unexpected_error THEN
889 ROLLBACK TO eam_asset_attr;
890 x_return_status := fnd_api.g_ret_sts_unexp_error;
891 fnd_msg_pub.count_and_get(
892 p_count => x_msg_count
893 ,p_data => x_msg_data);
894 WHEN OTHERS THEN
895 ROLLBACK TO eam_asset_attr;
896 x_return_status := fnd_api.g_ret_sts_unexp_error;
897
898 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
899 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
900 END IF;
901
902 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
903
904 END Delete_Row;
905
906
907 PROCEDURE COPY_ATTRIBUTE(
908 P_API_VERSION IN NUMBER,
909 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
910 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
911 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
912 P_INVENTORY_ITEM_ID IN NUMBER,
913 P_ORGANIZATION_ID IN NUMBER,
914 P_SERIAL_NUMBER_FROM IN VARCHAR2,
915 P_SERIAL_NUMBER_TO IN VARCHAR2,
916 X_OBJECT_ID OUT NOCOPY NUMBER,
917 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
918 X_MSG_COUNT OUT NOCOPY NUMBER,
919 X_MSG_DATA OUT NOCOPY VARCHAR2
920 ) IS
921 l_api_name CONSTANT VARCHAR2(30) := 'copy_attribute';
922 l_api_version CONSTANT NUMBER := 1.0;
923 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
924 sql_stmt_num NUMBER;
925 l_return_status VARCHAR2(30);
926 l_msg_count NUMBER;
927 l_msg_data VARCHAR2(30);
928 l_object_id number;
929 l_parent_object_id NUMBER;
930 l_parent_inventory_item_id NUMBER;
931 l_parent_serial_number VARCHAR2(30);
932
933
934
935 BEGIN
936
937 -- Standard Start of API savepoint
938 SAVEPOINT copy_attr;
939
940 -- Standard call to check for call compatibility.
941 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
942 RAISE fnd_api.g_exc_unexpected_error;
943 END IF;
944
945 -- Initialize message list if p_init_msg_list is set to TRUE.
946 IF fnd_api.to_boolean(p_init_msg_list) THEN
947 fnd_msg_pub.initialize;
948 END IF;
949
950 -- Initialize API return status to success
951 x_return_status := fnd_api.g_ret_sts_success;
952
953 -- API body
954 sql_stmt_num :=1;
955
956 select instance_id into l_object_id
957 from csi_item_instances
958 where serial_number = p_serial_number_to
959 and inventory_item_id = p_inventory_item_id;
960
961
962
963 insert into mtl_eam_asset_attr_values
964 (
965 association_id,
966 application_id,
967 descriptive_flexfield_name,
968 inventory_item_id,
969 serial_number,
970 organization_id,
971 attribute_category,
972 c_attribute1,
973 c_attribute2,
974 c_attribute3,
975 c_attribute4,
976 c_attribute5,
977 c_attribute6,
978 c_attribute7,
979 c_attribute8,
980 c_attribute9,
981 c_attribute10,
982 c_attribute11,
983 c_attribute12,
984 c_attribute13,
985 c_attribute14,
986 c_attribute15,
987 c_attribute16,
988 c_attribute17,
989 c_attribute18,
990 c_attribute19,
991 c_attribute20,
992 d_attribute1,
993 d_attribute2,
994 d_attribute3,
995 d_attribute4,
996 d_attribute5,
997 d_attribute6,
998 d_attribute7,
999 d_attribute8,
1000 d_attribute9,
1001 d_attribute10,
1002 n_attribute1,
1003 n_attribute2,
1004 n_attribute3,
1005 n_attribute4,
1006 n_attribute5,
1007 n_attribute6,
1008 n_attribute7,
1009 n_attribute8,
1010 n_attribute9,
1011 n_attribute10,
1012 last_update_date,
1013 last_updated_by,
1014 creation_date,
1015 created_by,
1016 last_update_login,
1017 program_id,
1018 program_update_date,
1019 program_application_id,
1020 request_id,
1021 maintenance_object_id,
1022 maintenance_object_type,
1023 creation_organization_id
1024 )
1025 select
1026 association_id,
1027 application_id,
1028 descriptive_flexfield_name,
1029 p_inventory_item_id,
1030 p_serial_number_to,
1031 p_organization_id,
1032 attribute_category,
1033 c_attribute1,
1034 c_attribute2,
1035 c_attribute3,
1036 c_attribute4,
1037 c_attribute5,
1038 c_attribute6,
1039 c_attribute7,
1040 c_attribute8,
1041 c_attribute9,
1042 c_attribute10,
1043 c_attribute11,
1044 c_attribute12,
1045 c_attribute13,
1046 c_attribute14,
1047 c_attribute15,
1048 c_attribute16,
1049 c_attribute17,
1050 c_attribute18,
1051 c_attribute19,
1052 c_attribute20,
1053 d_attribute1,
1054 d_attribute2,
1055 d_attribute3,
1056 d_attribute4,
1057 d_attribute5,
1058 d_attribute6,
1059 d_attribute7,
1060 d_attribute8,
1061 d_attribute9,
1062 d_attribute10,
1063 n_attribute1,
1064 n_attribute2,
1065 n_attribute3,
1066 n_attribute4,
1067 n_attribute5,
1068 n_attribute6,
1069 n_attribute7,
1070 n_attribute8,
1071 n_attribute9,
1072 n_attribute10,
1073 sysdate,--last_update_date,
1074 last_updated_by,
1075 sysdate,--creation_date,
1076 created_by,
1077 last_update_login,
1078 program_id,
1079 program_update_date,
1080 program_application_id,
1081 request_id,
1082 l_object_id,
1083 maintenance_object_type,
1084 organization_id
1085 from mtl_eam_asset_attr_values
1086 where descriptive_flexfield_name = 'MTL_EAM_ASSET_ATTR_VALUES'
1087 and inventory_item_id = p_inventory_item_id
1088 and serial_number = p_serial_number_from
1089 and attribute_category not in (
1090 select attribute_category
1091 from mtl_eam_asset_attr_values
1092 where descriptive_flexfield_name = 'MTL_EAM_ASSET_ATTR_VALUES'
1093 and inventory_item_id = p_inventory_item_id
1094 and serial_number = p_serial_number_to
1095 );
1096
1097 x_object_id := l_object_id;
1098
1099 eam_text_util.process_asset_update_event
1100 (
1101 p_event => 'UPDATE'
1102 ,p_instance_id => l_object_id
1103 ,p_commit => p_commit
1104 );
1105
1106
1107 -- End of API body.
1108 -- Standard check of p_commit.
1109 IF fnd_api.to_boolean(p_commit) THEN
1110 COMMIT WORK;
1111 END IF;
1112
1113 -- Standard call to get message count and if count is 1, get message info.
1114 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1115 EXCEPTION
1116 WHEN fnd_api.g_exc_error THEN
1117 ROLLBACK TO copy_attr;
1118 x_return_status := fnd_api.g_ret_sts_error;
1119 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1120 WHEN fnd_api.g_exc_unexpected_error THEN
1121 ROLLBACK TO copy_attr;
1122 x_return_status := fnd_api.g_ret_sts_unexp_error;
1123 fnd_msg_pub.count_and_get(
1124 p_count => x_msg_count
1125 ,p_data => x_msg_data);
1126 WHEN OTHERS THEN
1127 ROLLBACK TO copy_attr;
1128 x_return_status := fnd_api.g_ret_sts_unexp_error;
1129
1130 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1131 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1132 END IF;
1133
1134 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1135
1136 END COPY_ATTRIBUTE;
1137
1138 END EAM_ASSET_ATTR_PVT;