[Home] [Help]
PACKAGE BODY: APPS.EAM_ASSET_MOVE_UTIL
Source
1 PACKAGE BODY eam_asset_move_util AS
2 /* $Header: EAMAMUTB.pls 120.12.12010000.4 2008/10/23 08:01:53 vchidura ship $ */
3
4 -- validate whether an asset under context can be moved or not (before Asset Move UI is thrown)
5 -- Also called by AssetMove() which will be called for a list of asset records
6 g_pkg_name CONSTANT VARCHAR2(30):= 'EAM_ASSET_MOVE_UTIL';
7
8 Procedure isValidMove(
9 p_instance_id IN NUMBER,
10 p_transaction_date IN DATE,
11 p_inventory_item_id IN NUMBER,
12 p_curr_org_id IN NUMBER,
13 x_return_status IN OUT NOCOPY varchar2,
14 x_return_message OUT NOCOPY varchar2
15 )
16 IS
17 isValidMove varchar2(1) := 'N';
18
19 --logging variables
20 l_api_name constant VARCHAR2(30) := 'isValidMove';
21 l_module VARCHAR2(200);
22 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
23 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
24 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
25 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
26 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
27 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
28
29
30
31 BEGIN
32
33 IF (l_ulog) THEN
34 l_module := 'eam.plsql.'|| l_full_name;
35 END IF;
36
37 IF (l_plog) THEN
38 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
39 END IF;
40
41 if(eam_asset_move_util.isOpenPeriod(p_curr_org_id,p_transaction_date)) then
42 isValidMove := 'Y';
43 else
44 isValidMove := 'N';
45 x_return_status:='N';
46 x_return_message:='EAM_INV_NO_OPEN_PERIOD';
47 Return;
48 end if;
49
50 if(NOT(eam_asset_move_util.isAssetRoute(p_instance_id))) then
51 isValidMove := 'Y';
52 else
53 isValidMove := 'N';
54 x_return_status:='N';
55 x_return_message:='EAM_ASSET_ROUTE';
56 Return;
57 End if;
58
59 if(NOT(eam_asset_move_util.hasPropMngrLink(p_instance_id))) then
60 isValidMove := 'Y';
61 else
62 isValidMove := 'N';
63 x_return_status:='N';
64 x_return_message:='EAM_ASSET_PROP_MNGR';
65 Return;
66 End if;
67
68 if(eam_asset_move_util.isTransactable(p_inventory_item_id,p_curr_org_id)) then
69 isValidMove := 'Y';
70 else
71 isValidMove := 'N';
72 x_return_status:='N';
73 x_return_message:='EAM_ASSET_NON_TRANSACT';
74 Return;
75 End if;
76
77 if(NOT(eam_asset_move_util.isInTransit( p_instance_id))) then
78 isValidMove := 'Y';
79 else
80 isValidMove := 'N';
81 x_return_status:='N';
82 x_return_message:='EAM_ASSET_IN_TRANSIT';
83 Return;
84 End if;
85
86 if(NOT(eam_asset_move_util.hasProdEquipLink(p_instance_id))) then
87 isValidMove := 'Y';
88 else
89 isValidMove := 'N';
90 x_return_status:='N';
91 x_return_message:='EAM_ASSET_PROD_EQUIP';
92 Return;
93 End if;
94
95
96 IF isValidMove = 'Y' THEN
97 x_return_status := 'Y';
98 --x_return_message:='Is valid Move';
99 Return;
100 ELSE
101 x_return_status := 'N';
102 END IF;
103
104 IF (l_plog) THEN
105 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
106 END IF;
107
108 END isValidMove;
109
110
111 PROCEDURE isValidAssetMove(
112 p_asset_hierarchy_REC IN eam_asset_move_pub.asset_move_hierarchy_REC_TYPE,
113 p_dest_org_id IN NUMBER,
114 p_counter IN NUMBER,
115 x_return_status OUT NOCOPY varchar2,
116 x_return_message OUT NOCOPY varchar2
117 )
118 IS
119 isValidMove varchar2(1) := 'N';
120 --logging variables
121 l_api_name constant VARCHAR2(30) := 'isValidAssetMove';
122 l_module VARCHAR2(200);
123 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
124 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
125 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
126 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
127 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
128 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
129
130
131 BEGIN
132 -- Dbms_Output.put_line('Processing isValidAssetMove');
133 IF (l_ulog) THEN
134 l_module := 'eam.plsql.'|| l_full_name;
135 END IF;
136 IF (l_plog) THEN
137 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name||'for'||p_asset_hierarchy_REC.instance_id);
138 END IF;
139 IF(p_counter<>1) THEN
140 IF(isInMaintOrg(p_asset_hierarchy_REC.instance_id
141 ,p_asset_hierarchy_REC.current_org_id
142 ,p_asset_hierarchy_REC.gen_object_id)) THEN
143 isValidMove := 'Y';
144 else
145 isValidMove := 'N';
146 x_return_status:='N';
147 x_return_message:='EAM_ASSET_DIFF_MAINT_ORG';
148 Return;
149 end if;
150 END IF;
151
152 --This is call to check whether the item is assigned to the Current organisation.
153 --The check for the Destination organisation is done in the EAM_ASSET_MOVE_PUB.prepareMoveAsset
154 IF(isItemAssigned(p_asset_hierarchy_REC.inventory_item_id,p_asset_hierarchy_REC.current_org_id )) THEN
155 isValidMove := 'Y';
156 else
157 isValidMove := 'N';
158 x_return_status:='N';
159 x_return_message:='EAM_ITEM_NOT_ASSIGN';
160 Return;
161 end if;
162
163 IF isValidMove = 'Y' THEN
164 -- x_return_status := 'Y';
165 -- x_return_message:='Is valid Move';
166 --Dbms_Output.put_line('calling isValidAssetMoveProcedure');
167
168 IF (l_plog) THEN
169 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name||'for'||p_asset_hierarchy_REC.instance_id);
170 END IF;
171
172 eam_asset_move_util.isValidMove(p_asset_hierarchy_REC.instance_id,
173 sysdate ,
174 p_asset_hierarchy_REC.inventory_item_id ,
175 p_asset_hierarchy_REC.CURRENT_ORG_ID ,
176 x_return_status ,
177 x_return_message );
178 ELSE
179 x_return_status := 'N';
180 Return;
181 END IF;
182
183 IF x_return_status = 'Y' THEN
184
185 --for 7370638-AMWB-MR
186 if(eam_asset_move_util.hasSubInventory(p_asset_hierarchy_REC.instance_id)) then
187 x_return_status := 'Y'; --which means it's a valid asset
188 else
189 x_return_status:='MR';--This status is used to perform the Misc. receipt into intermediate subinvenotry
190 return;
191 End if;
192
193 ELSE
194 x_return_status := 'N';
195 --for 7370638-AMWB-MR
196 Return;
197 END IF;
198
199 END isValidAssetMove;
200
201
202 FUNCTION isOpenPeriod(
203 p_organization_id IN NUMBER,
204 p_transaction_date IN DATE
205 )
206 RETURN BOOLEAN
207 IS
208 x_period_id NUMBER;
209 l_open_past_period BOOLEAN;
210
211 --logging variables
212 l_api_name constant VARCHAR2(30) := 'isOpenPeriod';
213 l_module VARCHAR2(200);
214 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
215 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
216 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
217 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
218 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
219 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
220
221
222 BEGIN
223 -- Dbms_Output.put_line('Processing isValidAssetMove');
224 IF (l_ulog) THEN
225 l_module := 'eam.plsql.'|| l_full_name;
226 END IF;
227
228 IF (l_plog) THEN
229 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
230 END IF;
231
232 --Dbms_Output.put_line('Processing isOpenPeriod');
233 l_open_past_period :=FALSE ; /*important FOR TIME being 4th parameter IS made NULL*/
234 INVTTMTX.tdatechk(p_organization_id,p_transaction_date,x_period_id,l_open_past_period);--as for time being
235
236 If (x_period_id <> 0) then
237 return TRUE;
238 else
239 return FALSE;
240 END IF;
241
242 IF (l_plog) THEN
243 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
244 END IF;
245 end isOpenPeriod;
246
247
248 FUNCTION isTransactable(
249 p_inventory_item_id IN NUMBER
250 ,p_organization_id IN NUMBER
251 )
252 RETURN BOOlEAN
253 IS
254 l_flag varchar2(30);
255 --logging variables
256 l_api_name constant VARCHAR2(30) := 'isTransactable';
257 l_module VARCHAR2(200);
258 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
259 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
260 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
261 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
262 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
263 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
264
265
266 BEGIN
267
268 IF (l_ulog) THEN
269 l_module := 'eam.plsql.'|| l_full_name;
270 END IF;
271
272 IF (l_plog) THEN
273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
274 END IF;
275 --Dbms_Output.put_line('Processing isTransactable');
276
277 BEGIN
278 select MTL_TRANSACTIONS_ENABLED_FLAG into l_flag from
279 mtl_system_items_b where
280 inventory_item_id = p_inventory_item_id
281 and organization_id = p_organization_id;
282
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 SELECT 'N' INTO l_flag FROM dual;
286 END;
287
288 if (l_flag <> 'N') then
289 RETURN TRUE;
290 ELSE RETURN FALSE;
291 END IF;
292
293
294 IF (l_plog) THEN
295 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
296 END IF;
297
298 END isTransactable;
299
300
301 FUNCTION hasSubInventory(
302 p_instance_id IN NUMBER
303 )
304 RETURN BOOLEAN
305 IS
306
307 l_subinv_code csi_item_instances.inv_subinventory_name%TYPE;
308 --logging variables
309 l_api_name constant VARCHAR2(30) := 'hasSubInventory';
310 l_module VARCHAR2(200);
311 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
312 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
313 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
314 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
315 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
316 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
317
318
319 BEGIN
320
321 IF (l_ulog) THEN
322 l_module := 'eam.plsql.'|| l_full_name;
323 END IF;
324
325 IF (l_plog) THEN
326 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
327 END IF;
328 --Dbms_Output.put_line('Processing hasSubInventory');
329
330 BEGIN
331 SELECT inv_subinventory_name into l_subinv_code
332 FROM csi_item_instances
333 WHERE instance_id = p_instance_id;
334
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 l_subinv_code := NULL;
338 END;
339
340
341
342 IF (l_subinv_code IS NOT NULL ) then
343 --Dbms_Output.put_line('l_subinv_code is: '||l_subinv_code);
344 RETURN TRUE;
345 ELSE RETURN FALSE;
346
347 END IF;
348
349 IF (l_plog) THEN
350 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
351 END IF;
352
353 END hasSubInventory;
354
355
356 FUNCTION isLocated(
357 p_instance_id IN NUMBER
358 )
359 RETURN boolean
360 IS
361
362 l_location_id NUMBER;
363 --logging variables
364 l_api_name constant VARCHAR2(30) := 'isLocated';
365 l_module VARCHAR2(200);
366 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
367 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
371 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
368 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
369 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
370 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
372
373
374 BEGIN
375
376 IF (l_ulog) THEN
377 l_module := 'eam.plsql.'|| l_full_name;
378 END IF;
379
380 IF (l_plog) THEN
381 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
382 END IF;
383 -- Dbms_Output.put_line('Processing isLocated');
384
385 BEGIN
386 select location_id
387 into l_location_id
388 from csi_item_instances WHERE
389 instance_id=P_instance_id;
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 l_location_id := NULL;
394 END;
395
396 if (l_location_id IS NOT NULL) then
397 RETURN TRUE;
398 ELSE RETURN FALSE;
399
400 END IF;
401
402 IF (l_plog) THEN
403 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
404 END IF;
405
406 END isLocated;
407
408 FUNCTION isInTransit(
409 p_instance_id IN NUMBER
410 )
411 RETURN boolean
412
413 IS
414 l_intransitFlag NUMBER;
415
416
417 --logging variables
418 l_api_name constant VARCHAR2(30) := 'isInTransit';
419 l_module VARCHAR2(200);
420 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
421 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
422 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
423 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
424 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
425 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
426
427
428 BEGIN
429
430 IF (l_ulog) THEN
431 l_module := 'eam.plsql.'|| l_full_name;
432 END IF;
433
434 IF (l_plog) THEN
435 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
436 END IF;
437 --Dbms_Output.put_line('Processing isInTransit');
438
439 BEGIN
440 select msn.current_status into l_intransitFlag from mtl_serial_numbers msn,
441 csi_item_instances cii
442 WHERE cii.INSTANCE_id=p_instance_id
443 AND cii.INVENTORY_ITEM_ID = msn.INVENTORY_ITEM_ID
444 AND cii.SERIAL_NUMBER = msn.SERIAL_NUMBER ;
445 -- AND cii.INV_ORGANIZATION_ID = msn.CURRENT_ORGANIZATION_ID ;
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 SELECT 0 INTO l_intransitFlag FROM dual;
449 END;
450
451
452 if(l_intransitFlag =5) then
453 RETURN TRUE;
454 ELSE RETURN FALSE;
455 END IF;
456
457 IF (l_plog) THEN
458 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
459 END IF;
460
461 END isInTransit;
462
463 FUNCTION isAssetRoute(
464 p_instance_id IN NUMBER
465 )
466 RETURN boolean
467 IS
468 l_network_asset_flag varchar2(1);
469 --logging variables
470 l_api_name constant VARCHAR2(30) := 'isAssetRoute';
471 l_module VARCHAR2(200);
472 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
473 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
474 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
475 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
476 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
477 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
478
479
480 BEGIN
481
482 IF (l_ulog) THEN
483 l_module := 'eam.plsql.'|| l_full_name;
484 END IF;
485
486 IF (l_plog) THEN
487 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
488 END IF;
489
490 -- Dbms_Output.put_line('Processing isAssetRoute');
491 BEGIN
492 SELECT network_asset_flag INTO l_network_asset_flag
493 FROM csi_item_instances
494 WHERE instance_id = p_instance_id;
495
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN
498 SELECT 'N' INTO l_network_asset_flag FROM dual;
499 END;
500
501
502 if (l_network_asset_flag ='Y') then
503 RETURN TRUE;
504 ELSE RETURN FALSE;
505 END IF;
506
507 IF (l_plog) THEN
508 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
509 END IF;
510 END isAssetRoute;
511
512
513 FUNCTION hasProdEquipLink(
514 p_instance_id IN NUMBER
515 )
516 RETURN boolean
517 IS
518
519 l_EQUIPMENT_GEN_OBJECT_ID NUMBER;
520 --logging variables
521 l_api_name constant VARCHAR2(30) := 'hasProdEquipLink';
522 l_module VARCHAR2(200);
523 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
524 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
528 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
525 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
526 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
527 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
529
530
531 BEGIN
532
533 IF (l_ulog) THEN
534 l_module := 'eam.plsql.'|| l_full_name;
535 END IF;
536
537 IF (l_plog) THEN
538 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
539 END IF;
540
541 --Dbms_Output.put_line('Processing hasProdEquipLink');
542
543 BEGIN
544 SELECT EQUIPMENT_GEN_OBJECT_ID INTO l_EQUIPMENT_GEN_OBJECT_ID FROM csi_item_instances WHERE instance_id=p_instance_id;
545
546 --Dbms_Output.put_line(l_EQUIPMENT_GEN_OBJECT_ID);
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 l_EQUIPMENT_GEN_OBJECT_ID := NULL;
551 END;
552
553 if(l_EQUIPMENT_GEN_OBJECT_ID IS NOT NULL) then
554 RETURN TRUE ;
555
556 ELSE
557 RETURN FALSE ;
558
559 END IF;
563 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
560 --Dbms_Output.put_line('Completed Processing hasProdEquipLink');
561
562 IF (l_plog) THEN
564 END IF;
565
566
567 END hasProdEquipLink;
568
569
570 FUNCTION hasPropMngrLink(
571 p_instance_id IN NUMBER
572 )
573 RETURN boolean
574 IS
575
576 l_location_id Number;
577 --logging variables
578 l_api_name constant VARCHAR2(30) := 'hasPropMngrLink';
579 l_module VARCHAR2(200);
580 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
581 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
582 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
583 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
584 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
585 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
586
587
588 BEGIN
589
590 IF (l_ulog) THEN
591 l_module := 'eam.plsql.'|| l_full_name;
592 END IF;
593
594 IF (l_plog) THEN
595 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
596 END IF;
597
598 --Dbms_Output.put_line('Processing hasPropMngrLink');
599
600 BEGIN
601 SELECT pn_location_id into l_location_id
602 FROM csi_item_instances
603 where instance_id=p_instance_id;
604
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 l_location_id := NULL;
608 END;
609
610 if (l_location_id is NOT NULL)
611 THEN RETURN TRUE;
612 ELSE RETURN FALSE;
613 END IF;
614
615 IF (l_plog) THEN
616 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
617 END IF;
618
619 END hasPropMngrLink;
620
621 -- isInMaintOrg(instance_id,org_id)
622 -- Assets in Diff Maint Org
623 -- Assets in Diff Prod Org which are not maintained by the current parent maint_org_id
624
625 FUNCTION isInMaintOrg(
626 p_instance_id IN NUMBER
627 ,p_organization_id IN NUMBER
628 ,p_gen_object_id IN NUMBER
629 )
630 RETURN boolean
631 IS
632
633 l_parent_object_id NUMBER ;
634 l_parent_organization_id NUMBER;
635 l_organization_id NUMBER;
636 --logging variables
637 l_api_name constant VARCHAR2(30) := 'isInMaintOrg';
638 l_module VARCHAR2(200);
639 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
640 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
641 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
642 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
643 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
644 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
645
646
647 BEGIN
648
649 IF (l_ulog) THEN
650 l_module := 'eam.plsql.'|| l_full_name;
651 END IF;
652
653 IF (l_plog) THEN
654 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
655 END IF;
656
657 --Dbms_Output.put_line('Processing isInMaintOrg');
658
659 select mp.maint_organization_id into l_organization_id
660 from MTL_PARAMETERS mp,csi_item_instances cii
661 where mp.organization_id=cii.last_vld_organization_id
662 and cii.instance_id=p_instance_id ;
666
663
664 --Dbms_Output.put_line('l_organization_id is'||l_organization_id);
665 --Dbms_Output.put_line(p_instance_id);
667 BEGIN
668 --selecting parent_object_id
669 /*
670 SELECT parent_object_id into l_parent_object_id FROM mtl_object_genealogy WHERE object_id =
671 (SELECT gen_object_id FROM mtl_serial_numbers WHERE serial_number =
672 (SELECT serial_number FROM csi_item_instances WHERE instance_id = p_instance_id)
673 )
674 AND parent_object_id IN (SELECT gen_object_id FROM mtl_serial_numbers)
675 AND START_DATE_ACTIVE<=SYSDATE
676 AND Nvl(end_DATE_ACTIVE,SYSDATE+1)>=sysdate ;
677 */ --commented for the bug 7129016
678
679 SELECT parent_object_id into l_parent_object_id
680 FROM mtl_object_genealogy
681 WHERE object_id =p_gen_object_id
682 AND parent_object_id IN (SELECT gen_object_id FROM mtl_serial_numbers)
683 AND START_DATE_ACTIVE<=SYSDATE
684 AND Nvl(end_DATE_ACTIVE,SYSDATE+1)>=sysdate ;
685
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 l_parent_object_id := NULL;
689
690 END;
691
692 --Dbms_Output.put_line('l_parent_object_id is'||l_parent_object_id) ;
693
694
695 IF(l_parent_object_id IS NULL) THEN
696 RETURN TRUE;
697 ELSE
698 --Dbms_Output.put_line('searching l_parent_organization_id');
699 select mp.maint_organization_id into l_parent_organization_id
700 from MTL_PARAMETERS mp,csi_item_instances cii, mtl_serial_numbers msn
701 where mp.organization_id=cii.last_vld_organization_id
702 and cii.serial_number=msn.serial_number
703 and cii.INVENTORY_ITEM_ID = msn.INVENTORY_ITEM_ID
704 and cii.last_vld_organization_id = msn.CURRENT_ORGANIZATION_ID
705 and msn.gen_object_id=l_parent_object_id ;
706
707 --Dbms_Output.put_line('l_parent_organization_id is'||l_parent_organization_id) ;
708
709
710 IF (l_organization_id=l_parent_organization_id) THEN
711 RETURN TRUE ;
712 ELSE
713 RETURN FALSE;
714 END IF;
715
716 END IF;
717
718 IF (l_plog) THEN
719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
720 END IF;
721
722 END isInMaintOrg;
723
724 FUNCTION isItemAssigned(
725 p_inventory_item_id IN NUMBER
726 ,p_organization_id IN NUMBER
727 )
728 RETURN boolean
729 IS
730
731 l_org_assign_chk VARCHAR2(30);
732
733 --logging variables
734 l_api_name constant VARCHAR2(30) := 'isItemAssigned';
735 l_module VARCHAR2(200);
736 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
737 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
738 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
739 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
740 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
744 BEGIN
741 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
742
743
745
746 IF (l_ulog) THEN
747 l_module := 'eam.plsql.'|| l_full_name;
748 END IF;
749
750 IF (l_plog) THEN
751 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
752 END IF;
753 --Dbms_Output.put_line('Processing isItemAssigned');
754
755 BEGIN
756 SELECT ORGANIZATION_ID
757 INTO l_org_assign_chk
758 FROM mtl_system_items_b
759 WHERE inventory_item_id =p_inventory_item_id
760 AND organization_id= p_organization_id;
761
762 EXCEPTION
763 WHEN NO_DATA_FOUND THEN
764 SELECT 'N' INTO l_org_assign_chk FROM dual;
765 END;
766
767
768
769 IF(l_org_assign_chk <>'N')
770 THEN
771 RETURN TRUE;
772 ELSE
773 RETURN FALSE;
774 END IF ;
775
776 IF (l_plog) THEN
777 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
778 END IF;
779 END isItemAssigned;
780
781
782 FUNCTION isUniqueShipmentNumber(
783 p_shipment_number IN VARCHAR2
784 )
785 RETURN boolean
786 IS
787 l_shipment_number NUMBER;
788 --logging variables
789 l_api_name constant VARCHAR2(30) := 'isUniqueShipmentNumber';
790 l_module VARCHAR2(200);
791 l_log_level CONSTANT NUMBER := fnd_log.g_current_runtime_level;
792 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level;
793 l_pLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_procedure >= l_log_level;
794 l_exLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_exception >= l_log_level;
798
795 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
796 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
797
799 BEGIN
800
801 IF (l_ulog) THEN
802 l_module := 'eam.plsql.'|| l_full_name;
803 END IF;
804
805 IF (l_plog) THEN
806 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Entering ' || l_full_name);
807 END IF;
808 --Dbms_Output.put_line('Processing isUniqueShipmentNumber');
809
810 IF((INVTTMTX.ship_number_validation(p_shipment_number))=1) THEN
811 RETURN TRUE;
812 ELSE
813 RETURN FALSE;
814 END IF;
815
816 IF (l_plog) THEN
817 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'Exiting ' || l_full_name);
818 END IF;
819 END isUniqueShipmentNumber;
820
821 FUNCTION translate_message(
822 prod IN VARCHAR2
823 ,msg IN VARCHAR2
824 )
825 RETURN VARCHAR2 IS
826 BEGIN
827 fnd_message.set_name(prod, msg);
828 return fnd_message.get;
829 END translate_message;
830
831 END eam_asset_move_util;