APPS.CSZ_SERVICEREQUEST_UTIL_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

   )IS SELECT   user_name
       FROM fnd_user
       WHERE user_id = p_user_id;

Line: 25

   )IS SELECT  resource_name
       FROM jtf_rs_resource_extns_vl
       WHERE user_id = p_user_id;

Line: 112

  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;

Line: 120

    select hz.party_name
    into l_contact_name
    from hz_parties hz
    where hz.party_id = p_party_id;

Line: 125

    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';

Line: 268

     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';

Line: 312

            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';

Line: 370

  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;

Line: 378

  select template_group_name
  from jtf_task_temp_groups_vl
  where task_template_group_id = l_templ_grp_id;

Line: 560

  select
    business_process_id
  from
    cs_incident_types
  where
    incident_type_id = p_inc_type_id;

Line: 568

  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';

Line: 584

  select
    party_id
  from
    hz_relationships
  where
        object_id = l_object_id
    and subject_id = l_subject_id;

Line: 710

      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);

Line: 771

 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;

Line: 822

		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' ) ;

Line: 854

	 SELECT  timezone_id
	 INTO	 l_timezone_id
	 FROM    hz_contact_points
         WHERE   contact_point_id  =  p_contact_point_id
	 AND	 status = 'A' ;

Line: 860

		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';

Line: 875

	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';

Line: 908

      select  notes
      from    jtf_notes_vl
      where   source_object_code = 'SR' and  source_object_id =param_incident_id
       order by  creation_date;

Line: 1137

   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;

Line: 1197

   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';

Line: 1204

    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;

Line: 1213

    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;

Line: 1224

    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';

Line: 1299

   )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;

Line: 1309

  l_select_id        jtf_objects_b.select_id%TYPE;

Line: 1310

  l_select_details   jtf_objects_b.select_details%TYPE;

Line: 1315

  l_select_id_alias  jtf_objects_b.select_id%TYPE;

Line: 1316

  l_select_id_value  NUMBER;

Line: 1329

  FETCH c_jtf_object INTO l_select_id,
                          l_select_details,
                          l_from_table,
                          l_where_clause;

Line: 1339

  l_sql_statement := 'SELECT ' || l_select_details || ', ' || l_select_id
                     || ' FROM ' || l_from_table;

Line: 1345

  position := instr(l_select_id, ' ', -1, 1);

Line: 1346

  l_select_id_alias := substr(l_select_id, position+1);

Line: 1349

    l_sql_statement := 'SELECT * FROM (' ||
                       l_sql_statement ||
                       ' ) ' ||
                       ' WHERE ' ||
                       l_select_id_alias || ' = :select_id';

Line: 1359

                          || l_select_id || ' = :select_id';

Line: 1363

                          || l_select_id || ' = :select_id';

Line: 1368

  fetch details_cursor into l_details, l_select_id_value;

Line: 1400

  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;

Line: 1409

    select hz.party_name
    into l_assc_party_name
    from hz_parties hz
    where hz.party_id = p_assc_party_id;

Line: 1452

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')
 )
);

Line: 1473

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
);

Line: 1532

 SELECT first_name || ' ' ||last_name
 FROM per_workforce_x
 WHERE person_id = p_person_id;

Line: 1562

 SELECT email_address
 FROM per_workforce_x
 WHERE person_id = p_person_id;