[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_item_attributes(p_pending_status IN VARCHAR2
,p_control_level IN NUMBER
,p_Org_Id IN NUMBER
,p_Item_id IN NUMBER
,p_commit IN VARCHAR2:= FND_API.g_TRUE
,p_return_status OUT NOCOPY BOOLEAN)
IS
CURSOR c_get_status_control (cp_attrib_name VARCHAR2)
IS
SELECT status_control_code
FROM mtl_item_attributes
WHERE attribute_name = cp_attrib_name;
SELECT 1 INTO l_row_temp
FROM mtl_system_items msi
WHERE inventory_item_id = p_Item_Id
AND ((p_control_level = G_ITEM_ORG and msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)))
AND rownum < 2
FOR UPDATE NOWAIT;
SELECT count(*) INTO is_transactable_upd
FROM DUAL
WHERE EXISTS
(SELECT 'X' FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND mtl_transactions_enabled_flag <>
(SELECT attribute_value FROM mtl_status_attribute_values
WHERE attribute_name = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG'
AND inventory_item_status_code = p_pending_status));
SELECT count(*) INTO is_bom_enabled_upd
FROM mtl_status_attribute_values
WHERE attribute_name = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG'
AND attribute_value = 'N'
AND inventory_item_status_code = p_pending_status;
select count(*)
into G_BOM_status
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_Org_Id
and 1 = l_bom_cntrl_level
union all
select organization_id
from mtl_parameters
where organization_id = p_Org_Id
and 2 =l_bom_cntrl_level));
dependent attributes and accordingly selects a value of 1 in the corresponding global variable
*/
SELECT Sum(Decode(mav.attribute_name,G_TRANSACTIONS_ENABLED,Decode(mav.attribute_value,'N',Decode(msi.check_shortages_flag,'Y',1,0),0),0)) uncheck_transactable_err,
Sum(Decode(mav.attribute_name,G_PURCHASING_ENABLED ,Decode(mav.attribute_value,'N',Decode(msi.default_so_source_type,'EXTERNAL',1,0),0),0)) uncheck_purchasable_err,
Sum(Decode(mav.attribute_name,G_CUSTOMER_ENABLED ,Decode(mav.attribute_value,'N',Decode(msi.orderable_on_web_flag,'Y',1,0),0),0)) uncheck_orderable_err
INTO G_TRANSACTABLE
,G_PURCHASABLE
,G_ORDERABLE
FROM mtl_system_items_b msi
,mtl_status_attribute_values mav
WHERE msi.inventory_item_id = p_Item_Id
AND msi.organization_id = p_Org_Id
AND mav.inventory_item_status_code = p_pending_status
AND mav.attribute_name IN (G_TRANSACTIONS_ENABLED,G_PURCHASING_ENABLED,G_CUSTOMER_ENABLED);
/* Lock is success - Update Item status */
UPDATE mtl_system_items msi
SET (inventory_item_status_code,
last_update_date,
last_updated_by,
last_update_login) =
(SELECT p_pending_status,
sysdate,
G_USER_ID,
G_USER_ID
FROM mtl_status_attribute_values v,
mtl_item_attributes a
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_STOCK_ENABLED
AND a.attribute_name = v.attribute_name)
WHERE msi.inventory_item_id = p_Item_Id
AND ((p_control_level = G_ITEM_ORG and msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
/* Update all the Eight Item Attributes */
UPDATE mtl_system_items msi
SET msi.stock_enabled_flag =
( SELECT DECODE(mti.inventory_item_flag,'N','N',v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_STOCK_ENABLED
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_stock_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)
AND msi.inventory_item_id = p_Item_Id
AND msi.inventory_item_flag = 'Y'
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.mtl_transactions_enabled_flag =
( SELECT DECODE (mti.stock_enabled_flag,'N','N', v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_TRANSACTIONS_ENABLED
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_trans_cntrl_level IN ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL)
AND msi.inventory_item_id = p_Item_Id
-- AND msi.stock_enabled_flag = 'Y' /* commented for bug 3375455 */
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.purchasing_enabled_flag =
(SELECT DECODE(mti.purchasing_item_flag,'N','N',v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_PURCHASING_ENABLED
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_purch_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
AND msi.purchasing_item_flag = 'Y'
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.invoice_enabled_flag =
( SELECT DECODE(mti.invoiceable_item_flag,'N','N',v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_INVOICE_ENABLED
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_invoice_cntrl_level in ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
AND msi.invoiceable_item_flag = 'Y'
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.build_in_wip_flag =
( SELECT DECODE(mti.inventory_item_flag,'N','N',decode(mti.bom_item_type,4,v.attribute_value,'N'))
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_BUILD_IN_WIP
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_wip_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
AND msi.inventory_item_flag = 'Y'
AND msi.bom_item_type = 4
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.customer_order_enabled_flag =
( SELECT DECODE(mti.customer_order_flag,'N','N',v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_CUSTOMER_ENABLED
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_cust_cntrl_level IN (G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL)
AND msi.inventory_item_id = p_Item_Id
AND msi.customer_order_flag = 'Y'
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.internal_order_enabled_flag =
( SELECT DECODE(mti.internal_order_flag,'N','N',v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a,
mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_INTERNAL_ENABLED
AND a.attribute_name = v.attribute_name
AND mti.inventory_item_id = p_Item_Id
AND mti.organization_id = p_Org_Id)
WHERE l_int_cntrl_level IN ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
AND msi.internal_order_flag = 'Y'
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.bom_enabled_flag =
( SELECT v.attribute_value --Bug:3546140DECODE(mti.inventory_item_flag,'N','N',v.attribute_value)
FROM mtl_status_attribute_values v,
mtl_item_attributes a
--Bug:3546140 ,mtl_system_items mti
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_BOM_ENABLED
AND a.attribute_name = v.attribute_name
-- AND mti.inventory_item_id = p_Item_Id
-- AND mti.organization_id = p_Org_Id
)
WHERE l_bom_cntrl_level IN ( G_UNDER_STATUS_CONTROL, G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
--Bug:3546140 AND msi.inventory_item_flag = 'Y'
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.recipe_enabled_flag =
(SELECT v.attribute_value
FROM mtl_status_attribute_values v,
mtl_item_attributes a
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_RECIPE_ENABLED
AND a.attribute_name = v.attribute_name)
WHERE l_recipe_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_system_items msi
SET msi.PROCESS_EXECUTION_ENABLED_FLAG =
(SELECT decode(msi.inventory_item_flag,'N','N',
decode(msi.recipe_enabled_flag,'N','N',v.attribute_value))
FROM mtl_status_attribute_values v,
mtl_item_attributes a
WHERE v.inventory_item_status_code = p_pending_status
AND a.attribute_name = G_PROCESS_EXECUTION_ENABLED
AND a.attribute_name = v.attribute_name)
WHERE l_process_exec_cntrl_level in ( G_UNDER_STATUS_CONTROL,G_DEFAULT_CONTROL )
AND msi.inventory_item_id = p_Item_Id
AND ((p_control_level = G_ITEM_ORG AND msi.organization_id = p_Org_Id)
OR
(p_control_level = G_ITEM
AND msi.organization_id IN
(SELECT p2.organization_id
FROM mtl_parameters p1,
mtl_parameters p2
WHERE p1.organization_id = p_Org_Id
AND p1.master_organization_id = p2.master_organization_id)));
UPDATE mtl_pending_item_status
SET pending_flag = 'N' ,
implemented_date = SYSDATE ,
request_id = G_REQUEST_ID,
program_application_id = G_PROG_APPID,
program_id = G_PROG_ID,
program_update_date = SYSDATE,
last_update_login = G_LOGIN_ID,
last_updated_by = G_USER_ID
WHERE status_code = p_pending_status
AND organization_id = p_Org_Id
AND inventory_item_id = p_Item_Id
AND effective_date <= SYSDATE
AND pending_flag = 'Y';
END update_item_attributes;
SELECT pis.status_code,
pis.inventory_item_id,
pis.organization_id
FROM mtl_pending_item_status pis,
mtl_item_status pit
WHERE pis.effective_date <= sysdate
AND pis.pending_flag = 'Y'
AND pis.inventory_item_id = nvl(cp_item_id,pis.inventory_item_id)
AND pis.organization_id = nvl(cp_org_id,pis.organization_id)
AND pis.status_code = pit.inventory_item_status_code
AND nvl(pit.disable_date,sysdate+1) > sysdate
--2800987 : When called from PLM, p_msg_logname is PLM_LOG, through CP it is FILE.
AND ((p_msg_logname ='FILE' AND lifecycle_id IS NULL) OR (p_msg_logname ='PLM_LOG'))
--2772279 -last_update_date,rowid in order clause
ORDER BY pis.effective_date,pis.last_update_date,pis.rowid;
SELECT control_level
FROM mtl_item_attributes
WHERE attribute_name = G_STATUS_CODE;
SELECT status_control_code
FROM mtl_item_attributes
WHERE attribute_name = cp_attrib_name;
update_item_attributes(p_pending_status => l_status_code
,p_control_level => l_control_level
,p_Org_Id => l_org_id
,p_item_id => l_Item_Id
,p_commit => p_commit
,p_return_status => l_done);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);