[Home] [Help]
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
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
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
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
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'
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'
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')
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')
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
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'
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'
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'
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'