1 package body INV_ATTRIBUTE_CONTROL_PVT as
2 /* $Header: INVATTCB.pls 120.4 2006/01/24 22:16:06 anmurali noship $ */
3
4 function get_attribute_control(x_source_item varchar2) return number is
5 l_control_level number := 0;
6 attr_name varchar2(200);
7 begin
8 attr_name := 'MTL_SYSTEM_ITEMS.'||x_source_item;
9 begin
10 select control_level
11 into l_control_level
12 from mtl_item_attributes
13 where attribute_name = attr_name;
14 exception
15 when no_data_found then
16 null;
17 end;
18 return l_control_level;
19 end get_attribute_control;
20
21
22 function check_pending_adjustments(p_org_id in number,
23 p_item_id in number,
24 p_source_item varchar2) return boolean IS
25 l_org_count number := 0;
26 l_master_count number := 0;
27 l_master_org number := 0;
28 begin
29 -- check at org level
30 select count(1)
31 into l_org_count
32 from mtl_cycle_count_entries
33 where inventory_item_id = p_item_id and
34 organization_id = p_org_id and
35 entry_status_code = 2 and
36 rownum = 1;
37
38 /* Fix for bug 4505262 - Added check on adjustment_quantity in the below query. */
39 if (l_org_count <> 1) then
40 select count(1)
41 into l_org_count
42 from mtl_physical_adjustments
43 where inventory_item_id = p_item_id
44 and organization_id = p_org_id
45 and approval_status = 1
46 and adjustment_quantity <> 0
47 and rownum = 1;
48 end if;
49 --check at master level
50 if (l_org_count <> 1 and get_attribute_control(p_source_item) = 1) then
51 select master_organization_id into l_master_org
52 from mtl_parameters
53 where organization_id = p_org_id;
54
55 select count(1)
56 into l_master_count
57 from mtl_cycle_count_entries
58 where inventory_item_id = p_item_id and
59 (organization_id in
60 (select organization_id
61 from mtl_parameters
62 where master_organization_id = l_master_org
63 )
64 )
65 and entry_status_code = 2
66 and rownum = 1;
67
68 /* Fix for bug 4505262 - Added check on adjustment_quantity in the below query. */
69 if (l_master_count <> 1) then
70 select count(1)
71 into l_master_count
72 from mtl_physical_adjustments
73 where inventory_item_id = p_item_id
74 and (organization_id in
75 (select organization_id
76 from mtl_parameters
77 where master_organization_id = l_master_org
78 )
79 )
80 and approval_status = 1
81 and adjustment_quantity <> 0
82 and rownum = 1;
83 end if;
84 end if;
85 if (l_org_count = 1 or l_master_count = 1) then
86 return TRUE;
87 else
88 return FALSE;
89 end if;
90 end check_pending_adjustments;
91
92
93
94 -- when try to change from yes to no
95 function reservable_uncheck (p_org_id in number,
96 p_item_id in number) return boolean is
97 l_org_count number := 0;
98 l_master_count number := 0;
99 l_master_org number := 0;
100 begin
101 select count(1) into l_org_count
102 from wsh_delivery_details
103 where inventory_item_id = p_item_id
104 and released_status = 'S'
105 and source_code = 'OE'
106 and organization_id = p_org_id
107 and rownum = 1;
108
109 if (l_org_count <> 1 and
110 get_attribute_control('RESERVABLE_TYPE') = 1 ) then
111 select master_organization_id into l_master_org
112 from mtl_parameters
113 where organization_id = p_org_id;
114
115 select count(1) into l_master_count
116 from wsh_delivery_details
117 where inventory_item_id = p_item_id
118 and released_status = 'S'
119 and source_code = 'OE'
120 and organization_id in
121 (select organization_id
122 from mtl_parameters
123 where master_organization_id = l_master_org)
124 and rownum = 1;
125 end if;
126 if (l_org_count = 1 or l_master_count = 1) then
127 return TRUE;
128 else
129 return FALSE;
130 end if;
131 end reservable_uncheck;
132
133
134 -- when changes from no to yes
135 function reservable_check (p_org_id in number,
136 p_item_id in number) return boolean is
137 l_org_count number := 0;
138 l_master_count number := 0;
139 l_master_org number := 0;
140 begin
141 select count(1) into l_org_count
142 from wsh_delivery_details
143 where inventory_item_id = p_item_id
144 and released_status = 'Y'
145 and source_code = 'OE'
146 and organization_id = p_org_id
147 and rownum = 1;
148
149 if (l_org_count <> 1 and
150 get_attribute_control('RESERVABLE_TYPE') = 1 ) then
151 select master_organization_id into l_master_org
152 from mtl_parameters
153 where organization_id = p_org_id;
154
155 select count(1) into l_master_count
156 from wsh_delivery_details
157 where inventory_item_id = p_item_id
158 and released_status = 'Y'
159 and source_code = 'OE'
160 and organization_id in
161 (select organization_id
162 from mtl_parameters
163 where master_organization_id = l_master_org)
164 and rownum = 1;
165 end if;
166 if (l_org_count = 1 or l_master_count = 1) then
167 return TRUE;
168 else
169 return FALSE;
170 end if;
171 end reservable_check;
172
173
174
175
176 -- when changes from no to Yes
177 function transactable_check(p_org_id in number,
178 p_item_id in number) return boolean is
179 l_org_count number := 0;
180 l_master_count number := 0;
181 l_master_org number := 0;
182 begin
183 select count(1) into l_org_count
184 from oe_order_lines_all l,
185 wsh_delivery_details wdd
186 where l.inventory_item_id = p_item_id
187 and nvl(l.open_flag,'Y') = 'Y'
188 and wdd.released_status not in ('C','D')
189 and wdd.pickable_flag = 'N'
190 --and wdd.source_code = 'OE'
191 and l.line_id = wdd.source_line_id
192 and l.ship_from_org_id = p_org_id
193 and rownum = 1;
194
195 if (l_org_count <> 1 and
196 get_attribute_control('MTL_TRANSACTIONS_ENABLED_FLAG') = 1 ) then
197 select master_organization_id into l_master_org
198 from mtl_parameters
199 where organization_id = p_org_id;
200
201 select count(1) into l_master_count
202 from oe_order_lines_all l,
203 wsh_delivery_details wdd
204 where l.inventory_item_id = p_item_id
205 and nvl(l.open_flag,'Y') = 'Y'
206 and wdd.released_status not in ('C','D')
207 and wdd.pickable_flag = 'N'
208 --and wdd.source_code = 'OE'
209 and l.line_id = wdd.source_line_id
210 and l.ship_from_org_id in
211 (select organization_id
212 from mtl_parameters
213 where master_organization_id = l_master_org)
214 and rownum = 1;
215 end if;
216 if (l_org_count = 1 or l_master_count = 1) then
217 return TRUE;
218 else
219 return FALSE;
220 end if;
221 end transactable_check;
222
223
224
225
226 -- When changes from Yes to No
227 function transactable_uncheck(p_org_id in number,
228 p_item_id in number) return boolean is
229 l_org_count number := 0;
230 l_master_count number := 0;
231 l_master_org number := 0;
232 begin
233 select count(1) into l_org_count
234 from wsh_delivery_details
235 where inventory_item_id = p_item_id
236 and pickable_flag = 'Y'
237 --Bug 4643978 - Perf fix
238 and inv_interfaced_flag IN ('N','P')
239 --and source_code = 'OE'
240 and released_status <> 'D'
241 and organization_id = p_org_id
242 and rownum = 1;
243
244 if (l_org_count <> 1) then
245 select count(1) into l_org_count
246 from oe_order_lines_all
247 where source_type_code = 'EXTERNAL'
248 and open_flag = 'Y'
249 and nvl(shipped_quantity,0) = 0
250 and item_type_code in ('MODEL','STANDARD','OPTION')
251 and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
252 and inventory_item_id = p_item_id
253 and ship_from_org_id = p_org_id
254 and rownum = 1;
255 end if;
256
257 if (l_org_count <> 1) then
258 select count(1) into l_org_count
259 from oe_order_lines_all l
260 where booked_flag = 'Y'
261 and nvl(shipped_quantity,0) = 0
262 and inventory_item_id = p_item_id
263 and open_flag = 'Y'
264 and ship_from_org_id = p_org_id
265 and exists (select 1
266 from mtl_transactions_interface
267 where trx_source_line_id = l.line_id
268 and transaction_source_type_id in (2,8)
269 and source_code = 'ORDER ENTRY')
270 and rownum = 1;
271 end if;
272
273 if (l_org_count <> 1 and
274 get_attribute_control('MTL_TRANSACTIONS_ENABLED_FLAG') = 1 ) then
275
276 select master_organization_id into l_master_org
277 from mtl_parameters
278 where organization_id = p_org_id;
279 -- Bug: 4060557 For performance improvement, breaking the SQL
280 for i in (select organization_id
281 from mtl_parameters
282 where master_organization_id = l_master_org)
283 loop
284 select count(1) into l_master_count
285 from wsh_delivery_details
286 where inventory_item_id = p_item_id
287 and pickable_flag = 'Y'
288 --Bug 4643978 - Perf fix
289 and inv_interfaced_flag IN ('N','P')
290 --and source_code = 'OE'
291 and released_status <> 'D'
292 and organization_id = i.organization_id
293 and rownum = 1;
294
295 if nvl(l_master_count, 0) = 1 then
296 exit;
297 end if;
298 end loop;
299
300 if nvl(l_master_count, 0) <> 1 then
301 for i in (select organization_id
302 from mtl_parameters
303 where master_organization_id = l_master_org)
304 loop
305 select count(1) into l_org_count
306 from oe_order_lines_all
307 where source_type_code = 'EXTERNAL'
308 and open_flag = 'Y'
309 and nvl(shipped_quantity,0) = 0
310 and item_type_code in ('MODEL','STANDARD','OPTION')
311 and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
312 and inventory_item_id = p_item_id
313 and ship_from_org_id = i.organization_id
314 and rownum = 1;
315 if nvl(l_master_count, 0) = 1 then
316 exit;
317 end if;
318 end loop;
319 end if;
320
321 if nvl(l_master_count, 0) <> 1 then
322
323 for i in (select organization_id
324 from mtl_parameters
325 where master_organization_id = l_master_org)
326 loop
327 select count(1) into l_org_count
328 from oe_order_lines_all l
329 where booked_flag = 'Y'
330 and nvl(shipped_quantity,0) = 0
331 and inventory_item_id = p_item_id
332 and open_flag = 'Y'
333 and ship_from_org_id = i.organization_id
334 and exists (select 1
335 from mtl_transactions_interface
336 where trx_source_line_id = l.line_id
337 and transaction_source_type_id in (2,8)
338 and source_code = 'ORDER ENTRY')
339 and rownum = 1;
340
341 if nvl(l_master_count, 0) = 1 then
342 exit;
343 end if;
344 end loop;
345 end if;
346
347 end if;
348
349 if (l_org_count = 1 or l_master_count = 1) then
350 return TRUE;
351 else
352 return FALSE;
353 end if;
354
355 end transactable_uncheck;
356
357
358 function check_pending_interface(p_org_id in number,
359 p_item_id in number,
360 p_source_item varchar2) return boolean IS
361 l_org_count number := 0;
362 l_master_count number := 0;
363 l_master_org number := 0;
364 begin
365 -- check at org level
366 select count(1) into l_org_count
367 from wsh_delivery_details
368 where inventory_item_id = p_item_id
369 and oe_interfaced_flag = 'Y'
370 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
371 and released_status = 'C'
372 and source_code = 'OE'
373 and organization_id = p_org_id
374 and rownum = 1;
375
376 -- check at master level
377 if (l_org_count <> 1 and
378 get_attribute_control(p_source_item) = 1 ) then
379 select master_organization_id into l_master_org
380 from mtl_parameters
381 where organization_id = p_org_id;
382 select count(1)
383 into l_master_count
384 from wsh_delivery_details
385 where inventory_item_id = p_item_id
386 and oe_interfaced_flag = 'Y'
387 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
388 and released_status = 'C'
389 and source_code = 'OE'
390 and organization_id in
391 (select organization_id
392 from mtl_parameters
393 where master_organization_id = l_master_org)
394 and rownum = 1;
395 end if;
396
397 if (l_org_count = 1 or l_master_count = 1) then
398 return TRUE;
399 else
400 return FALSE;
401 end if;
402 end check_pending_interface;
403
404
405 -- When changes from None to Any control
406 function serial_check(p_org_id in number,
407 p_item_id in number) return boolean IS
408 l_org_count number := 0;
409 l_master_count number := 0;
410 l_master_org number := 0;
411 begin
412 -- check at org level
413 select count(1)
414 into l_org_count
415 from wsh_delivery_details
416 where inventory_item_id = p_item_id
417 and released_status not in ('B','R','D')
418 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
419 and pickable_flag = 'Y'
420 and source_code = 'OE'
421 and organization_id = p_org_id
422 and rownum = 1;
423
424 -- check at master level
428 from mtl_parameters
425 if (l_org_count <> 1 and
426 get_attribute_control('SERIAL_NUMBER_CONTROL_CODE') = 1 ) then
427 select master_organization_id into l_master_org
429 where organization_id = p_org_id;
430 select count(1)
431 into l_master_count
432 from wsh_delivery_details
433 where inventory_item_id = p_item_id
434 and released_status not in ('B','R','D')
435 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
436 and pickable_flag = 'Y'
437 and source_code = 'OE'
438 and organization_id in
439 (select organization_id
440 from mtl_parameters
441 where master_organization_id = l_master_org)
442 and rownum = 1;
443 end if;
444
445 if (l_org_count = 1 or l_master_count = 1) then
446 return TRUE;
447 else
448 return FALSE;
449 end if;
450 end serial_check;
451
452 -- When changes from Yes to No
453 function ato_uncheck(p_org_id in number,
454 p_item_id in number) return boolean is
455 l_org_count number := 0;
456 l_master_count number := 0;
457 l_master_org number := 0;
458 begin
459 select count(1) into l_org_count
460 from oe_order_lines_all
461 where inventory_item_id = p_item_id
462 and nvl(open_flag,'Y') = 'Y'
463 and cancelled_flag = 'N'
464 and ship_from_org_id = p_org_id
465 and rownum = 1;
466 if (l_org_count <> 1 and
467 get_attribute_control('REPLENISH_TO_ORDER_FLAG') = 1 ) then
468 select master_organization_id into l_master_org
469 from mtl_parameters
470 where organization_id = p_org_id;
471
472 select count(1) into l_master_count
473 from oe_order_lines_all
474 where inventory_item_id = p_item_id
475 and nvl(open_flag,'Y') = 'Y'
476 and cancelled_flag = 'N'
477 and ship_from_org_id in (select organization_id
478 from mtl_parameters
479 where master_organization_id = l_master_org)
480 and rownum = 1;
481 end if;
482 if (l_org_count = 1 or l_master_count = 1) then
483 return TRUE;
484 else
485 return FALSE;
486 end if;
487 end ato_uncheck;
488
489 -- when changes from no to Yes
490 function shippable_check(p_org_id in number,
491 p_item_id in number) return boolean is
492 l_org_count number := 0;
493 l_master_count number := 0;
494 l_master_org number := 0;
495 begin
496 select count(1) into l_org_count
497 from oe_order_lines_all
498 where inventory_item_id = p_item_id
499 and nvl(open_flag,'Y') = 'Y'
500 and cancelled_flag = 'N'
501 and ship_from_org_id = p_org_id
502 and rownum = 1;
503
504 if (l_org_count <> 1 and
505 get_attribute_control('SHIPPABLE_ITEM_FLAG') = 1 ) then
506 select master_organization_id into l_master_org
507 from mtl_parameters
508 where organization_id = p_org_id;
509
510 select count(1) into l_master_count
511 from oe_order_lines_all
512 where inventory_item_id = p_item_id
513 and nvl(open_flag,'Y') = 'Y'
514 and cancelled_flag = 'N'
515 and ship_from_org_id in
516 (select organization_id
517 from mtl_parameters
518 where master_organization_id = l_master_org)
519 and rownum = 1;
520 end if;
521 if (l_org_count = 1 or l_master_count = 1) then
522 return TRUE;
523 else
524 return FALSE;
525 end if;
526 end shippable_check;
527
528 end;
[Home] [Help]