[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
)IS SELECT user_name
FROM fnd_user
WHERE user_id = p_user_id;
)IS SELECT resource_name
FROM jtf_rs_resource_extns_vl
WHERE user_id = p_user_id;
select full_name,effective_start_date,effective_end_date
from per_all_people_f
where person_id = param_person_id
order by effective_start_date desc;
select hz.party_name
into l_contact_name
from hz_parties hz
where hz.party_id = p_party_id;
select hz.party_name
into l_contact_name
from hz_parties hz, hz_relationships rel
where rel.party_id = p_contact_party_id
and rel.object_id = p_party_id
and rel.subject_id = hz.party_id
and rel.subject_type = 'PERSON';
select l.address1 || decode (l.address2,null,null,',' || l.address2) ||
decode(l.address3,null,null,', '|| l.address3) || decode(l.address4,null,null,', '|| l.address4) ||
decode (l.city, null, null, ', '|| l.city) || decode(l.state,null,null,', ' || l.state) ||
decode(l.province,null,null,', ' || l.province) || decode(l.postal_code,null,null,' '
|| l.postal_code) || decode(l.country,null,null,' ' || l.country)
from hz_party_sites s, hz_locations l, hz_party_site_uses u
where s.party_id=param_party_id and s.status='A' and s.location_id=l.location_id and
s.party_site_id=u.party_site_id and u.site_use_type=param_site_usage
and u.primary_per_type='Y' and u.status='A';
select s.party_site_id from hz_party_sites s,
hz_party_site_uses u where s.party_id=param_party_id and s.status='A' and
s.party_site_id=u.party_site_id and u.site_use_type=param_site_usage
and u.primary_per_type='Y' and u.status='A';
select a.template_group_id
from jtf_tasks_b a, jtf_task_statuses_vl b
where a.task_status_id = b.task_status_id and
(nvl(b.closed_flag,'N') = 'N') and (nvl(b.completed_flag,'N') = 'N') and
a.source_object_type_code = 'SR' and
a.source_object_id = p_incident_id;
select template_group_name
from jtf_task_temp_groups_vl
where task_template_group_id = l_templ_grp_id;
select
business_process_id
from
cs_incident_types
where
incident_type_id = p_inc_type_id;
select
decode(contact.party_source_table, 'HZ_PARTIES', 'PARTY_RELATIONSHIP', 'EMPLOYEE') contact_type,
contact.party_id contact_id,
owner.party_id
from
csi_i_parties contact,
csi_i_parties owner
where
contact.instance_id = p_instance_id
and contact.contact_flag = 'Y'
and contact.primary_flag = 'Y'
and contact.contact_ip_id = owner.instance_party_id
and owner.contact_flag = 'N'
and owner.relationship_type_code = 'OWNER';
select
party_id
from
hz_relationships
where
object_id = l_object_id
and subject_id = l_subject_id;
select SrLnkEO.link_id link_id
, SrLnkEO.object_type object_type
, SrLnkEO.object_id object_id
, SrLnkEO.object_number object_number
, SrLnkEO.subject_id subject_id
, SrLnkEO.subject_type subject_type
, SrLnkEO.link_type_id link_type_id
, ltype.name link_type_name
from cs_incident_links SrLnkEO,
cs_incidents_all_b sr,
cs_sr_link_types_vl ltype
where SrLnkEO.object_id = sr.incident_id
and SrLnkEO.subject_type = 'SR'
and SrLnkEO.object_type = 'SR'
and sysdate between
nvl(SrLnkEO.start_date_active,sysdate)
and nvl(SrLnkEO.end_date_active,sysdate)
and SrLnkEO.link_type_id = ltype.link_type_id
and SrLnkEO.subject_id = l_incident_id
and ltype.link_type_id in (2, 3, 6);
select contact_type, party_id
from cs_hz_sr_contact_points
where incident_id = param_incident_id and primary_flag = param_primary_flag and
rownum < 2 order by party_id;
SELECT timezone_id
INTO l_timezone_id
FROM hz_locations
WHERE location_id = ( SELECT location_id
FROM hz_party_sites
WHERE party_id = p_contact_id
AND identifying_address_flag = 'Y'
AND status = 'A' ) ;
SELECT timezone_id
INTO l_timezone_id
FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND primary_flag = 'Y'
AND contact_point_id = p_contact_point_id;
SELECT name ,
timezone_code
INTO l_timezone_name,
l_timezone_code
FROM FND_TIMEZONES_VL
WHERE UPGRADE_TZ_ID = l_timezone_id
AND ENABLED_FLAG = 'Y';
select notes
from jtf_notes_vl
where source_object_code = 'SR' and source_object_id =param_incident_id
order by creation_date;
select trf.object_id
from jtf_tasks_b tsk,
jtf_task_references_b trf
where tsk.task_id = trf.task_id
and tsk.task_type_id = 22
and tsk.escalation_level not in ('DE', 'NE')
and nvl(tsk.open_flag, 'Y') = 'Y'
and trf.reference_code = 'ESC'
and trf.object_type_code = 'SR'
and trf.object_id = param_incident_id;
select sr_cont.party_id,
sr_cont.contact_type
from cs_hz_sr_contact_points sr_cont
where sr_cont.incident_id = p_incident_id
and sr_cont.primary_flag = 'Y';
select --employee_number,
full_name,
--, work_telephone, email_address,
effective_start_date,effective_end_date
from per_all_people_f
where person_id = param_party_contact_id
order by effective_start_date desc;
select --hz.party_number,
hz.party_name
--,hz.primary_phone_country_code||' '||
--hz.primary_phone_area_code||' '||
--hz.primary_phone_number||' '||
--hz.primary_phone_extension,
--hz.email_address
from hz_parties hz
where hz.party_id = p_customer_id;
select --hz.party_number,
hz.party_name
--,hz.primary_phone_country_code||' '||
--hz.primary_phone_area_code||' '||
--hz.primary_phone_number||' '||
--hz.primary_phone_extension,
--hz.email_address
from hz_parties hz, hz_relationships rel
where rel.party_id = param_contact_party_id
and rel.object_id = p_customer_id
and rel.subject_id = hz.party_id
and rel.subject_type = 'PERSON';
)IS SELECT select_id,
select_details ,
from_table ,
where_clause
FROM jtf_objects_b
WHERE select_id is not null
and select_details is not null
and from_table is not null
AND object_code = p_object_type;
l_select_id jtf_objects_b.select_id%TYPE;
l_select_details jtf_objects_b.select_details%TYPE;
l_select_id_alias jtf_objects_b.select_id%TYPE;
l_select_id_value NUMBER;
FETCH c_jtf_object INTO l_select_id,
l_select_details,
l_from_table,
l_where_clause;
l_sql_statement := 'SELECT ' || l_select_details || ', ' || l_select_id
|| ' FROM ' || l_from_table;
position := instr(l_select_id, ' ', -1, 1);
l_select_id_alias := substr(l_select_id, position+1);
l_sql_statement := 'SELECT * FROM (' ||
l_sql_statement ||
' ) ' ||
' WHERE ' ||
l_select_id_alias || ' = :select_id';
|| l_select_id || ' = :select_id';
|| l_select_id || ' = :select_id';
fetch details_cursor into l_details, l_select_id_value;
select full_name,effective_start_date,effective_end_date
from per_all_people_f
where person_id = param_person_id
order by effective_start_date desc;
select hz.party_name
into l_assc_party_name
from hz_parties hz
where hz.party_id = p_assc_party_id;
select distinct partyrole.name
from cs_hz_sr_contact_points cp, CS_PARTY_ROLES_VL partyrole
where cp.party_role_code = partyrole.party_role_code
and cp.incident_id = p_incident_id
and
(
(cp.contact_type = 'PERSON'
and cp.party_id = p_party_id)
or
(cp.contact_type = 'PARTY_RELATIONSHIP'
and cp.party_id in ( select party_id
from hz_relationships
where subject_id = p_party_id
and subject_type = 'PERSON')
)
);
select distinct partyrole.name
from cs_hz_sr_contact_points cp, CS_PARTY_ROLES_VL partyrole
where cp.party_role_code = partyrole.party_role_code
and cp.incident_id = p_incident_id
and
(
cp.contact_type = p_party_type
and cp.party_id = p_party_id
);
SELECT first_name || ' ' ||last_name
FROM per_workforce_x
WHERE person_id = p_person_id;
SELECT email_address
FROM per_workforce_x
WHERE person_id = p_person_id;
select esc.esc_level_name
from jtf_escalation_task_v esc
where esc.task_id = (
select max(jtf.task_id)
from jtf_escalation_task_v jtf,
jtf_task_references_b trf
where jtf.task_id = trf.task_id
and trf.object_type_code = 'SR'
and trf.object_id = param_incident_id);
SELECT DECODE(TRANSITION_IND,'Y',2,'N',3) TO_EXECUTE,
STATUS_GROUP_ID ,
DEFAULT_INCIDENT_STATUS_ID
INTO l_execute,
l_status_group_id,
l_default_incident_status_id
from
( SELECT DECODE(TRANSITION_IND, NULL, 'N', TRANSITION_IND) TRANSITION_IND,
ST.STATUS_GROUP_ID,
DEFAULT_INCIDENT_STATUS_ID
FROM
--if the value is -1 then query1 ottherwisse get transition id nad stuff
(select DECODE((select status_group_id
FROM CS_SR_TYPE_MAPPING
WHERE INCIDENT_TYPE_ID = P_INCIDENT_TYPE_ID
and responsibility_id = P_RESP_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
AND TRUNC(NVL(END_DATE,SYSDATE))),
NULL,DECODE((select status_group_id
FROM CS_INCIDENT_TYPES_B
where incident_type_id = P_INCIDENT_TYPE_ID
and incident_subtype = 'INC'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE))
AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))),NULL,null,(select status_group_id
FROM CS_INCIDENT_TYPES_B
where incident_type_id = P_INCIDENT_TYPE_ID
and incident_subtype = 'INC'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE))
AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))) ),
(select status_group_id
FROM CS_SR_TYPE_MAPPING
WHERE INCIDENT_TYPE_ID = P_INCIDENT_TYPE_ID
and responsibility_id =P_RESP_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE))
AND TRUNC(NVL(END_DATE,SYSDATE))) ) STATUS_GROUP_ID
FROM DUAL) ST,
CS_SR_STATUS_GROUPS_B B
WHERE b.STATUS_GROUP_ID =st.status_group_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE, SYSDATE)) AND TRUNC(NVL(END_DATE,SYSDATE))
) FT;
SELECT b.timezone_id
FROM hz_party_sites a, hz_locations b
WHERE a.location_id = b.location_id
AND a.party_site_id = p_incident_location_id ;
SELECT timezone_id
FROM hz_locations
WHERE location_id = p_incident_location_id ;
SELECT b.timezone_id
FROM hz_party_sites a, hz_locations b
WHERE a.location_id = b.location_id
AND a.identifying_address_flag = 'Y'
AND a.party_id = p_contact_party_id ;
SELECT timezone_id
FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND primary_flag = 'Y'
AND owner_table_id = p_contact_party_id ;
SELECT b.timezone_id
FROM hz_party_sites a, hz_locations b
WHERE a.location_id = b.location_id
AND a.identifying_address_flag = 'Y'
AND a.party_id = p_customer_id ;
SELECT timezone_id
FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND primary_flag = 'Y'
AND owner_table_id = p_customer_id ;