[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
IF p_act_metric_fact_rec.trans_forecasted_value IS NULL THEN
x_complete_rec.trans_forecasted_value := 0;
x_fact_rec.last_update_date := fnd_api.g_miss_date;
x_fact_rec.last_updated_by := fnd_api.g_miss_num;
x_fact_rec.last_update_login := fnd_api.g_miss_num;
SELECT fact.hierarchy_id, fact.node_id, fact.level_depth, fact.fact_value
,formula.formula_id, formula.formula_type
FROM ams_act_metric_facts_all fact, ams_act_metric_formulas formula
WHERE fact.activity_metric_id = p_act_metric_id
AND fact.formula_id = formula.formula_id
ORDER BY fact.level_depth, fact.node_id, formula.formula_type asc;
SELECT func_actual_value
FROM ams_act_metrics_all
WHERE activity_metric_id = p_act_metric_id;
SELECT node_value
FROM ams_terr_v
WHERE hierarchy_id = p_hierarchy_id
AND node_id = p_node_id;
SELECT NVL(SUM(fact_value), 0)
FROM ams_act_metric_facts_all fact
WHERE activity_metric_id = p_act_metric_id
AND hierarchy_id = p_hierarchy_id
AND EXISTS
(SELECT 1 FROM ams_act_metric_formulas formula
WHERE formula.formula_id = fact.formula_id
AND formula.formula_type = 'ALLOCATION'
AND formula.level_depth= p_level_depth + 1
AND formula.parent_formula_id = p_formula_id
)
AND EXISTS
(SELECT 1 FROM ams_terr_v terr
WHERE terr.hierarchy_id = p_hierarchy_id
AND terr.parent_id = p_node_id
AND terr.node_id = fact.node_id);
SELECT count(1)
FROM ams_act_metric_facts_all
WHERE activity_metric_fact_id = l_act_metric_fact_id;
SELECT ams_act_metric_facts_all_s.NEXTVAL
FROM dual;
AMS_Utility_PVT.debug_message(l_full_name ||': insert');
Insert into ams_act_metric_facts_all (
activity_metric_fact_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
act_metric_used_by_id,
arc_act_metric_used_by,
value_type ,
activity_metric_id ,
activity_geo_area_id ,
activity_product_id ,
transaction_currency_code,
trans_forecasted_value ,
base_quantity ,
functional_currency_code ,
func_forecasted_value ,
org_id ,
de_metric_id ,
de_geographic_area_id ,
de_geographic_area_type ,
de_inventory_item_id ,
de_inventory_item_org_id ,
time_id1 ,
time_id2 ,
time_id3 ,
time_id4 ,
time_id5 ,
time_id6 ,
time_id7 ,
time_id8 ,
time_id9 ,
time_id10 ,
time_id11 ,
time_id12 ,
time_id13 ,
time_id14 ,
time_id15 ,
time_id16 ,
time_id17 ,
time_id18 ,
time_id19 ,
time_id20 ,
time_id21 ,
time_id22 ,
time_id23 ,
time_id24 ,
time_id25 ,
time_id26 ,
time_id27 ,
time_id28 ,
time_id29 ,
time_id30 ,
time_id31 ,
time_id32 ,
time_id33 ,
time_id34 ,
time_id35 ,
time_id36 ,
time_id37 ,
time_id38 ,
time_id39 ,
time_id40 ,
time_id41 ,
time_id42 ,
time_id43 ,
time_id44 ,
time_id45 ,
time_id46 ,
time_id47 ,
time_id48 ,
time_id49 ,
time_id50 ,
time_id51 ,
time_id52 ,
time_id53 ,
hierarchy_id ,
node_id ,
level_depth ,
formula_id ,
from_date ,
to_date ,
fact_value ,
fact_percent ,
root_fact_id ,
previous_fact_id ,
fact_type ,
fact_reference ,
forward_buy_quantity ,
/* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
status_code ,
hierarchy_type ,
approval_date ,
recommend_total_amount ,
recommend_hb_amount ,
request_total_amount ,
request_hb_amount ,
actual_total_amount ,
actual_hb_amount ,
base_total_pct ,
base_hb_pct
/* 05/21/2002 yzhao: add ends */
)
VALUES ( l_act_metric_fact_rec.activity_metric_fact_id,
SYSDATE,
FND_GLOBAL.User_ID,
SYSDATE,
FND_GLOBAL.User_ID,
FND_GLOBAL.Conc_Login_ID,
1, --OBJECT_VERSION_NUMBER
l_act_metric_fact_rec.act_metric_used_by_id,
l_act_metric_fact_rec.arc_act_metric_used_by,
l_act_metric_fact_rec.value_type ,
l_act_metric_fact_rec.activity_metric_id ,
l_act_metric_fact_rec.activity_geo_area_id ,
l_act_metric_fact_rec.activity_product_id ,
l_act_metric_fact_rec.transaction_currency_code,
l_act_metric_fact_rec.trans_forecasted_value ,
l_act_metric_fact_rec.base_quantity ,
l_act_metric_fact_rec.functional_currency_code ,
l_act_metric_fact_rec.func_forecasted_value ,
TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) , -- org_id
l_act_metric_fact_rec.de_metric_id ,
l_act_metric_fact_rec.de_geographic_area_id ,
l_act_metric_fact_rec.de_geographic_area_type ,
l_act_metric_fact_rec.de_inventory_item_id ,
l_act_metric_fact_rec.de_inventory_item_org_id ,
l_act_metric_fact_rec.time_id1 ,
l_act_metric_fact_rec.time_id2 ,
l_act_metric_fact_rec.time_id3 ,
l_act_metric_fact_rec.time_id4 ,
l_act_metric_fact_rec.time_id5 ,
l_act_metric_fact_rec.time_id6 ,
l_act_metric_fact_rec.time_id7 ,
l_act_metric_fact_rec.time_id8 ,
l_act_metric_fact_rec.time_id9 ,
l_act_metric_fact_rec.time_id10 ,
l_act_metric_fact_rec.time_id11 ,
l_act_metric_fact_rec.time_id12 ,
l_act_metric_fact_rec.time_id13 ,
l_act_metric_fact_rec.time_id14 ,
l_act_metric_fact_rec.time_id15 ,
l_act_metric_fact_rec.time_id16 ,
l_act_metric_fact_rec.time_id17 ,
l_act_metric_fact_rec.time_id18 ,
l_act_metric_fact_rec.time_id19 ,
l_act_metric_fact_rec.time_id20 ,
l_act_metric_fact_rec.time_id21 ,
l_act_metric_fact_rec.time_id22 ,
l_act_metric_fact_rec.time_id23 ,
l_act_metric_fact_rec.time_id24 ,
l_act_metric_fact_rec.time_id25 ,
l_act_metric_fact_rec.time_id26 ,
l_act_metric_fact_rec.time_id27 ,
l_act_metric_fact_rec.time_id28 ,
l_act_metric_fact_rec.time_id29 ,
l_act_metric_fact_rec.time_id30 ,
l_act_metric_fact_rec.time_id31 ,
l_act_metric_fact_rec.time_id32 ,
l_act_metric_fact_rec.time_id33 ,
l_act_metric_fact_rec.time_id34 ,
l_act_metric_fact_rec.time_id35 ,
l_act_metric_fact_rec.time_id36 ,
l_act_metric_fact_rec.time_id37 ,
l_act_metric_fact_rec.time_id38 ,
l_act_metric_fact_rec.time_id39 ,
l_act_metric_fact_rec.time_id40 ,
l_act_metric_fact_rec.time_id41 ,
l_act_metric_fact_rec.time_id42 ,
l_act_metric_fact_rec.time_id43 ,
l_act_metric_fact_rec.time_id44 ,
l_act_metric_fact_rec.time_id45 ,
l_act_metric_fact_rec.time_id46 ,
l_act_metric_fact_rec.time_id47 ,
l_act_metric_fact_rec.time_id48 ,
l_act_metric_fact_rec.time_id49 ,
l_act_metric_fact_rec.time_id50 ,
l_act_metric_fact_rec.time_id51 ,
l_act_metric_fact_rec.time_id52 ,
l_act_metric_fact_rec.time_id53 ,
l_act_metric_fact_rec.hierarchy_id ,
l_act_metric_fact_rec.node_id ,
l_act_metric_fact_rec.level_depth ,
l_act_metric_fact_rec.formula_id ,
l_act_metric_fact_rec.from_date ,
l_act_metric_fact_rec.to_date ,
l_act_metric_fact_rec.fact_value ,
l_act_metric_fact_rec.fact_percent ,
l_act_metric_fact_rec.root_fact_id ,
l_act_metric_fact_rec.previous_fact_id ,
l_act_metric_fact_rec.fact_type ,
l_act_metric_fact_rec.fact_reference ,
l_act_metric_fact_rec.forward_buy_quantity ,
/* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
l_act_metric_fact_rec.status_code ,
l_act_metric_fact_rec.hierarchy_type ,
l_act_metric_fact_rec.approval_date ,
l_act_metric_fact_rec.recommend_total_amount ,
l_act_metric_fact_rec.recommend_hb_amount ,
l_act_metric_fact_rec.request_total_amount ,
l_act_metric_fact_rec.request_hb_amount ,
l_act_metric_fact_rec.actual_total_amount ,
l_act_metric_fact_rec.actual_hb_amount ,
l_act_metric_fact_rec.base_total_pct ,
l_act_metric_fact_rec.base_hb_pct
/* 05/21/2002 yzhao: add ends */
);
PROCEDURE Update_ActMetricFact (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_act_metric_fact_rec IN act_metric_fact_rec_type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTMETRICFACT';
SAVEPOINT Update_ActMetricFact_Pvt;
p_validation_mode => JTF_PLSQL_API.G_UPDATE,
x_complete_rec => l_act_metric_fact_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data ) ;
p_validation_mode => JTF_PLSQL_API.g_update,
x_return_status => l_return_status
);
AMS_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Facts Table');
Update ams_act_metric_facts_all Set
object_version_number = object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = NVL(fnd_global.user_id, -1),
last_update_login = NVL(fnd_global.conc_login_id, -1),
act_metric_used_by_id = l_act_metric_fact_rec.act_metric_used_by_id,
arc_act_metric_used_by = l_act_metric_fact_rec.arc_act_metric_used_by,
value_type = l_act_metric_fact_rec.value_type,
activity_metric_id = l_act_metric_fact_rec.activity_metric_id,
activity_geo_area_id = l_act_metric_fact_rec.activity_geo_area_id,
activity_product_id = l_act_metric_fact_rec.activity_product_id,
transaction_currency_code = l_act_metric_fact_rec.transaction_currency_code,
trans_forecasted_value = l_act_metric_fact_rec.trans_forecasted_value,
base_quantity = l_act_metric_fact_rec.base_quantity,
functional_currency_code = l_act_metric_fact_rec.functional_currency_code,
func_forecasted_value = l_act_metric_fact_rec.func_forecasted_value,
org_id = l_act_metric_fact_rec.org_id,
de_metric_id = l_act_metric_fact_rec.de_metric_id,
de_geographic_area_id = l_act_metric_fact_rec.de_geographic_area_id,
de_geographic_area_type = l_act_metric_fact_rec.de_geographic_area_type,
de_inventory_item_id = l_act_metric_fact_rec.de_inventory_item_id,
de_inventory_item_org_id = l_act_metric_fact_rec.de_inventory_item_org_id,
time_id1 = l_act_metric_fact_rec.time_id1,
time_id2 = l_act_metric_fact_rec.time_id2,
time_id3 = l_act_metric_fact_rec.time_id3,
time_id4 = l_act_metric_fact_rec.time_id4,
time_id5 = l_act_metric_fact_rec.time_id5,
time_id6 = l_act_metric_fact_rec.time_id6,
time_id7 = l_act_metric_fact_rec.time_id7,
time_id8 = l_act_metric_fact_rec.time_id8,
time_id9 = l_act_metric_fact_rec.time_id9,
time_id10 = l_act_metric_fact_rec.time_id10,
time_id11 = l_act_metric_fact_rec.time_id11,
time_id12 = l_act_metric_fact_rec.time_id12,
time_id13 = l_act_metric_fact_rec.time_id13,
time_id14 = l_act_metric_fact_rec.time_id14,
time_id15 = l_act_metric_fact_rec.time_id15,
time_id16 = l_act_metric_fact_rec.time_id16,
time_id17 = l_act_metric_fact_rec.time_id17,
time_id18 = l_act_metric_fact_rec.time_id18,
time_id19 = l_act_metric_fact_rec.time_id19,
time_id20 = l_act_metric_fact_rec.time_id20,
time_id21 = l_act_metric_fact_rec.time_id21,
time_id22 = l_act_metric_fact_rec.time_id22,
time_id23 = l_act_metric_fact_rec.time_id23,
time_id24 = l_act_metric_fact_rec.time_id24,
time_id25 = l_act_metric_fact_rec.time_id25,
time_id26 = l_act_metric_fact_rec.time_id26,
time_id27 = l_act_metric_fact_rec.time_id27,
time_id28 = l_act_metric_fact_rec.time_id28,
time_id29 = l_act_metric_fact_rec.time_id29,
time_id30 = l_act_metric_fact_rec.time_id30,
time_id31 = l_act_metric_fact_rec.time_id31,
time_id32 = l_act_metric_fact_rec.time_id32,
time_id33 = l_act_metric_fact_rec.time_id33,
time_id34 = l_act_metric_fact_rec.time_id34,
time_id35 = l_act_metric_fact_rec.time_id35,
time_id36 = l_act_metric_fact_rec.time_id36,
time_id37 = l_act_metric_fact_rec.time_id37,
time_id38 = l_act_metric_fact_rec.time_id38,
time_id39 = l_act_metric_fact_rec.time_id39,
time_id40 = l_act_metric_fact_rec.time_id40,
time_id41 = l_act_metric_fact_rec.time_id41,
time_id42 = l_act_metric_fact_rec.time_id42,
time_id43 = l_act_metric_fact_rec.time_id43,
time_id44 = l_act_metric_fact_rec.time_id44,
time_id45 = l_act_metric_fact_rec.time_id45,
time_id46 = l_act_metric_fact_rec.time_id46,
time_id47 = l_act_metric_fact_rec.time_id47,
time_id48 = l_act_metric_fact_rec.time_id48,
time_id49 = l_act_metric_fact_rec.time_id49,
time_id50 = l_act_metric_fact_rec.time_id50,
time_id51 = l_act_metric_fact_rec.time_id51,
time_id52 = l_act_metric_fact_rec.time_id52,
time_id53 = l_act_metric_fact_rec.time_id53,
hierarchy_id = l_act_metric_fact_rec.hierarchy_id,
node_id = l_act_metric_fact_rec.node_id,
level_depth = l_act_metric_fact_rec.level_depth,
formula_id = l_act_metric_fact_rec.formula_id,
from_date = l_act_metric_fact_rec.from_date,
to_date = l_act_metric_fact_rec.to_date,
fact_value = l_act_metric_fact_rec.fact_value,
fact_percent = l_act_metric_fact_rec.fact_percent,
root_fact_id = l_act_metric_fact_rec.root_fact_id,
previous_fact_id = l_act_metric_fact_rec.previous_fact_id,
fact_type = l_act_metric_fact_rec.fact_type,
fact_reference = l_act_metric_fact_rec.fact_reference,
forward_buy_quantity = l_act_metric_fact_rec.forward_buy_quantity,
/* 05/21/2002 yzhao: add 11 new columns for top-down bottom-up budgeting */
status_code = l_act_metric_fact_rec.status_code,
hierarchy_type = l_act_metric_fact_rec.hierarchy_type,
approval_date = l_act_metric_fact_rec.approval_date,
recommend_total_amount = l_act_metric_fact_rec.recommend_total_amount,
recommend_hb_amount = l_act_metric_fact_rec.recommend_hb_amount,
request_total_amount = l_act_metric_fact_rec.request_total_amount,
request_hb_amount = l_act_metric_fact_rec.request_hb_amount,
actual_total_amount = l_act_metric_fact_rec.actual_total_amount,
actual_hb_amount = l_act_metric_fact_rec.actual_hb_amount,
base_total_pct = l_act_metric_fact_rec.base_total_pct,
base_hb_pct = l_act_metric_fact_rec.base_hb_pct
/* 05/21/2002 yzhao: add ends */
Where activity_metric_fact_id = l_act_metric_fact_rec.activity_metric_fact_id;
ROLLBACK TO Update_ActMetricFact_pvt;
ROLLBACK TO Update_ActMetricFact_pvt;
ROLLBACK TO Update_ActMetricFact_pvt;
END Update_ActMetricFact;
SELECT activity_metric_fact_id
FROM ams_act_metric_facts_all
WHERE activity_metric_fact_id = p_activity_metric_fact_id
AND object_version_number = p_object_version_number
FOR UPDATE OF activity_metric_fact_id NOWAIT;
SELECT *
FROM ams_act_metric_facts_all
WHERE activity_metric_fact_id = p_act_metric_fact_rec.activity_metric_fact_id;
PROCEDURE Delete_ActMetricFact (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_activity_metric_fact_id IN NUMBER,
p_activity_metric_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ACTMETRICFACT';
SAVEPOINT Delete_ActMetricFact_pvt;
AMS_Utility_PVT.debug_message(l_full_name ||': delete with Validation');
DELETE FROM ams_act_metric_facts_all
WHERE activity_metric_id = p_activity_metric_id;
DELETE FROM ams_act_metric_facts_all
WHERE activity_metric_fact_id = p_activity_metric_fact_id
AND object_version_number = p_object_version_number;
ROLLBACK TO Delete_ActMetricFact_pvt;
ROLLBACK TO Delete_ActMetricFact_pvt;
ROLLBACK TO Delete_ActMetricFact_pvt;
END Delete_ActMetricFact;
SELECT 1 from ams_act_metric_facts_all
WHERE hierarchy_id = p_hierarchy_id
AND node_id = p_node_id
AND formula_id = p_formula_id
AND activity_metric_fact_id <> p_act_metric_fact_id
AND activity_metric_id = p_act_metric_id;