[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
, parent_request_id
, planmode -- this column is obsolete; will be removed
, last_updated_by
, last_update_date
, object_version_number
, consider_standby_shift
, spares_source
, spares_mandatory
, res_preference
, cust_or_disp_initiated
)
VALUES (
csf_r_sched_requests_s1.NEXTVAL
, p_name
, 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
, p_standby_param
, p_spares_source
, p_spares_mandatory
, p_resource_preference
, CASE WHEN p_cust_or_disp_init = 'Y' THEN 'C'
WHEN p_cust_or_disp_init = 'N' THEN 'D'
ELSE NULL
END
)
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
, skill_level
, resource_source
)
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
, p_resource_tbl(i).skill_level
, p_resource_tbl(i).resource_source
);
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
, shift_type
)
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
, p_shift_type
);
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
, warehouse
, shipment_method
, distance
)
VALUES (
csf_r_spares_options_s1.NEXTVAL
, l_resource_rec.resource_result_id
, p_spares_date_tbl(j)
, p_spares_cost_tbl(j)
, 1
, l_spares_warehouseInfo
, l_spares_shippingMethod
, l_spares_shippingDistance
)
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
, shift_type
)
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
, p_shift_type_tbl(j)
);