1 package body INV_ATTRIBUTE_CONTROL_PVT as
2 /* $Header: INVATTCB.pls 120.10.12020000.3 2013/01/29 04:31:15 evwang ship $ */
3
4 -- Bug 12393250 : Start
5 -- Global Variables
6 G_ITEM_ID NUMBER := -1;
7 G_ORG_ID NUMBER := -1;
8 G_ORG_COUNT NUMBER := 0;
9 G_ITEM_ID_1 NUMBER := -1;
10 G_ORG_ID_1 NUMBER := -1;
11 G_MASTER_COUNT NUMBER := 0;
12 -- Bug 12393250 : End
13
14 function get_attribute_control(x_source_item varchar2) return number is
15 l_control_level number := 0;
16 attr_name varchar2(200);
17 begin
18 attr_name := 'MTL_SYSTEM_ITEMS.'||x_source_item;
19 begin
20 select control_level
21 into l_control_level
22 from mtl_item_attributes
23 where attribute_name = attr_name;
24 exception
25 when no_data_found then
26 null;
27 end;
28 return l_control_level;
29 end get_attribute_control;
30
31
32 /* Bug 12393250 : In the below function some queries are geting executed around 12 times
33 for same item, org combination which is not needed. So made the changes
34 execute only once by caching the query execution is a gloal variable.
35 Which improves the performance.
36 */
37 function check_pending_adjustments(p_org_id in number,
38 p_item_id in number,
39 p_source_item varchar2) return boolean IS
40 l_org_count number := 0;
41 l_master_count number := 0;
42 l_master_org number := 0;
43 begin
44 -- check at org level
45 select count(1)
46 into l_org_count
47 from mtl_cycle_count_entries
48 where inventory_item_id = p_item_id and
49 organization_id = p_org_id and
50 /*Change for bug 8208397*/ /*Check should be for status 1-Uncounted/2-Pending Approval/3-Recount*/
51 /*entry_status_code = 2 and*/
52 entry_status_code IN (1,2,3) and
53 rownum = 1;
54
55 /* Fix for bug 4505262 - Added check on adjustment_quantity in the below query. */
56 if (l_org_count <> 1) then
57 IF(G_ITEM_ID <> p_item_id OR G_ORG_ID <> p_org_id) THEN -- Bug 12393250
58 select count(1)
59 into l_org_count
60 from mtl_physical_adjustments
61 where inventory_item_id = p_item_id
62 and organization_id = p_org_id
63 and approval_status = 1
64 and adjustment_quantity <> 0
65 and rownum = 1;
66 -- Bug 12393250 : Start
67 G_ORG_COUNT := l_org_count;
68 G_ITEM_ID := p_item_id;
69 G_ORG_ID := p_org_id;
70 ELSE
71 l_org_count := G_ORG_COUNT;
72 END IF;
73 -- Bug 12393250 : End
74 end if;
75 --check at master level
76 if (l_org_count <> 1 and get_attribute_control(p_source_item) = 1) then
77 select master_organization_id into l_master_org
78 from mtl_parameters
79 where organization_id = p_org_id;
80
81 select count(1)
82 into l_master_count
83 from mtl_cycle_count_entries
84 where inventory_item_id = p_item_id and
85 (organization_id in
86 (select organization_id
87 from mtl_parameters
88 where master_organization_id = l_master_org
89 )
90 )
91 /*Change for bug 8208397*/ /*Check should be for status 1-Uncounted/2-Pending Approval/3-Recount*/
92 /*and entry_status_code = 2*/
93 and entry_status_code IN (1,2,3)
94 and rownum = 1;
95
96 /* Fix for bug 4505262 - Added check on adjustment_quantity in the below query. */
97 if (l_master_count <> 1) then
98 IF(G_ITEM_ID_1 <> p_item_id or G_ORG_ID_1 <> p_org_id) THEN -- Bug 12393250
99 select count(1)
100 into l_master_count
101 from mtl_physical_adjustments
102 where inventory_item_id = p_item_id
103 and (organization_id in
104 (select organization_id
105 from mtl_parameters
106 where master_organization_id = l_master_org
107 )
108 )
109 and approval_status = 1
110 and adjustment_quantity <> 0
111 and rownum = 1;
112 -- Bug 12393250 : Start
113 G_MASTER_COUNT := l_master_count;
114 G_ITEM_ID_1 := p_item_id;
115 G_ORG_ID_1 := p_org_id;
116 ELSE
117 l_master_count := G_MASTER_COUNT;
118 END IF;
119 -- Bug 12393250 : End
120 end if;
121 end if;
122 if (l_org_count = 1 or l_master_count = 1) then
123 return TRUE;
124 else
125 return FALSE;
126 end if;
127 end check_pending_adjustments;
128
129
130
131 -- when try to change from yes to no
132 function reservable_uncheck (p_org_id in number,
133 p_item_id in number) return boolean is
134 l_org_count number := 0;
135 l_master_count number := 0;
136 l_master_org number := 0;
137 begin
138 select count(1) into l_org_count
139 from wsh_delivery_details
140 where inventory_item_id = p_item_id
141 and released_status = 'S'
142 and source_code = 'OE'
143 and organization_id = p_org_id
144 and rownum = 1;
145
146 if (l_org_count <> 1 and
147 get_attribute_control('RESERVABLE_TYPE') = 1 ) then
148 select master_organization_id into l_master_org
149 from mtl_parameters
150 where organization_id = p_org_id;
151
152 select count(1) into l_master_count
153 from wsh_delivery_details
154 where inventory_item_id = p_item_id
155 and released_status = 'S'
156 and source_code = 'OE'
157 and organization_id in
158 (select organization_id
159 from mtl_parameters
160 where master_organization_id = l_master_org)
161 and rownum = 1;
162 end if;
163 if (l_org_count = 1 or l_master_count = 1) then
164 return TRUE;
165 else
166 return FALSE;
167 end if;
168 end reservable_uncheck;
169
170
171 -- when changes from no to yes
172 function reservable_check (p_org_id in number,
173 p_item_id in number) return boolean is
174 l_org_count number := 0;
175 l_master_count number := 0;
176 l_master_org number := 0;
177 begin
178 select count(1) into l_org_count
179 from wsh_delivery_details
180 where inventory_item_id = p_item_id
181 and released_status = 'Y'
182 and source_code = 'OE'
183 and organization_id = p_org_id
184 and rownum = 1;
185
186 if (l_org_count <> 1 and
187 get_attribute_control('RESERVABLE_TYPE') = 1 ) then
188 select master_organization_id into l_master_org
189 from mtl_parameters
190 where organization_id = p_org_id;
191
192 select count(1) into l_master_count
193 from wsh_delivery_details
194 where inventory_item_id = p_item_id
195 and released_status = 'Y'
196 and source_code = 'OE'
197 and organization_id in
198 (select organization_id
199 from mtl_parameters
200 where master_organization_id = l_master_org)
201 and rownum = 1;
202 end if;
203 if (l_org_count = 1 or l_master_count = 1) then
204 return TRUE;
205 else
206 return FALSE;
207 end if;
208 end reservable_check;
209
210
211
212
213 -- when changes from no to Yes
214 function transactable_check(p_org_id in number,
215 p_item_id in number) return boolean is
216 l_org_count number := 0;
217 l_master_count number := 0;
218 l_master_org number := 0;
219 begin
220 select count(1) into l_org_count
221 from oe_order_lines_all l,
222 wsh_delivery_details wdd
223 where l.inventory_item_id = p_item_id
224 and l.open_flag = 'Y' -- Bug 8247770
225 and wdd.released_status not in ('C','D')
226 and wdd.pickable_flag = 'N'
227 --and wdd.source_code = 'OE'
228 and l.line_id = wdd.source_line_id
229 and l.ship_from_org_id = p_org_id
230 and rownum = 1;
231
232 if (l_org_count <> 1 and
233 get_attribute_control('MTL_TRANSACTIONS_ENABLED_FLAG') = 1 ) then
234 select master_organization_id into l_master_org
235 from mtl_parameters
236 where organization_id = p_org_id;
237
238 select count(1) into l_master_count
239 from oe_order_lines_all l,
240 wsh_delivery_details wdd
241 where l.inventory_item_id = p_item_id
242 and l.open_flag = 'Y' -- Bug 8247770
243 and wdd.released_status not in ('C','D')
244 and wdd.pickable_flag = 'N'
245 --and wdd.source_code = 'OE'
246 and l.line_id = wdd.source_line_id
247 and l.ship_from_org_id in
248 (select organization_id
249 from mtl_parameters
250 where master_organization_id = l_master_org)
251 and rownum = 1;
252 end if;
253 if (l_org_count = 1 or l_master_count = 1) then
254 return TRUE;
255 else
256 return FALSE;
257 end if;
258 end transactable_check;
259
260
261
262
263 -- When changes from Yes to No
264 function transactable_uncheck(p_org_id in number,
265 p_item_id in number) return boolean is
266 l_org_count number := 0;
267 l_master_count number := 0;
268 l_master_org number := 0;
269 begin
270 select count(1) into l_org_count
271 from wsh_delivery_details
272 where inventory_item_id = p_item_id
273 and pickable_flag = 'Y'
274 --Bug 4643978 - Perf fix
275 and inv_interfaced_flag IN ('N','P')
276 --and source_code = 'OE'
277 and released_status <> 'D'
278 and organization_id = p_org_id
279 and rownum = 1;
280
281 if (l_org_count <> 1) then
282 select count(1) into l_org_count
283 from oe_order_lines_all
284 where source_type_code = 'EXTERNAL'
285 and open_flag = 'Y'
286 and nvl(shipped_quantity,0) = 0
287 and item_type_code in ('MODEL','STANDARD','OPTION')
288 and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
289 and inventory_item_id = p_item_id
290 and ship_from_org_id = p_org_id
291 and rownum = 1;
292 end if;
293
294 if (l_org_count <> 1) then
295 select count(1) into l_org_count
296 from oe_order_lines_all l
297 where booked_flag = 'Y'
298 and nvl(shipped_quantity,0) = 0
299 and inventory_item_id = p_item_id
300 and open_flag = 'Y'
301 and ship_from_org_id = p_org_id
302 and exists (select 1
303 from mtl_transactions_interface
304 where trx_source_line_id = l.line_id
305 and transaction_source_type_id in (2,8)
306 and source_code = 'ORDER ENTRY')
307 and rownum = 1;
308 end if;
309
310 if (l_org_count <> 1) then -- Added for bug-8460461 Starts here
311 select count(1)
312 into l_org_count
313 from mtl_supply
314 where item_id = p_item_id
315 and to_organization_id =p_org_id
316 and rownum =1 ;
317 end if; -- Added for bug-#8460461 ends here
318
319 --bug 16227090 start
320 if (l_org_count <> 1) then
321 SELECT count(1)
322 into l_org_count
323 FROM GME_MATERIAL_DETAILS D, GME_BATCH_HEADER H
324 WHERE D.INVENTORY_ITEM_ID = p_item_id
325 AND D.ORGANIZATION_ID = p_org_id
326 AND D.BATCH_ID = H.BATCH_ID
327 AND (NVL(H.GL_POSTED_IND, 0) = 0 AND
328 H.BATCH_STATUS IN (1, 2, 3, 4))
329 AND rownum = 1;
330 end if;
331 --bug 16227090 end
332
333 if (l_org_count <> 1 and
334 get_attribute_control('MTL_TRANSACTIONS_ENABLED_FLAG') = 1 ) then
335
336 select master_organization_id into l_master_org
337 from mtl_parameters
338 where organization_id = p_org_id;
339 -- Bug: 4060557 For performance improvement, breaking the SQL
340 -- fix bug: 14396273 - start, performance issue : master item entry form
341 /*
342 for i in (select organization_id
343 from mtl_parameters
344 where master_organization_id = l_master_org)
345 loop
346 select count(1) into l_master_count
347 from wsh_delivery_details
348 where inventory_item_id = p_item_id
349 and pickable_flag = 'Y'
350 --Bug 4643978 - Perf fix
351 and inv_interfaced_flag IN ('N','P')
352 --and source_code = 'OE'
353 and released_status <> 'D'
354 and organization_id = i.organization_id
355 and rownum = 1;
356
357 if nvl(l_master_count, 0) = 1 then
358 exit;
359 end if;
360 end loop;
361 */
362 begin
363 select count(1) into l_master_count
364 from wsh_delivery_details
365 join mtl_parameters mp using (organization_id)
366 where inventory_item_id = p_item_id
367 and pickable_flag = 'Y'
368 --Bug 4643978 - Perf fix
369 and inv_interfaced_flag IN ('N','P')
370 --and source_code = 'OE'
371 and released_status <> 'D'
372 and mp.master_organization_id = l_master_org
373 group by organization_id
374 having count(1) = 1;
375 exception
376 when others then
377 l_master_count := 0;
378 end;
379 -- fix bug: 14396273 - end, performance issue : master item entry form
380 if nvl(l_master_count, 0) <> 1 then
381 select count(1) into l_master_count /*l_org_count */ /*Bugfix 8460461 */
382 from oe_order_lines_all
383 where source_type_code = 'EXTERNAL'
384 and open_flag = 'Y'
385 and nvl(shipped_quantity,0) = 0
386 and item_type_code in ('MODEL','STANDARD','OPTION')
387 and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
388 and inventory_item_id = p_item_id
389 -- bug 9558897, base bug 9327156, perf issue
390 and ship_from_org_id in (select organization_id
391 from mtl_parameters
392 where master_organization_id = l_master_org)
393 -- bug 9558897, base bug 9327156, perf issue
394 and rownum = 1;
395 end if;
396
397 if nvl(l_master_count, 0) <> 1 then
398
399 select count(1) into l_master_count /*l_org_count *//*bugfix 8460461 */
400 from oe_order_lines_all l
401 where booked_flag = 'Y'
402 and nvl(shipped_quantity,0) = 0
403 and inventory_item_id = p_item_id
404 and open_flag = 'Y'
405 -- bug 9558897, base bug 9327156, perf issue
406 and ship_from_org_id in (select organization_id
407 from mtl_parameters
408 where master_organization_id = l_master_org)
409 -- bug 9558897, base bug 9327156, perf issue
410 and exists (select 1
411 from mtl_transactions_interface
412 where trx_source_line_id = l.line_id
413 and transaction_source_type_id in (2,8)
414 and source_code = 'ORDER ENTRY')
415 and rownum = 1;
416
417 end if;
418
419 if nvl(l_master_count, 0) <> 1 then -- Added for bug#8460461 Starts here
420 -- fix bug: 14396273 - start, performance issue : master item entry form
421 /*
422 for i in (select organization_id
423 from mtl_parameters
424 where master_organization_id = l_master_org)
425 loop
426 select count(1) into l_master_count
427 from mtl_supply
428 where item_id = p_item_id
429 and to_organization_id = i.organization_id
430 and rownum =1 ;
431
432 if nvl(l_master_count, 0) = 1 then
433 exit;
434 end if;
435 end loop;
436 */
437 begin
438 select count(1) into l_master_count
439 from mtl_supply
440 join mtl_parameters mp on to_organization_id = organization_id
441 where item_id = p_item_id
442 and mp.master_organization_id = l_master_org
443 group by to_organization_id
444 having count(1) = 1;
445 exception
446 when others then
447 l_master_count := 0;
448 end;
449 -- fix bug: 14396273 - end, performance issue : master item entry form
450 end if; --end bugfix 8460461
451
452 -- bug 16227090 start
453 if nvl(l_master_count, 0) <> 1 then
454
455 SELECT count(1)
456 into l_master_count
457 FROM GME_MATERIAL_DETAILS D, GME_BATCH_HEADER H
458 WHERE D.INVENTORY_ITEM_ID = p_item_id
459 AND D.ORGANIZATION_ID in
460 (select organization_id
461 from mtl_parameters
462 where master_organization_id = l_master_org)
463 AND D.BATCH_ID = H.BATCH_ID
464 AND (NVL(H.GL_POSTED_IND, 0) = 0 AND
465 H.BATCH_STATUS IN (1, 2, 3, 4))
466 AND rownum = 1;
467
468 end if;
469 --bug 16227090 end
470
471 end if;
472
473 if (l_org_count = 1 or l_master_count = 1) then
474 return TRUE;
475 else
476 return FALSE;
477 end if;
478
479 end transactable_uncheck;
480
481
482 function check_pending_interface(p_org_id in number,
483 p_item_id in number,
484 p_source_item varchar2) return boolean IS
485 l_org_count number := 0;
486 l_master_count number := 0;
487 l_master_org number := 0;
488 begin
489 -- check at org level
490 select count(1) into l_org_count
491 from wsh_delivery_details
492 where inventory_item_id = p_item_id
493 and oe_interfaced_flag = 'Y'
494 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
495 and released_status = 'C'
496 and source_code = 'OE'
497 and organization_id = p_org_id
498 and rownum = 1;
499
500 -- check at master level
501 if (l_org_count <> 1 and
502 get_attribute_control(p_source_item) = 1 ) then
503 select master_organization_id into l_master_org
504 from mtl_parameters
505 where organization_id = p_org_id;
506 select count(1)
507 into l_master_count
508 from wsh_delivery_details
509 where inventory_item_id = p_item_id
510 and oe_interfaced_flag = 'Y'
511 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
512 and released_status = 'C'
513 and source_code = 'OE'
514 and organization_id in
515 (select organization_id
516 from mtl_parameters
517 where master_organization_id = l_master_org)
518 and rownum = 1;
519 end if;
520
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 check_pending_interface;
527
528
529 -- When changes from None to Any control
530 function serial_check(p_org_id in number,
531 p_item_id in number) return boolean IS
532 l_org_count number := 0;
533 l_master_count number := 0;
534 l_master_org number := 0;
535 begin
536 -- check at org level
537 select count(1)
538 into l_org_count
539 from wsh_delivery_details
540 where inventory_item_id = p_item_id
541 and released_status not in ('B','R','D')
542 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
543 and pickable_flag = 'Y'
544 and source_code = 'OE'
545 and organization_id = p_org_id
546 and rownum = 1;
547
548 -- check at master level
549 if (l_org_count <> 1 and
550 get_attribute_control('SERIAL_NUMBER_CONTROL_CODE') = 1 ) then
551 select master_organization_id into l_master_org
552 from mtl_parameters
553 where organization_id = p_org_id;
554 select count(1)
555 into l_master_count
556 from wsh_delivery_details
557 where inventory_item_id = p_item_id
558 and released_status not in ('B','R','D')
559 and nvl(inv_interfaced_flag,'N') not in ('Y','X')
560 and pickable_flag = 'Y'
561 and source_code = 'OE'
562 and organization_id in
563 (select organization_id
564 from mtl_parameters
565 where master_organization_id = l_master_org)
566 and rownum = 1;
567 end if;
568
569 if (l_org_count = 1 or l_master_count = 1) then
570 return TRUE;
571 else
572 return FALSE;
573 end if;
574 end serial_check;
575
576 -- When changes from Yes to No
577 function ato_uncheck(p_org_id in number,
578 p_item_id in number) return boolean is
579 l_org_count number := 0;
580 l_master_count number := 0;
581 l_master_org number := 0;
582 begin
583 select count(1) into l_org_count
584 from oe_order_lines_all
585 where inventory_item_id = p_item_id
586 and open_flag = 'Y' -- Bug 8247770
587 and cancelled_flag = 'N'
588 and ship_from_org_id = p_org_id
589 and rownum = 1;
590 if (l_org_count <> 1 and
591 get_attribute_control('REPLENISH_TO_ORDER_FLAG') = 1 ) then
592 select master_organization_id into l_master_org
593 from mtl_parameters
594 where organization_id = p_org_id;
595
596 select count(1) into l_master_count
597 from oe_order_lines_all
598 where inventory_item_id = p_item_id
599 and open_flag = 'Y' -- Bug 8247770
600 and cancelled_flag = 'N'
601 and ship_from_org_id in (select organization_id
602 from mtl_parameters
603 where master_organization_id = l_master_org)
604 and rownum = 1;
605 end if;
606 if (l_org_count = 1 or l_master_count = 1) then
607 return TRUE;
608 else
609 return FALSE;
610 end if;
611 end ato_uncheck;
612
613 -- when changes from no to Yes
614 function shippable_check(p_org_id in number,
615 p_item_id in number) return boolean is
616 l_org_count number := 0;
617 l_master_count number := 0;
618 l_master_org number := 0;
619 begin
620 select count(1) into l_org_count
621 from oe_order_lines_all
622 where inventory_item_id = p_item_id
623 and open_flag = 'Y' -- Bug 8247770
624 and cancelled_flag = 'N'
625 and ship_from_org_id = p_org_id
626 and rownum = 1;
627
628 if (l_org_count <> 1 and
629 get_attribute_control('SHIPPABLE_ITEM_FLAG') = 1 ) then
630 select master_organization_id into l_master_org
631 from mtl_parameters
632 where organization_id = p_org_id;
633
634 select count(1) into l_master_count
635 from oe_order_lines_all
636 where inventory_item_id = p_item_id
637 and open_flag = 'Y' -- Bug 8247770
638 and cancelled_flag = 'N'
639 and ship_from_org_id in
640 (select organization_id
641 from mtl_parameters
642 where master_organization_id = l_master_org)
643 and rownum = 1;
644 end if;
645 if (l_org_count = 1 or l_master_count = 1) then
646 return TRUE;
647 else
648 return FALSE;
649 end if;
650 end shippable_check;
651
652 end;
[Home] [Help]