[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO csf_r_sched_requests(
sched_request_id
, sched_request_name
, spares_likelihood
, parent_request_id
, planmode -- this column is obsolete; will be removed
, last_updated_by
, last_update_date
, object_version_number
)
VALUES (
csf_r_sched_requests_s1.NEXTVAL
, p_name
, p_spares_likelihood
, p_parent_id
, -1 -- dummy value for obsolete but mandatory column
, p_route_based_flag
, p_status_id
, p_changed_option_start
, p_changed_option_end
, p_changed_planned_start
, p_changed_planned_end
, p_disabled_access_hours_flag
, p_set_plan_task_confirmed
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, SYSDATE
, 1
)
RETURNING sched_request_id INTO x_request_id;
INSERT INTO csf_r_request_tasks(
request_task_id
, sched_request_id
, task_id
, object_version_number
)
VALUES (
csf_r_request_tasks_s1.NEXTVAL
, x_request_id
, p_object_id
, 1
)
RETURNING request_task_id INTO l_request_task_id;
INSERT INTO csf_r_resource_results
(
resource_result_id
, request_task_id
, resource_id
, resource_type
, territory_rank
, territory_id
, preferred_resources_flag
, planwin_start
, planwin_end
, object_version_number
)
VALUES (
csf_r_resource_results_s1.NEXTVAL
, p_request_task_id
, p_resource_tbl(i).resource_id
, p_resource_tbl(i).resource_type
, NVL(p_resource_tbl(i).territory_rank, '1')
, NVL(p_resource_tbl(i).territory_id, '-1')
, p_resource_tbl(i).preferred_resources_flag
, NVL(p_resource_tbl(i).planwin_start, SYSDATE)
, NVL(p_resource_tbl(i).planwin_end, SYSDATE)
, 1
);
SELECT rr.resource_result_id
, so.spares_option_id
, csf_r_plan_options_s1.NEXTVAL plan_option_id
FROM csf_r_resource_results rr, csf_r_spares_options so
WHERE rr.request_task_id = p_request_task_id
AND rr.resource_id = p_resource_id
AND rr.resource_type = p_resource_type
AND so.resource_result_id(+) = rr.resource_result_id
AND so.availability_date(+) = p_spares_date
AND so.COST(+) = p_spares_cost;
INSERT INTO csf_r_spares_options
(
spares_option_id
, resource_result_id
, availability_date
, COST
, object_version_number
)
VALUES (
csf_r_spares_options_s1.NEXTVAL
, l_resource_rec.resource_result_id
, p_spares_date
, p_spares_cost
, 1
)
RETURNING spares_option_id INTO l_resource_rec.spares_option_id;
INSERT INTO csf_r_plan_options
(
plan_option_id
, resource_result_id
, spares_option_id
, scheduled_start_date
, scheduled_end_date
, COST
, win_to_promis_id
, object_version_number
)
VALUES (
l_resource_rec.plan_option_id
, l_resource_rec.resource_result_id
, l_resource_rec.spares_option_id
, p_scheduled_start_date
, p_scheduled_end_date
, p_cost
, p_win_to_promis_id
, 1
);
INSERT INTO csf_r_plan_option_tasks
(
plan_option_task_id
, plan_option_id
, task_id
, scheduled_start_date
, scheduled_end_date
, travel_time
, task_assignment_id
, object_capacity_id
, object_version_number
)
VALUES (
csf_r_plan_option_tasks_s1.NEXTVAL
, p_plan_option_id
, p_task_id
, p_scheduled_start_date
, p_scheduled_end_date
, p_travel_time
, p_task_assign_id
, p_trip_id
, 1
);
SELECT request_task_id
, csf_r_messages_s1.NEXTVAL message_id
FROM csf_r_request_tasks
WHERE sched_request_id = p_request_id
AND ROWNUM = 1; -- there should be one task per request
INSERT INTO csf_r_messages
(
message_id
, request_task_id
, name
, type
, object_version_number
)
VALUES (
l_rec.message_id
, l_rec.request_task_id
, p_name
, p_type
, 1
);
INSERT INTO csf_r_message_tokens
(
message_token_id
, message_id
, NAME
, VALUE
, object_version_number
)
VALUES (
csf_r_message_tokens_s1.NEXTVAL
, p_message_id
, SUBSTR(p_name, 1, 60)
, SUBSTR(p_value, 1, 4000)
, 1
);
SELECT rr.resource_result_id
, so.spares_option_id
, csf_r_plan_options_s1.NEXTVAL plan_option_id
FROM csf_r_resource_results rr, csf_r_spares_options so
WHERE rr.request_task_id = p_request_task_id
AND rr.resource_id = p_resource_id
AND rr.resource_type = p_resource_type
AND so.resource_result_id(+) = rr.resource_result_id
AND so.availability_date(+) = p_spares_date
AND so.COST(+) = p_spares_cost;
INSERT INTO csf_r_spares_options
(
spares_option_id
, resource_result_id
, availability_date
, COST
, object_version_number
)
VALUES (
csf_r_spares_options_s1.NEXTVAL
, l_resource_rec.resource_result_id
, p_spares_date_tbl(j)
, p_spares_cost_tbl(j)
, 1
)
RETURNING spares_option_id INTO l_resource_rec.spares_option_id;
INSERT INTO csf_r_plan_options
(
plan_option_id
, resource_result_id
, spares_option_id
, scheduled_start_date
, scheduled_end_date
, COST
, win_to_promis_id
, object_version_number
)
VALUES (
l_resource_rec.plan_option_id
, l_resource_rec.resource_result_id
, l_resource_rec.spares_option_id
, p_scheduled_start_date_tbl(j)
, p_scheduled_end_date_tbl(j)
, p_cost_tbl(j)
, p_win_to_promis_id_tbl(j)
, 1
);