[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select trim(MESSAGE_TEXT),substr(message_name,5,3)
from fnd_new_messages
where application_id=513
AND language_code =USERENV('LANG')
and substr(message_name,1,4) = 'CSF_'
and translate(substr(message_name,6,2),'0123456789','xxxxxxxxxx') ='xx'
order by message_name;
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 DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y')
FROM jtf_tasks_b t
, jtf_task_statuses_b s
WHERE t.task_id = b_task_id
AND t.task_type_id = 22
AND s.task_status_id = t.task_status_id
AND NVL(s.closed_flag, 'N') <> 'Y'
AND NVL(t.deleted_flag, 'N') <> 'Y';
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT /*+ ORDERED use_nl (a tb tt tl sb sl pi ps hl ft)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
tb.task_id
, tl.task_name
, tb.task_number
, tb.source_object_type_code
, tb.source_object_id
, tt.NAME task_type
, sl.NAME task_status
, a.resource_id
, a.resource_type_code resource_type
, tb.planned_start_date
, tb.planned_end_date
, scheduled_start_date
, scheduled_end_date
, a.actual_start_date
, a.actual_end_date
, a.sched_travel_duration
, a.sched_travel_duration_uom
, tb.customer_id party_id
, NVL(sb.assigned_flag, 'N') assigned_flag
, tb.task_type_id
, csf_tasks_pub.get_task_address(tb.task_id,tb.address_id,tb.location_id,'Y') small_address
, pi.party_name party_name
, a.actual_travel_duration
, a.actual_travel_duration_uom
, tz.ACTIVE_TIMEZONE_CODE ic_tz_code
, tz.ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
, tb.planned_effort ||' '|| tb.planned_effort_uom plan_effort
, tb.actual_effort ||' '|| tb.actual_effort_uom act_effort
FROM jtf_task_assignments a
, jtf_tasks_b tb
, jtf_task_types_tl tt
, jtf_tasks_tl tl
, jtf_task_statuses_b sb
, jtf_task_statuses_tl sl
, hz_party_sites ps
, hz_locations hl
, hz_parties pi
, fnd_timezones_b tz
WHERE a.task_id = p_task_id
AND resource_id = p_resource_id
AND resource_type_code = p_resource_type
AND tb.task_id = a.task_id
AND tt.LANGUAGE = l_language
AND tt.task_type_id = tb.task_type_id
AND sl.LANGUAGE = l_language
AND sb.task_status_id = a.assignment_status_id
AND sl.task_status_id = sb.task_status_id
AND tl.LANGUAGE = l_language
AND tl.task_id = tb.task_id
AND ps.party_site_id(+) = tb.address_id
AND hl.location_id(+) = ps.location_id
AND pi.party_id(+) = tb.customer_id
AND NVL(sb.cancelled_flag, 'N') <> 'Y'
AND tz.UPGRADE_TZ_ID(+) = hl.timezone_id;
SELECT /*+ ORDERED USE_NL */
i.customer_product_id
, i.current_serial_number
, si.concatenated_segments product_name
FROM cs_incidents_all_b i, mtl_system_items_kfv si
WHERE si.inventory_item_id(+) = i.inventory_item_id
AND si.organization_id(+) = i.inv_organization_id
AND i.incident_id = b_incident_id;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT 'Y' required
FROM csp_requirement_headers
WHERE task_id = b_task_id;
SELECT serial_number
, lot_number
FROM csi_item_instances
WHERE instance_id = b_customer_product_id;
Select TIME_ZONE
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = p_resource_id
;
SELECT ACTIVE_TIMEZONE_CODE,ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
FROM fnd_timezones_vl ft
WHERE UPGRADE_TZ_ID =l_Res_TimeZone_id;
SELECT object_capacity_id
FROM jtf_task_assignments ja
, jtf_tasks_b jb
WHERE ja.task_id = jb.task_id
AND ja.task_id = p_task_id
AND jb.task_type_id not in (20,21)
AND ja.object_capacity_id is not null
and nvl(jb.deleted_flag,'N') <> 'Y';
SELECT ja.task_id
FROM jtf_task_assignments ja ,
jtf_tasks_b jb ,
jtf_task_statuses_b js
WHERE ja.task_id = jb.task_id
AND js.task_status_id = jb.task_status_id
AND ja.object_capacity_id = l_capacity
AND ja.resource_id = p_resource_id
AND NVL(js.cancelled_flag,'N') <> 'Y'
AND NVL(js.rejected_flag,'N') <> 'Y'
AND NVL(jb.deleted_flag,'N') <> 'Y'
AND jb.task_type_id NOT IN (20,21)
AND ja.object_capacity_id IS NOT NULL
ORDER BY NVL(ja.actual_start_date,jb.scheduled_start_date);
SELECT ja.task_id
, ja.actual_travel_duration
, ja.sched_travel_duration
, ja.actual_travel_duration_uom
, ja.sched_travel_duration_uom
FROM jtf_task_assignments ja ,
jtf_tasks_b jb
WHERE ja.task_id = jb.task_id
AND jb.task_type_id IN (21)
AND ja.task_id= p_task_id;
SELECT territory_id
FROM csf_dc_resources_v
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type;
SELECT field_name
FROM csf_gantt_chart_setup
WHERE user_id = fnd_global.user_id
AND setup_type = 'TOOLTIP'
ORDER BY seq_no;
SELECT field_name
FROM csf_gantt_chart_setup
WHERE user_id = -1
AND setup_type = 'TOOLTIP'
ORDER BY seq_no;
SELECT /*+ ORDERED use_nl (a tb tt tl sb sl pi ps hl ft)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
tb.task_id
, tl.task_name
, tb.task_number
, tb.source_object_type_code
, tb.source_object_id
, tt.NAME task_type
, sl.NAME task_status
, a.resource_id
, a.resource_type_code resource_type
, tb.planned_start_date
, tb.planned_end_date
, scheduled_start_date
, scheduled_end_date
, a.actual_start_date
, a.actual_end_date
, a.sched_travel_duration
, a.sched_travel_duration_uom
, tb.customer_id party_id
, NVL(sb.assigned_flag, 'N') assigned_flag
, tb.task_type_id
, csf_tasks_pub.get_task_address(tb.task_id,tb.address_id,tb.location_id,'Y') small_address
, pi.party_name party_name
, a.actual_travel_duration
, a.actual_travel_duration_uom
, tz.ACTIVE_TIMEZONE_CODE ic_tz_code
, tz.ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
, tb.planned_effort ||' '|| tb.planned_effort_uom plan_effort
, tb.actual_effort ||' '|| tb.actual_effort_uom act_effort
FROM jtf_task_assignments a
, jtf_tasks_b tb
, jtf_task_types_tl tt
, jtf_tasks_tl tl
, jtf_task_statuses_b sb
, jtf_task_statuses_tl sl
, hz_party_sites ps
, hz_locations hl
, hz_parties pi
, fnd_timezones_b tz
WHERE a.task_id = p_task_id
AND resource_id = p_resource_id
AND resource_type_code = p_resource_type
AND tb.task_id = a.task_id
AND tt.LANGUAGE = l_language
AND tt.task_type_id = tb.task_type_id
AND sl.LANGUAGE = l_language
AND sb.task_status_id = a.assignment_status_id
AND sl.task_status_id = sb.task_status_id
AND tl.LANGUAGE = l_language
AND tl.task_id = tb.task_id
AND ps.party_site_id(+) = tb.address_id
AND hl.location_id(+) = ps.location_id
AND pi.party_id(+) = tb.customer_id
AND NVL(sb.cancelled_flag, 'N') <> 'Y'
AND tz.UPGRADE_TZ_ID(+) = hl.timezone_id;
SELECT /*+ ORDERED USE_NL */
i.customer_product_id
, i.current_serial_number
, si.concatenated_segments product_name
FROM cs_incidents_all_b i, mtl_system_items_kfv si
WHERE si.inventory_item_id(+) = i.inventory_item_id
AND si.organization_id(+) = i.inv_organization_id
AND i.incident_id = b_incident_id;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT 'Y' required
FROM csp_requirement_headers
WHERE task_id = b_task_id;
SELECT serial_number
, lot_number
FROM csi_item_instances
WHERE instance_id = b_customer_product_id;
Select TIME_ZONE
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = p_resource_id
;
SELECT ACTIVE_TIMEZONE_CODE,ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
FROM fnd_timezones_vl ft
WHERE UPGRADE_TZ_ID =l_Res_TimeZone_id;
SELECT object_capacity_id
FROM jtf_task_assignments ja
, jtf_tasks_b jb
WHERE ja.task_id = jb.task_id
AND ja.task_id = p_task_id
AND jb.task_type_id not in (20,21)
AND ja.object_capacity_id is not null
and nvl(jb.deleted_flag,'N') <> 'Y';
SELECT ja.task_id
FROM jtf_task_assignments ja ,
jtf_tasks_b jb ,
jtf_task_statuses_b js
WHERE ja.task_id = jb.task_id
AND js.task_status_id = jb.task_status_id
AND ja.object_capacity_id = l_capacity
AND ja.resource_id = p_resource_id
AND NVL(js.cancelled_flag,'N') <> 'Y'
AND NVL(js.rejected_flag,'N') <> 'Y'
AND NVL(jb.deleted_flag,'N') <> 'Y'
AND jb.task_type_id NOT IN (20,21)
AND ja.object_capacity_id IS NOT NULL
ORDER BY NVL(ja.actual_start_date,jb.scheduled_start_date);
SELECT ja.task_id
, ja.actual_travel_duration
, ja.sched_travel_duration
, ja.actual_travel_duration_uom
, ja.sched_travel_duration_uom
FROM jtf_task_assignments ja ,
jtf_tasks_b jb
WHERE ja.task_id = jb.task_id
AND jb.task_type_id IN (21)
AND ja.task_id= p_task_id;
SELECT territory_id
FROM csf_dc_resources_v
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type;
SELECT /*+ ORDERED use_nl (a tb tt tl sb sl pi ps hl ft)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
tb.task_id
, tl.task_name
, tb.task_number
, tb.source_object_type_code
, tb.source_object_id
, tt.NAME task_type
, sl.NAME task_status
, a.resource_id
, a.resource_type_code resource_type
, tb.planned_start_date
, tb.planned_end_date
, scheduled_start_date
, scheduled_end_date
, a.actual_start_date
, a.actual_end_date
, a.sched_travel_duration
, a.sched_travel_duration_uom
, tb.customer_id party_id
, NVL(sb.assigned_flag, 'N') assigned_flag
, tb.task_type_id
, csf_tasks_pub.get_task_address(tb.task_id,tb.address_id,tb.location_id,'Y') small_address
, pi.party_name party_name
, a.actual_travel_duration
, a.actual_travel_duration_uom
, tz.ACTIVE_TIMEZONE_CODE ic_tz_code
, tz.ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
, tb.planned_effort ||' '|| tb.planned_effort_uom plan_effort
, tb.actual_effort ||' '|| tb.actual_effort_uom act_effort
FROM jtf_task_assignments a
, jtf_tasks_b tb
, jtf_task_types_tl tt
, jtf_tasks_tl tl
, jtf_task_statuses_b sb
, jtf_task_statuses_tl sl
, hz_party_sites ps
, hz_locations hl
, hz_parties pi
, fnd_timezones_b tz
WHERE a.task_id = p_task_id
AND resource_id = p_resource_id
AND resource_type_code = p_resource_type
AND tb.task_id = a.task_id
AND tt.LANGUAGE = l_language
AND tt.task_type_id = tb.task_type_id
AND sl.LANGUAGE = l_language
AND sb.task_status_id = a.assignment_status_id
AND sl.task_status_id = sb.task_status_id
AND tl.LANGUAGE = l_language
AND tl.task_id = tb.task_id
AND ps.party_site_id(+) = tb.address_id
AND hl.location_id(+) = ps.location_id
AND pi.party_id(+) = tb.customer_id
AND NVL(sb.cancelled_flag, 'N') <> 'Y'
AND tz.UPGRADE_TZ_ID(+) = hl.timezone_id;
SELECT /*+ ORDERED USE_NL */
i.customer_product_id
, i.current_serial_number
, si.concatenated_segments product_name
FROM cs_incidents_all_b i, mtl_system_items_kfv si
WHERE si.inventory_item_id(+) = i.inventory_item_id
AND si.organization_id(+) = i.inv_organization_id
AND i.incident_id = b_incident_id;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT 'Y' required
FROM csp_requirement_headers
WHERE task_id = b_task_id;
SELECT serial_number
, lot_number
FROM csi_item_instances
WHERE instance_id = b_customer_product_id;
Select TIME_ZONE
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = p_resource_id
;
SELECT ACTIVE_TIMEZONE_CODE,ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
FROM fnd_timezones_vl ft
WHERE UPGRADE_TZ_ID =l_Res_TimeZone_id;
SELECT territory_id
FROM csf_dc_resources_v
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type;
SELECT field_name
FROM csf_gantt_chart_setup
WHERE user_id = fnd_global.user_id
AND setup_type = 'TOOLTIP'
ORDER BY seq_no;
SELECT /*+ ORDERED use_nl (a tb tt tl sb sl pi ps hl ft)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
tb.task_id
, tl.task_name
, tb.task_number
, tb.source_object_type_code
, tb.source_object_id
, tt.NAME task_type
, sl.NAME task_status
, a.resource_id
, a.resource_type_code resource_type
, tb.planned_start_date
, tb.planned_end_date
, scheduled_start_date
, scheduled_end_date
, a.actual_start_date
, a.actual_end_date
, a.sched_travel_duration
, a.sched_travel_duration_uom
, tb.customer_id party_id
, NVL(sb.assigned_flag, 'N') assigned_flag
, tb.task_type_id
, csf_tasks_pub.get_task_address(tb.task_id,tb.address_id,tb.location_id,'Y') small_address
, pi.party_name party_name
, a.actual_travel_duration
, a.actual_travel_duration_uom
, tz.ACTIVE_TIMEZONE_CODE ic_tz_code
, tz.ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
, tb.planned_effort ||' '|| tb.planned_effort_uom plan_effort
, tb.actual_effort ||' '|| tb.actual_effort_uom act_effort
FROM jtf_task_assignments a
, jtf_tasks_b tb
, jtf_task_types_tl tt
, jtf_tasks_tl tl
, jtf_task_statuses_b sb
, jtf_task_statuses_tl sl
, hz_party_sites ps
, hz_locations hl
, hz_parties pi
, fnd_timezones_b tz
WHERE a.task_id = p_task_id
AND resource_id = p_resource_id
AND resource_type_code = p_resource_type
AND tb.task_id = a.task_id
AND tt.LANGUAGE = l_language
AND tt.task_type_id = tb.task_type_id
AND sl.LANGUAGE = l_language
AND sb.task_status_id = a.assignment_status_id
AND sl.task_status_id = sb.task_status_id
AND tl.LANGUAGE = l_language
AND tl.task_id = tb.task_id
AND ps.party_site_id(+) = tb.address_id
AND hl.location_id(+) = ps.location_id
AND pi.party_id(+) = tb.customer_id
AND NVL(sb.cancelled_flag, 'N') <> 'Y'
AND tz.UPGRADE_TZ_ID(+) = hl.timezone_id;
SELECT /*+ ORDERED USE_NL */
i.customer_product_id
, i.current_serial_number
, si.concatenated_segments product_name
FROM cs_incidents_all_b i, mtl_system_items_kfv si
WHERE si.inventory_item_id(+) = i.inventory_item_id
AND si.organization_id(+) = i.inv_organization_id
AND i.incident_id = b_incident_id;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT 'Y' required
FROM csp_requirement_headers
WHERE task_id = b_task_id;
SELECT serial_number
, lot_number
FROM csi_item_instances
WHERE instance_id = b_customer_product_id;
Select TIME_ZONE
From JTF_RS_RESOURCE_EXTNS
Where RESOURCE_ID = p_resource_id
;
SELECT ACTIVE_TIMEZONE_CODE,ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
FROM fnd_timezones_vl ft
WHERE UPGRADE_TZ_ID =l_Res_TimeZone_id;
SELECT territory_id
FROM csf_dc_resources_v
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type;
SELECT background_col_dec
INTO l_task_dec_color
FROM jtf_task_custom_colors
WHERE rule_id = l_rule_id;
SELECT DECODE(task_id, -1, ROWNUM, task_id) || plan_option_id
, start_time
, end_time
, 0 color
, ' ' NAME
, ' ' tooltip
, TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
+ TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5)) travel_time
, NVL(task_type_id, 0)
, resource_id || '-' || resource_type || '-' || plan_option_id
FROM csf_plan_options_v
WHERE sched_request_id = TO_NUMBER(SUBSTR(p_request_id, 1, INSTR(p_request_id, '-', 1) - 1))
AND (
task_id = -1
OR task_id =
TO_NUMBER(SUBSTR(p_request_id, INSTR(p_request_id, '-', 1) + 1
, LENGTH(p_request_id)))
)
AND (task_type_id IS NULL OR(task_type_id <> 20 OR task_type_id <> 21));
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT unit_of_measure_tl
FROM mtl_units_of_measure_vl
WHERE uom_code = p_code;
SELECT territory_id
FROM csf_dc_resources_v
WHERE resource_id = p_resource_id
AND resource_type = p_resource_type;
SELECT rs.resource_id
, rs.resource_type
, rs.winstart
, rs.winend
, rs.count_of_matching_skills
, rs.skill_level
FROM (SELECT rs.resource_id
, rs.resource_type
, GREATEST(
MAX(rs.start_date_active)
, NVL(MAX(ss.start_date_active), p_start)
, p_start
) winstart
, LEAST(
NVL(MIN(rs.end_date_active + 1), p_end)
, NVL(MIN(ss.end_date_active + 1), p_end)
, p_end
) winend
, COUNT(*) count_of_matching_skills
, SUM( 1/rsl.step_value ) skill_level
FROM csf_resource_skills_b rs
, csf_required_skills_b ts
, csf_skill_levels_b rsl
, csf_skill_levels_b tsl
, csf_skills_b ss
WHERE DECODE(
SIGN(rsl.step_value - tsl.step_value)
, -1, DECODE(l_levelmatch, 1, 'Y', 'N')
, 0, 'Y'
, 1, DECODE(l_levelmatch, 3, 'Y', 'N')
) = 'Y'
AND rsl.skill_level_id = rs.skill_level_id
AND tsl.skill_level_id = ts.skill_level_id
AND TRUNC(rs.start_date_active) < p_end
AND (TRUNC(rs.end_date_active + 1) > p_start OR rs.end_date_active IS NULL)
AND (rs.resource_id = p_resource_id OR p_resource_id IS NULL)
AND (rs.resource_type = p_resource_type OR p_resource_type IS NULL)
AND NVL(ts.disabled_flag, 'N') <> 'Y'
AND ts.has_skill_type = 'TASK'
AND ts.has_skill_id = p_task_id
AND ss.skill_id(+) = rs.skill_id
AND (
ts.skill_type_id NOT IN (2, 3)
AND rs.skill_id = ts.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND TRUNC(ss.start_date_active) < SYSDATE
AND TRUNC(NVL(ss.end_date_active, SYSDATE) + 1) > SYSDATE
OR ts.skill_type_id = 2
AND rs.skill_id = ts.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND EXISTS (SELECT 1 FROM mtl_system_items_kfv msi WHERE msi.inventory_item_id = rs.skill_id)
OR ts.skill_type_id = 3
AND rs.skill_id = ts.skill_id
AND ts.skill_type_id = rs.skill_type_id
AND EXISTS (SELECT 1
FROM mtl_item_categories mic
WHERE mic.category_id = rs.skill_id
AND category_set_id = fnd_profile.VALUE('CS_SR_DEFAULT_CATEGORY_SET'))
)
GROUP BY rs.resource_id, rs.resource_type) rs
, (
SELECT COUNT(*) count_of_req_skills
FROM csf_required_skills_b
WHERE NVL(disabled_flag, 'N') <> 'Y'
AND has_skill_type = 'TASK'
AND has_skill_id = p_task_id
) ts
WHERE rs.count_of_matching_skills = ts.count_of_req_skills
AND rs.winstart < rs.winend;
, p_update_plan_date in VARCHAR2 DEFAULT 'N'
, p_planned_start_date IN DATE DEFAULT NULL
, p_planned_end_date IN DATE DEFAULT NULL
, p_planned_effort IN NUMBER DEFAULT NULL
, p_planned_effort_uom IN VARCHAR2 DEFAULT NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_task_assignment_id OUT NOCOPY NUMBER
, x_task_object_version_number OUT NOCOPY NUMBER
, x_task_status_id OUT NOCOPY NUMBER
, x_task_status_name OUT NOCOPY VARCHAR2
, x_task_type_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'drag_n_drop';
SELECT object_version_number
, task_status_id
, scheduled_start_date
, scheduled_end_date
, parent_task_id
FROM jtf_tasks_b
WHERE task_id = p_task_id;
SELECT object_version_number
, actual_start_date
, actual_end_date
FROM JTF_TASK_ASSIGNMENTS
WHERE task_id= p_task_id
AND task_assignment_id =p_task_assignment_id;
SELECT object_version_number
FROM jtf_tasks_b
WHERE task_id = p_task_id;
csf_tasks_pub.update_task(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_task_id => p_task_id
, p_object_version_number => l_old_task_obj_ver_number
, p_planned_start_date => nvl(p_planned_start_date,fnd_api.g_miss_date)
, p_planned_end_date => nvl(p_planned_end_date,fnd_api.g_miss_date)
, p_scheduled_start_date => p_new_sched_start_date
, p_scheduled_end_date => p_new_sched_end_date
, p_actual_start_date => fnd_api.g_miss_date
, p_actual_end_date => fnd_api.g_miss_date
, p_planned_effort => l_planned_effort
, p_planned_effort_uom => l_planned_effort_uom
, p_task_status_id => l_task_status_id
, 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 => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_task_assignment_id => p_task_assignment_id
, p_object_version_number => l_old_ta_obj_version
, p_task_id => p_task_id
, p_resource_type_code => p_old_resource_type_code
, p_resource_id => p_old_resource_id
, p_resource_territory_id => fnd_api.g_miss_num
, p_assignment_status_id => p_cancel_status_id
, p_actual_start_date => fnd_api.g_miss_date
, p_actual_end_date => fnd_api.g_miss_date
, p_sched_travel_distance => fnd_api.g_miss_num
, p_sched_travel_duration => fnd_api.g_miss_num
, p_sched_travel_duration_uom => fnd_api.g_miss_char
, p_shift_construct_id => p_old_shift_construct_id
, p_object_capacity_id => p_old_object_capacity_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_task_object_version_number => x_task_object_version_number
, x_task_status_id => x_task_status_id
);
csf_task_assignments_pub.update_task_assignment(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, p_commit => fnd_api.g_false
, p_task_assignment_id => p_task_assignment_id
, p_object_version_number => l_old_ta_obj_version
, p_task_id => p_task_id
, p_resource_type_code => p_old_resource_type_code
, p_resource_id => p_old_resource_id
, p_resource_territory_id => fnd_api.g_miss_num
, p_assignment_status_id => p_assignment_status_id
, p_actual_start_date => fnd_api.g_miss_date
, p_actual_end_date => fnd_api.g_miss_date
, p_sched_travel_distance => p_sched_travel_distance
, p_sched_travel_duration => l_sched_travel_duration
, p_sched_travel_duration_uom => p_sched_travel_duration_uom
, p_shift_construct_id => p_new_shift_construct_id
, p_object_capacity_id => p_new_object_capacity_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_task_object_version_number => x_task_object_version_number
, x_task_status_id => x_task_status_id
);
csf_tasks_pub.update_task_longer_than_shift(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_true
, 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_parent_task_id
, p_object_version_number => l_obj_ver_number
, p_action => csf_tasks_pub.g_action_normal_to_parent
);
SELECT type_id
, priority_id
, assignment_status_id
, escalated_task
, background_col_dec
, background_col_rgb
FROM jtf_task_custom_colors
WHERE active_flag = 'Y'
ORDER BY color_determination_priority;
SELECT RESOURCE_ID ,
RESOURCE_TYPE ,
RESOURCE_NAME ,
RESOURCE_TYPE_NAME,
RES_KEY ,
NAME,
PHONE
FROM (
SELECT DISTINCT TR.RESOURCE_ID RESOURCE_ID ,
TR.RESOURCE_TYPE RESOURCE_TYPE ,
TR.RESOURCE_NAME RESOURCE_NAME ,
CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME( TR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME,
TR.RESOURCE_ID
||'-'
||TR.RESOURCE_TYPE RES_KEY,
TERR.NAME,
JRS.PHONE
FROM CSF_DC_RESOURCES_V TR,
JTF_TERR_ALL TERR,
JTF_RS_RESOURCES_VL JRS
WHERE TERR.TERR_ID = TR.TERRITORY_ID
AND JRS.RESOURCE_ID = TR.RESOURCE_ID
AND JRS.RESOURCE_TYPE = TR.RESOURCE_TYPE
)
ORDER BY UPPER(RESOURCE_NAME);
SELECT RESOURCE_ID,
RESOURCE_TYPE,
RESOURCE_NAME,
RESOURCE_TYPE_NAME,
RES_KEY,
NAME ,
PHONE
FROM (
SELECT DISTINCT
RES.RESOURCE_ID RESOURCE_ID,
RES.RESOURCE_TYPE RESOURCE_TYPE,
RES.RESOURCE_NAME RESOURCE_NAME,
CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RES.RESOURCE_TYPE) RESOURCE_TYPE_NAME,
RES.RESOURCE_ID || '-' || RES.RESOURCE_TYPE RES_KEY,
TERR.NAME,
JRS.PHONE
FROM CSF_DC_RESOURCES_V RES,
JTF_RS_ROLE_RELATIONS RR,
JTF_RS_ROLES_B RL ,
FND_LOOKUPS LKP,
JTF_TERR_ALL TERR,
JTF_RS_RESOURCES_VL JRS
WHERE RR.ROLE_ID = RL.ROLE_ID
AND RL.ROLE_TYPE_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
AND RES.RESOURCE_ID = RR.ROLE_RESOURCE_ID
AND RL.ROLE_TYPE_CODE = 'CSF_REPRESENTATIVE'
AND (SYSDATE >= TRUNC (RR.start_date_active) OR RR.start_date_active IS NULL)
AND (SYSDATE <= TRUNC (RR.end_date_active) + 1 OR RR.end_date_active IS NULL)
AND NVL(RR.DELETE_FLAG, 'N') = 'N'
AND TERR.TERR_ID = RES.TERRITORY_ID
AND JRS.RESOURCE_ID = RES.RESOURCE_ID
AND JRS.RESOURCE_TYPE = RES.RESOURCE_TYPE
)
ORDER BY UPPER (RESOURCE_NAME);
SELECT object_capacity_id
, start_date_time
, end_date_time
, status blocked_trip
, res_info.resource_id||'-'||res_info.resource_type resource_key
, nvl(ca.availability_type,'NULL')
FROM cac_sr_object_capacity ca,(SELECT TO_NUMBER(
SUBSTR(column_value
, 1
, INSTR(column_value, '-', 1, 1) - 1
)
)resource_id
, SUBSTR(column_value
, INSTR(column_value, '-', 1, 1) + 1
,LENGTH(column_value)
) resource_type
FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
) res_info
WHERE ca.object_type = res_info.resource_type
AND ca.object_id = res_info.resource_id
AND TRUNC(ca.start_date_time) >= trunc(p_start_date -1)
AND TRUNC(ca.end_date_time) <= trunc(p_end_date);
SELECT background_col_dec
FROM jtf_task_custom_colors
WHERE rule_id = t_rule_id;
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
a.task_id || '-' || a.task_assignment_id
, scheduled_start_date
, scheduled_end_date
, 0 color
, ' ' task_name
, NVL(sched_travel_duration, 0)
, t.task_type_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code resource_key
, task_assignment_id
, task_priority_id
, assignment_status_id
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, t.actual_effort
, t.actual_effort_uom
, t.planned_effort
, t.planned_effort_uom
, 'N' escalated_task
, scheduled_start_date
, scheduled_end_date
, csr.availability_type
FROM (SELECT TO_NUMBER(SUBSTR(column_value
, 1
, INSTR(column_value, '-', 1, 1) - 1
)
)resource_id
, SUBSTR(column_value
, INSTR(column_value, '-', 1, 1) + 1
,LENGTH(column_value)
) resource_type
FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_types_b tt
, jtf_task_statuses_b tsa
, jtf_task_statuses_b tsb
, cac_sr_object_capacity csr
WHERE t.task_id = a.task_id
AND t.task_type_id = tt.task_type_id
AND (t.task_type_id = 20 OR t.task_type_id = 21)
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND booking_end_date >= booking_start_date
AND booking_start_date >= (p_start_date_range -1)
AND TRUNC(booking_end_date) <= TRUNC(p_end_date_range)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsb.task_status_id = t.task_status_id
AND tsa.task_status_id = a.assignment_status_id
AND csr.object_capacity_id(+)=a.object_capacity_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
SELECT active
FROM csf_gnticons_setup_v
WHERE seq_id = 6;
SELECT icon_file_name
FROM csf_gnticons_setup_v
WHERE INSTR(ICON_FILE_NAME,'TASK') >0
AND nvl(active,'N')='Y'
ORDER BY RANKING;
SELECT /*+ ORDERED use_nl (res_info a t tl tt jtp jtpl tsa tsb pi ps l ca cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
DISTINCT
a.task_id || '-' || a.task_assignment_id
, t.task_number
, tl.task_name
, jtpl.name
, NVL(l.postal_code,' ')
, NVL(l.city,' ')
, NVL(ps.party_site_name,' ')
, scheduled_start_date
, scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, t.task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code resource_key
, nvl(pi.party_name,' ') incident_customer_name
, nvl(pi.party_name,' ') incident_customer_name1
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, 'N' escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, 'N' task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
FROM (SELECT TO_NUMBER(SUBSTR(column_value
, 1
, INSTR(column_value, '-', 1, 1) - 1
)
)resource_id
,SUBSTR(column_value
, INSTR(column_value, '-', 1, 1) + 1
, LENGTH(column_value)
) resource_type
FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
) res_info
, jtf_task_assignments a
, jtf_tasks_B t
, jtf_tasks_tl tl
, jtf_task_types_b tt
, jtf_task_priorities_B jtp
, jtf_task_priorities_tl jtpl
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, hz_parties pi
, hz_party_sites ps
, hz_locations l
, csf_access_hours_b ca
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND tl.task_id =t.task_id
AND tl.language=userenv('LANG')
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND jtp.task_priority_id=t.task_priority_id
AND jtpl.task_priority_id = jtp.task_priority_id
AND jtpl.language=userenv('LANG')
AND booking_start_date <= (p_end_date_range)
AND booking_end_date >= (p_start_date_range -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND pi.party_id(+) = t.customer_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date;
SELECT /*+ ORDERED use_nl (res_info a t tt jtp jtpl tsa tsb pi ps l ca jd jdd cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
DISTINCT
a.task_id || '-' || a.task_assignment_id
, t.task_number
, tl.task_name
, jtpl.name
, NVL(l.postal_code,' ')
, NVL(l.city,' ')
, NVL(ps.party_site_name,' ')
, scheduled_start_date
, scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, t.task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code resource_key
, nvl(pi.party_name,' ') incident_customer_name
, nvl(pi.party_name,' ') incident_customer_name1
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, 'N' escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
, nvl(jdd.dependent_on_task_id,0) task_dep1
FROM (SELECT TO_NUMBER(SUBSTR(column_value
, 1
, INSTR(column_value, '-', 1, 1) - 1
)
)resource_id
,SUBSTR(column_value
, INSTR(column_value, '-', 1, 1) + 1
, LENGTH(column_value)
) resource_type
FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_tasks_tl tl
, jtf_task_types_b tt
, jtf_task_priorities_B jtp
, jtf_task_priorities_tl jtpl
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, hz_parties pi
, hz_party_sites ps
, hz_locations l
, csf_access_hours_b ca
, jtf_task_depends jd
, jtf_task_depends jdd
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND tl.task_id =t.task_id
AND tl.language=userenv('LANG')
AND jtp.task_priority_id=t.task_priority_id
AND jtpl.task_priority_id = jtp.task_priority_id
AND jtpl.language=userenv('LANG')
AND booking_start_date <= (p_end_date_range)
AND booking_end_date >= (p_start_date_range -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND jd.task_id(+) = t.task_id
AND jdd.dependent_on_task_id(+) = t.task_id
AND pi.party_id(+) = t.customer_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date;
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
a.task_id || '-' || a.task_assignment_id
, scheduled_start_date
, scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code resource_key
, ' ' incident_customer_name
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, 'N' escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, 'N' task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
FROM (SELECT TO_NUMBER(SUBSTR(column_value
, 1
, INSTR(column_value, '-', 1, 1) - 1
)
)resource_id
,SUBSTR(column_value
, INSTR(column_value, '-', 1, 1) + 1
, LENGTH(column_value)
) resource_type
FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_types_b tt
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, csf_access_hours_b ca
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND booking_start_date <= (p_end_date_range)
AND booking_end_date >= (p_start_date_range -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date;
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca jd jdd cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
DISTINCT
a.task_id || '-' || a.task_assignment_id
, scheduled_start_date
, scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code resource_key
, ' ' incident_customer_name
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, 'N' escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
, nvl(jdd.dependent_on_task_id,0) task_dep1
FROM (SELECT TO_NUMBER(SUBSTR(column_value
, 1
, INSTR(column_value, '-', 1, 1) - 1
)
)resource_id
,SUBSTR(column_value
, INSTR(column_value, '-', 1, 1) + 1
, LENGTH(column_value)
) resource_type
FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_types_b tt
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, csf_access_hours_b ca
, jtf_task_depends jd
, jtf_task_depends jdd
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND booking_start_date <= (p_end_date_range)
AND booking_end_date >= (p_start_date_range -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND jd.task_id(+) = t.task_id
AND jdd.dependent_on_task_id(+) = t.task_id
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date;
SELECT type_id
, priority_id
, assignment_status_id
, escalated_task
, background_col_dec
, background_col_rgb
FROM jtf_task_custom_colors
WHERE active_flag = 'Y'
ORDER BY color_determination_priority;
SELECT active
FROM csf_gnticons_setup_v
WHERE seq_id = 6;
SELECT background_col_dec
FROM jtf_task_custom_colors
WHERE rule_id = t_rule_id;
SELECT icon_file_name
FROM csf_gnticons_setup_v
WHERE INSTR(ICON_FILE_NAME,'TASK') >0
AND nvl(active,'N')='Y'
ORDER BY RANKING;
SELECT DISTINCT cs.object_capacity_id cs
, ca.object_capacity_id
, ca.start_date_time
, ca.end_date_time
, status blocked_trip
, resource_id
, nvl(ca.availability_type,'NULL')
FROM cac_sr_object_capacity ca
, (SELECT resource_id || '-' || resource_type || '-' || plan_option_id
resource_id
, object_capacity_id
, resource_id res_id
, resource_type res_typ
FROM csf_plan_options_v
WHERE sched_request_id = p_req_id
AND task_type_id IN(20, 21)
) cs
WHERE ca.object_id = cs.res_id
AND ca.object_type = cs.res_typ
AND ca.start_date_time >= p_start_date -1
AND ca.end_date_time <= p_end_date;
SELECT
cpv.task_id || '-' || cpv.plan_option_id real_task_key
, cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
, cpv.start_time
, cpv.end_time
, t.task_number
, tl.task_name
, jtpl.name
, NVL(l.postal_code,' ')
, NVL(l.city,' ')
, NVL(ps.party_site_name,' ')
, 0 color
, ' ' tooltip
, NVL(
TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
+ TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
, 0
) travel_time
, t.task_type_id
, t.task_priority_id
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, nvl(pi.party_name,' ') incident_customer_name
, nvl(pi.party_name,' ') incident_customer_name1
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, 'N' task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
FROM csf_plan_options_v cpv
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_tasks_tl tl
, jtf_task_types_b tt
, jtf_task_priorities_B jtp
, jtf_task_priorities_tl jtpl
, hz_parties pi
, hz_party_sites ps
, hz_locations l
, csf_access_hours_b ca
, csp_requirement_headers cr
, jtf_task_statuses_b tsa
, jtf_task_statuses_b tsb
WHERE cpv.sched_request_id = p_req_id
AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
AND t.task_type_id = tt.task_type_id
AND tl.task_id =t.task_id
AND tl.language=userenv('LANG')
AND jtp.task_priority_id=t.task_priority_id
AND jtpl.task_priority_id = jtp.task_priority_id
AND jtpl.language=userenv('LANG')
AND cpv.start_time >= p_start_date
AND cpv.end_time <= p_end_date
AND cpv.task_id = t.task_id
AND cpv.task_id = a.task_id
AND ca.task_id(+) = t.task_id
AND pi.party_id(+) = t.customer_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
AND cr.task_id(+) = t.task_id
AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
AND tsb.task_status_id = t.task_status_id
AND tsa.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
SELECT
cpv.task_id || '-' || cpv.plan_option_id real_task_key
, cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
, cpv.start_time
, cpv.end_time
, t.task_number
, tl.task_name
, jtpl.name
, NVL(l.postal_code,' ')
, NVL(l.city,' ')
, NVL(ps.party_site_name,' ')
, 0 color
, ' ' tooltip
, NVL(
TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
+ TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
, 0
) travel_time
, t.task_type_id
, t.task_priority_id
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, nvl(pi.party_name,' ') incident_customer_name
, nvl(pi.party_name,' ') incident_customer_name1
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, nvl(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
FROM csf_plan_options_v cpv
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_tasks_tl tl
, jtf_task_types_b tt
, jtf_task_priorities_B jtp
, jtf_task_priorities_tl jtpl
, hz_parties pi
, hz_party_sites ps
, hz_locations l
, csf_access_hours_b ca
, csp_requirement_headers cr
, jtf_task_depends jd
, jtf_task_depends jdd
, jtf_task_statuses_b tsa
, jtf_task_statuses_b tsb
WHERE cpv.sched_request_id = p_req_id
AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
AND t.task_type_id = tt.task_type_id
AND tl.task_id =t.task_id
AND tl.language=userenv('LANG')
AND jtp.task_priority_id=t.task_priority_id
AND jtpl.task_priority_id = jtp.task_priority_id
AND jtpl.language=userenv('LANG')
AND cpv.start_time >= p_start_date
AND cpv.end_time <= p_end_date
AND cpv.task_id = t.task_id
AND cpv.task_id = a.task_id
AND ca.task_id(+) = t.task_id
AND pi.party_id(+) = t.customer_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
AND cr.task_id(+) = t.task_id
AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
AND jd.task_id(+) = t.task_id
AND jdd.dependent_on_task_id(+) = t.task_id
AND tsb.task_status_id = t.task_status_id
AND tsa.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
SELECT
cpv.task_id || '-' || cpv.plan_option_id real_task_key
, cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
, cpv.start_time
, cpv.end_time
, 0 color
, ' ' tooltip
, NVL(
TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
+ TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
, 0
) travel_time
, t.task_type_id
, task_priority_id
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, ' ' incident_customer_name
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, 'N' task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
FROM csf_plan_options_v cpv
, jtf_task_assignments a
, jtf_tasks_b t
, csf_access_hours_b ca
, csp_requirement_headers cr
, jtf_task_statuses_b tsa
, jtf_task_statuses_b tsb
WHERE cpv.sched_request_id = p_req_id
AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
AND cpv.start_time >= p_start_date
AND cpv.end_time <= p_end_date
AND cpv.task_id = t.task_id
AND cpv.task_id = a.task_id
AND ca.task_id(+) = t.task_id
AND cr.task_id(+) = t.task_id
AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
AND tsb.task_status_id = t.task_status_id
AND tsa.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
SELECT
cpv.task_id || '-' || cpv.plan_option_id real_task_key
, cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
, cpv.start_time
, cpv.end_time
, 0 color
, ' ' tooltip
, NVL(
TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
+ TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
, 0
) travel_time
, t.task_type_id
, task_priority_id
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, ' ' incident_customer_name
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, NVL(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
FROM csf_plan_options_v cpv
, jtf_task_assignments a
, jtf_tasks_b t
, csf_access_hours_b ca
, csp_requirement_headers cr
, jtf_task_depends jd
, jtf_task_depends jdd
, jtf_task_statuses_b tsa
, jtf_task_statuses_b tsb
WHERE cpv.sched_request_id = p_req_id
AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
AND cpv.start_time >= p_start_date
AND cpv.end_time <= p_end_date
AND cpv.task_id = t.task_id
AND cpv.task_id = a.task_id
AND ca.task_id(+) = t.task_id
AND cr.task_id(+) = t.task_id
AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
AND jd.task_id(+) = t.task_id
AND jdd.dependent_on_task_id(+) = t.task_id
AND tsb.task_status_id = t.task_status_id
AND tsa.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
SELECT DECODE(task_id, -1, ROWNUM, task_id) || plan_option_id plan_task_key
, resource_id || '-' || resource_type || '-' || plan_option_id plan_resource_key
, start_time
, end_time
, 65280 color
, ' ' NAME
, ' ' tooltip
, NVL(
TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
+ TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
, 0
) travel_time
, NVL(task_type_id, 0)
FROM csf_plan_options_v
WHERE sched_request_id = p_req_id
AND NVL(task_type_id, 0) NOT IN(20, 21)
AND start_time >= p_start_date
AND end_time <= p_end_date
AND (task_id = -1 OR task_id = p_task_id);
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb pi ca cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
a.task_id || '-'|| plan_option_id
, t.task_number
, tl.task_name
, jtpl.name
, NVL(l.postal_code,' ')
, NVL(l.city,' ')
, NVL(ps.party_site_name,' ')
, t.scheduled_start_date
, t.scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, t.task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
, nvl(pi.party_name,' ') incident_customer_name
, nvl(pi.party_name,' ') incident_customer_name1
, t.planned_start_date
, t.planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, 'N' task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
FROM (SELECT distinct rr.resource_id, rr.resource_type,pop.plan_option_id,pt.object_capacity_id
FROM csf_r_request_tasks rt,
csf_r_resource_results rr,
csf_r_plan_options pop,
csf_r_plan_option_tasks pt,
jtf_tasks_b t
WHERE rt.request_task_id = rr.request_task_id
AND rr.resource_result_id = POP.resource_result_id
AND POP.plan_option_id = pt.plan_option_id
AND pt.task_id = t.task_id(+)
AND rt.sched_request_id = p_req_id
AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_types_b tt
, jtf_tasks_tl tl
, jtf_task_priorities_B jtp
, jtf_task_priorities_tl jtpl
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, hz_parties pi
, hz_party_sites ps
, hz_locations l
, csf_access_hours_b ca
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND tl.task_id =t.task_id
AND tl.language=userenv('LANG')
AND jtp.task_priority_id=t.task_priority_id
AND jtpl.task_priority_id = jtp.task_priority_id
AND jtpl.language=userenv('LANG')
AND booking_start_date <= (p_end_date)
AND booking_end_date >= (p_start_date -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND pi.party_id(+) = t.customer_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date
AND a.object_capacity_id <>res_info.object_capacity_id;
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb pi ca jd jdd cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
DISTINCT
a.task_id || '-'|| plan_option_id
, t.task_number
, tl.task_name
, jtpl.name
, NVL(l.postal_code,' ')
, NVL(l.city,' ')
, NVL(ps.party_site_name,' ')
, t.scheduled_start_date
, t.scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, t.task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
, nvl(pi.party_name,' ') incident_customer_name
, nvl(pi.party_name,' ') incident_customer_name1
, t.planned_start_date
, t.planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
, nvl(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
FROM (SELECT distinct rr.resource_id, rr.resource_type,pop.plan_option_id,pt.object_capacity_id
FROM csf_r_request_tasks rt,
csf_r_resource_results rr,
csf_r_plan_options pop,
csf_r_plan_option_tasks pt,
jtf_tasks_b t
WHERE rt.request_task_id = rr.request_task_id
AND rr.resource_result_id = POP.resource_result_id
AND POP.plan_option_id = pt.plan_option_id
AND pt.task_id = t.task_id(+)
AND rt.sched_request_id = p_req_id
AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_tasks_tl tl
, jtf_task_types_b tt
, jtf_task_priorities_B jtp
, jtf_task_priorities_tl jtpl
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, hz_parties pi
, hz_party_sites ps
, hz_locations l
, csf_access_hours_b ca
, jtf_task_depends jd
, jtf_task_depends jdd
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND tl.task_id =t.task_id
AND tl.language=userenv('LANG')
AND jtp.task_priority_id=t.task_priority_id
AND jtpl.task_priority_id = jtp.task_priority_id
AND jtpl.language=userenv('LANG')
AND booking_start_date <= (p_end_date)
AND booking_end_date >= (p_start_date -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND jd.task_id(+) = t.task_id
AND jdd.dependent_on_task_id(+) = t.task_id
AND pi.party_id(+) = t.customer_id
AND ps.party_site_id(+) = t.address_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date
AND a.object_capacity_id <>res_info.object_capacity_id;
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
a.task_id || '-'|| plan_option_id
, scheduled_start_date
, scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
, ' ' incident_customer_name
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, 'N' task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
FROM (SELECT distinct rr.resource_id, rr.resource_type,pop.plan_option_id,pt.object_capacity_id
FROM csf_r_request_tasks rt,
csf_r_resource_results rr,
csf_r_plan_options pop,
csf_r_plan_option_tasks pt,
jtf_tasks_b t
WHERE rt.request_task_id = rr.request_task_id
AND rr.resource_result_id = POP.resource_result_id
AND POP.plan_option_id = pt.plan_option_id
AND pt.task_id = t.task_id(+)
AND rt.sched_request_id = p_req_id
AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_types_b tt
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, csf_access_hours_b ca
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND booking_start_date <= (p_end_date)
AND booking_end_date >= (p_start_date -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date
AND a.object_capacity_id <>res_info.object_capacity_id;
SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca jd jdd cr)
INDEX (t,JTF_TASKS_B_U3)
INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
DISTINCT
a.task_id || '-'|| plan_option_id
, scheduled_start_date
, scheduled_end_date
, 0 color
, NVL(sched_travel_duration, 0)
, t.task_type_id
, task_priority_id
, a.assignment_status_id
, '0' tooltip
, a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
, ' ' incident_customer_name
, planned_start_date
, planned_end_date
, a.actual_start_date
, a.actual_end_date
, NVL(a.actual_effort, t.actual_effort)
, NVL(a.actual_effort_uom, t.actual_effort_uom)
, t.planned_effort
, t.planned_effort_uom
, NVL(
DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
, 'N'
) escalated_task
, NVL(accesshour_required, 'N')
, NVL(after_hours_flag, 'N')
, NVL(task_confirmation_status, 'N')
, DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
, DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
, NVL(child_position, 'N') child_task
, a.actual_travel_duration
, a.actual_travel_duration_uom
, nvl(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
FROM (SELECT distinct rr.resource_id, rr.resource_type,POP.plan_option_id,pt.object_capacity_id
FROM csf_r_request_tasks rt,
csf_r_resource_results rr,
csf_r_plan_options pop,
csf_r_plan_option_tasks pt,
jtf_tasks_b t
WHERE rt.request_task_id = rr.request_task_id
AND rr.resource_result_id = POP.resource_result_id
AND POP.plan_option_id = pt.plan_option_id
AND pt.task_id = t.task_id(+)
AND rt.sched_request_id = p_req_id
AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_types_b tt
, jtf_task_statuses_b tsb
, jtf_task_statuses_b tsa
, csf_access_hours_b ca
, jtf_task_depends jd
, jtf_task_depends jdd
, csp_requirement_headers cr
WHERE t.task_id = a.task_id
AND t.source_object_type_code in( 'SR','TASK')
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_type_id NOT IN (20,21)
AND t.task_type_id = tt.task_type_id
AND booking_start_date <= (p_end_date)
AND booking_end_date >= (p_start_date -1)
AND a.resource_id = res_info.resource_id
AND a.resource_type_code = res_info.resource_type
AND tsa.task_status_id = t.task_status_id
AND tsb.task_status_id = a.assignment_status_id
AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
AND ca.task_id(+) = t.task_id
AND jd.task_id(+) = t.task_id
AND jdd.dependent_on_task_id(+) = t.task_id
AND cr.task_id(+) = t.task_id
AND booking_end_date >= booking_start_date
AND a.object_capacity_id <>res_info.object_capacity_id;
SELECT type_id
, priority_id
, assignment_status_id
, escalated_task
, background_col_dec
, background_col_rgb
FROM jtf_task_custom_colors
WHERE active_flag = 'Y'
ORDER BY color_determination_priority;
L_RESOURCE_QUERY := ' SELECT RR.RESOURCE_ID'
||',RR.RESOURCE_TYPE'
||',CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME'
||',CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME'
||',RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY'
||',POP.COST'
||' FROM '
||' CSF_R_REQUEST_TASKS RT,'
||' CSF_R_RESOURCE_RESULTS RR,'
||' CSF_R_PLAN_OPTIONS POP,'
||' CSF_R_PLAN_OPTION_TASKS PT,'
||' JTF_TASKS_B T'
||' WHERE RT.SCHED_REQUEST_ID =:1'
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
||' AND PT.TASK_ID = T.TASK_ID(+)';
L_RESOURCE_QUERY_COST := ' SELECT RR.RESOURCE_ID'
||',RR.RESOURCE_TYPE'
||',CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME'
||',CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME'
||',RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY'
||',POP.COST'
||' FROM '
||' CSF_R_REQUEST_TASKS RT,'
||' CSF_R_RESOURCE_RESULTS RR,'
||' CSF_R_PLAN_OPTIONS POP,'
||' CSF_R_PLAN_OPTION_TASKS PT,'
||' JTF_TASKS_B T'
||' WHERE RT.SCHED_REQUEST_ID =:1'
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
||' AND PT.TASK_ID = T.TASK_ID(+)'
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
||' AND (RR.RESOURCE_ID,POP.COST)'
||' IN '
||' (SELECT RR.RESOURCE_ID,MIN(POP.COST)'
||' FROM'
||' CSF_R_REQUEST_TASKS RT,'
||' CSF_R_RESOURCE_RESULTS RR,'
||' CSF_R_PLAN_OPTIONS POP,'
||' CSF_R_PLAN_OPTION_TASKS PT,'
||' JTF_TASKS_B T'
||' WHERE RT.SCHED_REQUEST_ID =:2'
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
||' AND PT.TASK_ID = T.TASK_ID(+)'
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
||' GROUP BY RR.RESOURCE_ID)';
L_RESOURCE_QUERY_COST_DAY := ' SELECT RR.RESOURCE_ID '
||' ,RR.RESOURCE_TYPE '
||' ,CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME '
||' ,CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME '
||' ,RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY '
||' ,POP.COST '
||' FROM '
||' CSF_R_REQUEST_TASKS RT, '
||' CSF_R_RESOURCE_RESULTS RR, '
||' CSF_R_PLAN_OPTIONS POP, '
||' CSF_R_PLAN_OPTION_TASKS PT, '
||' JTF_TASKS_B T '
||' WHERE RT.SCHED_REQUEST_ID = :1 '
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID '
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID '
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID '
||' AND PT.TASK_ID = T.TASK_ID(+) '
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21)) '
||' AND PT.PLAN_OPTION_ID IN ( '
||' SELECT PLAN_OPTION_ID FROM '
||' (SELECT PT.PLAN_OPTION_ID, MIN(trunc(PT.SCHEDULED_START_DATE)) START_TIME,MIN(POP.COST) COST '
||' FROM '
||' CSF_R_REQUEST_TASKS RT, '
||' CSF_R_RESOURCE_RESULTS RR, '
||' CSF_R_PLAN_OPTIONS POP, '
||' CSF_R_PLAN_OPTION_TASKS PT, '
||' JTF_TASKS_B T '
||' WHERE RT.SCHED_REQUEST_ID = :2 '
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID '
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID '
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID '
||' AND PT.TASK_ID = T.TASK_ID(+) '
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21)) '
||' GROUP BY PT.PLAN_OPTION_ID '
||' ) WHERE (TRUNC(START_TIME),COST) IN '
||' (select TRUNC(START_TIME), MIN(COST) from '
||' (SELECT MIN(trunc(PT.SCHEDULED_START_DATE)) START_TIME,MIN(POP.COST) COST '
||' FROM '
||' CSF_R_REQUEST_TASKS RT, '
||' CSF_R_RESOURCE_RESULTS RR, '
||' CSF_R_PLAN_OPTIONS POP, '
||' CSF_R_PLAN_OPTION_TASKS PT, '
||' JTF_TASKS_B T '
||' WHERE RT.SCHED_REQUEST_ID = :4 '
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID '
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID '
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID '
||' AND PT.TASK_ID = T.TASK_ID(+) '
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21)) '
||' GROUP BY PT.PLAN_OPTION_ID) '
||' GROUP BY TRUNC(START_TIME))) ';
L_RESOURCE_SINGLE_QUERY :=' SELECT RR.RESOURCE_ID'
||',RR.RESOURCE_TYPE'
||',CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME'
||',CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME'
||',RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY'
||',POP.COST'
||' FROM '
||' CSF_R_REQUEST_TASKS RT,'
||' CSF_R_RESOURCE_RESULTS RR,'
||' CSF_R_PLAN_OPTIONS POP,'
||' CSF_R_PLAN_OPTION_TASKS PT,'
||' JTF_TASKS_B T'
||' WHERE RT.SCHED_REQUEST_ID =:1'
||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
||' AND PT.TASK_ID = T.TASK_ID(+)'
||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
||' AND RR.RESOURCE_ID = :2'
||' AND RR.RESOURCE_TYPE = :3';
PROCEDURE insert_rows
( p_setup_type IN varchar2
, p_tooltip_setup_tbl IN tooltip_setup_tbl
, p_delete_rows IN boolean
, p_user_id IN number
, p_login_id IN number
)
IS
BEGIN
if p_delete_rows then
delete_rows(p_user_id);
insert into csf_gantt_chart_setup
(created_by,creation_date,last_updated_by, last_update_date, last_update_login, user_id, setup_type, seq_no, field_name, field_value)
values (p_user_id, sysdate, p_user_id,sysdate,p_login_id, p_user_id,p_setup_type,p_tooltip_setup_tbl(i).seq_no,p_tooltip_setup_tbl(i).field_name,p_tooltip_setup_tbl(i).field_value);
END INSERT_ROWS;
PROCEDURE DELETE_ROWS(p_user_id number)
is
begin
delete from csf_gantt_chart_setup where user_id = p_user_id;
END DELETE_ROWS;