[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update CST_ITEM_CST_DTLS_INTERFACE ct
SET error_flag = 'E',
error_code = 'CST_NULL_ORGANIZATION',
error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ORGANIZATION'),1,240)
where (Organization_id is null AND organization_code is null)
AND error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct
SET error_flag = 'E',
error_code = 'CST_INVALID_ORGANIZATION',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ORGANIZATION'),1,240)
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND NOT EXISTS (select 1 from mtl_parameters mp
where (NVL(ct.organization_id,mp.organization_id) = mp.organization_id)
AND (NVL(ct.organization_code,mp.organization_code) = mp.organization_code)
);
/* Select the corresponding organization_id from mtl_parameters given the
organization code.*/
Update CST_ITEM_CST_DTLS_INTERFACE ct
SET organization_id = (select organization_id
FROM mtl_parameters mp
WHERE mp.organization_code = ct.organization_code
)
WHERE ct.organization_id is null
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct
SET error_flag = 'E',
error_code = 'CST_PROCESS_ORG_ERROR',
error_explanation =
substrb(fnd_message.get_string(
'GMF','GMF_PROCESS_ORG_ERROR'),1,240)
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND EXISTS (select 'This is a process manufacturing org'
from mtl_parameters mp
where mp.organization_id = ct.organization_id
AND NVL(mp.process_enabled_flag, 'N') = 'Y'
)
;
Update CST_ITEM_CST_DTLS_INTERFACE
SET transaction_id = CST_ITEM_CST_DTLS_INTERFACE_S.NEXTVAL,
request_id = FND_GLOBAL.CONC_REQUEST_ID,
error_code = null,
error_explanation = null,
program_application_id = FND_GLOBAL.PROG_APPL_ID,
program_id = FND_GLOBAL.CONC_PROGRAM_ID,
program_update_date = sysdate,
process_flag = 2
where error_flag is null
AND process_flag = 1
AND group_id=SEQ_NEXTVAL;
UPDATE CST_ITEM_CST_DTLS_INTERFACE ct
SET ct.error_flag ='E',
ct.error_code = 'CST_NOT_COSTINGORG',
ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_COSTINGORG'),1,240)
WHERE ct.group_id = SEQ_NEXTVAL
AND ct.error_flag is null
AND EXISTS (Select 1 from MTL_PARAMETERS mp
WHERE mp.cost_organization_id <> mp.organization_id
AND mp.organization_id = ct.organization_id );
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_NULL_ITEMID',
error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_ITEMID'),1,240)
where group_id = SEQ_NEXTVAL
AND error_flag is null
AND inventory_item_id is null;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_ITEM_ID',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ITEMID'),1,240)
where group_id = SEQ_NEXTVAL
AND error_flag is null
AND NOT EXISTS (select 1 from mtl_system_items msi
where ct.organization_id = msi.organization_id
AND ct.inventory_item_id = msi.inventory_item_id
);
insert_csttype_and_def(Err,i_new_csttype,i_group_id);
Update CST_ITEM_CST_DTLS_INTERFACE ct
set ct.error_flag='E',
ct.error_code = 'CST_NOT_INVASSITEM',
ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_NOT_INVASSITEM'),1,240)
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND ct.inventory_asset_flag <> 1 ;
Update CST_ITEM_CST_DTLS_INTERFACE
SET error_flag = 'E',
error_code = 'CST_NULL_COSTELEMENT',
error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_COSTELEMENT'),1,240)
where (cost_element_id is null AND cost_element is null)
AND group_id = SEQ_NEXTVAL
AND error_flag is null;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_COSTELEMENT',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_COSTELEMENT'),1,240)
WHERE group_id = SEQ_NEXTVAL
AND error_flag is null
AND NOT EXISTS( Select 1 from cst_cost_elements cce where
NVL(ct.cost_element_id,cce.cost_element_id)= cce.cost_element_id AND NVL(ct.cost_element,cce.cost_element) = cce.cost_element
);
Update CST_ITEM_CST_DTLS_INTERFACE ct set
ct.cost_element_id = (select cost_element_id from cst_cost_elements cce
WHERE cce.cost_element = ct.cost_element)
WHERE ct.cost_element_id is null
AND ct.group_id = SEQ_NEXTVAL
AND ct.error_flag is null;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_SUBELEMENT',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_SUBELEMENT'),1,240)
WHERE error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND (ct.resource_id is NOT NULL OR ct.resource_code is not null)
AND NOT EXISTS (select 1 from bom_resources bm
WHERE NVL(ct.resource_id,bm.resource_id)=bm.resource_id
AND NVL(ct.resource_code,bm.resource_code)=bm.resource_code
AND ct.cost_element_id = bm.cost_element_id
AND ct.organization_id = bm.organization_id
);
Update CST_ITEM_CST_DTLS_INTERFACE ct
set ct.resource_id = (select bm.resource_id from bom_resources bm
WHERE ct.resource_code = bm.resource_code
AND bm.organization_id = ct.organization_id
AND ct.cost_element_id = bm.cost_element_id
)
WHERE ct.resource_id is null
AND ct.resource_code is not null
and ct.error_flag is null
and ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_ACTIVITY',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ACTIVITY'),1,240)
WHERE group_id = SEQ_NEXTVAL
AND ((ct.activity_id is not null ) OR (ct.activity is not null))
AND error_flag is null
AND NOT EXISTS( Select 1 from cst_activities ca where
NVL(ct.activity_id,ca.activity_id)= ca.activity_id AND NVL(ct.activity,ca.activity) = ca.activity
AND ct.organization_id = NVL(ca.organization_id,ct.organization_id)
AND NVL(ca.disable_date,sysdate +1) > sysdate
);
Update CST_ITEM_CST_DTLS_INTERFACE ct set
ct.activity_id = (select ca.activity_id from cst_activities ca
where ca.activity = ct.activity
AND NVL(ca.organization_id,ct.organization_id) = ct.organization_id
)
WHERE ct.activity_id is NULL
AND ct.activity is not null
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
resource_id = (Select Decode(ct.cost_element_id,1,mp.default_material_cost_id,null) from mtl_parameters mp where mp.organization_id = ct.organization_id)
WHERE error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND (ct.resource_id is null AND ct.resource_code is null);
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_NULL_DEFSUBELEMENT',
error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_DEFSUBELEMENT'),1,240)
WHERE error_flag is null
AND ct.group_id = SEQ_NEXTVAL
and resource_id is null;
Update CST_ITEM_CST_DTLS_INTERFACE ct
set ct.error_flag = 'E',
ct.error_code = 'CST_EXP_SUBELEMENT',
ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_EXP_SUBELEMENT'),1,240)
where ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND EXISTS (select 1 from BOM_RESOURCES bm
where bm.organization_id = ct.organization_id
AND bm.cost_element_id = ct.cost_element_id
AND bm.resource_id = ct.resource_id
AND ((sysdate >= NVL(bm.disable_date,sysdate+1)) OR (bm.allow_costs_flag = 2)));
Update CST_ITEM_CST_DTLS_INTERFACE ct
set error_flag = 'E',
error_code = 'CST_INVALID_FUNCCODE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_FUNCCODE'),1,240)
WHERE error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND ct.cost_element_id IN (3,4)
AND NOT EXISTS (select 1 from bom_resources bm
where bm.functional_currency_flag = 1
AND bm.resource_id = ct.resource_id
AND bm.cost_element_id = ct.cost_element_id
AND bm.organization_id = ct.organization_id
);
Update CST_ITEM_CST_DTLS_INTERFACE set
process_flag = 3 where
group_id=SEQ_NEXTVAL
AND error_flag is null
AND process_flag = 2;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
Error_flag = 'E',
error_code = 'CST_INVALID_ROLLUP_SRC_TYPE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ROLLUP_SRC_TYPE'),1,240)
where rollup_source_type <> 1
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
Error_flag = 'E',
error_code = 'CST_INVALID_LEVELTYPE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_LEVELTYPE'),1,240)
where level_type <> 1
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
Error_flag = 'E',
error_code = 'CST_NULL_USAGERTORAMT',
Error_explanation = substrb(fnd_message.get_string('BOM','CST_NULL_USAGERTORAMT'),1,240)
where error_flag is null
AND Usage_rate_or_amount is null
AND group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
Error_flag = 'E',
error_code = 'CST_INVALID_BASISTYPE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BASISTYPE'),1,240)
where ((ct.cost_element_id IN (1,3,4,5,6) AND ct.basis_type NOT IN (1,2)) OR (ct.cost_element_id = 2 AND (ct.basis_type <= 0 OR ct.basis_type > 6)))
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
Error_flag = 'E',
error_code ='CST_NO_ITORACUNITS',
error_explanation = substrb(fnd_message.get_string('BOM','CST_NO_ITORACUNITS'),1,240)
where ct.basis_type = 6
AND (ct.activity_units is null OR ct.item_units is null OR ct.item_units = 0)
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_SHRRATE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_SHRRATE'),1,240)
where (ct.shrinkage_rate < 0 OR ct.shrinkage_rate >= 1)
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_ZERO_LOTSIZE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_ZERO_LOTSIZE'),1,240)
where error_flag is null
AND group_id = SEQ_NEXTVAL
AND ct.lot_size <= 0;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_BASEDONRLP',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BASEDONRLP'),1,240)
where ct.based_on_rollup_flag NOT IN (1,2)
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_INVASSETFLG',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_INVASSETFLG'),1,240)
where ct.inventory_asset_flag NOT IN (1,2)
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
UPDATE CST_ITEM_CST_DTLS_INTERFACE set
error_flag = 'E',
error_code = 'CST_INVALID_RESRATE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_RESRATE'),1,240)
where error_flag is null
AND group_id = SEQ_NEXTVAL
AND ((resource_rate <> 1) AND (resource_rate is not null));
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag = 'E',
error_code = 'CST_INVALID_BUYITEM',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_BUYITEM'),1,240)
where ct.based_on_rollup_flag <> 1
AND ct.shrinkage_rate <> 0
AND ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct1 set
Error_flag = 'E',
error_code = 'CST_INVALID_CICFLAGS',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_CICFLAGS'),1,240)
where EXISTS (select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2 where
((NVL(ct1.based_on_rollup_flag,-1)<> NVL(ct2.based_on_rollup_flag,-1))
OR (NVL(ct1.shrinkage_rate,-1) <> NVL(ct2.shrinkage_rate,-1))
OR (NVL(ct1.inventory_asset_flag,-1) <> NVL(ct2.inventory_asset_flag,-1))
OR (NVL(ct1.lot_size,-1) <> NVL(ct2.lot_size,-1)))
AND ct1.organization_id = ct2.organization_id
AND ct1.inventory_item_id = ct2.inventory_item_id
AND ct1.cost_type_id = ct2.cost_type_id
AND ct2.group_id = SEQ_NEXTVAL
AND ct1.rowid <> ct2.rowid
)
AND ct1.group_id = SEQ_NEXTVAL
AND ct1.error_flag is null;
Update CST_ITEM_CST_DTLS_INTERFACE ct1
set ERROR_FLAG ='E',
error_code = 'CST_INVALID_ROWS',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_ROWS'),1,240)
WHERE EXISTS ( select 1 from CST_ITEM_CST_DTLS_INTERFACE ct2
WHERE ct1.organization_id = ct2.organization_id
AND ct1.inventory_item_id = ct2.inventory_item_id
AND ct1.cost_type_id = ct2.cost_type_id
AND ct2.error_flag = 'E'
AND ct2.group_id = SEQ_NEXTVAL)
AND ct1.error_flag is null
AND ct1.group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE set
process_flag = 4 where
group_id=SEQ_NEXTVAL
AND error_flag is null
AND process_flag = 3;
Procedure insert_csttype_and_def(Error_number OUT NOCOPY NUMBER
,i_new_csttype IN Varchar2
,i_group_id IN NUMBER
) is
l_stmt_no NUMBER := 0;
fnd_file.put_line(fnd_file.log,'-------------at the start of insert_csttype_and_def procedure-----------');
Select default_cost_type_id into l_def_cost_type_id from cst_cost_types
where cost_type = i_new_csttype;
select cost_type_id into l_cost_type_id
from CST_COST_TYPES
where cost_type = i_new_csttype;
/* Now update all the rows of the interface table with the new cost type id */
Update CST_ITEM_CST_DTLS_INTERFACE ct set
ct.cost_type_id = l_cost_type_id,
ct.cost_type = i_new_csttype
where error_flag is null
and group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
error_flag ='E',
error_code = 'CST_INVALID_DEFCSTTYPE',
error_explanation = substrb(fnd_message.get_string('BOM','CST_INVALID_DEFCSTTYPE'),1,240)
where
ct.error_flag is null
and ct.group_id = SEQ_NEXTVAL
and NOT EXISTS (select 1 from cst_item_costs cic where
ct.organization_id = cic.organization_id
and cic.cost_type_id = l_def_cost_type_id
and ct.inventory_item_id = cic.inventory_item_id)
AND (ct.lot_size is null OR ct.based_on_rollup_flag is null OR shrinkage_rate is null OR inventory_asset_flag is null) ;
Update CST_ITEM_CST_DTLS_INTERFACE ct set
rollup_source_type = NVL(ct.rollup_source_type,1),
basis_type = NVL(basis_type,1),
resource_rate = NVL(resource_rate,1),
level_type = NVL(ct.level_type,1)
where error_flag is null
and group_id= SEQ_NEXTVAL
and (rollup_source_type is null OR basis_type is null OR resource_rate is null OR level_type is null);
Update CST_ITEM_CST_DTLS_INTERFACE ct set
(lot_size,based_on_rollup_flag,shrinkage_rate,inventory_asset_flag) =
(Select NVL(ct.lot_size,cic.lot_size),
NVL(ct.based_on_rollup_flag,cic.based_on_rollup_flag),
NVL(ct.shrinkage_rate,cic.shrinkage_rate),
NVL(ct.inventory_asset_flag,cic.inventory_asset_flag)
FROM CST_ITEM_COSTS cic
WHERE cic.organization_id = ct.organization_id
AND cic.cost_type_id = l_def_cost_type_id
AND cic.inventory_item_id = ct.inventory_item_id )
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND (ct.lot_size is null OR ct.based_on_rollup_flag is null OR ct.inventory_asset_flag is null OR ct.shrinkage_rate is null);
fnd_file.put_line(fnd_file.log,'CICDI insert_csttype_and_def('|| to_char(l_stmt_no) || '),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
END insert_csttype_and_def;
Procedure insert_cic_cicd(Error_number OUT NOCOPY NUMBER,
i_group_id IN NUMBER,
i_del_option IN NUMBER,
i_run_option IN NUMBER) is
SEQ_NEXTVAL NUMBER;
/*first get the net_yield and basis_factor and update the interface tables */
fnd_file.put_line(fnd_file.log,'---------------entered the insert_cic_cicd procedure-----------------');
Update CST_ITEM_CST_DTLS_INTERFACE set
basis_factor = Decode(basis_type,1,1,2,(1/lot_size),6,(Activity_units/item_units))
where error_flag is null
and group_id = SEQ_NEXTVAL
and basis_type IN (1,2,6);
Update CST_ITEM_CST_DTLS_INTERFACE cicdi1 set
cicdi1.basis_factor = (select NVL(SUM(cicdi2.usage_rate_or_amount * cicdi2.basis_factor),0) from CST_ITEM_CST_DTLS_INTERFACE cicdi2
WHERE cicdi2.organization_id = cicdi1.organization_id
AND cicdi2.inventory_item_id = cicdi1.inventory_item_id
AND cicdi2.cost_type_id = cicdi1.cost_type_id
AND cicdi2.cost_element_id in (3,4)
AND cicdi2.error_flag is null
AND cicdi2.group_id = SEQ_NEXTVAL
AND EXISTS (select 1 from CST_RESOURCE_OVERHEADS cro
WHERE cro.cost_type_id = cicdi1.cost_type_id
AND cro.organization_id = cicdi1.organization_id
AND cro.overhead_id = cicdi1.resource_id
AND cro.resource_id = cicdi2.resource_id))
WHERE cicdi1.error_flag is null
AND cicdi1.group_id = SEQ_NEXTVAL
AND cicdi1.basis_type = 3
AND cicdi1.cost_element_id=2;
Update CST_ITEM_CST_DTLS_INTERFACE CICDI set
net_yield_or_shrinkage_factor = Decode(basis_type,1,(1/(1-shrinkage_rate)),2,(1/(1-shrinkage_rate)),3,(1/(1-shrinkage_rate)),1)
where error_flag is null
and group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE set
item_cost = NVL(usage_rate_or_amount,0) * NVL(basis_factor,1) * NVL(net_yield_or_shrinkage_factor,1) * NVL(resource_rate,1)
where basis_type IN (1,2,3,6)
AND error_flag is null
and group_id = SEQ_NEXTVAL;
Update CST_ITEM_CST_DTLS_INTERFACE cicdi1
set (cicdi1.basis_factor,cicdi1.item_cost) = (
select NVL(SUM(cicdi2.item_cost),0),(cicdi1.usage_rate_or_amount * NVL(SUM(cicdi2.item_cost),0)) from CST_ITEM_CST_DTLS_INTERFACE cicdi2
WHERE cicdi2.inventory_item_id = cicdi1.inventory_item_id
AND cicdi2.organization_id = cicdi1.organization_id
AND cicdi2.cost_type_id = cicdi1.cost_type_id
AND cicdi2.error_flag is null
AND cicdi2.group_id = SEQ_NEXTVAL
AND cicdi2.cost_element_id IN (3,4)
AND EXISTS ( select 1 from CST_RESOURCE_OVERHEADS cro
WHERE cro.organization_id = cicdi2.organization_id
AND cro.cost_type_id = cicdi2.cost_type_id
AND cro.overhead_id = cicdi1.resource_id
AND cro.resource_id = cicdi2.resource_id))
WHERE cicdi1.error_flag is null
AND cicdi1.group_id = SEQ_NEXTVAL
AND cicdi1.basis_type = 4
AND cicdi1.cost_element_id = 2;
Update CST_ITEM_CST_DTLS_INTERFACE cicdi1
set (cicdi1.basis_factor,cicdi1.item_cost) = (
select /*+ INDEX(cicdi2 CST_ITEM_CST_DTLS_INTERFACE_N1)*/
NVL(SUM(cicdi2.item_cost),0),(cicdi1.usage_rate_or_amount * NVL(SUM(cicdi2.item_cost),0)) from CST_ITEM_CST_DTLS_INTERFACE cicdi2
WHERE cicdi2.organization_id = cicdi1.organization_id
AND cicdi2.inventory_item_id = cicdi1.inventory_item_id
AND cicdi2.cost_type_id = cicdi1.cost_type_id
AND cicdi2.error_flag is null
AND cicdi2.group_id = SEQ_NEXTVAL
AND cicdi2.cost_element_id <> 2)
WHERE cicdi1.error_flag is null
AND cicdi1.group_id = SEQ_NEXTVAL
AND cicdi1.basis_type = 5
AND cicdi1.cost_element_id = 2;
/* Now insert first into cst_item_costs */
/* here we check for the run option.If it is insert only mode, we error out those rows for which rows already exist in CIC for the same item,org,cost type combo.For rem and replace mode, we just delete off all the existing rows and proceed */
IF i_run_option = 2 then
delete from cst_item_costs cic
WHERE (ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID) in
(Select ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID
from CST_ITEM_CST_DTLS_INTERFACE ct
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL);
delete from cst_item_cost_details cicd
WHERE (ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID) in
(Select ORGANIZATION_ID,INVENTORY_ITEM_ID,COST_TYPE_ID
from CST_ITEM_CST_DTLS_INTERFACE ct
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL);
UPDATE CST_ITEM_CST_DTLS_INTERFACE ct set
ct.error_flag ='E',
ct.error_code = 'CST_CANT_INSERT',
ct.error_explanation = substrb(fnd_message.get_string('BOM','CST_CANT_INSERT'),1,240)
WHERE ct.error_flag is null
AND ct.group_id = SEQ_NEXTVAL
AND EXISTS(Select 1 from cst_item_costs cic
WHERE ct.organization_id = cic.organization_id
AND ct.cost_type_id = cic.cost_type_id
AND ct.inventory_item_id = cic.inventory_item_id);
Insert into CST_ITEM_COSTS (Inventory_item_id,
organization_id,
cost_type_id,
Last_update_date,
last_updated_by,
creation_date,
created_by,
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,
item_cost,
unburdened_cost,
burden_cost,
request_id,
program_application_id,
program_id,
program_update_date )
Select Inventory_item_id,
organization_id,
cost_type_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
inventory_asset_flag,
lot_size,
based_on_rollup_flag,
shrinkage_rate,
2,
0,
0,
0,
0,
0,
SUM(decode(cost_element_id,1,ct.item_cost,null)),
SUM(decode(cost_element_id,2,ct.item_cost,null)),
SUM(decode(cost_element_id,3,ct.item_cost,null)),
SUM(decode(cost_element_id,4,ct.item_cost,null)),
SUM(decode(cost_element_id,5,ct.item_cost,null)),
SUM(decode(cost_element_id,1,ct.item_cost,null)),
SUM(decode(cost_element_id,2,ct.item_cost,null)),
SUM(decode(cost_element_id,3,ct.item_cost,null)),
SUM(decode(cost_element_id,4,ct.item_cost,null)),
SUM(decode(cost_element_id,5,ct.item_cost,null)),
0,
SUM(item_cost),
SUM(item_cost),
(SUM(ct.item_cost) - SUM(decode(ct.cost_element_id,2,ct.item_cost,0))),
SUM(decode(ct.cost_element_id,2,ct.item_cost,null)),
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate
FROM CST_ITEM_CST_DTLS_INTERFACE ct
WHERE error_flag is null
AND group_id = SEQ_NEXTVAL
group by organization_id,inventory_item_id,cost_type_id,based_on_rollup_flag,shrinkage_rate,lot_size,inventory_asset_flag;
fnd_file.put_line(fnd_file.log,'after the insert into CIC. Rows -> ' || sql%rowcount);
fnd_file.put_line(fnd_file.log,'sucessfully completed inserting ' || to_char(SQL%ROWCOUNT)|| ' rows into cic');
update CST_ITEM_COSTS cic set (attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15) =
(select attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
from cst_item_costs_interface cic_i
where group_id = SEQ_NEXTVAL
AND cic_i.inventory_item_id = cic.inventory_item_id
and cic_i.organization_id = cic.organization_id
and cic_i.cost_type_id = cic.cost_type_id )
WHERE (cic.organization_id, cic.inventory_item_id,
cic.cost_type_id) IN
(SELECT cici2.organization_id,
cici2.inventory_item_id,
cici2.cost_type_id
FROM cst_item_costs_interface cici2
where group_id = SEQ_NEXTVAL);
fnd_file.put_line(fnd_file.log,'Updated attribute info..... Rows -> ' || sql%rowcount);
/* Now insert into CICD */
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,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
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,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
SELECT inventory_item_id,
organization_id,
cost_type_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
level_type,
activity_id,
resource_id,
resource_rate,
item_units,
activity_units,
usage_rate_or_amount,
basis_type,
basis_factor,
net_yield_or_shrinkage_factor,
item_cost,
cost_element_id,
rollup_source_type,
activity_context,
FND_GLOBAL.CONC_REQUEST_ID,
FND_GLOBAL.PROG_APPL_ID,
FND_GLOBAL.CONC_PROGRAM_ID,
sysdate,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM CST_ITEM_CST_DTLS_INTERFACE ct
WHERE ct.error_flag is null
and ct.group_id = SEQ_NEXTVAL;
fnd_file.put_line(fnd_file.log,'sucessfully completed inserting ' || to_char(SQL%ROWCOUNT)|| ' rows into cicd');
Update CST_ITEM_CST_DTLS_INTERFACE ct set
process_flag = 5
WHERE error_flag is null
AND group_id = SEQ_NEXTVAL
AND process_flag = 4;
delete from CST_ITEM_CST_DTLS_INTERFACE
WHERE error_flag is null
AND process_flag = 5
AND group_id = SEQ_NEXTVAL;
fnd_file.put_line(fnd_file.log,'CICD/CICDI table insert_cic_cicd(' || to_char(l_stmt_no) || '),' || to_char(SQLCODE) || ',' || substr(SQLERRM,1,180));
END insert_cic_cicd;
Update CST_ITEM_CST_DTLS_INTERFACE
SET group_id = i_grp_id
where process_flag = 1
AND error_flag is null;
Select count(*) into l_cicdi_count
from CST_ITEM_CST_DTLS_INTERFACE cicdi
WHERE cicdi.group_id = i_grp_id
AND cicdi.error_flag is null
AND cicdi.process_flag = 1
AND rownum = 1;
insert_cic_cicd(Err,i_grp_id,i_del_option,i_run_option);
select count(*) into i_count from CST_ITEM_CST_DTLS_INTERFACE
where group_id = i_grp_id
and error_flag = 'E';