[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select p.party_name
, o.name
from cs_incidents_all_b i
, hz_parties p
, okc_k_lines_tl o
where i.incident_id = b_incident_id
and i.customer_id = p.party_id(+)
and i.contract_service_id = o.id(+)
and o.language(+) = userenv('lang');
select null tmp
from jtf_notes_b
where source_object_code = b_type
and source_object_id = b_id;
Select task_type_id,tt.name
from jtf_Task_types_tl tt
where task_type_id in (20,21)
and language=userenv('LANG');
SELECT 'Y'
FROM jtf_task_depends
WHERE p_task_id in (task_id,dependent_on_task_id);
SELECT RESOURCE_NAME,
RESOURCE_ID,
RESOURCE_TYPE
FROM (
SELECT RESOURCE_NAME,
RESOURCE_ID,
RESOURCE_TYPE
FROM CSF_SELECTED_RESOURCES_V
MINUS
SELECT DISTINCT
A.RESOURCE_NAME ,
A.RESOURCE_ID ,
A.RESOURCE_TYPE
FROM CSF_SELECTED_RESOURCES_V A,
JTF_RS_DEFRESROLES_VL B,
JTF_RS_ALL_RESOURCES_VL C,
JTF_RS_ROLES_B D
WHERE B.ROLE_RESOURCE_ID=A.RESOURCE_ID
AND C.RESOURCE_ID = B.ROLE_RESOURCE_ID
AND C.RESOURCE_TYPE =A.RESOURCE_TYPE
AND D.ROLE_ID = B.ROLE_ID
AND B.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
AND NVL( B.DELETE_FLAG, 'N') = 'N'
AND (SYSDATE >= TRUNC (B.RES_RL_START_DATE) OR B.RES_RL_START_DATE IS NULL)
AND (SYSDATE <= TRUNC (B.RES_RL_END_DATE) + 1 OR B.RES_RL_END_DATE IS NULL)
AND ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR')
)
ORDER BY UPPER (RESOURCE_NAME);
SELECT RESOURCE_NAME,
RESOURCE_ID,
RESOURCE_TYPE
FROM(
SELECT DISTINCT
RES.RESOURCE_NAME,
RES.RESOURCE_ID,
RES.RESOURCE_TYPE
FROM CSF_SELECTED_RESOURCES_V RES ,
JTF_RS_ROLE_RELATIONS RR,
JTF_RS_ROLES_B RL ,
FND_LOOKUPS LKP
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'
MINUS
SELECT DISTINCT
A.RESOURCE_NAME,
A.RESOURCE_ID ,
A.RESOURCE_TYPE
FROM CSF_SELECTED_RESOURCES_V A,
JTF_RS_ROLE_RELATIONS RR,
JTF_RS_ROLES_B RL ,
FND_LOOKUPS LKP
WHERE RR.ROLE_RESOURCE_ID = A.RESOURCE_ID
AND RL.ROLE_ID = RR.ROLE_ID
AND LKP.LOOKUP_CODE = RL.ROLE_TYPE_CODE
AND LKP.LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
AND RL.ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR')
AND RL.ROLE_TYPE_CODE = 'CSF_THIRD_PARTY'
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'
)
ORDER BY UPPER (resource_name);
SELECT /*+ cardinality(res 1) leading(res,a,cs,ts1,t) use_nl(res a) index(a,JTF_TASK_ASSIGNMENTS_N1) */
t.task_id
, decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
, t.task_number
, t.task_type_id
, decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
, t.task_priority_id
, t.source_object_type_code
, t.source_object_name
, t.source_object_id
, t.planned_start_date
, t.planned_end_date
, t.scheduled_start_date
, t.scheduled_end_date
, t.task_confirmation_status
, t.parent_task_id
, t.task_split_flag
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, l.city
, NULL customer
, NULL contract
, null type_name
, ts1.name
, 0 escalated
, a.actual_effort
, t.planned_effort
, res.resource_id
, res.resource_type
, res.resource_name
, ts1.schedulable_flag
, tt.schedule_flag
, a.object_capacity_id
, a.task_assignment_id
, cs.availability_type
FROM ( SELECT resource_id,
resource_type,
resource_name
FROM Table(Cast(v_restab As csf_resource_tbl))
) res
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_statuses_vl ts1
, jtf_task_statuses_b ts2
, jtf_task_types_b tt
, hz_locations l
, cac_sr_object_capacity cs
WHERE a.assignee_role = 'ASSIGNEE'
AND a.resource_id = res.resource_id
AND a.resource_type_code = res.resource_type
AND a.booking_end_date >= p_start_date
AND a.booking_start_date < p_end_date
AND a.booking_end_date >= a.booking_start_date
AND a.assignment_status_id = ts1.task_status_id
AND nvl(ts1.cancelled_flag,'N') <> 'Y'
AND t.task_id = a.task_id
-- AND t.scheduled_start_date is not null --commented for the bug 6729435
-- AND t.scheduled_end_date is not null
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_status_id = ts2.task_status_id
AND nvl(ts2.cancelled_flag,'N') <> 'Y'
and cs.object_capacity_id(+)= a.object_capacity_id
AND t.task_type_id = tt.task_type_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
ORDER BY res.resource_name
, nvl(a.actual_start_date,t.scheduled_start_date)
, DECODE(t.task_type_id, 20, 1, 21, 3, 2)
, a.task_assignment_id;
SELECT *
FROM
(SELECT /*+ cardinality(res 1) leading(res,a,cs,ts1,t) use_nl(res a) index(a,JTF_TASK_ASSIGNMENTS_N1) */
t.task_id
, decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
, t.task_number
, t.task_type_id
, decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
, t.task_priority_id
, t.source_object_type_code
, t.source_object_name
, t.source_object_id
, t.planned_start_date
, t.planned_end_date
, t.scheduled_start_date
, t.scheduled_end_date
, t.task_confirmation_status
, t.parent_task_id
, t.task_split_flag
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, l.city
, NULL customer
, NULL contract
, null type_name
, ts1.name
, 0 escalated
, a.actual_effort
, t.planned_effort
, res.resource_id
, res.resource_type
, res.resource_name
, ts1.schedulable_flag
, tt.schedule_flag
, a.object_capacity_id
, a.task_assignment_id
, cs.availability_type
FROM ( SELECT resource_id,
resource_type,
resource_name
FROM Table(Cast(v_restab As csf_resource_tbl))
) res
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_statuses_vl ts1
, jtf_task_statuses_b ts2
, jtf_task_types_b tt
, hz_locations l
, cac_sr_object_capacity cs
WHERE a.assignee_role = 'ASSIGNEE'
AND a.resource_id = res.resource_id
AND a.resource_type_code = res.resource_type
AND a.booking_end_date >= p_start_date
AND a.booking_start_date < p_end_date
AND a.booking_end_date >= a.booking_start_date
AND a.assignment_status_id = ts1.task_status_id
AND nvl(ts1.cancelled_flag,'N') <> 'Y'
AND t.task_id = a.task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
and cs.object_capacity_id(+)= a.object_capacity_id
AND t.task_type_id not in (20,21)
AND t.task_status_id = ts2.task_status_id
AND nvl(ts2.cancelled_flag,'N') <> 'Y'
AND t.task_type_id = tt.task_type_id
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
UNION
SELECT /*+ cardinality(res 1) leading(res,a,cs,ts1,t) use_nl(res a) index(a,JTF_TASK_ASSIGNMENTS_N1) */
t.task_id
, decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
, t.task_number
, t.task_type_id
, decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
, t.task_priority_id
, t.source_object_type_code
, t.source_object_name
, t.source_object_id
, t.planned_start_date
, t.planned_end_date
, t.scheduled_start_date
, t.scheduled_end_date
, t.task_confirmation_status
, t.parent_task_id
, t.task_split_flag
, a.assignment_status_id
, a.actual_start_date
, a.actual_end_date
, l.city
, NULL customer
, NULL contract
, null type_name
, ts1.name
, 0 escalated
, a.actual_effort
, t.planned_effort
, res.resource_id
, res.resource_type
, res.resource_name
, ts1.schedulable_flag
, tt.schedule_flag
, a.object_capacity_id
, a.task_assignment_id
,csr.availability_type
FROM ( SELECT resource_id,
resource_type,
resource_name
FROM Table(Cast(v_restab As csf_resource_tbl))
) res
, jtf_task_assignments a
, jtf_tasks_b t
, jtf_task_statuses_vl ts1
, jtf_task_statuses_b ts2
, jtf_task_types_b tt
, hz_locations l
, cac_sr_object_capacity csr
WHERE a.assignee_role = 'ASSIGNEE'
AND a.resource_id = res.resource_id
AND a.resource_type_code = res.resource_type
AND a.booking_end_date >= p_start_date
AND a.booking_start_date < p_end_date
AND a.booking_end_date >= a.booking_start_date
AND a.assignment_status_id = ts1.task_status_id
AND nvl(ts1.cancelled_flag,'N') <> 'Y'
AND t.task_id = a.task_id
AND NVL(t.deleted_flag, 'N') <> 'Y'
AND t.task_status_id = ts2.task_status_id
AND nvl(ts2.cancelled_flag,'N') <> 'Y'
AND t.task_type_id in (20,21)
AND t.task_type_id = tt.task_type_id
AND csr.object_capacity_id(+)=a.object_capacity_id
AND (NVL(csr.availability_type,'REGULAR') = decode (nvl(p_shift_reg,'N'),'R','REGULAR')
or NVL(csr.availability_type,NULL) = decode (nvl(p_shift_std,'N'),'S','STANDBY') )
AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
)
ORDER BY resource_name
, nvl(actual_start_date,scheduled_start_date)
, DECODE(task_type_id, 20, 1, 21, 3, 2)
, task_assignment_id;
select background_col_rgb
into p_item
from jtf_task_custom_colors
where rule_id=l_rule_id;
SELECT object_capacity_id,status
FROM cac_sr_object_capacity
WHERE object_capacity_id in (select DISTINCT column_value
FROM TABLE(CAST(trip_id AS jtf_NUMBER_table))
where column_value <> 0);
SELECT task_id
, NVL(accesshour_required, 'N') access_flag
, NVL(after_hours_flag, 'N') after_flag
FROM csf_access_hours_b
WHERE task_id in (select DISTINCT column_value
FROM TABLE(CAST(real_task_id AS jtf_NUMBER_table))
where column_value <> 0);
SELECT task_id
FROM csp_requirement_headers
WHERE task_id in (select DISTINCT column_value
FROM TABLE(CAST(real_task_id AS jtf_NUMBER_table))
where column_value <> 0);