[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function cost_update_required (p_config_item_id number,
p_organization_id number,
p_cto_cost_type_id number
)
return varchar2
is
CURSOR cost_details(c_config_item_id number, c_organization_id number, c_cost_type number) IS
select cst.inventory_item_id, cst.organization_id, cst.cost_type_id,
cst.item_cost , cst.material_cost, cst.material_overhead_cost,
cst.resource_cost, cst.outside_processing_cost, cst.overhead_cost
--cicd.cost_element_id ,cicd.item_cost cicd_item_cost
from cst_item_costs cst
where cst.inventory_item_id = c_config_item_id
AND cst.organization_id = c_organization_id
AND cst.cost_type_id = c_cost_type;
oe_debug_pub.add( 'Inside cost_update_required ' ,1 ) ;
END cost_update_required;
Insert a row into the cst_item_costs_table
+------------------------------------------------------- */
lStmtNumber := 220;
insert into CST_ITEM_COSTS
(inventory_item_id,
organization_id,
cost_type_id,
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,
cost_update_id,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
values
(
p_config_item_id, -- INVENTORY_ITEM_ID
p_organization_id,
p_buy_cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
1 , -- C.inventory_asset_flag,
1 , -- C.lot_size,
1 , -- C.based_on_rollup_flag,
0 , -- C.shrinkage_rate,
2 , -- C.defaulted_flag,
NULL, -- cost_update_id
0 , -- C.pl_material,
0 , -- C.pl_material_overhead,
0 , -- C.pl_resource,
0 , -- C.pl_outside_processing,
0 , -- C.pl_overhead,
p_buy_item_cost , -- C.tl_material,
0 , -- C.tl_material_overhead,
0 , -- C.tl_resource,
0 , -- C.tl_outside_processing,
0 , --C.tl_overhead,
p_buy_item_cost , -- C.material_cost,
0 , -- C.material_overhead_cost,
0, -- C.resource_cost,
0 , -- C.outside_processing_cost ,
0 , -- C.overhead_cost,
0 , -- C.pl_item_cost,
p_buy_item_cost , -- C.tl_item_cost,
p_buy_item_cost , -- C.item_cost,
0 , -- C.unburdened_cost ,
0 , -- C.burden_cost,
0 , -- C.attribute_category,
0 , -- C.attribute1,
0 , -- C.attribute2,
0 , -- C.attribute3,
0 , -- C.attribute4,
0 , -- C.attribute5,
0 , -- C.attribute6,
0 , -- C.attribute7,
0 , -- C.attribute8,
0 , -- C.attribute9,
0 , -- C.attribute10,
0 , -- C.attribute11,
0 , -- C.ATTRIBUTE12,
0 , -- C.attribute13,
0 , -- C.attribute14,
0 -- C.attribute15
);
oe_debug_pub.add('populate_buy_cost: ' || 'after insert:CST_ITEM_COSTS',2);
oe_debug_pub.add('populate_buy_cost: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
Insert rows into the cst_item_cost_details table
+-----------------------------------------------------*/
lStmtNumber := 230;
insert into cst_item_cost_details
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select
p_config_item_id, -- inventory_item_id
p_buy_cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
p_organization_id,
null , -- c.operation_sequence_id,
null , -- c.operation_seq_num,
null , -- c.department_id,
1 , -- c.level_type,
null , -- c.activity_id,
null , -- c.resource_seq_num,
mp.default_material_cost_id, -- c.resource_id[material sub element],
1 , -- c.resource_rate,
null , -- c.item_units,
null , -- c.activity_units,
p_buy_item_cost , -- c.usage_rate_or_amount,
1 , -- c.basis_type,
null , -- c.basis_resource_id,
1 , -- c.basis_factor,
1 , -- c.net_yield_or_shrinkage_factor,
p_buy_item_cost, --item_cost
1 , -- c.cost_element_id,
1 , -- C.rollup_source_type,
null , -- C.activity_context,
null , -- C.attribute_category,
null , -- C.attribute1,
null , -- C.attribute2,
null , -- C.attribute3,
null , -- C.attribute4,
null , -- C.attribute5,
null , -- C.attribute6,
null , -- C.attribute7,
null , -- C.attribute8,
null , -- C.attribute9,
null , -- C.attribute10,
null , --C.attribute11,
null , -- C.attribute12,
null , -- C.attribute13,
null , -- C.attribute14,
null -- C.attribute15
from mtl_parameters mp
where mp.organization_id = p_organization_id ;
oe_debug_pub.add('populate_buy_cost: ' || 'after insert:cst_item_cost_details',2);
oe_debug_pub.add('populate_buy_cost: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
Insert a row into the cst_item_costs_table
+------------------------------------------------------- */
lStmtNumber := 220;
insert into CST_ITEM_COSTS
(inventory_item_id,
organization_id,
cost_type_id,
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,
cost_update_id,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
values
(
p_config_item_id, -- INVENTORY_ITEM_ID
p_organization_id,
p_buy_cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
1 , -- C.inventory_asset_flag,
1 , -- C.lot_size,
1 , -- C.based_on_rollup_flag,
0 , -- C.shrinkage_rate,
2 , -- C.defaulted_flag,
NULL, -- cost_update_id
0 , -- C.pl_material,
0 , -- C.pl_material_overhead,
0 , -- C.pl_resource,
0 , -- C.pl_outside_processing,
0 , -- C.pl_overhead,
p_buy_item_cost , -- C.tl_material,
0 , -- C.tl_material_overhead,
0 , -- C.tl_resource,
0 , -- C.tl_outside_processing,
0 , --C.tl_overhead,
p_buy_item_cost , -- C.material_cost,
0 , -- C.material_overhead_cost,
0, -- C.resource_cost,
0 , -- C.outside_processing_cost ,
0 , -- C.overhead_cost,
0 , -- C.pl_item_cost,
p_buy_item_cost , -- C.tl_item_cost,
p_buy_item_cost , -- C.item_cost,
0 , -- C.unburdened_cost ,
0 , -- C.burden_cost,
0 , -- C.attribute_category,
0 , -- C.attribute1,
0 , -- C.attribute2,
0 , -- C.attribute3,
0 , -- C.attribute4,
0 , -- C.attribute5,
0 , -- C.attribute6,
0 , -- C.attribute7,
0 , -- C.attribute8,
0 , -- C.attribute9,
0 , -- C.attribute10,
0 , -- C.attribute11,
0 , -- C.ATTRIBUTE12,
0 , -- C.attribute13,
0 , -- C.attribute14,
0 -- C.attribute15
);
oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:CST_ITEM_COSTS',2);
oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
Insert rows into the cst_item_cost_details table
+-----------------------------------------------------*/
lStmtNumber := 230;
insert into cst_item_cost_details
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select
p_config_item_id, -- inventory_item_id
p_buy_cost_type_id,
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
p_organization_id,
null , -- c.operation_sequence_id,
null , -- c.operation_seq_num,
null , -- c.department_id,
1 , -- c.level_type,
null , -- c.activity_id,
null , -- c.resource_seq_num,
mp.default_material_cost_id, -- c.resource_id[material sub element],
1 , -- c.resource_rate,
null , -- c.item_units,
null , -- c.activity_units,
p_buy_item_cost , -- c.usage_rate_or_amount,
1 , -- c.basis_type,
null , -- c.basis_resource_id,
1 , -- c.basis_factor,
1 , -- c.net_yield_or_shrinkage_factor,
p_buy_item_cost, --item_cost
1 , -- c.cost_element_id,
1 , -- C.rollup_source_type,
null , -- C.activity_context,
null , -- C.attribute_category,
null , -- C.attribute1,
null , -- C.attribute2,
null , -- C.attribute3,
null , -- C.attribute4,
null , -- C.attribute5,
null , -- C.attribute6,
null , -- C.attribute7,
null , -- C.attribute8,
null , -- C.attribute9,
null , -- C.attribute10,
null , --C.attribute11,
null , -- C.attribute12,
null , -- C.attribute13,
null , -- C.attribute14,
null -- C.attribute15
from mtl_parameters mp
where mp.organization_id = p_organization_id ;
oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:cst_item_cost_details',2);
oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
l_cost_update number; --Bugfix 6363308
Insert a row into the cst_item_costs_table
+------------------------------------------------------- */
lStmtNumber := 220;
Select cst_lists_s.nextval
INTO l_cost_update
From DUAL;
insert into CST_ITEM_COSTS
(inventory_item_id,
organization_id,
cost_type_id,
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,
cost_update_id,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select -- distinct --Removing distinct for base BUG 13456609; FP 13541690
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
C.inventory_asset_flag,
C.lot_size,
C.based_on_rollup_flag,
C.shrinkage_rate,
C.defaulted_flag,
--p_cto_cost_type_id, -- cost_update_id
l_cost_update, --Bugfix 6363308 -- cost_update_id now taken from sequence
C.pl_material,
C.pl_material_overhead,
C.pl_resource,
C.pl_outside_processing,
C.pl_overhead,
C.tl_material,
C.tl_material_overhead,
C.tl_resource,
C.tl_outside_processing,
C.tl_overhead,
C.material_cost,
C.material_overhead_cost,
C.resource_cost,
C.outside_processing_cost ,
C.overhead_cost,
C.pl_item_cost,
C.tl_item_cost,
C.item_cost,
C.unburdened_cost ,
C.burden_cost,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.ATTRIBUTE12,
C.attribute13,
C.attribute14,
C.attribute15
from
cst_item_costs C
where C.inventory_item_id = p_config_item_id
and C.organization_id = p_organization_id
and C.cost_type_id = p_cto_cost_type_id;
oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
Insert rows into the cst_item_cost_details table
+-----------------------------------------------------*/
lStmtNumber := 230;
insert into cst_item_cost_details
(inventory_item_id,
cost_type_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
select -- distinct --Removing distinct for base BUG 13456609; FP 13541690
sysdate, -- last_update_date
-1, -- last_updated_by
sysdate, -- creation_date
-1, -- created_by
-1, -- last_update_login
p_organization_id,
c.operation_sequence_id,
c.operation_seq_num,
c.department_id,
c.level_type,
c.activity_id,
c.resource_seq_num,
c.resource_id,
c.resource_rate,
c.item_units,
c.activity_units,
c.usage_rate_or_amount,
c.basis_type,
c.basis_resource_id,
c.basis_factor,
c.net_yield_or_shrinkage_factor,
c.item_cost,
c.cost_element_id,
C.rollup_source_type,
C.activity_context,
C.attribute_category,
C.attribute1,
C.attribute2,
C.attribute3,
C.attribute4,
C.attribute5,
C.attribute6,
C.attribute7,
C.attribute8,
C.attribute9,
C.attribute10,
C.attribute11,
C.attribute12,
C.attribute13,
C.attribute14,
C.attribute15
from
cst_item_cost_details C
where C.inventory_item_id = p_config_item_id
and C.organization_id = p_organization_id
and C.cost_type_id = p_cto_cost_type_id ;
oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'Inserting records in csc and cec',2);
INSERT INTO cst_standard_costs
(cost_update_id,
organization_id,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
standard_cost_revision_date,
standard_cost
)
SELECT
l_cost_update,
p_organization_id,
p_config_item_id,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
SYSDATE,
NVL(SUM(item_cost),0)
FROM
cst_item_cost_details
WHERE organization_id = p_organization_id
AND inventory_item_id = p_config_item_id
AND cost_type_id = 1;
oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:cst_standard_costs ' || sql%rowcount ,2);
INSERT INTO cst_elemental_costs
(cost_update_id,
organization_id,
inventory_item_id,
cost_element_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
standard_cost
)
SELECT
l_cost_update,
p_organization_id,
p_config_item_id,
cost_element_id,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
NVL(SUM(item_cost),0)
FROM
cst_item_cost_details
WHERE organization_id = p_organization_id
AND inventory_item_id = p_config_item_id
AND cost_type_id = 1
GROUP BY cost_element_id;
oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:cst_elemental_costs ' || sql%rowcount ,2);
FUNCTION check_ct_updateable( p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_cost_type_id NUMBER )
Return BOOLEAN IS
Updateable VARCHAR2(10) := null ;
Cursor Check_Updateable is
Select 'NO'
From MTL_MATERIAL_TRANSACTIONS t
Where Inventory_Item_Id = p_inventory_item_id
And Exists
(Select 'all these org have the org as costing org'
From MTL_PARAMETERS
Where Cost_Organization_Id = p_organization_id
AND Organization_Id = t.Organization_Id);
Cursor Check_Updateable_2 is
Select 'NO'
From MTL_MATERIAL_TRANSACTIONS_TEMP t
Where Inventory_Item_Id = p_inventory_item_id
And Exists
(Select 'all these org have the org as costing org'
From MTL_PARAMETERS
Where Cost_Organization_Id = p_organization_id
AND Organization_Id = t.Organization_Id);
oe_debug_pub.add('Inside check_ct_updateable for p_inventory_item_id :'||p_inventory_item_id ||' p_organization_id :'||p_organization_id||' p_cost_type_id :'||p_cost_type_id );
IF (Updateable is NULL) THEN
Open Check_Updateable;
Fetch Check_Updateable into Updateable;
Close Check_Updateable;
oe_debug_pub.add('Inside check_ct_updateable : After closing Check_Updateable flag is :'|| Updateable);
IF (Updateable is Null) THEN
Open Check_Updateable_2;
Fetch Check_Updateable_2 into Updateable;
Close Check_Updateable_2;
oe_debug_pub.add('Inside check_ct_updateable : After closing Check_Updateable_2 flag is :'|| Updateable);
IF (Updateable is NULL) THEN
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('Inside check_ct_updateable : Checking for intransit_count');
select count(*)
into intransit_count
from mtl_supply m
where m.item_id = p_inventory_item_id
and m.intransit_owning_org_id = p_organization_id
and m.to_organization_id = p_organization_id ;
oe_debug_pub.add('Inside check_ct_updateable : Intransit_count ='|| intransit_count);
Updateable := 'NO';
IF (Updateable = 'NO') THEN
-- fnd_message.Set_Name('BOM', 'CST_ITEM_USED_IN_TXN');
oe_debug_pub.add( ' check_ct_updateable is null -> true ' ) ;
oe_debug_pub.add( ' check_ct_updateable is true ' ) ;
oe_debug_pub.add( ' check_ct_updateable is false' ) ;
oe_debug_pub.add( ' check_ct_updateable is null ' ) ;
END Check_CT_Updateable;
select
msi.base_item_id
, msi.inventory_item_id
, mp1.cost_organization_id -- 3116778
, nvl(org.operating_unit,0) oper_unit
from inv_organization_info_v org
, mtl_system_items msi
, cst_item_costs cic
, mtl_parameters mp1
where org.organization_id = msi.organization_id
and cic.inventory_item_id = msi.inventory_item_id
and cic.organization_id = mp1.cost_organization_id --3116778
and cic.based_on_rollup_flag = 1
and cic.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id)
and msi.organization_id = MP1.organization_id
and msi.inventory_item_id = xcfg_itm_id
and msi.organization_id = xcfg_org_id;
select msi.inventory_item_id
, msi.organization_id
, cic.cost_type_id
, cic.item_cost
from mtl_system_items msi
, cst_item_costs cic
, mtl_parameters mp
where msi.organization_id = cic.organization_id
and msi.inventory_item_id = cic.inventory_item_id
and msi.organization_id = mp.organization_id
and mp.primary_cost_method = cic.cost_type_id
and EXISTS /* check whether item has been rolled up */
( select NULL
from cst_item_cost_details
where rollup_source_type = 3
and inventory_item_id = msi.inventory_item_id
and cost_type_id = cic.cost_type_id
and organization_id = msi.organization_id
)
and msi.inventory_item_id = xcfg_itm_id
and msi.organization_id = xcfg_org_id;
cursor c_frozen_cost_update( c_cto_cost_type_id number,
xcfg_itm_id number,
xcfg_org_id number
)
is
select msi.inventory_item_id
, mp.cost_organization_id rollup_org_id
from mtl_system_items msi
, mtl_parameters mp
, cst_item_costs cic
where msi.organization_id = mp.organization_id
and mp.primary_cost_method = 1
and cic.inventory_item_id = msi.inventory_item_id
and cic.organization_id = msi.organization_id
and cic.cost_type_id = c_cto_cost_type_id
and msi.inventory_item_id = xcfg_itm_id
and msi.organization_id = xcfg_org_id;
v_cost_updateable BOOLEAN := false ;
l_cost_update_required varchar2(1) := 'N'; --Bugfix 6717614
CTO_COST_ROLLUP_CONC_PK.g_error_cache.delete;
/*select bom_lists_s.nextval
into lRollupId from dual;*/
select cst_lists_s.nextval
into lRollupId from dual;
select cost_type_id into v_cto_cost_type_id
from cst_cost_types
where cost_type = 'CTO' ;
select cost_type_id into v_cto_cost_type_id
from cst_cost_types
where cost_type = 'CTO' ;
select cost_type into v_cto_cost_type_name
from cst_cost_types
where cost_type_id = v_cto_cost_type_id ;
select cost_type_id into v_buy_cost_type_id
from cst_cost_types
where cost_type = lBuyCostType ;
-- Instead of doing a simple select, we are doing a cursor fetch
-- to ensure that it doesnt fail with too_many_rows.
/* bug 4341197 do not populate list price in buy cost type if the buy cost type profile is not set by the user */
if( lBuyCostType is not null ) then
oe_debug_pub.add ('---------------------------------------------------------------------------');
Select nvl(inventory_organization_id,0)
into c_po_valid_org
from financials_system_params_All
where org_id = c_oper_unit;
select item_cost into v_buy_cost
from cst_item_costs
where cost_type_id = v_buy_cost_type_id
and organization_id = c_org_id
and inventory_item_id = c_config_item_id ;
select nvl(list_price_per_unit,0) into v_buy_item_cost
from mtl_system_items
where organization_id = c_po_valid_org
and inventory_item_id = c_config_item_id ;
select organization_name into v_po_validation_org
from inv_organization_name_v
where organization_id = c_po_valid_org ;
select organization_name into v_org
from inv_organization_name_v
where organization_id = c_org_id ;
select concatenated_segments into v_model_name
from mtl_system_items_kfv
where inventory_item_id = ( select base_item_id
from mtl_system_items where inventory_item_id = c_config_item_id and rownum = 1 )
and rownum = 1 ;
select item_cost into v_cto_cost from cst_item_costs
where inventory_item_id = c_match_config_item_id
and organization_id = c_organization_id
and cost_type_id = v_cto_cost_type_id ;
/* can't do bulk insert into cst_sc_lists for PLS-00436 */
/* insert item by item */
lStmtNumber := 39.1;
insert into cst_sc_lists(
rollup_id,
inventory_item_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by
)
select distinct
lRollupId,
p_cfg_itm_tbl(i).cfg_item_id,
mp.cost_organization_id, --3116778
sysdate,
gUserId,
sysdate,
gUserId
from mtl_system_items msi,
mtl_parameters mp ,
cst_item_costs cic
where msi.costing_enabled_flag = 'Y'
and mp.organization_id = msi.organization_id
and cic.inventory_item_id = msi.base_item_id
and cic.organization_id = mp.cost_organization_id
and cic.based_on_rollup_flag = 1
and mp.primary_cost_method = cic.cost_type_id
and cic.cost_type_id in ( 1, 2, 5, 6 )
and msi.inventory_item_id = p_cfg_itm_tbl(i).cfg_item_id
and msi.organization_id = p_cfg_itm_tbl(i).cfg_org_id
and NOT EXISTS
(select NULL
from cst_sc_lists
where rollup_id = lRollupId
and inventory_item_id = msi.inventory_item_id
and organization_id = mp.cost_organization_id) --3116778
and NOT EXISTS /* check whether item has been rolled up */
( select NULL
from cst_item_cost_details
where rollup_source_type = 3
and inventory_item_id = msi.inventory_item_id
and cost_type_id = cic.cost_type_id
and organization_id = mp.cost_organization_id) ; --3116778
oe_debug_pub.add('populate_buy_cost: ' || 'rows inserted into bom_lists::'||to_char(lCnt), 2);
/* update Frozen cost with CTO Cost in case of standard costing organizations */
for i in p_cfg_itm_tbl.FIRST..p_cfg_itm_tbl.LAST
LOOP
--Bugfix 12957444: Skipping the configs that are in error cache.
if CTO_COST_ROLLUP_CONC_PK.g_error_cache.exists(p_cfg_itm_tbl(i).cfg_item_id || '-' || p_cfg_itm_tbl(i).cfg_org_id)
then
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('Config::' || p_cfg_itm_tbl(i).cfg_item_id || ' in org::' || p_cfg_itm_tbl(i).cfg_org_id ||
'is in error. Skipping it.');
open c_frozen_cost_update ( v_cto_cost_type_id,
p_cfg_itm_tbl(i).cfg_item_id,
p_cfg_itm_tbl(i).cfg_org_id) ;
fetch c_frozen_cost_update into c_config_item_id
, c_organization_id ;
select mp1.primary_cost_method into v_primary_cost_method
from mtl_parameters mp1
where mp1.organization_id = c_organization_id ;
v_is_cst_updatable := check_ct_updateable(c_config_item_id, c_organization_id, 1);
IF c_frozen_cost_update%found THEN --Bugfix 6038548
lStmtNumber := 95;
l_cost_update_required := cost_update_required(c_config_item_id,
c_organization_id,
v_cto_cost_type_id);
IF (l_cost_update_required = 'Y') THEN
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('***************** going to do frozen cost update for config ' || c_config_item_id
|| ' in org ' || c_organization_id , 1);
delete from cst_item_cost_details
where inventory_item_id = c_config_item_id
and organization_id = c_organization_id
and cost_type_id = 1 ;
oe_debug_pub.add('deleted details ' || SQL%ROWCOUNT , 1);
delete from cst_item_costs
where inventory_item_id = c_config_item_id
and organization_id = c_organization_id
and cost_type_id = 1 ;
oe_debug_pub.add('deleted headers ' || SQL%ROWCOUNT , 1);
END IF; --c_frozen_cost_update%found. Bugfix 6038548
-- Update CTO cost with Frozen cost
--
CTO_UTILITY_PK.copy_cost(v_primary_cost_method
, v_cto_cost_type_id
, c_config_item_id
, c_organization_id
);
END IF; -- IF check_ct_updateable = TRUE
close c_frozen_cost_update ;
select bcso.line_id
, bcso.model_item_id
, bcso.config_item_id
-- 3116778 , bcso.rcv_org_id
, mp2.cost_organization_id -- 3116778
, nvl(fsp.inventory_organization_id,0) po_valid_org
, nvl(org.operating_unit,0) oper_unit
from bom_cto_src_orgs bcso
, bom_cto_order_lines bcol
, financials_system_params_all fsp
, inv_organization_info_v org
, cst_item_costs cic
, mtl_parameters mp1 /* master organization */
, mtl_parameters mp2
where bcso.top_model_line_id = pTopAtoLineId
and bcol.line_id = bcso.line_id
and bcso.cost_rollup = 'Y'
and ( ( bcso.organization_type in (3,5) and bcol.config_creation in ( 1,2) )
OR bcol.config_creation = 3 )
and org.organization_id = bcso.organization_id
and nvl(org.operating_unit,-1) = nvl(fsp.org_id,-1)
and cic.inventory_item_id = bcso.config_item_id
and cic.organization_id = mp2.cost_organization_id -- 3116778
and mp2.organization_id = bcso.organization_id -- 3116778
and mp2.cost_organization_id = mp1.organization_id -- 3116778
and cic.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id) ;
The original cursor c_frozen_cost_update was using a union (organization_id , rcv_org_id )
to determine organizations where standard cost update needs to be performed.
The organizations where standard cost update will be performed will now be determined using organization_id.
This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
A distinct clause has been added to the statement.
*/
cursor c_frozen_cost_update(c_cto_cost_type_id number )
is
select distinct -- bug 3931290
bcso.config_item_id
, mp1.cost_organization_id rollup_org_id
from bom_cto_src_orgs bcso
, mtl_parameters mp1
, mtl_parameters mp2
, cst_item_costs cic
where bcso.top_model_line_id = pTopAtoLineId
and bcso.cost_rollup = 'Y'
and bcso.organization_id = mp2.organization_id
and mp2.cost_organization_id = mp1.organization_id
and mp1.primary_cost_method = 1
and cic.inventory_item_id = bcso.config_item_id
and cic.organization_id = mp1.organization_id
and cic.cost_type_id = c_cto_cost_type_id
and ( cic.based_on_rollup_flag = 1 or bcso.organization_type = 3 ); /* bug 3931290 */
select inventory_item_id,organization_id
from cst_sc_lists
where rollup_id =xRollupId;
select cst.inventory_item_id, cst.organization_id, cst.cost_type_id,
cst.item_cost , cst.material_cost, cst.material_overhead_cost,
cicd.cost_element_id ,cicd.item_cost cicd_item_cost
from cst_item_costs cst, cst_item_cost_details cicd
where cst.inventory_item_id = cicd.inventory_item_id
and cst.organization_id = cicd.organization_id
and cst.cost_type_id = cicd.cost_type_id
and cst.inventory_item_id = c_inventory_item_id ;
v_cost_updateable BOOLEAN := false ;
l_cost_update_required varchar2(1) := 'N'; --bug 6717614
/*select bom_lists_s.nextval
into lRollupId from dual;*/
select cst_lists_s.nextval
into lRollupId from dual;
select cost_type_id into v_buy_cost_type_id
from cst_cost_types
where cost_type = lBuyCostType ;
select item_cost into v_buy_cost
from cst_item_costs
where cost_type_id = v_buy_cost_type_id
and organization_id = c_cost_org_id -- 3116778
and inventory_item_id = c_config_item_id ;
select nvl(list_price_per_unit,0) into v_buy_item_cost
from mtl_system_items
where organization_id = c_po_valid_org and inventory_item_id = c_config_item_id ;
select organization_name into v_po_validation_org
from inv_organization_name_v
where organization_id = c_po_valid_org ;
select organization_name into v_org
from inv_organization_name_v
where organization_id = c_cost_org_id ;
select concatenated_segments into v_model_name
from mtl_system_items_kfv
where inventory_item_id = ( select base_item_id
from mtl_system_items where inventory_item_id = c_config_item_id and rownum = 1 )
and rownum = 1 ;
-- insert into CST_SC_LISTS rows for all config items in all possible
-- src orgs, where ->
-- 1. cost has not been calculated yet (cost_rollup = Y)
-- 2. costing_enabled_flag = Y
-- 3. primary_cost_method = 1 (standard)
--
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('Cost_Rollup_ML: ' || 'inserting into cst_sc_lists', 2);
The statement insert into cst_sc_lists was using a union (organization_id, rcv_org_id)
to determine organizations where cost rollup needs to be performed.
The organization where cost rollup will be performed will now be determined using organization_id.
This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
*/
lStmtNumber := 38;
insert into cst_sc_lists(
rollup_id,
inventory_item_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by
)
select distinct
lRollupId,
bcso.config_item_id,
mp.cost_organization_id,
sysdate,
gUserId,
sysdate,
gUserId
from bom_cto_src_orgs bcso,
mtl_system_items msi,
mtl_parameters mp ,
cst_item_costs cic
where bcso.top_model_line_id = pTopAtoLineId
and bcso.cost_rollup = 'Y'
and bcso.config_item_id = msi.inventory_item_id
and bcso.organization_id = msi.organization_id
and msi.costing_enabled_flag = 'Y'
and mp.organization_id = bcso.organization_id
and cic.inventory_item_id = msi.inventory_item_id
-- 3116778 and cic.organization_id = msi.organization_id
and cic.organization_id = mp.cost_organization_id -- 3116778
-- 3116778 and cic.based_on_rollup_flag = 1
and (cic.based_on_rollup_flag = 1 or bcso.organization_type = 3) -- 3116778
and (
( ( mp.primary_cost_method = 1 )
and cic.cost_type_id = 1
)
OR
( ( mp.primary_cost_method = 2 )
and cic.cost_type_id = 2
)
OR
( ( mp.primary_cost_method = 6 )
and cic.cost_type_id = 6
)
OR
( ( mp.primary_cost_method = 5 )
and cic.cost_type_id = 5
)
)
and NOT EXISTS
(select NULL
from cst_sc_lists
where rollup_id = lRollupId
and inventory_item_id = bcso.config_item_id
and organization_id = mp.cost_organization_id ) ;
oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
select cost_type_id into v_cto_cost_type_id
from cst_cost_types
where cost_type = 'CTO' ;
select cost_type_id into v_cto_cost_type_id
from cst_cost_types
where cost_type = 'CTO' ;
select cost_type into v_cto_cost_type_name
from cst_cost_types
where cost_type_id = v_cto_cost_type_id ;
select config_item_id
into c1_config_item_id
from bom_cto_order_lines
where line_id = pTopatolineid ;
if cst_sc_list_count > 0 then --bug4867460: call the costing api only if records are inserted
-- in cst_sc_lists. This is to improve performance.
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('******************** calling costing API ****************** ', 2);
oe_debug_pub.add('***No need to do frozen cost update as no rollup has been done' , 1);
/*update Frozen cost with CTO Cost in case of standard costing organizations */
lStmtNumber := 90;
open c_frozen_cost_update ( v_cto_cost_type_id ) ;
fetch c_frozen_cost_update into c_config_item_id
, c_organization_id ;
exit when c_frozen_cost_update%notfound ;
select mp1.primary_cost_method into v_primary_cost_method
from mtl_parameters mp1
where mp1.organization_id = c_organization_id ;
v_is_cst_updatable := check_ct_updateable(c_config_item_id, c_organization_id, 1);
oe_debug_pub.add('Cost_Rollup_ML: ' || 'v_is_cst_updatable = TRUE, so calling the frozen cost update API');
l_cost_update_required := cost_update_required(c_config_item_id,
c_organization_id,
v_cto_cost_type_id);
IF (l_cost_update_required = 'Y') THEN
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('********************** going to do frozen cost update for config ' || c_config_item_id
|| ' in org ' || c_organization_id , 1);
delete from cst_item_cost_details
where inventory_item_id = c_config_item_id
and organization_id = c_organization_id
and cost_type_id = 1 ;
oe_debug_pub.add('deleted details ' || SQL%ROWCOUNT , 1);
delete from cst_item_costs
where inventory_item_id = c_config_item_id
and organization_id = c_organization_id
and cost_type_id = 1 ;
oe_debug_pub.add('deleted headers ' || SQL%ROWCOUNT , 1);
END IF; --l_cost_update_required Bugfix 6717614
-- Update CTO cost with Frozen cost
--
CTO_UTILITY_PK.copy_cost(v_primary_cost_method
, v_cto_cost_type_id
, c_config_item_id
, c_organization_id
);
END IF; -- IF check_ct_updateable = TRUE THEN
close c_frozen_cost_update ;