The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 1;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 2;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 3;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 4;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 5;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 6;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 7;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 8;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 9;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 10;
select 'X' into temp
from MTL_DEFAULT_CATEGORY_SETS MDCS
where MDCS.category_set_id = current_cat_set_id
and MDCS.functional_area_id = 11;
select INVENTORY_ITEM_FLAG, PURCHASING_ITEM_FLAG,
INTERNAL_ORDER_FLAG, decode(SERVICE_ITEM_FLAG,'Y',SERVICE_ITEM_FLAG,
SERVICEABLE_PRODUCT_FLAG),
COSTING_ENABLED_FLAG, ENG_ITEM_FLAG, CUSTOMER_ORDER_FLAG,
MRP_PLANNING_CODE,EAM_ITEM_TYPE,CONTRACT_ITEM_TYPE_CODE --Bug: 2527058
into inv_item_flag, purch_item_flag,
int_order_flag, serv_item_flag,
cost_enab_flag, engg_item_flag, cust_order_flag,
mrp_plan_code, eam_item_type, contract_item_type --Bug: 2527058
from MTL_SYSTEM_ITEMS MSI
where MSI.inventory_item_id = current_item_id
and MSI.organization_id = current_org_id;
PROCEDURE INSERT_CATSET_CHILD_ORGS(
current_inv_item_id IN NUMBER,
current_org_id IN NUMBER,
current_master_org_id IN NUMBER,
current_cat_set_id IN NUMBER,
current_cat_id IN NUMBER,
cat_set_control_level IN NUMBER,
current_created_by IN NUMBER := NULL -- Added Bug-6045867
)
IS
l_organizations_rec ORG_LISTS;
select
p.organization_id
BULK COLLECT INTO
l_organizations_rec
from mtl_parameters p
where p.master_organization_id = current_master_org_id
and p.organization_id <> current_master_org_id
and exists
(select 'x'
from mtl_system_items i
where i.inventory_item_id = current_inv_item_id
and i.organization_id = p.organization_id)
/* Bug: 4932378 and exists
(select 'x'
from org_organization_definitions ood
where ood.organization_id = p.organization_id
and ood.inventory_enabled_flag = 'Y')*/;
insert into mtl_item_categories
(inventory_item_id,
category_set_id,
category_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
organization_id)
VALUES( current_inv_item_id,
current_cat_set_id,
current_cat_id,
sysdate,
-- current_created_by, -- bug 6045867 -- Commented for bug-6782351
NVL(current_created_by, FND_GLOBAL.USER_ID), -- NVL added for bug-6782351
sysdate,
-- current_created_by, -- bug 6045867 -- Commented for bug-6782351
NVL(current_created_by, FND_GLOBAL.USER_ID), -- NVL added for bug-6782351
-1,
-1,
-1,
sysdate,
-1,
l_organizations_rec(i));
END INSERT_CATSET_CHILD_ORGS;
PROCEDURE UPDATE_CATSET_CHILD_ORGS(
current_inv_item_id IN NUMBER,
current_org_id IN NUMBER,
current_master_org_id IN NUMBER,
current_cat_set_id IN NUMBER,
current_cat_id IN NUMBER,
cat_set_control_level IN NUMBER,
old_cat_id IN NUMBER,
current_last_updated_by IN NUMBER := NULL -- Added Bug-4949084
)
IS
l_organizations_rec ORG_LISTS;
update mtl_item_categories c
/*+ INDEX MTL_ITEM_CATEGORIES MTL_ITEM_CATEGORIES_U1 */
set c.category_id = current_cat_id,
c.last_update_date = sysdate,
c.last_updated_by = NVL(current_last_updated_by, FND_GLOBAL.USER_ID) -- Added Bug-4949084 @ 4886176
where c.inventory_item_id = current_inv_item_id
and c.category_set_id = current_cat_set_id
and c.category_id = old_cat_id
and c.organization_id in
(select p.organization_id from mtl_parameters p
where p.master_organization_id =
current_master_org_id
and exists (select 'x' from mtl_system_items i
where i.inventory_item_id =
current_inv_item_id
and i.organization_id = p.organization_id)
/* Bug: 4932378
and exists (select 'x' from org_organization_definitions ood
where ood.organization_id = p.organization_id
and ood.inventory_enabled_flag = 'Y')*/)
RETURNING organization_id
BULK COLLECT INTO l_organizations_rec;
,p_dml_type => 'UPDATE'
,p_inventory_item_id => current_inv_item_id
,p_organization_id => l_organizations_rec(i)
,p_category_set_id => current_cat_set_id
,p_category_id => current_cat_id);
END UPDATE_CATSET_CHILD_ORGS;
current_last_updated_by IN NUMBER
)
IS
CURSOR other_orgs_cur IS
select organization_id
from mtl_system_items
where
inventory_item_id = current_inv_item_id and
organization_id <> current_org_id and
organization_id in
( select organization_id
from mtl_parameters
where
master_organization_id = current_master_org_id
)
;
select category_set_id
into tmp_default_cat_set_id
from mtl_default_category_sets
where functional_area_id = 5;
current_last_updated_by,
tmp_cost_method,
current_cst_item_type,
tmp_cst_lot_size,
tmp_cst_shrink_rate,
tmp_cst_return,
tmp_cst_error
);
current_last_updated_by,
tmp_cost_method,
current_cst_item_type,
tmp_cst_lot_size,
tmp_cst_shrink_rate,
tmp_cst_return,
tmp_cst_error
);
select primary_cost_method
into tmp_cost_method
from mtl_parameters
where organization_id = tmp_organization_id;
select lot_size, shrinkage_rate
into tmp_cst_lot_size, tmp_cst_shrink_rate
from cst_item_costs
where
inventory_item_id = tmp_inv_item_id
and organization_id = tmp_organization_id
and cost_type_id = 1;
PROCEDURE DELETE_CATSET_CHILD_ORGS(
current_inv_item_id IN NUMBER,
current_master_org_id IN NUMBER,
current_cat_set_id IN NUMBER,
current_cat_id IN NUMBER
)
IS
l_organizations_rec ORG_LISTS;
So, during Deleting need to use all these 4 column values to delete
a single assignment.
*/
delete from mtl_item_categories c
where c.inventory_item_id = current_inv_item_id
and c.category_set_id = current_cat_set_id
and c.category_id = current_cat_id
and c.organization_id in
(select p.organization_id
from mtl_parameters p
where p.master_organization_id = current_master_org_id)
RETURNING organization_id
BULK COLLECT INTO
l_organizations_rec;
,p_dml_type => 'DELETE'
,p_inventory_item_id => current_inv_item_id
,p_organization_id => l_organizations_rec(i)
,p_category_set_id => current_cat_set_id
,p_category_id => current_cat_id);
END DELETE_CATSET_CHILD_ORGS;