[Home] [Help]
PACKAGE BODY: APPS.EAM_COMMON_UTILITIES_PVT
Source
1 PACKAGE BODY EAM_COMMON_UTILITIES_PVT AS
2 /* $Header: EAMPUTLB.pls 120.50.12020000.4 2013/03/22 20:55:07 esrodrig 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 -- Changed by shengwa to remove wildcard _
1002 FUNCTION invalid_item_name (p_item_name in varchar2)
1003 return boolean is
1004 l_boolean boolean;
1005 BEGIN
1006 if (instr(p_item_name,'%')>0) then
1007 l_boolean := true;
1008 else
1009 l_boolean := false;
1010 end if;
1011
1012 return l_boolean;
1013 END invalid_item_name;
1014
1015
1016
1017 FUNCTION get_mfg_meaning(p_lookup_type in VARCHAR2 , p_lookup_code in number)
1018 return VARCHAR2 IS
1019 l_meaning VARCHAR2(80);
1020
1021 begin
1022 select meaning
1023 into l_meaning
1024 from mfg_lookups
1025 where lookup_type = p_lookup_type
1026 and lookup_code=p_lookup_code;
1027
1028 return l_meaning;
1029
1030 end get_mfg_meaning;
1031
1032 FUNCTION get_item_name(p_service_request_id in number,
1033 p_org_id in number,
1034 p_inv_organization_id in number
1035 ) return varchar2 is
1036 l_item_name varchar2(240);
1037 l_organization_id number;
1038 l_inventory_item_id number;
1039 begin
1040 if p_inv_organization_id is not null then
1041 l_organization_id := p_inv_organization_id;
1042 else
1043 l_organization_id := p_org_id;
1044 end if;
1045
1046 begin
1047 select nvl(cia.inventory_item_id,0)
1048 into l_inventory_item_id
1049 from cs_incidents_all_b cia
1050 where cia.incident_id = p_service_request_id;
1051 exception
1052 when no_data_found then
1053 null;
1054 end;
1055
1056 if l_inventory_item_id is not null then
1057 select concatenated_segments
1058 into l_item_name
1059 from mtl_system_items_kfv msi
1060 where organization_id = l_organization_id
1061 and inventory_item_id = l_inventory_item_id;
1062 end if;
1063
1064 return l_item_name;
1065
1066
1067 end get_item_name;
1068
1069 -- Following new functions added by lllin for 11.5.10
1070
1071
1072 -- This function validates an asset group, asset activity, or
1073 -- rebuildable item. p_eam_item_type indicates the type of item being
1074 -- validated. Asset group: 1; Asset activity: 2; Rebuildable item: 3.
1075 FUNCTION validate_inventory_item_id
1076 (
1077 p_organization_id in number,
1078 p_inventory_item_id in number,
1079 p_eam_item_type in number
1080 ) return boolean is
1081 l_count number;
1082 begin
1083 select count(*) into l_count
1084 from mtl_system_items
1085 where inventory_item_id=p_inventory_item_id
1086 and organization_id=p_organization_id
1087 and eam_item_type=p_eam_item_type;
1088
1089 if (l_count>0) then
1090 return true;
1091 else
1092 return false;
1093 end if;
1094 end validate_inventory_item_id;
1095
1096
1097 -- This function validates an asset number or serialized rebuildable.
1098 -- p_eam_item_type indicates the type of serial number being validated.
1099 -- Asset group: 1; Asset activity: 2; Rebuildable item: 3.
1100 FUNCTION validate_serial_number
1101 (
1102 p_organization_id in number,
1103 p_inventory_item_id in number,
1104 p_serial_number in varchar2,
1105 p_eam_item_type in number:=1
1106 ) return boolean is
1107 l_count number;
1108 begin
1109 select count(*) into l_count
1110 from csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
1111 where cii.last_vld_organization_id=mp.organization_id
1112 and msi.organization_id = cii.last_vld_organization_id
1113 and mp.maint_organization_id = p_organization_id
1114 and cii.inventory_item_id=p_inventory_item_id
1115 and cii.serial_number=p_serial_number
1116 and cii.inventory_item_id=msi.inventory_item_id
1117 and msi.eam_item_type=p_eam_item_type;
1118
1119 if (l_count>0) then
1120 return true;
1121 else
1122 return false;
1123 end if;
1124 end;
1125
1126
1127 -- This function validates the boolean flags.
1128 -- A boolean flag has to be either 'Y' or 'N'.
1129 FUNCTION validate_boolean_flag
1130 (
1131 p_flag in varchar2
1132 ) return boolean is
1133 begin
1134 if (p_flag <> 'Y') and (p_flag <> 'N') then
1135 return false;
1136 else
1137 return true;
1138 end if;
1139 end;
1140
1141
1142 -- Following function validates department id in bom_departments table.
1143 FUNCTION validate_department_id
1144 (
1145 p_department_id in number,
1146 p_organization_id in number
1147 ) return boolean is
1148 l_count number;
1149 begin
1150 select count(*) into l_count
1151 from bom_departments
1152 where department_id=p_department_id
1153 and organization_id=p_organization_id;
1154
1155 if (l_count>0) then
1156 return true;
1157 else
1158 return false;
1159 end if;
1160 end;
1161
1162 -- Validates eam location id in mtl_eam_locations table.
1163 FUNCTION validate_eam_location_id
1164 (
1165 p_location_id in number
1166 ) return boolean
1167 is
1168 l_count number;
1169 begin
1170 select count(*) into l_count
1171 from mtl_eam_locations
1172 where location_id=p_location_id;
1173
1174 if (l_count>0) then
1175 return true;
1176 else
1177 return false;
1178 end if;
1179 end;
1180
1181
1182 -- The following function should NOT be called for rebuilds.
1183 -- This function validates the eam location for an asset.
1184 -- The location has to exist, and its organization_id has to
1185 -- the same as the current_organization_id of the serial number.
1186 FUNCTION validate_eam_location_id_asset
1187 (
1188 p_organization_id in number, -- use organization id, not creation org id
1189 p_location_id in number
1190 ) return boolean
1191 is
1192 l_count number;
1193 begin
1194 select count(*) into l_count
1195 from mtl_eam_locations
1196 where organization_id=p_organization_id
1197 and location_id=p_location_id
1198 and (END_DATE >= SYSDATE OR END_DATE IS NULL);
1199
1200 if (l_count>0) then
1201 return true;
1202 else
1203 return false;
1204 end if;
1205 end;
1206
1207 FUNCTION validate_wip_acct_class_code
1208 (
1209 p_organization_id in number,
1210 p_wip_accounting_class_code in varchar2
1211 ) return boolean
1212 is
1213 l_count number;
1214 begin
1215 select count(*) into l_count
1216 from WIP_ACCOUNTING_CLASSES
1217 where class_code = p_wip_accounting_class_code
1218 and organization_id = p_organization_id
1219 and class_type = 6; -- WIP_CLASS_TYPE=Maintenance Accounting Class
1220
1221 if (l_count>0) then
1222 return true;
1223 else
1224 return false;
1225 end if;
1226 end;
1227
1228
1229 FUNCTION validate_meter_id
1230 (
1231 p_meter_id in number,
1232 p_tmpl_flag in varchar2:=null
1233 ) return boolean
1234 is
1235 l_count number;
1236 begin
1237 if (p_tmpl_flag is null) then
1238 select count(*) into l_count
1239 from csi_counters_b
1240 where counter_id=p_meter_id;
1241 elsif (p_tmpl_flag='N') then
1242 select count(*) into l_count
1243 from csi_counters_b
1244 where counter_id=p_meter_id;
1245 elsif (p_tmpl_flag='Y') then
1246 select count(*) into l_count
1247 from csi_counter_template_b
1248 where counter_id=p_meter_id;
1249 else
1250 l_count:=0;
1251 end if;
1252
1253 if (l_count>0) then
1254 return true;
1255 else
1256 return false;
1257 end if;
1258 end;
1259
1260
1261 function validate_desc_flex_field
1262 (
1263 p_app_short_name IN VARCHAR:='EAM',
1264 p_desc_flex_name IN VARCHAR,
1265 p_ATTRIBUTE_CATEGORY IN VARCHAR2 default null,
1266 p_ATTRIBUTE1 IN VARCHAR2 default null,
1267 p_ATTRIBUTE2 IN VARCHAR2 default null,
1268 p_ATTRIBUTE3 IN VARCHAR2 default null,
1269 p_ATTRIBUTE4 IN VARCHAR2 default null,
1270 p_ATTRIBUTE5 IN VARCHAR2 default null,
1271 p_ATTRIBUTE6 IN VARCHAR2 default null,
1272 p_ATTRIBUTE7 IN VARCHAR2 default null,
1273 p_ATTRIBUTE8 IN VARCHAR2 default null,
1274 p_ATTRIBUTE9 IN VARCHAR2 default null,
1275 p_ATTRIBUTE10 IN VARCHAR2 default null,
1276 p_ATTRIBUTE11 IN VARCHAR2 default null,
1277 p_ATTRIBUTE12 IN VARCHAR2 default null,
1278 p_ATTRIBUTE13 IN VARCHAR2 default null,
1279 p_ATTRIBUTE14 IN VARCHAR2 default null,
1280 p_ATTRIBUTE15 IN VARCHAR2 default null,
1281 x_error_segments OUT NOCOPY NUMBER,
1282 x_error_message OUT NOCOPY VARCHAR2
1283 )
1284 return boolean
1285 is
1286 l_validated boolean;
1287 begin
1288 x_error_segments:=null;
1289 x_error_message:=null;
1290
1291 FND_FLEX_DESCVAL.set_context_value(p_attribute_category);
1292 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_ATTRIBUTE1);
1293 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_ATTRIBUTE2);
1294 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_ATTRIBUTE3);
1295 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_ATTRIBUTE4);
1296 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_ATTRIBUTE5);
1297 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_ATTRIBUTE6);
1298 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_ATTRIBUTE7);
1299 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_ATTRIBUTE8);
1300 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_ATTRIBUTE9);
1301 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_ATTRIBUTE10);
1302 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_ATTRIBUTE11);
1303 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_ATTRIBUTE12);
1304 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_ATTRIBUTE13);
1305 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_ATTRIBUTE14);
1306 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_ATTRIBUTE15);
1307
1308 l_validated:= FND_FLEX_DESCVAL.validate_desccols(
1309 p_app_short_name,
1310 p_desc_flex_name,
1311 'I',
1312 sysdate ) ;
1313
1314 if (l_validated) then
1315 return true;
1316 else
1317 x_error_segments:=FND_FLEX_DESCVAL.error_segment;
1318 x_error_message:=fnd_flex_descval.error_message;
1319 return false;
1320 end if;
1321 end validate_desc_flex_field;
1322
1323
1324 FUNCTION validate_mfg_lookup_code
1325 (p_lookup_type in VARCHAR2,
1326 p_lookup_code in NUMBER)
1327 return boolean IS
1328 l_count number;
1329 begin
1330 select count(*) into l_count
1331 from mfg_lookups
1332 where
1333 lookup_type=p_lookup_type and
1334 lookup_code=p_lookup_code;
1335
1336 if (l_count > 0) then
1337 return true;
1338 else
1339 return false;
1340 end if;
1341 end validate_mfg_lookup_code;
1342
1343 -- Validates that the maintained object type and id represent a valid
1344 -- maintained object.
1345
1346 FUNCTION validate_maintained_object_id
1347 (p_maintenance_object_type in NUMBER,
1348 p_maintenance_object_id in NUMBER,
1349 p_organization_id in number default null,
1350 p_eam_item_type in number
1351 )
1352 return boolean
1353 is
1354 l_count number;
1355 begin
1356 if (p_maintenance_object_type=3) then
1357 /* IMPORTANT: This validation only holds true for EAM work orders. CMRO work orders
1358 cannot use this validation. Since this API would be invoked only
1359 from EAM UIs, I'm not specifically testing for the type of work order
1360 this row may represent */
1361
1362 select count(*) into l_count
1363 from csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
1364 where
1365 msi.organization_id=cii.last_vld_organization_id and
1366 msi.inventory_item_id=cii.inventory_item_id and
1367 cii.instance_id = p_maintenance_object_id and
1368 msi.eam_item_type=p_eam_item_type;
1369
1370 if (l_count > 0) then
1371 return true;
1372 else
1373 return false;
1374 end if;
1375
1376 elsif (p_maintenance_object_type=2) then
1377 if (p_organization_id is null) then
1378 return false;
1379 end if;
1380
1381 select count(*) into l_count
1382 from mtl_system_items msi, mtl_parameters mp
1383 where
1384 msi.inventory_item_id=p_maintenance_object_id
1385 and msi.eam_item_type=p_eam_item_type
1386 and msi.organization_id=mp.organization_id
1387 and mp.maint_organization_id = p_organization_id;
1388
1389 if (l_count > 0) then
1390 return true;
1391 else
1392 return false;
1393 end if;
1394 else
1395 return false;
1396 end if;
1397 end validate_maintained_object_id;
1398
1399
1400 -- Validates that the combination (Organization_id, inventory_item_id, and
1401 -- serial number) and the combination (maintained_object_type and
1402 -- maintained_object_id) represent the same valid maintained object.
1403
1404 FUNCTION validate_maintained_object
1405 (p_organization_id in NUMBER,
1406 p_inventory_item_id in NUMBER,
1407 p_serial_number in VARCHAR2 default null,
1408 p_maintenance_object_type in NUMBER,
1409 p_maintenance_object_id in NUMBER,
1410 p_eam_item_type in number)
1411 return boolean
1412 is
1413 l_organization_id number;
1414 l_inventory_item_id number;
1415 l_serial_number varchar2(30);
1416 begin
1417 if (p_maintenance_object_type=1) then
1418 select msn.current_organization_id,
1419 msn.inventory_item_id,
1420 msn.serial_number
1421 into l_organization_id, l_inventory_item_id, l_serial_number
1422 from mtl_serial_numbers msn, mtl_system_items msi
1423 where
1424 msn.gen_object_id=p_maintenance_object_id and
1425 msi.inventory_item_id=msn.inventory_item_id and
1426 msi.organization_id=msn.current_organization_id and
1427 msi.eam_item_type=p_eam_item_type;
1428
1429 if (l_organization_id=p_organization_id and
1430 l_inventory_item_id=p_inventory_item_id and
1431 l_serial_number=p_serial_number) then
1432 return true;
1433 else
1434 return false;
1435 end if;
1436 elsif (p_maintenance_object_type=2) then
1437 select organization_id,
1438 inventory_item_id
1439 into l_organization_id, l_inventory_item_id
1440 from mtl_system_items
1441 where
1442 organization_id=p_organization_id and
1443 inventory_item_id=p_maintenance_object_id
1444 and eam_item_type=p_eam_item_type;
1445
1446 if (l_organization_id=p_organization_id and
1447 l_inventory_item_id=p_inventory_item_id and
1448 p_serial_number is null) then
1449 return true;
1450 else
1451 return false;
1452 end if;
1453 else
1454 return false;
1455 end if;
1456
1457 exception
1458 when no_data_found then
1459 return false;
1460
1461 end validate_maintained_object;
1462
1463
1464 procedure translate_asset_maint_obj
1465 (p_organization_id in number,
1466 p_inventory_item_id in number,
1467 p_serial_number in varchar2 default null,
1468 x_object_found out nocopy boolean,
1469 x_maintenance_object_type out nocopy number,
1470 x_maintenance_object_id out nocopy number)
1471 is
1472 begin
1473 x_object_found:=true;
1474
1475 if (p_serial_number is not null) then
1476 select instance_id
1477 into x_maintenance_object_id
1478 from csi_item_instances
1479 where inventory_item_id=p_inventory_item_id
1480 and serial_number=p_serial_number;
1481
1482 x_maintenance_object_type:=3;
1483 else
1484 select inventory_item_id
1485 into x_maintenance_object_id
1486 from mtl_system_items
1487 where inventory_item_id=p_inventory_item_id
1488 and eam_item_type in (1,3)
1489 and rownum = 1;
1490
1491 x_maintenance_object_type:=2;
1492 end if;
1493
1494 exception
1495 when no_data_found then
1496 x_object_found:=false;
1497 --dbms_output.put_line('no data found');
1498 end translate_asset_maint_obj;
1499
1500
1501 procedure translate_maint_obj_asset
1502 (p_maintenance_object_type in number,
1503 p_maintenance_object_id in number,
1504 p_organization_id in number default null,
1505 x_object_found out nocopy boolean,
1506 x_organization_id out nocopy number,
1507 x_inventory_item_id out nocopy number,
1508 x_serial_number out nocopy varchar2
1509 )
1510 is
1511 begin
1512 x_object_found:=true;
1513
1514 if (p_maintenance_object_type=3) then
1515 SELECT mp.maint_organization_id, cii.inventory_item_id, cii.serial_number
1516 INTO x_organization_id, x_inventory_item_id, x_serial_number
1517 FROM csi_item_instances cii, mtl_parameters mp
1518 WHERE cii.instance_id=p_maintenance_object_id
1519 AND cii.last_vld_organization_id = mp.organization_id;
1520 elsif (p_maintenance_object_type=2) then
1521 select inventory_item_id
1522 into x_inventory_item_id
1523 from mtl_system_items
1524 where inventory_item_id=p_maintenance_object_id
1525 and eam_item_type in (1,3)
1526 and rownum = 1;
1527 x_serial_number:=null;
1528 x_organization_id := p_organization_id;
1529 end if;
1530
1531 exception
1532 when no_data_found then
1533 x_object_found:=false;
1534 end translate_maint_obj_asset;
1535
1536 /* ----------------------------------------------------------------------------------------------
1537 -- Procedure to get the sum of today's work, overdue work and open work in Maintenance
1538 -- Engineer's Workbench
1539 -- Author : amondal, Aug '03
1540 ------------------------------------------------------------------------------------------------*/
1541
1542
1543
1544 PROCEDURE get_work_order_count (
1545 p_api_version IN NUMBER
1546 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1547 ,p_commit IN VARCHAR2 := fnd_api.g_false
1548 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1549 ,p_organization_id IN VARCHAR2
1550 ,p_employee_id IN VARCHAR2
1551 ,p_instance_id IN NUMBER
1552 ,p_asset_group_id IN NUMBER
1553 ,p_department_id IN NUMBER
1554 ,p_resource_id IN NUMBER
1555 ,p_current_date IN VARCHAR2
1556 ,x_todays_work OUT NOCOPY VARCHAR2
1557 ,x_overdue_work OUT NOCOPY VARCHAR2
1558 ,x_open_work OUT NOCOPY VARCHAR2
1559 ,x_todays_work_duration OUT NOCOPY VARCHAR2
1560 ,x_overdue_work_duration OUT NOCOPY VARCHAR2
1561 ,x_open_work_duration OUT NOCOPY VARCHAR2
1562 ,x_current_date OUT NOCOPY VARCHAR2
1563 ,x_current_time OUT NOCOPY VARCHAR2
1564 ,x_return_status OUT NOCOPY VARCHAR2
1565 ,x_msg_count OUT NOCOPY NUMBER
1566 ,x_msg_data OUT NOCOPY VARCHAR2)
1567
1568 IS
1569 l_api_name CONSTANT VARCHAR2(30) := 'get_work_order_count';
1570 l_api_version CONSTANT NUMBER := 1.0;
1571 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1572 l_organization_id NUMBER;
1573 l_stmt_num NUMBER;
1574 l_count NUMBER;
1575 l_success VARCHAR2(1);
1576 l_todays_work NUMBER;
1577 l_overdue_work NUMBER;
1578 l_open_work NUMBER;
1579 l_total_work NUMBER;
1580 l_todays_work_duration NUMBER;
1581 l_overdue_work_duration NUMBER;
1582 l_open_work_duration NUMBER;
1583 l_total_work_duration NUMBER;
1584 l_current_date VARCHAR2(100);
1585 l_current_time VARCHAR2(100);
1586 l_maint_supervisor_mode NUMBER;
1587 BEGIN
1588 -- Standard Start of API savepoint
1589 l_stmt_num := 10;
1590 SAVEPOINT get_work_order_count_pvt;
1591
1592 l_stmt_num := 20;
1593 -- Standard call to check for call compatibility.
1594 IF NOT fnd_api.compatible_api_call(
1595 l_api_version
1596 ,p_api_version
1597 ,l_api_name
1598 ,g_pkg_name) THEN
1599 RAISE fnd_api.g_exc_unexpected_error;
1600 END IF;
1601
1602 l_stmt_num := 30;
1603 -- Initialize message list if p_init_msg_list is set to TRUE.
1604 IF fnd_api.to_boolean(p_init_msg_list) THEN
1605 fnd_msg_pub.initialize;
1606 END IF;
1607
1608 l_stmt_num := 40;
1609 -- Initialize API return status to success
1610 x_return_status := fnd_api.g_ret_sts_success;
1611
1612 l_stmt_num := 50;
1613
1614 -- API body
1615
1616 l_maint_supervisor_mode := FND_PROFILE.VALUE('EAM_MAINTENANCE_SUPERVISOR');
1617 IF l_maint_supervisor_mode = 2 THEN -- Maintenance Engineer
1618 -- Count of Today's work orders
1619
1620 l_current_date := substr(p_current_date,1,19); -- format of date is 'yyyy-mm-dd HH24:mi:ss'
1621 l_current_time := substr(p_current_date,12);
1622
1623
1624 SELECT count(*) ,
1625 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1626 INTO l_todays_work,
1627 l_todays_work_duration
1628 FROM wip_entities we,
1629 wip_discrete_jobs wdj,
1630 wip_operations wo,
1631 wip_operation_resources wor,
1632 wip_op_resource_instances wori,
1633 bom_resource_employees bre,
1634 (SELECT wip_entity_id,
1635 operation_seq_num,
1636 resource_seq_num,
1637 organization_id,
1638 instance_id,
1639 SUM(completion_date - start_date) usage
1640 FROM wip_operation_resource_usage
1641 GROUP BY wip_entity_id,
1642 operation_seq_num,
1643 resource_seq_num,
1644 organization_id,
1645 instance_id) res
1646 WHERE wdj.wip_entity_id = we.wip_entity_id
1647 AND wdj.organization_id = we.organization_id
1648 AND we.organization_id = wo.organization_id
1649 AND we.wip_entity_id = wo.wip_entity_id
1650 AND wo.organization_id = wor.organization_id
1651 AND wo.wip_entity_id = wor.wip_entity_id
1652 AND wo.operation_seq_num = wor.operation_seq_num
1653 AND wor.organization_id = wori.organization_id
1654 AND wor.wip_entity_id = wori.wip_entity_id
1655 AND wor.operation_seq_num = wori.operation_seq_num
1656 AND wor.resource_seq_num = wori.resource_seq_num
1657 AND wori.serial_number IS NULL
1658 AND wori.instance_id = bre.instance_id
1659 AND wor.organization_id = bre.organization_id
1660 AND wor.resource_id = bre.resource_id
1661 AND sysdate >= bre.effective_start_date
1662 AND sysdate <= bre.effective_end_date
1663 AND wori.organization_id = res.organization_id (+)
1664 AND wori.wip_entity_id = res.wip_entity_id (+)
1665 AND wori.operation_seq_num = res.operation_seq_num (+)
1666 AND wori.resource_seq_num = res.resource_seq_num (+)
1667 AND wori.instance_id = res.instance_id (+)
1668 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1669 AND we.entity_type = 6
1670 AND wdj.status_type = 3
1671 AND bre.organization_id = p_organization_id
1672 AND bre.person_id = p_employee_id
1673 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);
1674
1675 x_todays_work := to_char(l_todays_work);
1676 x_todays_work_duration := to_char(l_todays_work_duration);
1677
1678 -- Count of Overdue Work
1679
1680 SELECT count(*) ,
1681 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1682 INTO l_overdue_work,
1683 l_overdue_work_duration
1684 FROM wip_entities we,
1685 wip_discrete_jobs wdj,
1686 wip_operations wo,
1687 wip_operation_resources wor,
1688 wip_op_resource_instances wori,
1689 bom_resource_employees bre,
1690 (SELECT wip_entity_id,
1691 operation_seq_num,
1692 resource_seq_num,
1693 organization_id,
1694 instance_id,
1695 SUM(completion_date - start_date) usage
1696 FROM wip_operation_resource_usage
1697 GROUP BY wip_entity_id,
1698 operation_seq_num,
1699 resource_seq_num,
1700 organization_id,
1701 instance_id) res
1702 WHERE wdj.wip_entity_id = we.wip_entity_id
1703 AND wdj.organization_id = we.organization_id
1704 AND we.organization_id = wo.organization_id
1705 AND we.wip_entity_id = wo.wip_entity_id
1706 AND wo.organization_id = wor.organization_id
1707 AND wo.wip_entity_id = wor.wip_entity_id
1708 AND wo.operation_seq_num = wor.operation_seq_num
1709 AND wor.organization_id = wori.organization_id
1710 AND wor.wip_entity_id = wori.wip_entity_id
1711 AND wor.operation_seq_num = wori.operation_seq_num
1712 AND wor.resource_seq_num = wori.resource_seq_num
1713 AND wori.serial_number IS NULL
1714 AND wori.instance_id = bre.instance_id
1715 AND wor.organization_id = bre.organization_id
1716 AND wor.resource_id = bre.resource_id
1717 AND sysdate >= bre.effective_start_date
1718 AND sysdate <= bre.effective_end_date
1719 AND wori.organization_id = res.organization_id (+)
1720 AND wori.wip_entity_id = res.wip_entity_id (+)
1721 AND wori.operation_seq_num = res.operation_seq_num (+)
1722 AND wori.resource_seq_num = res.resource_seq_num (+)
1723 AND wori.instance_id = res.instance_id (+)
1724 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1725 AND we.entity_type = 6
1726 AND wdj.status_type = 3
1727 AND bre.organization_id = p_organization_id
1728 AND bre.person_id = p_employee_id
1729 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');
1730
1731 x_overdue_work := l_overdue_work;
1732 x_overdue_work_duration := l_overdue_work_duration;
1733
1734
1735 -- Count of Open Work
1736
1737 SELECT count(*) ,
1738 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1739 INTO l_open_work,
1740 l_open_work_duration
1741 FROM wip_entities we,
1742 wip_discrete_jobs wdj,
1743 wip_operations wo,
1744 wip_operation_resources wor,
1745 wip_op_resource_instances wori,
1746 bom_resource_employees bre,
1747 (SELECT wip_entity_id,
1748 operation_seq_num,
1749 resource_seq_num,
1750 organization_id,
1751 instance_id,
1752 SUM(completion_date - start_date) usage
1753 FROM wip_operation_resource_usage
1754 GROUP BY wip_entity_id,
1755 operation_seq_num,
1756 resource_seq_num,
1757 organization_id,
1758 instance_id) res
1759 WHERE wdj.wip_entity_id = we.wip_entity_id
1760 AND wdj.organization_id = we.organization_id
1761 AND we.organization_id = wo.organization_id
1762 AND we.wip_entity_id = wo.wip_entity_id
1763 AND wo.organization_id = wor.organization_id
1764 AND wo.wip_entity_id = wor.wip_entity_id
1765 AND wo.operation_seq_num = wor.operation_seq_num
1766 AND wor.organization_id = wori.organization_id
1767 AND wor.wip_entity_id = wori.wip_entity_id
1768 AND wor.operation_seq_num = wori.operation_seq_num
1769 AND wor.resource_seq_num = wori.resource_seq_num
1770 AND wori.serial_number IS NULL
1771 AND wori.instance_id = bre.instance_id
1772 AND wor.organization_id = bre.organization_id
1773 AND wor.resource_id = bre.resource_id
1774 AND sysdate >= bre.effective_start_date
1775 AND sysdate <= bre.effective_end_date
1776 AND wori.organization_id = res.organization_id (+)
1777 AND wori.wip_entity_id = res.wip_entity_id (+)
1778 AND wori.operation_seq_num = res.operation_seq_num (+)
1779 AND wori.resource_seq_num = res.resource_seq_num (+)
1780 AND wori.instance_id = res.instance_id (+)
1781 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1782 AND we.entity_type = 6
1783 AND wdj.status_type = 3
1784 AND bre.organization_id = p_organization_id
1785 AND bre.person_id = p_employee_id;
1786
1787 x_open_work := l_open_work;
1788 x_open_work_duration := l_open_work_duration;
1789
1790 ELSE -- Maintenance Supervisor
1791 -- Count of Today's work orders
1792
1793 l_current_date := substr(p_current_date,1,19); -- format of date is 'yyyy-mm-dd HH24:mi:ss'
1794 l_current_time := substr(p_current_date,12);
1795
1796 SELECT count(*) ,
1797 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1798 INTO l_todays_work, l_todays_work_duration
1799 FROM wip_entities we,
1800 wip_discrete_jobs wdj,
1801 wip_operations wo,
1802 wip_operation_resources wor,
1803 bom_resources br,
1804 (SELECT wip_entity_id,
1805 operation_seq_num,
1806 resource_seq_num,
1807 organization_id,
1808 instance_id,
1809 SUM(completion_date - start_date) usage
1810 FROM wip_operation_resource_usage woru
1811 WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
1812 (SELECT 1
1813 FROM wip_op_resource_instances wori
1814 WHERE woru.wip_entity_id = wori.wip_entity_id
1815 AND woru.operation_seq_num = wori.operation_seq_num
1816 AND woru.resource_seq_num = wori.resource_seq_num
1817 )
1818 )
1819 GROUP BY wip_entity_id,
1820 operation_seq_num,
1821 resource_seq_num,
1822 organization_id,
1823 instance_id) res
1824 WHERE wdj.wip_entity_id = we.wip_entity_id
1825 AND wdj.organization_id = we.organization_id
1826 AND we.organization_id = wo.organization_id
1827 AND we.wip_entity_id = wo.wip_entity_id
1828 AND wo.organization_id = wor.organization_id
1829 AND wo.wip_entity_id = wor.wip_entity_id
1830 AND wo.operation_seq_num = wor.operation_seq_num
1831 AND wor.organization_id = res.organization_id (+)
1832 AND wor.wip_entity_id = res.wip_entity_id (+)
1833 AND wor.operation_seq_num = res.operation_seq_num (+)
1834 AND wor.resource_seq_num = res.resource_seq_num (+)
1835 AND wor.resource_id = br.resource_id
1836 AND wor.organization_id = br.organization_id
1837 AND br.resource_type = 2
1838 AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
1839 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1840 AND we.entity_type = 6
1841 AND wdj.status_type = 3
1842 AND we.organization_id = p_organization_id
1843 AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
1844 AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
1845 AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
1846 AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
1847 AND ( (p_department_id IS NOT NULL)
1848 OR EXISTS
1849 (
1850 SELECT 1
1851 FROM bom_resource_employees bre,
1852 bom_dept_res_instances bdri,
1853 bom_departments bd
1854 WHERE bre.person_id = p_employee_id
1855 AND bre.effective_start_date <= sysdate
1856 AND bre.effective_end_date >= sysdate
1857 AND bre.resource_id = bdri.resource_id
1858 AND bre.instance_id = bdri.instance_id
1859 AND bdri.department_id = bd.department_id
1860 AND bre.organization_id = bd.organization_id
1861 AND bre.organization_id = p_organization_id
1862 AND bd.department_id = wo.department_id
1863 )
1864 )
1865 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);
1866
1867
1868 x_todays_work := to_char(l_todays_work);
1869 x_todays_work_duration := to_char(l_todays_work_duration);
1870
1871 -- Count of Overdue Work
1872
1873 SELECT count(*) ,
1874 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1875 INTO l_overdue_work, l_overdue_work_duration
1876 FROM wip_entities we,
1877 wip_discrete_jobs wdj,
1878 wip_operations wo,
1879 wip_operation_resources wor,
1880 bom_resources br,
1881 (SELECT wip_entity_id,
1882 operation_seq_num,
1883 resource_seq_num,
1884 organization_id,
1885 instance_id,
1886 SUM(completion_date - start_date) usage
1887 FROM wip_operation_resource_usage woru
1888 WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
1889 (SELECT 1
1890 FROM wip_op_resource_instances wori
1891 WHERE woru.wip_entity_id = wori.wip_entity_id
1892 AND woru.operation_seq_num = wori.operation_seq_num
1893 AND woru.resource_seq_num = wori.resource_seq_num
1894 )
1895 )
1896 GROUP BY wip_entity_id,
1897 operation_seq_num,
1898 resource_seq_num,
1899 organization_id,
1900 instance_id) res
1901 WHERE wdj.wip_entity_id = we.wip_entity_id
1902 AND wdj.organization_id = we.organization_id
1903 AND we.organization_id = wo.organization_id
1904 AND we.wip_entity_id = wo.wip_entity_id
1905 AND wo.organization_id = wor.organization_id
1906 AND wo.wip_entity_id = wor.wip_entity_id
1907 AND wo.operation_seq_num = wor.operation_seq_num
1908 AND wor.organization_id = res.organization_id (+)
1909 AND wor.wip_entity_id = res.wip_entity_id (+)
1910 AND wor.operation_seq_num = res.operation_seq_num (+)
1911 AND wor.resource_seq_num = res.resource_seq_num (+)
1912 AND wor.resource_id = br.resource_id
1913 AND wor.organization_id = br.organization_id
1914 AND br.resource_type = 2
1915 AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
1916 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1917 AND we.entity_type = 6
1918 AND wdj.status_type = 3
1919 AND we.organization_id = p_organization_id
1920 AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
1921 AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
1922 AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
1923 AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
1924 AND ( (p_department_id IS NOT NULL)
1925 OR EXISTS
1926 (
1927 SELECT 1
1928 FROM bom_resource_employees bre,
1929 bom_dept_res_instances bdri,
1930 bom_departments bd
1931 WHERE bre.person_id = p_employee_id
1932 AND bre.effective_start_date <= sysdate
1933 AND bre.effective_end_date >= sysdate
1934 AND bre.resource_id = bdri.resource_id
1935 AND bre.instance_id = bdri.instance_id
1936 AND bdri.department_id = bd.department_id
1937 AND bre.organization_id = bd.organization_id
1938 AND bre.organization_id = p_organization_id
1939 AND bd.department_id = wo.department_id
1940 )
1941 )
1942 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');
1943
1944 x_overdue_work := l_overdue_work;
1945 x_overdue_work_duration := l_overdue_work_duration;
1946
1947 -- Count of Open Work
1948
1949 SELECT count(*) ,
1950 decode(SUM(res.usage), null,round(nvl(SUM(wor.completion_date - wor.start_date)*24,0),1),ROUND(SUM(res.usage)*24,1))
1951 INTO l_open_work, l_open_work_duration
1952 FROM wip_entities we,
1953 wip_discrete_jobs wdj,
1954 wip_operations wo,
1955 wip_operation_resources wor,
1956 bom_resources br,
1957 (SELECT wip_entity_id,
1958 operation_seq_num,
1959 resource_seq_num,
1960 organization_id,
1961 instance_id,
1962 SUM(completion_date - start_date) usage
1963 FROM wip_operation_resource_usage woru
1964 WHERE (woru.instance_id IS NOT NULL OR NOT EXISTS
1965 (SELECT 1
1966 FROM wip_op_resource_instances wori
1967 WHERE woru.wip_entity_id = wori.wip_entity_id
1968 AND woru.operation_seq_num = wori.operation_seq_num
1969 AND woru.resource_seq_num = wori.resource_seq_num
1970 )
1971 )
1972 GROUP BY wip_entity_id,
1973 operation_seq_num,
1974 resource_seq_num,
1975 organization_id,
1976 instance_id) res
1977 WHERE wdj.wip_entity_id = we.wip_entity_id
1978 AND wdj.organization_id = we.organization_id
1979 AND we.organization_id = wo.organization_id
1980 AND we.wip_entity_id = wo.wip_entity_id
1981 AND wo.organization_id = wor.organization_id
1982 AND wo.wip_entity_id = wor.wip_entity_id
1983 AND wo.operation_seq_num = wor.operation_seq_num
1984 AND wor.organization_id = res.organization_id (+)
1985 AND wor.wip_entity_id = res.wip_entity_id (+)
1986 AND wor.operation_seq_num = res.operation_seq_num (+)
1987 AND wor.resource_seq_num = res.resource_seq_num (+)
1988 AND wor.resource_id = br.resource_id
1989 AND wor.organization_id = br.organization_id
1990 AND br.resource_type = 2
1991 AND (br.disable_date IS NULL OR br.disable_date >= sysdate)
1992 AND ( wo.operation_completed IS NULL or wo.operation_completed = 'N')
1993 AND we.entity_type = 6
1994 AND wdj.status_type = 3
1995 AND we.organization_id = p_organization_id
1996 AND ( p_instance_id IS NULL OR (wdj.maintenance_object_type=3 AND wdj.maintenance_object_id = p_instance_id ))
1997 AND ( p_asset_group_id IS NULL OR NVL(wdj.rebuild_item_id,wdj.asset_group_id) = p_asset_group_id )
1998 AND ( p_department_id IS NULL OR wo.department_id = p_department_id )
1999 AND ( p_resource_id IS NULL OR wor.resource_id = p_resource_id )
2000 AND ( (p_department_id IS NOT NULL)
2001 OR EXISTS
2002 (
2003 SELECT 1
2004 FROM bom_resource_employees bre,
2005 bom_dept_res_instances bdri,
2006 bom_departments bd
2007 WHERE bre.person_id = p_employee_id
2008 AND bre.effective_start_date <= sysdate
2009 AND bre.effective_end_date >= sysdate
2010 AND bre.resource_id = bdri.resource_id
2011 AND bre.instance_id = bdri.instance_id
2012 AND bdri.department_id = bd.department_id
2013 AND bre.organization_id = bd.organization_id
2014 AND bre.organization_id = p_organization_id
2015 AND bd.department_id = wo.department_id
2016 )
2017 ) ;
2018
2019
2020 x_open_work := l_open_work;
2021 x_open_work_duration := l_open_work_duration;
2022
2023 END IF;
2024
2025 -- Bug #3449283 to get the date in format 'yyyy-mm-dd'
2026 l_current_date:= substr(l_current_date,1,10);
2027 x_current_date := to_char(to_date(l_current_date,'yyyy-mm-dd'));
2028
2029 x_current_time := l_current_time;
2030
2031 l_stmt_num := 998;
2032 -- End of API body.
2033 -- Standard check of p_commit.
2034 IF fnd_api.to_boolean(p_commit) THEN
2035 COMMIT WORK;
2036 END IF;
2037
2038 l_stmt_num := 999;
2039 -- Standard call to get message count and if count is 1, get message info.
2040 fnd_msg_pub.count_and_get(
2041 p_encoded => fnd_api.g_false
2042 ,p_count => x_msg_count
2043 ,p_data => x_msg_data);
2044
2045 EXCEPTION
2046 WHEN fnd_api.g_exc_error THEN
2047 ROLLBACK TO get_work_order_count_pvt;
2048 x_return_status := fnd_api.g_ret_sts_error;
2049 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2050 fnd_msg_pub.count_and_get(
2051 p_encoded => fnd_api.g_false
2052 ,p_count => x_msg_count
2053 ,p_data => x_msg_data);
2054 WHEN fnd_api.g_exc_unexpected_error THEN
2055 ROLLBACK TO get_work_order_count_pvt;
2056 x_return_status := fnd_api.g_ret_sts_unexp_error;
2057 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2058 fnd_msg_pub.count_and_get(
2059 p_encoded => fnd_api.g_false
2060 ,p_count => x_msg_count
2061 ,p_data => x_msg_data);
2062 WHEN OTHERS THEN
2063 ROLLBACK TO get_work_order_count_pvt;
2064 x_return_status := fnd_api.g_ret_sts_unexp_error;
2065 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2066 IF fnd_msg_pub.check_msg_level(
2067 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2068 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2069 END IF;
2070
2071 fnd_msg_pub.count_and_get(
2072 p_encoded => fnd_api.g_false
2073 ,p_count => x_msg_count
2074 ,p_data => x_msg_data);
2075
2076
2077 END get_work_order_count;
2078
2079
2080 PROCEDURE insert_into_wori (
2081 p_api_version IN NUMBER
2082 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2083 ,p_commit IN VARCHAR2 := fnd_api.g_false
2084 ,p_organization_id IN VARCHAR2
2085 ,p_employee_id IN VARCHAR2
2086 ,p_wip_entity_id IN VARCHAR2
2087 ,p_operation_seq_num IN VARCHAR2
2088 ,p_resource_seq_num IN VARCHAR2
2089 ,p_resource_id IN VARCHAR2
2090 ,x_return_status OUT NOCOPY VARCHAR2
2091 ,x_msg_count OUT NOCOPY NUMBER
2092 ,x_msg_data OUT NOCOPY VARCHAR2
2093 ,x_wip_entity_name OUT NOCOPY VARCHAR2)
2094
2095 IS
2096
2097 -- Input Tables
2098
2099 l_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
2100 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2101 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2102 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2103 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2104 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2105 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2106 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2107 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2108
2109 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
2110 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
2111 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2112 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2113 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2114 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2115 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2116 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2117 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2118
2119 -- Output Tables
2120
2121 x_out_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
2122 x_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2123 x_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2124 x_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2125 x_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2126 x_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2127 x_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2128 x_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2129 x_out_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2130
2131 x_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
2132 x_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2133 x_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2134 x_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2135 x_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2136 x_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2137 x_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2138 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2139
2140
2141
2142 -- Local Variables
2143 l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wori';
2144 l_api_version CONSTANT NUMBER := 1.0;
2145 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2146 l_return_status VARCHAR2(1);
2147 l_msg_count NUMBER;
2148 l_message_text VARCHAR2(256);
2149 l_stmt_num NUMBER;
2150 l_instance_id NUMBER;
2151 l_wip_entity_name VARCHAR2(80);
2152 l_output_dir VARCHAR2(512);
2153
2154 BEGIN
2155
2156 -- Standard Start of API savepoint
2157 l_stmt_num := 10;
2158 SAVEPOINT insert_into_wori_pvt;
2159
2160 l_stmt_num := 20;
2161 -- Standard call to check for call compatibility.
2162 IF NOT fnd_api.compatible_api_call(
2163 l_api_version
2164 ,p_api_version
2165 ,l_api_name
2166 ,g_pkg_name) THEN
2167 RAISE fnd_api.g_exc_unexpected_error;
2168 END IF;
2169
2170 l_stmt_num := 30;
2171 -- Initialize message list if p_init_msg_list is set to TRUE.
2172 IF fnd_api.to_boolean(p_init_msg_list) THEN
2173 fnd_msg_pub.initialize;
2174 END IF;
2175
2176 l_stmt_num := 40;
2177 -- Initialize API return status to success
2178 x_return_status := fnd_api.g_ret_sts_success;
2179
2180 l_stmt_num := 50;
2181
2182 -- API body
2183
2184
2185
2186 select instance_id
2187 into l_instance_id
2188 from bom_resource_employees
2189 where resource_id = p_resource_id
2190 and organization_id = p_organization_id
2191 and person_id = p_employee_id;
2192
2193
2194
2195 l_eam_res_inst_rec.WIP_ENTITY_ID := to_number(p_wip_entity_id);
2196 l_eam_res_inst_rec.ORGANIZATION_ID := to_number(p_organization_id);
2197 l_eam_res_inst_rec.OPERATION_SEQ_NUM := to_number(p_operation_seq_num);
2198 l_eam_res_inst_rec.RESOURCE_SEQ_NUM := to_number(p_resource_seq_num);
2199 l_eam_res_inst_rec.INSTANCE_ID := to_number(l_instance_id);
2200
2201 l_eam_res_inst_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
2202
2203 l_eam_res_inst_tbl(1) := l_eam_res_inst_rec;
2204
2205 -- Obtain the work order name and return it back
2206
2207 select wip_entity_name
2208 into l_wip_entity_name
2209 from wip_entities
2210 where wip_entity_id = l_eam_res_inst_rec.WIP_ENTITY_ID
2211 and organization_id = l_eam_res_inst_rec.ORGANIZATION_ID;
2212
2213 x_wip_entity_name := l_wip_entity_name;
2214
2215 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
2216
2217
2218 EAM_PROCESS_WO_PUB.Process_WO
2219 ( p_bo_identifier => 'EAM'
2220 , p_init_msg_list => TRUE
2221 , p_api_version_number => 1.0
2222 , p_eam_wo_rec => l_eam_wo_rec
2223 , p_eam_op_tbl => l_eam_op_tbl
2224 , p_eam_op_network_tbl => l_eam_op_network_tbl
2225 , p_eam_res_tbl => l_eam_res_tbl
2226 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2227 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2228 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2229 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2230 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
2231 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
2232 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2233 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2234 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2235 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2236 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2237 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2238 , p_eam_request_tbl => l_eam_request_tbl
2239 , x_eam_wo_rec => x_out_eam_wo_rec
2240 , x_eam_op_tbl => x_out_eam_op_tbl
2241 , x_eam_op_network_tbl => x_out_eam_op_network_tbl
2242 , x_eam_res_tbl => x_out_eam_res_tbl
2243 , x_eam_res_inst_tbl => x_out_eam_res_inst_tbl
2244 , x_eam_sub_res_tbl => x_out_eam_sub_res_tbl
2245 , x_eam_res_usage_tbl => x_out_eam_res_usage_tbl
2246 , x_eam_mat_req_tbl => x_out_eam_mat_req_tbl
2247 , x_eam_direct_items_tbl => x_out_eam_direct_items_tbl
2248 , x_eam_wo_comp_rec => x_out_eam_wo_comp_rec
2249 , x_eam_wo_quality_tbl => x_out_eam_wo_quality_tbl
2250 , x_eam_meter_reading_tbl => x_out_eam_meter_reading_tbl
2251 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2252 , x_eam_wo_comp_rebuild_tbl => x_out_eam_wo_comp_rebuild_tbl
2253 , x_eam_wo_comp_mr_read_tbl => x_out_eam_wo_comp_mr_read_tbl
2254 , x_eam_op_comp_tbl => x_out_eam_op_comp_tbl
2255 , x_eam_request_tbl => x_out_eam_request_tbl
2256 , x_return_status => l_return_status
2257 , x_msg_count => l_msg_count
2258 , p_debug =>NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2259 , p_debug_filename => 'insertwori.log'
2260 , p_output_dir => l_output_dir
2261 );
2262
2263 x_return_status := l_return_status ;
2264 x_msg_count := l_msg_count;
2265 x_msg_data := 'SUCCESS';
2266
2267
2268
2269
2270 -- End of API body.
2271 -- Standard check of p_commit.
2272 IF fnd_api.to_boolean(p_commit) THEN
2273 COMMIT WORK;
2274 END IF;
2275
2276 l_stmt_num := 999;
2277 -- Standard call to get message count and if count is 1, get message info.
2278 fnd_msg_pub.count_and_get(
2279 p_encoded => fnd_api.g_false
2280 ,p_count => x_msg_count
2281 ,p_data => x_msg_data);
2282
2283 EXCEPTION
2284 WHEN fnd_api.g_exc_error THEN
2285 ROLLBACK TO insert_into_wori_pvt;
2286 x_return_status := fnd_api.g_ret_sts_error;
2287 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2288 fnd_msg_pub.count_and_get(
2289 p_encoded => fnd_api.g_false
2290 ,p_count => x_msg_count
2291 ,p_data => x_msg_data);
2292 WHEN fnd_api.g_exc_unexpected_error THEN
2293 ROLLBACK TO insert_into_wori_pvt;
2294 x_return_status := fnd_api.g_ret_sts_unexp_error;
2295 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2296 fnd_msg_pub.count_and_get(
2297 p_encoded => fnd_api.g_false
2298 ,p_count => x_msg_count
2299 ,p_data => x_msg_data);
2300 WHEN OTHERS THEN
2301 ROLLBACK TO insert_into_wori_pvt;
2302 x_return_status := fnd_api.g_ret_sts_unexp_error;
2303 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, SQLERRM);
2304 IF fnd_msg_pub.check_msg_level(
2305 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2306 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2307 END IF;
2308
2309 fnd_msg_pub.count_and_get(
2310 p_encoded => fnd_api.g_false
2311 ,p_count => x_msg_count
2312 ,p_data => x_msg_data);
2313
2314
2315 END insert_into_wori;
2316
2317
2318 FUNCTION get_person_id RETURN VARCHAR2 IS
2319
2320 l_user_id NUMBER := FND_GLOBAL.USER_ID;
2321 l_person_id VARCHAR2(30) := '';
2322
2323 BEGIN
2324 l_user_id := FND_GLOBAL.USER_ID;
2325 begin
2326 select to_char(employee_id)
2327 into l_person_id
2328 from fnd_user
2329 where user_id = l_user_id;
2330
2331 exception
2332 when others then
2333 null;
2334
2335 end;
2336
2337 return l_person_id;
2338 END;
2339
2340 function get_dept_id(p_org_code in varchar2, p_org_id in number, p_dept_code in varchar2, p_dept_id in number)
2341 return number is
2342 l_dept_id number;
2343 l_organization_id number;
2344 l_inventory_item_id number;
2345 begin
2346
2347 if p_dept_id is not null then
2348 return p_dept_id;
2349 elsif p_dept_id is null and p_dept_code is null then
2350 return null;
2351 elsif p_dept_code is not null and p_org_id is not null then
2352 select department_id into l_dept_id
2353 from bom_departments
2354 where department_code = p_dept_code
2355 and organization_id = p_org_id;
2356
2357 return l_dept_id;
2358 else
2359 select bd.department_id into l_dept_id
2360 from bom_departments bd, mtl_parameters mp
2361 where bd.department_code = p_dept_code
2362 and mp.organization_code = p_org_code
2363 and bd.organization_id = mp.organization_id;
2364
2365 return l_dept_id;
2366 end if;
2367
2368
2369 end get_dept_id;
2370
2371 --This procedure validates and deactivates the asset
2372
2373 PROCEDURE deactivate_assets(
2374 P_API_VERSION IN NUMBER,
2375 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
2376 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
2377 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2378 P_INVENTORY_ITEM_ID IN NUMBER,
2379 P_SERIAL_NUMBER IN VARCHAR2,
2380 P_ORGANIZATION_ID IN NUMBER,
2381 P_GEN_OBJECT_ID IN NUMBER,
2382 P_INSTANCE_ID IN NUMBER,
2383 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2384 X_MSG_COUNT OUT NOCOPY NUMBER,
2385 X_MSG_DATA OUT NOCOPY VARCHAR2)
2386 IS
2387
2388 l_api_name CONSTANT VARCHAR2(30) := 'deactivate_assets';
2389 l_api_version CONSTANT NUMBER := 1.0;
2390 l_stmt_num number := 0;
2391 l_hr_exists varchar2(1);
2392 l_routes_exists varchar2(1);
2393 l_wo_exists varchar2(1);
2394 l_sr_exists varchar2(1);
2395 l_INVENTORY_ITEM_ID NUMBER;
2396 l_SERIAL_NUMBER VARCHAR2(30);
2397 l_ORGANIZATION_ID NUMBER;
2398 l_instance_id NUMBER;
2399 l_gen_object_id NUMBER;
2400 BEGIN
2401
2402 -- Standard Start of API savepoint
2403 l_stmt_num := 10;
2404 SAVEPOINT asset_util_pvt;
2405
2406 l_stmt_num := 20;
2407 -- Standard call to check for call compatibility.
2408 IF NOT fnd_api.compatible_api_call(
2409 l_api_version
2410 ,p_api_version
2411 ,l_api_name
2412 ,g_pkg_name) THEN
2413 RAISE fnd_api.g_exc_unexpected_error;
2414 END IF;
2415
2416 l_stmt_num := 30;
2417 -- Initialize message list if p_init_msg_list is set to TRUE.
2418 IF fnd_api.to_boolean(p_init_msg_list) THEN
2419 fnd_msg_pub.initialize;
2420 END IF;
2421
2422 l_stmt_num := 40;
2423 -- Initialize API return status to success
2424 x_return_status := fnd_api.g_ret_sts_success;
2425
2426 if P_INSTANCE_ID is null then
2427 l_organization_id := p_organization_id;
2428 select instance_id into l_instance_id
2429 from csi_item_instances
2430 where serial_number = p_serial_number
2431 and inventory_item_id = p_inventory_item_id
2432 ;
2433 select gen_object_id into l_gen_object_id
2434 from mtl_serial_numbers
2435 where serial_number = p_serial_number
2436 and inventory_item_id = p_inventory_item_id
2437 ;
2438 else
2439 l_instance_id := p_instance_id;
2440 l_gen_object_id := p_gen_object_id;
2441
2442 select serial_number, inventory_item_id, current_organization_id
2443 into l_serial_number, l_inventory_item_id, l_organization_id
2444 from mtl_serial_numbers
2445 where gen_object_id = p_gen_object_id;
2446
2447 end if;
2448
2449
2450 --HIERARCHY CHECK
2451
2452 begin
2453 SELECT 'Y'
2454 INTO l_hr_exists
2455 FROM DUAL
2456 WHERE EXISTS
2457 (SELECT mog.object_id
2458 FROM mtl_object_genealogy mog
2459 WHERE mog.object_id = l_gen_object_id
2460
2461 -- Fix for bug 2219479. We do not allow assets that are
2462 -- a child or a parent in the future to be deactivated.
2463 -- hence the check for start_date_active is removed
2464
2465 AND sysdate <= nvl(mog.end_date_active(+), sysdate))
2466 OR EXISTS
2467 (SELECT mog.object_id
2468 FROM mtl_object_genealogy mog
2469 WHERE mog.parent_object_id = l_gen_object_id
2470 AND sysdate <= nvl(mog.end_date_active(+), sysdate));
2471
2472 exception
2473 when no_data_found then
2474 l_hr_exists := 'N';
2475 end;
2476
2477 if (l_hr_exists = 'Y') then
2478 fnd_message.set_name('EAM','EAM_HIERARCHY_EXISTS');
2479 fnd_msg_pub.add;
2480 RAISE fnd_api.g_exc_error;
2481 end if;
2482
2483 -- ROUTES CHECK
2484
2485 begin
2486 SELECT 'Y'
2487 INTO l_routes_exists
2488 FROM DUAL
2489 WHERE EXISTS
2490 (SELECT mena.network_association_id
2491 FROM mtl_eam_network_assets mena
2492 WHERE mena.maintenance_object_type = 3
2493 AND mena.maintenance_object_id = l_instance_id
2494 AND sysdate >= nvl(mena.start_date_active(+), sysdate)
2495 AND sysdate <= nvl(mena.end_date_active(+), sysdate));
2496 exception
2497 when no_data_found then
2498 l_routes_exists := 'N';
2499 end;
2500
2501
2502 if (nvl(l_routes_exists,'N') = 'Y') then
2503 fnd_message.set_name('EAM','EAM_ROUTE_EXISTS');
2504 fnd_msg_pub.add;
2505 RAISE fnd_api.g_exc_error;
2506 end if;
2507
2508 -- WORK REQUEST AND WORK ORDER CHECK
2509 begin
2510 SELECT 'Y'
2511 INTO l_wo_exists
2512 FROM DUAL
2513 WHERE EXISTS
2514 (SELECT wdj.wip_entity_id
2515 FROM wip_discrete_jobs wdj
2516 WHERE wdj.status_type not in (4, 5, 7, 12)
2517 AND wdj.maintenance_object_type = 3
2518 AND wdj.maintenance_object_id = l_instance_id
2519 AND wdj.organization_id = l_organization_id)
2520 OR EXISTS
2521 (SELECT wewr.asset_number
2522 FROM wip_eam_work_requests wewr
2523 WHERE wewr.work_request_status_id not in (5, 6)
2524 AND wewr.organization_id = l_organization_id
2525 AND wewr.maintenance_object_type = 3
2526 AND wewr.maintenance_object_id = l_instance_id);
2527 exception
2528 when no_data_found then
2529 l_wo_exists := 'N';
2530 end;
2531
2532 if (nvl(l_wo_exists,'N') = 'Y') then
2533 fnd_message.set_name('EAM','EAM_WO_EXISTS');
2534 fnd_msg_pub.add;
2535 RAISE fnd_api.g_exc_error;
2536 end if;
2537
2538 -- check open Service Reqests
2539 begin
2540 SELECT 'Y'
2541 into l_sr_exists
2542 from dual
2543 where exists
2544 (
2545 select cia.incident_id from cs_incidents_vl_sec cia,CS_INCIDENT_STATUSES_VL cis
2546 where cia.customer_product_id = l_instance_id
2547 and cia.incident_status_id = cis.incident_status_id
2548 and nvl(cis.close_flag,'N') <> 'Y'
2549 and cis.language = userenv('lang')
2550
2551 );
2552 exception
2553 when no_data_found then
2554 l_sr_exists := 'N';
2555 end;
2556
2557 if (nvl(l_sr_exists,'N') = 'Y') then
2558 fnd_message.set_name('EAM','EAM_SR_EXISTS');
2559 fnd_msg_pub.add;
2560 RAISE fnd_api.g_exc_error;
2561 end if;
2562 eam_asset_number_pvt.update_asset(
2563 P_API_VERSION => 1.0
2564 ,p_commit => p_commit
2565 ,p_instance_id => l_instance_id
2566 ,P_INVENTORY_ITEM_ID => l_inventory_item_id
2567 ,P_SERIAL_NUMBER => l_serial_number
2568 ,P_ORGANIZATION_ID => l_organization_id
2569 ,p_active_end_date => sysdate
2570 ,X_RETURN_STATUS => x_return_status
2571 ,X_MSG_COUNT => x_msg_count
2572 ,X_MSG_DATA => x_msg_data
2573 );
2574
2575 EXCEPTION
2576 WHEN fnd_api.g_exc_error THEN
2577 ROLLBACK TO asset_util_pvt;
2578 x_return_status := fnd_api.g_ret_sts_error;
2579 fnd_msg_pub.count_and_get(
2580 p_encoded => fnd_api.g_false
2581 ,p_count => x_msg_count
2582 ,p_data => x_msg_data);
2583 WHEN fnd_api.g_exc_unexpected_error THEN
2584 ROLLBACK TO asset_util_pvt;
2585 x_return_status := fnd_api.g_ret_sts_unexp_error;
2586 fnd_msg_pub.count_and_get(
2587 p_encoded => fnd_api.g_false
2588 ,p_count => x_msg_count
2589 ,p_data => x_msg_data);
2590
2591 WHEN OTHERS THEN
2592 ROLLBACK TO asset_util_pvt;
2593 x_return_status := fnd_api.g_ret_sts_unexp_error;
2594
2595 IF fnd_msg_pub.check_msg_level(
2596 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2597 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name||'('||l_stmt_num||')');
2598 END IF;
2599
2600 fnd_msg_pub.count_and_get(
2601 p_encoded => fnd_api.g_false
2602 ,p_count => x_msg_count
2603 ,p_data => x_msg_data);
2604
2605 end deactivate_assets;
2606
2607
2608 --This procedure logs the api return status, message count and all messages
2609 --returned including the last message from the api as well as all messages in
2610 --the message stack at that time
2611 --Author: dgupta
2612 procedure log_api_return(
2613 p_module in varchar2,
2614 p_api in varchar2,
2615 p_return_status in varchar2,
2616 p_msg_count in number,
2617 p_msg_data in varchar2
2618 ) IS
2619 l_msg_count_1 number := null;
2620 l_msg_data_1 varchar2(2000) := NULL;
2621 l_return_char varchar2(2000) := NULL;
2622 begin
2623 -- This should be called only if logging is enabled.
2624 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2625 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2626 p_api || ' returns. '|| 'Return Status = ' || p_return_status ||
2627 '. Message Count = ' || p_msg_count);
2628 end if;
2629 if (p_msg_data is not null) then
2630 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2631 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2632 'Last Message = ' || REPLACE(p_msg_data, CHR(0), ' ')); --null p_msg_data is OK
2633 end if;
2634 end if;
2635 FND_MSG_PUB.Count_And_Get('T', l_msg_count_1, l_msg_data_1);
2636 if ((l_msg_count_1 is not null) and (l_msg_count_1 > 0) and
2637 ((p_msg_count is null) or (l_msg_count_1 <> p_msg_count))) then
2638 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2639 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2640 'Message Count (from message Stack)= ' || l_msg_count_1);
2641 end if;
2642 end if;
2643 l_msg_data_1 := fnd_msg_pub.get(fnd_msg_pub.G_FIRST, FND_API.G_FALSE); --set encoded to true
2644 if (l_msg_count_1 is not null and l_msg_count_1 > 0) then
2645 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2646 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2647 'Message #1 (from message stack) =' || l_msg_data_1);
2648 end if;
2649 for i in 2..l_msg_count_1 LOOP
2650 l_msg_data_1 := fnd_msg_pub.get(fnd_msg_pub.G_NEXT, FND_API.G_FALSE); --set encoded to true
2651 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2652 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_module,
2653 'Message #' || to_char(i) || ' (from message stack) =' || l_msg_data_1);
2654 end if;
2655 END LOOP;
2656 end if;
2657 end log_api_return;
2658
2659
2660
2661
2662 FUNCTION get_onhand_quant(p_org_id in number, p_inventory_item_id in number)
2663 RETURN number IS
2664
2665 CURSOR get_material_details(c_organization_id NUMBER,c_inventory_item_id NUMBER) IS
2666 SELECT
2667 msi.lot_control_code,
2668 msi.serial_number_control_code,
2669 msi.revision_qty_control_code
2670 FROM mtl_system_items_b msi
2671 WHERE msi.organization_id = c_organization_id
2672 AND msi.inventory_item_id = c_inventory_item_id;
2673
2674 l_is_revision_control BOOLEAN;
2675 l_is_lot_control BOOLEAN;
2676 l_is_serial_control BOOLEAN;
2677 l_qoh NUMBER;
2678 l_rqoh NUMBER;
2679 l_qr NUMBER;
2680 l_qs NUMBER;
2681 l_att NUMBER;
2682 l_atr NUMBER;
2683 l_return_status VARCHAR2(1);
2684 l_msg_count NUMBER;
2685 l_msg_data VARCHAR2(1000);
2686 X_QOH_PROFILE_VALUE NUMBER;
2687
2688 BEGIN
2689 X_QOH_PROFILE_VALUE := TO_NUMBER(FND_PROFILE.VALUE('EAM_REQUIREMENT_QOH_OPTION'));
2690 IF (X_QOH_PROFILE_VALUE IS NULL)
2691 THEN
2692 X_QOH_PROFILE_VALUE := 1;
2693 END IF;
2694
2695 IF X_QOH_PROFILE_VALUE = 1 THEN
2696 BEGIN
2697 FOR p_materials_csr IN get_material_details(p_org_id,p_inventory_item_id)
2698 LOOP
2699 IF (p_materials_csr.revision_qty_control_code = 2) THEN
2700 l_is_revision_control:=TRUE;
2701 ELSE
2702 l_is_revision_control:=FALSE;
2703 END IF;
2704
2705 IF (p_materials_csr.lot_control_code = 2) THEN
2706 l_is_lot_control:=TRUE;
2707 ELSE
2708 l_is_lot_control:=FALSE;
2709 END IF;
2710
2711 IF (p_materials_csr.serial_number_control_code = 1) THEN
2712 l_is_serial_control:=FALSE;
2713 ELSE
2714 l_is_serial_control:=TRUE;
2715 END IF;
2716
2717 END LOOP;
2718 inv_quantity_tree_pub.clear_quantity_cache;
2719 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
2720 ( p_api_version_number => 1.0
2721 , p_init_msg_lst => FND_API.G_TRUE
2722 , x_return_status => l_return_status
2723 , x_msg_count => l_msg_count
2724 , x_msg_data => l_msg_data
2725 , p_organization_id => p_org_id
2726 , p_inventory_item_id => p_inventory_item_id
2727 , p_tree_mode => 2 --available to transact
2728 , p_is_revision_control => l_is_revision_control
2729 , p_is_lot_control => l_is_lot_control
2730 , p_is_serial_control => l_is_serial_control
2731 , p_revision => NULL
2732 , p_lot_number => NULL
2733 , p_subinventory_code => NULL
2734 , p_locator_id => NULL
2735 , x_qoh => l_qoh
2736 , x_rqoh => l_rqoh
2737 , x_qr => l_qr
2738 , x_qs => l_qs
2739 , x_att => l_att
2740 , x_atr => l_atr
2741 );
2742
2743 IF(l_return_status <> 'S') THEN
2744 RETURN 0;
2745 END IF;
2746
2747 EXCEPTION
2748 WHEN OTHERS THEN
2749 RETURN 0;
2750 END;
2751 ELSE
2752
2753 SELECT NVL(SUM(QUANTITY),0)
2754 into l_qoh
2755 FROM MTL_SECONDARY_INVENTORIES MSS,
2756 MTL_ITEM_QUANTITIES_VIEW MOQ,
2757 MTL_SYSTEM_ITEMS MSI
2758 WHERE MOQ.ORGANIZATION_ID = p_org_id
2759 AND MSI.ORGANIZATION_ID = p_org_id
2760 AND MSS.ORGANIZATION_ID = p_org_id
2761 AND MOQ.INVENTORY_ITEM_ID = p_inventory_item_id
2762 AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
2763 AND MSS.SECONDARY_INVENTORY_NAME = MOQ.SUBINVENTORY_CODE
2764 AND MSS.AVAILABILITY_TYPE = 1;
2765 END IF;
2766
2767 RETURN l_qoh;
2768
2769 end get_onhand_quant;
2770
2771 /* Added Utility function to get available quantity for bug# 12952642*/
2772 FUNCTION get_available_quant(p_organization_id IN NUMBER,p_inventory_item_id IN NUMBER )
2773 RETURN number
2774 IS
2775 CURSOR get_material_details(c_organization_id NUMBER,c_inventory_item_id NUMBER) IS
2776 SELECT
2777 mtlbkfv.lot_control_code,
2778 mtlbkfv.serial_number_control_code,
2779 mtlbkfv.revision_qty_control_code
2780 FROM mtl_system_items_b_kfv mtlbkfv
2781 WHERE mtlbkfv.organization_id = c_organization_id
2782 AND mtlbkfv.inventory_item_id = c_inventory_item_id;
2783
2784 l_is_revision_control BOOLEAN;
2785 l_is_lot_control BOOLEAN;
2786 l_is_serial_control BOOLEAN;
2787 l_qoh NUMBER;
2788 l_rqoh NUMBER;
2789 l_qr NUMBER;
2790 l_qs NUMBER;
2791 l_att NUMBER;
2792 l_atr NUMBER;
2793 l_return_status VARCHAR2(1);
2794 l_msg_count NUMBER;
2795 l_msg_data VARCHAR2(1000);
2796 X_QOH_PROFILE_VALUE NUMBER;
2797 BEGIN
2798
2799 BEGIN
2800 X_QOH_PROFILE_VALUE := TO_NUMBER(FND_PROFILE.VALUE('EAM_REQUIREMENT_QOH_OPTION'));
2801 IF (X_QOH_PROFILE_VALUE IS NULL)
2802 THEN
2803 X_QOH_PROFILE_VALUE := 1;
2804 END IF;
2805
2806 FOR p_materials_csr IN get_material_details(p_organization_id,p_inventory_item_id)
2807 LOOP
2808 IF (p_materials_csr.revision_qty_control_code = 2) THEN
2809 l_is_revision_control:=TRUE;
2810 ELSE
2811 l_is_revision_control:=FALSE;
2812 END IF;
2813
2814 IF (p_materials_csr.lot_control_code = 2) THEN
2815 l_is_lot_control:=TRUE;
2816 ELSE
2817 l_is_lot_control:=FALSE;
2818 END IF;
2819
2820 IF (p_materials_csr.serial_number_control_code = 1) THEN
2821 l_is_serial_control:=FALSE;
2822 ELSE
2823 l_is_serial_control:=TRUE;
2824 END IF;
2825 END LOOP;
2826
2827 IF X_QOH_PROFILE_VALUE = 1 THEN
2828 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
2829 ( p_api_version_number => 1.0
2830 , p_init_msg_lst => FND_API.G_TRUE
2831 , x_return_status => l_return_status
2832 , x_msg_count => l_msg_count
2833 , x_msg_data => l_msg_data
2834 , p_organization_id => p_organization_id
2835 , p_inventory_item_id => p_inventory_item_id
2836 , p_tree_mode => 2 --available to transact
2837 , p_is_revision_control => l_is_revision_control
2838 , p_is_lot_control => l_is_lot_control
2839 , p_is_serial_control => l_is_serial_control
2840 , p_revision => NULL
2841 , p_lot_number => NULL
2842 , p_subinventory_code => NULL
2843 , p_locator_id => NULL
2844 , p_onhand_source => inv_quantity_tree_pvt.g_all_subs
2845 , x_qoh => l_qoh
2846 , x_rqoh => l_rqoh
2847 , x_qr => l_qr
2848 , x_qs => l_qs
2849 , x_att => l_att
2850 , x_atr => l_atr
2851 );
2852 ELSE
2853 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES
2854 ( p_api_version_number => 1.0
2855 , p_init_msg_lst => FND_API.G_TRUE
2856 , x_return_status => l_return_status
2857 , x_msg_count => l_msg_count
2858 , x_msg_data => l_msg_data
2859 , p_organization_id => p_organization_id
2860 , p_inventory_item_id => p_inventory_item_id
2861 , p_tree_mode => 2 --available to transact
2862 , p_is_revision_control => l_is_revision_control
2863 , p_is_lot_control => l_is_lot_control
2864 , p_is_serial_control => l_is_serial_control
2865 , p_revision => NULL
2866 , p_lot_number => NULL
2867 , p_subinventory_code => NULL
2868 , p_locator_id => NULL
2869 , p_onhand_source => inv_quantity_tree_pvt.g_nettable_only
2870 , x_qoh => l_qoh
2871 , x_rqoh => l_rqoh
2872 , x_qr => l_qr
2873 , x_qs => l_qs
2874 , x_att => l_att
2875 , x_atr => l_atr
2876 );
2877 END IF;
2878
2879 IF(l_return_status <> 'S') THEN
2880 RETURN 0;
2881 END IF;
2882
2883 EXCEPTION
2884 WHEN OTHERS THEN
2885 RETURN 0;
2886 END;
2887 RETURN l_att;
2888 END get_available_quant;
2889
2890 /* Bug # 3698307
2891 validate_linear_id is added for Linear Asset Management project
2892 Basically it verify's whether the passed linear_id exists in EAM_LINEAR_LOCATIONS
2893 table or not.
2894 */
2895
2896 FUNCTION validate_linear_id(p_eam_linear_id IN NUMBER)
2897 RETURN BOOLEAN IS
2898 l_count NUMBER;
2899 BEGIN
2900
2901 SELECT count(*) INTO l_count FROM eam_linear_locations
2902 WHERE eam_linear_id = p_eam_linear_id;
2903
2904 IF (l_count > 0) THEN
2905 RETURN true;
2906 ELSE
2907 RETURN false;
2908 END IF;
2909
2910 END validate_linear_id;
2911
2912 --------------------------------------------------------------------------
2913 -- PROCEDURE --
2914 -- Create_Asset --
2915 -- --
2916 -- DESCRIPTION --
2917 -- This API is used to create an IB instance whenever a work order is --
2918 -- saved on a rebuild in predefined status. It will call the wrapper --
2919 -- API that in turn calls the IB create_asset API --
2920 -- It a) Create the IB instance b) Updates current status in MSN --
2921 -- c) Instantiates the rebuild d) Updates the WO Record --
2922 -- OR when a rebuild work order's serial number is updated --
2923 -- --
2924 -- This API is invoked from the WO API. --
2925 -- --
2926 -- PURPOSE: --
2927 -- Oracle Applications Rel 12 --
2928 -- --
2929 -- HISTORY: --
2930 -- 05/20/05 Anju Gupta Created --
2931 ----------------------------------------------------------------------------
2932
2933 PROCEDURE CREATE_ASSET(
2934 P_API_VERSION IN NUMBER
2935 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
2936 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
2937 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2938 ,X_EAM_WO_REC IN OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type
2939 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
2940 ,X_MSG_COUNT OUT NOCOPY NUMBER
2941 ,X_MSG_DATA OUT NOCOPY VARCHAR2
2942 )
2943 is
2944 l_api_name CONSTANT VARCHAR2(30) := 'create_asset';
2945 l_api_version CONSTANT NUMBER := 1.0;
2946 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2947 l_count number := 0;
2948 l_x_asset_return_status varchar2(1);
2949 l_x_asset_msg_count number;
2950 l_x_asset_msg_data varchar2(20000);
2951 l_instance_id number;
2952 l_stmt_num number := 0;
2953 l_current_status number;
2954 l_organization_id number;
2955 l_description mtl_serial_numbers.descriptive_text%TYPE;
2956 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
2957 begin
2958 -- Standard Start of API savepoint
2959 SAVEPOINT create_asset;
2960
2961 -- Standard call to check for call compatibility.
2962 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2963 RAISE fnd_api.g_exc_unexpected_error;
2964 END IF;
2965
2966 -- Initialize message list if p_init_msg_list is set to TRUE.
2967 IF fnd_api.to_boolean(p_init_msg_list) THEN
2968 fnd_msg_pub.initialize;
2969 END IF;
2970
2971 -- Initialize API return status to success
2972 l_stmt_num := 10;
2973 x_return_status := fnd_api.g_ret_sts_success;
2974
2975 l_eam_wo_rec := x_eam_wo_rec;
2976
2977
2978 -- API body
2979 --Figure out some unknowns
2980 BEGIN
2981 select msn.current_organization_id, msn.descriptive_text, msn.current_status
2982 into l_organization_id, l_description, l_current_status
2983 from mtl_serial_numbers msn
2984 where msn.inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id,
2985 l_eam_wo_rec.asset_group_id)
2986 and msn.serial_number = nvl(l_eam_wo_rec.rebuild_serial_number,
2987 l_eam_wo_rec.asset_number);
2988
2989 EXCEPTION
2990
2991 WHEN NO_DATA_FOUND THEN
2992 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2993 THEN
2994 FND_MSG_PUB.add_exc_msg
2995 ( 'EAM_COMMON_UTILITIES_PVT'
2996 , '.Create_Asset : Statement -'||to_char(l_stmt_num)
2997 );
2998 END IF;
2999 RAISE fnd_api.g_exc_unexpected_error;
3000
3001 END;
3002
3003 --This is a predefined rebuild on which a work order is being defined -
3004 --Create an asset against it and reset the work order pl/sql table
3005 l_stmt_num := 30;
3006
3007 if l_current_status = 1 then
3008
3009 EAM_ASSET_NUMBER_PVT.Create_Asset(
3010 P_API_VERSION => p_api_version
3011 ,P_INIT_MSG_LIST => p_init_msg_list
3012 ,P_COMMIT => p_commit
3013 ,P_VALIDATION_LEVEL => p_validation_level
3014 ,P_INVENTORY_ITEM_ID => nvl(l_eam_wo_rec.rebuild_item_id, l_eam_wo_rec.asset_group_id)
3015 ,P_SERIAL_NUMBER => nvl(l_eam_wo_rec.rebuild_serial_number, l_eam_wo_rec.asset_number)
3016 ,P_INSTANCE_NUMBER => null
3017 ,P_INSTANCE_DESCRIPTION => l_description
3018 ,P_ORGANIZATION_ID => l_organization_id
3019 ,P_LAST_UPDATE_DATE => sysdate
3020 ,P_LAST_UPDATED_BY => l_eam_wo_rec.user_id
3021 ,P_CREATION_DATE => sysdate
3022 ,P_CREATED_BY => l_eam_wo_rec.user_id
3023 ,P_LAST_UPDATE_LOGIN => l_eam_wo_rec.user_id
3024 ,X_OBJECT_ID => l_instance_id
3025 ,X_RETURN_STATUS => l_x_asset_return_status
3026 ,X_MSG_COUNT => l_x_asset_msg_count
3027 ,X_MSG_DATA => l_x_asset_msg_data
3028 );
3029
3030 if (l_x_asset_return_status <> FND_API.G_RET_STS_SUCCESS) then
3031 l_stmt_num := 40;
3032 RAISE FND_API.G_EXC_ERROR ;
3033 end if;
3034
3035 else
3036
3037 l_stmt_num := 50;
3038 begin
3039
3040 select cii.instance_id
3041 into l_instance_id
3042 from csi_item_instances cii
3043 where inventory_item_id = nvl(l_eam_wo_rec.rebuild_item_id,
3044 l_eam_wo_rec.asset_group_id)
3045 and serial_number = nvl(l_eam_wo_rec.rebuild_serial_number,
3046 l_eam_wo_rec.asset_number);
3047
3048
3049 EXCEPTION
3050
3051 WHEN NO_DATA_FOUND THEN
3052 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3053 FND_MSG_PUB.add_exc_msg
3054 ( 'EAM_COMMON_UTILITIES_PVT'
3055 , '.Create_Asset : Statement -'||to_char(l_stmt_num)
3056 );
3057 END IF;
3058 RAISE fnd_api.g_exc_unexpected_error;
3059
3060 END;
3061 end if;
3062
3063
3064 --The Instance has been created sucessfully. Update the WO Record
3065
3066 l_stmt_num := 50;
3067
3068 l_eam_wo_rec.maintenance_object_type := 3;
3069 l_eam_wo_rec.maintenance_object_id := l_instance_id;
3070
3071 x_eam_wo_rec := l_eam_wo_rec;
3072
3073 -- End of API body.
3074 -- Standard check of p_commit.
3075 IF fnd_api.to_boolean(p_commit) THEN
3076 COMMIT WORK;
3077 END IF;
3078
3079 -- Standard call to get message count and if count is 1, get message info.
3080 fnd_msg_pub.count_and_get(
3081 p_count => x_msg_count
3082 ,p_data => x_msg_data);
3083
3084
3085 EXCEPTION
3086 WHEN fnd_api.g_exc_error THEN
3087 ROLLBACK TO create_asset;
3088 x_return_status := fnd_api.g_ret_sts_error;
3089 fnd_msg_pub.count_and_get(
3090 p_count => x_msg_count
3091 ,p_data => x_msg_data);
3092 WHEN fnd_api.g_exc_unexpected_error THEN
3093 ROLLBACK TO create_asset;
3094 x_return_status := fnd_api.g_ret_sts_unexp_error;
3095 fnd_msg_pub.count_and_get(
3096 p_count => x_msg_count
3097 ,p_data => x_msg_data);
3098 WHEN OTHERS THEN
3099 ROLLBACK TO create_asset;
3100 x_return_status := fnd_api.g_ret_sts_unexp_error;
3101
3102 IF fnd_msg_pub.check_msg_level(
3103 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3104 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
3105 END IF;
3106
3107 fnd_msg_pub.count_and_get(
3108 p_count => x_msg_count
3109 ,p_data => x_msg_data);
3110
3111 end create_asset;
3112
3113 FUNCTION check_deactivate(
3114 p_maintenance_object_id IN NUMBER, -- for Maintenance Object Type of 3, this should be Instance_Id
3115 p_maintenance_object_type IN NUMBER -- Type 3 (Instance Id)
3116
3117 )
3118 return boolean is
3119 l_gen_object_id number;
3120 l_result boolean;
3121 l_gen_obj_exists varchar2(1); -- Bug 6799616
3122 l_hr_exists varchar2(1);
3123 l_routes_exists varchar2(1);
3124 l_wo_exists varchar2(1);
3125 l_network_asset_flag varchar2(1);
3126 l_serial_number_control_code number;
3127 begin
3128 l_result := true;
3129 -- Bug 6799616
3130 -- Added exception handling block in case the item instance is not serial
3131 begin
3132 select gen_object_id into l_gen_object_id
3133 from mtl_serial_numbers msn, csi_item_instances cii
3134 where msn.inventory_item_id = cii.inventory_item_id
3135 and msn.serial_number = cii.serial_number
3136 and cii.instance_id = p_maintenance_object_id;
3137 if (l_gen_object_id is not null) then
3138 l_gen_obj_exists := 'Y';
3139 else
3140 l_gen_obj_exists := 'N';
3141 end if;
3142 exception
3143 when no_data_found then
3144 l_gen_obj_exists := 'N';
3145 end;
3146
3147 --Get the serial number control code for item.Bug 9836939.
3148 BEGIN
3149
3150 SELECT
3151 serial_number_control_code
3152 into
3153 l_serial_number_control_code
3154 FROM
3155 mtl_system_items_b msib,
3156 csi_item_instances cii
3157 WHERE
3158 cii.inventory_item_id=msib.inventory_item_id
3159 AND cii.last_vld_organization_id=msib.organization_id
3160 AND cii.instance_id=p_maintenance_object_id;
3161
3162 EXCEPTION
3163 when no_data_found then
3164 null;
3165 END;
3166
3167 --HIERARCHY CHECK
3168 if (l_gen_obj_exists = 'Y') then
3169 begin
3170 SELECT 'Y'
3171 INTO l_hr_exists
3172 FROM DUAL
3173 WHERE EXISTS
3174 (SELECT mog.object_id
3175 FROM mtl_object_genealogy mog
3176 WHERE mog.object_id = l_gen_object_id
3177
3178 -- Fix for bug 2219479. We do not allow assets that are
3179 -- a child or a parent in the future to be deactivated.
3180 -- hence the check for start_date_active is removed
3181
3182 AND sysdate <= nvl(mog.end_date_active(+), sysdate))
3183 OR EXISTS
3184 (SELECT mog.object_id
3185 FROM mtl_object_genealogy mog
3186 WHERE mog.parent_object_id = l_gen_object_id
3187 AND sysdate <= nvl(mog.end_date_active(+), sysdate));
3188
3189 exception
3190 when no_data_found then
3191 l_hr_exists := 'N';
3192 end;
3193
3194 if (l_hr_exists = 'Y') then
3195 fnd_message.set_name('EAM','EAM_HIERARCHY_EXISTS');
3196 fnd_msg_pub.add;
3197 l_result := false;
3198
3199 end if;
3200 end if;
3201
3202 -- ROUTES CHECK
3203 begin
3204 SELECT 'Y'
3205 INTO l_routes_exists
3206 FROM DUAL
3207 WHERE EXISTS
3208 (SELECT mena.network_association_id
3209 FROM mtl_eam_network_assets mena
3210 WHERE mena.maintenance_object_type =3
3211 AND mena.maintenance_object_id = p_maintenance_object_id
3212 AND sysdate >= nvl(mena.start_date_active(+), sysdate)
3213 AND sysdate <= nvl(mena.end_date_active(+), sysdate));
3214 exception
3215 when no_data_found then
3216 l_routes_exists := 'N';
3217 end;
3218
3219
3220 if (l_routes_exists = 'Y') then
3221 fnd_message.set_name('EAM','EAM_ROUTE_EXISTS');
3222 fnd_msg_pub.add;
3223 l_result := false;
3224 end if;
3225
3226 -- Check for existing work orders only for serialized items. Bug 9836939.
3227 IF l_serial_number_control_code IS NOT NULL AND l_serial_number_control_code <> 1 THEN
3228 -- WORK REQUEST AND WORK ORDER CHECK
3229 begin
3230 SELECT 'Y'
3231 INTO l_wo_exists
3232 FROM DUAL
3233 WHERE EXISTS
3234 (SELECT wdj.wip_entity_id
3235 FROM wip_discrete_jobs wdj
3236 WHERE wdj.status_type not in (4, 5, 7, 12)
3237 AND wdj.maintenance_object_type = 3
3238 AND wdj.maintenance_object_id = p_maintenance_object_id
3239 )
3240 OR EXISTS
3241 (SELECT wewr.asset_number
3242 FROM wip_eam_work_requests wewr
3243 WHERE wewr.work_request_status_id not in (5, 6)
3244 AND wewr.maintenance_object_type = 3
3245 AND wewr.maintenance_object_id = p_maintenance_object_id);
3246 exception
3247 when no_data_found then
3248 l_wo_exists := 'N';
3249 end;
3250
3251 if (l_wo_exists = 'Y') then
3252 fnd_message.set_name('EAM','EAM_WO_EXISTS');
3253 fnd_msg_pub.add;
3254 l_result := false;
3255 end if;
3256
3257 END IF; -- End if block for serial number control check.
3258
3259 -- ROUTES CHECK: Route assets cannot be de-activated
3260
3261 begin
3262 select network_asset_flag into l_network_asset_flag
3263 from csi_item_instances
3264 where instance_id = p_maintenance_object_id;
3265
3266 exception
3267 when no_data_found then
3268 l_network_asset_flag := 'N';
3269 end;
3270
3271 if (l_network_asset_flag = 'Y') then
3272 fnd_message.set_name('EAM','EAM_ROUTE_DEACTIVATE');
3273 fnd_msg_pub.add;
3274 l_result := false;
3275 end if;
3276
3277 return l_result;
3278 end check_deactivate;
3279
3280
3281 FUNCTION get_parent_asset(p_parent_job_id in number,
3282 p_organization_id in number)
3283 return VARCHAR2 IS
3284 l_parent_asset_number VARCHAR2(80);
3285
3286 begin
3287 select cii.instance_number
3288 into l_parent_asset_number
3289 from csi_item_instances cii, wip_discrete_jobs wdj
3290 where wdj.wip_entity_id = p_parent_job_id
3291 and wdj.organization_id = p_organization_id
3292 and wdj.maintenance_object_type = 3
3293 and wdj.maintenance_object_id = cii.instance_id;
3294
3295 return l_parent_asset_number;
3296
3297 end get_parent_asset;
3298
3299
3300 --------------------------------------------------------------------------
3301 -- PROCEDURE --
3302 -- Adjust_WORU --
3303 -- --
3304 -- DESCRIPTION --
3305 -- --
3306 -- This API is invoked from the Gantt Workbench --
3307 -- --
3308 -- PURPOSE: --
3309 -- Oracle Applications Rel 12 --
3310 -- --
3311 -- HISTORY: --
3312 -- 06/27/05 Anju Gupta Created --
3313 ----------------------------------------------------------------------------
3314 PROCEDURE write_WORU (
3315 P_WIP_ENTITY_ID IN NUMBER
3316 ,P_ORGANIZATION_ID IN NUMBER
3317 ,P_OPERATION_SEQ_NUM IN NUMBER
3318 ,P_RESOURCE_SEQ_NUM IN NUMBER
3319 ,P_UPDATE_HIERARCHY IN VARCHAR2
3320 ,P_START IN DATE
3321 ,P_END IN DATE
3322 ,P_DELTA IN NUMBER
3323 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
3324 ,X_MSG_COUNT OUT NOCOPY NUMBER
3325 ,X_MSG_DATA OUT NOCOPY VARCHAR2
3326 )
3327 is
3328 l_stmt_num number := 0;
3329 l_return_status varchar2(1);
3330 l_msg_count number;
3331 l_msg_data varchar2(20000);
3332
3333
3334 begin
3335 -- Standard Start of API savepoint
3336 SAVEPOINT write_woru;
3337
3338 -- Initialize API return status to success
3339 l_stmt_num := 10;
3340 x_return_status := fnd_api.g_ret_sts_success;
3341
3342 -- API body
3343
3344 l_stmt_num := 20;
3345 if (nvl(p_update_hierarchy, 'N') = 'N') then
3346
3347 if (p_operation_seq_num is null AND p_resource_seq_num is null) then
3348 update wip_operation_resource_usage
3349 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3350 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3351 where wip_entity_id = p_wip_entity_id
3352 and organization_id = p_organization_id;
3353
3354 elsif (p_operation_seq_num is not null AND p_resource_seq_num is null) then
3355 update wip_operation_resource_usage
3356 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3357 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3358 where wip_entity_id = p_wip_entity_id
3359 and organization_id = p_organization_id
3360 and operation_seq_num = p_operation_seq_num;
3361
3362 else
3363 update wip_operation_resource_usage
3364 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3365 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3366 where wip_entity_id = p_wip_entity_id
3367 and organization_id = p_organization_id
3368 and operation_seq_num = p_operation_seq_num
3369 and resource_seq_num = p_resource_seq_num;
3370
3371 end if;
3372 null;
3373
3374 else
3375 if (p_operation_seq_num is null AND p_resource_seq_num is null) then
3376 update wip_operation_resource_usage
3377 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3378 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3379 where wip_entity_id in (select p_wip_entity_id from dual
3380 union
3381 select child_object_id from wip_sched_relationships
3382 where relationship_type = 1
3383 start with parent_object_id = p_wip_entity_id
3384 connect by prior child_object_id = parent_object_id )
3385 and organization_id = p_organization_id;
3386
3387 elsif (p_operation_seq_num is not null AND p_resource_seq_num is null) then
3388 update wip_operation_resource_usage
3389 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3390 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3391 where wip_entity_id in (select p_wip_entity_id from dual
3392 union
3393 select child_object_id from wip_sched_relationships
3394 where relationship_type = 1
3395 start with parent_object_id = p_wip_entity_id
3396 connect by prior child_object_id = parent_object_id )
3397 and organization_id = p_organization_id;
3398
3399 else
3400 update wip_operation_resource_usage
3401 set start_date = decode(p_delta, null, p_start, start_date + p_delta),
3402 completion_date = decode(p_delta, null, p_end, completion_date + p_delta)
3403 where wip_entity_id in (select p_wip_entity_id from dual
3404 union
3405 select child_object_id from wip_sched_relationships
3406 where relationship_type = 1
3407 start with parent_object_id = p_wip_entity_id
3408 connect by prior child_object_id = parent_object_id )
3409 and organization_id = p_organization_id;
3410
3411 end if;
3412 null;
3413
3414 end if;
3415
3416
3417
3418
3419 EXCEPTION
3420
3421 WHEN fnd_api.g_exc_error THEN
3422 ROLLBACK TO adjust_woru;
3423 x_return_status := fnd_api.g_ret_sts_error;
3424
3425 WHEN fnd_api.g_exc_unexpected_error THEN
3426 ROLLBACK TO adjust_woru;
3427 x_return_status := fnd_api.g_ret_sts_unexp_error;
3428
3429 WHEN OTHERS THEN
3430 ROLLBACK TO adjust_woru;
3431 x_return_status := fnd_api.g_ret_sts_unexp_error;
3432
3433
3434
3435
3436
3437 end write_woru;
3438
3439
3440 PROCEDURE Adjust_WORU (
3441 P_API_VERSION IN NUMBER
3442 ,P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE
3443 ,P_COMMIT IN VARCHAR2 := FND_API.G_FALSE
3444 ,P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3445 ,P_WIP_ENTITY_ID IN NUMBER
3446 ,P_ORGANIZATION_ID IN NUMBER
3447 ,P_OPERATION_SEQ_NUM IN NUMBER
3448 ,P_RESOURCE_SEQ_NUM IN NUMBER
3449 ,P_DELTA IN NUMBER
3450 ,P_UPDATE_HIERARCHY IN VARCHAR2
3451 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
3452 ,X_MSG_COUNT OUT NOCOPY NUMBER
3453 ,X_MSG_DATA OUT NOCOPY VARCHAR2
3454 )
3455 is
3456 l_api_name CONSTANT VARCHAR2(30) := 'adjust_woru';
3457 l_api_version CONSTANT NUMBER := 1.0;
3458 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3459 l_woru_count number := 0;
3460 l_instance_id number;
3461 l_stmt_num number := 0;
3462 l_start_date date;
3463 l_return_status varchar2(1);
3464 l_msg_count number;
3465 l_msg_data varchar2(20000);
3466 l_min_woru_start_date date;
3467 l_max_woru_end_date date;
3468 l_end_date date;
3469 l_wor_start_date date;
3470 l_wor_end_date date;
3471 SHRINK_WITH_ASSIGNMENTS EXCEPTION;
3472 l_woru_duration number;
3473 l_wor_duration number;
3474 l_instance_count number;
3475
3476 CURSOR c_woru(p_wip_entity_id number,
3477 p_organization_id number,
3478 p_operation_seq_num number,
3479 p_resource_seq_num number) is
3480 select woru.start_date, woru.completion_date, woru.instance_id
3481 from wip_operation_resource_usage woru
3482 where woru.wip_entity_id = p_wip_entity_id
3483 and woru.operation_seq_num = p_operation_seq_num
3484 and woru.resource_seq_num = p_resource_seq_num
3485 and woru.organization_id = p_organization_id;
3486
3487 begin
3488 -- Standard Start of API savepoint
3489 SAVEPOINT adjust_woru;
3490
3491 -- Standard call to check for call compatibility.
3492 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3493 RAISE fnd_api.g_exc_unexpected_error;
3494 END IF;
3495
3496 -- Initialize message list if p_init_msg_list is set to TRUE.
3497 IF fnd_api.to_boolean(p_init_msg_list) THEN
3498 fnd_msg_pub.initialize;
3499 END IF;
3500
3501 -- Initialize API return status to success
3502 l_stmt_num := 10;
3503 x_return_status := fnd_api.g_ret_sts_success;
3504
3505 -- API body
3506 -- Figure out if it is a move or resize
3507
3508 if nvl(p_delta, 0) <> 0 then
3509 l_stmt_num := 20;
3510 --its a move. Adjust all the rows in WORU
3511 write_woru(P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
3512 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
3513 P_OPERATION_SEQ_NUM => P_OPERATION_SEQ_NUM,
3514 P_RESOURCE_SEQ_NUM => P_RESOURCE_SEQ_NUM,
3515 P_UPDATE_HIERARCHY => P_UPDATE_HIERARCHY,
3516 P_START => null,
3517 P_END => null,
3518 P_DELTA => P_DELTA,
3519 X_RETURN_STATUS => l_return_status,
3520 X_MSG_COUNT => l_msg_count,
3521 X_MSG_DATA => l_msg_data);
3522
3523 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3524 RAISE FND_API.G_EXC_ERROR ;
3525 end if;
3526
3527 else
3528 l_stmt_num := 30;
3529 -- Figure out if the WOR still encompasses WORU
3530 --!!!!!When p_delta is null, both operation_seq and resource_seq are
3531 --passed.
3532
3533 BEGIN
3534
3535 select min(WORU.start_date), max(WORU.completion_date)
3536 into l_min_woru_start_date, l_max_woru_end_date
3537 from wip_operation_resource_usage woru
3538 where woru.wip_entity_id = p_wip_entity_id
3539 and woru.operation_seq_num = p_operation_seq_num
3540 and woru.resource_seq_num = p_resource_seq_num
3541 and woru.organization_id = p_organization_id;
3542
3543 l_stmt_num := 40;
3544
3545 select wor.start_date, wor.completion_date
3546 into l_wor_start_date, l_wor_end_date
3547 from wip_operation_resources wor
3548 where wor.wip_entity_id = p_wip_entity_id
3549 and wor.operation_seq_num = p_operation_seq_num
3550 and wor.resource_seq_num = p_resource_seq_num
3551 and wor.organization_id = p_organization_id;
3552
3553
3554 EXCEPTION
3555
3556 WHEN NO_DATA_FOUND THEN
3557 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3558 THEN
3559 FND_MSG_PUB.add_exc_msg
3560 ( 'EAM_COMMON_UTILITIES_PVT'
3561 , '.Adjust_WORU : Statement -'||to_char(l_stmt_num)
3562 );
3563 END IF;
3564 RAISE fnd_api.g_exc_unexpected_error;
3565
3566 END;
3567
3568 l_stmt_num := 50;
3569
3570 if (l_wor_start_date <= l_min_woru_start_date ) then
3571 --WORU is still encompassed by WOR.
3572 --It is an expand of WOR dates
3573 --Update the rows in WORU where instance_id is null
3574 update wip_operation_resource_usage woru
3575 set start_date = l_wor_start_date
3576 where woru.wip_entity_id = p_wip_entity_id
3577 and woru.operation_seq_num = p_operation_seq_num
3578 and woru.resource_seq_num = p_resource_seq_num
3579 and woru.organization_id = p_organization_id
3580 and WORU.start_date = l_min_woru_start_date
3581 and woru.instance_id is null;
3582 /* if ( l_wor_end_date < l_max_woru_end_date) then
3583 return;
3584 end if;
3585 In case resource is rescheduled by moving the bar with out changing the duration it should not return.
3586 Hence commented, #6159641
3587 */
3588 end if;
3589 if ( l_wor_end_date >= l_max_woru_end_date) then
3590 --WORU is still encompassed by WOR.
3591 --It is an expand of WOR dates
3592 --Update the rows in WORU where instance_id is null
3593 update wip_operation_resource_usage woru
3594 set completion_date = l_wor_end_date
3595 where woru.wip_entity_id = p_wip_entity_id
3596 and woru.operation_seq_num = p_operation_seq_num
3597 and woru.resource_seq_num = p_resource_seq_num
3598 and woru.organization_id = p_organization_id
3599 and WORU.completion_date = l_max_woru_end_date
3600 and woru.instance_id is null;
3601
3602 /* return;
3603 In case resource is rescheduled by moving the bar with out changing the duration it should not return.
3604 Hence commented, #6159641
3605 */
3606 end if;
3607
3608
3609 --Calculate duration
3610 --If WORU duration is lesser than WOR duration, then its a shrink, otherwise it is a move
3611 --there might be a corner case, where the Resource is moved, but p_delta is not passed
3612 --Note we've already checked for WORU being encompassed, so this logic will hold
3613
3614 /*Added # 6159641, to update woru correctly when resource rescheduled by moving the bar with out
3615 changing duration, query woru again to get the recently updated data*/
3616
3617 select min(WORU.start_date), max(WORU.completion_date)
3618 into l_min_woru_start_date, l_max_woru_end_date
3619 from wip_operation_resource_usage woru
3620 where woru.wip_entity_id = p_wip_entity_id
3621 and woru.operation_seq_num = p_operation_seq_num
3622 and woru.resource_seq_num = p_resource_seq_num
3623 and woru.organization_id = p_organization_id;
3624
3625 /*--Code added, end #6159641---*/
3626
3627 l_woru_duration := l_max_woru_end_date - l_min_woru_start_date;
3628 l_wor_duration := l_wor_end_date - l_wor_start_date;
3629
3630 select count(*)
3631 into l_instance_count
3632 from wip_operation_resource_usage woru
3633 where woru.wip_entity_id = p_wip_entity_id
3634 and woru.operation_seq_num = p_operation_seq_num
3635 and woru.resource_seq_num = p_resource_seq_num
3636 and woru.organization_id = p_organization_id
3637 and (woru.instance_id is not null
3638 or woru.serial_number is not null);
3639
3640 if l_wor_duration < l_woru_duration then
3641 l_stmt_num := 60;
3642
3643 if (l_instance_count <> 0) then
3644 RAISE SHRINK_WITH_ASSIGNMENTS;
3645 end if;
3646 end if;
3647
3648 l_stmt_num := 70;
3649 if (l_wor_duration > l_woru_duration or (l_wor_duration < l_woru_duration and l_instance_count = 0)) then
3650 --no instances, only WORU rows that represent the Resource duration
3651 --Adjust these rows
3652
3653
3654 FOR c_woru_rec IN c_woru(p_wip_entity_id,
3655 p_organization_id,
3656 p_operation_seq_num,
3657 p_resource_seq_num) LOOP
3658
3659 if (l_wor_start_date <= c_woru_rec.start_date and l_wor_end_date >= c_woru_rec.start_date) then
3660 l_start_date := c_woru_rec.start_date;
3661 else
3662 l_start_date := l_wor_start_date;
3663 end if;
3664
3665 l_end_date := l_start_date + (c_woru_rec.completion_date - c_woru_rec.start_date);
3666
3667 if l_end_date > l_wor_end_date then
3668 l_end_date := l_wor_end_date;
3669 end if;
3670
3671 l_stmt_num := 80;
3672
3673 write_woru(P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
3674 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
3675 P_OPERATION_SEQ_NUM => P_OPERATION_SEQ_NUM,
3676 P_RESOURCE_SEQ_NUM => P_RESOURCE_SEQ_NUM,
3677 P_UPDATE_HIERARCHY => P_UPDATE_HIERARCHY,
3678 P_START => l_start_date,
3679 P_END => l_end_date,
3680 P_DELTA => null,
3681 X_RETURN_STATUS => l_return_status,
3682 X_MSG_COUNT => l_msg_count,
3683 X_MSG_DATA => l_msg_data);
3684
3685 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3686 RAISE FND_API.G_EXC_ERROR ;
3687 end if;
3688
3689 END LOOP;
3690
3691 end if;
3692 end if;
3693
3694
3695 -- End of API body.
3696 -- Standard check of p_commit.
3697 IF fnd_api.to_boolean(p_commit) THEN
3698 COMMIT WORK;
3699 END IF;
3700
3701 -- Standard call to get message count and if count is 1, get message info.
3702 fnd_msg_pub.count_and_get(
3703 p_count => x_msg_count
3704 ,p_data => x_msg_data);
3705
3706
3707 EXCEPTION
3708 WHEN SHRINK_WITH_ASSIGNMENTS THEN
3709 rollback to adjust_woru;
3710 x_return_status := fnd_api.g_ret_sts_error;
3711 fnd_msg_pub.count_and_get(
3712 p_count => x_msg_count
3713 ,p_data => x_msg_data);
3714
3715 WHEN fnd_api.g_exc_error THEN
3716 ROLLBACK TO adjust_woru;
3717 x_return_status := fnd_api.g_ret_sts_error;
3718 fnd_msg_pub.count_and_get(
3719 p_count => x_msg_count
3720 ,p_data => x_msg_data);
3721 WHEN fnd_api.g_exc_unexpected_error THEN
3722 ROLLBACK TO adjust_woru;
3723 x_return_status := fnd_api.g_ret_sts_unexp_error;
3724 fnd_msg_pub.count_and_get(
3725 p_count => x_msg_count
3726 ,p_data => x_msg_data);
3727 WHEN OTHERS THEN
3728 ROLLBACK TO adjust_woru;
3729 x_return_status := fnd_api.g_ret_sts_unexp_error;
3730
3731 IF fnd_msg_pub.check_msg_level(
3732 fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3733 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
3734 END IF;
3735
3736 fnd_msg_pub.count_and_get(
3737 p_count => x_msg_count
3738 ,p_data => x_msg_data);
3739
3740 end adjust_woru;
3741
3742 FUNCTION get_asset_area( p_instance_id NUMBER, p_maint_org_id NUMBER) RETURN VARCHAR2
3743 IS
3744 CURSOR get_area IS
3745 SELECT el.location_codes
3746 FROM eam_org_maint_defaults eomd,
3747 mtl_eam_locations el
3748 WHERE eomd.area_id = el.location_id
3749 AND eomd.object_type = 50
3750 AND eomd.object_id = p_instance_id
3751 AND eomd.organization_id = p_maint_org_id;
3752
3753 l_area_code MTL_EAM_LOCATIONS.LOCATION_CODES%TYPE;
3754 BEGIN
3755 OPEN get_area;
3756 FETCH get_area INTO l_area_code;
3757 CLOSE get_area;
3758
3759 RETURN l_area_code;
3760 END get_asset_area;
3761
3762
3763 PROCEDURE set_profile(
3764 name in varchar2,
3765 value in varchar2
3766 )
3767 is
3768
3769 begin
3770 fnd_profile.put(name, value);
3771 end set_profile;
3772
3773 Function is_active(
3774 p_instance_id number
3775 ) return varchar2
3776 is
3777 l_active_start_date date;
3778 l_active_end_date date;
3779 l_return_value varchar2(1);
3780 begin
3781
3782
3783 select active_start_date,active_end_date
3784 into l_active_start_date,l_active_end_date
3785 from csi_item_instances
3786 where instance_id = p_instance_id;
3787
3788 if (l_active_start_date > sysdate) then
3789 l_return_value := 'N';
3790 elsif (l_active_start_date <= sysdate and l_active_end_date is null) then
3791 l_return_value := 'Y';
3792 elsif (l_active_start_date <= sysdate and l_active_end_date < sysdate) then
3793 l_return_value := 'N';
3794 elsif (l_active_start_date <= sysdate and l_active_end_date > sysdate) then
3795 l_return_value := 'Y';
3796 end if;
3797
3798 return l_return_value;
3799 exception
3800 when no_data_found then
3801 l_return_value := 'N';
3802
3803 end is_active;
3804
3805 FUNCTION showCompletionFields( p_wip_entity_id NUMBER ) RETURN VARCHAR2
3806 IS
3807 CURSOR get_wo_details IS
3808 SELECT organization_id,
3809 maintenance_object_type,
3810 maintenance_object_id
3811 FROM wip_discrete_jobs
3812 WHERE wip_entity_id = p_wip_entity_id;
3813
3814 l_org_id wip_discrete_jobs.organization_id%TYPE;
3815 l_maint_object_id wip_discrete_jobs.maintenance_object_type%TYPE;
3816 l_maint_object_type wip_discrete_jobs.maintenance_object_id%TYPE;
3817
3818 l_obj_exists NUMBER := 0;
3819 l_return_status VARCHAR2(1) := 'N';
3820 BEGIN
3821 OPEN get_wo_details;
3822 FETCH get_wo_details INTO l_org_id, l_maint_object_type, l_maint_object_id;
3823 CLOSE get_wo_details;
3824
3825 IF l_maint_object_type IS NULL THEN
3826 return l_return_status;
3827 ELSIF l_maint_object_type = 2 THEN
3828
3829 select count(1) into l_obj_exists
3830 from mtl_system_items_b_kfv msik
3831 where msik.inventory_item_id = l_maint_object_id
3832 AND msik.organization_id = l_org_id;
3833
3834 IF l_obj_exists = 1 THEN
3835 l_return_status := 'Y';
3836 END IF;
3837
3838 ELSIF l_maint_object_type = 3 THEN
3839
3840 select count(1) into l_obj_exists
3841 from csi_item_instances cii,
3842 mtl_serial_numbers msn
3843 where cii.serial_number = msn.serial_number
3844 and cii.inventory_item_id = msn.inventory_item_id
3845 and cii.instance_id = l_maint_object_id
3846 and cii.last_vld_organization_id = l_org_id
3847 and msn.current_status = 4
3848 and nvl(cii.network_asset_flag,'N') <> 'Y';
3849
3850 IF l_obj_exists = 1 THEN
3851 l_return_status := 'Y';
3852 END IF;
3853
3854 END IF;
3855
3856 RETURN l_return_status;
3857 END showCompletionFields;
3858
3859 PROCEDURE update_logical_asset(
3860 p_inventory_item_id number
3861 ,p_serial_number varchar2
3862 ,p_equipment_gen_object_id number
3863 ,p_network_asset_flag varchar2
3864 ,p_pn_location_id number
3865 ,x_return_status out nocopy varchar2
3866 ) is
3867 l_gen_object_id number;
3868 l_equipment_gen_object_id number;
3869 l_return_value boolean;
3870
3871 begin
3872 l_return_value := FALSE;
3873 x_return_status := fnd_api.g_ret_sts_success;
3874 l_equipment_gen_object_id := p_equipment_gen_object_id;
3875
3876 if (p_pn_location_id is not null AND p_pn_location_id <> FND_API.G_MISS_NUM) then
3877 l_return_value := TRUE;
3878 end if;
3879
3880 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
3881 l_return_value := TRUE;
3882 end if;
3883
3884
3885 if (l_return_value <> TRUE AND l_equipment_gen_object_id IS NOT NULL
3886 AND l_equipment_gen_object_id <> FND_API.G_MISS_NUM) then
3887 begin
3888 select msn.gen_object_id
3889 into l_gen_object_id
3890 from mtl_serial_numbers msn
3891 where msn.serial_number = p_serial_number
3892 and msn.inventory_item_id = p_inventory_item_id
3893 ;
3894
3895 if (l_equipment_gen_object_id <> l_gen_object_id) then
3896 l_return_value := TRUE;
3897 end if;
3898 exception
3899 when others then
3900 x_return_status := fnd_api.g_ret_sts_unexp_error;
3901 end;
3902 end if;
3903
3904 if (l_return_value = TRUE) then
3905
3906 begin
3907 update mtl_serial_numbers
3908 set group_mark_id = 1
3909 where serial_number = p_serial_number
3910 and inventory_item_id = p_inventory_item_id;
3911 exception
3912 when others then
3913 x_return_status := fnd_api.g_ret_sts_unexp_error;
3914 end;
3915 end if;
3916
3917 begin
3918 update mtl_serial_numbers
3919 set eam_linear_location_id = -1
3920 where serial_number = p_serial_number
3921 and inventory_item_id = p_inventory_item_id;
3922
3923 exception
3924 when others then
3925 x_return_status := fnd_api.g_ret_sts_unexp_error;
3926 end;
3927
3928
3929 end update_logical_asset;
3930
3931
3932 FUNCTION get_scheduled_start_date( p_wip_entity_id NUMBER ) RETURN DATE
3933 IS
3934
3935 l_scheduled_start_date Date ;
3936
3937 BEGIN
3938 SELECT MIN(scheduled_start_date)
3939 INTO l_scheduled_start_date
3940 FROM (
3941 select scheduled_start_date
3942 FROM WIP_DISCRETE_JOBS wdj_child
3943 WHERE wdj_child.wip_entity_id= p_wip_entity_id
3944 union all
3945 SELECT scheduled_start_date
3946 FROM WIP_DISCRETE_JOBS wdj_child
3947 where wdj_child.wip_entity_id
3948 IN (SELECT child_object_id
3949 FROM eam_wo_relationships
3950 WHERE parent_relationship_type =1
3951 START WITH parent_object_id = p_wip_entity_id
3952 AND parent_relationship_type = 1
3953 CONNECT BY parent_object_id = prior child_object_id
3954 AND parent_relationship_type = 1 ) ) ;
3955
3956 RETURN(l_scheduled_start_date);
3957
3958 END get_scheduled_start_date;
3959
3960 FUNCTION get_scheduled_completion_date( p_wip_entity_id NUMBER ) RETURN DATE
3961 IS
3962
3963 l_scheduled_completion_date Date ;
3964
3965 BEGIN
3966 SELECT MAX(scheduled_completion_date)
3967 INTO l_scheduled_completion_date
3968 FROM (
3969 SELECT scheduled_completion_date
3970 FROM WIP_DISCRETE_JOBS wdj_child
3971 WHERE wdj_child.wip_entity_id=p_wip_entity_id
3972 union all
3973 Select Scheduled_completion_date
3974 from WIP_DISCRETE_JOBS wdj_child
3975 where wdj_child.wip_entity_id
3976 IN (SELECT child_object_id
3977 FROM eam_wo_relationships
3978 WHERE parent_relationship_type =1
3979 START WITH parent_object_id = p_wip_entity_id
3980 AND parent_relationship_type = 1
3981 CONNECT BY parent_object_id = prior child_object_id
3982 AND parent_relationship_type = 1));
3983
3984 RETURN(l_scheduled_completion_date);
3985
3986 END get_scheduled_completion_date;
3987
3988
3989 END EAM_COMMON_UTILITIES_PVT;