[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Assignment is updated and is linked with a different Trip.
* Decrease the new trip availability and increase the old
* trip availability.
* Case#3
* Assignment is cancelled. Increase the old trip
* availability.
* Case#4
* Assignment is updated. Same trip is used. Increase /
* Decrease the availability by the difference.
*/
PROCEDURE update_trip_info(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_assignment_id IN NUMBER
, p_task_id IN NUMBER
, p_resource_id IN NUMBER
, p_resource_type_code IN VARCHAR2
, p_actual_start_date IN DATE DEFAULT NULL
, p_actual_end_date IN DATE DEFAULT NULL
, p_actual_effort IN NUMBER DEFAULT NULL
, p_actual_effort_uom IN VARCHAR2 DEFAULT NULL
, p_actual_travel_duration IN NUMBER DEFAULT NULL
, p_actual_travel_duration_uom IN VARCHAR2 DEFAULT NULL
, p_sched_travel_duration IN NUMBER DEFAULT NULL
, p_sched_travel_duration_uom IN VARCHAR2 DEFAULT NULL
, p_old_trip_id IN NUMBER DEFAULT NULL
, p_old_trip_ovn IN NUMBER DEFAULT NULL
, x_trip_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP_INFO';
SELECT t.scheduled_start_date
, t.scheduled_end_date
, csf_util_pvt.convert_to_minutes(planned_effort, planned_effort_uom) planned_effort
, ta.actual_start_date
, ta.actual_end_date
, ta.resource_id
, ta.resource_type_code
, csf_util_pvt.convert_to_minutes(ta.actual_effort, ta.actual_effort_uom) actual_effort
, csf_util_pvt.convert_to_minutes(ta.sched_travel_duration, ta.sched_travel_duration_uom) sched_travel_duration
, csf_util_pvt.convert_to_minutes(ta.actual_travel_duration, ta.actual_travel_duration_uom) actual_travel_duration
, cac.object_capacity_id old_trip_id
, cac.object_version_number old_trip_ovn
FROM jtf_tasks_b t
, jtf_task_assignments ta
, jtf_task_statuses_b ts
, cac_sr_object_capacity cac
WHERE t.task_id = p_task_id
AND ta.task_id (+) = t.task_id
AND ts.task_status_id (+) = ta.assignment_status_id
AND cac.object_capacity_id (+) = ta.object_capacity_id
AND NVL(ts.closed_flag, 'N') = 'N'
AND NVL(ts.completed_flag, 'N') = 'N'
AND NVL(ts.cancelled_flag, 'N') = 'N'
AND (p_task_assignment_id IS NULL OR ta.task_assignment_id = p_task_assignment_id);
csf_trips_pub.update_trip(
p_api_version => 1
, p_init_msg_list => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_trip_id => l_task_info.old_trip_id
, p_object_version_number => l_task_info.old_trip_ovn
, p_upd_available_hours => l_old_booked_time
);
csf_trips_pub.update_trip(
p_api_version => 1
, p_init_msg_list => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_trip_id => l_trip.trip_id
, p_object_version_number => l_trip.object_version_number
, p_upd_available_hours => - l_new_booked_time
);
END update_trip_info;
, p_update_task IN VARCHAR2
, p_new_sts_cancelled_flag IN VARCHAR2
, x_task_object_version_number OUT NOCOPY NUMBER
, x_task_status_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'PROPAGATE_STATUS_CHANGE';
SELECT t.task_id
, t.object_version_number
, t.task_status_id
, t.scheduled_start_date
, t.scheduled_end_date
, t.task_split_flag
, t.parent_task_id
, (SELECT pt.object_version_number FROM jtf_tasks_b pt WHERE pt.task_id = t.parent_task_id) parent_task_ovn
, t.source_object_type_code
, ta.resource_id
, ta.resource_type_code
, cac.object_capacity_id trip_id
, cac.object_version_number trip_ovn
, NVL(
( SELECT 'Y'
FROM jtf_task_assignments ta2, jtf_task_statuses_b ts2
WHERE ta2.task_id = t.task_id
AND ta2.task_assignment_id <> ta.task_assignment_id
AND ts2.task_status_id = ta2.assignment_status_id
AND NVL(ts2.cancelled_flag, 'N') <> 'Y'
AND NVL(ts2.rejected_flag, 'N') <> 'Y'
AND ta2.assignee_role = 'ASSIGNEE'
AND ta2.assignment_status_id <> ta.assignment_status_id
AND ROWNUM = 1
)
, 'N'
) other_ta_exists
FROM jtf_tasks_b t
, jtf_task_assignments ta
, cac_sr_object_capacity cac
WHERE ta.task_assignment_id = p_task_assignment_id
AND t.task_id = ta.task_id
AND cac.object_capacity_id (+) = ta.object_capacity_id;
IF p_update_task IS NULL OR p_update_task = fnd_api.g_true THEN
IF l_task_info.other_ta_exists = 'N' AND l_task_info.task_status_id <> p_new_assignment_status_id THEN
x_task_status_id := p_new_assignment_status_id;
jtf_tasks_pub.update_task(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => l_task_info.task_id
, p_task_status_id => x_task_status_id
, p_object_version_number => x_task_object_version_number
, p_scheduled_start_date => l_scheduled_start
, p_scheduled_end_date => l_scheduled_end
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
csf_tasks_pub.update_task_longer_than_shift(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_false
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => l_task_info.parent_task_id
, p_object_version_number => l_task_info.parent_task_ovn
, p_action => csf_tasks_pub.g_action_normal_to_parent
);
update_trip_info(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_assignment_id => p_task_assignment_id
, p_task_id => l_task_info.task_id
, p_resource_id => l_task_info.resource_id
, p_resource_type_code => l_task_info.resource_type_code
, p_old_trip_id => l_task_info.trip_id
, p_old_trip_ovn => l_task_info.trip_ovn
, x_trip_id => l_task_info.trip_id
);
, p_update_task IN VARCHAR2
, x_task_assignment_id OUT NOCOPY NUMBER
, x_ta_object_version_number OUT NOCOPY NUMBER
, x_task_object_version_number OUT NOCOPY NUMBER
, x_task_status_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TASK_ASSIGNMENT';
SELECT ta.task_assignment_id
, ta.object_version_number
FROM jtf_task_assignments ta, jtf_task_statuses_b ts
WHERE ta.task_id = p_task_id
AND ta.resource_id = p_resource_id
AND ta.resource_type_code = p_resource_type_code
AND ta.assignment_status_id = ts.task_status_id
AND ta.actual_start_date IS NULL
AND ta.actual_end_date IS NULL
AND ts.cancelled_flag = 'Y';
SELECT object_version_number
FROM jtf_task_assignments
WHERE task_assignment_id = x_task_assignment_id;
update_trip_info(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_assignment_id => p_task_assignment_id
, p_task_id => p_task_id
, p_resource_type_code => p_resource_type_code
, p_resource_id => p_resource_id
, p_actual_start_date => p_actual_start_date
, p_actual_end_date => p_actual_end_date
, p_actual_effort => p_actual_effort
, p_actual_effort_uom => p_actual_effort_uom
, p_actual_travel_duration => p_actual_travel_duration
, p_actual_travel_duration_uom => p_actual_travel_duration_uom
, p_sched_travel_duration => p_sched_travel_duration
, p_sched_travel_duration_uom => p_sched_travel_duration_uom
, x_trip_id => l_trip_id
);
update_task_assignment(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_none
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_assignment_id => x_task_assignment_id
, p_object_version_number => x_ta_object_version_number
, p_task_id => p_task_id
, p_resource_type_code => p_resource_type_code
, p_resource_id => p_resource_id
, p_resource_territory_id => p_resource_territory_id
, p_assignment_status_id => p_assignment_status_id
, p_actual_start_date => p_actual_start_date
, p_actual_end_date => p_actual_end_date
, p_sched_travel_distance => p_sched_travel_distance
, p_sched_travel_duration => p_sched_travel_duration
, p_sched_travel_duration_uom => p_sched_travel_duration_uom
, p_shift_construct_id => p_shift_construct_id
, p_object_capacity_id => l_trip_id
, p_update_task => p_update_task
, x_task_object_version_number => x_task_object_version_number
, x_task_status_id => x_task_status_id
);
, p_update_task => p_update_task
, p_new_sts_cancelled_flag => 'N'
, x_task_object_version_number => x_task_object_version_number
, x_task_status_id => x_task_status_id
);
* Update an existing Task Assignment with new Task Attributes
*
* Given the Task Assignment ID and Task Object Version Number, it calls
* JTF Task Assignment API to update the Task Assignment with the new Attributes.
* It is actually a two step process
* 1. Updating the Task Assignment with the new Task Attributes except Status
* 2. Updating the Task Assignment with the new Task Status (if not FND_API.G_MISS_NUM)
* by calling UPDATE_ASSIGNMENT_STATUS.
*
* Because of the two step process, the returned Task Assignment Object
* Version Number might be incremented by 2 when user might have expected an
* increment of only 1.
*
* Except Task Assignment ID and Object Version Number parameters, all are optional.
*/
PROCEDURE update_task_assignment(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_assignment_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_task_id IN NUMBER
, p_resource_type_code IN VARCHAR2
, p_resource_id IN NUMBER
, p_resource_territory_id IN NUMBER
, p_assignment_status_id IN NUMBER
, p_actual_start_date IN DATE
, p_actual_end_date IN DATE
, p_sched_travel_distance IN NUMBER
, p_sched_travel_duration IN NUMBER
, p_sched_travel_duration_uom IN VARCHAR2
, p_shift_construct_id IN NUMBER
, p_object_capacity_id IN NUMBER
, p_update_task IN VARCHAR2
, p_task_number IN VARCHAR2
, p_task_name IN VARCHAR2
, p_resource_name IN VARCHAR2
, p_actual_effort IN NUMBER
, p_actual_effort_uom IN VARCHAR2
, p_actual_travel_distance IN NUMBER
, p_actual_travel_duration IN NUMBER
, p_actual_travel_duration_uom IN VARCHAR2
, p_attribute1 IN VARCHAR2
, p_attribute2 IN VARCHAR2
, p_attribute3 IN VARCHAR2
, p_attribute4 IN VARCHAR2
, p_attribute5 IN VARCHAR2
, p_attribute6 IN VARCHAR2
, p_attribute7 IN VARCHAR2
, p_attribute8 IN VARCHAR2
, p_attribute9 IN VARCHAR2
, p_attribute10 IN VARCHAR2
, p_attribute11 IN VARCHAR2
, p_attribute12 IN VARCHAR2
, p_attribute13 IN VARCHAR2
, p_attribute14 IN VARCHAR2
, p_attribute15 IN VARCHAR2
, p_attribute_category IN VARCHAR2
, p_show_on_calendar IN VARCHAR2
, p_category_id IN NUMBER
, p_schedule_flag IN VARCHAR2
, p_alarm_type_code IN VARCHAR2
, p_alarm_contact IN VARCHAR2
, p_palm_flag IN VARCHAR2
, p_wince_flag IN VARCHAR2
, p_laptop_flag IN VARCHAR2
, p_device1_flag IN VARCHAR2
, p_device2_flag IN VARCHAR2
, p_device3_flag IN VARCHAR2
, p_enable_workflow IN VARCHAR2
, p_abort_workflow IN VARCHAR2
, x_task_object_version_number OUT NOCOPY NUMBER
, x_task_status_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_ASSIGNMENT';
SELECT NVL (ts.cancelled_flag, 'N') cancelled_flag
FROM jtf_task_statuses_b ts
WHERE ts.task_status_id = p_assignment_status_id;
SELECT ta.assignment_status_id, ta.task_id
FROM jtf_task_assignments ta
WHERE task_assignment_id = p_task_assignment_id;
SAVEPOINT csf_update_task_assignment_pub;
update_trip_info(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => l_task_id
, p_task_assignment_id => p_task_assignment_id
, p_resource_type_code => p_resource_type_code
, p_resource_id => p_resource_id
, p_actual_start_date => p_actual_start_date
, p_actual_end_date => p_actual_end_date
, p_actual_effort => p_actual_effort
, p_actual_effort_uom => p_actual_effort_uom
, p_actual_travel_duration => p_actual_travel_duration
, p_actual_travel_duration_uom => p_actual_travel_duration_uom
, p_sched_travel_duration => p_sched_travel_duration
, p_sched_travel_duration_uom => p_sched_travel_duration_uom
, x_trip_id => l_trip_id
);
jtf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_assignment_id => p_task_assignment_id
, p_object_version_number => p_object_version_number
, p_task_id => l_task_id
, p_resource_type_code => p_resource_type_code
, p_resource_id => p_resource_id
, p_resource_territory_id => p_resource_territory_id
, p_assignment_status_id => p_assignment_status_id
, p_actual_start_date => p_actual_start_date
, p_actual_end_date => p_actual_end_date
, p_sched_travel_distance => l_distance
, p_sched_travel_duration => l_duration
, p_sched_travel_duration_uom => l_duration_uom
, p_shift_construct_id => p_shift_construct_id
, p_object_capacity_id => l_trip_id
, p_task_number => p_task_number
, p_task_name => p_task_name
, p_resource_name => p_resource_name
, p_actual_effort => p_actual_effort
, p_actual_effort_uom => p_actual_effort_uom
, p_actual_travel_distance => p_actual_travel_distance
, p_actual_travel_duration => p_actual_travel_duration
, p_actual_travel_duration_uom => p_actual_travel_duration_uom
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_attribute_category => p_attribute_category
, p_show_on_calendar => p_show_on_calendar
, p_category_id => p_category_id
, p_schedule_flag => p_schedule_flag
, p_alarm_type_code => p_alarm_type_code
, p_alarm_contact => p_alarm_contact
, p_palm_flag => p_palm_flag
, p_wince_flag => p_wince_flag
, p_laptop_flag => p_laptop_flag
, p_device1_flag => p_device1_flag
, p_device2_flag => p_device2_flag
, p_device3_flag => p_device3_flag
, p_enable_workflow => p_enable_workflow
, p_abort_workflow => p_abort_workflow
);
, p_update_task => p_update_task
, p_new_sts_cancelled_flag => l_new_sts_cancelled_flag
, x_task_object_version_number => x_task_object_version_number
, x_task_status_id => x_task_status_id
);
ROLLBACK TO csf_update_task_assignment_pub;
ROLLBACK TO csf_update_task_assignment_pub;
ROLLBACK TO csf_update_task_assignment_pub;
END update_task_assignment;
* Update the Status of the Task Assignment with the given Status and propagate to the
* Task also if required.
*
* Task Assignment is updated with the new Status if the Transition from the current
* status to the new status is allowed as determined by
* CSF_TASKS_PUB.VALIDATE_STATE_TRANSITION. Transition validation is done only
* when Validation Level is passed as FULL.
*
* In addition to updating the Task Assignment Status, the following operations are also
* done
* 1. If the Task corresponding to the given Task Assignment has no other
* Open / Active Task Assignments other than the given one, then the Assignment
* Status is propagated to the Task also. If there exists any other Active
* Assignment, then the Task is not updated.
* The parameters P_TASK_OBJECT_VERSION_NUMBER and X_TASK_STATUS_ID reflect
* the Object Version Number and Task Status ID of the Task in Database
* irrespective of the fact whether the update has taken place or not.
*
* 2. If the Assignment goes to Cancelled (as per the new status), then if any
* Spares Order is linked to the Assignment, they are cleaned up by calling
* CLEAN_MATERIAL_TRANSACTION of Spares.
*
* 3. If the Assignment goes to Assigned (as per the new status), and the
* old status is not Assigned, then Orders are created and linked to the
* Task Assignment.
*
* 4. If the Assignnment goes to Working (as per the new status), then it means
* that the Resource is working on the Task and so his location should be updated
* to reflect the location of the Task. This is required by Map Functionality.
* THIS IS WRONG AND SHOULD BE REMOVED. MAP SHOULD BE USING HZ_LOCATIONS TABLE.
*
* @param p_api_version API Version (1.0)
* @param p_init_msg_list Initialize Message List
* @param p_commit Commit the Work
* @param p_validation_level Validate the given Parameters
* @param x_return_status Return Status of the Procedure.
* @param x_msg_count Number of Messages in the Stack.
* @param x_msg_data Stack of Error Messages.
* @param p_task_assignment_id Task Assignment ID of the Assignment to be updated
* @param p_assignment_status_id New Task Status ID for the Task Assignment.
* @param p_show_on_calendar
* @param p_object_version_number Current Task Version and also container for new one.
* @param x_task_object_version_number Task Object Version Number (either old or new)
* @param x_task_status_id Task Status ID (either old or new)
*/
PROCEDURE update_assignment_status(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_assignment_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_assignment_status_id IN NUMBER
, p_update_task IN VARCHAR2
, p_show_on_calendar IN VARCHAR2
, x_task_object_version_number OUT NOCOPY NUMBER
, x_task_status_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ASSIGNMENT_STATUS';
SELECT ta.assignment_status_id
FROM jtf_task_assignments ta
WHERE task_assignment_id = p_task_assignment_id;
SELECT NVL (ts.cancelled_flag, 'N') cancelled_flag
FROM jtf_task_statuses_b ts
WHERE ts.task_status_id = p_assignment_status_id;
SAVEPOINT csf_update_assign_status_pub;
jtf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_object_version_number => p_object_version_number
, p_task_assignment_id => p_task_assignment_id
, p_assignment_status_id => p_assignment_status_id
, p_sched_travel_distance => l_distance
, p_sched_travel_duration => l_duration
, p_sched_travel_duration_uom => l_duration_uom
, p_object_capacity_id => l_trip_id
, p_show_on_calendar => p_show_on_calendar
, p_category_id => NULL
, p_enable_workflow => fnd_api.g_false
, p_abort_workflow => fnd_api.g_false
);
, p_update_task => p_update_task
, p_new_sts_cancelled_flag => l_new_sts_cancelled_flag
, x_task_object_version_number => x_task_object_version_number
, x_task_status_id => x_task_status_id
);
ROLLBACK TO csf_update_assign_status_pub;
ROLLBACK TO csf_update_assign_status_pub;
ROLLBACK TO csf_update_assign_status_pub;
END update_assignment_status;