1 PACKAGE BODY edw_mtl_inventory_loc_pkg AS
2 /* $Header: OPIILFKB.pls 120.1 2005/06/08 05:31:38 appldev $ */
3
4 FUNCTION get_locator_fk (p_inventory_location_id IN NUMBER, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2) RETURN VARCHAR2 IS
5 v_locator_fk VARCHAR2(240) := 'NA_EDW';
6 v_instance_code edw_local_instance.instance_code%TYPE;
7 v_organization_code mtl_parameters.organization_code%TYPE;
8 BEGIN
9
10 SELECT instance_code INTO v_instance_code
11 FROM edw_local_instance;
12
13 IF p_organization_id IS NULL THEN
14 RETURN 'NA_EDW';
15 ELSE
16 SELECT mp.organization_code INTO v_organization_code
17 FROM mtl_parameters mp
18 WHERE mp.organization_id = p_organization_id;
19 IF (p_inventory_location_id IS NOT null and p_inventory_location_id<>0) THEN
20 RETURN(p_inventory_location_id ||'-'||v_organization_code||'-'||v_instance_code);
21 ELSIF (p_subinventory_code IS NOT NULL) THEN
22 RETURN(p_subinventory_code||'-'||v_organization_code||'-'||v_instance_code||'-SUBI');
23 ELSE
24 RETURN(v_organization_code||'-'||v_instance_code||'-PLNT');
25 END IF;
26 END IF;
27
28 EXCEPTION
29 WHEN no_data_found THEN
30 RETURN('NA_EDW');
31 WHEN others THEN
32 RETURN('Invalid '||to_char(p_inventory_location_id));
33 END get_locator_fk;
34
35 FUNCTION get_locator_fk (p_inventory_location_id IN NUMBER,
36 p_organization_id IN NUMBER,
37 p_subinventory_code IN VARCHAR2,
38 p_whse_loct_ctl IN NUMBER,
39 P_item_loct_ctl IN NUMBER,
40 p_location_code IN VARCHAR2,
41 p_organization_code IN VARCHAR2,
42 p_instance_code IN VARCHAR2) RETURN VARCHAR2 IS
43 v_locator_fk VARCHAR2(240) := 'NA_EDW';
44 BEGIN
45 IF p_organization_id IS NULL THEN
46 RETURN 'NA_EDW';
47 ELSIF p_location_code IS NULL THEN
48 IF (p_subinventory_code IS NOT NULL) THEN
49 RETURN(p_subinventory_code||'-'||p_organization_code||'-'||p_instance_code||'-SUBI');
50 ELSE
51 RETURN(p_organization_code||'-'||p_instance_code||'-PLNT');
52 END IF;
53 ELSIF p_location_code IS NOT NULL THEN
54 IF P_item_loct_ctl * P_whse_loct_ctl = 1
55 THEN
56 RETURN(p_inventory_location_id ||'-'||p_organization_code||'-'||p_instance_code);
57 ELSIF P_item_loct_ctl * P_whse_loct_ctl = 0
58 THEN
59 RETURN(p_location_code ||'-'||p_organization_code||'-OPMNL-'||p_instance_code);
60 ELSE
61 RETURN(p_location_code ||'-'||p_organization_code||'-OPMNV-'||p_instance_code);
62 END IF;
63 ELSE
64 RETURN 'NA_EDW';
65 END IF;
66 END get_locator_fk;
67
68 FUNCTION get_stock_room_fk (p_secondary_inventory_name IN VARCHAR2, p_organization_id IN NUMBER) RETURN VARCHAR2 IS
69 v_instance_code edw_local_instance.instance_code%TYPE;
70 v_organization_code mtl_parameters.organization_code%TYPE;
71 BEGIN
72
73 SELECT instance_code INTO v_instance_code
74 FROM edw_local_instance;
75
76 IF p_organization_id IS NULL THEN
77 RETURN 'NA_EDW';
78 ELSE
79 SELECT mp.organization_code INTO v_organization_code
80 FROM mtl_parameters mp
81 WHERE mp.organization_id = p_organization_id;
82
83 IF (p_secondary_inventory_name IS NOT NULL) THEN
84 RETURN(p_secondary_inventory_name ||'-'||v_organization_code||'-'||v_instance_code||'-SUBI');
85 ELSE
86 RETURN(v_organization_code||'-'||v_instance_code||'-PLNT');
87 END IF;
88 END IF;
89
90 EXCEPTION WHEN others THEN
91 RETURN('Invalid '||p_secondary_inventory_name);
92 END get_stock_room_fk;
93 END;
[Home] [Help]