[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM cst_item_costs CIC
WHERE CIC.organization_id = I_ORGANIZATION_ID
AND CIC.cost_type_id = I_COST_TYPE_ID
AND CIC.inventory_item_id in
(SELECT C2.inventory_item_id
FROM cst_lists CL
, cst_item_costs C2
WHERE list_id = I_LIST_ID
AND C2.cost_type_id = I_FROM_COST_TYPE
AND C2.organization_id = I_ORGANIZATION_ID
AND C2.inventory_item_id = CL.entity_id
);
INSERT INTO cst_item_costs
(
inventory_item_id, organization_id, cost_type_id,
request_id, program_application_id,
program_id, program_update_date,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, inventory_asset_flag,
lot_size, based_on_rollup_flag,
shrinkage_rate, defaulted_flag,
pl_material, pl_material_overhead,
pl_resource, pl_outside_processing,
pl_overhead,
tl_material, tl_material_overhead,
tl_resource, tl_outside_processing,
tl_overhead,
material_cost, material_overhead_cost,
resource_cost, outside_processing_cost,
overhead_cost,
pl_item_cost, tl_item_cost,
unburdened_cost, burden_cost,
item_cost, attribute_category,
attribute1, attribute2, attribute3,
attribute4, attribute5, attribute6,
attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15
)
SELECT CIC.inventory_item_id
, CIC.organization_id
, I_COST_TYPE_ID
, I_REQ_ID
, I_PRGM_APPL_ID
, I_PRGM_ID
, SYSDATE
, SYSDATE
, I_USER_ID
, SYSDATE
, I_USER_ID
, -1
, CIC.inventory_asset_flag
, CIC.lot_size
, CIC.based_on_rollup_flag
, CIC.shrinkage_rate
, CIC.defaulted_flag
, CIC.pl_material
, CIC.pl_material_overhead
, CIC.pl_resource
, CIC.pl_outside_processing
, CIC.pl_overhead
, CIC.tl_material
, CIC.tl_material_overhead
, CIC.tl_resource
, CIC.tl_outside_processing
, CIC.tl_overhead
, CIC.material_cost
, CIC.material_overhead_cost
, CIC.resource_cost
, CIC.outside_processing_cost
, CIC.overhead_cost
, CIC.pl_item_cost
, CIC.tl_item_cost
, CIC.unburdened_cost
, CIC.burden_cost
, CIC.item_cost
, CIC.attribute_category
, CIC.attribute1
, CIC.attribute2
, CIC.attribute3
, CIC.attribute4
, CIC.attribute5
, CIC.attribute6
, CIC.attribute7
, CIC.attribute8
, CIC.attribute9
, CIC.attribute10
, CIC.attribute11
, CIC.attribute12
, CIC.attribute13
, CIC.attribute14
, CIC.attribute15
FROM cst_lists CL
, cst_item_costs CIC
WHERE CL.list_id = I_LIST_ID
AND CIC.inventory_item_id = CL.entity_id
AND CIC.cost_type_id = I_FROM_COST_TYPE
AND CIC.organization_id = I_ORGANIZATION_ID;
DELETE FROM cst_item_cost_details CICD
WHERE CICD.organization_id = I_ORGANIZATION_ID
AND CICD.cost_type_id = I_COST_TYPE_ID
AND CICD.inventory_item_id in
(SELECT C2.inventory_item_id
FROM cst_lists CL
, cst_item_costs C2
WHERE list_id = I_LIST_ID
AND C2.cost_type_id = I_FROM_COST_TYPE
AND C2.organization_id = I_ORGANIZATION_ID
AND C2.inventory_item_id = CL.entity_id
);
INSERT INTO cst_item_cost_details
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, operation_sequence_id
, operation_seq_num
, department_id
, level_type
, activity_id
, resource_seq_num
, resource_id
, resource_rate
, item_units
, activity_units
, usage_rate_or_amount
, basis_type
, basis_resource_id
, basis_factor
, net_yield_or_shrinkage_factor
, item_cost
, cost_element_id
, rollup_source_type
, activity_context
, request_id
, program_application_id
, program_id
, program_update_date
, yielded_cost
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15)
SELECT
CICD.inventory_item_id
, CICD.organization_id
, I_COST_TYPE_ID
, SYSDATE
, I_USER_ID
, SYSDATE
, I_USER_ID
, I_USER_ID
, CICD.operation_sequence_id
, CICD.operation_seq_num
, CICD.department_id
, CICD.level_type
, CICD.activity_id
, CICD.resource_seq_num
, CICD.resource_id
, CICD.resource_rate
, CICD.item_units
, CICD.activity_units
, CICD.usage_rate_or_amount
, CICD.basis_type
, CICD.basis_resource_id
, CICD.basis_factor
, CICD.net_yield_or_shrinkage_factor
, CICD.item_cost
, CICD.cost_element_id
, CICD.rollup_source_type
, CICD.activity_context
, I_REQ_ID
, I_PRGM_APPL_ID
, I_PRGM_ID
, SYSDATE
, CICD.yielded_cost
, CICD.attribute_category
, CICD.attribute1
, CICD.attribute2
, CICD.attribute3
, CICD.attribute4
, CICD.attribute5
, CICD.attribute6
, CICD.attribute7
, CICD.attribute8
, CICD.attribute9
, CICD.attribute10
, CICD.attribute11
, CICD.attribute12
, CICD.attribute13
, CICD.attribute14
, CICD.attribute15
FROM cst_lists CL
, cst_item_cost_details CICD
WHERE CL.list_id = I_LIST_ID
AND CICD.cost_type_id = I_FROM_COST_TYPE
AND CICD.organization_id = I_ORGANIZATION_ID
AND CICD.inventory_item_id = CL.entity_id;
(select entity_id
from cst_lists l
where L.list_id = I_LIST_ID
and NOT EXISTS (select inventory_item_id
from cst_item_costs cic2
where cic2.inventory_item_id = l.entity_id
and cic2.organization_id = I_ORGANIZATION_ID
AND cic2.cost_type_id = I_COST_TYPE_ID)
);
INSERT INTO cst_item_costs
( inventory_item_id
, organization_id
, cost_type_id
, request_id
, program_application_id
, program_id
, program_update_date
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, inventory_asset_flag
, lot_size
, based_on_rollup_flag
, shrinkage_rate
, defaulted_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
SELECT
CIC.inventory_item_id
, CIC.organization_id
, I_COST_TYPE_ID
, I_REQ_ID
, I_PRGM_APPL_ID
, I_PRGM_ID
, SYSDATE
, SYSDATE
, I_USER_ID
, SYSDATE
, I_USER_ID
, -1
, 1
, CIC.lot_size
, CIC.based_on_rollup_flag
, CIC.shrinkage_rate
, CIC.defaulted_flag
, CIC.attribute_category
, CIC.attribute1
, CIC.attribute2
, CIC.attribute3
, CIC.attribute4
, CIC.attribute5
, CIC.attribute6
, CIC.attribute7
, CIC.attribute8
, CIC.attribute9
, CIC.attribute10
, CIC.attribute11
, CIC.attribute12
, CIC.attribute13
, CIC.attribute14
, CIC.attribute15
FROM cst_item_costs CIC
WHERE CIC.organization_id = I_ORGANIZATION_ID
AND CIC.cost_type_id in (1,2)
AND CIC.inventory_item_id = l_temp_table(i)
AND CIC.inventory_asset_flag = 1 ;
Fix for Bug#2122019 - Added activity_id in the insert to populate default
activity assigned to the sub element being edited. Selected default_activity_id
from bom_resources for the edited sub element
*/
l_location := 2;
INSERT INTO cst_item_cost_details
( inventory_item_id
, organization_id
, cost_type_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, level_type
, activity_id
, resource_id
, resource_rate
, usage_rate_or_amount
, basis_type
, basis_factor
, net_yield_or_shrinkage_factor
, item_cost
, cost_element_id
, rollup_source_type
, request_id
, program_application_id
, program_id
, program_update_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
SELECT
CIC.inventory_item_id
, CIC.organization_id
, CIC.cost_type_id
, SYSDATE
, I_USER_ID
, SYSDATE
, I_USER_ID
, 1
, BR.default_activity_id
, I_RESOURCE_ID
, 1
, 0
, 1 /* Item */
, 1
, 1
, 0
, 1 /* Material */
, 1 /* User defined */
, I_REQ_ID
, I_PRGM_APPL_ID
, I_PRGM_ID
, SYSDATE
, CIC.attribute_category
, CIC.attribute1
, CIC.attribute2
, CIC.attribute3
, CIC.attribute4
, CIC.attribute5
, CIC.attribute6
, CIC.attribute7
, CIC.attribute8
, CIC.attribute9
, CIC.attribute10
, CIC.attribute11
, CIC.attribute12
, CIC.attribute13
, CIC.attribute14
, CIC.attribute15
FROM cst_lists L
, cst_item_costs CIC
, bom_resources BR
WHERE CIC.organization_id = I_ORGANIZATION_ID
AND L.list_id = I_LIST_ID
AND BR.resource_id = I_RESOURCE_ID
AND CIC.cost_type_id = I_COST_TYPE_ID
AND CIC.inventory_item_id = L.entity_id
AND CIC.inventory_asset_flag = 1
AND L.entity_id not in (
SELECT inventory_item_id
FROM cst_item_cost_details
WHERE organization_id = I_ORGANIZATION_ID
AND cost_type_id = I_COST_TYPE_ID
-- Commented out lines to fix bug # 1962252 , mass edit adds new
-- sub-elements on unit cost of items.The changes were introduced due to fix made
-- for bug # 1175172
-- AND resource_id = I_RESOURCE_ID
-- AND level_type = 1
-- AND cost_element_id = 1
);
SELECT entity_id
FROM cst_lists cl
WHERE CL.list_id= I_LIST_ID
AND EXISTS (SELECT /*+ no_unnest index( cicd CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
NULL FROM
CST_ITEM_COST_DETAILS CICD
WHERE CICD.organization_id = I_ORGANIZATION_ID
AND CICD.cost_type_id = I_COST_TYPE_ID
AND CICD.level_type = 1
AND CICD.cost_element_id = 2
AND CICD.basis_type = 5
AND CL.entity_id = CICD.inventory_item_id);
select entity_id from cst_lists cl
where list_id = I_LIST_ID
AND EXISTS (SELECT /*+ no_unnest index( cicd CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
NULL FROM
CST_ITEM_COST_DETAILS CICD
WHERE CICD.organization_id = I_ORGANIZATION_ID
AND CICD.cost_type_id = I_COST_TYPE_ID
AND CL.entity_id = CICD.inventory_item_id);
SELECT SUM(NVL(CICD.item_cost,0))
into l_basis_factor
FROM cst_item_cost_details CICD
WHERE CICD.inventory_item_id = ITEMS.entity_id
AND CICD.organization_id = I_ORGANIZATION_ID
AND CICD.cost_type_id = I_COST_TYPE_ID
AND NOT (CICD.cost_element_id = 2 AND CICD.level_type = 1);
UPDATE cst_item_cost_details CICD
SET last_update_date = SYSDATE,
last_updated_by = I_USER_ID,
basis_factor = l_basis_factor,
item_cost = ROUND((CICD.usage_rate_or_amount*l_basis_factor), l_ext_prec),
request_id = I_REQ_ID,
program_application_id = I_PRGM_APPL_ID,
program_id = I_PRGM_ID,
program_update_date = SYSDATE
WHERE organization_id = I_ORGANIZATION_ID
AND cost_type_id = I_COST_TYPE_ID
AND level_type = 1 /* This Level */
AND cost_element_id = 2 /* Material Overhead */
AND basis_type = 5 /* Total Value */
AND CICD.inventory_item_id = ITEMS.entity_id;
UPDATE cst_item_costs CIC
SET (
last_update_date,
last_updated_by,
pl_material,
pl_material_overhead,
pl_resource,
pl_outside_processing,
pl_overhead,
tl_material,
tl_material_overhead,
tl_resource,
tl_outside_processing,
tl_overhead,
material_cost,
material_overhead_cost,
resource_cost,
outside_processing_cost,
overhead_cost,
pl_item_cost,
tl_item_cost,
item_cost,
unburdened_cost,
burden_cost,
request_id,
program_application_id,
program_id,
program_update_date) =
(
SELECT /*+ index(CICD CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
SYSDATE
, I_USER_ID
, SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0))
, SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0))
, SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0))
, SUM(DECODE(cost_element_id,1,item_cost))
, SUM(DECODE(cost_element_id,2,item_cost))
, SUM(DECODE(cost_element_id,3,item_cost))
, SUM(DECODE(cost_element_id,4,item_cost))
, SUM(DECODE(cost_element_id,5,item_cost))
, SUM(DECODE(level_type,2,item_cost,0))
, SUM(DECODE(level_type,1,item_cost,0))
, SUM(item_cost)
, SUM(DECODE(cost_element_id,
2, DECODE(level_type,2,item_cost,0),
item_cost))
, SUM(DECODE(cost_element_id,
2, DECODE(level_type,1,item_cost,0),
0))
, I_REQ_ID, I_PRGM_APPL_ID, I_PRGM_ID, SYSDATE
FROM cst_item_cost_details CICD
WHERE organization_id = I_ORGANIZATION_ID
AND cost_type_id = I_COST_TYPE_ID
AND inventory_item_id = c_item_id_tbl(i)
)
WHERE CIC.organization_id = I_ORGANIZATION_ID
AND CIC.cost_type_id = I_COST_TYPE_ID
AND CIC.inventory_item_id = c_item_id_tbl(i);
l_num_CIC_rows NUMBER := 0; -- number of rows updated in CIC
l_num_CICD_rows NUMBER := 0; -- number of rows updated in CICD
fnd_file.put_line(fnd_file.log,'Lot Size Selection: '||to_char(i_lotsz_lov));
UPDATE cst_item_costs cic
SET based_on_rollup_flag = decode(i_bor_flag, 1,1, 2,2, 4,cic.BASED_ON_ROLLUP_FLAG, NULL),
defaulted_flag = decode(i_def_flag, 1,1, 2,2, 4,cic.DEFAULTED_FLAG, NULL),
lot_size = decode(i_lotsz_lov, 1,nvl(i_lot_size,cic.LOT_SIZE), 3,cic.LOT_SIZE, NULL),
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_pgm_app_id,
program_id = l_pgm_id,
program_update_date = sysdate
WHERE cic.cost_type_id = i_cost_type
AND cic.organization_id = i_org_id
AND (i_range = 1
OR (i_range = 2
AND cic.inventory_item_id = i_specific_item)
OR (i_range = 3
AND cic.inventory_item_id IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items_kfv msi1
WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
OR (i_range = 5
AND cic.inventory_item_id IN
(SELECT msi2.inventory_item_id
FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.inventory_item_id = msi2.inventory_item_id
AND mic.organization_id = msi2.organization_id
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)));
UPDATE cst_item_costs cic
SET (based_on_rollup_flag,
defaulted_flag,
lot_size,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date) =
(SELECT decode(i_bor_flag, 1,1, 2,2, 3,nvl(msi.PLANNING_MAKE_BUY_CODE,cic.BASED_ON_ROLLUP_FLAG),
4,cic.BASED_ON_ROLLUP_FLAG, NULL),
decode(i_def_flag, 1,1, 2,2, 4,cic.DEFAULTED_FLAG, NULL),
decode(i_lotsz_lov, 1,nvl(i_lot_size,cic.LOT_SIZE), 2,nvl(msi.STD_LOT_SIZE,cic.LOT_SIZE),
3,cic.LOT_SIZE, NULL),
sysdate,
l_user_id,
l_login_id,
l_request_id,
l_pgm_app_id,
l_pgm_id,
sysdate
FROM mtl_system_items msi
WHERE msi.organization_id = cic.organization_id
AND msi.inventory_item_id = cic.inventory_item_id)
WHERE cic.cost_type_id = i_cost_type
AND cic.organization_id = i_org_id
AND (i_range = 1
OR (i_range = 2
AND cic.inventory_item_id = i_specific_item)
OR (i_range = 3
AND cic.inventory_item_id IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items_kfv msi1
WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
OR (i_range = 5
AND cic.inventory_item_id IN
(SELECT msi2.inventory_item_id
FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.inventory_item_id = msi2.inventory_item_id
AND mic.organization_id = msi2.organization_id
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)));
UPDATE cst_item_costs cic
SET (based_on_rollup_flag,
defaulted_flag,
lot_size,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date) =
(SELECT decode(i_bor_flag, 1,1, 2,2, 3,nvl(cic1.BASED_ON_ROLLUP_FLAG,cic2.BASED_ON_ROLLUP_FLAG),
4,cic2.BASED_ON_ROLLUP_FLAG, NULL),
decode(i_def_flag, 1,1, 2,2, 3,nvl(cic1.DEFAULTED_FLAG,cic2.DEFAULTED_FLAG),
4,cic2.DEFAULTED_FLAG, NULL),
decode(i_lotsz_lov, 1,nvl(i_lot_size,cic2.LOT_SIZE), 2,nvl(cic1.LOT_SIZE,cic2.LOT_SIZE),
3,cic2.LOT_SIZE, NULL),
sysdate,
l_user_id,
l_login_id,
l_request_id,
l_pgm_app_id,
l_pgm_id,
sysdate
FROM cst_item_costs cic1, cst_item_costs cic2
WHERE cic2.organization_id = cic.organization_id
AND cic2.inventory_item_id = cic.inventory_item_id
AND cic2.cost_type_id = cic.cost_type_id
AND cic1.organization_id (+) = cic2.organization_id
AND cic1.inventory_item_id (+) = cic2.inventory_item_id
AND cic1.cost_type_id (+) = i_src_cost_type)
WHERE cic.cost_type_id = i_cost_type
AND cic.organization_id = i_org_id
AND (i_range = 1
OR (i_range = 2
AND cic.inventory_item_id = i_specific_item)
OR (i_range = 3
AND cic.inventory_item_id IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items_kfv msi1
WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
OR (i_range = 5
AND cic.inventory_item_id IN
(SELECT msi2.inventory_item_id
FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.inventory_item_id = msi2.inventory_item_id
AND mic.organization_id = msi2.organization_id
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)));
fnd_file.put_line(fnd_file.log,'Updated '||to_char(l_num_CIC_rows)||' rows in cst_item_costs.');
UPDATE cst_item_cost_details cicd
SET (cicd.basis_factor,
cicd.usage_rate_or_amount,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date) =
(SELECT nvl( (1/cic.lot_size), cicd.basis_factor),
nvl( (cicd.usage_rate_or_amount * cicd.basis_factor * cic.lot_size), cicd.usage_rate_or_amount),
sysdate,
l_user_id,
l_login_id,
l_request_id,
l_pgm_app_id,
l_pgm_id,
sysdate
FROM cst_item_costs cic
WHERE cic.organization_id = cicd.organization_id
AND cic.cost_type_id = cicd.cost_type_id
AND cic.inventory_item_id = cicd.inventory_item_id)
WHERE cicd.cost_type_id = i_cost_type
AND cicd.organization_id = i_org_id
AND (i_range = 1
OR (i_range = 2
AND cicd.inventory_item_id = i_specific_item)
OR (i_range = 3
AND cicd.inventory_item_id IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items_kfv msi1
WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
OR (i_range = 5
AND cicd.inventory_item_id IN
(SELECT msi2.inventory_item_id
FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.inventory_item_id = msi2.inventory_item_id
AND mic.organization_id = msi2.organization_id
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)))
AND cicd.basis_type = 2
AND cicd.level_type = 1;
fnd_file.put_line(fnd_file.log,'Updated '||to_char(l_num_CICD_rows)||' rows in cst_item_cost_details for lotsize.');
UPDATE cst_item_costs cic
SET cic.shrinkage_rate = 0
WHERE cic.cost_type_id = i_cost_type
AND cic.organization_id = i_org_id
AND (i_range = 1
OR (i_range = 2
AND cic.inventory_item_id = i_specific_item)
OR (i_range = 3
AND cic.inventory_item_id IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items_kfv msi1
WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
OR (i_range = 5
AND cic.inventory_item_id IN
(SELECT msi2.inventory_item_id
FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.inventory_item_id = msi2.inventory_item_id
AND mic.organization_id = msi2.organization_id
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)))
AND cic.shrinkage_rate <> 0
AND cic.based_on_rollup_flag = 2
RETURNING cic.inventory_item_id BULK COLLECT INTO l_items;
select organization_code
into l_orgcode
from mtl_parameters
where organization_id = i_org_id;
select cost_type
into l_costtype
from cst_cost_types
where cost_type_id = i_cost_type;
select concatenated_segments
into l_itemname
from mtl_system_items_kfv
where organization_id = i_org_id
and inventory_item_id = l_items(i);
UPDATE cst_item_cost_details
SET usage_rate_or_amount = (usage_rate_or_amount * net_yield_or_shrinkage_factor),
net_yield_or_shrinkage_factor = 1,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id,
request_id = l_request_id,
program_application_id = l_pgm_app_id,
program_id = l_pgm_id,
program_update_date = sysdate
WHERE cost_type_id = i_cost_type
AND organization_id = i_org_id
AND inventory_item_id = l_items(i);
fnd_file.put_line(fnd_file.log,'Updated '||to_char(SQL%ROWCOUNT)||' rows in cst_item_cost_details related to the shrinkage rate.');
l_items.DELETE; -- reset the collection
UPDATE cst_item_costs cic
SET cic.defaulted_flag = 2
WHERE cic.cost_type_id = i_cost_type
AND cic.organization_id = i_org_id
AND (i_range = 1
OR (i_range = 2
AND cic.inventory_item_id = i_specific_item)
OR (i_range = 3
AND cic.inventory_item_id IN
(SELECT msi1.inventory_item_id
FROM mtl_system_items_kfv msi1
WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
OR (i_range = 5
AND cic.inventory_item_id IN
(SELECT msi2.inventory_item_id
FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.inventory_item_id = msi2.inventory_item_id
AND mic.organization_id = msi2.organization_id
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)))
AND cic.defaulted_flag = 1
AND (EXISTS (SELECT 'X'
FROM cst_item_cost_details cicd
WHERE cicd.organization_id = cic.organization_id
AND cicd.cost_type_id = cic.cost_type_id
AND cicd.inventory_item_id = cic.inventory_item_id
AND cicd.rollup_source_type = 1) -- user defined
OR NOT EXISTS (SELECT 'X'
FROM cst_item_costs cic1, cst_cost_types cct
WHERE cic1.organization_id = cic.organization_id
AND cic1.cost_type_id = cct.default_cost_type_id
AND cct.cost_type_id = cic.cost_type_id
AND cic1.inventory_item_id = cic.inventory_item_id))
RETURNING cic.inventory_item_id BULK COLLECT INTO l_items;
select organization_code
into l_orgcode
from mtl_parameters
where organization_id = i_org_id;
select cost_type
into l_costtype
from cst_cost_types
where cost_type_id = i_cost_type;
select concatenated_segments
into l_itemname
from mtl_system_items_kfv
where organization_id = i_org_id
and inventory_item_id = l_items(i);
l_err_msg := 'Cannot update defaulted_flag to YES';
UPDATE cst_item_cost_details cicd
SET cicd.rollup_source_type = 1
WHERE cicd.rollup_source_type = 2
AND cicd.cost_type_id = i_cost_type
AND cicd.organization_id = i_org_id
AND EXISTS (SELECT 'X'
FROM cst_item_costs cic
WHERE cic.organization_id = cicd.organization_id
AND cic.cost_type_id = cicd.cost_type_id
AND cic.inventory_item_id = cicd.inventory_item_id
AND cic.defaulted_flag = 2
);
UPDATE cst_item_cost_details cicd
SET cicd.rollup_source_type = 1
WHERE cicd.rollup_source_type = 2
AND cicd.cost_type_id = i_cost_type
AND cicd.organization_id = i_org_id
AND cicd.inventory_item_id = i_specific_item
AND EXISTS (SELECT 'X'
FROM cst_item_costs cic
WHERE cic.organization_id = cicd.organization_id
AND cic.cost_type_id = cicd.cost_type_id
AND cic.inventory_item_id = cicd.inventory_item_id
AND cic.defaulted_flag = 2
);
UPDATE cst_item_cost_details cicd
SET cicd.rollup_source_type = 1
WHERE cicd.rollup_source_type = 2
AND cicd.cost_type_id = i_cost_type
AND cicd.organization_id = i_org_id
AND cicd.inventory_item_id IN (SELECT msi1.inventory_item_id FROM mtl_system_items_kfv msi1 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to)
AND EXISTS (SELECT 'X'
FROM cst_item_costs cic
WHERE cic.organization_id = cicd.organization_id
AND cic.cost_type_id = cicd.cost_type_id
AND cic.inventory_item_id = cicd.inventory_item_id
AND cic.defaulted_flag = 2
);
UPDATE cst_item_cost_details cicd
SET cicd.rollup_source_type = 1
WHERE cicd.rollup_source_type = 2
AND cicd.cost_type_id = i_cost_type
AND cicd.organization_id = i_org_id
AND cicd.inventory_item_id IN (SELECT mic.inventory_item_id FROM mtl_item_categories mic, mtl_categories_kfv mc
WHERE mic.organization_id = i_org_id
AND mic.category_set_id = i_category_set
AND mic.category_id = mc.category_id
AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)
AND EXISTS (SELECT 'X'
FROM cst_item_costs cic
WHERE cic.organization_id = cicd.organization_id
AND cic.cost_type_id = cicd.cost_type_id
AND cic.inventory_item_id = cicd.inventory_item_id
AND cic.defaulted_flag = 2
);
/* Change this select for bug 4881571 */
END IF;
fnd_file.put_line(fnd_file.log,'Updated '||to_char(SQL%ROWCOUNT)||' rows in cst_item_cost_details making them user-defined.');