1 PACKAGE BODY PO_SUBINVENTORIES_S AS
2 /* $Header: POXCOS1B.pls 115.9 2002/11/25 23:38:39 sbull ship $*/
3
4 /* create client package body */
5 /*
6 PACKAGE BODY PO_SUBINVENTORIES_S IS
7 */
8
9 /*===========================================================================
10
11 FUNCTION NAME: val_subinventory(...)
12
13 ===========================================================================*/
14
15 FUNCTION val_subinventory
16 (
17 x_subinventory IN VARCHAR2,
18 x_organization_id IN NUMBER,
19 x_transaction_date IN DATE,
20 x_item_id IN NUMBER,
21 x_destination_type IN VARCHAR2
22 )
23 RETURN NUMBER IS
24 -- 0 for success, non 0 for failure
25
26 x_progress VARCHAR2(3) := '';
27 x_status VARCHAR2(20) := '';
28 x_restrict_sub NUMBER := 0;
29
30 BEGIN
31 x_progress := '000';
32 /* Commented DBMS_OUTPUT.PUT_LINE for bug 1555260 */
33 -- dbms_output.put_line ('val_subinventory : x_subinventory : ' ||
34 -- x_subinventory);
35 -- dbms_output.put_line ('val_subinventory : x_organization_id : ' ||
36 -- TO_CHAR(x_organization_id));
37 -- dbms_output.put_line ('val_subinventory : x_transaction_date : ' ||
38 -- TO_CHAR(x_transaction_date));
39 -- dbms_output.put_line ('val_subinventory : x_item_id : ' ||
40 -- TO_CHAR(x_item_id));
41 -- dbms_output.put_line ('val_subinventory : x_destination_type : ' ||
42 -- x_destination_type);
43
44 /* no subinventory required, this function really should not be called.
45 ** just one simple check anyway.
46 */
47 IF x_destination_type <> 'INVENTORY' THEN
48 RETURN 3;
49 END IF;
50
51 /* destination type is inventory, but not subinventories is given. */
52 IF x_subinventory IS NULL THEN
53 RETURN 3;
54 END IF;
55
56 /* check sub's inactive date */
57 BEGIN
58
59 x_progress := '010';
60 SELECT 'sub_valid'
61 INTO x_status
62 FROM MTL_SECONDARY_INVENTORIES
63 WHERE SECONDARY_INVENTORY_NAME = x_subinventory
64 AND ORGANIZATION_ID = x_organization_id
65 AND NVL(DISABLE_DATE, x_transaction_date+1) > x_transaction_date;
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN x_status := 'bad_sub';
69 WHEN OTHERS THEN
70 po_message_s.sql_error('val_subinventory', x_progress, sqlcode);
71 RAISE;
72 END;
73
74
75 -- dbms_output.put_line ('val_subinventory : x_status : ' ||
76 -- x_status);
77
78 IF x_status <> 'sub_valid' THEN
79 RETURN 3;
80 END IF;
81
82 /* varify if the given item has restricted sub control */
83 x_progress := '020';
84 SELECT RESTRICT_SUBINVENTORIES_CODE
85 INTO x_restrict_sub
86 FROM MTL_SYSTEM_ITEMS
87 WHERE INVENTORY_ITEM_ID = x_item_id
88 AND ORGANIZATION_ID = x_organization_id;
89
90 -- dbms_output.put_line ('val_subinventory : x_restrict_sub : ' ||
91 -- x_restrict_sub);
92
93 /* check mfg_lookups for lookup_type = 'RESTRICT_SUBINVENTORIES_CODE' */
94
95 /* check the given sub is in the restricted list. */
96 IF x_restrict_sub = 1 THEN
97 x_progress := '030';
98
99 BEGIN
100
101 SELECT 'sub_ok'
102 INTO x_status
103 FROM MTL_ITEM_SUB_INVENTORIES
104 WHERE INVENTORY_ITEM_ID = x_item_id
105 AND SECONDARY_INVENTORY = x_subinventory
106 AND ORGANIZATION_ID = x_organization_id;
107
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN x_status := 'bad_sub';
110 WHEN OTHERS THEN
111 po_message_s.sql_error('val_subinventory', x_progress, sqlcode);
112 RAISE;
113 END;
114
115 IF x_status <> 'sub_ok' THEN
116 RETURN 3;
117 END IF;
118
119 END IF;
120
121 RETURN 0;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 po_message_s.sql_error('val_subinventory', x_progress, sqlcode);
126 RAISE;
127 END val_subinventory;
128
129 /*===========================================================================
130
131 FUNCTION NAME: val_locator(...)
132
133 ===========================================================================*/
134
135 FUNCTION val_locator
136 (
137 x_locator IN NUMBER,
138 x_item_id IN NUMBER,
139 x_subinventory IN VARCHAR2,
140 x_organization_id IN NUMBER
141 )
142 RETURN NUMBER IS
143
144 x_progress VARCHAR2(3) := NULL;
145 x_status VARCHAR2(20) := NULL;
146 x_locator_control NUMBER := 0;
147 x_restrict_loc NUMBER := 0;
148
149 BEGIN
150 x_progress := '000';
151
152 po_subinventories_s.get_locator_control (
153 x_organization_id,
154 x_subinventory,
155 x_item_id,
156 x_locator_control
157 );
158
159 /* if no locator control, simply return ok */
160 IF x_locator_control = 1 THEN /* No locator control */
161 RETURN 0;
162 END IF;
163
164 /* if use prespecified locators */
165 IF x_locator_control = 2 THEN
166 x_progress := '010';
167 SELECT 'pre_loc_ok'
168 INTO x_status
169 FROM MTL_ITEM_LOCATIONS
170 WHERE INVENTORY_LOCATION_ID = x_locator
171 AND ORGANIZATION_ID = x_organization_id
172 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE;
173
174 IF x_status <> 'pre_loc_ok' THEN
175 RETURN 2;
176 END IF;
177
178 /* varify if the given item has restricted loc control */
179 x_progress := '020';
180 SELECT RESTRICT_LOCATORS_CODE
181 INTO x_restrict_loc
182 FROM MTL_SYSTEM_ITEMS
183 WHERE INVENTORY_ITEM_ID = x_item_id
184 AND ORGANIZATION_ID = x_organization_id;
185 /* check mfg_lookups for lookup_type = 'RESTRICT_LOCATORS_CODE' */
186
187 /* if an item under restricted loc control, locator must be defined
188 ** in mtl_secondary_locators.
189 */
190 IF x_restrict_loc = 1 THEN
191 x_progress := '030';
192 SELECT 'restrict_ok'
193 INTO x_status
194 FROM MTL_SECONDARY_LOCATORS
195 WHERE INVENTORY_ITEM_ID = x_item_id
196 AND ORGANIZATION_ID = x_organization_id
197 AND SECONDARY_LOCATOR = x_locator;
198
199 IF x_status <> 'restrict_ok' THEN
200 RETURN 2;
201 END IF;
202 END IF;
203
204 RETURN 0;
205 END IF;
206
207 /* Dynamic locator entry allowed */
208 IF x_locator_control = 3 THEN
209 RETURN 100; /* inv_locator.add_locator(x_locator); */
210 END IF;
211
212 RETURN 1;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 po_message_s.sql_error('val_locator', x_progress, sqlcode);
217 RAISE;
218 END val_locator;
219
220 /*===========================================================================
221
222 PROCEDURE NAME: get_locator_control(...)
223
224 1 - No locator control
225 2 - Prespecified locator control
226 3 - Dynamic entry locator control
227 4 - Locator control determined at subinventory level
228 5 - Locator control determined at item level
229 check mfg_lookups table with lookup_type = 'MTL_ITEM_LOCATOR_CONTROL' and
230 'MTL_LOCATION_CONTROL'.
231
232 ===========================================================================*/
233
234 PROCEDURE get_locator_control
235 (
236 x_organization_id IN NUMBER,
237 x_subinventory IN VARCHAR2,
238 x_item_id IN NUMBER,
239 x_locator IN OUT NOCOPY NUMBER,
240 x_restrict_locator IN OUT NOCOPY NUMBER
241 ) IS
242
243 x_progress VARCHAR2(3) := NULL;
244 x_status VARCHAR2(20) := NULL;
245 x_locator_control NUMBER := 0;
246
247 BEGIN
248
249 -- dbms_output.put_line ('get_locator_control : x_organization_id : ' ||
250 -- to_char(x_organization_id));
251 -- dbms_output.put_line ('get_locator_control : x_subinventory : ' ||
252 -- x_subinventory);
253 -- dbms_output.put_line ('get_locator_control : x_item_id : ' ||
254 -- to_char(x_item_id));
255
256 -- bug 520036
257 -- This procedure with x_restrict_locator is not being called by
258 -- any client/server procedures.
259
260 x_progress := '000';
261 /*
262 ** Check inventory installation status... may use an inventory api func
263 ** instead of selecing directly from inventory tables.
264 */
265 /* always check item restrict locator control */
266 --SELECT restrict_locators_code, location_control_code
267 --INTO x_restrict_locator, x_item_locator_control
268 --FROM mtl_system_items
269 --WHERE organization_id = x_organization_id
270 --AND inventory_item_id = x_item_id;
271
272 /* if an item under restrict locator control, set control = 2 */
273 --IF x_restrict_locator = 1 THEN
274 -- x_locator := 2;
275 --
276 -- -- dbms_output.put_line ('get_locator_control : x_locator : ' ||
277 -- to_char(x_locator));
278 --
279 -- RETURN;
280 --END IF;
281 --
282
283 /* check organization level locator control */
284 SELECT stock_locator_control_code
285 INTO x_locator
286 FROM mtl_parameters
287 WHERE organization_id = x_organization_id;
288
289 IF x_locator = 4 THEN
290
291 x_progress := '010';
292 SELECT locator_type
293 INTO x_locator
294 FROM mtl_secondary_inventories
295 WHERE organization_id = x_organization_id
296 AND secondary_inventory_name = x_subinventory;
297
298 /* debug- what if x_locator <>5, set error some how. talk to vasant */
299 END IF;
300
301 /*
302 ** bug 724495, get the restrict_locators_code
303 ** from mtl_system_items as well
304 */
305
306 IF x_locator = 5 THEN
307 x_progress := '020';
308 SELECT restrict_locators_code, location_control_code
309 INTO x_restrict_locator, x_locator
310 FROM mtl_system_items
311 WHERE organization_id = x_organization_id
312 AND inventory_item_id = x_item_id;
313 END IF;
314
315 /* debug- set error some how. talk to vasant */
316 -- dbms_output.put_line ('get_locator_control : x_locator : ' ||
317 -- to_char(x_locator));
318
319 RETURN;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 po_message_s.sql_error('get_locator_control', x_progress, sqlcode);
324 RAISE;
325
326 RETURN;
327 END get_locator_control;
328
329 PROCEDURE get_locator_control
330 (
331 x_organization_id IN NUMBER,
332 x_subinventory IN VARCHAR2,
333 x_item_id IN NUMBER,
334 x_locator IN OUT NOCOPY NUMBER
335 ) IS
336
337 x_progress VARCHAR2(3) := NULL;
338 x_status VARCHAR2(20) := NULL;
339 x_locator_control NUMBER := 0;
340 x_restrict_loc NUMBER := 0;
341
342 BEGIN
343
344 -- dbms_output.put_line ('get_locator_control : x_organization_id : ' ||
345 -- to_char(x_organization_id));
346 -- dbms_output.put_line ('get_locator_control : x_subinventory : ' ||
347 -- x_subinventory);
348 -- dbms_output.put_line ('get_locator_control : x_item_id : ' ||
349 -- to_char(x_item_id));
350
351 x_progress := '000';
352 /*
353 ** Check inventory installation status... may use an inventory api func
354 ** instead of selecing directly from inventory tables.
355 */
356
357 -- bug 520036
358 -- Default locator control from organization, subinventory and item level
359
360 /* check organization level locator control */
361 SELECT stock_locator_control_code
362 INTO x_locator
363 FROM mtl_parameters
364 WHERE organization_id = x_organization_id;
365
366 IF x_locator = 4 THEN
367
368 x_progress := '010';
369 SELECT locator_type
370 INTO x_locator
371 FROM mtl_secondary_inventories
372 WHERE organization_id = x_organization_id
373 AND secondary_inventory_name = x_subinventory;
374
375 /* debug- what if x_locator <>5, set error some how. talk to vasant */
376 END IF;
377
378 IF x_locator = 5 THEN
379 x_progress := '020';
380 SELECT location_control_code
381 INTO x_locator
382 FROM mtl_system_items
383 WHERE organization_id = x_organization_id
384 AND inventory_item_id = x_item_id;
385 END IF;
386
387 /* debug- set error some how. talk to vasant */
388 -- dbms_output.put_line ('get_locator_control : x_locator : ' ||
389 -- to_char(x_locator));
390
391 RETURN;
392
393 EXCEPTION
394 WHEN OTHERS THEN
395 po_message_s.sql_error('get_locator_control', x_progress, sqlcode);
396 RAISE;
397
398 RETURN;
399 END get_locator_control;
400
401 /*===========================================================================
402
403 FUNCTION NAME: get_default_subinventory
404
405 ===========================================================================*/
406
407 PROCEDURE get_default_subinventory
408 (
409 x_organization_id IN NUMBER,
410 x_item_id IN NUMBER,
411 x_subinventory IN OUT NOCOPY VARCHAR2
412 ) IS
413
414 X_progress VARCHAR2(3) := '000';
415
416 BEGIN
417
418
419 /* Bug 2519790 : Copy initial x_subinventory into a local variable
420 so that same value could be returned on exception. We should call
421 the SELECT statement only when x_subinventory is NULL.
422 The local variables are superfluous after adding the IF condition.
423 Removed the local variables introduced as part of earlier fix as
424 it is not required.
425 */
426
427 X_progress := '010';
428
429 IF (x_subinventory is NULL ) THEN
430
431 X_progress := '020';
432
433 SELECT mis.subinventory_code
434 INTO x_subinventory
435 FROM mtl_item_sub_defaults mis,
436 mtl_secondary_inventories msi
437 WHERE mis.inventory_item_id = x_item_id
438 AND mis.organization_id = x_organization_id
439 AND mis.default_type = 2
440 AND mis.organization_id = msi.organization_id
441 AND mis.subinventory_code = msi.secondary_inventory_name
442 AND trunc(NVL(msi.disable_date, trunc(sysdate+1))) > trunc(sysdate);
443
444 END IF ;
445
446 RETURN;
447
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 X_subinventory := NULL;
451 RETURN;
452 WHEN OTHERS THEN
453 po_message_s.sql_error('get_default_subinventory', x_progress, sqlcode);
454 RAISE;
455
456 END get_default_subinventory;
457
458 /*===========================================================================
459
460 PROCEDURE NAME: get_default_locator
461
462 ===========================================================================*/
463
464 PROCEDURE get_default_locator
465 (
466 x_organization_id IN NUMBER,
467 x_item_id IN NUMBER,
468 x_subinventory IN VARCHAR2,
469 x_locator_id IN OUT NOCOPY NUMBER
470 ) IS
471
472 X_progress VARCHAR2(3) := '000';
473
474 BEGIN
475
476 X_progress := '010';
477 SELECT mld.locator_id
478 INTO X_locator_id
479 FROM mtl_item_loc_defaults mld,
480 mtl_item_locations mil
484 AND mld.default_type = 2
481 WHERE mld.inventory_item_id = X_item_id
482 AND mld.organization_id = X_organization_id
483 AND mld.subinventory_code = X_subinventory
485 AND mld.organization_id = mil.organization_id
486 AND mld.locator_id = mil.inventory_location_id
487 AND trunc(NVL(mil.disable_date, trunc(sysdate+1))) > trunc(sysdate);
488
489 RETURN;
490
491 EXCEPTION
492 WHEN NO_DATA_FOUND THEN
493 X_locator_id := NULL;
494 RETURN;
495 WHEN OTHERS THEN
496 po_message_s.sql_error('get_default_locator', x_progress, sqlcode);
497 RAISE;
498
499 END get_default_locator;
500
501 /*===========================================================================
502
503 PROCEDURE NAME: check_sub_transfer()
504
505 ===========================================================================*/
506
507 PROCEDURE check_sub_transfer (x_source_organization_id IN NUMBER,
508 x_destination_organization_id IN NUMBER,
509 x_destination_subinventory IN NUMBER,
510 x_item_id IN NUMBER,
511 x_allow_expense_source OUT NOCOPY VARCHAR2
512 ) IS
513
514 x_asset_flag mtl_system_items.inventory_asset_flag%type;
515 x_intransit_type mtl_interorg_parameters.intransit_type%type;
516 x_dest_sub_name mtl_item_sub_defaults.subinventory_code%type;
517 x_sub_type mtl_secondary_inventories.asset_inventory%type;
518 x_progress VARCHAR2(3) := NULL;
519
520 BEGIN
521
522 x_progress := '010';
523
524
525 /* get the asset flag for the item in the destination org */
526
527 SELECT inventory_asset_flag
528 INTO x_asset_flag
529 FROM mtl_system_items
530 WHERE organization_id = x_destination_organization_id
531 AND inventory_item_id = x_item_id;
532
533 -- dbms_output.put_line('Asset flag = ' || x_asset_flag);
534
535
536 /* If the item is NOT an asset item, set allow_expense_source
537 ** to 'Y' and return , else continue.
538 */
539
540 IF (x_asset_flag = 'N') THEN
541 x_allow_expense_source := 'Y';
542 return;
543
544 END IF;
545
546 /* At this point the asset_flag is either null or 'Y'. Get
547 ** the in_transit type for the organizations involved in
548 ** the transfer if they are different.
549 */
550
551 x_progress := '020';
552
553 SELECT intransit_type
554 INTO x_intransit_type
555 FROM mtl_interorg_parameters
556 WHERE from_organization_id = x_source_organization_id
557 AND to_organization_id = x_destination_organization_id
558 AND x_source_organization_id <> x_destination_organization_id;
559
560 -- dbms_output.put_line('Intransit Type = ' || x_intransit_type);
561
562
563 /* If in_transit, set allow_expense_source to 'N' */
564
565 IF (x_intransit_type = 2) THEN
566 x_allow_expense_source := 'N';
567 return;
568
569 END IF;
570
571
572 /* Get the default destination subinventory if not provided */
573
574 IF (x_destination_subinventory is null) THEN
575
576 x_progress := '030';
577
578 SELECT subinventory_code
579 INTO x_dest_sub_name
580 FROM mtl_item_sub_defaults
581 WHERE inventory_item_id = x_item_id
582 AND organization_id = x_destination_organization_id
583 AND default_type = 2;
584
585 -- dbms_output.put_line('Default Dest Subinventory = ' || x_dest_sub_name);
586
587 ELSE
588 x_dest_sub_name := x_destination_subinventory;
589
590 END IF;
591
592 /* Get the subinventory type for the subinventory and
593 ** and destination organization
594 */
595
596 x_progress := '040';
597
598 SELECT asset_inventory
599 INTO x_sub_type
600 FROM mtl_secondary_inventories
601 WHERE organization_id = x_destination_organization_id
602 AND secondary_inventory_name = x_dest_sub_name;
603
604
605 -- dbms_output.put_line('Asset Inventory = ' || x_sub_type);
606
607
608 /* If the subinventory is an ASSET sub, set allow_expense_source
609 ** to 'N' else set allow_expense_source to 'Y'
610 */
611
612 IF (x_sub_type = 1) THEN
613 x_allow_expense_source := 'N';
614
615 ELSE
616 x_allow_expense_source := 'Y';
617
618 END IF;
619
620
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 -- dbms_output.put_line('In exception');
625 po_message_s.sql_error('po_subinventories_s.check_sub_transfer',
626 x_progress, sqlcode);
627 raise;
628
629 END check_sub_transfer;
630
631
632 /*===========================================================================
633
634 PROCEDURE NAME: test_check_sub_transfer()
635
636 ===========================================================================*/
637
638 PROCEDURE test_check_sub_transfer (x_source_organization_id IN NUMBER,
639 x_destination_organization_id IN NUMBER,
640 x_destination_subinventory IN NUMBER,
641 x_item_id IN NUMBER) IS
642
643 x_allow_expense_source VARCHAR2(1);
644
645 BEGIN
646
647 po_subinventories_s.check_sub_transfer (
648 x_source_organization_id,
649 x_destination_organization_id,
650 x_destination_subinventory,
651 x_item_id,
652 x_allow_expense_source);
653
654
658
655 -- dbms_output.put_line('Allow expense source = '|| x_allow_expense_source);
656
657 END test_check_sub_transfer;
659
660
661 /*===========================================================================
662
663 FUNCTION NAME: val_src_subinventory(...)
664
665 ===========================================================================*/
666
667 FUNCTION val_src_subinventory ( x_src_sub IN VARCHAR2,
668 x_src_org_id IN INTEGER,
669 x_dest_type IN VARCHAR2,
670 x_dest_org_id IN INTEGER,
671 x_dest_sub IN VARCHAR2,
672 x_item_id IN NUMBER
673 )
674 RETURN BOOLEAN IS
675
676 x_progress VARCHAR2(3) := '';
677 x_count NUMBER := 0;
678 x_restrict_sub mtl_system_items.restrict_subinventories_code%type;
679 x_intransit_type mtl_interorg_parameters.intransit_type%type;
680 x_allow_expense_source VARCHAR2(1) := '';
681
682 BEGIN
683
684
685 /* Return false if there isn't a source
686 ** organization specified. Note that this
687 ** routine should not be called with a null
688 ** source subinventory too. If the destination org
689 ** is cleared then this routine returns false.
690 */
691
692 IF ((x_src_org_id is null) OR
693 (x_src_sub is null) OR
694 (x_item_id is null) OR
695 (x_dest_org_id is null)) THEN
696 return (FALSE);
697
698 END IF;
699
700
701 x_progress := '010';
702
703
704 /* Validate that the src sub is active
705 ** for the source organization. Also validate
706 ** that the value of quantity tracked is 1.
707 */
708
709 SELECT count(1)
710 INTO x_count
711 FROM mtl_secondary_inventories msub
712 WHERE msub.organization_id = x_src_org_id
713 AND msub.secondary_inventory_name = x_src_sub
714 AND trunc(sysdate) < nvl(disable_date, trunc(sysdate + 1))
715 AND msub.quantity_tracked = 1;
716
717 IF (x_count = 0) THEN
718 return (FALSE);
719
720 END IF;
721
722
723 /* Verify that the subinventory is not a restricted
724 ** sub. If it is then verify that the item is valid
725 ** for the subinventory.
726 */
727
728 x_progress := '020';
729
730 SELECT msi.restrict_subinventories_code
731 INTO x_restrict_sub
732 FROM mtl_system_items msi
733 WHERE msi.inventory_item_id = x_item_id
734 AND organization_id = x_src_org_id;
735
736
737 IF x_restrict_sub = 1 THEN
738
739 x_progress := '030';
740
741 SELECT count(1)
742 INTO x_count
743 FROM mtl_item_sub_inventories mis
744 WHERE mis.inventory_item_id = x_item_id
745 AND mis.secondary_inventory = x_src_sub
746 AND mis.organization_id = x_src_org_id;
747
748 END IF;
749
750 IF (x_count = 0) THEN
751 return (FALSE);
752
753 END IF;
754
755 /* Validate that we are not sourcing and delivering
756 ** to the same subinventory. If we are then display
757 ** the message PO_RQ_SOURCE_SUB_EQS_DEST_SUB.
758 */
759
760 IF ((x_src_org_id = x_dest_org_id) AND
761 (x_src_sub = x_dest_sub)) THEN
762 po_message_s.app_error('PO_RQ_SOURCE_SUB_EQS_DEST_SUB');
763 return (FALSE);
764
765 END IF;
766
767
768 /*
769 ** Validate that if the intransit type is not 1 (not direct transfer)
770 ** for the source and destination org combination
771 ** then sourcing from an expense sub is not allowed.
772 */
773
774 x_progress := '040';
775
776 SELECT mip.intransit_type
777 INTO x_intransit_type
778 FROM mtl_interorg_parameters mip
779 WHERE mip.from_organization_id = x_src_org_id
780 AND mip.to_organization_id = x_dest_org_id;
781
782 IF (x_intransit_type <> 1) THEN
783
784 x_progress := '050';
785
786 SELECT count(1)
787 INTO x_count
788 FROM mtl_secondary_inventories msi
789 WHERE msi.secondary_inventory_name = x_src_sub
790 AND msi.asset_inventory = 2;
791
792 IF (x_count = 1) THEN
793 return (FALSE);
794
795 END IF;
796 END IF;
797
798 /* Do not allow transfer of an asset item
799 ** from an expense subinventory to an asset
800 ** subinventory for direct transfers.
801 */
802
803 x_progress := '060';
804
805 po_subinventories_s.check_sub_transfer (
806 x_src_org_id,
807 x_dest_org_id,
808 x_dest_sub,
809 x_item_id,
810 x_allow_expense_source);
811
812 IF (x_allow_expense_source <> 'Y') THEN
813
814 x_progress := '070';
815
816 SELECT count(1)
817 INTO x_count
818 FROM mtl_secondary_inventories msi
819 WHERE msi.secondary_inventory_name = x_src_sub
820 AND msi.asset_inventory = 2;
821
822 IF (x_count = 1) THEN
823 return (FALSE);
824
825 END IF;
826 END IF;
827
828 return (TRUE);
829
830 EXCEPTION
831 when no_data_found then
832 return (FALSE);
833 when others then
834 po_message_s.sql_error('val_src_subinventory', x_progress, sqlcode);
835 raise;
836
837 END val_src_subinventory;
838
839
840
841
842 /*===========================================================================
843
847 FUNCTION val_locator_control(
844 FUNCTION NAME: val_locator_control
845
846 ===========================================================================*/
848 X_to_organization_id IN NUMBER,
849 X_item_id IN NUMBER,
850 X_subinventory IN VARCHAR2,
851 X_locator_id IN NUMBER)
852 RETURN BOOLEAN IS
853
854 locator_control INTEGER := 0;
855 X_progress VARCHAR2(4) := '000';
856
857 BEGIN
858
859 /*
860 ** See if org/sub/item is under locator control
861 */
862 X_progress := '1220';
863 po_subinventories_s.get_locator_control
864 (X_to_organization_id,
865 X_subinventory,
866 X_item_id,
867 locator_control);
868
869 /*
870 ** If locator control is 2 which means it is under predefined
871 ** locator contol or 3 which means it's under dynamic (any value)
872 ** locator control then you need to go get the default locator id
873 */
874 IF (locator_control = 2 OR locator_control = 3) THEN
875
876 IF (X_locator_id IS NULL) THEN
877 RETURN FALSE;
878 END IF;
879
880 END IF; -- (locator_control = 2 OR locator_control = 3)
881
882 RETURN TRUE;
883
884 EXCEPTION
885 WHEN OTHERS THEN
886 po_message_s.sql_error('val_locator_control', X_progress, sqlcode);
887 RAISE;
888
889 END val_locator_control;
890
891
892 /*===========================================================================
893
894 PROCEDURE NAME: get_subinventory_details
895
896 ===========================================================================*/
897
898 PROCEDURE get_subinventory_details (x_subinventory IN OUT NOCOPY VARCHAR2,
899 x_organization_id IN OUT NOCOPY NUMBER,
900 x_asset_inventory IN OUT NOCOPY NUMBER
901 ) IS
902
903 x_progress VARCHAR2(3) := NULL;
904
905 BEGIN
906
907 x_progress := '010';
908
909 /* get the asset inventory column for the subinventory */
910
911 SELECT msi.asset_inventory
912 INTO x_asset_inventory
913 FROM mtl_secondary_inventories msi
914 WHERE msi.organization_id = x_organization_id
915 AND msi.secondary_inventory_name = x_subinventory;
916
917
918 -- dbms_output.put_line('Asset Inventory = ' || x_asset_inventory);
919
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 -- dbms_output.put_line('In exception');
924 po_message_s.sql_error('po_subinventories_s.get_subinventory_details',
925 x_progress, sqlcode);
926 raise;
927
928 END get_subinventory_details;
929
930
931 END PO_SUBINVENTORIES_S;
[Home] [Help]