1 PACKAGE BODY EAM_COMMON_UTILITIES_PVT AS
2 /* $Header: EAMPUTLB.pls 120.47.12010000.2 2008/11/08 01:33:08 mashah ship $*/
3 -- Start of comments
4 -- API name : APIname
5 -- Type : Public or Group or 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 -- parameter1
17 -- parameter2
18 -- .
19 -- .
20 -- OUT x_return_status OUT VARCHAR2(1)
21 -- x_msg_count OUT NUMBER
22 -- x_msg_data OUT VARCHAR2(2000)
23 -- parameter1
24 -- parameter2
25 -- .
26 -- .
27 -- Version Current version x.x
28 -- Changed....
29 -- previous version y.y
30 -- Changed....
31 -- .
32 -- .
33 -- previous version 2.0
34 -- Changed....
35 -- Initial version 1.0
36 --
37 -- Notes Note text
38 --
39 -- End of comments
40
41 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_COMMON_UTILITIES_PVT';
42
43
44 PROCEDURE get_org_code(
45 p_api_version IN NUMBER
46 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
47 ,p_commit IN VARCHAR2 := fnd_api.g_false
48 ,p_validation_level IN NUMBER
49 := fnd_api.g_valid_level_full
50 ,p_organization_id IN NUMBER
51 ,x_organization_code OUT NOCOPY VARCHAR2
52 ,x_return_status OUT NOCOPY VARCHAR2
53 ,x_msg_count OUT NOCOPY NUMBER
54 ,x_msg_data OUT NOCOPY VARCHAR2)
55
56 IS
57 l_api_name CONSTANT VARCHAR2(30) := 'get_org_code';
58 l_api_version CONSTANT NUMBER := 1.0;
59 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
60 l_organization_id NUMBER;
61 l_organization_code VARCHAR2(3) ;
62 l_stmt_num NUMBER;
63 CURSOR c_org_code( p_org_id IN NUMBER) IS
64 SELECT MP.organization_code
65 FROM mtl_parameters MP
66 WHERE mp.organization_id = p_org_id ;
67
68 BEGIN
69 -- Standard Start of API savepoint
70 l_stmt_num := 10;
71 SAVEPOINT get_org_code_pvt;
72
73 l_stmt_num := 20;
74 -- Standard call to check for call compatibility.
75 IF NOT fnd_api.compatible_api_call(
76 l_api_version
77 ,p_api_version
78 ,l_api_name
79 ,g_pkg_name) THEN
80 RAISE fnd_api.g_exc_unexpected_error;
81 END IF;
82
83 l_stmt_num := 30;
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF fnd_api.to_boolean(p_init_msg_list) THEN
86 fnd_msg_pub.initialize;
87 END IF;
88
89 l_stmt_num := 40;
90 -- Initialize API return status to success
91 x_return_status := fnd_api.g_ret_sts_success;
92
93 l_stmt_num := 50;
94 -- API body
95 l_organization_id := 0;
96
97 l_stmt_num := 60;
98 IF (p_organization_id IS NULL) THEN
99
100 fnd_message.set_name('EAM', 'EAM_INPUT_PARAMS_NULL');
101 fnd_message.set_token('EAM_DEBUG',l_full_name||'('||l_stmt_num||')');
102 fnd_msg_pub.add;
103 RAISE fnd_api.g_exc_error;
104 END IF;
105
106 l_organization_id := p_organization_id;
107
108 l_stmt_num := 70;
109 OPEN c_org_code (l_organization_id) ;
110 FETCH c_org_code INTO l_organization_code;
111
112 IF (c_org_code%NOTFOUND) THEN
113
114 fnd_message.set_name('EAM', 'EAM_ORG_CODE_NULL');
115 fnd_message.set_token('EAM_DEBUG',l_full_name||'('||l_stmt_num||')');
116 fnd_msg_pub.add;
117 RAISE fnd_api.g_exc_error;
118 END IF;
119 CLOSE c_org_code;
120
121 x_organization_code := l_organization_code;
122
123 l_stmt_num := 998;
124 -- End of API body.
125 -- Standard check of p_commit.
126 IF fnd_api.to_boolean(p_commit) THEN
127 COMMIT WORK;
128 END IF;
129
130 l_stmt_num := 999;
131 -- Standard call to get message count and if count is 1, get message info.
132 fnd_msg_pub.count_and_get(
133 p_count => x_msg_count
134 ,p_data => x_msg_data);
135 EXCEPTION
136 WHEN fnd_api.g_exc_error THEN
137 ROLLBACK TO get_org_code_pvt;
138 x_return_status := fnd_api.g_ret_sts_error;
139 fnd_msg_pub.count_and_get(
140 p_count => x_msg_count
141 ,p_data => x_msg_data);
142 WHEN fnd_api.g_exc_unexpected_error THEN
143 ROLLBACK TO get_org_code_pvt;
144 x_return_status := fnd_api.g_ret_sts_unexp_error;
145
146 fnd_msg_pub.count_and_get(
147 p_count => x_msg_count
148 ,p_data => x_msg_data);
149 WHEN OTHERS THEN
150 ROLLBACK TO get_org_code_pvt;
151 x_return_status := fnd_api.g_ret_sts_unexp_error;
152 IF fnd_msg_pub.check_msg_level(
153 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
154 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
155 END IF;
156
157 fnd_msg_pub.count_and_get(
158 p_count => x_msg_count
159 ,p_data => x_msg_data);
160 END get_org_code;
161
162 PROCEDURE get_item_id(
163 p_api_version IN NUMBER
164 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
165 ,p_commit IN VARCHAR2 := fnd_api.g_false
166 ,p_validation_level IN NUMBER
167 := fnd_api.g_valid_level_full
168 ,p_organization_id IN NUMBER
169 ,p_concatenated_segments IN VARCHAR2
170 ,x_inventory_item_id OUT NOCOPY NUMBER
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2)
174 IS
175 l_api_name CONSTANT VARCHAR2(30) := 'get_org_code';
176 l_api_version CONSTANT NUMBER := 1.0;
177 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
178 l_organization_id NUMBER;
179 l_concatenated_segments VARCHAR2(800);
180 l_inventory_item_id NUMBER;
181 l_stmt_num NUMBER;
182 BEGIN
183 -- Standard Start of API savepoint
184 l_stmt_num := 10;
185 SAVEPOINT get_item_id_pvt;
186
187 l_stmt_num := 20;
188 -- Standard call to check for call compatibility.
189 IF NOT fnd_api.compatible_api_call(
190 l_api_version
191 ,p_api_version
192 ,l_api_name
193 ,g_pkg_name) THEN
194 RAISE fnd_api.g_exc_unexpected_error;
195 END IF;
196
197 l_stmt_num := 30;
198 -- Initialize message list if p_init_msg_list is set to TRUE.
199 IF fnd_api.to_boolean(p_init_msg_list) THEN
200 fnd_msg_pub.initialize;
201 END IF;
202
203 l_stmt_num := 40;
204 -- Initialize API return status to success
205 x_return_status := fnd_api.g_ret_sts_success;
206
207 l_stmt_num := 50;
208 -- API body
209 l_organization_id := NULL;
210 l_concatenated_segments := NULL;
211 l_inventory_item_id := NULL;
212
213 l_stmt_num := 60;
214 IF (p_organization_id IS NULL OR p_concatenated_segments IS NULL) THEN
215
216 fnd_message.set_name('EAM', 'EAM_INPUT_PARAMS_NULL');
217 fnd_message.set_token('EAM_DEBUG',l_full_name||'('||l_stmt_num||')');
218 fnd_msg_pub.add;
219 RAISE fnd_api.g_exc_error;
220 END IF;
221
222 l_organization_id := p_organization_id;
223 l_concatenated_segments := p_concatenated_segments;
224
225 l_stmt_num := 70;
226
227 SELECT msikfv.inventory_item_id
228 INTO l_inventory_item_id
229 FROM mtl_system_items_kfv msikfv, mtl_parameters mp
230 WHERE msikfv.organization_id = mp.organization_id
231 AND mp.maint_organization_id = l_organization_id
232 AND msikfv.concatenated_segments = l_concatenated_segments
233 AND rownum = 1;
234
235 x_inventory_item_id := l_inventory_item_id;
236
237 l_stmt_num := 998;
238 -- End of API body.
239 -- Standard check of p_commit.
240 IF fnd_api.to_boolean(p_commit) THEN
241 COMMIT WORK;
242 END IF;
243
244 l_stmt_num := 999;
245 -- Standard call to get message count and if count is 1, get message info.
246 fnd_msg_pub.count_and_get(
247 p_count => x_msg_count
248 ,p_data => x_msg_data);
249 EXCEPTION
250 WHEN fnd_api.g_exc_error THEN
251 ROLLBACK TO get_org_code_pvt;
252 x_return_status := fnd_api.g_ret_sts_error;
253 fnd_msg_pub.count_and_get(
254 p_count => x_msg_count
255 ,p_data => x_msg_data);
256 WHEN fnd_api.g_exc_unexpected_error THEN
257 ROLLBACK TO get_org_code_pvt;
258 x_return_status := fnd_api.g_ret_sts_unexp_error;
259 fnd_msg_pub.count_and_get(
260 p_count => x_msg_count
261 ,p_data => x_msg_data);
262 WHEN OTHERS THEN
263 ROLLBACK TO get_org_code_pvt;
264 x_return_status := fnd_api.g_ret_sts_unexp_error;
265
266 IF fnd_msg_pub.check_msg_level(
267 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
268 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
269 END IF;
270
271 fnd_msg_pub.count_and_get(
272 p_count => x_msg_count
273 ,p_data => x_msg_data);
274 END get_item_id;
275
276
277
278
279
280
281 PROCEDURE get_current_period(
282 p_api_version IN NUMBER
283 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
284 ,p_commit IN VARCHAR2 := fnd_api.g_false
285 ,p_validation_level IN NUMBER
286 := fnd_api.g_valid_level_full
287 ,p_organization_id IN NUMBER
288 ,x_period_name OUT NOCOPY VARCHAR2
289 ,x_return_status OUT NOCOPY VARCHAR2
290 ,x_msg_count OUT NOCOPY NUMBER
291 ,x_msg_data OUT NOCOPY VARCHAR2)
292
293 IS
294 l_api_name CONSTANT VARCHAR2(30) := 'get_current_period';
295 l_api_version CONSTANT NUMBER := 1.0;
296 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
297 l_organization_id NUMBER;
298 l_period_name VARCHAR2(30);
299 l_stmt_num NUMBER;
300 l_period_set_name VARCHAR2(30);
301
302 BEGIN
303 -- Standard Start of API savepoint
304 l_stmt_num := 10;
305 SAVEPOINT get_current_period_pvt;
306
307 l_stmt_num := 20;
308 -- Standard call to check for call compatibility.
309 IF NOT fnd_api.compatible_api_call(
310 l_api_version
311 ,p_api_version
312 ,l_api_name
313 ,g_pkg_name) THEN
314 RAISE fnd_api.g_exc_unexpected_error;
315 END IF;
316
317 l_stmt_num := 30;
318 -- Initialize message list if p_init_msg_list is set to TRUE.
319 IF fnd_api.to_boolean(p_init_msg_list) THEN
320 fnd_msg_pub.initialize;
321 END IF;
322
323 l_stmt_num := 40;
324 -- Initialize API return status to success
325 x_return_status := fnd_api.g_ret_sts_success;
326
327 l_stmt_num := 50;
328 -- API body
329 l_organization_id := 0;
330 l_period_name := ' ';
331
332 l_stmt_num := 60;
333 IF (p_organization_id IS NULL) THEN
334
335 fnd_message.set_name('EAM', 'EAM_INPUT_PARAMS_NULL');
336 fnd_message.set_token('EAM_DEBUG',l_full_name||'('||l_stmt_num||')');
337 fnd_msg_pub.add;
338 RAISE fnd_api.g_exc_error;
339 END IF;
340
341 l_organization_id := p_organization_id;
342
343 l_stmt_num := 70;
344
345 SELECT gsob.period_set_name
346 INTO l_period_set_name
347 FROM hr_organization_information ood,
348 gl_sets_of_books gsob
349 WHERE ood.organization_id = l_organization_id
350 AND to_number(ood.org_information1) = gsob.set_of_books_id
351 AND ood.org_information_context||'' = 'Accounting Information';
352
353 select nvl(max(period_name),'') into l_period_name from gl_periods where start_date <= sysdate and (end_date+1) >= sysdate and period_set_name = l_period_set_name;
354
355 x_period_name := l_period_name;
356
357 l_stmt_num := 998;
358 -- End of API body.
359 -- Standard check of p_commit.
360 IF fnd_api.to_boolean(p_commit) THEN
361 COMMIT WORK;
362 END IF;
363
364 l_stmt_num := 999;
365 -- Standard call to get message count and if count is 1, get message info.
366 fnd_msg_pub.count_and_get(
367 p_count => x_msg_count
368 ,p_data => x_msg_data);
369 EXCEPTION
370 WHEN fnd_api.g_exc_error THEN
371 ROLLBACK TO get_current_period_pvt;
372 x_return_status := fnd_api.g_ret_sts_error;
373 fnd_msg_pub.count_and_get(
374 p_count => x_msg_count
375 ,p_data => x_msg_data);
376 WHEN fnd_api.g_exc_unexpected_error THEN
377 ROLLBACK TO get_current_period_pvt;
378 x_return_status := fnd_api.g_ret_sts_unexp_error;
379 fnd_msg_pub.count_and_get(
380 p_count => x_msg_count
381 ,p_data => x_msg_data);
382 WHEN OTHERS THEN
383 ROLLBACK TO get_current_period_pvt;
384 x_return_status := fnd_api.g_ret_sts_unexp_error;
385
386 IF fnd_msg_pub.check_msg_level(
387 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
388 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
389 END IF;
390
391 fnd_msg_pub.count_and_get(
392 p_count => x_msg_count
393 ,p_data => x_msg_data);
394 END get_current_period;
395
396
397
398
399
400 PROCEDURE get_currency(
401 p_api_version IN NUMBER
402 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
403 ,p_commit IN VARCHAR2 := fnd_api.g_false
404 ,p_validation_level IN NUMBER
405 := fnd_api.g_valid_level_full
406 ,p_organization_id IN NUMBER
407 ,x_currency OUT NOCOPY VARCHAR2
408 ,x_return_status OUT NOCOPY VARCHAR2
409 ,x_msg_count OUT NOCOPY NUMBER
410 ,x_msg_data OUT NOCOPY VARCHAR2)
411
412 IS
413 l_api_name CONSTANT VARCHAR2(30) := 'get_currency';
414 l_api_version CONSTANT NUMBER := 1.0;
415 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
416 l_organization_id NUMBER;
417 l_currency VARCHAR2(30);
418 l_stmt_num NUMBER;
419 BEGIN
420 -- Standard Start of API savepoint
421 l_stmt_num := 10;
422 SAVEPOINT get_currency_pvt;
423
424 l_stmt_num := 20;
425 -- Standard call to check for call compatibility.
426 IF NOT fnd_api.compatible_api_call(
427 l_api_version
428 ,p_api_version
429 ,l_api_name
430 ,g_pkg_name) THEN
431 RAISE fnd_api.g_exc_unexpected_error;
432 END IF;
433
434 l_stmt_num := 30;
435 -- Initialize message list if p_init_msg_list is set to TRUE.
436 IF fnd_api.to_boolean(p_init_msg_list) THEN
437 fnd_msg_pub.initialize;
438 END IF;
439
440 l_stmt_num := 40;
441 -- Initialize API return status to success
442 x_return_status := fnd_api.g_ret_sts_success;
443
444 l_stmt_num := 50;
445 -- API body
446 l_organization_id := 0;
447 l_currency := ' ';
448
449 l_stmt_num := 60;
450 IF (p_organization_id IS NULL) THEN
451
452 fnd_message.set_name('EAM', 'EAM_INPUT_PARAMS_NULL');
453 fnd_message.set_token('EAM_DEBUG',l_full_name||'('||l_stmt_num||')');
454 fnd_msg_pub.add;
455 RAISE fnd_api.g_exc_error;
456 END IF;
457
458 l_organization_id := p_organization_id;
459
460 l_stmt_num := 70;
461
462 select currency_code curr_code into l_currency from hr_organization_information, gl_sets_of_books where set_of_books_id = ORG_INFORMATION1 and organization_id = l_organization_id and ORG_INFORMATION_CONTEXT = 'Accounting Information';
463
464 x_currency := l_currency;
465
466 l_stmt_num := 998;
467 -- End of API body.
468 -- Standard check of p_commit.
469 IF fnd_api.to_boolean(p_commit) THEN
470 COMMIT WORK;
471 END IF;
472
473 l_stmt_num := 999;
474 -- Standard call to get message count and if count is 1, get message info.
475 fnd_msg_pub.count_and_get(
476 p_count => x_msg_count
477 ,p_data => x_msg_data);
478 EXCEPTION
479 WHEN fnd_api.g_exc_error THEN
480 ROLLBACK TO get_currency_pvt;
481 x_return_status := fnd_api.g_ret_sts_error;
482 fnd_msg_pub.count_and_get(
483 p_count => x_msg_count
484 ,p_data => x_msg_data);
485 WHEN fnd_api.g_exc_unexpected_error THEN
486 ROLLBACK TO get_currency_pvt;
487 x_return_status := fnd_api.g_ret_sts_unexp_error;
488 fnd_msg_pub.count_and_get(
489 p_count => x_msg_count
490 ,p_data => x_msg_data);
491 WHEN OTHERS THEN
492 ROLLBACK TO get_currency_pvt;
493 x_return_status := fnd_api.g_ret_sts_unexp_error;
494
495 IF fnd_msg_pub.check_msg_level(
496 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
497 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
498 END IF;
499
500 fnd_msg_pub.count_and_get(
501 p_count => x_msg_count
502 ,p_data => x_msg_data);
503 END get_currency;
504
505
506
507
508 PROCEDURE get_next_asset_number (
509 p_api_version IN NUMBER
510 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
511 ,p_commit IN VARCHAR2 := fnd_api.g_false
512 ,p_validation_level IN NUMBER
513 := fnd_api.g_valid_level_full
514 ,p_organization_id IN NUMBER
515 ,p_inventory_item_id IN NUMBER
516 ,x_asset_number OUT NOCOPY VARCHAR2
517 ,x_return_status OUT NOCOPY VARCHAR2
518 ,x_msg_count OUT NOCOPY NUMBER
519 ,x_msg_data OUT NOCOPY VARCHAR2)
520
521 IS
522 l_api_name CONSTANT VARCHAR2(30) := 'get_next_asset_number';
523 l_api_version CONSTANT NUMBER := 1.0;
524 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
525 l_organization_id NUMBER;
526 l_disable_allowed VARCHAR2(1);
527 l_stmt_num NUMBER;
528 l_asset_number VARCHAR2(30);
529 l_asset_prefix VARCHAR2(30);
530 l_serial_number_type NUMBER;
531 l_serial_generation NUMBER;
532 l_concat_asset_number VARCHAR2(30);
533 l_count NUMBER;
534 l_success VARCHAR2(1);
535 l_base_item_id NUMBER;
536
537 BEGIN
538 -- Standard Start of API savepoint
539 l_stmt_num := 10;
540 SAVEPOINT get_next_asset_number_pvt;
541
542 l_stmt_num := 20;
543 -- Standard call to check for call compatibility.
544 IF NOT fnd_api.compatible_api_call(
545 l_api_version
546 ,p_api_version
547 ,l_api_name
548 ,g_pkg_name) THEN
549 RAISE fnd_api.g_exc_unexpected_error;
550 END IF;
551
552 l_stmt_num := 30;
553 -- Initialize message list if p_init_msg_list is set to TRUE.
554 IF fnd_api.to_boolean(p_init_msg_list) THEN
555 fnd_msg_pub.initialize;
556 END IF;
557
558 l_stmt_num := 40;
559 -- Initialize API return status to success
560 x_return_status := fnd_api.g_ret_sts_success;
561
562 l_stmt_num := 50;
563 -- API body
564 l_organization_id := 0;
565
566 l_disable_allowed := FND_API.G_TRUE;
567
568 l_stmt_num := 60;
569 SELECT SERIAL_NUMBER_GENERATION
570 INTO l_serial_generation
571 FROM MTL_PARAMETERS
572 WHERE ORGANIZATION_ID = p_organization_id;
573
574
575 IF (l_serial_generation = 1) THEN
576 /*----------------------------------------------------------------+
577 | Serial number generation is set to the org level.
578 | Get the serial prefix and the start number from MTL_PARAMETERS.
579 +--------------------------------------------------------------*/
580
581 l_stmt_num := 70;
582 SELECT AUTO_SERIAL_ALPHA_PREFIX,
583 START_AUTO_SERIAL_NUMBER
584 INTO l_asset_prefix,
585 l_asset_number
586 FROM MTL_PARAMETERS
587 WHERE ORGANIZATION_ID = p_organization_id
588 FOR UPDATE OF START_AUTO_SERIAL_NUMBER;
589
590
591 ELSIF (l_serial_generation = 2) THEN
592 /*----------------------------------------------------------------+
593 | Serial number generation is set to the item level.
594 | Get the serial prefix and the start number from MTL_SYSTEM_ITEMS.
595 +--------------------------------------------------------------*/
596
597 l_stmt_num := 80;
598 SELECT AUTO_SERIAL_ALPHA_PREFIX,
599 START_AUTO_SERIAL_NUMBER
600 INTO l_asset_prefix,
601 l_asset_number
602 FROM MTL_SYSTEM_ITEMS
603 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
604 AND ORGANIZATION_ID = p_organization_id;
605 -- FOR UPDATE OF START_AUTO_SERIAL_NUMBER;
606
607 END IF;
608
609 l_success := FND_API.G_FALSE;
610
611 /* Here we use the condition "l_success = FND_API.G_FALSE"
612 * as the loop invariant. The loop will continue unless one
613 * of the validation test fails, when l_success will be assigned
614 * FND_API.G_TRUE
615 */
616 WHILE (l_success = FND_API.G_FALSE) LOOP
617
618 l_success := FND_API.G_TRUE;
619
620 IF (l_asset_number IS NOT NULL) THEN
621 l_concat_asset_number := l_asset_prefix ||l_asset_number;
622 ELSE
623 l_concat_asset_number := NULL;
624 --commenting due to bug 3718290
625 --RAISE fnd_api.g_exc_error;
626 END IF;
627
628 if (l_concat_asset_number is not null) then
629 l_stmt_num := 90;
630 SELECT SERIAL_NUMBER_TYPE
631 INTO l_serial_number_type
632 FROM MTL_PARAMETERS
633 WHERE ORGANIZATION_ID = p_organization_id;
634
635 /* for item level uniqueness */
636 -- bug 3718290: Changing serial_number_type from 1 to 4 below.
637 -- previously, 1 used to be 'within inventory item'
638 -- but post 11.5.10, 4 = ''within inventory item'
639 IF (l_serial_number_type = 4 ) THEN
640 l_stmt_num := 100;
641
642 SELECT count(*)
643 INTO l_count
644 FROM MTL_SERIAL_NUMBERS
645 WHERE serial_number = l_concat_asset_number
646 and inventory_item_id=p_inventory_item_id;
647
648 /* Fix for bug 3408752. Case 2
649 added inventory_id join. */
650
651 IF (l_count > 0) THEN
652 l_success := FND_API.G_FALSE;
653 /* Start Fix for bug 3408752. Case 1
654 Check for item start_serial_number in all orgs for that item */
655 ELSE
656 select count(*) into l_count
657 from
658 MTL_SYSTEM_ITEMS msi, MTL_PARAMETERS mp
659 where
660 msi.organization_id=mp.organization_id and
661 mp.serial_number_generation = 2 and
662 msi.inventory_item_id=p_inventory_item_id and
663 msi.auto_serial_alpha_prefix=l_asset_prefix and
664 msi.start_auto_serial_number-1=l_asset_number ;
665 IF (l_count > 0) THEN
666 l_success := FND_API.G_FALSE;
667 END IF;
668 /* End Fix for bug 3408752. Case 1
669 Check for item start_serial_number in all orgs for that item */
670 END IF;
671
672 /* for org level uniqueness */
673 ELSIF (l_serial_number_type = 2) THEN
674 l_stmt_num := 110;
675 SELECT count(*)
676 INTO l_count
677 FROM MTL_SERIAL_NUMBERS
678 WHERE SERIAL_NUMBER = l_concat_asset_number
679 AND CURRENT_ORGANIZATION_ID = p_organization_id;
680
681 IF (l_count > 0) THEN
682 l_success := FND_API.G_FALSE;
683 END IF;
684
685 l_stmt_num := 120;
686 SELECT count(*)
687 INTO l_count
688 FROM MTL_SERIAL_NUMBERS S,
689 MTL_PARAMETERS P
690 WHERE S.CURRENT_ORGANIZATION_ID = P.ORGANIZATION_ID
691 AND S.SERIAL_NUMBER = l_concat_asset_number
692 AND P.SERIAL_NUMBER_TYPE = 3;
693
694
695 IF (l_count > 0 AND l_success = FND_API.G_TRUE) THEN
696 l_success := FND_API.G_FALSE;
697
698 /* Start Fix for bug 3408752.
699 Check for item start_serial_number in the same org for all items*/
700 ELSE
701 /* Fix Case 3*/
702 if (l_serial_generation = 2) THEN
703
704 null;
705
706 /* Fix Case 4*/
707 elsif (l_serial_generation = 1) then
708 select count(*) into l_count
709 from
710 MTL_PARAMETERS mp
711 where
712 mp.organization_id=p_organization_id and
713 mp.auto_serial_alpha_prefix=l_asset_prefix and
714 mp.start_auto_serial_number-1=l_asset_number;
715 end if;
716 IF (l_count > 0) THEN
717 l_success := FND_API.G_FALSE;
718 END IF;
719 /* End Fix for bug 3408752.
720 Check for item start_serial_number in the same org for all items*/
721 END IF;
722
723 /* for uniqueness across all orgs*/
724 ELSIF (l_serial_number_type = 3 ) THEN
725 l_stmt_num := 130;
726 SELECT count(*)
727 INTO l_count
728 FROM MTL_SERIAL_NUMBERS
729 WHERE SERIAL_NUMBER = l_concat_asset_number;
730
731 IF (l_count > 0 ) THEN
732 l_success := FND_API.G_FALSE;
733 /* Start Fix for bug 3408752.
734 Check for item start_serial_number in all orgs for all items*/
735 ELSE
736 /* Fix Case 5*/
737 /* check in orgs with item level serial number generation */
738 select count(*) into l_count
739 from
740 MTL_SYSTEM_ITEMS msi, MTL_PARAMETERS mp
741 where
742 msi.organization_id=mp.organization_id and
743 mp.serial_number_generation = 2 and
744 msi.inventory_item_id=p_inventory_item_id and
745 msi.auto_serial_alpha_prefix=l_asset_prefix and
746 msi.start_auto_serial_number-1=l_asset_number;
747 IF (l_count > 0) THEN
748 l_success := FND_API.G_FALSE;
749 ELSE
750 /* Fix Case 6*/
751 /* check in orgs with org level serial number generation */
752 select count(*) into l_count
753 from
754 MTL_PARAMETERS mp
755 where
756 mp.serial_number_generation = 1 and
757 mp.auto_serial_alpha_prefix=l_asset_prefix and
758 mp.start_auto_serial_number-1=l_asset_number;
759 IF (l_count > 0) THEN
760 l_success := FND_API.G_FALSE;
761 END IF;
762 END IF;
763 /* End Fix for bug 3408752.
764 Check for item start_serial_number in all orgs for all items*/
765 END IF;
766
767 -- bug 3718290: serial_number_type = 1 means uniqueness 'within inventory model and items'
768 ELSIF (l_serial_number_type = 1 ) THEN
769 select base_item_id
770 into l_base_item_id
771 from mtl_system_items
772 where inventory_item_id = p_inventory_item_id
773 and organization_id = p_organization_id;
774
775 if (l_base_item_id is not null) then
776 if (l_concat_asset_number is not null) then
777 select count(*) into l_count
778 from mtl_serial_numbers msn1, mtl_system_items msi1
779 where msn1.serial_number = l_concat_asset_number
780 and msn1.inventory_item_id = msi1.inventory_item_id
781 and msn1.current_organization_id = msi1.organization_id
782 and msi1.base_item_id = l_base_item_id;
783
784 if l_count > 0 then
785 l_success := FND_API.G_FALSE;
786 end if;
787 end if;
788 --Bug 5188972
789 else
790 SELECT count(*)
791 INTO l_count
792 FROM MTL_SERIAL_NUMBERS
793 WHERE serial_number = l_concat_asset_number
794 AND inventory_item_id=p_inventory_item_id;
795 IF (l_count > 0) THEN
796 l_success := FND_API.G_FALSE;
797 ELSE
798 SELECT count(*) INTO l_count
799 FROM
800 MTL_SYSTEM_ITEMS msi, MTL_PARAMETERS mp
801 WHERE
802 msi.organization_id=mp.organization_id AND
803 mp.serial_number_generation = 2 AND
804 msi.inventory_item_id=p_inventory_item_id AND
805 msi.auto_serial_alpha_prefix=l_asset_prefix AND
806 msi.start_auto_serial_number-1=l_asset_number ;
807 IF (l_count > 0) THEN
808 l_success := FND_API.G_FALSE;
809 END IF;
810 END IF;
811 end if;
812
813
814
815
816 ELSE
817 l_concat_asset_number := NULL;
818 RAISE fnd_api.g_exc_error;
819
820 END IF;
821
822 -- chrng: 2002-07-25: To fix bug 2479889.
823
824 --hkarmach: 2003-02-05 Fix for bug 2786784
825 IF (length(l_asset_number) < length(l_asset_number + 1)) then
826 l_asset_number := l_asset_number + 1;
827 ELSE
828 l_asset_number := LPAD(TO_NUMBER(l_asset_number) + 1 ,length(l_asset_number), '0');
829 END IF;
830 end if;
831 END LOOP;
832
833
834 IF (l_serial_generation = 1) THEN
835 /*----------------------------------------------------------------+
836 | Serial number generation is set to the org level.
837 | Get the serial prefix and the start number from MTL_PARAMETERS.
838 +--------------------------------------------------------------*/
839 l_stmt_num := 140;
840 UPDATE MTL_PARAMETERS
841 SET AUTO_SERIAL_ALPHA_PREFIX = l_asset_prefix,
842 START_AUTO_SERIAL_NUMBER = l_asset_number
843 WHERE ORGANIZATION_ID = p_organization_id;
844 -- fix for bug 2860820. This ensures that the asset number
845 -- definition form is not locked for all users when some one is
846 -- trying to define an asset or a rebuild.
847 commit;
848
849 ELSIF (l_serial_generation = 2) THEN
850 /*----------------------------------------------------------------+
851 | Serial number generation is set to the item level.
852 | Get the serial prefix and the start number from MTL_SYSTEM_ITEMS.
853 +--------------------------------------------------------------*/
854 l_stmt_num := 150;
855 UPDATE MTL_SYSTEM_ITEMS
856 SET AUTO_SERIAL_ALPHA_PREFIX = l_asset_prefix,
857 START_AUTO_SERIAL_NUMBER = l_asset_number
858 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
859 AND ORGANIZATION_ID = p_organization_id;
860 -- fix for bug 2860820. This ensures that the asset number
861 -- definition form is not locked for all users when some one is
862 -- trying to define an asset or a rebuild.
863 commit;
864
865 END IF;
866
867 l_stmt_num := 160;
868 x_asset_number := l_concat_asset_number;
869
870
871
872 l_stmt_num := 998;
873 -- End of API body.
874 -- Standard check of p_commit.
875 IF fnd_api.to_boolean(p_commit) THEN
876 COMMIT WORK;
877 END IF;
878
879 l_stmt_num := 999;
880 -- Standard call to get message count and if count is 1, get message info.
881 fnd_msg_pub.count_and_get(
882 p_encoded => fnd_api.g_false
883 ,p_count => x_msg_count
884 ,p_data => x_msg_data);
885
886 EXCEPTION
887 WHEN fnd_api.g_exc_error THEN
888 ROLLBACK TO get_next_asset_number_pvt;
889 x_return_status := fnd_api.g_ret_sts_error;
890 x_asset_number := NULL;
891 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
892 fnd_msg_pub.count_and_get(
893 p_encoded => fnd_api.g_false
894 ,p_count => x_msg_count
895 ,p_data => x_msg_data);
896 WHEN fnd_api.g_exc_unexpected_error THEN
897 ROLLBACK TO get_next_asset_number_pvt;
898 x_return_status := fnd_api.g_ret_sts_unexp_error;
899 x_asset_number := NULL;
900 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
901 fnd_msg_pub.count_and_get(
902 p_encoded => fnd_api.g_false
903 ,p_count => x_msg_count
904 ,p_data => x_msg_data);
905 WHEN OTHERS THEN
906 ROLLBACK TO get_next_asset_number_pvt;
907 x_return_status := fnd_api.g_ret_sts_unexp_error;
908 x_asset_number := NULL;
909 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
910 IF fnd_msg_pub.check_msg_level(
911 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
912 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
913 END IF;
914
915 fnd_msg_pub.count_and_get(
916 p_encoded => fnd_api.g_false
917 ,p_count => x_msg_count
918 ,p_data => x_msg_data);
919
920
921 END get_next_asset_number;
922
923
924 /* Bug # 4759672 : When called from public API, null value will be
925 passed to p_resp_id. The default value for p_resp_id is
926 FND_GLOBAL.RESP_ID and this would give a value of -1 if called from
927 outside Oracle Application.
928 */
929
930 PROCEDURE verify_org(
931 p_resp_id number,
932 p_resp_app_id number,
933 p_org_id number,
934 p_init_msg_list in VARCHAR2 := FND_API.G_FALSE,
935 x_boolean out NOCOPY number,
936 x_return_status out NOCOPY VARCHAR2,
937 x_msg_count out NOCOPY NUMBER,
938 x_msg_data out NOCOPY VARCHAR2)
939 is
940
941 l_err_num NUMBER;
942 l_err_code VARCHAR2(240);
943 l_err_msg VARCHAR2(240);
944 l_stmt_num NUMBER;
945 l_return_status VARCHAR2(1);
946 l_msg_count NUMBER;
947 l_msg_data VARCHAR2(30);
948
949 l_primary_cost_method NUMBER;
950 l_std_cg_acct NUMBER;
951
952 l_api_name CONSTANT VARCHAR2(30) := 'verify_org';
953 l_api_version CONSTANT NUMBER := 115.0;
954
955
956 CST_FAILED_STD_CG_FLAG EXCEPTION;
957
958 BEGIN
959
960 x_boolean := 0;
961 l_stmt_num := 10;
962
963 IF (p_resp_id IS NOT NULL) THEN
964 select count(*)
965 into x_boolean
966 from org_access_view oav,
967 mtl_parameters mp,
968 wip_eam_parameters wep
969 where oav.organization_id = mp.organization_id
970 and oav.responsibility_id = p_resp_id
971 and oav.resp_application_id = p_resp_app_id
972 and NVL(mp.eam_enabled_flag,'N') = 'Y'
973 and oav.organization_id = p_org_id
974 and wep.organization_id = p_org_id;
975 ELSE
976 /* For bug # 4759672 */
977 select count(*)
978 into x_boolean
979 from wip_eam_parameters wep
980 where wep.organization_id = p_org_id;
981 END IF;
982
983 x_return_status := fnd_api.g_ret_sts_success;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 rollback;
988 x_return_status := fnd_api.g_ret_sts_unexp_error;
989 IF fnd_msg_pub.check_msg_level(
990 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
991 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
992 END IF;
993
994 fnd_msg_pub.count_and_get(
995 p_count => x_msg_count
996 ,p_data => x_msg_data);
997
998 END verify_org;
999
1000 -- Added by sraval to verify if an item name contains wildcard characters such as % and _
1001 FUNCTION invalid_item_name (p_item_name in varchar2)
1002 return boolean is
1003 l_boolean boolean;
1004 BEGIN
1005 if ((instr(p_item_name,'%')>0) Or (instr(p_item_name,'_')>0)) then
1006 l_boolean := true;
1007 else
1008 l_boolean := false;
1009 end if;
1010
1011 return l_boolean;
1012 END invalid_item_name;
1013
1014
1015
1016 FUNCTION get_mfg_meaning(p_lookup_type in VARCHAR2 , p_lookup_code in number)
1017 return VARCHAR2 IS
1018 l_meaning VARCHAR2(80);
1019
1020 begin
1021 select meaning
1022 into l_meaning
1023 from mfg_lookups
1024 where lookup_type = p_lookup_type
1025 and lookup_code=p_lookup_code;
1026
1027 return l_meaning;
1028
1029 end get_mfg_meaning;
1030
1031 FUNCTION get_item_name(p_service_request_id in number,
1032 p_org_id in number,
1033 p_inv_organization_id in number
1034 ) return varchar2 is
1035 l_item_name varchar2(240);
1036 l_organization_id number;
1037 l_inventory_item_id number;
1038 begin
1039 if p_inv_organization_id is not null then
1040 l_organization_id := p_inv_organization_id;
1041 else
1042 l_organization_id := p_org_id;
1043 end if;
1044
1045 begin
1046 select nvl(cia.inventory_item_id,0)
1047 into l_inventory_item_id
1048 from cs_incidents_all_b cia
1049 where cia.incident_id = p_service_request_id;
1050 exception
1051 when no_data_found then
1052 null;
1053 end;
1054
1055 if l_inventory_item_id is not null then
1056 select concatenated_segments
1057 into l_item_name
1058 from mtl_system_items_kfv msi
1059 where organization_id = l_organization_id
1060 and inventory_item_id = l_inventory_item_id;
1061 end if;
1062
1063 return l_item_name;
1064
1065
1066 end get_item_name;
1067
1068 -- Following new functions added by lllin for 11.5.10
1069
1070
1071 -- This function validates an asset group, asset activity, or
1072 -- rebuildable item. p_eam_item_type indicates the type of item being
1073 -- validated. Asset group: 1; Asset activity: 2; Rebuildable item: 3.
1074 FUNCTION validate_inventory_item_id
1075 (
1076 p_organization_id in number,
1077 p_inventory_item_id in number,
1078 p_eam_item_type in number
1079 ) return boolean is
1080 l_count number;
1081 begin
1082 select count(*) into l_count
1083 from mtl_system_items
1084 where inventory_item_id=p_inventory_item_id
1085 and organization_id=p_organization_id
1086 and eam_item_type=p_eam_item_type;
1087
1088 if (l_count>0) then
1089 return true;
1090 else
1091 return false;
1092 end if;
1093 end validate_inventory_item_id;
1094
1095
1096 -- This function validates an asset number or serialized rebuildable.
1097 -- p_eam_item_type indicates the type of serial number being validated.
1098 -- Asset group: 1; Asset activity: 2; Rebuildable item: 3.
1099 FUNCTION validate_serial_number
1100 (
1101 p_organization_id in number,
1102 p_inventory_item_id in number,
1103 p_serial_number in varchar2,
1104 p_eam_item_type in number:=1
1105 ) return boolean is
1106 l_count number;
1107 begin
1108 select count(*) into l_count
1109 from csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
1110 where cii.last_vld_organization_id=mp.organization_id
1111 and msi.organization_id = cii.last_vld_organization_id
1112 and mp.maint_organization_id = p_organization_id
1113 and cii.inventory_item_id=p_inventory_item_id
1114 and cii.serial_number=p_serial_number
1115 and cii.inventory_item_id=msi.inventory_item_id
1116 and msi.eam_item_type=p_eam_item_type;
1117
1118 if (l_count>0) then
1119 return true;
1120 else
1121 return false;
1122 end if;
1123 end;
1124
1125
1126 -- This function validates the boolean flags.
1127 -- A boolean flag has to be either 'Y' or 'N'.
1128 FUNCTION validate_boolean_flag
1129 (
1130 p_flag in varchar2
1131 ) return boolean is
1132 begin
1133 if (p_flag <> 'Y') and (p_flag <> 'N') then
1134 return false;
1135 else
1136 return true;
1137 end if;
1138 end;
1139
1140
1141 -- Following function validates department id in bom_departments table.
1142 FUNCTION validate_department_id
1143 (
1144 p_department_id in number,
1145 p_organization_id in number
1146 ) return boolean is
1147 l_count number;
1148 begin
1149 select count(*) into l_count
1150 from bom_departments
1151 where department_id=p_department_id
1152 and organization_id=p_organization_id;
1153
1154 if (l_count>0) then
1155 return true;
1156 else
1157 return false;
1158 end if;
1159 end;
1160
1161 -- Validates eam location id in mtl_eam_locations table.
1162 FUNCTION validate_eam_location_id
1163 (
1164 p_location_id in number
1165 ) return boolean
1166 is
1167 l_count number;
1168 begin
1169 select count(*) into l_count
1170 from mtl_eam_locations
1171 where location_id=p_location_id;
1172
1173 if (l_count>0) then
1174 return true;
1175 else
1176 return false;
1177 end if;
1178 end;
1179
1180
1181 -- The following function should NOT be called for rebuilds.
1182 -- This function validates the eam location for an asset.
1183 -- The location has to exist, and its organization_id has to
1184 -- the same as the current_organization_id of the serial number.
1185 FUNCTION validate_eam_location_id_asset
1186 (
1187 p_organization_id in number, -- use organization id, not creation org id
1188 p_location_id in number
1189 ) return boolean
1190 is
1191 l_count number;
1192 begin
1193 select count(*) into l_count
1194 from mtl_eam_locations
1195 where organization_id=p_organization_id
1196 and location_id=p_location_id
1197 and (END_DATE >= SYSDATE OR END_DATE IS NULL);
1198
1199 if (l_count>0) then
1200 return true;
1201 else
1202 return false;
1203 end if;
1204 end;
1205
1206 FUNCTION validate_wip_acct_class_code
1207 (
1208 p_organization_id in number,
1209 p_wip_accounting_class_code in varchar2
1210 ) return boolean
1211 is
1212 l_count number;
1213 begin
1214 select count(*) into l_count
1215 from WIP_ACCOUNTING_CLASSES
1216 where class_code = p_wip_accounting_class_code
1217 and organization_id = p_organization_id
1218 and class_type = 6; -- WIP_CLASS_TYPE=Maintenance Accounting Class
1219
1220 if (l_count>0) then
1221 return true;
1222 else
1223 return false;
1224 end if;
1225 end;
1226
1227
1228 FUNCTION validate_meter_id
1229 (
1230 p_meter_id in number,
1231 p_tmpl_flag in varchar2:=null
1232 ) return boolean
1233 is
1234 l_count number;
1235 begin
1236 if (p_tmpl_flag is null) then
1237 select count(*) into l_count
1238 from csi_counters_b
1239 where counter_id=p_meter_id;
1240 elsif (p_tmpl_flag='N') then
1241 select count(*) into l_count
1242 from csi_counters_b
1243 where counter_id=p_meter_id;
1244 elsif (p_tmpl_flag='Y') then
1245 select count(*) into l_count
1246 from csi_counter_template_b
1247 where counter_id=p_meter_id;
1248 else
1249 l_count:=0;
1250 end if;
1251
1252 if (l_count>0) then
1253 return true;
1254 else
1255 return false;
1256 end if;
1257 end;
1258
1259
1260 function validate_desc_flex_field
1261 (
1262 p_app_short_name IN VARCHAR:='EAM',
1263 p_desc_flex_name IN VARCHAR,
1264 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
1265 p_ATTRIBUTE1 IN VARCHAR2 default null,
1266 p_ATTRIBUTE2 IN VARCHAR2 default null,
1267 p_ATTRIBUTE3 IN VARCHAR2 default null,
1268 p_ATTRIBUTE4 IN VARCHAR2 default null,
1269 p_ATTRIBUTE5 IN VARCHAR2 default null,
1270 p_ATTRIBUTE6 IN VARCHAR2 default null,
1271 p_ATTRIBUTE7 IN VARCHAR2 default null,
1272 p_ATTRIBUTE8 IN VARCHAR2 default null,
1273 p_ATTRIBUTE9 IN VARCHAR2 default null,
1274 p_ATTRIBUTE10 IN VARCHAR2 default null,
1275 p_ATTRIBUTE11 IN VARCHAR2 default null,
1276 p_ATTRIBUTE12 IN VARCHAR2 default null,
1277 p_ATTRIBUTE13 IN VARCHAR2 default null,
1278 p_ATTRIBUTE14 IN VARCHAR2 default null,
1279 p_ATTRIBUTE15 IN VARCHAR2 default null,
1280 x_error_segments OUT NOCOPY NUMBER,
1281 x_error_message OUT NOCOPY VARCHAR2
1282 )
1283 return boolean
1284 is
1285 l_validated boolean;
1286 begin
1287 x_error_segments:=null;
1288 x_error_message:=null;
1289
1290 FND_FLEX_DESCVAL.set_context_value(p_attribute_category);
1291 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_ATTRIBUTE1);
1292 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_ATTRIBUTE2);
1293 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_ATTRIBUTE3);
1294 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_ATTRIBUTE4);
1295 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_ATTRIBUTE5);
1296 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_ATTRIBUTE6);
1297 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_ATTRIBUTE7);
1298 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_ATTRIBUTE8);
1299 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_ATTRIBUTE9);
1300 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_ATTRIBUTE10);
1301 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_ATTRIBUTE11);
1302 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_ATTRIBUTE12);
1303 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_ATTRIBUTE13);
1304 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_ATTRIBUTE14);
1305 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_ATTRIBUTE15);
1306
1307 l_validated:= FND_FLEX_DESCVAL.validate_desccols(
1308 p_app_short_name,
1309 p_desc_flex_name,
1310 'I',
1311 sysdate ) ;
1312
1313 if (l_validated) then
1314 return true;
1315 else
1316 x_error_segments:=FND_FLEX_DESCVAL.error_segment;
1317 x_error_message:=fnd_flex_descval.error_message;
1318 return false;
1319 end if;
1320 end validate_desc_flex_field;
1321
1322
1323 FUNCTION validate_mfg_lookup_code
1324 (p_lookup_type in VARCHAR2,
1325 p_lookup_code in NUMBER)
1326 return boolean IS
1327 l_count number;
1328 begin
1329 select count(*) into l_count
1330 from mfg_lookups
1331 where
1332 lookup_type=p_lookup_type and
1333 lookup_code=p_lookup_code;
1334
1335 if (l_count > 0) then
1336 return true;
1337 else
1338 return false;
1339 end if;
1340 end validate_mfg_lookup_code;
1341
1342 -- Validates that the maintained object type and id represent a valid
1343 -- maintained object.
1344
1345 FUNCTION validate_maintained_object_id
1346 (p_maintenance_object_type in NUMBER,
1347 p_maintenance_object_id in NUMBER,
1348 p_organization_id in number default null,
1349 p_eam_item_type in number
1350 )
1351 return boolean
1352 is
1353 l_count number;
1354 begin
1355 if (p_maintenance_object_type=3) then
1356 /* IMPORTANT: This validation only holds true for EAM work orders. CMRO work orders
1357 cannot use this validation. Since this API would be invoked only
1358 from EAM UIs, I'm not specifically testing for the type of work order
1359 this row may represent */
1360
1361 select count(*) into l_count
1362 from csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
1363 where
1364 msi.organization_id=cii.last_vld_organization_id and
1365 msi.inventory_item_id=cii.inventory_item_id and
1366 cii.instance_id = p_maintenance_object_id and
1367 msi.eam_item_type=p_eam_item_type;
1368
1369 if (l_count > 0) then
1370 return true;
1371 else
1372 return false;
1373 end if;
1374
1375 elsif (p_maintenance_object_type=2) then
1376 if (p_organization_id is null) then
1377 return false;
1378 end if;
1379
1380 select count(*) into l_count
1381 from mtl_system_items msi, mtl_parameters mp
1382 where
1383 msi.inventory_item_id=p_maintenance_object_id
1384 and msi.eam_item_type=p_eam_item_type
1385 and msi.organization_id=mp.organization_id
1386 and mp.maint_organization_id = p_organization_id;
1387
1388 if (l_count > 0) then
1389 return true;
1390 else
1391 return false;
1392 end if;
1393 else
1394 return false;
1395 end if;
1396 end validate_maintained_object_id;
1397
1398
1399 -- Validates that the combination (Organization_id, inventory_item_id, and
1400 -- serial number) and the combination (maintained_object_type and
1401 -- maintained_object_id) represent the same valid maintained object.
1402
1403 FUNCTION validate_maintained_object
1404 (p_organization_id in NUMBER,
1405 p_inventory_item_id in NUMBER,
1406 p_serial_number in VARCHAR2 default null,
1407 p_maintenance_object_type in NUMBER,
1408 p_maintenance_object_id in NUMBER,
1409 p_eam_item_type in number)
1410 return boolean
1411 is
1412 l_organization_id number;
1413 l_inventory_item_id number;
1414 l_serial_number varchar2(30);
1415 begin
1416 if (p_maintenance_object_type=1) then
1417 select msn.current_organization_id,
1418 msn.inventory_item_id,
1419 msn.serial_number
1420 into l_organization_id, l_inventory_item_id, l_serial_number
1421 from mtl_serial_numbers msn, mtl_system_items msi
1422 where
1423 msn.gen_object_id=p_maintenance_object_id and
1424 msi.inventory_item_id=msn.inventory_item_id and
1425 msi.organization_id=msn.current_organization_id and
1426 msi.eam_item_type=p_eam_item_type;
1427
1428 if (l_organization_id=p_organization_id and
1429 l_inventory_item_id=p_inventory_item_id and
1430 l_serial_number=p_serial_number) then
1431 return true;
1432 else
1433 return false;
1434 end if;
1435 elsif (p_maintenance_object_type=2) then
1436 select organization_id,
1437 inventory_item_id
1438 into l_organization_id, l_inventory_item_id
1439 from mtl_system_items
1440 where
1441 organization_id=p_organization_id and
1442 inventory_item_id=p_maintenance_object_id
1443 and eam_item_type=p_eam_item_type;
1444
1445 if (l_organization_id=p_organization_id and
1446 l_inventory_item_id=p_inventory_item_id and
1447 p_serial_number is null) then
1448 return true;
1449 else
1450 return false;
1451 end if;
1452 else
1453 return false;
1454 end if;
1455
1456 exception
1457 when no_data_found then
1458 return false;
1459
1460 end validate_maintained_object;
1461
1462
1463 procedure translate_asset_maint_obj
1464 (p_organization_id in number,
1465 p_inventory_item_id in number,
1466 p_serial_number in varchar2 default null,
1467 x_object_found out nocopy boolean,
1468 x_maintenance_object_type out nocopy number,
1469 x_maintenance_object_id out nocopy number)
1470 is
1471 begin
1472 x_object_found:=true;
1473
1474 if (p_serial_number is not null) then
1475 select instance_id
1476 into x_maintenance_object_id
1477 from csi_item_instances
1478 where inventory_item_id=p_inventory_item_id
1479 and serial_number=p_serial_number;
1480
1481 x_maintenance_object_type:=3;
1482 else
1483 select inventory_item_id
1484 into x_maintenance_object_id
1485 from mtl_system_items
1486 where inventory_item_id=p_inventory_item_id
1487 and eam_item_type in (1,3)
1488 and rownum = 1;
1489
1490 x_maintenance_object_type:=2;
1491 end if;
1492
1493 exception
1494 when no_data_found then
1495 x_object_found:=false;
1496 --dbms_output.put_line('no data found');
1497 end translate_asset_maint_obj;
1498
1499
1500 procedure translate_maint_obj_asset
1501 (p_maintenance_object_type in number,
1502 p_maintenance_object_id in number,
1503 p_organization_id in number default null,
1504 x_object_found out nocopy boolean,
1505 x_organization_id out nocopy number,
1506 x_inventory_item_id out nocopy number,
1507 x_serial_number out nocopy varchar2
1508 )
1509 is
1510 begin
1511 x_object_found:=true;
1512
1513 if (p_maintenance_object_type=3) then
1514 SELECT mp.maint_organization_id, cii.inventory_item_id, cii.serial_number
1515 INTO x_organization_id, x_inventory_item_id, x_serial_number
1516 FROM csi_item_instances cii, mtl_parameters mp
1517 WHERE cii.instance_id=p_maintenance_object_id
1518 AND cii.last_vld_organization_id = mp.organization_id;
1519 elsif (p_maintenance_object_type=2) then
1520 select inventory_item_id
1521 into x_inventory_item_id
1522 from mtl_system_items
1523 where inventory_item_id=p_maintenance_object_id
1524 and eam_item_type in (1,3)
1525 and rownum = 1;
1526 x_serial_number:=null;
1527 x_organization_id := p_organization_id;
1528 end if;
1529
1530 exception
1531 when no_data_found then
1532 x_object_found:=false;
1533 end translate_maint_obj_asset;
1534
1535 /* ----------------------------------------------------------------------------------------------
1536 -- Procedure to get the sum of today's work, overdue work and open work in Maintenance
1537 -- Engineer's Workbench
1538 -- Author : amondal, Aug '03
1539 ------------------------------------------------------------------------------------------------*/
1540
1541
1542
1543 PROCEDURE get_work_order_count (
1544 p_api_version IN NUMBER
1545 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1546 ,p_commit IN VARCHAR2 := fnd_api.g_false
1547 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1548 ,p_organization_id IN VARCHAR2
1549 ,p_employee_id IN VARCHAR2
1550 ,p_instance_id IN NUMBER
1551 ,p_asset_group_id IN NUMBER
1552 ,p_department_id IN NUMBER
1553 ,p_resource_id IN NUMBER
1554 ,p_current_date IN VARCHAR2
1555 ,x_todays_work OUT NOCOPY VARCHAR2
1556 ,x_overdue_work OUT NOCOPY VARCHAR2
1557 ,x_open_work OUT NOCOPY VARCHAR2
1558 ,x_todays_work_duration OUT NOCOPY VARCHAR2
1559 ,x_overdue_work_duration OUT NOCOPY VARCHAR2
1560 ,x_open_work_duration OUT NOCOPY VARCHAR2
1561 ,x_current_date OUT NOCOPY VARCHAR2
1562 ,x_current_time OUT NOCOPY VARCHAR2
1563 ,x_return_status OUT NOCOPY VARCHAR2
1564 ,x_msg_count OUT NOCOPY NUMBER
1565 ,x_msg_data OUT NOCOPY VARCHAR2)
1566
1567 IS
1568 l_api_name CONSTANT VARCHAR2(30) := 'get_work_order_count';
1569 l_api_version CONSTANT NUMBER := 1.0;
1570 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1571 l_organization_id NUMBER;
1572 l_stmt_num NUMBER;
1573 l_count NUMBER;
1574 l_success VARCHAR2(1);
1575 l_todays_work NUMBER;
1576 l_overdue_work NUMBER;
1577 l_open_work NUMBER;
1578 l_total_work NUMBER;
1579 l_todays_work_duration NUMBER;
1580 l_overdue_work_duration NUMBER;
1581 l_open_work_duration NUMBER;
1582 l_total_work_duration NUMBER;
1583 l_current_date VARCHAR2(100);
1584 l_current_time VARCHAR2(100);
1585 l_maint_supervisor_mode NUMBER;
1586 BEGIN
1587 -- Standard Start of API savepoint
1588 l_stmt_num := 10;
1589 SAVEPOINT get_work_order_count_pvt;
1590
1591 l_stmt_num := 20;
1592 -- Standard call to check for call compatibility.
1593 IF NOT fnd_api.compatible_api_call(
1594 l_api_version
1595 ,p_api_version
1596 ,l_api_name
1597 ,g_pkg_name) THEN
1598 RAISE fnd_api.g_exc_unexpected_error;
1599 END IF;
1600
1601 l_stmt_num := 30;
1602 -- Initialize message list if p_init_msg_list is set to TRUE.
1603 IF fnd_api.to_boolean(p_init_msg_list) THEN
1604 fnd_msg_pub.initialize;
1605 END IF;
1606
1607 l_stmt_num := 40;
1608 -- Initialize API return status to success
1609 x_return_status := fnd_api.g_ret_sts_success;
1610
1611 l_stmt_num := 50;
1612
1613 -- API body
1614
1615 l_maint_supervisor_mode := FND_PROFILE.VALUE('EAM_MAINTENANCE_SUPERVISOR');
1616 IF l_maint_supervisor_mode = 2 THEN -- Maintenance Engineer
1617 -- Count of Today's work orders
1618
1619 l_current_date := substr(p_current_date,1,19); -- format of date is 'yyyy-mm-dd HH24:mi:ss'
1620 l_current_time := substr(p_current_date,12);
1621
1622
1623 SELECT count(*) ,
1624 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1625 INTO l_todays_work,
1626 l_todays_work_duration
1627 FROM wip_entities we,
1628 wip_discrete_jobs wdj,
1629 wip_operations wo,
1630 wip_operation_resources wor,
1631 wip_op_resource_instances wori,
1632 bom_resource_employees bre,
1633 (SELECT wip_entity_id,
1634 operation_seq_num,
1635 resource_seq_num,
1636 organization_id,
1637 instance_id,
1638 SUM(completion_date - start_date) usage
1639 FROM wip_operation_resource_usage
1640 GROUP BY wip_entity_id,
1641 operation_seq_num,
1642 resource_seq_num,
1643 organization_id,
1644 instance_id) res
1645 WHERE wdj.wip_entity_id = we.wip_entity_id
1646 AND wdj.organization_id = we.organization_id
1647 AND we.organization_id = wo.organization_id
1648 AND we.wip_entity_id = wo.wip_entity_id
1649 AND wo.organization_id = wor.organization_id
1650 AND wo.wip_entity_id = wor.wip_entity_id
1651 AND wo.operation_seq_num = wor.operation_seq_num
1652 AND wor.organization_id = wori.organization_id
1653 AND wor.wip_entity_id = wori.wip_entity_id
1654 AND wor.operation_seq_num = wori.operation_seq_num
1655 AND wor.resource_seq_num = wori.resource_seq_num
1656 AND wori.serial_number IS NULL
1657 AND wori.instance_id = bre.instance_id
1658 AND wor.organization_id = bre.organization_id
1659 AND wor.resource_id = bre.resource_id
1660 AND sysdate >= bre.effective_start_date
1661 AND sysdate <= bre.effective_end_date
1662 AND wori.organization_id = res.organization_id (+)
1663 AND wori.wip_entity_id = res.wip_entity_id (+)
1664 AND wori.operation_seq_num = res.operation_seq_num (+)
1665 AND wori.resource_seq_num = res.resource_seq_num (+)
1666 AND wori.instance_id = res.instance_id (+)
1667 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1668 AND we.entity_type = 6
1669 AND wdj.status_type = 3
1670 AND bre.organization_id = p_organization_id
1671 AND bre.person_id = p_employee_id
1672 AND TO_CHAR(wo.first_unit_start_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' ) - sysdate),'yyyy-mm-dd') = substr(l_current_date,1,10);
1673
1674 x_todays_work := to_char(l_todays_work);
1675 x_todays_work_duration := to_char(l_todays_work_duration);
1676
1677 -- Count of Overdue Work
1678
1679 SELECT count(*) ,
1680 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1681 INTO l_overdue_work,
1682 l_overdue_work_duration
1683 FROM wip_entities we,
1684 wip_discrete_jobs wdj,
1685 wip_operations wo,
1686 wip_operation_resources wor,
1687 wip_op_resource_instances wori,
1688 bom_resource_employees bre,
1689 (SELECT wip_entity_id,
1690 operation_seq_num,
1691 resource_seq_num,
1692 organization_id,
1693 instance_id,
1694 SUM(completion_date - start_date) usage
1695 FROM wip_operation_resource_usage
1696 GROUP BY wip_entity_id,
1697 operation_seq_num,
1698 resource_seq_num,
1699 organization_id,
1700 instance_id) res
1701 WHERE wdj.wip_entity_id = we.wip_entity_id
1702 AND wdj.organization_id = we.organization_id
1703 AND we.organization_id = wo.organization_id
1704 AND we.wip_entity_id = wo.wip_entity_id
1705 AND wo.organization_id = wor.organization_id
1706 AND wo.wip_entity_id = wor.wip_entity_id
1707 AND wo.operation_seq_num = wor.operation_seq_num
1708 AND wor.organization_id = wori.organization_id
1709 AND wor.wip_entity_id = wori.wip_entity_id
1710 AND wor.operation_seq_num = wori.operation_seq_num
1711 AND wor.resource_seq_num = wori.resource_seq_num
1712 AND wori.serial_number IS NULL
1713 AND wori.instance_id = bre.instance_id
1714 AND wor.organization_id = bre.organization_id
1715 AND wor.resource_id = bre.resource_id
1716 AND sysdate >= bre.effective_start_date
1717 AND sysdate <= bre.effective_end_date
1718 AND wori.organization_id = res.organization_id (+)
1719 AND wori.wip_entity_id = res.wip_entity_id (+)
1720 AND wori.operation_seq_num = res.operation_seq_num (+)
1721 AND wori.resource_seq_num = res.resource_seq_num (+)
1722 AND wori.instance_id = res.instance_id (+)
1723 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1724 AND we.entity_type = 6
1725 AND wdj.status_type = 3
1726 AND bre.organization_id = p_organization_id
1727 AND bre.person_id = p_employee_id
1728 AND wo.last_unit_completion_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' ) - sysdate) < to_date(substr(p_current_date,1,10), 'yyyy-mm-dd');
1729
1730 x_overdue_work := l_overdue_work;
1731 x_overdue_work_duration := l_overdue_work_duration;
1732
1733
1734 -- Count of Open Work
1735
1736 SELECT count(*) ,
1737 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1738 INTO l_open_work,
1739 l_open_work_duration
1740 FROM wip_entities we,
1741 wip_discrete_jobs wdj,
1742 wip_operations wo,
1743 wip_operation_resources wor,
1744 wip_op_resource_instances wori,
1745 bom_resource_employees bre,
1746 (SELECT wip_entity_id,
1747 operation_seq_num,
1748 resource_seq_num,
1749 organization_id,
1750 instance_id,
1751 SUM(completion_date - start_date) usage
1752 FROM wip_operation_resource_usage
1753 GROUP BY wip_entity_id,
1754 operation_seq_num,
1755 resource_seq_num,
1756 organization_id,
1757 instance_id) res
1758 WHERE wdj.wip_entity_id = we.wip_entity_id
1759 AND wdj.organization_id = we.organization_id
1760 AND we.organization_id = wo.organization_id
1761 AND we.wip_entity_id = wo.wip_entity_id
1762 AND wo.organization_id = wor.organization_id
1763 AND wo.wip_entity_id = wor.wip_entity_id
1764 AND wo.operation_seq_num = wor.operation_seq_num
1765 AND wor.organization_id = wori.organization_id
1766 AND wor.wip_entity_id = wori.wip_entity_id
1767 AND wor.operation_seq_num = wori.operation_seq_num
1768 AND wor.resource_seq_num = wori.resource_seq_num
1769 AND wori.serial_number IS NULL
1770 AND wori.instance_id = bre.instance_id
1771 AND wor.organization_id = bre.organization_id
1772 AND wor.resource_id = bre.resource_id
1773 AND sysdate >= bre.effective_start_date
1774 AND sysdate <= bre.effective_end_date
1775 AND wori.organization_id = res.organization_id (+)
1776 AND wori.wip_entity_id = res.wip_entity_id (+)
1777 AND wori.operation_seq_num = res.operation_seq_num (+)
1778 AND wori.resource_seq_num = res.resource_seq_num (+)
1779 AND wori.instance_id = res.instance_id (+)
1780 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1781 AND we.entity_type = 6
1782 AND wdj.status_type = 3
1783 AND bre.organization_id = p_organization_id
1784 AND bre.person_id = p_employee_id;
1785
1786 x_open_work := l_open_work;
1787 x_open_work_duration := l_open_work_duration;
1788
1789 ELSE -- Maintenance Supervisor
1790 -- Count of Today's work orders
1791
1792 l_current_date := substr(p_current_date,1,19); -- format of date is 'yyyy-mm-dd HH24:mi:ss'
1793 l_current_time := substr(p_current_date,12);
1794
1795 SELECT count(*) ,
1796 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1797 INTO l_todays_work, l_todays_work_duration
1798 FROM wip_entities we,
1799 wip_discrete_jobs wdj,
1800 wip_operations wo,
1801 wip_operation_resources wor,
1802 bom_resources br,
1803 (SELECT wip_entity_id,
1804 operation_seq_num,
1805 resource_seq_num,
1806 organization_id,
1807 instance_id,
1808 SUM(completion_date - start_date) usage
1809 FROM wip_operation_resource_usage woru
1810 WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
1811 (SELECT 1
1812 FROM wip_op_resource_instances wori
1813 WHERE woru.wip_entity_id = wori.wip_entity_id
1814 AND woru.operation_seq_num = wori.operation_seq_num
1815 AND woru.resource_seq_num = wori.resource_seq_num
1816 )
1817 )
1818 GROUP BY wip_entity_id,
1819 operation_seq_num,
1820 resource_seq_num,
1821 organization_id,
1822 instance_id) res
1823 WHERE wdj.wip_entity_id = we.wip_entity_id
1824 AND wdj.organization_id = we.organization_id
1825 AND we.organization_id = wo.organization_id
1826 AND we.wip_entity_id = wo.wip_entity_id
1827 AND wo.organization_id = wor.organization_id
1828 AND wo.wip_entity_id = wor.wip_entity_id
1829 AND wo.operation_seq_num = wor.operation_seq_num
1830 AND wor.organization_id = res.organization_id (+)
1831 AND wor.wip_entity_id = res.wip_entity_id (+)
1832 AND wor.operation_seq_num = res.operation_seq_num (+)
1833 AND wor.resource_seq_num = res.resource_seq_num (+)
1834 AND wor.resource_id = br.resource_id
1835 AND wor.organization_id = br.organization_id
1836 AND br.resource_type = 2
1837 AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
1838 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1839 AND we.entity_type = 6
1840 AND wdj.status_type = 3
1841 AND we.organization_id = p_organization_id
1842 AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
1843 AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
1844 AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
1845 AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
1846 AND ( (p_department_id IS NOT NULL)
1847 OR EXISTS
1848 (
1849 SELECT 1
1850 FROM bom_resource_employees bre,
1851 bom_dept_res_instances bdri,
1852 bom_departments bd
1853 WHERE bre.person_id = p_employee_id
1854 AND bre.effective_start_date <= sysdate
1855 AND bre.effective_end_date >= sysdate
1856 AND bre.resource_id = bdri.resource_id
1857 AND bre.instance_id = bdri.instance_id
1858 AND bdri.department_id = bd.department_id
1859 AND bre.organization_id = bd.organization_id
1860 AND bre.organization_id = p_organization_id
1861 AND bd.department_id = wo.department_id
1862 )
1863 )
1864 AND TO_CHAR(wo.first_unit_start_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' ) - sysdate),'yyyy-mm-dd') = substr(l_current_date,1,10);
1865
1866
1867 x_todays_work := to_char(l_todays_work);
1868 x_todays_work_duration := to_char(l_todays_work_duration);
1869
1870 -- Count of Overdue Work
1871
1872 SELECT count(*) ,
1873 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1874 INTO l_overdue_work, l_overdue_work_duration
1875 FROM wip_entities we,
1876 wip_discrete_jobs wdj,
1877 wip_operations wo,
1878 wip_operation_resources wor,
1879 bom_resources br,
1880 (SELECT wip_entity_id,
1881 operation_seq_num,
1882 resource_seq_num,
1883 organization_id,
1884 instance_id,
1885 SUM(completion_date - start_date) usage
1886 FROM wip_operation_resource_usage woru
1887 WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
1888 (SELECT 1
1889 FROM wip_op_resource_instances wori
1890 WHERE woru.wip_entity_id = wori.wip_entity_id
1891 AND woru.operation_seq_num = wori.operation_seq_num
1892 AND woru.resource_seq_num = wori.resource_seq_num
1893 )
1894 )
1895 GROUP BY wip_entity_id,
1896 operation_seq_num,
1897 resource_seq_num,
1898 organization_id,
1899 instance_id) res
1900 WHERE wdj.wip_entity_id = we.wip_entity_id
1901 AND wdj.organization_id = we.organization_id
1902 AND we.organization_id = wo.organization_id
1903 AND we.wip_entity_id = wo.wip_entity_id
1904 AND wo.organization_id = wor.organization_id
1905 AND wo.wip_entity_id = wor.wip_entity_id
1906 AND wo.operation_seq_num = wor.operation_seq_num
1907 AND wor.organization_id = res.organization_id (+)
1908 AND wor.wip_entity_id = res.wip_entity_id (+)
1909 AND wor.operation_seq_num = res.operation_seq_num (+)
1910 AND wor.resource_seq_num = res.resource_seq_num (+)
1911 AND wor.resource_id = br.resource_id
1912 AND wor.organization_id = br.organization_id
1913 AND br.resource_type = 2
1914 AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
1915 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1916 AND we.entity_type = 6
1917 AND wdj.status_type = 3
1918 AND we.organization_id = p_organization_id
1919 AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
1920 AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
1921 AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
1922 AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
1923 AND ( (p_department_id IS NOT NULL)
1924 OR EXISTS
1925 (
1926 SELECT 1
1927 FROM bom_resource_employees bre,
1928 bom_dept_res_instances bdri,
1929 bom_departments bd
1930 WHERE bre.person_id = p_employee_id
1931 AND bre.effective_start_date <= sysdate
1932 AND bre.effective_end_date >= sysdate
1933 AND bre.resource_id = bdri.resource_id
1934 AND bre.instance_id = bdri.instance_id
1935 AND bdri.department_id = bd.department_id
1936 AND bre.organization_id = bd.organization_id
1937 AND bre.organization_id = p_organization_id
1938 AND bd.department_id = wo.department_id
1939 )
1940 )
1941 AND wo.last_unit_completion_date + (to_date(l_current_date,'yyyy-mm-dd HH24:mi:ss' )-sysdate) < to_date(substr(p_current_date,1,10), 'yyyy-mm-dd');
1942
1943 x_overdue_work := l_overdue_work;
1944 x_overdue_work_duration := l_overdue_work_duration;
1945
1946 -- Count of Open Work
1947
1948 SELECT count(*) ,
1949 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1950 INTO l_open_work, l_open_work_duration
1951 FROM wip_entities we,
1952 wip_discrete_jobs wdj,
1953 wip_operations wo,
1954 wip_operation_resources wor,
1955 bom_resources br,
1956 (SELECT wip_entity_id,
1957 operation_seq_num,
1958 resource_seq_num,
1959 organization_id,
1960 instance_id,
1961 SUM(completion_date - start_date) usage
1962 FROM wip_operation_resource_usage woru
1963 WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
1964 (SELECT 1
1965 FROM wip_op_resource_instances wori
1966 WHERE woru.wip_entity_id = wori.wip_entity_id
1967 AND woru.operation_seq_num = wori.operation_seq_num
1968 AND woru.resource_seq_num = wori.resource_seq_num
1969 )
1970 )
1971 GROUP BY wip_entity_id,
1972 operation_seq_num,
1973 resource_seq_num,
1974 organization_id,
1975 instance_id) res
1976 WHERE wdj.wip_entity_id = we.wip_entity_id
1977 AND wdj.organization_id = we.organization_id
1978 AND we.organization_id = wo.organization_id
1979 AND we.wip_entity_id = wo.wip_entity_id
1980 AND wo.organization_id = wor.organization_id
1981 AND wo.wip_entity_id = wor.wip_entity_id
1982 AND wo.operation_seq_num = wor.operation_seq_num
1983 AND wor.organization_id = res.organization_id (+)
1984 AND wor.wip_entity_id = res.wip_entity_id (+)
1985 AND wor.operation_seq_num = res.operation_seq_num (+)
1986 AND wor.resource_seq_num = res.resource_seq_num (+)
1987 AND wor.resource_id = br.resource_id
1988 AND wor.organization_id = br.organization_id
1989 AND br.resource_type = 2
1990 AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
1991 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1992 AND we.entity_type = 6
1993 AND wdj.status_type = 3
1994 AND we.organization_id = p_organization_id
1995 AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
1996 AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
1997 AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
1998 AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
1999 AND ( (p_department_id IS NOT NULL)
2000 OR EXISTS
2001 (
2002 SELECT 1
2003 FROM bom_resource_employees bre,
2004 bom_dept_res_instances bdri,
2005 bom_departments bd
2006 WHERE bre.person_id = p_employee_id
2007 AND bre.effective_start_date <= sysdate
2008 AND bre.effective_end_date >= sysdate
2009 AND bre.resource_id = bdri.resource_id
2010 AND bre.instance_id = bdri.instance_id
2011 AND bdri.department_id = bd.department_id
2012 AND bre.organization_id = bd.organization_id
2013 AND bre.organization_id = p_organization_id
2014 AND bd.department_id = wo.department_id
2015 )
2016 ) ;
2017
2018
2019 x_open_work := l_open_work;
2020 x_open_work_duration := l_open_work_duration;
2021
2022 END IF;
2023
2024 -- Bug #3449283 to get the date in format 'yyyy-mm-dd'
2025 l_current_date:= substr(l_current_date,1,10);
2026 x_current_date := to_char(to_date(l_current_date,'yyyy-mm-dd'));
2027
2028 x_current_time := l_current_time;
2029
2030 l_stmt_num := 998;
2031 -- End of API body.
2032 -- Standard check of p_commit.
2033 IF fnd_api.to_boolean(p_commit) THEN
2034 COMMIT WORK;
2035 END IF;
2036
2037 l_stmt_num := 999;
2038 -- Standard call to get message count and if count is 1, get message info.
2039 fnd_msg_pub.count_and_get(
2040 p_encoded => fnd_api.g_false
2041 ,p_count => x_msg_count
2042 ,p_data => x_msg_data);
2043
2044 EXCEPTION
2045 WHEN fnd_api.g_exc_error THEN
2046 ROLLBACK TO get_work_order_count_pvt;
2047 x_return_status := fnd_api.g_ret_sts_error;
2048 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2049 fnd_msg_pub.count_and_get(
2050 p_encoded => fnd_api.g_false
2051 ,p_count => x_msg_count
2052 ,p_data => x_msg_data);
2053 WHEN fnd_api.g_exc_unexpected_error THEN
2054 ROLLBACK TO get_work_order_count_pvt;
2055 x_return_status := fnd_api.g_ret_sts_unexp_error;
2056 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2057 fnd_msg_pub.count_and_get(
2058 p_encoded => fnd_api.g_false
2059 ,p_count => x_msg_count
2060 ,p_data => x_msg_data);
2061 WHEN OTHERS THEN
2062 ROLLBACK TO get_work_order_count_pvt;
2063 x_return_status := fnd_api.g_ret_sts_unexp_error;
2064 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2065 IF fnd_msg_pub.check_msg_level(
2066 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2067 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2068 END IF;
2069
2070 fnd_msg_pub.count_and_get(
2071 p_encoded => fnd_api.g_false
2072 ,p_count => x_msg_count
2073 ,p_data => x_msg_data);
2074
2075
2076 END get_work_order_count;
2077
2078
2079 PROCEDURE insert_into_wori (
2080 p_api_version IN NUMBER
2081 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2082 ,p_commit IN VARCHAR2 := fnd_api.g_false
2083 ,p_organization_id IN VARCHAR2
2084 ,p_employee_id IN VARCHAR2
2085 ,p_wip_entity_id IN VARCHAR2
2086 ,p_operation_seq_num IN VARCHAR2
2087 ,p_resource_seq_num IN VARCHAR2
2088 ,p_resource_id IN VARCHAR2
2089 ,x_return_status OUT NOCOPY VARCHAR2
2090 ,x_msg_count OUT NOCOPY NUMBER
2091 ,x_msg_data OUT NOCOPY VARCHAR2
2092 ,x_wip_entity_name OUT NOCOPY VARCHAR2)
2093
2094 IS
2095
2096 -- Input Tables
2097
2098 l_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
2099 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2100 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2101 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2102 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2103 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2104 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2105 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2106 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2107
2108 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
2109 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
2110 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2111 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2112 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2113 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2114 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2115 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2116 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2117
2118 -- Output Tables
2119
2120 x_out_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
2121 x_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2122 x_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2123 x_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2124 x_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2125 x_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2126 x_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2127 x_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2128 x_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2129
2130 x_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
2131 x_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2132 x_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2133 x_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2134 x_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2135 x_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2136 x_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2137 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2138
2139
2140
2141 -- Local Variables
2142 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wori';
2143 l_api_version CONSTANT NUMBER := 1.0;
2144 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2145 l_return_status VARCHAR2(1);
2146 l_msg_count NUMBER;
2147 l_message_text VARCHAR2(256);
2148 l_stmt_num NUMBER;
2149 l_instance_id NUMBER;
2150 l_wip_entity_name VARCHAR2(80);
2151 l_output_dir VARCHAR2(512);
2152
2153 BEGIN
2154
2155 -- Standard Start of API savepoint
2156 l_stmt_num := 10;
2157 SAVEPOINT insert_into_wori_pvt;
2158
2159 l_stmt_num := 20;
2160 -- Standard call to check for call compatibility.
2161 IF NOT fnd_api.compatible_api_call(
2162 l_api_version
2163 ,p_api_version
2164 ,l_api_name
2165 ,g_pkg_name) THEN
2166 RAISE fnd_api.g_exc_unexpected_error;
2167 END IF;
2168
2169 l_stmt_num := 30;
2170 -- Initialize message list if p_init_msg_list is set to TRUE.
2171 IF fnd_api.to_boolean(p_init_msg_list) THEN
2172 fnd_msg_pub.initialize;
2173 END IF;
2174
2175 l_stmt_num := 40;
2176 -- Initialize API return status to success
2177 x_return_status := fnd_api.g_ret_sts_success;
2178
2179 l_stmt_num := 50;
2180
2181 -- API body
2182
2183
2184
2185 select instance_id
2186 into l_instance_id
2187 from bom_resource_employees
2188 where resource_id = p_resource_id
2189 and organization_id = p_organization_id
2190 and person_id = p_employee_id;
2191
2192
2193
2194 l_eam_res_inst_rec.WIP_ENTITY_ID := to_number(p_wip_entity_id);
2195 l_eam_res_inst_rec.ORGANIZATION_ID := to_number(p_organization_id);
2196 l_eam_res_inst_rec.OPERATION_SEQ_NUM := to_number(p_operation_seq_num);
2197 l_eam_res_inst_rec.RESOURCE_SEQ_NUM := to_number(p_resource_seq_num);
2198 l_eam_res_inst_rec.INSTANCE_ID := to_number(l_instance_id);
2199
2200 l_eam_res_inst_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
2201
2202 l_eam_res_inst_tbl(1) := l_eam_res_inst_rec;
2203
2204 -- Obtain the work order name and return it back
2205
2206 select wip_entity_name
2207 into l_wip_entity_name
2208 from wip_entities
2209 where wip_entity_id = l_eam_res_inst_rec.WIP_ENTITY_ID
2210 and organization_id = l_eam_res_inst_rec.ORGANIZATION_ID;
2211
2212 x_wip_entity_name := l_wip_entity_name;
2213
2214 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
2215
2216
2217 EAM_PROCESS_WO_PUB.Process_WO
2218 ( p_bo_identifier => 'EAM'
2219 , p_init_msg_list => TRUE
2220 , p_api_version_number => 1.0
2221 , p_eam_wo_rec => l_eam_wo_rec
2222 , p_eam_op_tbl => l_eam_op_tbl
2223 , p_eam_op_network_tbl => l_eam_op_network_tbl
2224 , p_eam_res_tbl => l_eam_res_tbl
2225 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2226 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2227 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2228 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2229 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
2230 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
2231 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2232 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2233 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2234 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2235 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2236 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2237 , p_eam_request_tbl => l_eam_request_tbl
2238 , x_eam_wo_rec => x_out_eam_wo_rec
2239 , x_eam_op_tbl => x_out_eam_op_tbl
2240 , x_eam_op_network_tbl => x_out_eam_op_network_tbl
2241 , x_eam_res_tbl => x_out_eam_res_tbl
2242 , x_eam_res_inst_tbl => x_out_eam_res_inst_tbl
2243 , x_eam_sub_res_tbl => x_out_eam_sub_res_tbl
2244 , x_eam_res_usage_tbl => x_out_eam_res_usage_tbl
2245 , x_eam_mat_req_tbl => x_out_eam_mat_req_tbl
2246 , x_eam_direct_items_tbl => x_out_eam_direct_items_tbl
2247 , x_eam_wo_comp_rec => x_out_eam_wo_comp_rec
2248 , x_eam_wo_quality_tbl => x_out_eam_wo_quality_tbl
2249 , x_eam_meter_reading_tbl => x_out_eam_meter_reading_tbl
2250 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2251 , x_eam_wo_comp_rebuild_tbl => x_out_eam_wo_comp_rebuild_tbl
2252 , x_eam_wo_comp_mr_read_tbl => x_out_eam_wo_comp_mr_read_tbl
2253 , x_eam_op_comp_tbl => x_out_eam_op_comp_tbl
2254 , x_eam_request_tbl => x_out_eam_request_tbl
2255 , x_return_status => l_return_status
2256 , x_msg_count => l_msg_count
2257 , p_debug =>NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2258 , p_debug_filename => 'insertwori.log'
2259 , p_output_dir => l_output_dir
2260 );
2261
2262 x_return_status := l_return_status ;
2263 x_msg_count := l_msg_count;
2264 x_msg_data := 'SUCCESS';
2265
2266
2267
2268
2269 -- End of API body.
2270 -- Standard check of p_commit.
2271 IF fnd_api.to_boolean(p_commit) THEN
2272 COMMIT WORK;
2273 END IF;
2274
2275 l_stmt_num := 999;
2276 -- Standard call to get message count and if count is 1, get message info.
2277 fnd_msg_pub.count_and_get(
2278 p_encoded => fnd_api.g_false
2279 ,p_count => x_msg_count
2280 ,p_data => x_msg_data);
2281
2282 EXCEPTION
2283 WHEN fnd_api.g_exc_error THEN
2284 ROLLBACK TO insert_into_wori_pvt;
2285 x_return_status := fnd_api.g_ret_sts_error;
2286 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2287 fnd_msg_pub.count_and_get(
2288 p_encoded => fnd_api.g_false
2289 ,p_count => x_msg_count
2290 ,p_data => x_msg_data);
2291 WHEN fnd_api.g_exc_unexpected_error THEN
2292 ROLLBACK TO insert_into_wori_pvt;
2293 x_return_status := fnd_api.g_ret_sts_unexp_error;
2294 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2295 fnd_msg_pub.count_and_get(
2296 p_encoded => fnd_api.g_false
2297 ,p_count => x_msg_count
2298 ,p_data => x_msg_data);
2299 WHEN OTHERS THEN
2300 ROLLBACK TO insert_into_wori_pvt;
2301 x_return_status := fnd_api.g_ret_sts_unexp_error;
2302 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2303 IF fnd_msg_pub.check_msg_level(
2304 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2305 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2306 END IF;
2307
2308 fnd_msg_pub.count_and_get(
2309 p_encoded => fnd_api.g_false
2310 ,p_count => x_msg_count
2311 ,p_data => x_msg_data);
2312
2313
2314 END insert_into_wori;
2315
2316
2317 FUNCTION get_person_id RETURN VARCHAR2 IS
2318
2319 l_user_id NUMBER := FND_GLOBAL.USER_ID;
2320 l_person_id VARCHAR2(30) := '';
2321
2322 BEGIN
2323 l_user_id := FND_GLOBAL.USER_ID;
2324 begin
2325 select to_char(employee_id)
2326 into l_person_id
2327 from fnd_user
2328 where user_id = l_user_id;
2329
2330 exception
2331 when others then
2332 null;
2333
2334 end;
2335
2336 return l_person_id;
2337 END;
2338
2339 function get_dept_id(p_org_code in varchar2, p_org_id in number, p_dept_code in varchar2, p_dept_id in number)
2340 return number is
2341 l_dept_id number;
2342 l_organization_id number;
2343 l_inventory_item_id number;
2344 begin
2345
2346 if p_dept_id is not null then
2347 return p_dept_id;
2348 elsif p_dept_id is null and p_dept_code is null then
2349 return null;
2350 elsif p_dept_code is not null and p_org_id is not null then
2351 select department_id into l_dept_id
2352 from bom_departments
2353 where department_code = p_dept_code
2354 and organization_id = p_org_id;
2355
2356 return l_dept_id;
2357 else
2358 select bd.department_id into l_dept_id
2359 from bom_departments bd, mtl_parameters mp
2360 where bd.department_code = p_dept_code
2361 and mp.organization_code = p_org_code
2362 and bd.organization_id = mp.organization_id;
2363
2364 return l_dept_id;
2365 end if;
2366
2367
2368 end get_dept_id;
2369
2370 --This procedure validates and deactivates the asset
2371
2372 PROCEDURE deactivate_assets(
2373 P_API_VERSION IN NUMBER,
2374 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2375 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
2376 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2377 P_INVENTORY_ITEM_ID IN NUMBER,
2378 P_SERIAL_NUMBER IN VARCHAR2,
2379 P_ORGANIZATION_ID IN NUMBER,
2380 P_GEN_OBJECT_ID IN NUMBER,
2381 P_INSTANCE_ID IN NUMBER,
2382 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2383 X_MSG_COUNT OUT NOCOPY NUMBER,
2384 X_MSG_DATA OUT NOCOPY VARCHAR2)
2385 IS
2386
2387 l_api_name CONSTANT VARCHAR2(30) := 'deactivate_assets';
2388 l_api_version CONSTANT NUMBER := 1.0;
2389 l_stmt_num number := 0;
2390 l_hr_exists varchar2(1);
2391 l_routes_exists varchar2(1);
2392 l_wo_exists varchar2(1);
2393 l_sr_exists varchar2(1);
2394 l_INVENTORY_ITEM_ID NUMBER;
2395 l_SERIAL_NUMBER VARCHAR2(30);
2396 l_ORGANIZATION_ID NUMBER;
2397 l_instance_id NUMBER;
2398 l_gen_object_id NUMBER;
2399 BEGIN
2400
2401 -- Standard Start of API savepoint
2402 l_stmt_num := 10;
2403 SAVEPOINT asset_util_pvt;
2404
2405 l_stmt_num := 20;
2406 -- Standard call to check for call compatibility.
2407 IF NOT fnd_api.compatible_api_call(
2408 l_api_version
2409 ,p_api_version
2410 ,l_api_name
2411 ,g_pkg_name) THEN
2412 RAISE fnd_api.g_exc_unexpected_error;
2413 END IF;
2414
2415 l_stmt_num := 30;
2416 -- Initialize message list if p_init_msg_list is set to TRUE.
2417 IF fnd_api.to_boolean(p_init_msg_list) THEN
2418 fnd_msg_pub.initialize;
2419 END IF;
2420
2421 l_stmt_num := 40;
2422 -- Initialize API return status to success
2423 x_return_status := fnd_api.g_ret_sts_success;
2424
2425 if P_INSTANCE_ID is null then
2426 l_organization_id := p_organization_id;
2427 select instance_id into l_instance_id
2428 from csi_item_instances
2429 where serial_number = p_serial_number
2430 and inventory_item_id = p_inventory_item_id
2431 ;
2432 select gen_object_id into l_gen_object_id
2433 from mtl_serial_numbers
2434 where serial_number = p_serial_number
2435 and inventory_item_id = p_inventory_item_id
2436 ;
2437 else
2438 l_instance_id := p_instance_id;
2439 l_gen_object_id := p_gen_object_id;
2440
2441 select serial_number, inventory_item_id, current_organization_id
2442 into l_serial_number, l_inventory_item_id, l_organization_id
2443 from mtl_serial_numbers
2444 where gen_object_id = p_gen_object_id;
2445
2446 end if;
2447
2448
2449 --HIERARCHY CHECK
2450
2451 begin
2452 SELECT 'Y'
2453 INTO l_hr_exists
2454 FROM DUAL
2455 WHERE EXISTS
2456 (SELECT mog.object_id
2457 FROM mtl_object_genealogy mog
2458 WHERE mog.object_id = l_gen_object_id
2459
2460 -- Fix for bug 2219479. We do not allow assets that are
2461 -- a child or a parent in the future to be deactivated.
2462 -- hence the check for start_date_active is removed
2463
2464 AND sysdate <= nvl(mog.end_date_active(+), sysdate))
2465 OR EXISTS
2466 (SELECT mog.object_id
2467 FROM mtl_object_genealogy mog
2468 WHERE mog.parent_object_id = l_gen_object_id
2469 AND sysdate <= nvl(mog.end_date_active(+), sysdate));
2470
2471 exception
2472 when no_data_found then
2473 l_hr_exists := 'N';
2474 end;
2475
2476 if (l_hr_exists = 'Y') then
2477 fnd_message.set_name('EAM','EAM_HIERARCHY_EXISTS');
2478 fnd_msg_pub.add;
2479 RAISE fnd_api.g_exc_error;
2480 end if;
2481
2482 -- ROUTES CHECK
2483
2484 begin
2485 SELECT 'Y'
2486 INTO l_routes_exists
2487 FROM DUAL
2488 WHERE EXISTS
2489 (SELECT mena.network_association_id
2490 FROM mtl_eam_network_assets mena
2491 WHERE mena.maintenance_object_type = 3
2492 AND mena.maintenance_object_id = l_instance_id
2493 AND sysdate >= nvl(mena.start_date_active(+), sysdate)
2494 AND sysdate <= nvl(mena.end_date_active(+), sysdate));
2495 exception
2496 when no_data_found then
2497 l_routes_exists := 'N';
2498 end;
2499
2500
2501 if (nvl(l_routes_exists,'N') = 'Y') then
2502 fnd_message.set_name('EAM','EAM_ROUTE_EXISTS');
2503 fnd_msg_pub.add;
2504 RAISE fnd_api.g_exc_error;
2505 end if;
2506
2507 -- WORK REQUEST AND WORK ORDER CHECK
2508 begin
2509 SELECT 'Y'
2510 INTO l_wo_exists
2511 FROM DUAL
2512 WHERE EXISTS
2513 (SELECT wdj.wip_entity_id
2514 FROM wip_discrete_jobs wdj
2515 WHERE wdj.status_type not in (4, 5, 7, 12)
2516 AND wdj.maintenance_object_type = 3
2517 AND wdj.maintenance_object_id = l_instance_id
2518 AND wdj.organization_id = l_organization_id)
2519 OR EXISTS
2520 (SELECT wewr.asset_number
2521 FROM wip_eam_work_requests wewr
2522 WHERE wewr.work_request_status_id not in (5, 6)
2523 AND wewr.organization_id = l_organization_id
2524 AND wewr.maintenance_object_type = 3
2525 AND wewr.maintenance_object_id = l_instance_id);
2526 exception
2527 when no_data_found then
2528 l_wo_exists := 'N';
2529 end;
2530
2531 if (nvl(l_wo_exists,'N') = 'Y') then
2532 fnd_message.set_name('EAM','EAM_WO_EXISTS');
2533 fnd_msg_pub.add;
2534 RAISE fnd_api.g_exc_error;
2535 end if;
2536
2537 -- check open Service Reqests
2538 begin
2539 SELECT 'Y'
2540 into l_sr_exists
2541 from dual
2542 where exists
2543 (
2544 select cia.incident_id from cs_incidents_vl_sec cia,CS_INCIDENT_STATUSES_VL cis
2545 where cia.customer_product_id = l_instance_id
2546 and cia.incident_status_id = cis.incident_status_id
2547 and nvl(cis.close_flag,'N') <> 'Y'
2548 and cis.language = userenv('lang')
2549
2550 );
2551 exception
2552 when no_data_found then
2553 l_sr_exists := 'N';
2554 end;
2555
2556 if (nvl(l_sr_exists,'N') = 'Y') then
2557 fnd_message.set_name('EAM','EAM_SR_EXISTS');
2558 fnd_msg_pub.add;
2559 RAISE fnd_api.g_exc_error;
2560 end if;
2561 eam_asset_number_pvt.update_asset(
2562 P_API_VERSION => 1.0
2563 ,p_commit => p_commit
2564 ,p_instance_id => l_instance_id
2565 ,P_INVENTORY_ITEM_ID => l_inventory_item_id
2566 ,P_SERIAL_NUMBER => l_serial_number
2567 ,P_ORGANIZATION_ID => l_organization_id
2568 ,p_active_end_date => sysdate
2569 ,X_RETURN_STATUS => x_return_status
2570 ,X_MSG_COUNT => x_msg_count
2571 ,X_MSG_DATA => x_msg_data
2572 );
2573
2574 EXCEPTION
2575 WHEN fnd_api.g_exc_error THEN
2576 ROLLBACK TO asset_util_pvt;
2577 x_return_status := fnd_api.g_ret_sts_error;
2578 fnd_msg_pub.count_and_get(
2579 p_encoded => fnd_api.g_false
2580 ,p_count => x_msg_count
2581 ,p_data => x_msg_data);
2582 WHEN fnd_api.g_exc_unexpected_error THEN
2583 ROLLBACK TO asset_util_pvt;
2584 x_return_status := fnd_api.g_ret_sts_unexp_error;
2585 fnd_msg_pub.count_and_get(
2586 p_encoded => fnd_api.g_false
2587 ,p_count => x_msg_count
2588 ,p_data => x_msg_data);
2589
2590 WHEN OTHERS THEN
2591 ROLLBACK TO asset_util_pvt;
2592 x_return_status := fnd_api.g_ret_sts_unexp_error;
2593
2594 IF fnd_msg_pub.check_msg_level(
2595 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2596 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
2597 END IF;
2598
2599 fnd_msg_pub.count_and_get(
2600 p_encoded => fnd_api.g_false
2601 ,p_count => x_msg_count
2602 ,p_data => x_msg_data);
2603
2604 end deactivate_assets;
2605
2606
2607 --This procedure logs the api return status, message count and all messages
2608 --returned including the last message from the api as well as all messages in
2609 --the message stack at that time
2610 --Author: dgupta
2611 procedure log_api_return(
2612 p_module in varchar2,
2613 p_api in varchar2,
2614 p_return_status in varchar2,
2615 p_msg_count in number,
2616 p_msg_data in varchar2
2617 ) IS
2618 l_msg_count_1 number := null;
2619 l_msg_data_1 varchar2(2000) := NULL;
2620 l_return_char varchar2(2000) := NULL;
2621 begin
2622 -- This should be called only if logging is enabled.
2623 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2624 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2625 p_api || ' returns. '|| 'Return Status = ' || p_return_status ||
2626 '. Message Count = ' || p_msg_count);
2627 end if;
2628 if (p_msg_data is not null) then
2629 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2630 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2631 'Last Message = ' || REPLACE(p_msg_data, CHR(0), ' ')); --null p_msg_data is OK
2632 end if;
2633 end if;
2634 FND_MSG_PUB.Count_And_Get('T', l_msg_count_1, l_msg_data_1);
2635 if ((l_msg_count_1 is not null) and (l_msg_count_1 > 0) and
2636 ((p_msg_count is null) or (l_msg_count_1 <> p_msg_count))) then
2637 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2638 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2639 'Message Count (from message Stack)= ' || l_msg_count_1);
2640 end if;
2641 end if;
2642 l_msg_data_1 := fnd_msg_pub.get(fnd_msg_pub.G_FIRST, FND_API.G_FALSE); --set encoded to true
2643 if (l_msg_count_1 is not null and l_msg_count_1 > 0) then
2644 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2645 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2646 'Message #1 (from message stack) =' || l_msg_data_1);
2647 end if;
2648 for i in 2..l_msg_count_1 LOOP
2649 l_msg_data_1 := fnd_msg_pub.get(fnd_msg_pub.G_NEXT, FND_API.G_FALSE); --set encoded to true
2650 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2651 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2652 'Message #' || to_char(i) || ' (from message stack) =' || l_msg_data_1);
2653 end if;
2654 END LOOP;
2655 end if;
2656 end log_api_return;
2657
2658
2659
2660
2661 FUNCTION get_onhand_quant(p_org_id in number, p_inventory_item_id in number)
2662 RETURN number IS
2663
2664 CURSOR get_material_details(c_organization_id NUMBER,c_inventory_item_id NUMBER) IS
2665 SELECT
2666 msi.lot_control_code,
2667 msi.serial_number_control_code,
2668 msi.revision_qty_control_code
2669 FROM mtl_system_items_b msi
2670 WHERE msi.organization_id = c_organization_id
2671 AND msi.inventory_item_id = c_inventory_item_id;
2672
2673 l_is_revision_control BOOLEAN;
2674 l_is_lot_control BOOLEAN;
2675 l_is_serial_control BOOLEAN;
2676 l_qoh NUMBER;
2677 l_rqoh NUMBER;
2678 l_qr NUMBER;
2679 l_qs NUMBER;
2680 l_att NUMBER;
2681 l_atr NUMBER;
2682 l_return_status VARCHAR2(1);
2683 l_msg_count NUMBER;
2684 l_msg_data VARCHAR2(1000);
2685 X_QOH_PROFILE_VALUE NUMBER;
2686
2687 BEGIN
2688 X_QOH_PROFILE_VALUE := TO_NUMBER(FND_PROFILE.VALUE('EAM_REQUIREMENT_QOH_OPTION'));
2689 IF (X_QOH_PROFILE_VALUE IS NULL)
2690 THEN
2691 X_QOH_PROFILE_VALUE := 1;
2692 END IF;
2693
2694 IF X_QOH_PROFILE_VALUE = 1 THEN
2695 BEGIN
2696 FOR p_materials_csr IN get_material_details(p_org_id,p_inventory_item_id)
2697 LOOP
2698 IF (p_materials_csr.revision_qty_control_code = 2) THEN
2699 l_is_revision_control:=TRUE;
2700 ELSE
2701 l_is_revision_control:=FALSE;
2702 END IF;
2703
2704 IF (p_materials_csr.lot_control_code = 2) THEN
2705 l_is_lot_control:=TRUE;
2706 ELSE
2707 l_is_lot_control:=FALSE;
2708 END IF;
2709
2710 IF (p_materials_csr.serial_number_control_code = 1) THEN
2711 l_is_serial_control:=FALSE;
2712 ELSE
2713 l_is_serial_control:=TRUE;
2714 END IF;
2715
2716 END LOOP;
2717
2718 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
2719 ( p_api_version_number => 1.0
2720 , p_init_msg_lst => FND_API.G_TRUE
2721 , x_return_status => l_return_status
2722 , x_msg_count => l_msg_count
2723 , x_msg_data => l_msg_data
2724 , p_organization_id => p_org_id
2725 , p_inventory_item_id => p_inventory_item_id
2726 , p_tree_mode => 2 --available to transact
2727 , p_is_revision_control => l_is_revision_control
2728 , p_is_lot_control => l_is_lot_control
2729 , p_is_serial_control => l_is_serial_control
2730 , p_revision => NULL
2731 , p_lot_number => NULL
2732 , p_subinventory_code => NULL
2733 , p_locator_id => NULL
2734 , x_qoh => l_qoh
2735 , x_rqoh => l_rqoh
2736 , x_qr => l_qr
2737 , x_qs => l_qs
2738 , x_att => l_att
2739 , x_atr => l_atr
2740 );
2741
2742 IF(l_return_status <> 'S') THEN
2743 RETURN 0;
2744 END IF;
2745
2746 EXCEPTION
2747 WHEN OTHERS THEN
2748 RETURN 0;
2749 END;
2750 ELSE
2751
2752 SELECT NVL(SUM(QUANTITY),0)
2753 into l_qoh
2754 FROM MTL_SECONDARY_INVENTORIES MSS,
2755 MTL_ITEM_QUANTITIES_VIEW MOQ,
2756 MTL_SYSTEM_ITEMS MSI
2757 WHERE MOQ.ORGANIZATION_ID = p_org_id
2758 AND MSI.ORGANIZATION_ID = p_org_id
2759 AND MSS.ORGANIZATION_ID = p_org_id
2760 AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id
2761 AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
2762 AND MSS.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
2763 AND MSS.AVAILABILITY_TYPE = 1;
2764 END IF;
2765
2766 RETURN l_qoh;
2767
2768 end get_onhand_quant;
2769
2770
2771 /* Bug # 3698307
2772 validate_linear_id is added for Linear Asset Management project
2773 Basically it verify's whether the passed linear_id exists in EAM_LINEAR_LOCATIONS
2774 table or not.
2775 */
2776
2777 FUNCTION validate_linear_id(p_eam_linear_id IN NUMBER)
2778 RETURN BOOLEAN IS
2779 l_count NUMBER;
2780 BEGIN
2781
2782 SELECT count(*) INTO l_count FROM eam_linear_locations
2783 WHERE eam_linear_id = p_eam_linear_id;
2784
2785 IF (l_count > 0) THEN
2786 RETURN true;
2787 ELSE
2788 RETURN false;
2789 END IF;
2790
2791 END validate_linear_id;
2792
2793 --------------------------------------------------------------------------
2794 -- PROCEDURE --
2795 -- Create_Asset --
2796 -- --
2797 -- DESCRIPTION --
2798 -- This API is used to create an IB instance whenever a work order is --
2799 -- saved on a rebuild in predefined status. It will call the wrapper --
2800 -- API that in turn calls the IB create_asset API --
2801 -- It a) Create the IB instance b) Updates current status in MSN --
2802 -- c) Instantiates the rebuild d) Updates the WO Record --
2803 -- OR when a rebuild work order's serial number is updated --
2804 -- --
2805 -- This API is invoked from the WO API. --
2806 -- --
2807 -- PURPOSE: --
2808 -- Oracle Applications Rel 12 --
2809 -- --
2810 -- HISTORY: --
2811 -- 05/20/05 Anju Gupta Created --
2812 ----------------------------------------------------------------------------
2813
2814 PROCEDURE CREATE_ASSET(
2815 P_API_VERSION IN NUMBER
2816 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2817 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
2818 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2819 ,X_EAM_WO_REC IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type
2820 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
2821 ,X_MSG_COUNT OUT NOCOPY NUMBER
2822 ,X_MSG_DATA OUT NOCOPY VARCHAR2
2823 )
2824 is
2825 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
2826 l_api_version CONSTANT NUMBER := 1.0;
2827 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2828 l_count number := 0;
2829 l_x_asset_return_status varchar2(1);
2830 l_x_asset_msg_count number;
2831 l_x_asset_msg_data varchar2(20000);
2832 l_instance_id number;
2833 l_stmt_num number := 0;
2834 l_current_status number;
2835 l_organization_id number;
2836 l_description mtl_serial_numbers.descriptive_text%TYPE;
2837 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
2838 begin
2839 -- Standard Start of API savepoint
2840 SAVEPOINT create_asset;
2841
2842 -- Standard call to check for call compatibility.
2843 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2844 RAISE fnd_api.g_exc_unexpected_error;
2845 END IF;
2846
2847 -- Initialize message list if p_init_msg_list is set to TRUE.
2848 IF fnd_api.to_boolean(p_init_msg_list) THEN
2849 fnd_msg_pub.initialize;
2850 END IF;
2851
2852 -- Initialize API return status to success
2853 l_stmt_num := 10;
2854 x_return_status := fnd_api.g_ret_sts_success;
2855
2856 l_eam_wo_rec := x_eam_wo_rec;
2857
2858
2859 -- API body
2860 --Figure out some unknowns
2861 BEGIN
2862 select msn.current_organization_id, msn.descriptive_text, msn.current_status
2863 into l_organization_id, l_description, l_current_status
2864 from mtl_serial_numbers msn
2865 where msn.inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id,
2866 l_eam_wo_rec.asset_group_id)
2867 and msn.serial_number = nvl(l_eam_wo_rec.rebuild_serial_number,
2868 l_eam_wo_rec.asset_number);
2869
2870 EXCEPTION
2871
2872 WHEN NO_DATA_FOUND THEN
2873 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2874 THEN
2875 FND_MSG_PUB.add_exc_msg
2876 ( 'EAM_COMMON_UTILITIES_PVT'
2877 , '.Create_Asset : Statement -'||to_char(l_stmt_num)
2878 );
2879 END IF;
2880 RAISE fnd_api.g_exc_unexpected_error;
2881
2882 END;
2883
2884 --This is a predefined rebuild on which a work order is being defined -
2885 --Create an asset against it and reset the work order pl/sql table
2886 l_stmt_num := 30;
2887
2888 if l_current_status = 1 then
2889
2890 EAM_ASSET_NUMBER_PVT.Create_Asset(
2891 P_API_VERSION => p_api_version
2892 ,P_INIT_MSG_LIST => p_init_msg_list
2893 ,P_COMMIT => p_commit
2894 ,P_VALIDATION_LEVEL => p_validation_level
2895 ,P_INVENTORY_ITEM_ID => nvl(l_eam_wo_rec.rebuild_item_id, l_eam_wo_rec.asset_group_id)
2896 ,P_SERIAL_NUMBER => nvl(l_eam_wo_rec.rebuild_serial_number, l_eam_wo_rec.asset_number)
2897 ,P_INSTANCE_NUMBER => null
2898 ,P_INSTANCE_DESCRIPTION => l_description
2899 ,P_ORGANIZATION_ID => l_organization_id
2900 ,P_LAST_UPDATE_DATE => sysdate
2901 ,P_LAST_UPDATED_BY => l_eam_wo_rec.user_id
2902 ,P_CREATION_DATE => sysdate
2903 ,P_CREATED_BY => l_eam_wo_rec.user_id
2904 ,P_LAST_UPDATE_LOGIN => l_eam_wo_rec.user_id
2905 ,X_OBJECT_ID => l_instance_id
2906 ,X_RETURN_STATUS => l_x_asset_return_status
2907 ,X_MSG_COUNT => l_x_asset_msg_count
2908 ,X_MSG_DATA => l_x_asset_msg_data
2909 );
2910
2911 if (l_x_asset_return_status <> FND_API.G_RET_STS_SUCCESS) then
2912 l_stmt_num := 40;
2913 RAISE FND_API.G_EXC_ERROR ;
2914 end if;
2915
2916 else
2917
2918 l_stmt_num := 50;
2919 begin
2920
2921 select cii.instance_id
2922 into l_instance_id
2923 from csi_item_instances cii
2924 where inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id,
2925 l_eam_wo_rec.asset_group_id)
2926 and serial_number = nvl(l_eam_wo_rec.rebuild_serial_number,
2927 l_eam_wo_rec.asset_number);
2928
2929
2930 EXCEPTION
2931
2932 WHEN NO_DATA_FOUND THEN
2933 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2934 FND_MSG_PUB.add_exc_msg
2935 ( 'EAM_COMMON_UTILITIES_PVT'
2936 , '.Create_Asset : Statement -'||to_char(l_stmt_num)
2937 );
2938 END IF;
2939 RAISE fnd_api.g_exc_unexpected_error;
2940
2941 END;
2942 end if;
2943
2944
2945 --The Instance has been created sucessfully. Update the WO Record
2946
2947 l_stmt_num := 50;
2948
2949 l_eam_wo_rec.maintenance_object_type := 3;
2950 l_eam_wo_rec.maintenance_object_id := l_instance_id;
2951
2952 x_eam_wo_rec := l_eam_wo_rec;
2953
2954 -- End of API body.
2955 -- Standard check of p_commit.
2956 IF fnd_api.to_boolean(p_commit) THEN
2957 COMMIT WORK;
2958 END IF;
2959
2960 -- Standard call to get message count and if count is 1, get message info.
2961 fnd_msg_pub.count_and_get(
2962 p_count => x_msg_count
2963 ,p_data => x_msg_data);
2964
2965
2966 EXCEPTION
2967 WHEN fnd_api.g_exc_error THEN
2968 ROLLBACK TO create_asset;
2969 x_return_status := fnd_api.g_ret_sts_error;
2970 fnd_msg_pub.count_and_get(
2971 p_count => x_msg_count
2972 ,p_data => x_msg_data);
2973 WHEN fnd_api.g_exc_unexpected_error THEN
2974 ROLLBACK TO create_asset;
2975 x_return_status := fnd_api.g_ret_sts_unexp_error;
2976 fnd_msg_pub.count_and_get(
2977 p_count => x_msg_count
2978 ,p_data => x_msg_data);
2979 WHEN OTHERS THEN
2980 ROLLBACK TO create_asset;
2981 x_return_status := fnd_api.g_ret_sts_unexp_error;
2982
2983 IF fnd_msg_pub.check_msg_level(
2984 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2985 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
2986 END IF;
2987
2988 fnd_msg_pub.count_and_get(
2989 p_count => x_msg_count
2990 ,p_data => x_msg_data);
2991
2992 end create_asset;
2993
2994 FUNCTION check_deactivate(
2995 p_maintenance_object_id IN NUMBER, -- for Maintenance Object Type of 3, this should be Instance_Id
2996 p_maintenance_object_type IN NUMBER -- Type 3 (Instance Id)
2997
2998 )
2999 return boolean is
3000 l_gen_object_id number;
3001 l_result boolean;
3002 l_gen_obj_exists varchar2(1); -- Bug 6799616
3003 l_hr_exists varchar2(1);
3004 l_routes_exists varchar2(1);
3005 l_wo_exists varchar2(1);
3006 l_network_asset_flag varchar2(1);
3007 begin
3008 l_result := true;
3009 -- Bug 6799616
3010 -- Added exception handling block in case the item instance is not serial
3011 begin
3012 select gen_object_id into l_gen_object_id
3013 from mtl_serial_numbers msn, csi_item_instances cii
3014 where msn.inventory_item_id = cii.inventory_item_id
3015 and msn.serial_number = cii.serial_number
3016 and cii.instance_id = p_maintenance_object_id;
3017 if (l_gen_object_id is not null) then
3018 l_gen_obj_exists := 'Y';
3019 else
3020 l_gen_obj_exists := 'N';
3021 end if;
3022 exception
3023 when no_data_found then
3024 l_gen_obj_exists := 'N';
3025 end;
3026
3027 --HIERARCHY CHECK
3028 if (l_gen_obj_exists = 'Y') then
3029 begin
3030 SELECT 'Y'
3031 INTO l_hr_exists
3032 FROM DUAL
3033 WHERE EXISTS
3034 (SELECT mog.object_id
3035 FROM mtl_object_genealogy mog
3036 WHERE mog.object_id = l_gen_object_id
3037
3038 -- Fix for bug 2219479. We do not allow assets that are
3039 -- a child or a parent in the future to be deactivated.
3040 -- hence the check for start_date_active is removed
3041
3042 AND sysdate <= nvl(mog.end_date_active(+), sysdate))
3043 OR EXISTS
3044 (SELECT mog.object_id
3045 FROM mtl_object_genealogy mog
3046 WHERE mog.parent_object_id = l_gen_object_id
3047 AND sysdate <= nvl(mog.end_date_active(+), sysdate));
3048
3049 exception
3050 when no_data_found then
3051 l_hr_exists := 'N';
3052 end;
3053
3054 if (l_hr_exists = 'Y') then
3055 fnd_message.set_name('EAM','EAM_HIERARCHY_EXISTS');
3056 fnd_msg_pub.add;
3057 l_result := false;
3058
3059 end if;
3060 end if;
3061
3062 -- ROUTES CHECK
3063 begin
3064 SELECT 'Y'
3065 INTO l_routes_exists
3066 FROM DUAL
3067 WHERE EXISTS
3068 (SELECT mena.network_association_id
3069 FROM mtl_eam_network_assets mena
3070 WHERE mena.maintenance_object_type =3
3071 AND mena.maintenance_object_id = p_maintenance_object_id
3072 AND sysdate >= nvl(mena.start_date_active(+), sysdate)
3073 AND sysdate <= nvl(mena.end_date_active(+), sysdate));
3074 exception
3075 when no_data_found then
3076 l_routes_exists := 'N';
3077 end;
3078
3079
3080 if (l_routes_exists = 'Y') then
3081 fnd_message.set_name('EAM','EAM_ROUTE_EXISTS');
3082 fnd_msg_pub.add;
3083 l_result := false;
3084 end if;
3085
3086 -- WORK REQUEST AND WORK ORDER CHECK
3087 begin
3088 SELECT 'Y'
3089 INTO l_wo_exists
3090 FROM DUAL
3091 WHERE EXISTS
3092 (SELECT wdj.wip_entity_id
3093 FROM wip_discrete_jobs wdj
3094 WHERE wdj.status_type not in (4, 5, 7, 12)
3095 AND wdj.maintenance_object_type = 3
3096 AND wdj.maintenance_object_id = p_maintenance_object_id
3097 )
3098 OR EXISTS
3099 (SELECT wewr.asset_number
3100 FROM wip_eam_work_requests wewr
3101 WHERE wewr.work_request_status_id not in (5, 6)
3102 AND wewr.maintenance_object_type = 3
3103 AND wewr.maintenance_object_id = p_maintenance_object_id);
3104 exception
3105 when no_data_found then
3106 l_wo_exists := 'N';
3107 end;
3108
3109 if (l_wo_exists = 'Y') then
3110 fnd_message.set_name('EAM','EAM_WO_EXISTS');
3111 fnd_msg_pub.add;
3112 l_result := false;
3113 end if;
3114
3115 -- ROUTES CHECK: Route assets cannot be de-activated
3116
3117 begin
3118 select network_asset_flag into l_network_asset_flag
3119 from csi_item_instances
3120 where instance_id = p_maintenance_object_id;
3121
3122 exception
3123 when no_data_found then
3124 l_network_asset_flag := 'N';
3125 end;
3126
3127 if (l_network_asset_flag = 'Y') then
3128 fnd_message.set_name('EAM','EAM_ROUTE_DEACTIVATE');
3129 fnd_msg_pub.add;
3130 l_result := false;
3131 end if;
3132
3133 return l_result;
3134 end check_deactivate;
3135
3136
3137 FUNCTION get_parent_asset(p_parent_job_id in number,
3138 p_organization_id in number)
3139 return VARCHAR2 IS
3140 l_parent_asset_number VARCHAR2(80);
3141
3142 begin
3143 select cii.instance_number
3144 into l_parent_asset_number
3145 from csi_item_instances cii, wip_discrete_jobs wdj
3146 where wdj.wip_entity_id = p_parent_job_id
3147 and wdj.organization_id = p_organization_id
3148 and wdj.maintenance_object_type = 3
3149 and wdj.maintenance_object_id = cii.instance_id;
3150
3151 return l_parent_asset_number;
3152
3153 end get_parent_asset;
3154
3155
3156 --------------------------------------------------------------------------
3157 -- PROCEDURE --
3158 -- Adjust_WORU --
3159 -- --
3160 -- DESCRIPTION --
3161 -- --
3162 -- This API is invoked from the Gantt Workbench --
3163 -- --
3164 -- PURPOSE: --
3165 -- Oracle Applications Rel 12 --
3166 -- --
3167 -- HISTORY: --
3168 -- 06/27/05 Anju Gupta Created --
3169 ----------------------------------------------------------------------------
3170 PROCEDURE write_WORU (
3171 P_WIP_ENTITY_ID IN NUMBER
3172 ,P_ORGANIZATION_ID IN NUMBER
3173 ,P_OPERATION_SEQ_NUM IN NUMBER
3174 ,P_RESOURCE_SEQ_NUM IN NUMBER
3175 ,P_UPDATE_HIERARCHY IN VARCHAR2
3176 ,P_START IN DATE
3177 ,P_END IN DATE
3178 ,P_DELTA IN NUMBER
3179 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
3180 ,X_MSG_COUNT OUT NOCOPY NUMBER
3181 ,X_MSG_DATA OUT NOCOPY VARCHAR2
3182 )
3183 is
3184 l_stmt_num number := 0;
3185 l_return_status varchar2(1);
3186 l_msg_count number;
3187 l_msg_data varchar2(20000);
3188
3189
3190 begin
3191 -- Standard Start of API savepoint
3192 SAVEPOINT write_woru;
3193
3194 -- Initialize API return status to success
3195 l_stmt_num := 10;
3196 x_return_status := fnd_api.g_ret_sts_success;
3197
3198 -- API body
3199
3200 l_stmt_num := 20;
3201 if (nvl(p_update_hierarchy, 'N') = 'N') then
3202
3203 if (p_operation_seq_num is null AND p_resource_seq_num is null) then
3204 update wip_operation_resource_usage
3205 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3206 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3207 where wip_entity_id = p_wip_entity_id
3208 and organization_id = p_organization_id;
3209
3210 elsif (p_operation_seq_num is not null AND p_resource_seq_num is null) then
3211 update wip_operation_resource_usage
3212 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3213 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3214 where wip_entity_id = p_wip_entity_id
3215 and organization_id = p_organization_id
3216 and operation_seq_num = p_operation_seq_num;
3217
3218 else
3219 update wip_operation_resource_usage
3220 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3221 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3222 where wip_entity_id = p_wip_entity_id
3223 and organization_id = p_organization_id
3224 and operation_seq_num = p_operation_seq_num
3225 and resource_seq_num = p_resource_seq_num;
3226
3227 end if;
3228 null;
3229
3230 else
3231 if (p_operation_seq_num is null AND p_resource_seq_num is null) then
3232 update wip_operation_resource_usage
3233 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3234 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3235 where wip_entity_id in (select p_wip_entity_id from dual
3236 union
3237 select child_object_id from wip_sched_relationships
3238 where relationship_type = 1
3239 start with parent_object_id = p_wip_entity_id
3240 connect by prior child_object_id = parent_object_id )
3241 and organization_id = p_organization_id;
3242
3243 elsif (p_operation_seq_num is not null AND p_resource_seq_num is null) then
3244 update wip_operation_resource_usage
3245 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3246 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3247 where wip_entity_id in (select p_wip_entity_id from dual
3248 union
3249 select child_object_id from wip_sched_relationships
3250 where relationship_type = 1
3251 start with parent_object_id = p_wip_entity_id
3252 connect by prior child_object_id = parent_object_id )
3253 and organization_id = p_organization_id;
3254
3255 else
3256 update wip_operation_resource_usage
3257 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3258 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3259 where wip_entity_id in (select p_wip_entity_id from dual
3260 union
3261 select child_object_id from wip_sched_relationships
3262 where relationship_type = 1
3263 start with parent_object_id = p_wip_entity_id
3264 connect by prior child_object_id = parent_object_id )
3265 and organization_id = p_organization_id;
3266
3267 end if;
3268 null;
3269
3270 end if;
3271
3272
3273
3274
3275 EXCEPTION
3276
3277 WHEN fnd_api.g_exc_error THEN
3278 ROLLBACK TO adjust_woru;
3279 x_return_status := fnd_api.g_ret_sts_error;
3280
3281 WHEN fnd_api.g_exc_unexpected_error THEN
3282 ROLLBACK TO adjust_woru;
3283 x_return_status := fnd_api.g_ret_sts_unexp_error;
3284
3285 WHEN OTHERS THEN
3286 ROLLBACK TO adjust_woru;
3287 x_return_status := fnd_api.g_ret_sts_unexp_error;
3288
3289
3290
3291
3292
3293 end write_woru;
3294
3295
3296 PROCEDURE Adjust_WORU (
3297 P_API_VERSION IN NUMBER
3298 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
3299 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
3300 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3301 ,P_WIP_ENTITY_ID IN NUMBER
3302 ,P_ORGANIZATION_ID IN NUMBER
3303 ,P_OPERATION_SEQ_NUM IN NUMBER
3304 ,P_RESOURCE_SEQ_NUM IN NUMBER
3305 ,P_DELTA IN NUMBER
3306 ,P_UPDATE_HIERARCHY IN VARCHAR2
3307 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
3308 ,X_MSG_COUNT OUT NOCOPY NUMBER
3309 ,X_MSG_DATA OUT NOCOPY VARCHAR2
3310 )
3311 is
3312 l_api_name CONSTANT VARCHAR2(30) := 'adjust_woru';
3313 l_api_version CONSTANT NUMBER := 1.0;
3314 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3315 l_woru_count number := 0;
3316 l_instance_id number;
3317 l_stmt_num number := 0;
3318 l_start_date date;
3319 l_return_status varchar2(1);
3320 l_msg_count number;
3321 l_msg_data varchar2(20000);
3322 l_min_woru_start_date date;
3323 l_max_woru_end_date date;
3324 l_end_date date;
3325 l_wor_start_date date;
3326 l_wor_end_date date;
3327 SHRINK_WITH_ASSIGNMENTS EXCEPTION;
3328 l_woru_duration number;
3329 l_wor_duration number;
3330 l_instance_count number;
3331
3332 CURSOR c_woru(p_wip_entity_id number,
3333 p_organization_id number,
3334 p_operation_seq_num number,
3335 p_resource_seq_num number) is
3336 select woru.start_date, woru.completion_date, woru.instance_id
3337 from wip_operation_resource_usage woru
3338 where woru.wip_entity_id = p_wip_entity_id
3339 and woru.operation_seq_num = p_operation_seq_num
3340 and woru.resource_seq_num = p_resource_seq_num
3341 and woru.organization_id = p_organization_id;
3342
3343 begin
3344 -- Standard Start of API savepoint
3345 SAVEPOINT adjust_woru;
3346
3347 -- Standard call to check for call compatibility.
3348 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3349 RAISE fnd_api.g_exc_unexpected_error;
3350 END IF;
3351
3352 -- Initialize message list if p_init_msg_list is set to TRUE.
3353 IF fnd_api.to_boolean(p_init_msg_list) THEN
3354 fnd_msg_pub.initialize;
3355 END IF;
3356
3357 -- Initialize API return status to success
3358 l_stmt_num := 10;
3359 x_return_status := fnd_api.g_ret_sts_success;
3360
3361 -- API body
3362 -- Figure out if it is a move or resize
3363
3364 if nvl(p_delta, 0) <> 0 then
3365 l_stmt_num := 20;
3366 --its a move. Adjust all the rows in WORU
3367 write_woru(P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
3368 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
3369 P_OPERATION_SEQ_NUM => P_OPERATION_SEQ_NUM,
3370 P_RESOURCE_SEQ_NUM => P_RESOURCE_SEQ_NUM,
3371 P_UPDATE_HIERARCHY => P_UPDATE_HIERARCHY,
3372 P_START => null,
3373 P_END => null,
3374 P_DELTA => P_DELTA,
3375 X_RETURN_STATUS => l_return_status,
3376 X_MSG_COUNT => l_msg_count,
3377 X_MSG_DATA => l_msg_data);
3378
3379 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3380 RAISE FND_API.G_EXC_ERROR ;
3381 end if;
3382
3383 else
3384 l_stmt_num := 30;
3385 -- Figure out if the WOR still encompasses WORU
3386 --!!!!!When p_delta is null, both operation_seq and resource_seq are
3387 --passed.
3388
3389 BEGIN
3390
3391 select min(WORU.start_date), max(WORU.completion_date)
3392 into l_min_woru_start_date, l_max_woru_end_date
3393 from wip_operation_resource_usage woru
3394 where woru.wip_entity_id = p_wip_entity_id
3395 and woru.operation_seq_num = p_operation_seq_num
3396 and woru.resource_seq_num = p_resource_seq_num
3397 and woru.organization_id = p_organization_id;
3398
3399 l_stmt_num := 40;
3400
3401 select wor.start_date, wor.completion_date
3402 into l_wor_start_date, l_wor_end_date
3403 from wip_operation_resources wor
3404 where wor.wip_entity_id = p_wip_entity_id
3405 and wor.operation_seq_num = p_operation_seq_num
3406 and wor.resource_seq_num = p_resource_seq_num
3407 and wor.organization_id = p_organization_id;
3408
3409
3410 EXCEPTION
3411
3412 WHEN NO_DATA_FOUND THEN
3413 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3414 THEN
3415 FND_MSG_PUB.add_exc_msg
3416 ( 'EAM_COMMON_UTILITIES_PVT'
3417 , '.Adjust_WORU : Statement -'||to_char(l_stmt_num)
3418 );
3419 END IF;
3420 RAISE fnd_api.g_exc_unexpected_error;
3421
3422 END;
3423
3424 l_stmt_num := 50;
3425
3426 if (l_wor_start_date <= l_min_woru_start_date ) then
3427 --WORU is still encompassed by WOR.
3428 --It is an expand of WOR dates
3429 --Update the rows in WORU where instance_id is null
3430 update wip_operation_resource_usage woru
3431 set start_date = l_wor_start_date
3432 where woru.wip_entity_id = p_wip_entity_id
3433 and woru.operation_seq_num = p_operation_seq_num
3434 and woru.resource_seq_num = p_resource_seq_num
3435 and woru.organization_id = p_organization_id
3436 and WORU.start_date = l_min_woru_start_date
3437 and woru.instance_id is null;
3438 /* if ( l_wor_end_date < l_max_woru_end_date) then
3439 return;
3440 end if;
3441 In case resource is rescheduled by moving the bar with out changing the duration it should not return.
3442 Hence commented, #6159641
3443 */
3444 end if;
3445 if ( l_wor_end_date >= l_max_woru_end_date) then
3446 --WORU is still encompassed by WOR.
3447 --It is an expand of WOR dates
3448 --Update the rows in WORU where instance_id is null
3449 update wip_operation_resource_usage woru
3450 set completion_date = l_wor_end_date
3451 where woru.wip_entity_id = p_wip_entity_id
3452 and woru.operation_seq_num = p_operation_seq_num
3453 and woru.resource_seq_num = p_resource_seq_num
3454 and woru.organization_id = p_organization_id
3455 and WORU.completion_date = l_max_woru_end_date
3456 and woru.instance_id is null;
3457
3458 /* return;
3459 In case resource is rescheduled by moving the bar with out changing the duration it should not return.
3460 Hence commented, #6159641
3461 */
3462 end if;
3463
3464
3465 --Calculate duration
3466 --If WORU duration is lesser than WOR duration, then its a shrink, otherwise it is a move
3467 --there might be a corner case, where the Resource is moved, but p_delta is not passed
3468 --Note we've already checked for WORU being encompassed, so this logic will hold
3469
3470 /*Added # 6159641, to update woru correctly when resource rescheduled by moving the bar with out
3471 changing duration, query woru again to get the recently updated data*/
3472
3473 select min(WORU.start_date), max(WORU.completion_date)
3474 into l_min_woru_start_date, l_max_woru_end_date
3475 from wip_operation_resource_usage woru
3476 where woru.wip_entity_id = p_wip_entity_id
3477 and woru.operation_seq_num = p_operation_seq_num
3478 and woru.resource_seq_num = p_resource_seq_num
3479 and woru.organization_id = p_organization_id;
3480
3481 /*--Code added, end #6159641---*/
3482
3483 l_woru_duration := l_max_woru_end_date - l_min_woru_start_date;
3484 l_wor_duration := l_wor_end_date - l_wor_start_date;
3485
3486 select count(*)
3487 into l_instance_count
3488 from wip_operation_resource_usage woru
3489 where woru.wip_entity_id = p_wip_entity_id
3490 and woru.operation_seq_num = p_operation_seq_num
3491 and woru.resource_seq_num = p_resource_seq_num
3492 and woru.organization_id = p_organization_id
3493 and (woru.instance_id is not null
3494 or woru.serial_number is not null);
3495
3496 if l_wor_duration < l_woru_duration then
3497 l_stmt_num := 60;
3498
3499 if (l_instance_count <> 0) then
3500 RAISE SHRINK_WITH_ASSIGNMENTS;
3501 end if;
3502 end if;
3503
3504 l_stmt_num := 70;
3505 if (l_wor_duration > l_woru_duration or (l_wor_duration < l_woru_duration and l_instance_count = 0)) then
3506 --no instances, only WORU rows that represent the Resource duration
3507 --Adjust these rows
3508
3509
3510 FOR c_woru_rec IN c_woru(p_wip_entity_id,
3511 p_organization_id,
3512 p_operation_seq_num,
3513 p_resource_seq_num) LOOP
3514
3515 if (l_wor_start_date <= c_woru_rec.start_date and l_wor_end_date >= c_woru_rec.start_date) then
3516 l_start_date := c_woru_rec.start_date;
3517 else
3518 l_start_date := l_wor_start_date;
3519 end if;
3520
3521 l_end_date := l_start_date + (c_woru_rec.completion_date - c_woru_rec.start_date);
3522
3523 if l_end_date > l_wor_end_date then
3524 l_end_date := l_wor_end_date;
3525 end if;
3526
3527 l_stmt_num := 80;
3528
3529 write_woru(P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
3530 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
3531 P_OPERATION_SEQ_NUM => P_OPERATION_SEQ_NUM,
3532 P_RESOURCE_SEQ_NUM => P_RESOURCE_SEQ_NUM,
3533 P_UPDATE_HIERARCHY => P_UPDATE_HIERARCHY,
3534 P_START => l_start_date,
3535 P_END => l_end_date,
3536 P_DELTA => null,
3537 X_RETURN_STATUS => l_return_status,
3538 X_MSG_COUNT => l_msg_count,
3539 X_MSG_DATA => l_msg_data);
3540
3541 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3542 RAISE FND_API.G_EXC_ERROR ;
3543 end if;
3544
3545 END LOOP;
3546
3547 end if;
3548 end if;
3549
3550
3551 -- End of API body.
3552 -- Standard check of p_commit.
3553 IF fnd_api.to_boolean(p_commit) THEN
3554 COMMIT WORK;
3555 END IF;
3556
3557 -- Standard call to get message count and if count is 1, get message info.
3558 fnd_msg_pub.count_and_get(
3559 p_count => x_msg_count
3560 ,p_data => x_msg_data);
3561
3562
3563 EXCEPTION
3564 WHEN SHRINK_WITH_ASSIGNMENTS THEN
3565 rollback to adjust_woru;
3566 x_return_status := fnd_api.g_ret_sts_error;
3567 fnd_msg_pub.count_and_get(
3568 p_count => x_msg_count
3569 ,p_data => x_msg_data);
3570
3571 WHEN fnd_api.g_exc_error THEN
3572 ROLLBACK TO adjust_woru;
3573 x_return_status := fnd_api.g_ret_sts_error;
3574 fnd_msg_pub.count_and_get(
3575 p_count => x_msg_count
3576 ,p_data => x_msg_data);
3577 WHEN fnd_api.g_exc_unexpected_error THEN
3578 ROLLBACK TO adjust_woru;
3579 x_return_status := fnd_api.g_ret_sts_unexp_error;
3580 fnd_msg_pub.count_and_get(
3581 p_count => x_msg_count
3582 ,p_data => x_msg_data);
3583 WHEN OTHERS THEN
3584 ROLLBACK TO adjust_woru;
3585 x_return_status := fnd_api.g_ret_sts_unexp_error;
3586
3587 IF fnd_msg_pub.check_msg_level(
3588 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3589 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
3590 END IF;
3591
3592 fnd_msg_pub.count_and_get(
3593 p_count => x_msg_count
3594 ,p_data => x_msg_data);
3595
3596 end adjust_woru;
3597
3598 FUNCTION get_asset_area( p_instance_id NUMBER, p_maint_org_id NUMBER) RETURN VARCHAR2
3599 IS
3600 CURSOR get_area IS
3601 SELECT el.location_codes
3602 FROM eam_org_maint_defaults eomd,
3603 mtl_eam_locations el
3604 WHERE eomd.area_id = el.location_id
3605 AND eomd.object_type = 50
3606 AND eomd.object_id = p_instance_id
3607 AND eomd.organization_id = p_maint_org_id;
3608
3609 l_area_code MTL_EAM_LOCATIONS.LOCATION_CODES%TYPE;
3610 BEGIN
3611 OPEN get_area;
3612 FETCH get_area INTO l_area_code;
3613 CLOSE get_area;
3614
3615 RETURN l_area_code;
3616 END get_asset_area;
3617
3618
3619 PROCEDURE set_profile(
3620 name in varchar2,
3621 value in varchar2
3622 )
3623 is
3624
3625 begin
3626 fnd_profile.put(name, value);
3627 end set_profile;
3628
3629 Function is_active(
3630 p_instance_id number
3631 ) return varchar2
3632 is
3633 l_active_start_date date;
3634 l_active_end_date date;
3635 l_return_value varchar2(1);
3636 begin
3637
3638
3639 select active_start_date,active_end_date
3640 into l_active_start_date,l_active_end_date
3641 from csi_item_instances
3642 where instance_id = p_instance_id;
3643
3644 if (l_active_start_date > sysdate) then
3645 l_return_value := 'N';
3646 elsif (l_active_start_date <= sysdate and l_active_end_date is null) then
3647 l_return_value := 'Y';
3648 elsif (l_active_start_date <= sysdate and l_active_end_date < sysdate) then
3649 l_return_value := 'N';
3650 elsif (l_active_start_date <= sysdate and l_active_end_date > sysdate) then
3651 l_return_value := 'Y';
3652 end if;
3653
3654 return l_return_value;
3655 exception
3656 when no_data_found then
3657 l_return_value := 'N';
3658
3659 end is_active;
3660
3661 FUNCTION showCompletionFields( p_wip_entity_id NUMBER ) RETURN VARCHAR2
3662 IS
3663 CURSOR get_wo_details IS
3664 SELECT organization_id,
3665 maintenance_object_type,
3666 maintenance_object_id
3667 FROM wip_discrete_jobs
3668 WHERE wip_entity_id = p_wip_entity_id;
3669
3670 l_org_id wip_discrete_jobs.organization_id%TYPE;
3671 l_maint_object_id wip_discrete_jobs.maintenance_object_type%TYPE;
3672 l_maint_object_type wip_discrete_jobs.maintenance_object_id%TYPE;
3673
3674 l_obj_exists NUMBER := 0;
3675 l_return_status VARCHAR2(1) := 'N';
3676 BEGIN
3677 OPEN get_wo_details;
3678 FETCH get_wo_details INTO l_org_id, l_maint_object_type, l_maint_object_id;
3679 CLOSE get_wo_details;
3680
3681 IF l_maint_object_type IS NULL THEN
3682 return l_return_status;
3683 ELSIF l_maint_object_type = 2 THEN
3684
3685 select count(1) into l_obj_exists
3686 from mtl_system_items_b_kfv msik
3687 where msik.inventory_item_id = l_maint_object_id
3688 AND msik.organization_id = l_org_id;
3689
3690 IF l_obj_exists = 1 THEN
3691 l_return_status := 'Y';
3692 END IF;
3693
3694 ELSIF l_maint_object_type = 3 THEN
3695
3696 select count(1) into l_obj_exists
3697 from csi_item_instances cii,
3698 mtl_serial_numbers msn
3699 where cii.serial_number = msn.serial_number
3700 and cii.inventory_item_id = msn.inventory_item_id
3701 and cii.instance_id = l_maint_object_id
3702 and cii.last_vld_organization_id = l_org_id
3703 and msn.current_status = 4
3704 and nvl(cii.network_asset_flag,'N') <> 'Y';
3705
3706 IF l_obj_exists = 1 THEN
3707 l_return_status := 'Y';
3708 END IF;
3709
3710 END IF;
3711
3712 RETURN l_return_status;
3713 END showCompletionFields;
3714
3715 PROCEDURE update_logical_asset(
3716 p_inventory_item_id number
3717 ,p_serial_number varchar2
3718 ,p_equipment_gen_object_id number
3719 ,p_network_asset_flag varchar2
3720 ,p_pn_location_id number
3721 ,x_return_status out nocopy varchar2
3722 ) is
3723 l_gen_object_id number;
3724 l_equipment_gen_object_id number;
3725 l_return_value boolean;
3726
3727 begin
3728 l_return_value := FALSE;
3729 x_return_status := fnd_api.g_ret_sts_success;
3730 l_equipment_gen_object_id := p_equipment_gen_object_id;
3731
3732 if (p_pn_location_id is not null AND p_pn_location_id <> FND_API.G_MISS_NUM) then
3733 l_return_value := TRUE;
3734 end if;
3735
3736 if (p_network_asset_flag is not null AND p_network_asset_flag <> FND_API.G_MISS_CHAR AND p_network_asset_flag = 'Y' AND l_return_value <> TRUE) then
3737 l_return_value := TRUE;
3738 end if;
3739
3740
3741 if (l_return_value <> TRUE AND l_equipment_gen_object_id IS NOT NULL
3742 AND l_equipment_gen_object_id <> FND_API.G_MISS_NUM) then
3743 begin
3744 select msn.gen_object_id
3745 into l_gen_object_id
3746 from mtl_serial_numbers msn
3747 where msn.serial_number = p_serial_number
3748 and msn.inventory_item_id = p_inventory_item_id
3749 ;
3750
3751 if (l_equipment_gen_object_id <> l_gen_object_id) then
3752 l_return_value := TRUE;
3753 end if;
3754 exception
3755 when others then
3756 x_return_status := fnd_api.g_ret_sts_unexp_error;
3757 end;
3758 end if;
3759
3760 if (l_return_value = TRUE) then
3761
3762 begin
3763 update mtl_serial_numbers
3764 set group_mark_id = 1
3765 where serial_number = p_serial_number
3766 and inventory_item_id = p_inventory_item_id;
3767 exception
3768 when others then
3769 x_return_status := fnd_api.g_ret_sts_unexp_error;
3770 end;
3771 end if;
3772
3773 begin
3774 update mtl_serial_numbers
3775 set eam_linear_location_id = -1
3776 where serial_number = p_serial_number
3777 and inventory_item_id = p_inventory_item_id;
3778
3779 exception
3780 when others then
3781 x_return_status := fnd_api.g_ret_sts_unexp_error;
3782 end;
3783
3784
3785 end update_logical_asset;
3786
3787
3788 FUNCTION get_scheduled_start_date( p_wip_entity_id NUMBER ) RETURN DATE
3789 IS
3790
3791 l_scheduled_start_date Date ;
3792
3793 BEGIN
3794 SELECT MIN(scheduled_start_date)
3795 INTO l_scheduled_start_date
3796 FROM (
3797 select scheduled_start_date
3798 FROM WIP_DISCRETE_JOBS wdj_child
3799 WHERE wdj_child.wip_entity_id= p_wip_entity_id
3800 union all
3801 SELECT scheduled_start_date
3802 FROM WIP_DISCRETE_JOBS wdj_child
3803 where wdj_child.wip_entity_id
3804 IN (SELECT child_object_id
3805 FROM eam_wo_relationships
3806 WHERE parent_relationship_type =1
3807 START WITH parent_object_id = p_wip_entity_id
3808 AND parent_relationship_type = 1
3809 CONNECT BY parent_object_id = prior child_object_id
3810 AND parent_relationship_type = 1 ) ) ;
3811
3812 RETURN(l_scheduled_start_date);
3813
3814 END get_scheduled_start_date;
3815
3816 FUNCTION get_scheduled_completion_date( p_wip_entity_id NUMBER ) RETURN DATE
3817 IS
3818
3819 l_scheduled_completion_date Date ;
3820
3821 BEGIN
3822 SELECT MAX(scheduled_completion_date)
3823 INTO l_scheduled_completion_date
3824 FROM (
3825 SELECT scheduled_completion_date
3826 FROM WIP_DISCRETE_JOBS wdj_child
3827 WHERE wdj_child.wip_entity_id=p_wip_entity_id
3828 union all
3829 Select Scheduled_completion_date
3830 from WIP_DISCRETE_JOBS wdj_child
3831 where wdj_child.wip_entity_id
3832 IN (SELECT child_object_id
3833 FROM eam_wo_relationships
3834 WHERE parent_relationship_type =1
3835 START WITH parent_object_id = p_wip_entity_id
3836 AND parent_relationship_type = 1
3837 CONNECT BY parent_object_id = prior child_object_id
3838 AND parent_relationship_type = 1));
3839
3840 RETURN(l_scheduled_completion_date);
3841
3842 END get_scheduled_completion_date;
3843
3844
3845 END EAM_COMMON_UTILITIES_PVT;
[Home] [Help]