[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT task_status_id
FROM jtf_task_statuses_b
WHERE task_status_id = p_status_id
AND NVL (cancelled_flag, 'N') = 'Y';
SELECT task_type_id
FROM jtf_task_types_b
WHERE rule = 'DISPATCH'
AND NVL (schedule_flag, 'N') = 'Y'
AND task_type_id = p_task_type_id;
SELECT task_status_id
FROM jtf_task_statuses_b
WHERE NVL (schedulable_flag, 'N') = 'Y'
AND task_status_id = p_task_status_id;
SELECT task_number
FROM jtf_tasks_b
WHERE task_id = p_task_id;
SELECT NVL (cdh.processed_flag, 'PENDING') debrief_status
FROM csf_debrief_headers cdh
WHERE cdh.task_assignment_id = p_task_assignment_id;
p_deleted_flag IN VARCHAR2
, p_planned_start_date IN DATE
, p_planned_end_date IN DATE
, p_planned_effort IN NUMBER
, p_task_type_id IN NUMBER
, p_task_status_id IN NUMBER
, x_reason_code OUT NOCOPY VARCHAR2
) RETURN BOOLEAN IS
BEGIN
x_reason_code := NULL;
IF p_deleted_flag = 'Y' THEN
x_reason_code := 'CSF_DELETED_TASK';
SELECT NAME
FROM jtf_task_statuses_vl
WHERE task_status_id = p_task_status_id;
SELECT NULL
FROM jtf_state_responsibilities re
, jtf_state_rules_b ru
, jtf_state_transitions tr
WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
AND re.rule_id = ru.rule_id
AND ru.state_type = p_state_type
AND ru.rule_id = tr.rule_id
AND tr.initial_state_id = p_new_status_id;
SELECT NULL
FROM jtf_state_responsibilities re
, jtf_state_rules_b ru
, jtf_state_transitions tr
WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
AND re.rule_id = ru.rule_id
AND ru.state_type = p_state_type
AND ru.rule_id = tr.rule_id
AND tr.initial_state_id = p_old_status_id
AND tr.final_state_id = p_new_status_id;
SELECT DISTINCT tr.initial_state_id, ts.name
FROM jtf_state_responsibilities re
, jtf_state_rules_b ru
, jtf_state_transitions tr
, jtf_task_statuses_tl ts
WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
AND re.rule_id = ru.rule_id
AND ru.state_type = p_state_type
AND ru.rule_id = tr.rule_id
AND ts.task_status_id = tr.initial_state_id
AND ts.language = userenv('LANG');
SELECT DISTINCT tr.final_state_id, ts.name
FROM jtf_state_responsibilities re
, jtf_state_rules_b ru
, jtf_state_transitions tr
, jtf_task_statuses_tl ts
WHERE (re.responsibility_id = fnd_global.resp_id OR fnd_global.resp_id = -1)
AND re.rule_id = ru.rule_id
AND ru.state_type = p_state_type
AND ru.rule_id = tr.rule_id
AND tr.initial_state_id = p_old_status_id
AND ts.task_status_id = tr.final_state_id
AND ts.language = userenv('LANG');
SELECT task_status_id
FROM jtf_tasks_b
WHERE task_id = p_task_id;
SELECT ta.task_assignment_id
, t.scheduled_start_date
, t.scheduled_end_date
, NVL (ts.closed_flag, 'N') closed_flag
, NVL (ts.cancelled_flag, 'N') cancelled_flag
, NVL (ts.completed_flag, 'N') completed_flag
, NVL (ts.rejected_flag, 'N') rejected_flag
FROM jtf_task_assignments ta, jtf_tasks_b t, jtf_task_statuses_b ts
WHERE ta.task_id = t.task_id
AND t.task_id = p_task_id
AND assignment_status_id = ts.task_status_id;
l_update_schedulable_task VARCHAR2(3);
l_update_schedulable_task := NVL(fnd_profile.value('CSFW_UPD_SCHEDULABLE'), 'NO');
IF l_update_schedulable_task = 'YES' THEN
IF NOT is_debrief_closed(v_task_assignment.task_assignment_id) THEN
fnd_message.set_name('CSF', 'CSF_DEBRIEF_PENDING');
SELECT task_type_id
, task_status_id
, planned_start_date
, planned_end_date
, planned_effort
, address_id
, deleted_flag
FROM jtf_tasks_b
WHERE task_id = p_task_id;
p_deleted_flag => l_task_details.deleted_flag
, p_planned_start_date => l_task_details.planned_start_date
, p_planned_end_date => l_task_details.planned_end_date
, p_planned_effort => l_task_details.planned_effort
, p_task_type_id => l_task_details.task_type_id
, p_task_status_id => l_task_details.task_status_id
, x_reason_code => l_message_name
);
SELECT t.scheduled_start_date
, t.scheduled_end_date
, t.task_split_flag
, t.task_status_id
, ta.resource_id
FROM jtf_tasks_b t, jtf_task_assignments ta
WHERE ta.task_id = t.task_id AND t.task_id = p_task_id;
SELECT task_id
FROM jtf_task_references_b r
WHERE r.reference_code = 'ESC'
AND r.object_type_code = 'TASK'
AND r.object_id = p_task_id;
SELECT 1
FROM jtf_tasks_b t
, jtf_task_statuses_b s
WHERE t.task_id = b_task_id
AND t.task_type_id = g_esc_task_type_id
AND s.task_status_id = t.task_status_id
AND NVL(s.closed_flag, 'N') <> 'Y'
AND NVL(t.deleted_flag, 'N') <> 'Y';
update_task_status (
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 => p_task_id
, p_task_status_id => l_close_status_id
, p_object_version_number => l_object_version_number
);
SELECT t.object_version_number
, t.task_status_id
, ts.name task_status_name
, t.scheduled_start_date
, t.scheduled_end_date
, t.task_split_flag
, t.parent_task_id
, ts.schedulable_flag ts_schedulable_flag
, ts.assigned_flag
, tt.schedule_flag tt_schedule_flag
, ta.resource_name
FROM jtf_tasks_b t
, csf_ct_task_assignments ta
, jtf_task_statuses_vl ts
, jtf_task_types_b tt
WHERE t.ROWID = CHARTOROWID (p_row_id)
AND ts.task_status_id = t.task_status_id
AND tt.task_type_id = t.task_type_id
AND ta.task_id (+) = t.task_id;
x_collection(l_idx).updated_flag := 'Y';
/* reset updated flag if not different */
x_collection (l_idx).updated_flag := 'N';
, p_date_selected IN VARCHAR2
, p_category_id IN NUMBER
, p_show_on_calendar IN VARCHAR2
, p_task_assign_tbl IN jtf_tasks_pub.task_assign_tbl
, p_task_depends_tbl IN jtf_tasks_pub.task_depends_tbl
, p_task_rsrc_req_tbl IN jtf_tasks_pub.task_rsrc_req_tbl
, p_task_refer_tbl IN jtf_tasks_pub.task_refer_tbl
, p_task_dates_tbl IN jtf_tasks_pub.task_dates_tbl
, p_task_notes_tbl IN jtf_tasks_pub.task_notes_tbl
, p_task_recur_rec IN jtf_tasks_pub.task_recur_rec
, p_task_contacts_tbl IN jtf_tasks_pub.task_contacts_tbl
, p_template_id IN NUMBER
, p_template_group_id IN NUMBER
, p_enable_workflow IN VARCHAR2
, p_abort_workflow IN VARCHAR2
, p_task_split_flag IN VARCHAR2
, p_parent_task_number IN VARCHAR2
, p_parent_task_id IN NUMBER
, p_child_position IN VARCHAR2
, p_child_sequence_num IN NUMBER
, x_task_id OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
p_deleted_flag => 'N'
, p_planned_start_date => p_planned_start_date
, p_planned_end_date => p_planned_end_date
, p_planned_effort => p_planned_effort
, p_task_type_id => p_task_type_id
, p_task_status_id => p_task_status_id
, x_reason_code => l_reason_code
);
, p_date_selected => p_date_selected
, p_category_id => p_category_id
, p_show_on_calendar => p_show_on_calendar
, p_owner_status_id => p_owner_status_id
, p_template_id => p_template_id
, p_template_group_id => p_template_group_id
, p_enable_workflow => p_enable_workflow
, p_abort_workflow => p_abort_workflow
, p_task_split_flag => p_task_split_flag
, p_parent_task_number => p_parent_task_number
, p_parent_task_id => p_parent_task_id
, p_child_position => p_child_position
, p_child_sequence_num => p_child_sequence_num
, x_task_id => x_task_id
);
PROCEDURE delete_task (
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_id IN NUMBER
, p_task_number IN VARCHAR2
, p_object_version_number IN NUMBER
, p_delete_future_recurrences IN VARCHAR2
) IS
BEGIN
jtf_tasks_pub.delete_task (
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_task_id => p_task_id
, p_task_number => p_task_number
, p_object_version_number => p_object_version_number
, p_delete_future_recurrences => p_delete_future_recurrences
);
END delete_task;
SELECT ta.task_assignment_id
, ta.object_version_number
FROM csf_ct_task_assignments ta
, jtf_task_statuses_b ts
WHERE ta.task_id = p_task_id
AND ts.task_status_id = ta.assignment_status_id
AND ( NVL (ts.working_flag, 'N') = 'Y'
OR NVL (ts.accepted_flag, 'N') = 'Y'
OR NVL (ts.on_hold_flag, 'N') = 'Y'
OR NVL (ts.schedulable_flag, 'N') = 'Y'
OR ( NVL(ts.assigned_flag, 'N') = 'Y'
AND NVL(ts.closed_flag, 'N') <> 'Y'
AND NVL(ts.approved_flag, 'N') <> 'Y'
AND NVL(ts.completed_flag, 'N') <> 'Y'
AND NVL(ts.rejected_flag, 'N') <> 'Y')
);
SELECT ta.task_assignment_id
, ta.object_version_number
, NVL (ts.closed_flag, 'N') closed_flag
, NVL (ts.cancelled_flag, 'N') cancelled_flag
, NVL (ts.completed_flag, 'N') completed_flag
, NVL (ts.rejected_flag, 'N') rejected_flag
FROM jtf_task_assignments ta, jtf_task_statuses_b ts
WHERE ta.task_id = p_task_id
AND ts.task_status_id = ta.assignment_status_id;
csf_task_assignments_pub.update_assignment_status(
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 => v_task_assignment.task_assignment_id
, p_object_version_number => v_task_assignment.object_version_number
, p_assignment_status_id => p_new_task_status_id
, p_update_task => fnd_api.g_false
, x_task_object_version_number => l_task_ovn
, x_task_status_id => l_task_status_id
);
* I didnt understand the significance of using CSFW: Update Schedulable Task
* to check whether Debrief should be checked or not. The significance
* of the profile is to govern whether Debrief can be invoked directly
* without Scheduling the Task and not the other way round.
* Therefore removed the logic - venjayar.
*/
-- Check whether the Task Assignment is still open.
IF ( v_task_assignment.closed_flag = 'N'
AND v_task_assignment.completed_flag = 'N'
AND v_task_assignment.cancelled_flag = 'N'
AND v_task_assignment.rejected_flag = 'N' )
THEN
fnd_message.set_name ('CSF', 'CSF_CLOSED_TASK');
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 => v_task_assignment.task_assignment_id
, p_object_version_number => v_task_assignment.object_version_number
, p_assignment_status_id => p_new_task_status_id
);
* Update the status of a Task and propagate to Task Assignments also.
*
* If the new Status of the Task is CANCELLED, then all Assignments which are open
* (Working, Accepted, Assigned, In-Planning, Planned, On-Hold) needs to be
* cancelled too. Other Assignments of the Task will not be updated.
*
* If the new Status of the Task is CLOSED, then we have to validate if the Task
* can be closed. For this, there should not be any Open Task Assignments linked
* to the Task. Moreover, if the Profile "CSFW: Update Schedulable Task" is set to
* Yes, then the debrief linked with the Assignments should have been COMPLETED.
* Otherwise Task cant be closed. If all verifications passes, then Task and the
* open Assignments are closed.
*/
PROCEDURE update_task_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_id IN NUMBER
, p_task_status_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK_STATUS';
SELECT t.task_status_id
, t.scheduled_start_date
, t.scheduled_end_date
, t.object_version_number
, t.source_object_type_code
FROM jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE task_id = p_task_id
AND ts.task_status_id = t.task_status_id;
SELECT NVL (ts.closed_flag, 'N') closed_flag
, NVL (ts.cancelled_flag, 'N') cancelled_flag
FROM jtf_task_statuses_b ts
WHERE ts.task_status_id = p_task_status_id;
SAVEPOINT update_task_status_pub;
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 => p_task_id
, p_object_version_number => p_object_version_number
, p_task_status_id => p_task_status_id
, p_scheduled_start_date => l_task_info.scheduled_start_date
, p_scheduled_end_date => l_task_info.scheduled_end_date
);
ROLLBACK TO update_task_status_pub;
ROLLBACK TO update_task_status_pub;
ROLLBACK TO update_task_status_pub;
END update_task_status;
PROCEDURE update_task(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_validation_level IN NUMBER DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_task_number IN VARCHAR2
, p_task_name IN VARCHAR2
, p_description IN VARCHAR2
, p_planned_start_date IN DATE
, p_planned_end_date IN DATE
, p_scheduled_start_date IN DATE
, p_scheduled_end_date IN DATE
, p_actual_start_date IN DATE
, p_actual_end_date IN DATE
, p_timezone_id IN NUMBER
, p_source_object_type_code IN VARCHAR2
, p_source_object_id IN NUMBER
, p_source_object_name IN VARCHAR2
, p_task_status_id IN NUMBER
, p_task_type_id IN NUMBER
, p_task_priority_id IN NUMBER
, p_owner_type_code IN VARCHAR2
, p_owner_id IN NUMBER
, p_owner_territory_id IN NUMBER
, p_owner_status_id IN NUMBER
, p_assigned_by_id IN NUMBER
, p_customer_id IN NUMBER
, p_cust_account_id IN NUMBER
, p_address_id IN NUMBER
, p_location_id IN NUMBER
, p_duration IN NUMBER
, p_duration_uom IN VARCHAR2
, p_planned_effort IN NUMBER
, p_planned_effort_uom IN VARCHAR2
, p_actual_effort IN NUMBER
, p_actual_effort_uom IN VARCHAR2
, p_percentage_complete IN NUMBER
, p_reason_code IN VARCHAR2
, p_private_flag IN VARCHAR2
, p_publish_flag IN VARCHAR2
, p_restrict_closure_flag 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_date_selected IN VARCHAR2
, p_category_id IN NUMBER
, p_multi_booked_flag IN VARCHAR2
, p_milestone_flag IN VARCHAR2
, p_holiday_flag IN VARCHAR2
, p_billable_flag IN VARCHAR2
, p_bound_mode_code IN VARCHAR2
, p_soft_bound_flag IN VARCHAR2
, p_workflow_process_id IN NUMBER
, p_notification_flag IN VARCHAR2
, p_notification_period IN NUMBER
, p_notification_period_uom IN VARCHAR2
, p_alarm_start IN NUMBER
, p_alarm_start_uom IN VARCHAR2
, p_alarm_on IN VARCHAR2
, p_alarm_count IN NUMBER
, p_alarm_fired_count IN NUMBER
, p_alarm_interval IN NUMBER
, p_alarm_interval_uom 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_show_on_calendar IN VARCHAR2
, p_costs IN NUMBER
, p_currency_code IN VARCHAR2
, p_escalation_level IN VARCHAR2
, p_parent_task_id IN NUMBER
, p_parent_task_number IN VARCHAR2
, p_task_split_flag IN VARCHAR2
, p_child_position IN VARCHAR2
, p_child_sequence_num IN NUMBER
, p_enable_workflow IN VARCHAR2
, p_abort_workflow IN VARCHAR2
, p_find_overlap IN VARCHAR2 DEFAULT NULL
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK';
SELECT NVL(TASK_NUMBER,TASK_ID) overlap_task_num
FROM csr_trip_tasks_v
WHERE object_capacity_id = p_trip_id
AND task_id <> p_task_id
AND NVL(actual_end_date,scheduled_end_date) >= p_start_date
AND NVL(actual_start_date,scheduled_start_date) <= p_end_date;
SELECT t.scheduled_start_date
, t.scheduled_end_date
, CASE WHEN ta.actual_start_date IS NULL AND ta.actual_end_date IS NULL THEN 'N' ELSE 'Y' END is_visited
, ta.resource_id
, ta.resource_type_code
, ta.object_capacity_id
, nvl(ta.actual_effort,t.planned_effort) planned_effort
, nvl(ta.actual_effort_uom,t.planned_effort_uom) planned_effort_uom
, ta.task_assignment_id
, ta.object_version_number
, t.task_status_id
, t.planned_start_date
, t.planned_end_date
, ta.assignment_status_id
, t.task_split_flag
, t.task_number
FROM jtf_tasks_b t,
(SELECT tas.actual_start_date
, tas.actual_end_date
, tas.resource_id
, tas.resource_type_code
, tas.object_capacity_id
, tas.task_assignment_id
, tas.object_version_number
, tas.assignment_status_id
, tas.task_id
, tas.actual_effort
, tas.actual_effort_uom
FROM jtf_task_assignments tas, jtf_task_statuses_b ts
WHERE task_id = p_task_id
AND ts.task_status_id = tas.assignment_status_id
AND NVL(ts.cancelled_flag, 'N') <> 'Y'
AND NVL(ts.closed_flag, 'N') <> 'Y'
AND NVL(ts.completed_flag, 'N') <> 'Y'
AND NVL(ts.rejected_flag, 'N') <> 'Y'
) ta
WHERE t.task_id = p_task_id
AND t.task_id = ta.task_id(+)
AND NVL(t.deleted_flag, 'N') <> 'Y';
SELECT NVL (ts.closed_flag, 'N') closed_flag
, NVL (ts.cancelled_flag, 'N') cancelled_flag
FROM jtf_task_statuses_b ts
WHERE ts.task_status_id = p_task_status_id;
SAVEPOINT csf_update_task;
fnd_message.set_name ('CSF', 'CSF_TASK_UPDATE_NOT_ALLOWED');
csf_task_assignments_pub.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 => l_task_info.task_assignment_id
, p_object_version_number => l_task_info.object_version_number
, p_object_capacity_id => l_trip_id
, p_update_task => fnd_api.g_false
, x_task_object_version_number => l_task_object_version
, x_task_status_id => l_task_status_id
);
jtf_tasks_pub.update_task(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, 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 => p_task_id
, p_object_version_number => p_object_version_number
, p_task_number => p_task_number
, p_task_name => p_task_name
, p_description => p_description
, p_task_status_id => p_task_status_id
, p_planned_start_date => p_planned_start_date
, p_planned_end_date => p_planned_end_date
, p_scheduled_start_date => l_new_start_date
, p_scheduled_end_date => l_new_end_date
, p_actual_start_date => p_actual_start_date
, p_actual_end_date => p_actual_end_date
, p_timezone_id => p_timezone_id
, p_source_object_type_code => p_source_object_type_code
, p_source_object_id => p_source_object_id
, p_source_object_name => p_source_object_name
, p_task_type_id => p_task_type_id
, p_task_priority_id => p_task_priority_id
, p_owner_type_code => p_owner_type_code
, p_owner_id => p_owner_id
, p_owner_territory_id => p_owner_territory_id
, p_owner_status_id => p_owner_status_id
, p_assigned_by_id => p_assigned_by_id
, p_customer_id => p_customer_id
, p_cust_account_id => p_cust_account_id
, p_address_id => p_address_id
, p_location_id => p_location_id
, p_duration => p_duration
, p_duration_uom => p_duration_uom
, p_planned_effort => p_planned_effort
, p_planned_effort_uom => p_planned_effort_uom
, p_actual_effort => p_actual_effort
, p_actual_effort_uom => p_actual_effort_uom
, p_percentage_complete => p_percentage_complete
, p_reason_code => p_reason_code
, p_private_flag => p_private_flag
, p_publish_flag => p_publish_flag
, p_restrict_closure_flag => p_restrict_closure_flag
, 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_date_selected => p_date_selected
, p_category_id => p_category_id
, p_multi_booked_flag => p_multi_booked_flag
, p_milestone_flag => p_milestone_flag
, p_holiday_flag => p_holiday_flag
, p_billable_flag => p_billable_flag
, p_bound_mode_code => p_bound_mode_code
, p_soft_bound_flag => p_soft_bound_flag
, p_workflow_process_id => p_workflow_process_id
, p_notification_flag => p_notification_flag
, p_notification_period => p_notification_period
, p_notification_period_uom => p_notification_period_uom
, p_alarm_start => p_alarm_start
, p_alarm_start_uom => p_alarm_start_uom
, p_alarm_on => p_alarm_on
, p_alarm_count => p_alarm_count
, p_alarm_fired_count => p_alarm_fired_count
, p_alarm_interval => p_alarm_interval
, p_alarm_interval_uom => p_alarm_interval_uom
, 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_show_on_calendar => p_show_on_calendar
, p_costs => p_costs
, p_currency_code => p_currency_code
, p_escalation_level => p_escalation_level
, p_parent_task_id => p_parent_task_id
, p_parent_task_number => p_parent_task_number
, p_task_split_flag => p_task_split_flag
, p_child_position => p_child_position
, p_child_sequence_num => p_child_sequence_num
, p_enable_workflow => p_enable_workflow
, p_abort_workflow => p_abort_workflow
);
ROLLBACK TO csf_update_task;
ROLLBACK TO csf_update_task;
ROLLBACK TO csf_update_task;
END update_task;
SELECT t.task_number
, t.task_status_id
, t.object_version_number
, t.scheduled_start_date
, t.scheduled_end_date
, NVL (t.task_confirmation_status, 'N') task_confirmation_status
, ta.task_assignment_id
, ta.object_version_number ta_object_version_number
, ta.object_capacity_id
, ta.assignment_status_id
, cac.status trip_status
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 NVL (ts.assigned_flag, 'N') <> 'Y'
AND NVL (ts.working_flag, 'N') <> 'Y'
AND NVL (ts.completed_flag, 'N') <> 'Y'
AND NVL (ts.closed_flag, 'N') <> 'Y'
AND NVL (ts.cancelled_flag, 'N') <> 'Y'
AND cac.object_capacity_id (+) = ta.object_capacity_id;
SELECT t.task_number
, t.task_status_id
, t.object_version_number
, t.scheduled_start_date
, t.scheduled_end_date
, NVL (t.task_confirmation_status, 'N') task_confirmation_status
, ta.task_assignment_id
, ta.object_version_number ta_object_version_number
, ta.object_capacity_id
, ta.assignment_status_id
, cac.status trip_status
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 NVL (ts.assigned_flag, 'N') <> 'Y'
AND NVL (ts.working_flag, 'N') <> 'Y'
AND NVL (ts.completed_flag, 'N') <> 'Y'
AND NVL (ts.closed_flag, 'N') <> 'Y'
AND NVL (ts.cancelled_flag, 'N') <> 'Y'
AND cac.object_capacity_id (+) = ta.object_capacity_id
AND ta.resource_id = p_resource_id
AND ta.resource_type_code = p_resource_type;
csf_task_assignments_pub.update_assignment_status(
p_api_version => 1.0
, p_validation_level => fnd_api.g_valid_level_none
, 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_assignment_id => l_task_details.task_assignment_id
, p_assignment_status_id => l_new_status_id
, p_object_version_number => l_task_details.ta_object_version_number
, x_task_object_version_number => l_task_details.object_version_number
, x_task_status_id => l_task_details.task_status_id
);
SELECT where_clause
FROM csf_dc_queries_b
WHERE query_id = p_query_id;
SELECT t.task_id
, cac.status trip_status
FROM jtf_tasks_b t
, jtf_task_assignments ta
, jtf_task_statuses_b ts
, cac_sr_object_capacity cac
WHERE t.parent_task_id = p_parent_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 -- made this outer join for bug 6940526
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND NVL(ts.cancelled_flag, 'N') <> 'Y'
ORDER BY 1 DESC;
SELECT count(task_id)
FROM jtf_task_assignments a
, jtf_task_statuses_b b
WHERE a.task_id = p_task_id
AND a.assignment_status_id = b.task_status_id
AND nvl(b.cancelled_flag ,'N') <> 'Y';
l_query := ' SELECT DISTINCT NVL(csf_ct_tasks.parent_task_id, csf_ct_tasks.task_id) task_id
, (SELECT t1.task_split_flag
FROM jtf_tasks_b t1
WHERE t1.task_id = NVL(csf_ct_tasks.parent_task_id, csf_ct_tasks.task_id)) task_split_flag
FROM csf_ct_tasks
WHERE ' || l_where_clause || ' ORDER BY 1 DESC';
l_query := 'SELECT DISTINCT NVL(t.parent_task_id, t.task_id) task_id
, t.task_split_flag
FROM jtf_tasks_b t
, jtf_task_assignments ta
, jtf_task_statuses_b ts
WHERE ta.resource_id = :1
AND ta.resource_type_code = :2
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, ''N'') = ''N''
AND NVL(ts.completed_flag, ''N'') = ''N''
AND NVL(ts.cancelled_flag, ''N'') = ''N''
AND ta.booking_start_date BETWEEN :3 and :4
AND t.task_id = ta.task_id
AND t.task_type_id NOT IN (20,21)
AND NVL(t.deleted_flag, ''N'') <> ''Y''
AND t.source_object_type_code = ''SR''
ORDER BY 1 DESC';
l_query := 'SELECT NVL(t.parent_task_id, t.task_id) task_id
, task_split_flag
FROM jtf_tasks_b t
WHERE t.task_id = :1';
l_query := 'SELECT NVL(t.parent_task_id, t.task_id) task_id
, task_split_flag
FROM cac_sr_object_capacity cac
, jtf_task_assignments ta
, jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE cac.object_capacity_id = :1
AND ta.resource_id = cac.object_id
AND ta.resource_type_code = cac.object_type
AND ta.booking_start_date <= (cac.end_date_time + ' || g_overtime || ')
AND ta.booking_end_date >= cac.start_date_time
AND ts.task_status_id = ta.assignment_status_id
AND NVL(ts.closed_flag, ''N'') = ''N''
AND NVL(ts.completed_flag, ''N'') = ''N''
AND NVL(ts.cancelled_flag, ''N'') = ''N''
AND t.task_id = ta.task_id
AND t.task_type_id NOT IN (20,21)
AND NVL(t.deleted_flag, ''N'') <> ''Y''
AND t.source_object_type_code = ''SR''
ORDER BY 1 DESC';
* Updates the Task Information of the Parent Task by considering the current information
* of all the Children.
*
* The various attributes updated are
* 1. Task Status Propagation from Child Task to Parent Task
* 2. Scheduled Start Date of the Task
* 3. Scheduled End Date of the Task
* 4. Actual Start Date of the Task
* 5. Actual Effort of the Task
*
* Task Status Propagation
* The Bitcodes of each Task Status is defined above. The Bitcodes have been
* carefully chosen so that AND of the Bitcodes of all the Child Tasks will
* give the Bitcode of the Task Status the Parent should ultimately take.
*
* For Example
* Case#1:
* Let us assume there is a Parent Task P with three children C1, C2 and C3.
*
* C1 Task Status = Closed : Bitcode = 11001
* C2 Task Status = Working : Bitcode = 00001
* C3 Task Status = Assigned : Bitcode = 00011
*
* We expect the Parent Task to be in Working Status. BIT AND of all the Child
* Tasks will result in 00001 which translates to Working.
*
* Case#2:
* Let us assume there is a Parent Task P with three children C1, C2 and C3.
*
* C1 Task Status = Closed : Bitcode = 11001
* C2 Task Status = Closed : Bitcode = 11001
* C3 Task Status = Assigned : Bitcode = 00011
*
* Since one of the Child Tasks is already Closed, it means that the Technician has
* started to work on the Parent Task. So the Task Status should be Working. The BIT AND
* of all the child tasks results in the same thing even though none of the child task is
* in Working status.
*
* Case#3:
* Bitcode Transition will fail however when On-Hold comes into picture. If there are
* any Child Tasks in On-Hold Status and all others are in Closed, Cancelled or Completed
* status, then the Parent should be updated to On-Hold status. Even if any one of the
* Child Task is in Working/Assigned/Planned status, then the Parent Task should
* be updated to Working/Assigned/Planned (in the same order of preference). Thus any
* Bitcode assigned to On-Hold will not work and it has to be treated separately.
*
* Since there are Default Task Profiles for Planned, Asssigned, Cancelled and Working
* a Global PLSQL Table is maintained to cache that information. But we might require
* statuses corresponding to Closed, Completed and On-Hold. These are retrieved from the
* Child Tasks and so another Local Table is also maintained to store these information
* which will go out of scope once the procedure completes. Note that In-Planning
* is not used as a task cant be a Parent if its in In-Planning.
*
* For more information refer to Bug#4032201.
*
*
* Scheduled Start Date will the minimum start date of all the children.
* Scheduled End Date will the maximum end date of all the children.
* Actual Start Date will the minimum start date of all the children.
* Actual End Date will the maximum end date of all the children.
* Actual Effort will be the sum of all the Actuals of Children after converting
* to minutes.
*
* @param p_api_version API Version (1.0)
* @param p_init_msg_list Initialize Message List
* @param p_commit Commit the Work
* @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_parent_task_id Task Identifier of the Parent Task.
* @param p_parent_version_number Object Version of Parent Task
* @param p_planned_start_date Planned start date of Parent Task.
* @param p_planned_end_date Planned end date of Parent Task.
*/
PROCEDURE sync_parent_with_child(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_parent_task_id IN NUMBER
, p_parent_version_number IN OUT NOCOPY NUMBER
, p_planned_start_date IN DATE
, p_planned_end_date IN DATE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'SYNC_PARENT_WITH_CHILD';
SELECT t.task_status_id
, t.actual_start_date
, t.actual_end_date
, t.scheduled_start_date
, t.scheduled_end_date
, t.planned_start_date
, t.planned_end_date
, csf_util_pvt.convert_to_minutes(t.actual_effort, t.actual_effort_uom) actual_effort
FROM jtf_tasks_b t
WHERE t.task_id = p_parent_task_id
AND NVL(t.deleted_flag, 'N') <> 'Y';
SELECT g_inplanning task_status_id
, MIN(t.scheduled_start_date) scheduled_start_date
, MAX(t.scheduled_end_date) scheduled_end_date
, MIN(t.actual_start_date) actual_start_date
, MAX(t.actual_end_date) actual_end_date
, SUM(csf_util_pvt.convert_to_minutes(t.actual_effort, t.actual_effort_uom)) actual_effort
FROM jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE t.parent_task_id = p_parent_task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND ts.task_status_id = t.task_status_id
AND NVL(ts.cancelled_flag, 'N') <> 'Y';
SELECT t.task_id
, t.task_status_id
, NVL(ts.schedulable_flag, 'N') schedulable_flag
, NVL(ts.assigned_flag, 'N') assigned_flag
, NVL(ts.working_flag, 'N') working_flag
, NVL(ts.completed_flag, 'N') completed_flag
, NVL(ts.closed_flag, 'N') closed_flag
, NVL(ts.on_hold_flag, 'N') on_hold_flag
, NVL(ts.rejected_flag, 'N') rejected_flag
, NVL(ts.cancelled_flag, 'N') cancelled_flag
, NVL(ts.accepted_flag, 'N') accepted_flag
, NVL(ts.assignment_status_flag, 'N') assignment_status_flag
, 0 status_bitcode
FROM jtf_tasks_b t
, jtf_task_statuses_b ts
WHERE t.parent_task_id = p_parent_task_id
AND ts.task_status_id = t.task_status_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
ORDER BY t.task_id;
l_update_parent BOOLEAN;
l_update BOOLEAN := FALSE;
l_update := TRUE;
l_update := FALSE;
IF l_status_bitcode_map_tbl.EXISTS(l_pri_sts_bitcode) and not (l_update) THEN
l_new_parent_info.task_status_id := l_status_bitcode_map_tbl(l_pri_sts_bitcode);
ELSIF l_update THEN
l_new_parent_info.task_status_id := l_child_status;
l_update_parent :=
l_curr_parent_info.task_status_id <> l_new_parent_info.task_status_id
OR ( NVL(l_curr_parent_info.scheduled_start_date, fnd_api.g_miss_date)
<> NVL(l_new_parent_info.scheduled_start_date, fnd_api.g_miss_date) )
OR ( NVL(l_curr_parent_info.scheduled_end_date, fnd_api.g_miss_date)
<> NVL(l_new_parent_info.scheduled_end_date, fnd_api.g_miss_date) )
OR ( NVL(l_curr_parent_info.actual_start_date, fnd_api.g_miss_date)
<> NVL(l_new_parent_info.actual_start_date, fnd_api.g_miss_date) )
OR ( NVL(l_curr_parent_info.actual_end_date, fnd_api.g_miss_date)
<> NVL(l_new_parent_info.actual_end_date, fnd_api.g_miss_date) )
OR ( NVL(l_curr_parent_info.planned_start_date, fnd_api.g_miss_date)
<> NVL(p_planned_start_date, fnd_api.g_miss_date) )
OR ( NVL(l_curr_parent_info.planned_end_date, fnd_api.g_miss_date)
<> NVL(p_planned_end_date, fnd_api.g_miss_date) )
OR ( NVL(l_curr_parent_info.actual_effort, -1)
<> NVL(l_new_parent_info.actual_effort, -1) );
IF l_update_parent THEN
IF l_new_parent_info.actual_effort IS NOT NULL THEN
l_actual_effort_uom := csf_util_pvt.get_uom_minutes;
jtf_tasks_pub.update_task (
p_api_version => 1.0
, p_init_msg_list => p_init_msg_list
, 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 => p_parent_task_id
, p_object_version_number => p_parent_version_number
, p_task_status_id => l_new_parent_info.task_status_id
, p_scheduled_start_date => l_new_parent_info.scheduled_start_date
, p_scheduled_end_date => l_new_parent_info.scheduled_end_date
, p_planned_start_date => p_planned_start_date
, p_planned_end_date => p_planned_end_date
, p_actual_start_date => l_new_parent_info.actual_start_date
, p_actual_end_date => l_new_parent_info.actual_end_date
, p_actual_effort => l_new_parent_info.actual_effort
, p_actual_effort_uom => l_actual_effort_uom
, p_task_split_flag => 'M'
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
* Updates the Attributes of the Child Tasks by considering the Parent Task.
*
* @param p_api_version API Version (1.0)
* @param p_init_msg_list Initialize Message List
* @param p_commit Commit the Work
* @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_parent_task_id Task Identifier of the Parent Task.
*/
PROCEDURE sync_child_from_parent(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_parent_task_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'SYNC_CHILD_FROM_PARENT';
SELECT t.task_id
, t.object_version_number
, NVL(t.child_position, '@@') child_position
, NVL(t.child_sequence_num, -1) child_sequence_num
, RANK() OVER (ORDER BY t.scheduled_start_date, t.scheduled_end_date,nvl(t.child_sequence_num,-1)) correct_seq_num
, LEAD (t.task_id) OVER (ORDER BY t.scheduled_start_date, t.scheduled_end_date,nvl(t.child_sequence_num,-1)) next_task_id
FROM jtf_tasks_b t ,jtf_task_statuses_b ts
WHERE t.parent_task_id = p_parent_task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND ts.task_status_id = t.task_status_id
AND NVL(ts.cancelled_flag, 'N') <> 'Y';
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 => v_child_task.task_id
, p_task_split_flag => fnd_api.g_miss_char
, p_object_version_number => v_child_task.object_version_number
, p_child_sequence_num => v_child_task.correct_seq_num
, p_child_position => l_child_position
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
SELECT t.task_id
, t.task_status_id
, t.task_split_flag
, t.object_version_number
, t.scheduled_start_date
, t.scheduled_end_date
, NVL( ( SELECT 'Y'
FROM jtf_task_assignments ta, jtf_task_statuses_b ats
WHERE ta.task_id = p_task_id
AND ta.assignment_status_id = ats.task_status_id
AND NVL(ats.cancelled_flag, 'N') <> 'Y'
AND ROWNUM = 1
), 'N'
) is_scheduled
FROM jtf_tasks_b t
WHERE t.task_id = p_task_id;
SELECT ta.resource_id
, ta.resource_type_code
FROM jtf_task_assignments ta
WHERE ta.task_assignment_id = p_old_task_assignment_id;
csf_task_assignments_pub.update_task_assignment (
p_api_version => 1.0
, 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 => p_old_task_assignment_id
, p_object_version_number => x_ta_object_version_number
, p_assignment_status_id => l_assignment_status_id
, p_object_capacity_id => p_object_capacity_id
, 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_update_task => fnd_api.g_false
, x_task_object_version_number => l_task_info.object_version_number
, x_task_status_id => l_task_info.task_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 => p_task_id
, p_object_version_number => p_object_version_number
, p_task_status_id => p_task_status_id
, p_scheduled_start_date => p_scheduled_start_date
, p_scheduled_end_date => p_scheduled_end_date
, p_planned_start_date => p_planned_start_date
, p_planned_end_date => p_planned_end_date
, p_planned_effort => l_planned_effort
, p_planned_effort_uom => l_planned_effort_uom
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
, p_update_task => fnd_api.g_false
, x_task_assignment_id => x_task_assignment_id
, x_ta_object_version_number => x_ta_object_version_number
, x_task_object_version_number => p_object_version_number
, x_task_status_id => l_task_info.task_status_id
);
SELECT t.task_id
, t.task_status_id
, t.task_split_flag
, source_object_type_code
, scheduled_start_date
, scheduled_end_date
, ta.assignment_status_id
, ta.object_capacity_id
FROM jtf_tasks_b t , jtf_task_assignments ta
WHERE t.task_id = p_task_id
AND ta.task_id = t.task_id
AND ta.task_assignment_id = p_task_assignment_id;
SELECT NVL (ts.closed_flag, 'N') closed_flag
, NVL (ts.cancelled_flag, 'N') cancelled_flag
FROM jtf_task_statuses_b ts
WHERE ts.task_status_id = p_task_status_id;
csf_task_assignments_pub.update_task_assignment (
p_api_version => 1.0
, 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 => p_task_assignment_id
, p_assignment_status_id => NVL(p_assignment_status_id, g_cancelled)
, p_object_version_number => p_ta_object_version_number
, p_object_capacity_id => NULL
, p_update_task => fnd_api.g_false
, x_task_object_version_number => p_object_version_number
, x_task_status_id => l_task_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 => p_task_id
, p_object_version_number => p_object_version_number
, p_task_status_id => p_task_status_id
, p_scheduled_start_date => l_task_info.scheduled_start_date
, p_scheduled_end_date => l_task_info.scheduled_end_date
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
csf_tasks_pub.delete_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 => p_task_id
, p_object_version_number => p_object_version_number
);
PROCEDURE update_task_and_assignment(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_scheduled_start_date IN DATE
, p_scheduled_end_date IN DATE
, p_task_assignment_id IN NUMBER
, p_ta_object_version_number IN OUT NOCOPY NUMBER
, p_sched_travel_distance IN NUMBER
, p_sched_travel_duration IN NUMBER
, p_sched_travel_duration_uom IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_AND_ASSIGNMENT';
SAVEPOINT csf_update_task_and_assignment;
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_ta_object_version_number
, 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_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
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 => p_task_id
, p_object_version_number => p_object_version_number
, p_scheduled_start_date => p_scheduled_start_date
, p_scheduled_end_date => p_scheduled_end_date
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
ROLLBACK TO csf_update_task_and_assignment;
ROLLBACK TO csf_update_task_and_assignment;
ROLLBACK TO csf_update_task_and_assignment;
END update_task_and_assignment;
PROCEDURE update_task_longer_than_shift(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_planned_start_date IN DATE
, p_planned_end_date IN DATE
, p_action IN PLS_INTEGER
, p_task_status_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_LONGER_THAN_SHIFT';
SELECT t.task_id
, t.task_status_id
, t.scheduled_start_date
, t.scheduled_end_date
, t.planned_effort
, t.planned_effort_uom
, t.task_split_flag
FROM jtf_tasks_b t
WHERE t.task_id = p_task_id;
SELECT t.task_id
, t.object_version_number task_ovn
, t.task_status_id
, ta.task_assignment_id
, ta.object_version_number task_assignment_ovn
, ta.assignment_status_id
FROM jtf_tasks_b t ,jtf_task_statuses_b ts ,jtf_task_assignments ta
WHERE t.parent_task_id = p_task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND ts.task_status_id = t.task_status_id
AND NVL(ts.cancelled_flag, 'N') <> 'Y'
AND t.task_id = ta.task_id
AND ta.assignment_status_id = ts.task_status_id;
SAVEPOINT update_task_longer_than_shift;
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 => p_task_id
, p_object_version_number => p_object_version_number
, p_task_status_id => p_task_status_id
, p_scheduled_start_date => NULL
, p_scheduled_end_date => NULL
, p_task_split_flag => NULL
, p_actual_start_date => NULL
, p_actual_end_date => NULL
, p_actual_effort => NULL
, p_actual_effort_uom => NULL
, p_enable_workflow => fnd_api.g_miss_char
, p_abort_workflow => fnd_api.g_miss_char
);
ROLLBACK TO update_task_longer_than_shift;
ROLLBACK TO update_task_longer_than_shift;
ROLLBACK TO update_task_longer_than_shift;
END update_task_longer_than_shift;
SELECT t.task_name
, t.description
, t.task_type_id
, t.task_priority_id
, t.address_id
, t.customer_id
, t.source_object_type_code
, t.source_object_id
, t.source_object_name
, t.owner_type_code
, t.owner_id
, t.task_confirmation_status
, t.task_confirmation_counter
, t.cust_account_id
, t.planned_effort_uom
FROM jtf_tasks_vl t
WHERE t.task_id = p_parent_task_id;
* Updates the customer confirmation for normal/child/parent task
*
* @param p_api_version API Version (1.0)
* @param p_init_msg_list Initialize Message List
* @param p_commit Commit the Work
* @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_id Task to be processed
* @param p_object_version_number Object version of input task
* @param p_action Whether Required/Received/Not Required
* @param p_initiated Whether Customer or Dispatcher
*/
PROCEDURE update_cust_confirmation(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_task_id IN NUMBER
, p_object_version_number IN OUT NOCOPY NUMBER
, p_action IN PLS_INTEGER
, p_initiated IN PLS_INTEGER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CUST_CONFIRMATION';
SELECT t.task_id
, t.task_split_flag
, t.parent_task_id
, t.task_confirmation_status
FROM jtf_tasks_b t
WHERE t.task_id = p_task_id;
SELECT jtb.task_id
, jtb.object_version_number
FROM jtf_task_statuses_vl ts, jtf_tasks_b jtb
WHERE jtb.parent_task_id = p_task_id
AND ts.task_status_id = jtb.task_status_id
AND jtb.task_split_flag = 'D'
AND ( NVL(ts.on_hold_flag, 'N') = 'Y'
OR NVL(ts.working_flag, 'N') = 'Y'
OR NVL(ts.schedulable_flag, 'N') = 'Y'
OR ( NVL(ts.assigned_flag, 'N') = 'Y'
AND NVL(ts.closed_flag, 'N') <> 'Y'
AND NVL(ts.approved_flag, 'N') <> 'Y'
AND NVL(ts.completed_flag, 'N') <> 'Y'
AND NVL(ts.rejected_flag, 'N') <> 'Y' ))
UNION
SELECT t.task_id
, t.object_version_number
FROM jtf_tasks_b t
WHERE task_id = p_task_id;
SAVEPOINT csf_update_cust_confirmation;
ROLLBACK TO csf_update_cust_confirmation;
ROLLBACK TO csf_update_cust_confirmation;
ROLLBACK TO csf_update_cust_confirmation;
END update_cust_confirmation;
SELECT ps.location_id
FROM hz_party_sites ps
WHERE ps.party_site_id = p_party_site_id;
SELECT NVL(t.location_id, ps.location_id)
FROM jtf_tasks_b t
, hz_party_sites ps
WHERE t.task_id = p_task_id
AND ps.party_site_id(+) = t.address_id;
SELECT l.address1
, l.address2
, l.address3
, l.address4
, l.postal_code
, l.city
, l.state
, l.province
, l.country
FROM hz_locations l
WHERE l.location_id = p_location_id;
SELECT l.address1
, l.address2
, l.address3
, l.address4
, l.postal_code
, l.city
, l.state
, l.province
, l.country
FROM hz_party_sites ps
, hz_locations l
WHERE ps.party_site_id = p_party_site_id
AND l.location_id = ps.location_id;
SELECT l.address1
, l.address2
, l.address3
, l.address4
, l.postal_code
, l.city
, l.state
, l.province
, l.country
FROM jtf_tasks_b t
, hz_party_sites ps
, hz_locations l
WHERE t.task_id = p_task_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id = NVL(t.location_id, ps.location_id);
SELECT NVL(p_planned_effort, planned_effort) planned_effort
, NVL(p_planned_effort_uom, planned_effort_uom) planned_effort_uom
, decode(p_task_split_flag, '@', task_split_flag, p_task_split_flag) task_split_flag
FROM jtf_tasks_b
WHERE task_id = p_task_id;
SELECT pc.person_last_name last_name
, pc.person_first_name first_name
, pc.person_title title
, tp.phone_id
FROM jtf_task_contacts tc
, jtf_party_all_contacts_v pc
, jtf_task_phones_v tp
WHERE tc.task_id = p_task_id
AND tc.contact_id IN (pc.party_id, pc.subject_party_id)
AND tp.task_contact_id (+) = tc.task_contact_id;
SELECT sub_last_name last_name
, sub_first_name first_name
, sub_title title
, contact_point_id
, party_id
, contact_type
FROM cs_sr_contact_points_v
WHERE incident_id = p_incident_id
AND primary_flag = 'Y';
SELECT cp.contact_point_type
, DECODE(cp.phone_country_code, '', '', NULL, '', cp.phone_country_code || '-' )
|| DECODE(cp.phone_area_code, '', '', NULL, '', cp.phone_area_code || '-')
|| cp.phone_number phone
, cp.phone_extension
, cp.email_address
FROM hz_contact_points cp
, ar_lookups ar
WHERE cp.contact_point_id = l_contact_point_id
AND cp.contact_point_type IN ('EMAIL', 'PHONE')
AND cp.phone_line_type = ar.lookup_code (+)
AND ar.lookup_type(+) = 'PHONE_LINE_TYPE';
SELECT p.last_name
, p.first_name
, p.title
, pp.phone_number
, p.email_address
FROM cs_hz_sr_contact_points sr_cp
, per_all_people_f p
, per_phones pp
, hr_lookups hrl
WHERE sr_cp.incident_id = p_incident_id
AND sr_cp.primary_flag = 'Y'
AND p.person_id = sr_cp.party_id
AND pp.phone_id(+) = sr_cp.contact_point_id
AND pp.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND hrl.lookup_code(+) = pp.phone_type
AND hrl.lookup_type(+) = 'PHONE_TYPE'
ORDER BY p.effective_end_date desc;
procedure update_personal_task(
p_api_version in number
, p_init_msg_list in varchar2
, p_commit in varchar2
, p_task_id in varchar2
, p_task_name in varchar2
, x_version in out nocopy number
, p_description in number
, p_task_type_id in number
, p_task_status_id in number
, p_task_priority_id in number
, p_owner_id in number
, p_owner_type_code in varchar2
, p_address_id in number
, p_customer_id in number
, p_planned_start_date in date
, p_planned_end_date in date
, p_scheduled_start_date in date
, p_scheduled_end_date in date
, p_source_object_type_code in varchar2
, p_planned_effort in number
, p_planned_effort_uom in varchar2
, p_bound_mode_code in varchar2
, p_soft_bound_flag in varchar2
, p_type in varchar2
, p_trip in number
, x_return_status out nocopy varchar2
, x_msg_count out nocopy number
, x_msg_data out nocopy varchar2
)
is
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PERSONAL_TASK';
select object_version_number
from jtf_tasks_b
where task_id =p_task;
select jta.task_assignment_id
from jtf_task_assignments jta,jtf_tasks_b jt
where jt.task_id=p_task_id
and jta.task_id=jt.task_id
and jt.source_object_type_code = 'TASK'
and jt.task_type_id not in (20,21);
SAVEPOINT csf_update_per_task;
csf_tasks_pub.update_task(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
,p_task_id => p_task_id
,p_object_version_number => x_version
, p_task_name => p_task_name
, p_description => p_description
, p_task_type_id => p_task_type_id
, p_task_status_id => p_task_status_id
, p_task_priority_id => p_task_priority_id
, p_owner_id => p_owner_id
, p_owner_type_code => p_owner_type_code
, p_address_id => p_address_id
, p_customer_id => p_customer_id
, p_location_id => l_location
, p_planned_start_date => p_planned_start_date
, p_planned_end_date => p_planned_end_date
, p_scheduled_start_date => p_scheduled_start_date
, p_scheduled_end_date => p_scheduled_end_date
, p_source_object_type_code => p_source_object_type_code
, p_planned_effort => p_planned_effort
, p_planned_effort_uom => p_planned_effort_uom
, p_bound_mode_code => p_bound_mode_code
, p_soft_bound_flag => p_soft_bound_flag
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
csf_task_assignments_pub.update_task_assignment(
p_api_version =>p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit =>p_commit
, x_return_status =>x_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
, p_task_assignment_id =>l_task_assignment_id
, p_object_version_number => x_version
, p_task_id =>p_task_id
, p_resource_type_code =>p_owner_type_code
, p_resource_id =>p_owner_id
, p_assignment_status_id => p_task_status_id
, p_actual_start_date =>p_scheduled_start_date
, p_actual_end_date =>p_scheduled_end_date
, p_object_capacity_id => p_trip
, p_task_name =>p_task_name
, x_task_object_version_number => l_obj_task
, x_task_status_id =>l_ts);
ROLLBACK TO csf_update_per_task;
ROLLBACK TO csf_update_per_task;
ROLLBACK TO csf_update_per_task;