[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT end_assembly_pegging_flag
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
select application_column_name, required_flag
from fnd_id_flex_segments
where application_id = 401
and id_flex_code = 'MTLL'
and id_flex_num = 101
and application_column_name not in ('SEGMENT19','SEGMENT20')
and nvl(enabled_flag, 'N') = 'Y'
order by segment_num;
L_stmt := 'SELECT l2.inventory_location_id, ' ||
'l2.subinventory_code, ' ||
'DECODE(l1.subinventory_code, ' ||
'l2.subinventory_code, 1, ' ||
'NULL, NULL, 0) ' ||
'FROM mtl_item_locations l1 ' ||
', mtl_item_locations l2 ' ||
'WHERE l1.organization_id = :org_id ' ||
'AND l2.organization_id = l1.organization_id ' ||
'AND l1.inventory_location_id = :locator_id ';
select mtl_item_locations_s.nextval
into L_locator_id
from dual;
insert into mtl_item_locations
( last_update_date
, last_updated_by
, creation_date
, created_by
, inventory_location_id
, organization_id
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, segment7
, segment8
, segment9
, segment10
, segment11
, segment12
, segment13
, segment14
, segment15
, segment16
, segment17
, segment18
, segment19
, segment20
, summary_flag
, enabled_flag
, subinventory_code
, physical_location_id)
select sysdate
, L_user_id
, sysdate
, L_user_id
, L_locator_id
, X_organization_id
, segment1
, segment2
, segment3
, segment4
, segment5
, segment6
, segment7
, segment8
, segment9
, segment10
, segment11
, segment12
, segment13
, segment14
, segment15
, segment16
, segment17
, segment18
, X_project_id
, X_task_id
, 'N'
, 'Y'
, subinventory_code
, decode(X_project_id,
NULL, L_locator_id,
nvl(physical_location_id,inventory_location_id)
)
from mtl_item_locations
where organization_id = X_organization_id
and inventory_location_id = X_locator_id;
select nvl(msi.locator_type , 0)
into L_locator_ctrl
from mtl_secondary_inventories msi
, mtl_item_locations mil
where mil.organization_id = X_organization_id
and mil.inventory_location_id = X_locator_id
and msi.organization_id (+) = mil.organization_id
and msi.secondary_inventory_name (+) = mil.subinventory_code;
insert into mtl_secondary_locators
( last_update_date
, last_updated_by
, creation_date
, created_by
, secondary_locator
, inventory_item_id
, organization_id
, subinventory_code)
select sysdate
, L_user_id
, sysdate
, L_user_id
, L_locator_id
, inventory_item_id
, X_organization_id
, subinventory_code
from mtl_secondary_locators msl
where organization_id = X_organization_id
and secondary_locator = X_locator_id
and exists (
select 'Locator restriction is on'
from mtl_system_items
where organization_id = msl.organization_id
and inventory_item_id = msl.inventory_item_id
and restrict_locators_code = 1);
select project_id
, task_id
, physical_location_id
into L_project_id
, L_task_id
, L_phy_loc_id
from mtl_item_locations
where organization_id = X_organization_id
and inventory_location_id = X_locator_id;
update mtl_item_locations
set physical_location_id = L_phy_loc_id
where organization_id = X_organization_id
and inventory_location_id = X_locator_id;
SELECT nvl(allow_cross_proj_issues,'N')
FROM pjm_org_parameters
WHERE organization_id = C_organization_id;
SELECT primary_cost_method
FROM mtl_parameters
WHERE organization_id = C_organization_id;
SELECT planning_group
, nvl(costing_group_id,0)
FROM pjm_project_parameters
WHERE organization_id = C_organization_id
AND project_id = C_project_id;
SELECT project_reference_enabled,
project_control_level,
organization_code
from mtl_parameters
where organization_id = org_id;
SELECT project_id,
task_id
FROM mtl_item_locations
WHERE inventory_location_id = loc_id
AND organization_id = org_id;
SELECT project_id
FROM pjm_projects_v
WHERE project_id = p_id;
SELECT task_id
FROM pjm_tasks_v
WHERE task_id = t_id;
SELECT 1
FROM MTL_ITEM_SUB_INVENTORIES
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND secondary_inventory = p_sub;
SELECT 1
FROM MTL_SECONDARY_LOCATORS
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND secondary_locator = p_loc;
SELECT mtl.end_assembly_pegging_flag,
mtl.location_control_code,
NVL(mtl.restrict_locators_code,2),
NVL(mtl.restrict_subinventories_code,2),
mp.stock_locator_control_code
INTO l_peg_flag,
l_item_loc_control,
l_item_loc_restrict,
l_item_sub_restrict,
l_org_loc_control
FROM mtl_system_items mtl,
mtl_parameters mp
WHERE mtl.inventory_item_id = p_item_id
AND mtl.organization_id = p_organization_id
AND mtl.organization_id = mp.organization_id;
SELECT sub.locator_type
INTO l_sub_loc_control
FROM mtl_secondary_inventories sub
WHERE sub.secondary_inventory_name = p_sub
AND sub.organization_id = p_organization_id;
the locator control allows dynamic insert
without any restriction on locator or subinventory.
Note that there are three levels of locator
control: Org, Sub, Item
*/
IF (l_org_loc_control = 3
or
(l_org_loc_control = 4 and
l_sub_loc_control = 3)
or
(l_org_loc_control = 4 and
l_sub_loc_control = 5 and
l_item_loc_control = 3)) THEN
/* If the item has a restricted list of subinventory,
then check if the input subinventory is within the resticted list */
IF (l_item_sub_restrict = 1) THEN
OPEN C_ITEM_SUB;
SELECT rowid
from mtl_item_locations
where inventory_location_id = v_loc_id
and organization_id = v_org_id
and nvl(project_id, -1) = nvl(v_project_id, -1)
and nvl(task_id, -1) = nvl(v_task_id, -1);
SELECT physical_location_id
FROM mtl_item_locations
WHERE inventory_location_id = v_loc_id
and organization_id = v_org_id;
** as selected above for the organization_id, project_id and task_id
** passed in. Dynamic SQL is employed here for performance.
*/
p_project_locator_id := map_locator( p_organization_id
, p_locator_id
, p_project_id
, p_task_id
, TRUE );
SELECT rop.inventory_item_id,
rop.supply_subinventory,
rop.supply_locator_id,
rop.wip_supply_type, rop.rowid
FROM wip_requirement_operations rop
WHERE rop.organization_id = V_org_id
AND rop.wip_entity_id = V_wip_entity_id
AND rop.supply_locator_id is not null
ORDER BY rop.operation_seq_num
FOR UPDATE;
SELECT NVL(mp.project_reference_enabled, 2),
mp.stock_locator_control_code
INTO L_proj_ref_enabled,
l_org_loc_control
FROM mtl_parameters mp
WHERE mp.organization_id = p_organization_id;
UPDATE wip_requirement_operations rop
SET rop.supply_locator_id = L_supply_loc_id
WHERE rop.rowid = L_row_id;
SELECT mti.inventory_item_id,
mti.subinventory_code,
mti.locator_id,
mti.rowid
FROM mtl_transactions_interface mti
WHERE mti.organization_id = V_org_id
AND ( V_wip_entity_id is null
OR mti.transaction_source_id = V_wip_entity_id )
AND mti.locator_id is not null
AND mti.parent_id = V_parent_id
AND mti.transaction_action_id in (1, 27, 33, 34)
AND mti.transaction_source_type_id = 5
AND mti.flow_schedule = 'Y'
ORDER BY mti.operation_seq_num
FOR UPDATE;
SELECT NVL(mp.project_reference_enabled, 2),
mp.stock_locator_control_code
INTO L_proj_ref_enabled,
l_org_loc_control
FROM mtl_parameters mp
WHERE mp.organization_id = p_organization_id;
update mtl_transactions_interface
set(locator_id, project_id, task_id) =
(select inventory_location_id, project_id, task_id
from mtl_item_locations
where inventory_location_id = l_supply_loc_id
and organization_id = p_organization_id)
where rowid = l_row_id;