[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select organization_id,
subinventory_code
into l_org_id,
l_subinv
from mtl_material_transactions
where transaction_id = i_txn_id;
select decode(inventory_asset_flag,'Y', 0, 1)
into l_exp_item_flag
from mtl_system_items
where inventory_item_id = i_inv_item_id
and organization_id = l_org_id;
select decode(asset_inventory, 1, 0, 1)
into l_exp_sub_flag
from mtl_secondary_inventories
where secondary_inventory_name = l_subinv
and organization_id = l_org_id;
select cst_wip_layers_s.nextval
into l_wip_layer_id
from dual;
insert into cst_wip_layers
(
wip_layer_id,
wip_entity_id,
operation_seq_num,
inventory_item_id,
repetitive_schedule_id,
inv_layer_id,
inv_layer_date,
create_txn_id,
applied_matl_qty,
relieved_matl_comp_qty,
relieved_matl_scrap_qty,
relieved_matl_final_comp_qty,
temp_relieved_qty,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
l_wip_layer_id, -- wip_layer_id
i_wip_entity_id, -- wip_entity_id
i_op_seq_num, -- operation_seq_num
CIL.inventory_item_id, -- inventory_item_id
null, -- repetitive_schedule_id
decode(l_exp_sub_flag, 1, -1, CIL.inv_layer_id),
-- inv_layer_id
CIL.creation_date, -- inv_layer_date
i_txn_id, -- create_txn_id
NVL( i_layer_qty_table(i).layer_qty, 0 ),
-- applied_matl_qty
0, -- relieved_matl_comp_qty
0, -- relieved_matl_scrap_qty
0, -- relieved_matl_final_comp_qty
0, -- temp_relieved_qty
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_inv_layers CIL
where
CIL.inv_layer_id = i_layer_qty_table(i).layer_id and
CIL.inventory_item_id = i_inv_item_id;
insert into cst_wip_layer_cost_details
(
wip_layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_cost,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
l_wip_layer_id, -- wip_layer_id
decode(l_exp_sub_flag, 1, -1, CILCD.inv_layer_id),
-- inv_layer_id
CILCD.cost_element_id, -- cost_element_id
CILCD.level_type, -- level_type
sum(decode(l_zero_cost_flag, 1, 0, CILCD.layer_cost)),
-- layer_cost
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
cst_inv_layer_cost_details CILCD
where
CILCD.inv_layer_id = i_layer_qty_table(i).layer_id
group by
CILCD.inv_layer_id,
CILCD.cost_element_id,
CILCD.level_type;
insert into cst_wip_layers
(
wip_layer_id,
wip_entity_id,
operation_seq_num,
inventory_item_id,
repetitive_schedule_id,
inv_layer_id,
inv_layer_date,
create_txn_id,
applied_matl_qty,
relieved_matl_comp_qty,
relieved_matl_scrap_qty,
relieved_matl_final_comp_qty,
temp_relieved_qty,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
l_wip_layer_id, -- wip_layer_id
i_wip_entity_id, -- wip_entity_id
i_op_seq_num, -- operation_seq_num
i_inv_item_id, -- inventory_item_id
null, -- repetitive_schedule_id
-1, -- inv_layer_id
sysdate, -- inv_layer_date
i_txn_id, -- create_txn_id
NVL( i_layer_qty_table(i).layer_qty, 0 ),
-- applied_matl_qty
0, -- relieved_matl_comp_qty
0, -- relieved_matl_scrap_qty
0, -- relieved_matl_final_comp_qty
0, -- temp_relieved_qty
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
dual;
insert into cst_wip_layer_cost_details
(
wip_layer_id,
inv_layer_id,
cost_element_id,
level_type,
layer_cost,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
select
l_wip_layer_id, -- wip_layer_id
-1, -- inv_layer_id
1, -- cost_element_id
1, -- level_type
0, -- layer_cost
sysdate, -- LAST_UPDATE_DATE
i_user_id, -- LAST_UPDATED_BY
sysdate, -- CREATION_DATE
i_user_id, -- CREATED_BY
i_login_id, -- LAST_UPDATE_LOGIN
i_request_id, -- REQUEST_ID
i_prog_appl_id, -- PROGRAM_APPLICATION_ID
i_prog_id, -- PROGRAM_ID
sysdate -- PROGRAM_UPDATE_DATE
from
dual;
' select * ' ||
' from cst_wip_layers CWL ' ||
' where ' ||
' CWL.wip_entity_id = :wip_entity_id and ' ||
' CWL.operation_seq_num = :op_seq_num and ' ||
' CWL.inventory_item_id = :inv_item_id ' || l_optional_and ||
i_where_clause ||
' order by ' ||
l_sql_order_by;
select mmt.inventory_item_id,
mmt.organization_id
into l_item_id,
l_org_id
from mtl_material_transactions mmt
where mmt.transaction_id = i_txn_id;
select decode(inventory_asset_flag,'Y', 0, 1)
into l_exp_item_flag
from mtl_system_items
where inventory_item_id = l_item_id
and organization_id = l_org_id;
update wip_req_operation_cost_details
set temp_relieved_value = 0
where wip_entity_id = i_wip_entity_id and
operation_seq_num = i_op_seq_num and
inventory_item_id = i_inv_item_id;
INSERT INTO WIP_REQ_OPERATION_COST_DETAILS WROCD
(
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
APPLIED_MATL_VALUE,
RELIEVED_MATL_COMPLETION_VALUE,
RELIEVED_MATL_SCRAP_VALUE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
i_wip_entity_id, -- WIP_ENTITY_ID,
i_op_seq_num, -- OPERATION_SEQ_NUM,
i_org_id, -- ORGANIZATION_ID,
i_inv_item_id, -- INVENTORY_ITEM_ID,
CCE.cost_element_id, -- COST_ELEMENT_ID,
0, -- APPLIED_MATL_VALUE,
0, -- RELIEVED_MATL_COMPLETION_VALUE,
0, -- RELIEVED_MATL_SCRAP_VALUE,
i_user_id, -- LAST_UPDATED_BY,
sysdate, -- LAST_UPDATE_DATE,
sysdate, -- CREATION_DATE,
i_user_id, -- CREATED_BY,
i_login_id, -- LAST_UPDATE_LOGIN,
i_request_id, -- REQUEST_ID,
i_prog_appl_id, -- PROGRAM_APPLICATION_ID,
i_prog_id, -- PROGRAM_ID,
sysdate -- PROGRAM_UPDATE_DATE
from
cst_cost_elements CCE
where
NOT EXISTS
(
SELECT 'X'
FROM WIP_REQ_OPERATION_COST_DETAILS WROCD2
WHERE
WROCD2.WIP_ENTITY_ID = i_wip_entity_id AND
WROCD2.OPERATION_SEQ_NUM = i_op_seq_num AND
WROCD2.INVENTORY_ITEM_ID = i_inv_item_id AND
WROCD2.COST_ELEMENT_ID = CCE.cost_element_id
) AND
EXISTS
(
select 'x'
from wip_requirement_operations WRO
where WRO.wip_entity_id = i_wip_entity_id and
WRO.operation_seq_num = i_op_seq_num and
WRO.inventory_item_id = i_inv_item_id and
WRO.wip_supply_type not in (4, 5, 6)
)
group by
CCE.cost_element_id;
update cst_wip_layers CWL
set temp_relieved_qty = 0
where
CWL.wip_entity_id = i_wip_entity_id and
CWL.operation_seq_num = i_op_seq_num and
CWL.inventory_item_id = i_inv_item_id;
SELECT cost_group_id
INTO l_cost_group_id
FROM cst_quantity_layers
WHERE layer_id = i_layer_id;
SELECT NVL(MIN(CQL.layer_id),-1)
INTO l_item_layer_id
FROM cst_quantity_layers CQL
WHERE CQL.inventory_item_id = i_inv_item_id
AND CQL.organization_id = i_org_id
AND CQL.cost_group_id = l_cost_group_id;
UPDATE WIP_REQ_OPERATION_COST_DETAILS
SET temp_relieved_value = 0
where WIP_ENTITY_ID = i_wip_entity_id;
UPDATE WIP_OPERATION_RESOURCES
SET temp_relieved_value = 0
where WIP_ENTITY_ID = i_wip_entity_id;
UPDATE WIP_OPERATION_OVERHEADS
SET temp_relieved_value = 0
where WIP_ENTITY_ID = i_wip_entity_id;
update cst_wip_layers
set temp_relieved_qty = 0
where wip_entity_id = i_wip_entity_id;