1 PACKAGE BODY wma_special_lovs AS
2 /* $Header: wmaslovb.pls 115.6 2002/11/14 23:20:35 jyeung ship $ */
3
4 /**
5 * This procedure is used by SubinvLovBean as the lov statement. The OUT param
6 * is a ref cursor which contains all the subinv that are valid for the given
7 * item.
8 */
9 PROCEDURE getSubinventories(
10 subinventories OUT NOCOPY LovCurType,
11 orgID IN NUMBER,
12 itemID IN NUMBER,
13 trxTypeID IN NUMBER,
14 invName IN VARCHAR2) IS
15 restrictSubinv NUMBER;
16 BEGIN
17 select restrict_subinventories_code
18 into restrictSubinv
19 from mtl_system_items
20 where organization_id = orgID
21 and inventory_item_id = itemID;
22
23 if ( restrictSubinv <> 1 ) then
24 -- item is not restricted to subinventories
25 OPEN subinventories FOR
26 select secondary_inventory_name,
27 locator_type
28 from mtl_secondary_inventories
29 where organization_id = orgID
30 and secondary_inventory_name like invName || '%'
31 and secondary_inventory_name <> 'AX_INTRANS'
32 and inv_material_status_grp.is_status_applicable(
33 NULL, -- p_wms_installed,
34 NULL,
35 trxTypeID, -- p_trx_type_id (is this same as trx_type_id?)
36 NULL,
37 NULL,
38 orgID,
39 itemID,
40 secondary_inventory_name,
41 NULL,
42 NULL,
43 NULL,
44 'Z') = 'Y'
45 order by secondary_inventory_name;
46 else
47 -- item is restricted to subinventories
48 OPEN subinventories FOR
49 select misi.secondary_inventory,
50 msi.locator_type
51 from mtl_item_sub_inventories misi,
52 mtl_secondary_inventories msi
53 where misi.secondary_inventory = msi.secondary_inventory_name
54 and misi.organization_id = msi.organization_id
55 and misi.inventory_item_id = itemID
56 and misi.organization_id = orgID
57 and misi.secondary_inventory like invName || '%'
58 and misi.secondary_inventory <> 'AX_INTRANS'
59 and inv_material_status_grp.is_status_applicable(
60 NULL, -- p_wms_installed,
61 NULL,
62 trxTypeID, -- p_trx_type_id (is this same as trx_type_id?)
63 NULL,
64 NULL,
65 orgID,
66 itemID,
67 msi.secondary_inventory_name,
68 NULL,
69 NULL,
70 NULL,
71 'Z') = 'Y'
72 order by misi.secondary_inventory;
73 end if;
74 EXCEPTION
75 when others then
76 NULL;
77 END getSubinventories;
78
79
80 /**
81 * This function is used by the SubinvLovBean. It returns the appropriate
82 * locator control code depending on if it's determined at the org, subinv,
83 * or item level.
84 */
85 FUNCTION locatorControl(
86 orgID IN NUMBER,
87 subinv IN VARCHAR2,
88 itemID IN NUMBER) RETURN NUMBER IS
89 orgLevelCtl NUMBER;
90 subinvLevelCtl NUMBER;
91 itemLevelCtl NUMBER;
92 BEGIN
93 select stock_locator_control_code
94 into orgLevelCtl
95 from mtl_parameters
96 where organization_id = orgID;
97
98 if ( orgLevelCtl < 4 ) then -- controlled at org level
99 return orgLevelCtl;
100 end if;
101
102 -- not 1, 2, 3, so it must be subinv level control
103 select locator_type
104 into subinvLevelCtl
105 from mtl_secondary_inventories
106 where organization_id = orgID
107 and secondary_inventory_name = subinv;
108
109 if ( subinvLevelCtl < 4 ) then -- subinv level
110 return subinvLevelCtl;
111 end if;
112
113
114 -- not 1 ,2, 3, so it must be item level
115 select location_control_code
116 into itemLevelCtl
117 from mtl_system_items
118 where inventory_item_id = itemID
119 and organization_id = orgID;
120
121 return itemLevelCtl;
122
123
124 -- excution should never be here. If it does, then no control.
125 return 1;
126
127 END locatorControl;
128
129 /**
130 * This procedure is used by the LocatorLovBean as the lov statement. The OUT param
131 * is a ref cursor which contains all the valid locators for the given item.
132 */
133 PROCEDURE getLocators(
134 locators OUT NOCOPY LovCurType,
135 orgID IN NUMBER,
136 subinv IN VARCHAR2,
137 itemID IN NUMBER,
138 trxTypeID IN NUMBER,
139 locName IN VARCHAR2) IS
140 restrictLocatorCode NUMBER;
141 BEGIN
142 select restrict_locators_code
143 into restrictLocatorCode
144 from mtl_system_items
145 where inventory_item_id = itemID
146 and organization_id = orgID;
147
148 if ( restrictLocatorCode = 2 ) then
149 -- not restricted to predefined locators
150 OPEN locators FOR
151 select inventory_location_id,
152 concatenated_segments
153 from mtl_item_locations_kfv
154 where organization_id = orgID
155 and subinventory_code = subinv
156 and concatenated_segments like locName || '%'
157 and inv_material_status_grp.is_status_applicable(
158 NULL, -- p_wms_installed,
159 NULL,
160 trxTypeID, -- p_trx_type_id (is this same as trx_type_id?)
161 NULL,
162 NULL,
163 orgID,
164 itemID,
165 NULL,
166 inventory_location_id,
167 NULL,
168 NULL,
169 'L') = 'Y'
170 order by concatenated_segments;
171 else
172 -- restricted to predefined locators
173 OPEN locators FOR
174 select msl.secondary_locator,
175 milk.concatenated_segments
176 from mtl_secondary_locators msl,
177 mtl_item_locations_kfv milk
178 where msl.organization_id = milk.organization_id
179 and msl.secondary_locator = milk.inventory_location_id
180 and msl.subinventory_code = milk.subinventory_code
181 and msl.inventory_item_id = itemID
182 and msl.organization_id = orgID
183 and msl.subinventory_code = subinv
184 and milk.concatenated_segments like locName || '%'
185 and inv_material_status_grp.is_status_applicable(
186 NULL, -- p_wms_installed,
187 NULL,
188 trxTypeID, -- p_trx_type_id (is this same as trx_type_id?)
189 NULL,
190 NULL,
191 orgID,
192 itemID,
193 NULL,
194 inventory_location_id,
195 NULL,
196 NULL,
197 'L') = 'Y'
198 order by milk.concatenated_segments;
199 end if;
200 EXCEPTION
201 when others then
202 NULL;
203 END getLocators;
204
205 END wma_special_lovs;
[Home] [Help]