The following lines contain the word 'select', 'insert', 'update' or 'delete':
I_LAST_UPDATED_BY IN NUMBER,
I_COST_TYPE_ID IN NUMBER,
I_ITEM_TYPE IN NUMBER,
I_LOT_SIZE IN NUMBER,
I_SHRINKAGE_RATE IN NUMBER,
O_RETURN_CODE OUT NOCOPY NUMBER,
O_RETURN_ERR OUT NOCOPY VARCHAR2) AS
p_def_matl_cost_code_id NUMBER;
SELECT NVL(process_enabled_flag, 'N')
INTO l_process_enabled_flag
FROM mtl_parameters
WHERE organization_id = i_organization_id;
/* Insert default material overhead */
p_location := 2;
I_LAST_UPDATED_BY,
I_COST_TYPE_ID,
I_ITEM_TYPE,
I_LOT_SIZE,
I_SHRINKAGE_RATE,
l_return_code,
l_return_err);
SELECT
MP.DEFAULT_MATERIAL_COST_ID
INTO
p_def_matl_cost_code_id
FROM mtl_parameters MP
WHERE MP.organization_id = I_ORGANIZATION_ID;
/* Insert into CICD only if no rows exist now. */
p_location := 5;
select count(*)
into p_dummy
from cst_item_cost_details
where inventory_item_id = I_INVENTORY_ITEM_ID
AND organization_id = I_ORGANIZATION_ID
AND cost_type_id = I_COST_TYPE_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,
LEVEL_TYPE,
RESOURCE_ID,
USAGE_RATE_OR_AMOUNT,
BASIS_TYPE,
BASIS_FACTOR,
NET_YIELD_OR_SHRINKAGE_FACTOR,
ITEM_COST,
COST_ELEMENT_ID,
ROLLUP_SOURCE_TYPE)
VALUES (I_INVENTORY_ITEM_ID,
I_ORGANIZATION_ID,
I_COST_TYPE_ID,
sysdate,
I_LAST_UPDATED_BY,
sysdate,
I_LAST_UPDATED_BY,
1, /* TL */
p_def_matl_cost_code_id,
0,
1, /* item basis */
1,
DECODE(NVL(I_SHRINKAGE_RATE,-9),-9,1,1,0,
1 / (1 - I_SHRINKAGE_RATE)),
0,
1,
1);
SELECT AVG_RATES_COST_TYPE_ID
INTO l_avg_rates_cost_type_id
FROM mtl_parameters
WHERE organization_id = I_ORGANIZATION_ID;
I_LAST_UPDATED_BY,
l_avg_rates_cost_type_id,
I_ITEM_TYPE,
I_LOT_SIZE,
I_SHRINKAGE_RATE,
l_return_code,
l_return_err);