[Home] [Help]
PACKAGE BODY: APPS.EAM_LINEAR_LOCATIONS_PUB
Source
1 PACKAGE BODY eam_linear_locations_pub AS
2 /* $Header: EAMPELLB.pls 120.4 2006/02/24 15:18:31 sraval noship $*/
3 -- Start of comments
4 -- API name : eam_linear_locations_pub
5 -- Type : Public.
6 -- Function :
7 -- Pre-reqs : None.
8 -- Parameters :
9 -- IN p_api_version IN NUMBER Required
10 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
11 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
12 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
13 -- parameter1
14 -- parameter2
15 -- .
16 -- .
17 -- OUT x_return_status OUT VARCHAR2(1)
18 -- x_msg_count OUT NUMBER
19 -- x_msg_data OUT VARCHAR2(2000)
20 -- parameter1
21 -- parameter2
22 -- .
23 -- .
24 -- Version Current version x.x
25 -- Changed....
26 -- previous version y.y
27 -- Changed....
28 -- .
29 -- .
30 -- previous version 2.0
31 -- Changed....
32 -- Initial version 1.0
33 --
34 -- Notes : Note text
35 --
36 -- End of comments
37
38 g_pkg_name CONSTANT VARCHAR2(30):= 'eam_linear_locations_pub';
39
40 function return_user_id(p_user_name in varchar2) return number
41 is
42 l_user_id number;
43 begin
44 select user_id into l_user_id
45 from fnd_user
46 where user_name = p_user_name;
47
48 return l_user_id;
49 end;
50
51 function check_valid_user(p_user_name in varchar2) return number is
52 l_user_id number;
53 l_resp_id number;
54 l_appl_id number;
55 l_check number;
56 l_lang varchar2(10);
57 l_resp varchar2(35);
58 begin
59 -- populate with the new responsibility id.
60 l_resp_id := 111;
61 l_lang := 'US';
62 l_appl_id := 426;
63 l_resp := 'Linear Asset Management User';
64
65 select responsibility_id into l_resp_id
66 from fnd_responsibility_tl
67 where responsibility_name = l_resp
68 and application_id = l_appl_id
69 and language = l_lang;
70
71 l_user_id := -1;
72 begin
73 l_user_id := return_user_id(p_user_name);
74
75
76 begin
77 select 1
78 into l_check
79 from fnd_user_resp_groups
80 where user_id = l_user_id
81 and responsibility_id = l_resp_id;
82 exception
83 when no_data_found then
84 return l_user_id;
85 end;
86
87 return l_user_id;
88 exception
89 when no_data_found then
90 return l_user_id;
91
92 end;
93
94 end check_valid_user;
95
96
97
98 PROCEDURE insert_row
99 (
100 p_api_version IN NUMBER
101 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
102 ,p_commit IN VARCHAR2 := fnd_api.g_false
103 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
104 ,p_external_linear_id IN NUMBER
105 ,p_external_linear_name IN VARCHAR2
106 ,p_external_source_name IN VARCHAR2
107 ,p_external_linear_type IN VARCHAR2
108 ,x_eam_linear_id OUT NOCOPY NUMBER
109 ,x_return_status OUT NOCOPY VARCHAR2
110 ,x_msg_count OUT NOCOPY NUMBER
111 ,x_msg_data OUT NOCOPY VARCHAR2
112 ) IS
113
114 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
115 l_api_version CONSTANT NUMBER := 1.0;
116 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
117 l_count NUMBER := 0;
118
119 BEGIN
120 -- Standard Start of API savepoint
121 SAVEPOINT eam_linear_locations_pub;
122
123 -- Standard call to check for call compatibility.
124 IF NOT fnd_api.compatible_api_call(
125 l_api_version
126 ,p_api_version
127 ,l_api_name
128 ,g_pkg_name) THEN
129 RAISE fnd_api.g_exc_unexpected_error;
130 END IF;
131
132 -- Initialize message list if p_init_msg_list is set to TRUE.
133 IF fnd_api.to_boolean(p_init_msg_list) THEN
134 fnd_msg_pub.initialize;
135 END IF;
136
137 -- Initialize API return status to success
138 x_return_status := fnd_api.g_ret_sts_success;
139
140 -- API body
141
142 -- Validation of external_source_name
143 SELECT count(*) INTO l_count FROM dual WHERE EXISTS
144 (SELECT 1 FROM mfg_lookups WHERE lookup_code = p_external_source_name
145 AND lookup_type = 'EAM_EXTERNAL_SOURCE_NAME');
146
147 IF (l_count = 0) THEN
148 fnd_message.set_name('EAM', 'EAM_INVALID_PARAMETER');
149 fnd_message.set_token('NAME', 'p_external_source_name : ' || p_external_source_name);
150 fnd_msg_pub.add;
151 RAISE fnd_api.g_exc_error;
152 END IF;
153
154 -- Validation of external_linear_id
155 IF (p_external_linear_id IS NULL) THEN
156 fnd_message.set_name('EAM', 'EAM_INVALID_PARAMETER');
157 fnd_message.set_token('NAME', 'p_external_linear_id : ' || p_external_linear_id);
158 fnd_msg_pub.add;
159 RAISE fnd_api.g_exc_error;
160 END IF;
161
162 SELECT count(*) INTO l_count FROM dual WHERE EXISTS
163 (SELECT 1 FROM eam_linear_locations WHERE external_linear_id = p_external_linear_id
164 AND external_source_name = p_external_source_name
165 AND external_linear_type = p_external_linear_type);
166
167 IF (l_count > 0) THEN
168 fnd_message.set_name('EAM', 'EAM_EXT_LINEAR_ID_EXISTS');
169 fnd_msg_pub.add;
170 RAISE fnd_api.g_exc_error;
171 END IF;
172
173 -- insert into eam_linear_locations table
174 INSERT INTO eam_linear_locations
175 (
176 external_linear_id
177 ,external_source_name
178 ,external_linear_name
179 ,external_linear_type
180 ,eam_linear_id
181 ) VALUES
182 (
183 p_external_linear_id
184 ,p_external_source_name
185 ,p_external_linear_name
186 ,p_external_linear_type
187 ,eam_linear_locations_s.nextval
188 ) RETURNING eam_linear_id INTO x_eam_linear_id;
189
190 -- End of API body.
191
192 -- Standard check of p_commit.
193 IF fnd_api.to_boolean(p_commit) THEN
194 COMMIT WORK;
195 END IF;
196
197 -- Standard call to get message count and if count is 1, get message info.
198 fnd_msg_pub.count_and_get(p_count => x_msg_count
199 ,p_data => x_msg_data);
200 EXCEPTION
201 WHEN fnd_api.g_exc_error THEN
202 ROLLBACK TO eam_linear_locations_pub;
203 x_return_status := fnd_api.g_ret_sts_error;
204 fnd_msg_pub.count_and_get(p_count => x_msg_count
205 ,p_data => x_msg_data);
206 WHEN fnd_api.g_exc_unexpected_error THEN
207 ROLLBACK TO eam_linear_locations_pub;
208 x_return_status := fnd_api.g_ret_sts_unexp_error;
209 fnd_msg_pub.count_and_get(p_count => x_msg_count
210 ,p_data => x_msg_data);
211 WHEN OTHERS THEN
212 ROLLBACK TO eam_linear_locations_pub;
213 x_return_status := fnd_api.g_ret_sts_unexp_error;
214 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
215 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
216 END IF;
217 fnd_msg_pub.count_and_get(p_count => x_msg_count
218 ,p_data => x_msg_data);
219 END insert_row;
220
221
222 PROCEDURE update_row
223 (
224 p_api_version IN NUMBER
225 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
226 ,p_commit IN VARCHAR2 := fnd_api.g_false
227 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
228 ,p_external_linear_id IN NUMBER
229 ,p_external_linear_name IN VARCHAR2
230 ,p_external_source_name IN VARCHAR2
231 ,p_external_linear_type IN VARCHAR2
232 ,p_eam_linear_id IN NUMBER
233 ,x_return_status OUT NOCOPY VARCHAR2
234 ,x_msg_count OUT NOCOPY NUMBER
235 ,x_msg_data OUT NOCOPY VARCHAR2
236 ) IS
237
238 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
241 l_count NUMBER := 0;
242
243 BEGIN
244 -- Standard Start of API savepoint
245 SAVEPOINT eam_linear_locations_pub;
246
247 -- Standard call to check for call compatibility.
248 IF NOT fnd_api.compatible_api_call(
249 l_api_version
250 ,p_api_version
251 ,l_api_name
252 ,g_pkg_name) THEN
253 RAISE fnd_api.g_exc_unexpected_error;
254 END IF;
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF fnd_api.to_boolean(p_init_msg_list) THEN
258 fnd_msg_pub.initialize;
259 END IF;
260
261 -- Initialize API return status to success
262 x_return_status := fnd_api.g_ret_sts_success;
263
264 -- API body
265
266 -- Validation of eam_linear_id
267 SELECT count(*) INTO l_count FROM dual WHERE EXISTS
268 (SELECT 1 FROM eam_linear_locations WHERE eam_linear_id = p_eam_linear_id);
269
270 IF (l_count = 0) THEN
271 fnd_message.set_name('EAM', 'EAM_INVALID_PARAMETER');
272 fnd_message.set_token('NAME', 'p_eam_linear_id : ' || p_eam_linear_id);
273 fnd_msg_pub.add;
274 RAISE fnd_api.g_exc_error;
275 END IF;
276
277 -- Validation of external_source_name
278 SELECT count(*) INTO l_count FROM dual WHERE EXISTS
279 (SELECT 1 FROM mfg_lookups WHERE lookup_code = p_external_source_name
280 AND lookup_type = 'EAM_EXTERNAL_SOURCE_NAME');
281
282 IF (l_count = 0) THEN
283 fnd_message.set_name('EAM', 'EAM_INVALID_PARAMETER');
284 fnd_message.set_token('NAME', 'p_external_source_name : ' || p_external_source_name);
285 fnd_msg_pub.add;
286 RAISE fnd_api.g_exc_error;
287 END IF;
288
289 -- Validation of external_linear_id
290 IF (p_external_linear_id IS NULL) THEN
291 fnd_message.set_name('EAM', 'EAM_INVALID_PARAMETER');
292 fnd_message.set_token('NAME', 'p_external_linear_id : ' || p_external_linear_id);
293 fnd_msg_pub.add;
294 RAISE fnd_api.g_exc_error;
295 END IF;
296
297 SELECT count(*) INTO l_count FROM dual WHERE EXISTS
298 (SELECT 1 FROM eam_linear_locations WHERE external_linear_id = p_external_linear_id
299 AND external_source_name = p_external_source_name
300 AND external_linear_type = p_external_linear_type
301 AND eam_linear_id <> p_eam_linear_id);
302
303 IF (l_count > 0) THEN
304 fnd_message.set_name('EAM', 'EAM_EXT_LINEAR_ID_EXISTS');
305 fnd_msg_pub.add;
306 RAISE fnd_api.g_exc_error;
307 END IF;
308
309 -- Update the record in eam_linear_locations table
310 UPDATE eam_linear_locations SET
311 external_linear_id = p_external_linear_id
312 ,external_source_name = p_external_source_name
313 ,external_linear_name = p_external_linear_name
314 ,external_linear_type = p_external_linear_type
315 WHERE
316 eam_linear_id = p_eam_linear_id;
317
318 -- End of API body.
319
320 -- Standard check of p_commit.
321 IF fnd_api.to_boolean(p_commit) THEN
322 COMMIT WORK;
323 END IF;
324
325 -- Standard call to get message count and if count is 1, get message info.
326 fnd_msg_pub.count_and_get(p_count => x_msg_count
327 ,p_data => x_msg_data);
328 EXCEPTION
329 WHEN fnd_api.g_exc_error THEN
330 ROLLBACK TO eam_linear_locations_pub;
331 x_return_status := fnd_api.g_ret_sts_error;
332 fnd_msg_pub.count_and_get(p_count => x_msg_count
333 ,p_data => x_msg_data);
334 WHEN fnd_api.g_exc_unexpected_error THEN
335 ROLLBACK TO eam_linear_locations_pub;
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337 fnd_msg_pub.count_and_get(p_count => x_msg_count
338 ,p_data => x_msg_data);
339 WHEN OTHERS THEN
340 ROLLBACK TO eam_linear_locations_pub;
341 x_return_status := fnd_api.g_ret_sts_unexp_error;
342 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
343 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
344 END IF;
345 fnd_msg_pub.count_and_get(p_count => x_msg_count
346 ,p_data => x_msg_data);
347 END update_row;
348
349
350 PROCEDURE get_eam_linear_id
351 (
352 p_api_version IN NUMBER
353 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
354 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
355 ,p_external_linear_id IN NUMBER
356 ,p_external_source_name IN VARCHAR2
357 ,p_external_linear_type IN VARCHAR2
358 ,x_eam_linear_id OUT NOCOPY NUMBER
359 ,x_return_status OUT NOCOPY VARCHAR2
360 ,x_msg_count OUT NOCOPY NUMBER
361 ,x_msg_data OUT NOCOPY VARCHAR2
362 ) IS
363
364 l_api_name CONSTANT VARCHAR2(30) := 'get_eam_linear_id';
365 l_api_version CONSTANT NUMBER := 1.0;
366 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
367 l_count NUMBER := 0;
368
369 BEGIN
370 -- Standard Start of API savepoint
371 SAVEPOINT eam_linear_locations_pub;
372
373 -- Standard call to check for call compatibility.
374 IF NOT fnd_api.compatible_api_call(
375 l_api_version
376 ,p_api_version
377 ,l_api_name
378 ,g_pkg_name) THEN
379 RAISE fnd_api.g_exc_unexpected_error;
380 END IF;
381
382 -- Initialize message list if p_init_msg_list is set to TRUE.
383 IF fnd_api.to_boolean(p_init_msg_list) THEN
384 fnd_msg_pub.initialize;
385 END IF;
386
387 -- Initialize API return status to success
388 x_return_status := fnd_api.g_ret_sts_success;
389
390 -- API body
391
395 AND lookup_type = 'EAM_EXTERNAL_SOURCE_NAME');
392 -- Validation of external_source_name
393 SELECT count(*) INTO l_count FROM dual WHERE EXISTS
394 (SELECT 1 FROM mfg_lookups WHERE lookup_code = p_external_source_name
396
397 IF (l_count = 0) THEN
398 fnd_message.set_name('EAM', 'EAM_INVALID_PARAMETER');
399 fnd_message.set_token('NAME', 'p_external_source_name : ' || p_external_source_name);
400 fnd_msg_pub.add;
401 RAISE fnd_api.g_exc_error;
402 END IF;
403
404
405 BEGIN
406 SELECT eam_linear_id INTO x_eam_linear_id FROM eam_linear_locations
407 WHERE external_linear_id = p_external_linear_id
408 AND external_source_name = p_external_source_name
409 AND external_linear_type = p_external_linear_type;
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 x_eam_linear_id := -1;
413 --fnd_message.set_name('EAM', 'EAM_INVALID_EXT_LINEAR_ID');
414 --fnd_msg_pub.add;
415 --RAISE fnd_api.g_exc_error;
416 END;
417
418 -- Standard call to get message count and if count is 1, get message info.
419 fnd_msg_pub.count_and_get(p_count => x_msg_count
420 ,p_data => x_msg_data);
421 EXCEPTION
422 WHEN fnd_api.g_exc_error THEN
423 ROLLBACK TO eam_linear_locations_pub;
424 x_return_status := fnd_api.g_ret_sts_error;
425 fnd_msg_pub.count_and_get(p_count => x_msg_count
426 ,p_data => x_msg_data);
427 WHEN fnd_api.g_exc_unexpected_error THEN
428 ROLLBACK TO eam_linear_locations_pub;
429 x_return_status := fnd_api.g_ret_sts_unexp_error;
430 fnd_msg_pub.count_and_get(p_count => x_msg_count
431 ,p_data => x_msg_data);
432 WHEN OTHERS THEN
433 ROLLBACK TO eam_linear_locations_pub;
434 x_return_status := fnd_api.g_ret_sts_unexp_error;
435 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
436 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
437 END IF;
438 fnd_msg_pub.count_and_get(p_count => x_msg_count
439 ,p_data => x_msg_data);
440 END get_eam_linear_id;
441
442 procedure create_asset(
443 p_api_version IN NUMBER
444 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
445 ,p_commit IN VARCHAR2 := fnd_api.g_false
446 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
447 ,p_external_linear_id IN NUMBER
448 ,p_external_linear_name IN VARCHAR2
449 ,p_external_source_name IN VARCHAR2
450 ,p_external_linear_type IN VARCHAR2
451 ,p_serial_number IN VARCHAR2
452 ,p_user_name IN VARCHAR2
453 ,p_inventory_item_id IN NUMBER
454 ,p_current_organization_id IN NUMBER
455 ,p_owning_department_id IN NUMBER
456 ,p_descriptive_text IN VARCHAR2
457 ,x_object_id OUT NOCOPY VARCHAR2
458 ,x_return_status OUT NOCOPY VARCHAR2
459 ,x_msg_count OUT NOCOPY NUMBER
460 ,x_msg_data OUT NOCOPY VARCHAR2
461 ) IS
462
463 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
464 l_api_version CONSTANT NUMBER := 1.0;
465 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
466 l_count NUMBER := 0;
467 l_x_return_status varchar2(30);
468 l_x_msg_count number;
469 l_x_msg_data varchar2(2000);
470 l_x_eam_linear_id number;
471 l_boolean boolean;
472
473 l_user_id number;
474 l_resp_id number := 111;
475 l_resp_appl_id number := 426;
476
477 BEGIN
478 -- Standard Start of API savepoint
479 SAVEPOINT eam_linear_locations_pub;
480
481
482 -- validate and set the user context
483 l_user_id := return_user_id(p_user_name);
484
485
486 if (l_user_id <> -1) then
487 fnd_global.apps_initialize
488 (
489 user_id => l_user_id,
490 resp_id => l_resp_id,
491 resp_appl_id => l_resp_appl_id
492 );
493 else
494 RAISE fnd_api.g_exc_unexpected_error;
495 end if;
496
497 -- Standard call to check for call compatibility.
498 IF NOT fnd_api.compatible_api_call(
499 l_api_version
500 ,p_api_version
501 ,l_api_name
502 ,g_pkg_name) THEN
503 RAISE fnd_api.g_exc_unexpected_error;
504 END IF;
505
506 -- Initialize message list if p_init_msg_list is set to TRUE.
507 IF fnd_api.to_boolean(p_init_msg_list) THEN
508 fnd_msg_pub.initialize;
509 END IF;
510
511 -- Initialize API return status to success
512 x_return_status := fnd_api.g_ret_sts_success;
513
514 -- API body
515 --first check if asset already exists in EAM
516 eam_linear_locations_pub.get_eam_linear_id(
517 p_api_version => p_api_version
518 ,p_init_msg_list => p_init_msg_list
519 ,p_validation_level => p_validation_level
520 ,p_external_linear_id => p_external_linear_id
521 ,p_external_source_name => p_external_source_name
522 ,p_external_linear_type => p_external_linear_type
523 ,x_eam_linear_id => l_x_eam_linear_id
524 ,x_return_status => l_x_return_status
525 ,x_msg_count => l_x_msg_count
526 ,x_msg_data => l_x_msg_data
527 );
528
529 -- if asset does not exist in ELL, then insert row and get EAM linear id
533 ,p_init_msg_list => p_init_msg_list
530 if (l_x_eam_linear_id = -1) then
531 eam_linear_locations_pub.insert_row(
532 p_api_version => p_api_version
534 ,p_commit => p_commit
535 ,p_validation_level => p_validation_level
536 ,p_external_linear_id => p_external_linear_id
537 ,p_external_linear_name => p_external_linear_name
538 ,p_external_source_name => p_external_source_name
539 ,p_external_linear_type => p_external_linear_type
540 ,x_eam_linear_id => l_x_eam_linear_id
541 ,x_return_status => l_x_return_status
542 ,x_msg_count => l_x_msg_count
543 ,x_msg_data => l_x_msg_data
544 ) ;
545 end if;
546
547 -- create asset with EAM linear Id
548 EAM_AssetNumber_Pub.Insert_Asset_Number
549 (
550 p_api_version => 1.0
551 ,p_init_msg_list => p_init_msg_list
552 ,p_commit => p_commit
553 ,p_validation_level => p_validation_level
554
555 ,x_return_status => x_return_status
556 ,x_msg_count => x_msg_count
557 ,x_msg_data => x_msg_data
558 ,x_object_id => x_object_id
559 ,p_INVENTORY_ITEM_ID => p_inventory_item_id
560 ,p_SERIAL_NUMBER => p_serial_number
561 ,p_CURRENT_STATUS => 3
562 ,p_DESCRIPTIVE_TEXT => p_descriptive_text
563 ,p_CURRENT_ORGANIZATION_ID => p_current_organization_id
564
565 ,p_MAINTAINABLE_FLAG => 'Y'
566 ,p_OWNING_DEPARTMENT_ID => p_OWNING_DEPARTMENT_ID
567 ,p_NETWORK_ASSET_FLAG => 'N'
568
569 ,p_instantiate_flag => TRUE
570 ,p_eam_linear_id => l_x_eam_linear_id
571 );
572
573
574 -- End API body
575 -- Standard check of p_commit.
576 IF FND_API.To_Boolean( p_commit ) THEN
577 COMMIT WORK;
578 END IF;
579 -- Standard call to get message count and if count is 1, get message info.
580 fnd_msg_pub.count_and_get(p_count => x_msg_count
581 ,p_data => x_msg_data);
582 EXCEPTION
583 WHEN fnd_api.g_exc_error THEN
584 ROLLBACK TO eam_linear_locations_pub;
585 x_return_status := fnd_api.g_ret_sts_error;
586 fnd_msg_pub.count_and_get(p_count => x_msg_count
587 ,p_data => x_msg_data);
588 WHEN fnd_api.g_exc_unexpected_error THEN
589 ROLLBACK TO eam_linear_locations_pub;
590 x_return_status := fnd_api.g_ret_sts_unexp_error;
591 fnd_msg_pub.count_and_get(p_count => x_msg_count
592 ,p_data => x_msg_data);
593 WHEN OTHERS THEN
594 ROLLBACK TO eam_linear_locations_pub;
595 x_return_status := fnd_api.g_ret_sts_unexp_error;
596 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
597 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
598 END IF;
599 fnd_msg_pub.count_and_get(p_count => x_msg_count
600 ,p_data => x_msg_data);
601 END create_asset;
602
603 procedure create_work_request(
604 p_api_version IN NUMBER
605 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
606 ,p_commit IN VARCHAR2 := fnd_api.g_false
607 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
608 ,p_external_linear_id IN NUMBER
609 ,p_external_linear_name IN VARCHAR2
610 ,p_external_source_name IN VARCHAR2
611 ,p_external_linear_type IN VARCHAR2
612 ,p_work_request_rec IN WIP_EAM_WORK_REQUESTS%ROWTYPE
613 ,p_user_name IN VARCHAR2
614 ,p_mode IN VARCHAR2
615 ,p_request_log IN VARCHAR2
616 ,x_work_request_id OUT NOCOPY VARCHAR2
617 ,x_return_status OUT NOCOPY VARCHAR2
618 ,x_msg_count OUT NOCOPY NUMBER
619 ,x_msg_data OUT NOCOPY VARCHAR2
620 )
621
622 IS
623
624 l_api_name CONSTANT VARCHAR2(30) := 'create_work_request';
625 l_api_version CONSTANT NUMBER := 1.0;
626 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
627 l_count NUMBER := 0;
628 l_x_return_status varchar2(30);
629 l_x_msg_count number;
630 l_x_msg_data varchar2(2000);
631 l_x_eam_linear_id number;
632 l_work_request_rec WIP_EAM_WORK_REQUESTS%ROWTYPE;
633
634 l_user_id number;
635 l_resp_id number := 111;
636 l_resp_appl_id number := 426;
637
638 BEGIN
639 -- Standard Start of API savepoint
640 SAVEPOINT eam_linear_locations_pub;
641
642
643 -- validate and set the user context
644 l_user_id := return_user_id(p_user_name);
645
646
647 if (l_user_id <> -1) then
648 fnd_global.apps_initialize
649 (
650 user_id => l_user_id,
651 resp_id => l_resp_id,
652 resp_appl_id => l_resp_appl_id
653 );
654 else
655 RAISE fnd_api.g_exc_unexpected_error;
656 end if;
657
658 -- Standard call to check for call compatibility.
659 IF NOT fnd_api.compatible_api_call(
660 l_api_version
661 ,p_api_version
662 ,l_api_name
663 ,g_pkg_name) THEN
664 RAISE fnd_api.g_exc_unexpected_error;
665 END IF;
666
667 -- Initialize message list if p_init_msg_list is set to TRUE.
668 IF fnd_api.to_boolean(p_init_msg_list) THEN
669 fnd_msg_pub.initialize;
670 END IF;
671
675 -- API body
672 -- Initialize API return status to success
673 x_return_status := fnd_api.g_ret_sts_success;
674
676
677 eam_linear_locations_pub.get_eam_linear_id(
678 p_api_version => p_api_version
679 ,p_init_msg_list => p_init_msg_list
680 ,p_validation_level => p_validation_level
681 ,p_external_linear_id => p_external_linear_id
682 ,p_external_source_name => p_external_source_name
683 ,p_external_linear_type => p_external_linear_type
684 ,x_eam_linear_id => l_x_eam_linear_id
685 ,x_return_status => l_x_return_status
686 ,x_msg_count => l_x_msg_count
687 ,x_msg_data => l_x_msg_data
688 );
689
690 -- if asset does not exist in ELL, then insert row and get EAM linear id
691 if (l_x_eam_linear_id = -1) then
692 eam_linear_locations_pub.insert_row(
693 p_api_version => p_api_version
694 ,p_init_msg_list => p_init_msg_list
695 ,p_commit => p_commit
696 ,p_validation_level => p_validation_level
697 ,p_external_linear_id => p_external_linear_id
698 ,p_external_linear_name => p_external_linear_name
699 ,p_external_source_name => p_external_source_name
700 ,p_external_linear_type => p_external_linear_type
701 ,x_eam_linear_id => l_x_eam_linear_id
702 ,x_return_status => l_x_return_status
703 ,x_msg_count => l_x_msg_count
704 ,x_msg_data => l_x_msg_data
705 ) ;
706 end if;
707
708 l_work_request_rec := p_work_request_rec;
709 l_work_request_rec.eam_linear_location_id := l_x_eam_linear_id;
710
711 WIP_EAM_WORKREQUEST_PUB.work_request_import
712 (
713 p_api_version => 1.0
714 ,p_mode => p_mode
715 ,p_work_request_rec => l_work_request_rec
716 ,p_request_log => p_request_log
717 ,p_user_id => l_user_id
718 ,x_work_request_id=>x_work_request_id
719 ,x_return_status => x_return_status
720 ,x_msg_count => x_msg_count
721 ,x_msg_data => x_msg_data
722 );
723
724
725 --END API body
726 -- Standard check of p_commit.
727 IF FND_API.To_Boolean( p_commit ) THEN
728 COMMIT WORK;
729 END IF;
730 -- Standard call to get message count and if count is 1, get message info.
731 fnd_msg_pub.count_and_get(p_count => x_msg_count
732 ,p_data => x_msg_data);
733 EXCEPTION
734 WHEN fnd_api.g_exc_error THEN
735 ROLLBACK TO eam_linear_locations_pub;
736 x_return_status := fnd_api.g_ret_sts_error;
737 fnd_msg_pub.count_and_get(p_count => x_msg_count
738 ,p_data => x_msg_data);
739 WHEN fnd_api.g_exc_unexpected_error THEN
740 ROLLBACK TO eam_linear_locations_pub;
741 x_return_status := fnd_api.g_ret_sts_unexp_error;
742 fnd_msg_pub.count_and_get(p_count => x_msg_count
743 ,p_data => x_msg_data);
744 WHEN OTHERS THEN
745 ROLLBACK TO eam_linear_locations_pub;
746 x_return_status := fnd_api.g_ret_sts_unexp_error;
747 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
748 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
749 END IF;
750 fnd_msg_pub.count_and_get(p_count => x_msg_count
751 ,p_data => x_msg_data);
752
753 end create_work_request;
754
755
756
757
758
759
760
761
762
763 PROCEDURE CREATE_EAM_WO
764 ( p_bo_identifier IN VARCHAR2 := 'EAM'
765 , p_api_version IN NUMBER := 1.0
766 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
767 , p_commit IN VARCHAR2 := fnd_api.g_false
768 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
769 , p_external_source_name IN VARCHAR2
770 , p_external_linear_type IN VARCHAR2 := 'ASSET'
771 , p_external_linear_name IN VARCHAR2
772 , p_external_linear_id IN NUMBER
773 , p_user_name IN VARCHAR2
774 , x_wip_entity_id OUT NOCOPY NUMBER
775 , x_msg_data OUT NOCOPY VARCHAR2
776 , p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type
777 , p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type
778 , p_eam_op_network_tbl IN EAM_PROCESS_WO_PUB.eam_op_network_tbl_type
779 , p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type
780 , p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
781 , p_eam_sub_res_tbl IN EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type
782 , p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
783 , p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type
784 , p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type
785 , p_eam_wo_comp_rec IN EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
786 , p_eam_wo_quality_tbl IN EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type
787 , p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type
788 , p_eam_wo_comp_rebuild_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type
789 , p_eam_wo_comp_mr_read_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type
790 , p_eam_op_comp_tbl IN EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type
791 , p_eam_request_tbl IN EAM_PROCESS_WO_PUB.eam_request_tbl_type
795 , x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type
792 , x_eam_wo_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type
793 , x_eam_op_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_tbl_type
794 , x_eam_op_network_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_network_tbl_type
796 , x_eam_res_inst_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
797 , x_eam_sub_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type
798 , x_eam_res_usage_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
799 , x_eam_mat_req_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type
800 , x_eam_direct_items_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type
801 , x_eam_wo_comp_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type
802 , x_eam_wo_quality_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type
803 , x_eam_meter_reading_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type
804 , x_eam_wo_comp_rebuild_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type
805 , x_eam_wo_comp_mr_read_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type
806 , x_eam_op_comp_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type
807 , x_eam_request_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_request_tbl_type
808 , x_return_status OUT NOCOPY VARCHAR2
809 , x_msg_count OUT NOCOPY NUMBER
810 , p_debug IN VARCHAR2 := fnd_api.g_false
811 , p_output_dir IN VARCHAR2 := NULL
812 , p_debug_filename IN VARCHAR2 := 'EAM_WO_DEBUG.log'
813 , p_debug_file_mode IN VARCHAR2 := 'w'
814 )
815 IS
816
817 l_api_name CONSTANT VARCHAR2(30) := 'create_eam_wo';
818 l_api_version CONSTANT NUMBER := 1.0;
819 l_full_name CONSTANT VARCHAR2(60) := 'eam_linear_locations_pub' || '.' || l_api_name;
820 l_count NUMBER := 0;
821 l_message VARCHAR2(2000);
822 l_x_return_status varchar2(30);
823 l_x_msg_count number;
824 l_x_msg_data varchar2(2000);
825 l_x_eam_linear_id number;
826
827 l_bo_identifier VARCHAR2(30);
828 l_api_version_number NUMBER;
829 l_init_msg_list VARCHAR2(10);
830 l_commit VARCHAR2(2);
831
832 l_external_source_name VARCHAR2(240);
833 l_external_linear_type VARCHAR2(240);
834 l_external_linear_name VARCHAR2(240);
835 l_external_linear_id NUMBER;
836 l_wip_entity_id NUMBER;
837 l_class_code VARCHAR2(10);
838
839 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
840 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
841 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
842 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
843 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
844 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
845 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
846 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
847 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
848 l_x_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
849 l_x_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
850 l_x_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
851 l_x_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
852 l_x_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
853 l_x_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
854 l_x_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
855 l_x_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
856 l_x_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
857 l_x_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
858
859 l_return_status VARCHAR2(10);
860 l_msg_count NUMBER;
861 l_debug VARCHAR2(2);
862 l_output_dir VARCHAR2(240);
863 l_debug_filename VARCHAR2(512);
864 l_debug_file_mode VARCHAR2(2);
865
866 l_user_id number;
867 l_resp_id number := 111;
868 l_resp_appl_id number := 426;
869
870 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
871 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
872 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
873 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
874 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
875 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
876 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
877 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
878 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
879 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
880 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
881 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
882 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
883 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
884 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
885
886 BEGIN
887 -- Standard Start of API savepoint
888 SAVEPOINT create_eam_wo;
889
893
890 -- validate and set the user context
891 l_user_id := return_user_id(p_user_name);
892
894 if (l_user_id <> -1) then
895 fnd_global.apps_initialize
896 (
897 user_id => l_user_id,
898 resp_id => l_resp_id,
899 resp_appl_id => l_resp_appl_id
900 );
901 else
902 RAISE fnd_api.g_exc_unexpected_error;
903 end if;
904
905 -- Standard call to check for call compatibility.
906 IF NOT fnd_api.compatible_api_call(
907 l_api_version
908 ,p_api_version
909 ,l_api_name
910 ,g_pkg_name) THEN
911 RAISE fnd_api.g_exc_unexpected_error;
912 END IF;
913
914 -- Initialize message list if p_init_msg_list is set to TRUE.
915 IF fnd_api.to_boolean(p_init_msg_list) THEN
916 fnd_msg_pub.initialize;
917 END IF;
918
919 -- Initialize API return status to success
920 x_return_status := fnd_api.g_ret_sts_success;
921
922 -- API body
923
924 l_bo_identifier := p_bo_identifier;
925 l_api_version_number := p_api_version;
926 l_init_msg_list := p_init_msg_list;
927 l_commit := p_commit;
928
929 l_external_source_name := p_external_source_name;
930 l_external_linear_type := p_external_linear_type;
931 l_external_linear_name := p_external_linear_name;
932 l_external_linear_id := p_external_linear_id;
933
934 l_eam_wo_rec := p_eam_wo_rec;
935 l_eam_op_tbl := p_eam_op_tbl;
936 l_eam_op_network_tbl := p_eam_op_network_tbl;
937 l_eam_res_tbl := p_eam_res_tbl;
938 l_eam_res_inst_tbl := p_eam_res_inst_tbl;
939 l_eam_sub_res_tbl := p_eam_sub_res_tbl;
940 l_eam_res_usage_tbl := p_eam_res_usage_tbl;
941 l_eam_mat_req_tbl := p_eam_mat_req_tbl;
942 l_eam_direct_items_tbl := p_eam_direct_items_tbl;
943
944 l_debug := p_debug;
945 l_output_dir := p_output_dir;
946 l_debug_filename := p_debug_filename;
947 l_debug_file_mode := p_debug_file_mode;
948
949
950 eam_linear_locations_pub.get_eam_linear_id(
951 p_api_version => p_api_version
952 ,p_init_msg_list => p_init_msg_list
953 ,p_validation_level => p_validation_level
954 ,p_external_linear_id => p_external_linear_id
955 ,p_external_source_name => p_external_source_name
956 ,p_external_linear_type => p_external_linear_type
957 ,x_eam_linear_id => l_x_eam_linear_id
958 ,x_return_status => l_x_return_status
959 ,x_msg_count => l_x_msg_count
960 ,x_msg_data => l_x_msg_data
961 );
962
963 -- if asset does not exist in ELL, then insert row and get EAM linear id
964 if (l_x_eam_linear_id = -1) then
965 eam_linear_locations_pub.insert_row(
966 p_api_version => p_api_version
967 ,p_init_msg_list => p_init_msg_list
968 ,p_commit => p_commit
969 ,p_validation_level => p_validation_level
970 ,p_external_linear_id => p_external_linear_id
971 ,p_external_linear_name => p_external_linear_name
972 ,p_external_source_name => p_external_source_name
973 ,p_external_linear_type => p_external_linear_type
974 ,x_eam_linear_id => l_x_eam_linear_id
975 ,x_return_status => l_x_return_status
976 ,x_msg_count => l_x_msg_count
977 ,x_msg_data => l_x_msg_data
978 ) ;
979 end if;
980
981 l_eam_wo_rec := p_eam_wo_rec;
982 l_eam_wo_rec.eam_linear_location_id := l_x_eam_linear_id;
983
984
985 -- If the external source has not passed a WAC, then default it
986 if l_eam_wo_rec.class_code is null then
987
988 -- First get the asset number for the maintenance_object_id
989 -- if it has not been passed.
990 if l_eam_wo_rec.asset_number is null then
991 select serial_number into l_eam_wo_rec.asset_number
992 from mtl_serial_numbers
993 where gen_object_id = l_eam_wo_rec.maintenance_object_id
994 and current_organization_id = l_eam_wo_rec.organization_id;
995 end if;
996
997 WIP_EAM_UTILS.DEFAULT_ACC_CLASS(
998 p_org_id => l_eam_wo_rec.organization_id,
999 p_job_type => 1,
1000 p_serial_number => l_eam_wo_rec.asset_number,
1001 p_asset_group_id => l_eam_wo_rec.asset_group_id,
1002 p_parent_wo_id => l_eam_wo_rec.parent_wip_entity_id,
1003 p_asset_activity_id => l_eam_wo_rec.asset_activity_id,
1004 p_project_id => l_eam_wo_rec.project_id,
1005 p_task_id => l_eam_wo_rec.task_id,
1006 x_class_code => l_class_code,
1007 x_return_status => l_x_return_status,
1008 x_msg_data => l_x_msg_data
1009 );
1010
1011 l_eam_wo_rec.class_code := l_class_code;
1012
1013 end if;
1014
1015
1016 if p_debug = fnd_api.g_false then l_debug := 'N';
1020
1017 else l_debug := 'Y';end if;
1018 if p_commit = fnd_api.g_false then l_commit := 'N';
1019 else l_commit := 'Y';end if;
1021 EAM_PROCESS_WO_PUB.PROCESS_WO
1022 ( p_bo_identifier => l_bo_identifier
1023 , p_api_version_number => l_api_version_number
1024 , p_init_msg_list => fnd_api.to_boolean(l_init_msg_list)
1025 , p_commit => l_commit
1026 , p_eam_wo_rec => l_eam_wo_rec
1027 , p_eam_op_tbl => l_eam_op_tbl
1028 , p_eam_op_network_tbl => l_eam_op_network_tbl
1029 , p_eam_res_tbl => l_eam_res_tbl
1030 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1031 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1032 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1033 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1034 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1035 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1036 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1037 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1038 , p_eam_counter_prop_tbl => l_x_eam_counter_prop_tbl
1039 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1040 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1041 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1042 , p_eam_request_tbl => l_eam_request_tbl
1043 , x_eam_wo_rec => l_x_eam_wo_rec
1044 , x_eam_op_tbl => l_x_eam_op_tbl
1045 , x_eam_op_network_tbl => l_x_eam_op_network_tbl
1046 , x_eam_res_tbl => l_x_eam_res_tbl
1047 , x_eam_res_inst_tbl => l_x_eam_res_inst_tbl
1048 , x_eam_sub_res_tbl => l_x_eam_sub_res_tbl
1049 , x_eam_res_usage_tbl => l_x_eam_res_usage_tbl
1050 , x_eam_mat_req_tbl => l_x_eam_mat_req_tbl
1051 , x_eam_direct_items_tbl => l_x_eam_direct_items_tbl
1052 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
1053 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1054 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1055 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1056 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1057 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1058 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1059 , x_eam_request_tbl => l_out_eam_request_tbl
1060 , x_return_status => x_return_status
1061 , x_msg_count => x_msg_count
1062 , p_debug => l_debug
1063 , p_output_dir => l_output_dir
1064 , p_debug_filename => l_debug_filename
1065 , p_debug_file_mode => l_debug_file_mode
1066 );
1067
1068 if x_return_status <> 'S' and x_msg_count > 0 then
1069 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
1070 p_encoded => 'F',
1071 p_data => x_msg_data,
1072 p_msg_index_out => l_count);
1073 end if;
1074
1075 x_wip_entity_id := l_x_eam_wo_rec.wip_entity_id;
1076 x_eam_wo_rec := l_x_eam_wo_rec;
1077 x_eam_op_tbl := l_x_eam_op_tbl;
1078 x_eam_op_network_tbl := l_x_eam_op_network_tbl;
1079 x_eam_res_tbl := l_x_eam_res_tbl;
1080 x_eam_res_inst_tbl := l_x_eam_res_inst_tbl;
1081 x_eam_sub_res_tbl := l_x_eam_sub_res_tbl;
1082 x_eam_res_usage_tbl := l_x_eam_res_usage_tbl;
1083 x_eam_mat_req_tbl := l_x_eam_mat_req_tbl;
1084 x_eam_direct_items_tbl := l_x_eam_direct_items_tbl;
1085
1086
1087
1088 --END API body
1089 -- Standard check of p_commit.
1090 IF FND_API.To_Boolean( p_commit ) THEN
1091 COMMIT WORK;
1092 END IF;
1093 -- Standard call to get message count and if count is 1, get message info.
1094 fnd_msg_pub.count_and_get(p_count => x_msg_count
1095 ,p_data => x_msg_data);
1096 EXCEPTION
1097 WHEN fnd_api.g_exc_error THEN
1098 ROLLBACK TO create_eam_wo;
1099 x_return_status := fnd_api.g_ret_sts_error;
1100 fnd_msg_pub.count_and_get(p_count => x_msg_count
1101 ,p_data => x_msg_data);
1102 WHEN fnd_api.g_exc_unexpected_error THEN
1103 ROLLBACK TO create_eam_wo;
1104 x_return_status := fnd_api.g_ret_sts_unexp_error;
1105 fnd_msg_pub.count_and_get(p_count => x_msg_count
1106 ,p_data => x_msg_data);
1107 WHEN OTHERS THEN
1108 ROLLBACK TO create_eam_wo;
1109 x_return_status := fnd_api.g_ret_sts_unexp_error;
1110 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1111 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1112 END IF;
1113 fnd_msg_pub.count_and_get(p_count => x_msg_count
1114 ,p_data => x_msg_data);
1115
1116 end create_eam_wo;
1117
1118 procedure return_bom_departments
1119 ( p_organization_id in number
1120 ,p_user_name in VARCHAR2
1121 ,x_bom_departments_table out nocopy eam_linear_locations_pub.bom_departments_table
1122 ) is
1123 l_user_id number;
1124 l_resp_id number;
1125 l_resp_appl_id number;
1126 l_dept_index binary_integer;
1127
1128 cursor dept_cursor (p_organization_id in number) is
1129 select department_id, department_code,description,organization_id
1130 from bom_departments
1131 where organization_id = p_organization_id
1132 and nvl(disable_date, sysdate+1) >= sysdate;
1136 if (check_valid_user(p_user_name) <> -1) then
1133
1134 begin
1135
1137
1138 l_user_id := return_user_id(p_user_name);
1139 /*
1140 fnd_global.apps_initialize
1141 (
1142 user_id => l_user_id,
1143 resp_id => l_resp_id,
1144 resp_appl_id => l_resp_appl_id
1145 );
1146 */
1147 l_dept_index := 1;
1148
1149 FOR l_bom_dept_row in dept_cursor(p_organization_id) LOOP
1150 x_bom_departments_table(l_dept_index).Department_Id := l_bom_dept_row.department_id;
1151 x_bom_departments_table(l_dept_index).Department_Code := l_bom_dept_row.department_Code;
1152 x_bom_departments_table(l_dept_index).Description := l_bom_dept_row.Description;
1153 x_bom_departments_table(l_dept_index).Organization_Id := l_bom_dept_row.Organization_Id;
1154 l_dept_index := l_dept_index + 1;
1155 END LOOP;
1156 end if;
1157
1158 end return_bom_departments;
1159
1160 Procedure return_organizations
1161 (
1162 p_user_name VARCHAR2
1163 ,x_organizations_table OUT NOCOPY EAM_LINEAR_LOCATIONS_PUB.Org_Access_Table
1164 )
1165 is
1166 l_user_id number;
1167 l_organizations_index binary_integer;
1168
1169 cursor org_cursor is
1170 select distinct(hou.organization_id) org_id, mp.organization_code org_code,hout.name org_name
1171 from hr_all_organization_units hou,
1172 hr_all_organization_units_tl hout,
1173 mtl_parameters mp,wip_eam_parameters wep
1174 where hou.organization_id = mp.organization_id
1175 and hou.organization_id = hout.organization_id
1176 and hou.organization_id = wep.organization_id
1177 AND hout.LANGUAGE = USERENV('LANG')
1178 and NVL(mp.eam_enabled_flag,'N') = 'Y';
1179
1180
1181 begin
1182 l_user_id := check_valid_user(p_user_name);
1183
1184 l_organizations_index := 1;
1185 if (l_user_id <> -1) then
1186 FOR l_org_row IN org_cursor
1187 LOOP
1188 x_organizations_table(l_organizations_index).Organization_Id := l_org_row.Org_Id;
1189 x_organizations_table(l_organizations_index).Organization_Code := l_org_row.Org_Code;
1190 x_organizations_table(l_organizations_index).Organization_Name := l_org_row.Org_Name;
1191 l_organizations_index := l_organizations_index + 1;
1192 END LOOP;
1193
1194
1195 end if;
1196
1197 end return_organizations;
1198
1199
1200
1201 Procedure return_work_request_details
1202 (
1203 p_user_name VARCHAR2
1204 , p_work_request_id number
1205 , x_work_request_table OUT NOCOPY EAM_LINEAR_LOCATIONS_PUB.Work_Request_Table
1206 )
1207
1208 is
1209 l_user_id number;
1210 l_wr_index binary_integer;
1211 l_lang varchar2(10);
1212 l_resp varchar2(35);
1213 l_resp_id number;
1214 l_appl_id number;
1215
1216 cursor wr_cursor(resp in number, appl in number) is
1217 select wewr.work_request_number work_request_number, wewr.asset_number asset_number,
1218 oav.organization_code organization_code, oav.organization_name organization_name,
1219 ml.meaning work_request_status, ml1.meaning work_request_priority,
1220 bd.department_code owning_dept_code, bd.description owning_dept_description,
1221 we.wip_entity_name work_order, wewr.description description,
1222 wewr.expected_resolution_date expected_resolution_date,
1223 ml2.meaning work_request_type, wewr.phone_number phone_number,
1224 wewr.e_mail e_mail, wewr.contact_preference contact_preference
1225 from org_access_view oav, mfg_lookups ml, mfg_lookups ml1,
1226 mfg_lookups ml2, wip_eam_work_requests wewr, wip_entities we,
1227 bom_departments bd
1228 where wewr.work_request_id = p_work_request_id
1229 and oav.organization_id = wewr.organization_id
1230 and oav.resp_application_id = appl
1231 and oav.responsibility_id = resp
1232 and ml.lookup_type(+) = 'WIP_EAM_WORK_REQ_STATUS'
1233 and ml.lookup_code(+) = wewr.work_request_status_id
1234 and ml1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
1235 and ml1.lookup_code(+) = wewr.work_request_priority_id
1236 and ml2.lookup_type(+) = 'WIP_EAM_WORK_REQ_TYPE'
1237 and ml2.lookup_code(+) = wewr.work_request_type_id
1238 and bd.department_id(+) = wewr.work_request_owning_dept
1239 and we.wip_entity_id(+) = wewr.wip_entity_id;
1240
1241
1242
1243
1244 begin
1245 l_user_id := check_valid_user(p_user_name);
1246 l_appl_id := 426;
1247 l_lang := 'US';
1248 l_resp := 'Linear Asset Management User';
1249
1250 select responsibility_id
1251 into l_resp_id
1252 from fnd_responsibility_tl
1253 where responsibility_name = l_resp
1254 and application_id = l_appl_id
1255 and language = l_lang;
1256
1257 l_wr_index := 1;
1258 if (l_user_id <> -1) then
1259 FOR l_wr_row IN wr_cursor(l_resp_id, l_appl_id)
1260 LOOP
1261 x_work_request_table(l_wr_index).Work_Request_Number := l_wr_row.Work_Request_Number;
1262 x_work_request_table(l_wr_index).Asset_Number := l_wr_row.Asset_Number;
1263 x_work_request_table(l_wr_index).ORGANIZATION_CODE := l_wr_row.ORGANIZATION_CODE;
1264 x_work_request_table(l_wr_index).ORGANIZATION_NAME := l_wr_row.ORGANIZATION_NAME;
1265 x_work_request_table(l_wr_index).work_request_status := l_wr_row.work_request_status;
1266 x_work_request_table(l_wr_index).work_request_priority := l_wr_row.work_request_priority;
1267 x_work_request_table(l_wr_index).owning_dept_code := l_wr_row.owning_dept_code;
1268 x_work_request_table(l_wr_index).owning_dept_description := l_wr_row.owning_dept_description;
1269 x_work_request_table(l_wr_index).EXPECTED_RESOLUTION_DATE := l_wr_row.EXPECTED_RESOLUTION_DATE;
1270 x_work_request_table(l_wr_index).work_order := l_wr_row.work_order;
1271 x_work_request_table(l_wr_index).DESCRIPTION := l_wr_row.DESCRIPTION;
1275 x_work_request_table(l_wr_index).CONTACT_PREFERENCE := l_wr_row.CONTACT_PREFERENCE;
1272 x_work_request_table(l_wr_index).WORK_REQUEST_TYPE := l_wr_row.WORK_REQUEST_TYPE;
1273 x_work_request_table(l_wr_index).PHONE_NUMBER := l_wr_row.PHONE_NUMBER;
1274 x_work_request_table(l_wr_index).E_MAIL := l_wr_row.E_MAIL;
1276 l_wr_index := l_wr_index + 1;
1277 END LOOP;
1278
1279 end if;
1280
1281 end return_work_request_details;
1282
1283
1284
1285
1286 Procedure return_work_order_details
1287 (
1288 p_user_name VARCHAR2
1289 , p_wip_entity_id number
1290 , x_work_order_rec OUT NOCOPY EAM_LINEAR_LOCATIONS_PUB.Work_Order_Record
1291 )
1292
1293 IS
1294
1295 l_user_id number;
1296
1297 BEGIN
1298
1299 l_user_id := check_valid_user(p_user_name);
1300
1301 if (l_user_id <> -1) then
1302
1303
1304 SELECT we.wip_entity_name
1305 , wdj.wip_entity_id
1306 , wdj.organization_id
1307 , wdj.description
1308 , wdj.asset_number
1309 , wdj.asset_group_id
1310 , wdj.rebuild_item_id
1311 , wdj.rebuild_serial_number
1312 , we.gen_object_id
1313 , wdj.maintenance_object_id
1314 , wdj.maintenance_object_type
1315 , wdj.maintenance_object_source
1316 , wdj.eam_linear_location_id
1317 , wdj.class_code
1318 , wdj.primary_item_id
1319 , wdj.activity_type
1320 , wdj.activity_cause
1321 , wdj.activity_source
1322 , wdj.work_order_type
1323 , wdj.status_type
1324 , ml.meaning as wo_status
1325 , wdj.start_quantity
1326 , wdj.date_released
1327 , wdj.owning_department
1328 , wdj.priority
1329 , wdj.requested_start_date
1330 , wdj.due_date
1331 , wdj.shutdown_type
1332 , wdj.firm_planned_flag
1333 , wdj.notification_required
1334 , wdj.tagout_required
1335 , wdj.plan_maintenance
1336 , wdj.project_id
1337 , wdj.task_id
1338 , wdj.end_item_unit_number
1339 , wdj.schedule_group_id
1340 , wdj.bom_revision_date
1341 , wdj.routing_revision_date
1342 , wdj.alternate_routing_designator
1343 , wdj.alternate_bom_designator
1344 , wdj.routing_revision
1345 , wdj.bom_revision
1346 , wdj.parent_wip_entity_id
1347 , wdj.manual_rebuild_flag
1348 , wdj.pm_schedule_id
1349 , wdj.material_account
1350 , wdj.material_overhead_account
1351 , wdj.resource_account
1352 , wdj.outside_processing_account
1353 , wdj.material_variance_account
1354 , wdj.resource_variance_account
1355 , wdj.outside_proc_variance_account
1356 , wdj.std_cost_adjustment_account
1357 , wdj.overhead_account
1358 , wdj.overhead_variance_account
1359 , wdj.scheduled_start_date
1360 , wdj.scheduled_completion_date
1361 , wdj.common_bom_sequence_id
1362 , wdj.common_routing_sequence_id
1363 , wdj.po_creation_time
1364 , wdj.attribute_category
1365 , wdj.attribute1
1366 , wdj.attribute2
1367 , wdj.attribute3
1368 , wdj.attribute4
1369 , wdj.attribute5
1370 , wdj.attribute6
1371 , wdj.attribute7
1372 , wdj.attribute8
1373 , wdj.attribute9
1374 , wdj.attribute10
1375 , wdj.attribute11
1376 , wdj.attribute12
1377 , wdj.attribute13
1378 , wdj.attribute14
1379 , wdj.attribute15
1380 , wdj.material_issue_by_mo
1381 , wdj.source_line_id
1382 , wdj.source_code
1383 , wdj.issue_zero_cost_flag
1384 INTO
1385 x_work_order_rec.wip_entity_name
1386 , x_work_order_rec.wip_entity_id
1387 , x_work_order_rec.organization_id
1388 , x_work_order_rec.description
1389 , x_work_order_rec.asset_number
1390 , x_work_order_rec.asset_group_id
1391 , x_work_order_rec.rebuild_item_id
1392 , x_work_order_rec.rebuild_serial_number
1393 , x_work_order_rec.gen_object_id
1394 , x_work_order_rec.maintenance_object_id
1395 , x_work_order_rec.maintenance_object_type
1396 , x_work_order_rec.maintenance_object_source
1397 , x_work_order_rec.eam_linear_location_id
1398 , x_work_order_rec.class_code
1399 , x_work_order_rec.asset_activity_id
1400 , x_work_order_rec.activity_type
1401 , x_work_order_rec.activity_cause
1402 , x_work_order_rec.activity_source
1403 , x_work_order_rec.work_order_type
1404 , x_work_order_rec.status_type
1405 , x_work_order_rec.wo_status
1406 , x_work_order_rec.job_quantity
1407 , x_work_order_rec.date_released
1408 , x_work_order_rec.owning_department
1409 , x_work_order_rec.priority
1410 , x_work_order_rec.requested_start_date
1411 , x_work_order_rec.due_date
1412 , x_work_order_rec.shutdown_type
1413 , x_work_order_rec.firm_planned_flag
1414 , x_work_order_rec.notification_required
1415 , x_work_order_rec.tagout_required
1416 , x_work_order_rec.plan_maintenance
1417 , x_work_order_rec.project_id
1418 , x_work_order_rec.task_id
1419 , x_work_order_rec.end_item_unit_number
1420 , x_work_order_rec.schedule_group_id
1421 , x_work_order_rec.bom_revision_date
1422 , x_work_order_rec.routing_revision_date
1423 , x_work_order_rec.alternate_routing_designator
1424 , x_work_order_rec.alternate_bom_designator
1425 , x_work_order_rec.routing_revision
1426 , x_work_order_rec.bom_revision
1427 , x_work_order_rec.parent_wip_entity_id
1428 , x_work_order_rec.manual_rebuild_flag
1429 , x_work_order_rec.pm_schedule_id
1430 , x_work_order_rec.material_account
1431 , x_work_order_rec.material_overhead_account
1432 , x_work_order_rec.resource_account
1433 , x_work_order_rec.outside_processing_account
1434 , x_work_order_rec.material_variance_account
1435 , x_work_order_rec.resource_variance_account
1436 , x_work_order_rec.outside_proc_variance_account
1437 , x_work_order_rec.std_cost_adjustment_account
1438 , x_work_order_rec.overhead_account
1439 , x_work_order_rec.overhead_variance_account
1440 , x_work_order_rec.scheduled_start_date
1441 , x_work_order_rec.scheduled_completion_date
1442 , x_work_order_rec.common_bom_sequence_id
1443 , x_work_order_rec.common_routing_sequence_id
1444 , x_work_order_rec.po_creation_time
1445 , x_work_order_rec.attribute_category
1446 , x_work_order_rec.attribute1
1447 , x_work_order_rec.attribute2
1448 , x_work_order_rec.attribute3
1449 , x_work_order_rec.attribute4
1450 , x_work_order_rec.attribute5
1451 , x_work_order_rec.attribute6
1452 , x_work_order_rec.attribute7
1453 , x_work_order_rec.attribute8
1454 , x_work_order_rec.attribute9
1455 , x_work_order_rec.attribute10
1456 , x_work_order_rec.attribute11
1457 , x_work_order_rec.attribute12
1458 , x_work_order_rec.attribute13
1459 , x_work_order_rec.attribute14
1460 , x_work_order_rec.attribute15
1461 , x_work_order_rec.material_issue_by_mo
1462 , x_work_order_rec.source_line_id
1463 , x_work_order_rec.source_code
1464 , x_work_order_rec.issue_zero_cost_flag
1465 FROM wip_discrete_jobs wdj, wip_entities we, mfg_lookups ml
1466 WHERE wdj.wip_entity_id = we.wip_entity_id
1467 AND wdj.organization_id = we.organization_id
1468 AND wdj.wip_entity_id = p_wip_entity_id
1469 AND wdj.status_type = ml.lookup_code
1470 AND ml.lookup_type = 'WIP_JOB_STATUS';
1471
1472 end if;
1473
1474 END;
1475
1476
1477
1478
1479 END eam_linear_locations_pub;