The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW_MOH(
p_rule_id IN NUMBER,
p_last_update_date IN DATE,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_created_by IN NUMBER,
p_organization_id IN NUMBER,
p_earn_moh IN NUMBER,
p_transaction_type IN NUMBER,
p_selection_criteria IN NUMBER ,
p_category_id IN NUMBER ,
p_item_from IN NUMBER ,
p_item_to IN NUMBER ,
p_item_type IN NUMBER,
p_ship_from_org IN NUMBER ,
p_cost_type_id IN NUMBER,
err_code OUT NOCOPY NUMBER,
err_msg OUT NOCOPY VARCHAR2 ) IS
l_stmt_num NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row_MOH';
SAVEPOINT insert_row_moh;
INSERT INTO cst_material_ovhd_rules
(rule_id,
last_update_date,
creation_date,
last_updated_by,
created_by,
organization_id,
selection_criteria,
earn_moh,
item_type,
cost_type_id,
ship_from_org,
category_id,
item_from,
item_to,
transaction_type
)
VALUES
(p_rule_id,
p_last_update_date,
p_creation_date,
p_last_updated_by,
p_created_by,
p_organization_id,
p_selection_criteria,
p_earn_moh,
p_item_type,
p_cost_type_id,
p_ship_from_org,
p_category_id,
p_item_from,
p_item_to,
p_transaction_type
);
err_msg := 'CST_MOH_RULES_PUB.insert_row_moh(' ||l_stmt_num|| '): Error while inserting';
END insert_row_moh;
PROCEDURE update_row_moh(
p_rule_id IN NUMBER,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_earn_moh IN NUMBER,
p_transaction_type IN NUMBER,
p_selection_criteria IN NUMBER ,
p_category_id IN NUMBER ,
p_item_from IN NUMBER ,
p_item_to IN NUMBER ,
p_item_type IN NUMBER,
p_ship_from_org IN NUMBER ,
p_cost_type_id IN NUMBER,
err_code OUT NOCOPY NUMBER,
err_msg OUT NOCOPY VARCHAR2
) IS
BEGIN
err_code := 0;
UPDATE cst_material_ovhd_rules
SET last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
earn_moh = p_earn_moh,
selection_criteria = p_selection_criteria,
category_id = p_category_id,
item_from = p_item_from,
item_to = p_item_to ,
item_type = p_item_type,
ship_from_org = p_ship_from_org,
cost_type_id = p_cost_type_id
WHERE rule_id = p_rule_id;
END update_row_moh;
PROCEDURE delete_row_moh(
p_rule_id IN NUMBER,
err_code OUT NOCOPY NUMBER,
err_msg OUT NOCOPY VARCHAR2
) IS
BEGIN
err_code := 0;
DELETE FROM cst_material_ovhd_rules
WHERE rule_id = p_rule_id;
END delete_row_moh;
SELECT mmt.transaction_type_id, mmt.transaction_source_type_id, mmt.transaction_action_id
INTO l_txn_type_id, l_source_type_id, l_txn_action_id
FROM mtl_material_transactions mmt
WHERE transaction_id = p_txn_id;
SELECT planning_make_buy_code
INTO l_item_type
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT count(*)
INTO l_count
FROM cst_material_ovhd_rules
WHERE transaction_type = l_txn_type
AND organization_id = p_organization_id;
SELECT earn_moh, rule_id, count(*)
INTO p_earn_moh,l_rule_id, l_rule_count
FROM cst_material_ovhd_rules
WHERE transaction_type = l_txn_type
AND organization_id = p_organization_id
AND item_type = l_item_type
GROUP BY earn_moh, rule_id;
SELECT earn_moh, rule_id,count(*)
INTO p_earn_moh,l_rule_id,l_rule_count
FROM cst_material_ovhd_rules
WHERE transaction_type = l_txn_type
AND organization_id = p_organization_id
AND item_type = 3
GROUP BY earn_moh, rule_id;