1 PACKAGE BODY MTL_PARAM_VALIDATE_PKG as
2 /* $Header: INVSDO2B.pls 120.3.12020000.2 2012/07/09 08:17:37 asugandh ship $ */
3
4 function master_has_items(curr_org_id in NUMBER,
5 master_org_id in NUMBER)
6 return integer is
7 v_status number := 1;
8 v_garbage VARCHAR(255);
9 BEGIN
10 select 'x'
11 into v_garbage
12 from mtl_system_items i
13 where i.organization_id =
14 curr_org_id
15 and not exists (select 'x'
16 from mtl_system_items i2
17 where i2.organization_id =
18 master_org_id
19 and i2.inventory_item_id =
20 i.inventory_item_id)
21 and rownum < 2;
22
23 return(v_status);
24
25 EXCEPTION
26 WHEN NO_DATA_FOUND THEN
27 v_status := 2;
28 return(v_status);
29 end master_has_items;
30
31 function org_has_children(org_id in NUMBER)
32 return integer is
33 v_status number := 1;
34 v_garbage VARCHAR(255);
35 BEGIN
36 select 'org has children'
37 into v_garbage
38 from mtl_parameters
39 where master_organization_id =
40 org_id
41 and organization_id <>
42 org_id
43 and rownum < 2;
44
45 return(v_status);
46
47 EXCEPTION
48 WHEN NO_DATA_FOUND THEN
49 v_status := 2;
50 return(v_status);
51 END org_has_children;
52
53 function lot_control_validate(curr_lot_control in NUMBER,
54 org_id in NUMBER)
55 return integer is
56 v_garbage VARCHAR2(255);
57 v_return_val integer := 1;
58 BEGIN
59
60 if curr_lot_control = 1 then
61 BEGIN
62 select 'uniqueness violated'
63 into v_garbage
64 from mtl_lot_numbers a, mtl_lot_numbers b
65 where a.lot_number = b.lot_number
66 and a.inventory_item_id <> b.inventory_item_id
67 and rownum < 2;
68
69 v_return_val := -1;
70 return(v_return_val);
71
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 return(v_return_val);
75 END;
76 elsif curr_lot_control = 2 then
77 BEGIN
78 select 'uniqueness already imposed'
79 into v_garbage
80 from mtl_parameters
81 where lot_number_uniqueness = 1
82 and organization_id <>
83 org_id
84 and rownum < 2;
85
86 v_return_val := 2;
87 return(v_return_val);
88
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 return(v_return_val);
92 END;
93
94 else
95 app_exception.invalid_argument('LOT_CONTROL_VALIDATE',
96 'CURR_LOT_CONTROL', curr_lot_control);
97 end if;
98 end lot_control_validate;
99
100 function serial_control_validate(
101 curr_serial_control in NUMBER,
102 org_id in NUMBER)
103 return integer IS
104 v_garbage VARCHAR2(255);
105 v_return_val integer := 1;
106 l_ser_num VARCHAR2(30);
107 l_ser_count NUMBER;
108 cursor ser_config(p_org_id number) is
109 select serial_number,count(*)
110 from mtl_serial_numbers
111 where inventory_item_id in (select inventory_item_id
112 from mtl_system_items_b
113 where organization_id = p_org_id
114 and serial_number_control_code <> 1
115 and base_item_id is not null
116 and bom_item_type = 4)
117 and current_organization_id = p_org_id
118 group by serial_number
119 having count(*) > 1;
120 BEGIN
121 /* bug 3403255 Added check to validate serial uniqueness with in model and items
122 * this is to support the serial uniqueness for the configured items of the
123 * same base model can have the same serial numbers.
124 * Lookup codes are changed now
125 * 1 -- Serial unique with in model and item
126 * 4 -- Serial unique with in items
127 * When changing to 1 we need to validate whether same base model configurations
128 * are having the same serial if so error out */
129
130 if curr_serial_control is null then
131 app_exception.invalid_argument(
132 'SERIAL_CONTROL_VALIDATE',
133 'CURR_SERIAL_CONTROL',
134 curr_serial_control);
135 elsif curr_serial_control = 4 then -- At item Level
136 BEGIN
137 select 'break constraint'
138 into v_garbage
139 from mtl_parameters
140 where serial_number_type = 3
141 and organization_id <> org_id
142 and rownum < 2;
143
144 v_return_val := 3;
145 return(v_return_val);
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 v_return_val := 1;
150 return(v_return_val);
151 END;
152
153 elsif curr_serial_control = 2 then -- With in Org
154 BEGIN
155 select 'uniqueness violated'
156 into v_garbage
157 from mtl_serial_numbers a,
158 mtl_serial_numbers b
159 where a.serial_number = b.serial_number
160 and a.current_organization_id = org_id
161 and b.current_organization_id = org_id
162 and a.inventory_item_id <> b.inventory_item_id
163 and rownum < 2;
164
165 v_return_val := 0;
166 return(v_return_val);
167
168 EXCEPTION
169 WHEN NO_DATA_FOUND THEN
170 BEGIN
171 select 'break constraint'
172 into v_garbage
173 from mtl_parameters
174 where serial_number_type = 3
175 and organization_id <> org_id
176 and rownum < 2;
177
178 v_return_val := 3;
179 return(v_return_val);
180
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 v_return_val := 1;
184 return(v_return_val);
185 END;
186 END;
187
188 elsif curr_serial_control = 3 then -- Across Org
189 --Bug 5263266 Modified the below sql for performance issues.
190 --select 'uniqueness violated'
191 --into v_garbage
192 --from mtl_serial_numbers a,
193 -- mtl_serial_numbers b
194 --where a.serial_number = b.serial_number
195 --and a.current_organization_id <>
196 -- b.current_organization_id
197 --and rownum < 2;
198 --Modified SQL
199
200 /* Commented following query for bug 9945449 as form used to hang at this SQL
201 select 'uniqueness violated'
202 into v_garbage
203 from mtl_serial_numbers a
204 where exists ( select 1
205 from mtl_serial_numbers b
206 where a.serial_number = b.serial_number
207 and a.current_organization_id <> b.current_organization_id )
208 and rownum < 2;
209 */
210
211 --End Bug 5263266
212
213 /* Added following query for bug 9945449 */
214
215 /*select 'uniqueness violated'
216 into v_garbage
217 from dual
218 where exists (select 1 from mtl_serial_numbers a, mtl_serial_numbers b
219 where a.serial_number = b.serial_number
220 and a.current_organization_id <> b.current_organization_id);
221
222 v_return_val := 2;
223 return(v_return_val);
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 BEGIN
227 select 'uniqueness violated'
228 into v_garbage
229 from mtl_serial_numbers a,
230 mtl_serial_numbers b
231 where a.serial_number = b.serial_number
232 and a.current_organization_id = org_id
233 and b.current_organization_id = org_id
234 and a.inventory_item_id <> b.inventory_item_id
235 and rownum < 2;
236
237 v_return_val := 0;
238 return(v_return_val);
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 v_return_val := 1;
242 return(v_return_val);
243 END;*/
244 BEGIN
245 select 'uniqueness violated'
246 into v_garbage
247 from dual
248 where
249 exists (
250 select serial_number, count(1)
251 from mtl_serial_numbers
252 group by serial_number
253 having count(1) > 1
254 );
255
256 BEGIN
257 select 'uniqueness violated'
258 into v_garbage
259 from dual
260 where
261 exists (
262 select serial_number, count(1)
263 from mtl_serial_numbers
264 group by serial_number, current_organization_id
265 having count(1) > 1
266 );
267 v_return_val := 0;
268 return(v_return_val);
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 v_return_val := 2;
272 return(v_return_val);
273 END;
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276 v_return_val := 1;
277 return(v_return_val);
278 END;
279
280 elsif curr_serial_control = 1 then -- At model level
281 begin
282 open ser_config(org_id);
283 fetch ser_config into l_ser_num,l_ser_count;
284 if (ser_config%found) then
285 v_return_val := 4;
286 else
287 select 'break constraint'
288 into v_garbage
289 from mtl_parameters
290 where serial_number_type = 3
291 and organization_id <> org_id
292 and rownum < 2;
293 v_return_val := 3;
294 end if;
295
296 close ser_config;
297 return(v_return_val);
298
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 v_return_val := 1; close ser_config;
302 return(v_return_val);
303 END;
304 else
305 app_exception.invalid_argument(
306 'SERIAL_CONTROL_VALIDATE',
307 'CURR_SERIAL_CONTROL',
308 curr_serial_control);
309 end if;
310 end serial_control_validate;
311
312 END MTL_PARAM_VALIDATE_PKG;
[Home] [Help]