[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select control_level
into l_control_level
from mtl_item_attributes
where attribute_name = attr_name;
select count(1)
into l_org_count
from mtl_cycle_count_entries
where inventory_item_id = p_item_id and
organization_id = p_org_id and
/*Change for bug 8208397*/ /*Check should be for status 1-Uncounted/2-Pending Approval/3-Recount*/
/*entry_status_code = 2 and*/
entry_status_code IN (1,2,3) and
rownum = 1;
select count(1)
into l_org_count
from mtl_physical_adjustments
where inventory_item_id = p_item_id
and organization_id = p_org_id
and approval_status = 1
and adjustment_quantity <> 0
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1)
into l_master_count
from mtl_cycle_count_entries
where inventory_item_id = p_item_id and
(organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org
)
)
/*Change for bug 8208397*/ /*Check should be for status 1-Uncounted/2-Pending Approval/3-Recount*/
/*and entry_status_code = 2*/
and entry_status_code IN (1,2,3)
and rownum = 1;
select count(1)
into l_master_count
from mtl_physical_adjustments
where inventory_item_id = p_item_id
and (organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org
)
)
and approval_status = 1
and adjustment_quantity <> 0
and rownum = 1;
select count(1) into l_org_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and released_status = 'S'
and source_code = 'OE'
and organization_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1) into l_master_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and released_status = 'S'
and source_code = 'OE'
and organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;
select count(1) into l_org_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and released_status = 'Y'
and source_code = 'OE'
and organization_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1) into l_master_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and released_status = 'Y'
and source_code = 'OE'
and organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;
select count(1) into l_org_count
from oe_order_lines_all l,
wsh_delivery_details wdd
where l.inventory_item_id = p_item_id
and l.open_flag = 'Y' -- Bug 8247770
and wdd.released_status not in ('C','D')
and wdd.pickable_flag = 'N'
--and wdd.source_code = 'OE'
and l.line_id = wdd.source_line_id
and l.ship_from_org_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1) into l_master_count
from oe_order_lines_all l,
wsh_delivery_details wdd
where l.inventory_item_id = p_item_id
and l.open_flag = 'Y' -- Bug 8247770
and wdd.released_status not in ('C','D')
and wdd.pickable_flag = 'N'
--and wdd.source_code = 'OE'
and l.line_id = wdd.source_line_id
and l.ship_from_org_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;
select count(1) into l_org_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and pickable_flag = 'Y'
--Bug 4643978 - Perf fix
and inv_interfaced_flag IN ('N','P')
--and source_code = 'OE'
and released_status <> 'D'
and organization_id = p_org_id
and rownum = 1;
select count(1) into l_org_count
from oe_order_lines_all
where source_type_code = 'EXTERNAL'
and open_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and item_type_code in ('MODEL','STANDARD','OPTION')
and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
and inventory_item_id = p_item_id
and ship_from_org_id = p_org_id
and rownum = 1;
select count(1) into l_org_count
from oe_order_lines_all l
where booked_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and inventory_item_id = p_item_id
and open_flag = 'Y'
and ship_from_org_id = p_org_id
and exists (select 1
from mtl_transactions_interface
where trx_source_line_id = l.line_id
and transaction_source_type_id in (2,8)
and source_code = 'ORDER ENTRY')
and rownum = 1;
select count(1)
into l_org_count
from mtl_supply
where item_id = p_item_id
and to_organization_id =p_org_id
and rownum =1 ;
SELECT count(1)
into l_org_count
FROM GME_MATERIAL_DETAILS D, GME_BATCH_HEADER H
WHERE D.INVENTORY_ITEM_ID = p_item_id
AND D.ORGANIZATION_ID = p_org_id
AND D.BATCH_ID = H.BATCH_ID
AND (NVL(H.GL_POSTED_IND, 0) = 0 AND
H.BATCH_STATUS IN (1, 2, 3, 4))
AND rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
for i in (select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
loop
select count(1) into l_master_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and pickable_flag = 'Y'
--Bug 4643978 - Perf fix
and inv_interfaced_flag IN ('N','P')
--and source_code = 'OE'
and released_status <> 'D'
and organization_id = i.organization_id
and rownum = 1;
select count(1) into l_master_count
from wsh_delivery_details
join mtl_parameters mp using (organization_id)
where inventory_item_id = p_item_id
and pickable_flag = 'Y'
--Bug 4643978 - Perf fix
and inv_interfaced_flag IN ('N','P')
--and source_code = 'OE'
and released_status <> 'D'
and mp.master_organization_id = l_master_org
group by organization_id
having count(1) = 1;
select count(1) into l_master_count /*l_org_count */ /*Bugfix 8460461 */
from oe_order_lines_all
where source_type_code = 'EXTERNAL'
and open_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and item_type_code in ('MODEL','STANDARD','OPTION')
and FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
and inventory_item_id = p_item_id
-- bug 9558897, base bug 9327156, perf issue
and ship_from_org_id in (select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
-- bug 9558897, base bug 9327156, perf issue
and rownum = 1;
select count(1) into l_master_count /*l_org_count *//*bugfix 8460461 */
from oe_order_lines_all l
where booked_flag = 'Y'
and nvl(shipped_quantity,0) = 0
and inventory_item_id = p_item_id
and open_flag = 'Y'
-- bug 9558897, base bug 9327156, perf issue
and ship_from_org_id in (select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
-- bug 9558897, base bug 9327156, perf issue
and exists (select 1
from mtl_transactions_interface
where trx_source_line_id = l.line_id
and transaction_source_type_id in (2,8)
and source_code = 'ORDER ENTRY')
and rownum = 1;
for i in (select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
loop
select count(1) into l_master_count
from mtl_supply
where item_id = p_item_id
and to_organization_id = i.organization_id
and rownum =1 ;
select count(1) into l_master_count
from mtl_supply
join mtl_parameters mp on to_organization_id = organization_id
where item_id = p_item_id
and mp.master_organization_id = l_master_org
group by to_organization_id
having count(1) = 1;
SELECT count(1)
into l_master_count
FROM GME_MATERIAL_DETAILS D, GME_BATCH_HEADER H
WHERE D.INVENTORY_ITEM_ID = p_item_id
AND D.ORGANIZATION_ID in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
AND D.BATCH_ID = H.BATCH_ID
AND (NVL(H.GL_POSTED_IND, 0) = 0 AND
H.BATCH_STATUS IN (1, 2, 3, 4))
AND rownum = 1;
select count(1) into l_org_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and oe_interfaced_flag = 'Y'
and nvl(inv_interfaced_flag,'N') not in ('Y','X')
and released_status = 'C'
and source_code = 'OE'
and organization_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1)
into l_master_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and oe_interfaced_flag = 'Y'
and nvl(inv_interfaced_flag,'N') not in ('Y','X')
and released_status = 'C'
and source_code = 'OE'
and organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;
select count(1)
into l_org_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and released_status not in ('B','R','D')
and nvl(inv_interfaced_flag,'N') not in ('Y','X')
and pickable_flag = 'Y'
and source_code = 'OE'
and organization_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1)
into l_master_count
from wsh_delivery_details
where inventory_item_id = p_item_id
and released_status not in ('B','R','D')
and nvl(inv_interfaced_flag,'N') not in ('Y','X')
and pickable_flag = 'Y'
and source_code = 'OE'
and organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;
select count(1) into l_org_count
from oe_order_lines_all
where inventory_item_id = p_item_id
and open_flag = 'Y' -- Bug 8247770
and cancelled_flag = 'N'
and ship_from_org_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1) into l_master_count
from oe_order_lines_all
where inventory_item_id = p_item_id
and open_flag = 'Y' -- Bug 8247770
and cancelled_flag = 'N'
and ship_from_org_id in (select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;
select count(1) into l_org_count
from oe_order_lines_all
where inventory_item_id = p_item_id
and open_flag = 'Y' -- Bug 8247770
and cancelled_flag = 'N'
and ship_from_org_id = p_org_id
and rownum = 1;
select master_organization_id into l_master_org
from mtl_parameters
where organization_id = p_org_id;
select count(1) into l_master_count
from oe_order_lines_all
where inventory_item_id = p_item_id
and open_flag = 'Y' -- Bug 8247770
and cancelled_flag = 'N'
and ship_from_org_id in
(select organization_id
from mtl_parameters
where master_organization_id = l_master_org)
and rownum = 1;