1 PACKAGE BODY MTL_PARAM_VALIDATE_PKG as
2 /* $Header: INVSDO2B.pls 120.2 2006/07/19 06:50:48 jrayarot noship $ */
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 BEGIN
190 --Bug 5263266 Modified the below sql for performance issues.
191 --select 'uniqueness violated'
192 --into v_garbage
193 --from mtl_serial_numbers a,
194 -- mtl_serial_numbers b
195 --where a.serial_number = b.serial_number
196 --and a.current_organization_id <>
197 -- b.current_organization_id
198 --and rownum < 2;
199 --Modified SQL
200 select 'uniqueness violated'
201 into v_garbage
202 from mtl_serial_numbers a
203 where exists ( select 1
204 from mtl_serial_numbers b
205 where a.serial_number = b.serial_number
206 and a.current_organization_id <> b.current_organization_id )
207 and rownum < 2;
208 --End Bug 5263266
209 v_return_val := 2;
210 return(v_return_val);
211 EXCEPTION
212 WHEN NO_DATA_FOUND THEN
213 BEGIN
214 select 'uniqueness violated'
215 into v_garbage
216 from mtl_serial_numbers a,
217 mtl_serial_numbers b
218 where a.serial_number = b.serial_number
219 and a.current_organization_id = org_id
220 and b.current_organization_id = org_id
221 and a.inventory_item_id <> b.inventory_item_id
222 and rownum < 2;
223
224 v_return_val := 0;
225 return(v_return_val);
226 EXCEPTION
227 WHEN NO_DATA_FOUND THEN
228 v_return_val := 1;
229 return(v_return_val);
230 END;
231 END;
232 elsif curr_serial_control = 1 then -- At model level
233 begin
234 open ser_config(org_id);
235 fetch ser_config into l_ser_num,l_ser_count;
236 if (ser_config%found) then
237 v_return_val := 4;
238 else
239 select 'break constraint'
240 into v_garbage
241 from mtl_parameters
242 where serial_number_type = 3
243 and organization_id <> org_id
244 and rownum < 2;
245 v_return_val := 3;
246 end if;
247
248 close ser_config;
249 return(v_return_val);
250
251 EXCEPTION
252 WHEN NO_DATA_FOUND THEN
253 v_return_val := 1; close ser_config;
254 return(v_return_val);
255 END;
256 else
257 app_exception.invalid_argument(
258 'SERIAL_CONTROL_VALIDATE',
259 'CURR_SERIAL_CONTROL',
260 curr_serial_control);
261 end if;
262 end serial_control_validate;
263
264 END MTL_PARAM_VALIDATE_PKG;
[Home] [Help]