[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FOR cur IN (SELECT control_level, attribute_name
FROM mtl_item_attributes
WHERE attribute_name IN ('MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE'
,'MTL_SYSTEM_ITEMS.SHELF_LIFE_CODE'
,'MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE'
,'MTL_SYSTEM_ITEMS.REVISION_QTY_CONTROL_CODE'
,'MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE'
,'MTL_SYSTEM_ITEMS.COMMS_NL_TRACKABLE_FLAG'
,'MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND'
,'MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE'
,'MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE'
,'MTL_SYSTEM_ITEMS.SECONDARY_DEFAULT_IND'
,'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_HIGH'
,'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_LOW'
,'MTL_SYSTEM_ITEMS.CHILD_LOT_FLAG'
,'MTL_SYSTEM_ITEMS.LOT_DIVISIBLE_FLAG'
,'MTL_SYSTEM_ITEMS.GRADE_CONTROL_FLAG'
,'MTL_SYSTEM_ITEMS.RESERVABLE_TYPE'
,'MTL_SYSTEM_ITEMS.SHIPPABLE_ITEM_FLAG'
,'MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG'
,'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG'
,'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG')) -- bug 6501149
LOOP
IF cur.attribute_name = 'MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE' THEN
lot_level := cur.control_level;
SELECT COUNT(1) INTO onhand_org_count
FROM mtl_onhand_quantities_detail -- Bug:2687570
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND ROWNUM = 1;
SELECT COUNT(1) INTO onhand_master_count
FROM mtl_onhand_quantities_detail -- Bug:2687570
WHERE inventory_item_id = p_item_id
AND (organization_id IN (SELECT organization_id
FROM mtl_parameters
WHERE master_organization_id = p_master_org))
AND ROWNUM = 1;
SELECT count(1) INTO material_org_count
FROM mtl_material_transactions_temp
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND rownum = 1;
SELECT count(1) INTO material_org_count
FROM mtl_supply
WHERE item_id = p_item_id
AND (from_organization_id = p_org_id OR to_organization_id = p_org_id)
AND rownum = 1;
SELECT count(1) INTO material_org_count_ls
FROM mtl_supply
WHERE item_id = p_item_id
AND (from_organization_id = p_org_id
OR to_organization_id = p_org_id)
and supply_type_code in ('RECEIVING', 'SHIPMENT')
AND rownum = 1;
SELECT COUNT(1) INTO material_org_count
FROM mtl_demand
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND rownum = 1;
SELECT COUNT(1) INTO material_master_count
FROM mtl_material_transactions_temp
WHERE inventory_item_id = p_item_id
AND (organization_id IN (SELECT organization_id
FROM mtl_parameters
WHERE master_organization_id = p_master_org))
AND rownum = 1;
SELECT COUNT(1) INTO material_master_count
FROM mtl_supply ms
WHERE ms.item_id = p_item_id
AND EXISTS (SELECT 1
FROM mtl_parameters
WHERE master_organization_id = p_master_org
AND (organization_id = ms.from_organization_id OR organization_id = ms.to_organization_id))
AND rownum = 1;
SELECT COUNT(1)
INTO material_master_count_ls
FROM mtl_supply ms
WHERE ms.item_id = p_item_id
AND EXISTS (SELECT 1
FROM mtl_parameters
WHERE master_organization_id = p_master_org
AND (organization_id = ms.from_organization_id
OR organization_id = ms .to_organization_id))
AND supply_type_code in ('RECEIVING', 'SHIPMENT')
AND rownum = 1;
SELECT COUNT(1) INTO material_master_count
FROM mtl_demand md
WHERE md.inventory_item_id = p_item_id
AND EXISTS (SELECT 1
FROM mtl_parameters
WHERE master_organization_id = p_master_org
AND organization_id = md.organization_id)
AND rownum = 1;
select count(1)
into lots_org_count
from mtl_lot_numbers
where inventory_item_id = p_item_id and
organization_id = p_org_id and
rownum = 1;
select count(1)
into lots_master_count
from mtl_lot_numbers mln
where inventory_item_id = p_item_id
and exists (select 1
from mtl_parameters
where master_organization_id = p_master_org
and organization_id = mln.organization_id)
and rownum = 1;
select count(1)
into l_intr_ship_org
from mtl_supply
where supply_type_code = 'SHIPMENT'
and item_id = p_item_id
and to_organization_id = p_org_id
and from_organization_id is not null
and po_line_location_id is null
and rownum = 1;
select count(1)
into l_intr_ship_master
from mtl_supply
where supply_type_code = 'SHIPMENT'
and item_id = p_item_id
and to_organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = p_master_org)
and from_organization_id is not null
and po_line_location_id is null
and rownum = 1;
select count(1)
into l_org
from mtl_supply
where supply_type_code in ('RECEIVING', 'SHIPMENT')
and item_id = p_item_id
and to_organization_id =p_org_id
and rownum =1 ;
select count(1)
into l_master
from mtl_supply
where supply_type_code in ('RECEIVING', 'SHIPMENT')
and item_id = p_item_id
and to_organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = p_master_org)
and rownum = 1;
select count(*) into X_onhand_all
from dual
where exists ( select 'x'
from mtl_onhand_quantities_detail moh -- Bug:2687570
where moh.inventory_item_id = p_item_id
and moh.organization_id in ( select mp.organization_id
from mtl_parameters mp
where mp.master_organization_id = p_master_org));
select count(*) into X_wip_repetitive_item
from dual
where exists ( select 'x'
from WIP_REPETITIVE_ITEMS wri
where wri.PRIMARY_ITEM_ID = p_item_id
and wri.ORGANIZATION_ID in ( select mp.organization_id
from mtl_parameters mp
where mp.master_organization_id = p_master_org));
select count(1) into x_rsv_exists
from mtl_reservations res,
mtl_parameters param
where res.inventory_item_id = p_item_id
AND res.organization_id = param.organization_id
and param.master_organization_id = p_master_org
and reservation_quantity > 0
and rownum = 1 ;
select count(1) into X_rsv_exists
from mtl_reservations
where organization_id = p_org_id
and inventory_item_id = p_item_id
and reservation_quantity > 0
and rownum = 1 ;
select count(1) into shipping_level
from mtl_item_attributes
where control_level = 1
and attribute_name=attr_name;
select 1 into X_so_ship
from oe_order_lines_all l
where l.inventory_item_id = p_item_id
and l.open_flag = 'Y'
and nvl(l.shipping_interfaced_flag,'N') = 'N'
and l.ship_from_org_id = p_org_id
and rownum = 1;
select 1 into X_so_ship
from oe_order_lines_all l
where l.inventory_item_id = p_item_id
and l.open_flag = 'Y'
and nvl(l.shipping_interfaced_flag,'N') = 'N'
and l.ship_from_org_id in
(select organization_id
from mtl_parameters
where master_organization_id = p_master_org
)
and rownum = 1;
select 1 into X_so_ship
from wsh_delivery_details wdd
where wdd.inventory_item_id = p_item_id
and wdd.inv_interfaced_flag in ('N','P')
-- Bug 3963689 Condition added so that if no sales order and on hand qty 0
--then shippable flag of the item can be modified - Anmurali
and wdd.released_status <> 'D'
and wdd.source_code = 'OE'
and wdd.organization_id = p_org_id
and rownum = 1;
for i in (select organization_id
from mtl_parameters
where master_organization_id = p_master_org)
loop
begin
select 1 into X_so_ship
from wsh_delivery_details wdd
where wdd.inventory_item_id = p_item_id
and wdd.inv_interfaced_flag in ('N','P')
-- Bug 3963689 Condition added so that if no sales order and on hand qty 0
--then shippable flag of the item can be modified - Anmurali
and wdd.released_status <> 'D'
and wdd.source_code = 'OE'
and wdd.organization_id = i.organization_id
and rownum = 1;
select count(1)
into X_so_txn
from oe_order_lines_all l
where l.inventory_item_id = p_item_id
and l.open_flag || '' = 'Y'
and (l.ship_from_org_id in
(select organization_id
from mtl_parameters
where master_organization_id= p_master_org
and 1=transaction_level)
or l.ship_from_org_id= p_org_id)
and rownum = 1;
select count(*) into X_so_open_exists from dual
where exists
( select * from oe_order_lines_all
where inventory_item_id = p_item_id
and open_flag || '' = 'Y' );
select count(1)
into X_demand_exists
from oe_order_lines_all
where inventory_item_id = p_item_id
and visible_demand_flag = 'Y'
and shipped_quantity is NOT null
and (ship_from_org_id in
(select organization_id
from mtl_parameters
where master_organization_id = p_master_org
and 1=(select control_level
from mtl_item_attributes
where
attribute_name='MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG'
)
)
or ship_from_org_id = p_org_id)
and rownum = 1;
select count(*)
into uom_conv
from dual
where exists
( select 'x'
from mtl_uom_conversions
where inventory_item_id = p_item_id
and uom_code = p_primary_uom_code
);
select decode(base_uom_flag, 'Y', 1, 0)
into uom_conv
from mtl_units_of_measure_vl
where uom_code = p_primary_uom_code;
select count(1)
into uom_other_conv
from sys.dual
where exists
(select 'x' from mtl_uom_conversions
where inventory_item_id = p_item_id)
or exists
(select 'x' from mtl_uom_class_conversions
where inventory_item_id = p_item_id);
select LEADING(BIC) INDEX(BIC BOM_INVENTORY_COMPONENTS_N1) USE_NL(BIC BOM)
count(1)
into X_comp_atp
from bom_inventory_components bic, bom_bill_of_materials bom
where bic.bill_sequence_id = bom.common_bill_sequence_id
and bic.component_item_id = p_item_id
and bic.check_atp = 1
and (bom.organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = p_master_org
and 1 = (select control_level
from mtl_item_attributes
where attribute_name= 'MTL_SYSTEM_ITEMS.ATP_FLAG')
)
or bom.organization_id = p_org_id)
and rownum = 1;
/*select count(*) into bom_row_exists from dual
where exists
( select 'x' from bom_bill_of_materials bom
where bom.assembly_item_id = p_item_id
and bom.organization_id in
( select organization_id
from mtl_parameters
where master_organization_id = p_master_org
)
);*/
select count(*) into bom_row_exists from dual
where exists
( select 'x' from bom_bill_of_materials bom
where bom.assembly_item_id = p_item_id
and bom.organization_id in
( select organization_id
from mtl_parameters
where master_organization_id = p_master_org
and 1 = bom_enabled_level
union all
select organization_id
from mtl_parameters
where organization_id = p_org_id
and 2 = bom_enabled_level
)
);
select count(1)
into bom_substitute
from bom_substitute_components sub,
bom_inventory_components inv,
bom_bill_of_materials bom
where sub.substitute_component_id = p_item_id
and sub.component_sequence_id = inv.component_sequence_id
and inv.bill_sequence_id = bom.bill_sequence_id
and bom.organization_id in
(select organization_id
from mtl_parameters
where master_organization_id= p_master_org)
and rownum = 1;
select /*+ LEADING(INV) INDEX(INV BOM_INVENTORY_COMPONENTS_N1) USE_NL(INV BOM) */
count(1)
into bom_inventory
from bom_inventory_components inv, bom_bill_of_materials bom
where inv.component_item_id = p_item_id
and inv.bill_sequence_id=bom.bill_sequence_id
and bom.organization_id in
(select organization_id
from mtl_parameters
where master_organization_id= p_master_org)
and rownum = 1;
select count(1)
into cost_moq
from mtl_onhand_quantities_detail -- Bug:2687570
where inventory_item_id = p_item_id
and organization_id in
(select organization_id
from mtl_parameters
where cost_organization_id = p_org_id)
and rownum = 1;
select count(1)
into cost_moq2
from mtl_onhand_quantities_detail -- Bug:2687570
where inventory_item_id = p_item_id
and organization_id in
(select organization_id
from mtl_parameters
where master_organization_id = p_master_org
and (1=inv_asset_level OR 1=cost_enabled_level))
and rownum = 1;
select count(1)
into cost_tmp
from mtl_material_transactions_temp
where inventory_item_id = p_item_id
and organization_id in
(select organization_id
from mtl_parameters
where cost_organization_id = p_org_id)
and rownum = 1;
select count(1)
into cost_mmt
from mtl_material_transactions
where inventory_item_id = p_item_id
and organization_id in
(select organization_id
from mtl_parameters
where cost_organization_id = p_org_id)
and costed_flag is not null
and rownum = 1;
select count(1)
into X_null_elem_exists
from mtl_descriptive_elements e,
mtl_descr_element_values v
where e.required_element_flag = 'Y'
and e.item_catalog_group_id = p_catalog_group_id
and v.inventory_item_id = p_item_id
and v.element_name = e.element_name
and v.element_value is null
and rownum = 1;
select count(1)
into X_mrp_schedule
from mrp_schedule_items
where inventory_item_id = p_item_id
and organization_id = p_org_id
and rownum = 1;
SELECT count(1)
INTO l_tab_exists
FROM TAB
WHERE TNAME = 'FTE_VEHICLE_TYPES'
AND ROWNUM = 1;
'SELECT count(1) '||
'FROM FTE_VEHICLE_TYPES '||
'WHERE INVENTORY_ITEM_ID = :p_item_id '||
'AND ORGANIZATION_ID = :p_org_id '||
'AND ROWNUM = 1'
INTO X_fte_vechicle_exists USING IN p_item_id, IN p_org_id;
select count(1)
into X_process_enabled
from mtl_parameters
where organization_id = p_org_id
and process_enabled_flag = 'Y'
and rownum = 1;
'SELECT ''Y''
FROM eng_revised_items eri
WHERE eri.revised_item_id = :cp_item_id
AND eri.status_type NOT IN (5, 6)
AND ( eri.NEW_ITEM_REVISION_ID IS NOT null --this CO creates a revision
OR EXISTS --this CO has AML Change
(SELECT NULL
FROM ego_mfg_part_num_chgs
WHERE change_line_id = eri.revised_item_sequence_id )
OR EXISTS --this CO has UDA Change
(SELECT NULL
FROM ego_items_attrs_changes_b
WHERE change_line_id = eri.revised_item_sequence_id )
OR EXISTS --this CO has Attachment Change
(SELECT NULL
FROM eng_attachment_changes
WHERE revised_item_sequence_id = eri.revised_item_sequence_id )
OR EXISTS --this CO has Operational Attribute Change
(SELECT NULL
FROM ego_mtl_sy_items_chg_b
WHERE change_line_id = eri.revised_item_sequence_id
AND change_id = eri.change_id)
OR EXISTS --this CO has GTIN Single Change
(SELECT NULL
FROM ego_gtn_attr_chg_b
WHERE change_line_id = eri.revised_item_sequence_id
AND change_id = eri.change_id)
OR EXISTS --this CO has GTIN Multi Change
(SELECT NULL
FROM ego_gtn_mul_attr_chg_b
WHERE change_line_id = eri.revised_item_sequence_id
AND change_id = eri.change_id)
OR EXISTS --this CO has Related Doc Change
(SELECT NULL
FROM eng_relationship_changes
WHERE ENTITY_ID = eri.revised_item_sequence_id
AND change_id = eri.change_id
AND ENTITY_NAME=''ITEM'')
OR EXISTS --this CO has Structure Changes
(SELECT NULL
FROM bom_components_b
WHERE revised_item_sequence_id = eri.revised_item_sequence_id)
)
AND ROWNUM =1 ';
SELECT 'Y' INTO L_child_catalog
FROM mtl_item_catalog_groups_b icg
WHERE icg.item_creation_allowed_flag = 'Y' AND
((inactive_date is null) or ((trunc(inactive_date) > trunc(sysdate)) OR
(icg.item_catalog_group_id=item_catalog_group_id))) AND
icg.item_catalog_group_id = new_catalog_group_id
CONNECT BY prior icg.item_catalog_group_id = icg.parent_catalog_group_id
START WITH icg.item_catalog_group_id = old_catalog_group_id;
SELECT new_item_request_reqd
FROM mtl_item_catalog_groups_b
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = cp_catalog_group_id;
SELECT approval_status
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;