[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_actforecast_rec.last_update_date := FND_API.g_miss_date;
x_actforecast_rec.last_updated_by := FND_API.g_miss_num;
x_actforecast_rec.last_update_login := FND_API.g_miss_num;
x_actforecast_rec.program_update_date := FND_API.g_miss_date;
IF ((p_validation_mode = JTF_PLSQL_API.g_create) OR (p_validation_mode = JTF_PLSQL_API.g_update)) THEN
IF p_act_forecast_rec.forecast_calendar IS NULL THEN
x_complete_rec.forecast_calendar := 'NONE';
SELECT count(*)
FROM ozf_act_forecasts_all
WHERE forecast_id = l_forecast_id;
SELECT ozf_act_forecasts_all_s.NEXTVAL
FROM dual;
OZF_Utility_PVT.debug_message(l_full_name ||': insert');
INSERT INTO ozf_act_forecasts_all
(forecast_id
,forecast_type
,arc_act_fcast_used_by
,act_fcast_used_by_id
,creation_date
,created_from
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,object_version_number
,hierarchy
,hierarchy_level
,level_value
,forecast_calendar
,period_level
,forecast_period_id
,forecast_date
,forecast_uom_code
,forecast_quantity
,forward_buy_quantity
,forward_buy_period
,cumulation_period_choice
,base_quantity
,context
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
,forecast_remaining_quantity
,forecast_remaining_percent
,base_quantity_type
,forecast_spread_type
,dimention1
,dimention2
,dimention3
,last_scenario_id
,freeze_flag
,comments
,price_list_id
,base_quantity_start_date
,base_quantity_end_date
,base_quantity_ref
,offer_code
)
VALUES( l_act_forecast_rec.forecast_id
,l_act_forecast_rec.forecast_type
,l_act_forecast_rec.arc_act_fcast_used_by
,l_act_forecast_rec.act_fcast_used_by_id
,SYSDATE
,l_act_forecast_rec.created_from
,FND_GLOBAL.User_ID
,SYSDATE
,FND_GLOBAL.User_ID
,FND_GLOBAL.Conc_Login_ID
,l_act_forecast_rec.program_application_id
,l_act_forecast_rec.program_id
,l_act_forecast_rec.program_update_date
,l_act_forecast_rec.request_id
,1 -- object_version_number
,l_act_forecast_rec.hierarchy
,l_act_forecast_rec.hierarchy_level
,l_act_forecast_rec.level_value
,l_act_forecast_rec.forecast_calendar
,l_act_forecast_rec.period_level
,l_act_forecast_rec.forecast_period_id
,l_act_forecast_rec.forecast_date
,l_act_forecast_rec.forecast_uom_code
,l_act_forecast_rec.forecast_quantity
,l_act_forecast_rec.forward_buy_quantity
,l_act_forecast_rec.forward_buy_period
,l_act_forecast_rec.cumulation_period_choice
,l_act_forecast_rec.base_quantity
,l_act_forecast_rec.context
,l_act_forecast_rec.attribute_category
,l_act_forecast_rec.attribute1
,l_act_forecast_rec.attribute2
,l_act_forecast_rec.attribute3
,l_act_forecast_rec.attribute4
,l_act_forecast_rec.attribute5
,l_act_forecast_rec.attribute6
,l_act_forecast_rec.attribute7
,l_act_forecast_rec.attribute8
,l_act_forecast_rec.attribute9
,l_act_forecast_rec.attribute10
,l_act_forecast_rec.attribute11
,l_act_forecast_rec.attribute12
,l_act_forecast_rec.attribute13
,l_act_forecast_rec.attribute14
,l_act_forecast_rec.attribute15
,TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) -- org_id
,l_act_forecast_rec.forecast_remaining_quantity
,l_act_forecast_rec.forecast_remaining_percent
,l_act_forecast_rec.base_quantity_type
,l_act_forecast_rec.forecast_spread_type
,l_act_forecast_rec.dimention1
,l_act_forecast_rec.dimention2
,l_act_forecast_rec.dimention3
,l_act_forecast_rec.last_scenario_id
,l_act_forecast_rec.freeze_flag
,l_act_forecast_rec.comments
,l_act_forecast_rec.price_list_id
,l_act_forecast_rec.base_quantity_start_date
,l_act_forecast_rec.base_quantity_end_date
,l_act_forecast_rec.base_quantity_ref
,l_act_forecast_rec.offer_code);
PROCEDURE Update_ActForecast (
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_forecast_rec IN act_forecast_rec_type
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_ACTFORECAST';
SAVEPOINT Update_ActForecast_Pvt;
p_validation_mode => JTF_PLSQL_API.G_UPDATE,
x_complete_rec => l_act_forecast_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
);
OZF_Utility_PVT.debug_message(l_full_name ||': Update Activity Metric Facts Table');
UPDATE ozf_act_forecasts_all SET
object_version_number = object_version_number +1,
forecast_type = l_act_forecast_rec.forecast_type,
arc_act_fcast_used_by = l_act_forecast_rec.arc_act_fcast_used_by,
act_fcast_used_by_id = l_act_forecast_rec.act_fcast_used_by_id,
created_from = l_act_forecast_rec.created_from,
hierarchy = l_act_forecast_rec.hierarchy,
hierarchy_level = l_act_forecast_rec.hierarchy_level,
level_value = l_act_forecast_rec.level_value,
forecast_calendar = l_act_forecast_rec.forecast_calendar,
period_level = l_act_forecast_rec.period_level,
forecast_period_id = l_act_forecast_rec.forecast_period_id ,
forecast_date = l_act_forecast_rec.forecast_date,
forecast_uom_code = l_act_forecast_rec.forecast_uom_code,
forecast_quantity = l_act_forecast_rec.forecast_quantity,
forward_buy_quantity = l_act_forecast_rec.forward_buy_quantity,
forward_buy_period = l_act_forecast_rec.forward_buy_period,
cumulation_period_choice = l_act_forecast_rec.cumulation_period_choice,
base_quantity = l_act_forecast_rec.base_quantity,
context = l_act_forecast_rec.context,
attribute_category = l_act_forecast_rec.attribute_category,
attribute1 = l_act_forecast_rec.attribute1,
attribute2 = l_act_forecast_rec.attribute2,
attribute3 = l_act_forecast_rec.attribute3,
attribute4 = l_act_forecast_rec.attribute4,
attribute5 = l_act_forecast_rec.attribute5,
attribute6 = l_act_forecast_rec.attribute6,
attribute7 = l_act_forecast_rec.attribute7,
attribute8 = l_act_forecast_rec.attribute8,
attribute9 = l_act_forecast_rec.attribute9,
attribute10 = l_act_forecast_rec.attribute10,
attribute11 = l_act_forecast_rec.attribute11,
attribute12 = l_act_forecast_rec.attribute12,
attribute13 = l_act_forecast_rec.attribute13,
attribute14 = l_act_forecast_rec.attribute14,
attribute15 = l_act_forecast_rec.attribute15,
org_id = l_act_forecast_rec.org_id,
forecast_remaining_quantity = l_act_forecast_rec.forecast_remaining_quantity,
forecast_remaining_percent = l_act_forecast_rec.forecast_remaining_percent,
base_quantity_type = l_act_forecast_rec.base_quantity_type,
forecast_spread_type = l_act_forecast_rec.forecast_spread_type,
dimention1 = l_act_forecast_rec.dimention1,
dimention2 = l_act_forecast_rec.dimention2,
dimention3 = l_act_forecast_rec.dimention3,
last_scenario_id = l_act_forecast_rec.last_scenario_id,
freeze_flag = l_act_forecast_rec.freeze_flag,
comments = l_act_forecast_rec.comments,
price_list_id = l_act_forecast_rec.price_list_id
Where forecast_id = l_act_forecast_rec.forecast_id
And object_version_number = l_act_forecast_rec.object_version_number;
ROLLBACK TO Update_ActForecast_pvt;
ROLLBACK TO Update_ActForecast_pvt;
ROLLBACK TO Update_ActForecast_pvt;
END Update_ActForecast;
PROCEDURE Delete_ActForecast (
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_forecast_id IN NUMBER,
p_object_version_number IN NUMBER
)
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_ACTFORECAST';
SAVEPOINT Delete_ActForecast_pvt;
OZF_Utility_PVT.debug_message(l_full_name ||': delete with Validation');
DELETE from ozf_act_metric_facts_all
WHERE act_metric_used_by_id = p_forecast_id
AND arc_act_metric_used_by = 'FCST';
DELETE from ozf_act_metrics_all
WHERE act_metric_used_by_id = p_forecast_id
AND arc_act_metric_used_by = 'FCST';
DELETE from ozf_act_forecasts_all
WHERE forecast_id = p_forecast_id;
ROLLBACK TO Delete_ActForecast_pvt;
ROLLBACK TO Delete_ActForecast_pvt;
ROLLBACK TO Delete_ActForecast_pvt;
END Delete_ActForecast;
SELECT forecast_id
FROM ozf_act_forecasts_all
WHERE forecast_id = p_forecast_id
AND object_version_number = p_object_version_number
FOR UPDATE OF forecast_id NOWAIT;
SELECT *
FROM ozf_act_forecasts_all
WHERE forecast_id = p_act_forecast_rec.forecast_id;
IF p_act_forecast_rec.last_update_date = FND_API.G_MISS_DATE THEN
x_complete_fcst_rec.last_update_date := l_act_forecast_rec.last_update_date;
IF p_act_forecast_rec.last_updated_by = FND_API.G_MISS_NUM THEN
x_complete_fcst_rec.last_updated_by := l_act_forecast_rec.last_updated_by ;
IF p_act_forecast_rec.last_update_login = FND_API.G_MISS_NUM THEN
x_complete_fcst_rec.last_update_login := l_act_forecast_rec.last_update_login;
IF p_act_forecast_rec.program_update_date = FND_API.G_MISS_DATE THEN
x_complete_fcst_rec.program_update_date := l_act_forecast_rec.program_update_date;