[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select material_sub_elem,
material_oh_sub_elem,
organization_id,
transaction_id
into l_material_sub_elem,
l_material_oh_sub_elem,
l_organization_id,
l_transaction_id
from mtl_system_items_interface
where transaction_id = I_TRANSID; */
select primary_cost_method,
cost_organization_id
into l_cost_type_id,
l_cost_organization_id
from mtl_parameters
where organization_id = l_organization_id;
select resource_id
into l_material_sub_elem_id
from bom_resources br
where br.resource_code = l_material_sub_elem
and br.organization_id = l_cost_organization_id
and br.cost_code_type = 1;
select resource_id
into l_material_oh_sub_elem_id
from bom_resources br
where br.resource_code = l_material_oh_sub_elem
and br.organization_id = l_cost_organization_id
and br.cost_code_type = 2;
/* Update mtl_system_items_interface
** NP 06MAY96: Does not require the xset_id since transaction_id
** is unique to each row in MSII even though there may be multiple
** parallel IOI processes
*/
l_location := 130;
update mtl_system_items_interface
set material_sub_elem_id = decode(l_material_sub_elem_id,
0,NULL,l_material_sub_elem_id),
material_oh_sub_elem_id = decode(l_material_oh_sub_elem_id,
0,NULL,l_material_oh_sub_elem_id)
where transaction_id = I_TRANSID;
/* Insert material and material overhead cost into */
/* cst_item_cost_details */
/* */
/* */
/* Note : */
/* The basis type will default to the following if one is not define */
/* by the user in the bom_resources table. */
/* I_COST_ELEMENT_ID : 1 Material item basis */
/* 2 Material Overhead total value basis */
/* For average costing, there is no material overhead concept */
/* Assume that the record does not exist yet */
/* */
/*======================================================================*/
PROCEDURE CSTPIICD (
I_ORGANIZATION_ID IN NUMBER,
I_INVENTORY_ITEM_ID IN NUMBER,
I_COST_ELEMENT_ID IN NUMBER,
I_COST_RATE IN NUMBER,
I_RESOURCE_ID IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRGM_ID IN NUMBER,
I_PRGM_APPL_ID IN NUMBER,
O_RETURN_CODE OUT NOCOPY NUMBER,
O_ERR_TEXT IN OUT NOCOPY VARCHAR2) IS
l_basis_type NUMBER;
cost_update NUMBER;
select nvl(default_basis_type,decode(i_cost_element_id,1,1,2,5,0))
into l_basis_type
from bom_resources
where resource_id = i_resource_id;
/* Added delete st for bug 3219632 */
delete from cst_item_cost_details cicd
where cicd.ORGANIZATION_ID = I_ORGANIZATION_ID
AND cicd.INVENTORY_ITEM_ID = I_INVENTORY_ITEM_ID
AND cicd.COST_ELEMENT_ID = I_COST_ELEMENT_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
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15)
select i_inventory_item_id, -- inventory_item_id
i_organization_id, -- organization_id
mp.primary_cost_method, -- cost_type_id
sysdate, -- last_update_date
I_USER_ID, -- last_updated_by
sysdate, -- creation_date
I_USER_ID, -- created_by
I_LOGIN_ID, -- last_update_login
NULL, -- operation_sequence_id
NULL, -- operation_seq_num
NULL, -- department_id
1, -- level_type
NULL, -- activity_id
NULL, -- resource_seq_num
I_RESOURCE_ID, -- resource_id
NULL, -- resource_rate
NULL, -- item_units
NULL, -- activity_units
nvl(I_COST_RATE,0), -- usage_rate_or_amount
l_basis_type, -- basis_type
NULL, -- basis_resource_id
1, -- basis_factor
1, -- net_yield_or_shrinkage_factor
decode(l_basis_type,
5,0,nvl(I_COST_RATE,0)), -- item_cost
I_COST_ELEMENT_ID, -- cost_element_id
1, -- rollup_source_type
NULL, -- activity_context
I_REQ_ID, -- request_id
I_PRGM_APPL_ID, -- program_application_id
I_PRGM_ID, -- program_id
NULL, -- program_update_date
NULL, -- attribute_category
NULL, -- attribute1
NULL, -- attribute2
NULL, -- attribute3
NULL, -- attribute4
NULL, -- attribute5
NULL, -- attribute6
NULL, -- attribute7
NULL, -- attribute8
NULL, -- attribute9
NULL, -- attribute10
NULL, -- attribute11
NULL, -- attribute12
NULL, -- attribute13
NULL, -- attribute14
NULL -- attribute15
from mtl_parameters mp
where mp.organization_id = i_organization_id;
Select cst_lists_s.nextval
INTO cost_update
From DUAL;
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 cost_update, I_ORGANIZATION_ID,
I_INVENTORY_ITEM_ID,
SYSDATE, I_USER_ID,
SYSDATE, I_USER_ID, I_LOGIN_ID,
SYSDATE, NVL(SUM(cicd.item_cost),0)
FROM cst_item_cost_details cicd, mtl_parameters mp
WHERE cicd.ORGANIZATION_ID = I_ORGANIZATION_ID
AND cicd.INVENTORY_ITEM_ID = I_INVENTORY_ITEM_ID
AND cicd.COST_TYPE_ID = mp.primary_cost_method
AND mp.ORGANIZATION_ID = I_ORGANIZATION_ID;
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 cost_update, I_ORGANIZATION_ID,
I_INVENTORY_ITEM_ID, cicd.cost_element_id,
SYSDATE, I_USER_ID,
SYSDATE, I_USER_ID, I_LOGIN_ID,
NVL(SUM(cicd.item_cost),0)
FROM cst_item_cost_details cicd, mtl_parameters mp
WHERE cicd.ORGANIZATION_ID = I_ORGANIZATION_ID
AND cicd.INVENTORY_ITEM_ID = I_INVENTORY_ITEM_ID
AND cicd.COST_TYPE_ID = mp.primary_cost_method
AND mp.ORGANIZATION_ID = I_ORGANIZATION_ID
GROUP BY cost_element_id;
select primary_cost_method
into l_cost_type_id
from mtl_parameters
where organization_id = I_ORGANIZATION_ID;
select msii.transaction_id,
msii.inventory_item_id,
msii.material_cost,
msii.material_sub_elem,
msii.material_sub_elem_id,
msii.material_oh_rate,
msii.material_oh_sub_elem,
msii.material_oh_sub_elem_id,
mp.primary_cost_method,
mp.organization_id,
mp.master_organization_id,
mp.cost_organization_id
from mtl_system_items_interface msii,
mtl_parameters mp
where msii.process_flag = 4
and msii.transaction_type = 'CREATE'
and msii.costing_enabled_flag = 'Y'
and msii.organization_id = mp.organization_id
and mp.organization_id = mp.cost_organization_id
and msii.set_process_id = xset_id
order by mp.organization_id;
select msii.transaction_id,
msii.inventory_item_id,
msii.material_cost,
msii.material_sub_elem,
msii.material_sub_elem_id,
msii.material_oh_rate,
msii.material_oh_sub_elem,
msii.material_oh_sub_elem_id,
mp.primary_cost_method,
mp.organization_id,
mp.master_organization_id,
mp.cost_organization_id
from mtl_system_items_interface msii,
mtl_parameters mp
where msii.process_flag = 4
and msii.transaction_type = 'CREATE'
and msii.costing_enabled_flag = 'Y'
and msii.organization_id = mp.organization_id
and msii.set_process_id = xset_id
and mp.organization_id <> mp.cost_organization_id
and mp.cost_organization_id = mp.master_organization_id
order by mp.organization_id;
select msii.transaction_id,
msii.inventory_item_id,
msii.material_cost,
msii.material_sub_elem,
msii.material_sub_elem_id,
msii.material_oh_rate,
msii.material_oh_sub_elem,
msii.material_oh_sub_elem_id,
mp.primary_cost_method,
mp.organization_id,
mp.master_organization_id,
mp.cost_organization_id
from mtl_system_items_interface msii,
mtl_parameters mp
where msii.process_flag = 4
and msii.transaction_type = 'CREATE'
and msii.costing_enabled_flag = 'Y'
and msii.organization_id = mp.organization_id
and msii.set_process_id = xset_id
and mp.organization_id = mp.cost_organization_id
and mp.organization_id <> mp.master_organization_id
order by mp.organization_id;
select cst_lists_s.nextval
into l_list_id
from dual;
delete from cst_lists
where list_id = l_list_id;
insert into cst_lists (LIST_ID,ENTITY_ID)
values (l_list_id,cc1.inventory_item_id);
delete from cst_lists
where list_id = l_list_id;
select count(*)
into l_cst_exist
from cst_item_costs
where inventory_item_id = cc2.inventory_item_id
and organization_id = cc2.cost_organization_id
and cost_type_id = cc2.primary_cost_method;
delete from cst_lists
where list_id = l_list_id;
insert into cst_lists (LIST_ID,ENTITY_ID)
values (l_list_id,cc2.inventory_item_id);
select count(*)
into New_List_ct
from cst_lists
where list_id = l_list_id
and rownum =1;
delete from cst_lists
where list_id = l_list_id;
select count(*)
into l_cst_exist
from cst_item_costs
where inventory_item_id = cc3.inventory_item_id
and organization_id = cc3.master_organization_id
and cost_type_id = cc3.primary_cost_method;
delete from cst_lists
where list_id = l_list_id;
insert into cst_lists (LIST_ID,ENTITY_ID)
values (l_list_id,cc3.inventory_item_id);
select count(*)
into New_List_ct
from cst_lists
where list_id = l_list_id
and rownum =1;
delete from cst_lists
where list_id = l_list_id;