APPS.CUG_GENERIC_WF_PKG SQL Statements

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

Line: 38

      SELECT INSTALL_SITE_USE_ID
        FROM CS_INCIDENTS_ALL_VL
       WHERE INCIDENT_ID = l_request_id;

Line: 43

      SELECT ADDRESS1, ADDRESS2, CITY, STATE, COUNTRY, POSTAL_CODE
        FROM HZ_LOCATIONS WHERE LOCATION_ID = l_install_site_use_id;

Line: 49

     SELECT incident_location_id, incident_address, incident_city, incident_state,
            incident_postal_code, incident_country
     FROM    cs_incidents_all_b
     WHERE  incident_id = l_request_id;

Line: 56

      SELECT address, city, state, postal_code, country
      FROM   cs_sr_incident_address_v
      WHERE  location_id = l_incident_location_id;

Line: 64

       select INCIDENT_TYPE_ID, SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
        WHERE INCIDENT_TYPE_ID = l_incident_type_id;

Line: 69

        SELECT incidnt_attr_val_id from CUG_INCIDNT_ATTR_VALS_VL
            WHERE INCIDENT_ID = l_request_id;

Line: 74

        SELECT tsk_typ_attr_dep_id FROM CUG_TSK_TYP_ATTR_DEPS_B
            WHERE incident_type_id = l_incident_type_id;

Line: 87

                SELECT incident_type_id into l_incident_type_id FROM CS_INCIDENTS_ALL_B WHERE
                    incident_id = l_request_id;

Line: 221

        select resource_id  FROM CUG_SR_TYPE_DUP_CHK_INFO
                    WHERE incident_type_id = l_incident_type_id;

Line: 229

        select resource_name, resource_type from cs_sr_owners_v where
            resource_id = l_default_owner_id and
	    resource_type = 'RS_EMPLOYEE';

Line: 238

        select incident_owner_id, owner_group_id, org_id from
         cs_incidents_all_b where
         incident_id = l_request_id;

Line: 244

        select source_id from jtf_rs_resource_extns where
        resource_id = l_default_owner_id;

Line: 254

        SELECT person_id FROM per_people_x
	   WHERE full_name = l_GetResourceName_rec.resource_name AND employee_number IS NOT NULL;

Line: 270

        select resource_id from jtf_rs_group_members_vl where
            group_id = l_default_owner_id;

Line: 274

        select team_resource_id from jtf_rs_team_members_vl where
            team_id = l_default_owner_id;

Line: 285

          select INCIDENT_TYPE_ID into l_incident_type_id FROM
                CS_INCIDENTS_ALL_VL WHERE INCIDENT_ID = l_request_id;

Line: 306

   *)  if the current sr already has the group_id filled, do NOT update the inci
dent_owner id
   *)  if the current sr does not have the gorup is filled , but individual owne
r id filled - do not update the incident_owner id
   *)  if the current sr has no group and no current sr - update the incident_ow
ner id col with resp party id
*/

                OPEN l_GetResourceInfo_csr;

Line: 422

         SELECT DISTINCT OVERRIDE_ADDR_VALID_FLAG FROM CUG_INCIDNT_ATTR_VALS_B
          WHERE INCIDENT_ID = l_request_id;

Line: 481

       select SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
        WHERE INCIDENT_TYPE_ID = l_request_type_id;

Line: 492

          select INCIDENT_TYPE_ID into l_request_type_id from CS_INCIDENTS_ALL_VL
                WHERE INCIDENT_ID = l_request_id;

Line: 566

        select ADDRESS1, ADDRESS2, CITY, STATE, POSTAL_CODE, COUNTRY FROM
            HZ_LOCATIONS where LOCATION_ID = l_install_at_site_id;

Line: 571

        SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
            FROM CS_INCIDENTS_ALL_VL, HZ_LOCATIONS
            WHERE HZ_LOCATIONS.LOCATION_ID = CS_INCIDENTS_ALL_VL.INSTALL_SITE_USE_ID AND
                  HZ_LOCATIONS.ADDRESS1 = l_IncidentAddress_rec.Address1 AND
                  nvl(HZ_LOCATIONS.ADDRESS2, 'Not Filled') = nvl(l_IncidentAddress_rec.Address2, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.CITY, 'Not Filled') = nvl(l_IncidentAddress_rec.City, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.STATE, 'Not Filled') = nvl(l_IncidentAddress_rec.State, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.POSTAL_CODE, 'Not Filled') =  nvl(l_IncidentAddress_rec.Postal_Code, 'Not Filled') AND
--                  HZ_LOCATIONS.COUNTRY =  l_IncidentAddress_rec.Country AND
                  CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id;

Line: 587

     SELECT incident_location_id, incident_address, incident_city, incident_state,
            incident_postal_code, incident_country
     FROM    cs_incidents_all_b
     WHERE  incident_id = l_request_id;

Line: 594

      SELECT address, city, state, postal_code, country
      FROM   cs_sr_incident_address_v
      WHERE  location_id = l_incident_location_id;

Line: 600

        SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
            FROM CS_INCIDENTS_ALL_VL, HZ_LOCATIONS
            WHERE HZ_LOCATIONS.LOCATION_ID = CS_INCIDENTS_ALL_VL.INCIDENT_LOCATION_ID AND
                  nvl(HZ_LOCATIONS.ADDRESS1, 'Not Filled') = nvl(l_incident_address, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.CITY, 'Not Filled') = nvl(l_incident_city, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.STATE, 'Not Filled') = nvl(l_incident_state, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.POSTAL_CODE, 'Not Filled') =  nvl(l_incident_postal_Code, 'Not Filled') AND
                  nvl(HZ_LOCATIONS.COUNTRY, 'Not Filled') =  nvl(l_incident_country, 'Not Filled') AND
                  CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id
        UNION
        SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
            FROM CS_INCIDENTS_ALL_VL
            WHERE nvl(incident_ADDRESS, 'Not Filled') = nvl(l_incident_address, 'Not Filled') AND
                  nvl(incident_CITY, 'Not Filled') = nvl(l_incident_city, 'Not Filled') AND
                  nvl(incident_STATE, 'Not Filled') = nvl(l_incident_state, 'Not Filled') AND
                  nvl(incident_POSTAL_CODE, 'Not Filled') =  nvl(l_incident_postal_Code, 'Not Filled') AND
                  nvl(incident_COUNTRY, 'Not Filled') =  nvl(l_incident_country, 'Not Filled') AND
                  LAST_UPDATE_DATE > l_duplicate_date AND
                  INCIDENT_TYPE_ID = l_request_type_id AND
                  INCIDENT_ID <> l_request_id;

Line: 629

        SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
            FROM CS_INCIDENTS_ALL_VL
            WHERE CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id AND
                  CS_INCIDENTS_ALL_VL.INCIDENT_LOCATION_ID is NULL AND
                  CS_INCIDENTS_ALL_VL.incident_ADDRESS is NULL AND
                  CS_INCIDENTS_ALL_VL.incident_CITY is NULL AND
                  CS_INCIDENTS_ALL_VL.incident_STATE is NULL AND
                  CS_INCIDENTS_ALL_VL.incident_POSTAL_CODE is NULL AND
                  CS_INCIDENTS_ALL_VL.incident_COUNTRY is NULL;

Line: 644

        select SR_ATTRIBUTE_CODE from CUG_SR_TYPE_ATTR_MAPS_VL
             where INCIDENT_TYPE_ID = l_request_type_id AND
                    SR_ATTR_DUP_CHECK_FLAG = 'Y' AND
                   ( END_DATE_ACTIVE IS NULL OR
                     to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );

Line: 653

       SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
              sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
              incident_id = l_request_id;

Line: 658

       SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
              sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
              incident_id = l_IncidentId_rec.Incident_Id;

Line: 663

       SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
              sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
              incident_id = l_IncidentId_noLoc_rec.Incident_Id;

Line: 693

           select INSTALL_SITE_USE_ID into l_install_at_site_id
            from CS_INCIDENTS_ALL_VL where INCIDENT_ID = l_request_id;

Line: 725

          select INCIDENT_TYPE_ID into l_request_type_id
            from CS_INCIDENTS_ALL_VL where INCIDENT_ID = l_request_id;

Line: 766

                        l_sql_stmt := 'select sr_attribute_value from cug_incidnt_attr_vals_vl where sr_attribute_code = '
                                    || l_DuplicateCheckAttrs_rec.sr_attribute_code ||  ' and incident_id = :incident_id';

Line: 832

                        l_sql_stmt := 'select sr_attribute_value from cug_incidnt_attr_vals_vl where sr_attribute_code = '
                                    || l_DuplicateCheckAttrs_rec.sr_attribute_code ||  ' and incident_id = :incident_id';

Line: 898

   PROCEDURE UPDATE_DUPLICATE_INFO(
                itemtype	VARCHAR2,
				itemkey		VARCHAR2,
				actid		NUMBER,
				funmode		VARCHAR2,
				result		OUT NOCOPY VARCHAR2 )
   IS
        l_request_id        NUMBER;

Line: 911

        l_last_updated_by NUMBER(15):= FND_GLOBAL.USER_ID;

Line: 913

        l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;

Line: 924

            SELECT jtf_note_id from JTF_NOTES_B WHERE
                note_type = 'CUG_SR_ATTR_DETAILS' AND
                source_object_code = 'SR' AND
                source_object_id = l_request_id;

Line: 938

          select INCIDENT_TYPE_ID into l_request_type_id FROM
                CS_INCIDENTS_ALL_VL WHERE INCIDENT_ID = l_request_id;

Line: 941

          select RESOURCE_ID into l_default_owner_id FROM CUG_SR_TYPE_DUP_CHK_INFO
                WHERE Incident_Type_ID = l_request_type_id;

Line: 959

                p_last_update_date	=> sysdate,
                p_last_updated_by	=> l_default_owner_id,
     	      	p_creation_date     => sysdate,
                p_note_type         => 'Duplicate Service Request'
              );

Line: 974

                    p_last_update_date => sysdate,
               	  	p_last_updated_by  => l_default_owner_id,
             		p_creation_date => sysdate,
                    p_note_context_type_id => l_note_context_id,
                    p_note_context_type => 'Duplicate',
                    x_note_context_id   => l_note_context_id);

Line: 989

   	SELECT jtf_notes_s.NEXTVAL INTO l_note_context_id  FROM dual;

Line: 999

           INSERT INTO JTF_NOTE_CONTEXTS(
               NOTE_CONTEXT_ID,
               JTF_NOTE_ID,
               NOTE_CONTEXT_TYPE_ID,
               NOTE_CONTEXT_TYPE,
               LAST_UPDATE_DATE,
               LAST_UPDATED_BY,
               CREATION_DATE,
               CREATED_BY,
               LAST_UPDATE_LOGIN)
        	 VALUES
               (l_note_context_id,
                l_note_id,
                l_note_context_type_id,
            	'CUG_DUPLICATE_SR',
                sysdate,
                l_last_updated_by,
                sysdate,
                l_created_by,
                l_last_update_login);

Line: 1027

      WF_CORE.Context('CUG_GENERIC_WF_PKG', 'UPDATE_DUPLICATE_INFO',
                      itemtype, itemkey, actid, funmode);

Line: 1031

      WF_CORE.Context('CUG_GENERIC_WF_PKG', 'UPDATE_DUPLICATE_INFO',
		      itemtype, itemkey, actid, funmode);

Line: 1036

   END UPDATE_DUPLICATE_INFO;

Line: 1065

/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
/*  added the following parameter */
          l_request_number VARCHAR2(64);

Line: 1068

/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */

          l_planned_start_date date;

Line: 1079

            SELECT * FROM CUG_TSK_TYP_ATTR_DEPS_VL WHERE INCIDENT_TYPE_ID = l_request_type_id AND
            (START_DATE_ACTIVE IS NULL OR
             to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
             (END_DATE_ACTIVE is NULL OR
              to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );

Line: 1088

            SELECT * FROM CUG_SR_TASK_TYPE_DETS_VL WHERE
                TSK_TYP_ATTR_DEP_ID = l_tsk_typ_attr_dep_id;

Line: 1095

            SELECT SR_ATTRIBUTE_VALUE FROM CUG_INCIDNT_ATTR_VALS_VL WHERE
                INCIDENT_ID = l_request_id AND
                SR_ATTRIBUTE_CODE = l_sr_attribute_code;

Line: 1103

             SELECT workflow  FROM JTF_TASK_TYPES_B
                WHERE task_type_id = l_task_type_id;

Line: 1109

            SELECT lookup_code from FND_LOOKUP_VALUES where
                description = l_sr_attribute_value;

Line: 1130

/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
        l_request_number := WF_Engine.GetItemAttrText(itemtype => itemtype,
                                                    itemkey => itemkey,
                                                    aname => 'REQUEST_NUMBER');

Line: 1134

/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */

        SELECT INCIDENT_TYPE_ID into l_request_type_id FROM CS_INCIDENTS_ALL_VL WHERE
                    INCIDENT_ID = l_request_id;

Line: 1243

/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
/* Uncommented the following 3 lines */
                  p_source_object_type_code => 'SR',
                  p_source_object_id => l_request_id,
                  p_source_object_name => l_request_number,
/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
                  p_scheduled_start_date => sysdate,
                  p_scheduled_end_date => sysdate,
                  p_private_flag => l_SRTasks_Details_rec.private_flag,
                  p_publish_flag => l_SRTasks_Details_rec.publish_flag,
                  x_return_status  => l_return_status,
                  x_msg_count => l_msg_count,
                  x_msg_data => l_msg_data,
                  x_task_id  => l_task_id
               );

Line: 1283

/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
/* commenting out the following section */
/*
                update jtf_tasks_b set
                source_object_id = l_request_id,
                source_object_type_code = 'SR' where
                task_id = l_task_id;

Line: 1291

/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */

               l_task_type_id := l_SRTasks_rec.task_type_id;

Line: 1369

        SELECT tsk.task_number, tsk.task_id, tsk.task_type_id, sts.name FROM JTF_TASKS_VL tsk, JTF_TASK_STATUSES_VL sts WHERE
            tsk.task_status_id = sts.task_status_id and
            tsk.SOURCE_OBJECT_ID = l_request_id and
            tsk.source_object_type_code = 'SR';

Line: 1379

        SELECT cst.owner_id from CUG_SR_TASK_TYPE_DETS_B cst, CUG_TSK_TYP_ATTR_DEPS_B cta WHERE
            cst.tsk_typ_attr_dep_id = cta.tsk_typ_attr_dep_id AND
            cta.task_type_id = l_task_type_id AND
            cta.incident_type_id = l_request_type_id;

Line: 1385

        SELECT source_name from JTF_RS_RESOURCE_EXTNS WHERE
            resource_id = l_task_owner_id;

Line: 1390

        SELECT cst.owner_id, cst.owner_type_code
            from CUG_SR_TASK_TYPE_DETS_B cst, CUG_TSK_TYP_ATTR_DEPS_B cta
            WHERE cst.tsk_typ_attr_dep_id = cta.tsk_typ_attr_dep_id AND
            cta.task_type_id = l_task_type_id AND
            cta.incident_type_id = l_request_type_id;

Line: 1397

        SELECT resource_id from JTF_RS_RESOURCE_EXTNS WHERE
            resource_id = l_task_owner_id;

Line: 1416

        SELECT incident_type_id into l_request_type_id from CS_INCIDENTS_ALL_B where incident_id = l_request_id;

Line: 1563

        SELECT incident_type_id FROM CS_INCIDENTS_ALL_VL
            WHERE INCIDENT_ID = l_request_id;

Line: 1568

        SELECT duplicate_offset, duplicate_uom FROM CUG_SR_TYPE_DUP_CHK_INFO
            WHERE INCIDENT_TYPE_ID = l_request_type_id;

Line: 1573

        SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
            WHERE UNIT_OF_MEASURE = l_duplicate_uom;

Line: 1674

         SELECT jtf_task_workflow_process_s.nextval
           FROM dual;

Line: 1679

         SELECT task_name, description, owner_type_code owner_code, owner_id, task_number

           FROM jtf_tasks_v
          WHERE task_id = p_task_id;

Line: 1752

      select task_status, task_priority , task_type
      into l_task_status_name, l_task_priority_name  , l_task_type_name
      from jtf_tasks_v where task_id = p_task_id ;

Line: 1873

         SELECT task_type_id
           FROM jtf_task_types_vl
          WHERE task_type_id = p_task_type_id
            AND (START_DATE_ACTIVE IS NULL OR
             to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
             (END_DATE_ACTIVE is NULL OR
              to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );

Line: 1884

         SELECT task_priority_id
           FROM jtf_task_priorities_b
          WHERE task_priority_id = p_task_priority_id
            AND (START_DATE_ACTIVE IS NULL OR
             to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
             (END_DATE_ACTIVE is NULL OR
              to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );

Line: 1895

         SELECT task_status_id
           FROM jtf_task_statuses_b
          WHERE task_status_id = p_task_status_id
            AND (START_DATE_ACTIVE IS NULL OR
             to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
             (END_DATE_ACTIVE is NULL OR
              to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );

Line: 1913

   SELECT name into l_task_name
      FROM jtf_task_types_vl
      WHERE task_type_id = p_task_type_id;

Line: 1961

  PROCEDURE Update_CIC_Request_Info ( itemtype	VARCHAR2,
				  itemkey	VARCHAR2,
				  actid		NUMBER,
				  funmode	VARCHAR2,
				  result	OUT NOCOPY VARCHAR2 ) IS

    l_return_status	VARCHAR2(1);

Line: 1977

      SELECT inc.customer_product_id, inc.expected_resolution_date,inc.inventory_item_id,inc.summary, emp.source_id incident_owner_id
        FROM cs_incidents_all_vl inc ,cs_sr_owners_v owner, jtf_rs_resource_extns emp
        WHERE inc.INCIDENT_OWNER_ID = owner.resource_id(+) AND
              emp.resource_id = owner.resource_id AND
              incident_id = l_request_id;

Line: 2083

      WF_CORE.Context('CUG_GENERIC_WF_PKG', 'Update_CIC_Request_Info',
		      itemtype, itemkey, actid, funmode);

Line: 2087

  END Update_CIC_Request_Info;

Line: 2112

      SELECT *
        FROM CS_INCIDENTS_ALL_VL
       WHERE INCIDENT_NUMBER = l_request_number;