[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT item_supplied
FROM csp_supersede_items
where INVENTORY_ITEM_ID = c_inventory_item_id
and ORGANIZATION_ID = c_org_id;
SELECT decode(MTL_TRANSACTIONS_ENABLED_FLAG,'Y','N','N','Y')
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = c_inventory_item_id
AND ORGANIZATION_ID = c_org_id;
select mri.related_item_id
from MTL_RELATED_ITEMS mri
where mri.ORGANIZATION_ID = nvl(c_master_org_id,mri.ORGANIZATION_ID)
and mri.RELATIONSHIP_TYPE_ID = 8
and mri.inventory_item_id = c_item_id;
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = c_organization_id;
PROCEDURE insert_item_supplied(p_planned_subinv_code IN varchar2
,p_planned_org_id IN NUMBER
,p_replaced_item_id IN NUMBER
,p_supply_level IN NUMBER
,p_master_org_id IN NUMBER
,x_return_status OUT NOCOPY varchar2);
PROCEDURE insert_item_planned(p_master_org_id IN NUMBER
,p_org_id IN NUMBER
,p_item_id IN NUMBER
,p_subinv_code IN VARCHAR2
,p_supply_chain_id IN NUMBER
,x_return_status OUT NOCOPY Varchar2);
select distinct(mp.MASTER_ORGANIZATION_ID)
from csp_planning_parameters cpp,mtl_parameters mp
where (cpp.ORGANIZATION_TYPE is not null or cpp.node_type = 'SUBINVENTORY')
and mp.ORGANIZATION_ID = cpp.ORGANIZATION_ID;
select mri.inventory_item_id ,
mri.related_item_id,
mri.reciprocal_flag
from MTL_RELATED_ITEMS mri
where mri.ORGANIZATION_ID = c_master_org_id
and mri.RELATIONSHIP_TYPE_ID = 8;
SELECT cscp.ORGANIZATION_ID,decode(cscp.SECONDARY_INVENTORY,'-',null,cscp.SECONDARY_INVENTORY),SUPPLY_LEVEL
FROM CSP_SUPPLY_CHAIN cscp, mtl_parameters mp
WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
and cscp.INVENTORY_ITEM_ID = c_inv_item_id
--and cscp.SUPPLY_LEVEL = 1
and cscp.organization_id = mp.organization_id
and cscp.source_organization_id is not null;*/
SELECT DISTINCT csc.ORGANIZATION_ID,decode(csc.SECONDARY_INVENTORY,'-',null,csc.SECONDARY_INVENTORY),SUPPLY_LEVEL
FROM CSP_SUPPLY_CHAIN csc, mtl_parameters mp, csp_planning_parameters cpp
WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.organization_id = mp.organization_id
and cpp.organization_id = csc.organization_id
and nvl(cpp.SECONDARY_INVENTORY,'-') = csc.SECONDARY_INVENTORY
and cpp.node_type = 'SUBINVENTORY' ;
select MRI.inventory_item_id
from MTL_RELATED_ITEMS mri , MTL_SYSTEM_ITEMS_B msi
where mri.ORGANIZATION_ID = c_master_org_id
and mri.RELATIONSHIP_TYPE_ID = 8
and mri.related_item_id = c_related_item_id
and mri.reciprocal_flag = 'Y'
and mri.inventory_item_id <> c_inventory_item_id
and MSI.ORGANIZATION_ID = c_master_org_id
and MSI.inventory_item_id = mri.inventory_item_id
and MSI.MTL_TRANSACTIONS_ENABLED_FLAG = 'Y';
insert_item_supplied(l_planned_sub_inv
,l_planned_org
,l_replaced_item
,l_supply_level
,l_master_org_id
,l_return_status);
PROCEDURE insert_item_supplied(p_planned_subinv_code IN varchar2
,p_planned_org_id IN NUMBER
,p_replaced_item_id IN NUMBER
,p_supply_level IN NUMBER
,p_master_org_id IN NUMBER
,x_return_status OUT NOCOPY varchar2) IS
l_inventory_item_Id NUMBER;
SELECT NVL(MIN_MINMAX_QUANTITY,0)
from MTL_ITEM_SUB_INVENTORIES
where organization_id = p_planned_org_id
and inventory_item_id = l_inventory_item_Id
and SECONDARY_INVENTORY = p_planned_subinv_code;
SELECT nvl(decode(cpr.newbuy_rop,0,null), NVL(MIN_MINMAX_QUANTITY,0))
from mtl_system_items_b msib, csp_plan_reorders cpr
where msib.organization_id = p_planned_org_id
and msib.inventory_item_id = l_inventory_item_Id
and cpr.organization_id = msib.organization_id
and cpr.inventory_item_id = msib.inventory_item_id ;
/* SELECT NVL(MIN_MINMAX_QUANTITY,0)
from mtl_system_items_b
where organization_id = p_planned_org_id
and inventory_item_id = l_inventory_item_Id;*/
select mri.related_item_id
from MTL_RELATED_ITEMS mri
where mri.ORGANIZATION_ID = c_master_org_id
and mri.RELATIONSHIP_TYPE_ID = 8
and mri.inventory_item_id = c_item_id;
SELECT csc.SOURCE_ORGANIZATION_ID, decode(csc.SOURCE_SUBINVENTORY,'-',null),cpp.ORGANIZATION_TYPE
FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
WHERE cpp.organization_id = c_org_id
and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
and csc.organization_id = c_org_id
and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
l_sql_string := 'select MRI.inventory_item_id
from MTL_RELATED_ITEMS mri , MTL_SYSTEM_ITEMS_B msi
where mri.ORGANIZATION_ID =' || p_master_org_id ||
'and mri.RELATIONSHIP_TYPE_ID = 8
and mri.related_item_id =' || l_inventory_item_id ||
'and mri.reciprocal_flag =' || '''' || 'Y' || '''' ||
'and mri.inventory_item_id <>' || l_inventory_item_id ||
'and MSI.ORGANIZATION_ID =' || p_master_org_id ||
'and MSI.inventory_item_id = mri.inventory_item_id
and MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
'and MRI.inventory_item_id NOT IN ' || l_where_string;
CSP_SUPERSEDE_ITEMS_PKG.insert_row(px_supersede_id => l_supersede_id
,p_created_by => -1
,p_creation_date => sysdate
,p_last_updated_by => -1
,p_last_update_date=> sysdate
,p_last_update_login => -1
,p_inventory_item_id => p_replaced_item_id
,p_organization_id => p_planned_org_id
,p_sub_inventory_code => nvl(p_planned_subinv_code,'-')
,p_item_supplied => l_item_supplied
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL);
END insert_item_supplied;
SELECT csc.ORGANIZATION_ID,decode(csc.SECONDARY_INVENTORY,'-',null,csc.SECONDARY_INVENTORY) ,csc.SOURCE_ORGANIZATION_ID, decode(csc.SOURCE_SUBINVENTORY,'-',null,csc.SOURCE_SUBINVENTORY)
FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
WHERE cpp.organization_id = c_org_id
and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
and csc.organization_id = c_org_id
and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
SELECT csc.ORGANIZATION_ID,decode(csc.SECONDARY_INVENTORY,'-',null,csc.SECONDARY_INVENTORY),csc.SUPPLY_LEVEL
FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
WHERE cpp.organization_id = c_org_id
and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
and csc.organization_id = c_org_id
and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
SELECT csc.SOURCE_ORGANIZATION_ID, decode(csc.SOURCE_SUBINVENTORY,'-',null)
FROM CSP_SUPPLY_CHAIN csc,csp_planning_parameters cpp
WHERE cpp.organization_id = c_org_id
and (cpp.ORGANIZATION_TYPE IS NOT null or cpp.node_type = 'SUBINVENTORY')
and cpp.secondary_inventory = nvl(c_secondary_inventory,cpp.secondary_inventory)
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.SUPPLY_LEVEL = NVL(c_supply_level,csc.SUPPLY_LEVEL)
and csc.organization_id = c_org_id
and csc.SECONDARY_INVENTORY = decode(cpp.ORGANIZATION_TYPE , 'W', '-',nvl(c_secondary_inventory,csc.SECONDARY_INVENTORY));
select distinct(mp.MASTER_ORGANIZATION_ID)
from csp_planning_parameters cpp,mtl_parameters mp
where cpp.NODE_TYPE IN('SUBINVENTORY','ORGANIZATION_WH')
and mp.ORGANIZATION_ID = cpp.ORGANIZATION_ID;
select mri.inventory_item_id
from MTL_RELATED_ITEMS mri
where mri.ORGANIZATION_ID = c_master_org_id
and mri.RELATIONSHIP_TYPE_ID = 8;
select mri.inventory_item_id
from MTL_RELATED_ITEMS mri
where mri.ORGANIZATION_ID = c_master_org_id
and mri.RELATIONSHIP_TYPE_ID = 18
and mri.inventory_item_id <> c_item_id
and mri.RELATED_ITEM_ID = c_item_id ;
SELECT DISTINCT csc.ORGANIZATION_ID,DECODE(csc.SECONDARY_INVENTORY,'-',NULL)
FROM CSP_SUPPLY_CHAIN csc, mtl_parameters mp
WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.organization_id = mp.organization_id
and csc.SOURCE_ORGANIZATION_ID IS NULL;*/
SELECT DISTINCT csc.ORGANIZATION_ID,DECODE(csc.SECONDARY_INVENTORY,'-',NULL),csc.supply_level
FROM CSP_SUPPLY_CHAIN csc, mtl_parameters mp, csp_planning_parameters cpp
WHERE mp.MASTER_ORGANIZATION_ID = c_master_org_id
and csc.INVENTORY_ITEM_ID = c_inv_item_id
and csc.organization_id = mp.organization_id
and cpp.organization_id = csc.organization_id
and nvl(cpp.SECONDARY_INVENTORY,'-') = csc.SECONDARY_INVENTORY
and cpp.node_type = 'ORGANIZATION_WH'
order by csc.supply_level desc;
SELECT mri.related_item_id
FROM MTL_RELATED_ITEMS mri
WHERE mri.ORGANIZATION_ID = c_master_org_id
AND mri.RELATIONSHIP_TYPE_ID = 8
AND mri.inventory_item_id = c_item_id;
/* SELECT NVL(MIN_MINMAX_QUANTITY,0)
from mtl_system_items_b
where organization_id = c_org_id
and inventory_item_id = c_item_id;*/
SELECT nvl(decode(cpr.newbuy_rop,0,null), NVL(MIN_MINMAX_QUANTITY,0))
from mtl_system_items_b msib, csp_plan_reorders cpr
where msib.organization_id = c_org_id
and msib.inventory_item_id = c_item_id
and cpr.organization_id = msib.organization_id
and cpr.inventory_item_id = msib.inventory_item_id ;
select 'Y'
from csp_supersede_items
where organization_id=c_org_id
and inventory_item_id=c_item_id
and sub_inventory_code ='-';
insert_item_planned(p_master_org_id => l_master_org_id
,p_org_id => l_top_org
,p_item_id => l_item_being_planned
,p_subinv_code => l_subinv_code
,p_supply_chain_id => l_supply_level
,x_return_status => l_return_status);
PROCEDURE insert_item_planned(p_master_org_id IN NUMBER
,p_org_id IN NUMBER
,p_item_id IN NUMBER
,p_subinv_code IN VARCHAR2
,p_supply_chain_id IN NUMBER
,x_return_status OUT NOCOPY Varchar2) IS
l_rop NUMBER ;
/*SELECT NVL(MIN_MINMAX_QUANTITY,0)
from mtl_system_items_b
where organization_id = p_org_id
and inventory_item_id = p_item_id;*/
SELECT nvl(decode(cpr.newbuy_rop,0,null), NVL(MIN_MINMAX_QUANTITY,0))
from mtl_system_items_b msib, csp_plan_reorders cpr
where msib.organization_id = p_org_id
and msib.inventory_item_id = p_item_id
and cpr.organization_id = msib.organization_id
and cpr.inventory_item_id = msib.inventory_item_id ;
SELECT SOURCE_ORGANIZATION_ID, SOURCE_SUBINVENTORY
FROM CSP_SOURCES
WHERE ORGANIZATION_ID = p_org_id
AND CONDITION_TYPE = 'B';*/
select msv.source_organization_id,NULL
from csp_planning_parameters cpp, mrp_sources_V msv
where cpp.organization_id = p_org_id
and cpp.NODE_TYPE = 'ORGANIZATION_WH'
and msv.assignment_set_id = cpp.DEFECTIVE_ASSIGNMENT_SET_ID
and msv.inventory_item_id = l_item_id
and msv.organization_id = p_org_id;
select decode(mri.inventory_item_id,p_item_id,'Y','N')
from mtl_related_items mri,mtl_parameters mp
where mp.organization_id = p_org_id
and mri.organization_id = mp.master_organization_id
and mri.inventory_item_id = p_item_id
and mri.RELATIONSHIP_TYPE_ID = 18;
/* select msa.SECONDARY_INVENTORY_NAME
from MTL_SUBINVENTORIES_ALL_V msa , CSP_SEC_INVENTORIES_V csi
where msa.organization_id = p_org_id
and csi.organization_id = msa.organization_id
and csi.SECONDARY_INVENTORY_NAME = msa.SECONDARY_INVENTORY_NAME
and csi.condition_type <> 'B';*/
select msa.SECONDARY_INVENTORY_NAME
from mtl_secondary_inventories msa , CSP_SEC_INVENTORIES csi
where msa.organization_id = p_org_id
and csi.organization_id = msa.organization_id
and csi.SECONDARY_INVENTORY_NAME = msa.SECONDARY_INVENTORY_NAME
and csi.condition_type <> 'B';
select csi.item_supplied,csc.source_organization_id,csc.supply_level,csc.source_subinventory
from csp_supersede_items csi,csp_supply_chain csc
where csc.organization_id= l_previou_source_org_id
and csc.secondary_inventory = nvl(l_source_subinv_code,'-')
and csc.inventory_item_id = l_item_id
and csc.supply_level = l_supply_level
and csi.inventory_item_id(+) = l_item_id
and csi.organization_id (+) = csc.source_organization_id;
l_sql_string := 'select mri.inventory_item_id
from MTL_RELATED_ITEMS mri,mtl_system_items_b msi
where mri.ORGANIZATION_ID = ' || p_org_id ||
'and mri.RELATIONSHIP_TYPE_ID = 18
and mri.inventory_item_id <> ' || l_item_id ||
'and mri.RELATED_ITEM_ID = ' || l_item_id ||
'and mri.inventory_item_id NOT IN ';
l_sql_string := 'select mri.inventory_item_id
from MTL_RELATED_ITEMS mri,mtl_system_items_b msi
where mri.ORGANIZATION_ID = ' || p_org_id ||
'and mri.RELATIONSHIP_TYPE_ID = 18
and mri.inventory_item_id <> ' || l_item_id ||
'and mri.RELATED_ITEM_ID = ' || l_item_id ||
' and msi.inventory_item_id = mri.inventory_item_id and
MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
' and msi.organization_id = mri.ORGANIZATION_ID' ;
l_repairable_tbl.delete;
CSP_SUPERSEDE_ITEMS_PKG.insert_row(px_supersede_id => l_supersede_id
,p_created_by => -1
,p_creation_date => sysdate
,p_last_updated_by => -1
,p_last_update_date=> sysdate
,p_last_update_login => -1
,p_inventory_item_id => p_item_id
,p_organization_id => p_org_id
,p_sub_inventory_code => '-'
,p_item_supplied => l_planned_item_id
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL);
END insert_item_planned;
SELECT mri.related_item_id
FROM mtl_related_items mri, mtl_parameters mp, mtl_system_items_b msi
WHERE mp.organization_id = c_organization_id
AND mri.organization_id = mp.master_organization_id
AND mri.inventory_item_id = c_inventory_item_id
AND mri.RELATIONSHIP_TYPE_ID = 8
AND msi.organization_id = mri.organization_id
AND msi.inventory_item_id = mri.inventory_item_id;
l_sql_string := 'select MRI.inventory_item_id
from MTL_RELATED_ITEMS mri , MTL_SYSTEM_ITEMS_B msi
where mri.ORGANIZATION_ID =' || l_master_org ||
'and mri.RELATIONSHIP_TYPE_ID = 8
and mri.related_item_id =' || p_inventory_item_id ||
'and mri.reciprocal_flag =' || '''' || 'Y' || '''' ||
'and mri.inventory_item_id <>' || p_inventory_item_id ||
'and MSI.ORGANIZATION_ID =' || l_master_org ||
'and MSI.inventory_item_id = mri.inventory_item_id
and MSI.MTL_TRANSACTIONS_ENABLED_FLAG =' || '''' || 'Y' || '''' ||
'and MRI.inventory_item_id NOT IN ' || l_not_in_string;
select mri.inventory_item_id
from MTL_RELATED_ITEMS mri
where mri.ORGANIZATION_ID = nvl(c_master_org_id,mri.ORGANIZATION_ID)
and mri.RELATIONSHIP_TYPE_ID = 8
and mri.related_item_id = c_item_id;
select cpp.ORGANIZATION_ID, cpp.SECONDARY_INVENTORY, mp.master_organization_id
from csp_planning_parameters cpp, mtl_parameters mp
where cpp.level_id = p_level_id
and mp.ORGANIZATION_ID = cpp.organization_id ;
select inventory_item_id,supply_level, SOURCE_ORGANIZATION_ID,supply_chain_id
from csp_supply_chain
where organization_id = l_organization_id
and secondary_inventory = l_subinv_code;
insert_item_supplied(p_planned_subinv_code => l_subinv_code
,p_planned_org_id => l_organization_id
,p_replaced_item_id => gsc.inventory_item_id
,p_supply_level => gsc.supply_level
,p_master_org_id => l_master_org_id
,x_return_status => l_return_status );
insert_item_planned(p_master_org_id => l_master_org_id
,p_org_id => l_organization_id
,p_item_id => gsc.inventory_item_id
,p_subinv_code => l_subinv_code
,p_supply_chain_id => gsc.supply_chain_id
,x_return_status => l_return_status);
select RELATED_ITEM_ID
from MTL_RELATED_ITEMS mriv, mtl_parameters mp
where mp.organization_id = p_org_id
and mriv.inventory_item_id = p_item_id
and mriv. organization_id = mp.master_organization_id
and mriv.RELATIONSHIP_TYPE_ID = 8;
select concatenated_segments
from mtl_system_items_b_kfv
where inventory_item_id = c_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id;