DBA Data[Home] [Help]

APPS.CS_SRCONTACT_PKG SQL Statements

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

Line: 40

, p_updated_cp_qry      IN         VARCHAR2
, p_updated_cp_bind     IN         NUM_TBL
, p_incident_id         IN         NUMBER
, x_return_status       OUT NOCOPY VARCHAR2
) IS
l_not_updated_contacts CS_SERVICEREQUEST_PVT.contacts_table;
Line: 73

    l_sql := 'SELECT contact_type, party_id, party_role_code, contact_point_id, contact_point_type, start_date_active, end_date_active
              FROM   cs_hz_sr_contact_points
              WHERE  incident_id = :incident_id
               /* AND  TRUNC(SYSDATE) BETWEEN NVL(start_date_active, TRUNC(SYSDATE))
                                        AND NVL(end_date_active  , TRUNC(SYSDATE)) */';
Line: 78

    IF p_updated_cp_qry <> ':1'
    THEN
      l_sql := l_sql || ' AND sr_contact_point_id NOT IN ( '|| p_updated_cp_qry || ')';
Line: 87

    IF p_updated_cp_qry <> ':1'
    THEN
      FOR indx in p_updated_cp_bind.FIRST..p_updated_cp_bind.LAST
      LOOP
        DBMS_SQL.bind_variable(l_cur_hdl, ':' ||to_char(indx), p_updated_cp_bind(indx));
Line: 114

        l_not_updated_contacts(l_index).party_id           := l_party_id;
Line: 115

        l_not_updated_contacts(l_index).contact_type       := l_contact_type;
Line: 116

        l_not_updated_contacts(l_index).party_role_code    := l_party_role_code;
Line: 117

        l_not_updated_contacts(l_index).end_date_active    := l_end_date_active;
Line: 118

        l_not_updated_contacts(l_index).contact_point_id   := l_contact_point_id;
Line: 119

        l_not_updated_contacts(l_index).start_date_active  := l_start_date_active;
Line: 120

        l_not_updated_contacts(l_index).contact_point_type := l_contact_point_type;
Line: 166

    IF l_not_updated_contacts.count > 0
    THEN
      FOR k in l_not_updated_contacts.FIRST..l_not_updated_contacts.LAST
      LOOP
        l_st_dt2  := NVL(l_not_updated_contacts(k).start_date_active, TRUNC(SYSDATE-36500));
Line: 171

        l_end_dt2 := NVL(l_not_updated_contacts(k).end_date_active  , TRUNC(SYSDATE+36500));
Line: 172

        IF p_new_contact_tbl(i).party_id           = l_not_updated_contacts(k).party_id           AND
           p_new_contact_tbl(i).contact_type       = l_not_updated_contacts(k).contact_type       AND
           p_new_contact_tbl(i).party_role_code    = l_not_updated_contacts(k).party_role_code    AND
           NVL(p_new_contact_tbl(i).contact_point_id  ,-99) = NVL(l_not_updated_contacts(k).contact_point_id  ,-99) AND
           NVL(p_new_contact_tbl(i).contact_point_type,'-') = NVL(l_not_updated_contacts(k).contact_point_type,'-') AND
           ( l_st_dt1  BETWEEN l_st_dt2 AND l_end_dt2 OR
             l_st_dt2 BETWEEN  l_st_dt1 AND l_end_dt1
           )
        THEN
          l_dup_found := 1;
Line: 191

    END IF; --end of condition IF l_not_updated_contacts.count > 0
Line: 236

  SELECT COUNT(1)
  INTO   l_count
  FROM   cs_hz_sr_contact_points
  WHERE  incident_id = p_incident_id
    AND  party_role_code = 'CONTACT'
    AND  NVL(END_DATE_ACTIVE,sysdate) >= l_dt
  ;
Line: 278

  INSERT INTO CS_HZ_SR_CONTACT_PNTS_AUDIT
  ( sr_contact_point_audit_id
  , sr_contact_point_id
  , incident_id
  , party_id
  , old_party_id
  , contact_type
  , old_contact_type
  , party_role_code
  , old_party_role_code
  , contact_point_type
  , old_contact_point_type
  , contact_point_id
  , old_contact_point_id
  , start_date_active
  , old_start_date_active
  , end_date_active
  , old_end_date_active
  , primary_flag
  , old_primary_flag
  , creation_date
  , last_update_date
  , created_by
  , last_updated_by
  , last_update_login
  , contact_point_modified_by
  , contact_point_modified_on
  )
  VALUES
  ( cs.cs_hz_sr_cont_pnts_audit_s.NEXTVAL
  , p_sr_contact_point_id
  , p_incident_id
  , p_new_cp_rec.party_id
  , p_old_cp_rec.party_id
  , p_new_cp_rec.contact_type
  , p_old_cp_rec.contact_type
  , p_new_cp_rec.party_role_code
  , p_old_cp_rec.party_role_code
  , p_new_cp_rec.contact_point_type
  , p_old_cp_rec.contact_point_type
  , p_new_cp_rec.contact_point_id
  , p_old_cp_rec.contact_point_id
  , p_new_cp_rec.start_date_active
  , p_old_cp_rec.start_date_active
  , p_new_cp_rec.end_date_active
  , p_old_cp_rec.end_date_active
  , p_new_cp_rec.primary_flag
  , p_old_cp_rec.primary_flag
  , l_dt
  , l_dt
  , FND_GLOBAL.USER_ID
  , FND_GLOBAL.USER_ID
  , FND_GLOBAL.LOGIN_ID
  , p_cp_modified_by
  , p_cp_modified_on
  );
Line: 409

     p_mode := 'UPDATE';
Line: 414

  IF p_mode = 'UPDATE'
  THEN
    -- check contact type update
    IF p_new_contact_rec.contact_type <> p_old_contact_rec.contact_type
    THEN
      FND_MESSAGE.set_name  ('CS','CS_SR_CP_CONTACT_TYPE_UPD_NA');
Line: 440

  END IF; -- p_mode = 'UPDATE'
Line: 760

, x_updated_cp_qry     OUT NOCOPY VARCHAR2
, x_updated_cp_bind    OUT NOCOPY NUM_TBL
, x_return_status      OUT NOCOPY VARCHAR2
) IS
l_incident_id    CS_INCIDENTS_ALL_B.incident_id % type;
Line: 765

l_updated_cp_index NUMBER;
Line: 768

  SELECT
    sr_contact_point_id
  , party_id
  , contact_point_id
  , contact_point_type
  , contact_type
  , primary_flag
  , party_role_code
  , start_date_active
  , end_date_active
  , incident_id
  FROM
    cs_hz_sr_contact_points
  WHERE sr_contact_point_id = p_sr_contact_point_id;
Line: 785

  x_updated_cp_qry := ':1';
Line: 786

  l_updated_cp_index := 1;
Line: 787

  x_updated_cp_bind(l_updated_cp_index) := -1;
Line: 831

        l_updated_cp_index       := l_updated_cp_index + 1;
Line: 832

        x_updated_cp_qry         :=
          x_updated_cp_qry || ', :' || to_char(l_updated_cp_index);
Line: 834

        x_updated_cp_bind(l_updated_cp_index) := x_old_contact_tbl(i).sr_contact_point_id;
Line: 934

PROCEDURE create_update
( p_incident_id     IN  NUMBER
, p_invocation_mode IN  VARCHAR2
, p_sr_update_date  IN  DATE
, p_sr_updated_by   IN  VARCHAR2
, p_sr_update_login IN  VARCHAR2
, p_contact_tbl     IN  CS_SERVICEREQUEST_PVT.contacts_table
, p_old_contact_tbl IN  CS_SERVICEREQUEST_PVT.contacts_table
, x_return_status   OUT NOCOPY VARCHAR2
) IS
--
l_audit_id            NUMBER;
Line: 976

      SELECT cs_hz_sr_contact_points_s.NEXTVAL
      INTO l_sr_contact_point_id
      FROM DUAL;
Line: 982

      INSERT INTO cs_hz_sr_contact_points
      ( sr_contact_point_id
      , party_id
      , incident_id
      , contact_point_type
      , contact_type
      , contact_point_id
      , primary_flag
      , party_role_code
      , start_date_active
      , end_date_active
      , creation_date
      , created_by
      , last_update_date
      , last_updated_by
      , last_update_login
      , object_version_number)
      VALUES
      ( l_sr_contact_point_id
      , p_contact_tbl(loop_index).party_id
      , p_incident_id
      , p_contact_tbl(loop_index).contact_point_type
      , p_contact_tbl(loop_index).contact_type
      , p_contact_tbl(loop_index).contact_point_id
      , p_contact_tbl(loop_index).primary_flag
      , NVL(p_contact_tbl(loop_index).party_role_code,'CONTACT')
      , p_contact_tbl(loop_index).start_date_active
      , p_contact_tbl(loop_index).end_date_active
      , p_sr_update_date
      , p_sr_updated_by
      , p_sr_update_date
      , p_sr_updated_by
      , p_sr_update_login
      , 1 );
Line: 1034

      UPDATE cs_hz_sr_contact_points
      SET primary_flag          = p_contact_tbl(loop_index).primary_flag
      ,   contact_point_id      = p_contact_tbl(loop_index).contact_point_id
      ,   contact_point_type    = p_contact_tbl(loop_index).contact_point_type
      ,   start_date_active     = p_contact_tbl(loop_index).start_date_active
      ,   end_date_active       = p_contact_tbl(loop_index).end_date_active
      ,   last_updated_by       = p_sr_updated_by
      ,   last_update_date      = p_sr_update_date
      ,   last_update_login     = p_sr_update_login
      ,   object_version_number = object_version_number+1
      WHERE sr_contact_point_id = l_sr_contact_point_id;
Line: 1051

      , p_updated_entity_code   => 'SR_CONTACT_POINT'
      , p_updated_entity_id     => l_sr_contact_point_id
      , p_entity_update_date    => p_sr_update_date
      , p_entity_activity_code  => l_activity_code
      , x_audit_id              => l_audit_id
      , x_return_status         => x_return_status
      , x_msg_count             => l_msg_count
      , x_msg_data              => l_msg_data
      );
Line: 1067

        , p_cp_modified_by      => p_sr_updated_by
        , p_cp_modified_on      => p_sr_update_date
        , x_return_status       => x_return_status
        , x_msg_count           => l_msg_count
        , x_msg_data            => l_msg_data
        );
Line: 1078

END CREATE_UPDATE;
Line: 1106

  UPDATE cs_hz_sr_contact_points
     SET primary_flag = 'N'
   WHERE incident_id = p_incident_id
     AND primary_flag = 'Y'
     AND sr_contact_point_id <> NVL(p_sr_contact_point_id,-9)
   RETURNING
    sr_contact_point_id ,
    party_id            ,
    contact_point_id    ,
    primary_flag        ,
    contact_point_type  ,
    contact_type        ,
    party_role_code     ,
    start_date_active   ,
    end_date_active
  INTO l_new_cp_rec
  ;
Line: 1183

l_updated_cp_qry           VARCHAR2(4000);
Line: 1184

l_updated_cp_bind          NUM_TBL;
Line: 1197

  SELECT
    party_id
  , DECODE(contact_point_type,'PHONE',contact_point_id,NULL)
  FROM
     cs_hz_sr_contact_points
  WHERE incident_id = p_incident_id
    AND primary_flag = 'Y';
Line: 1222

  , x_updated_cp_qry  => l_updated_cp_qry
  , x_updated_cp_bind => l_updated_cp_bind
  , x_return_status   => x_return_status
  );
Line: 1288

    ELSE -- means primary contact count is exactly 1. in case of update mode
         -- existing primary cantact should be made non primary.
      IF p_mode <> 'CREATE'
      THEN
        IF l_sr_contact_point_id_pri = FND_API.G_MISS_NUM
        THEN
          l_sr_contact_point_id_pri := NULL;
Line: 1347

    , p_updated_cp_qry      => l_updated_cp_qry
    , p_updated_cp_bind     => l_updated_cp_bind
    , p_incident_id         => p_incident_id
    , x_return_status       => x_return_status
    );
Line: 1396

	       select LAST_NAME,FIRST_NAME into l_last_name,l_first_name
	       from PER_ALL_PEOPLE_F  where person_id = l_primary_contact_id;
Line: 1400

		select PERSON_LAST_NAME,PERSON_FIRST_NAME into l_last_name,l_first_name
		from HZ_PARTIES where party_id = l_primary_contact_id;
Line: 1404

		select PERSON_LAST_NAME,PERSON_FIRST_NAME into l_last_name,l_first_name
		from HZ_PARTIES p, HZ_RELATIONSHIPS r
		where r.party_id = l_primary_contact_id
		and r.subject_id = p.party_id
		and r.subject_type = 'PERSON'
		and r.subject_table_name = 'HZ_PARTIES'
		and r.directional_flag = 'F';
Line: 1471

          SELECT *
            FROM cs_hz_sr_contact_points
           WHERE sr_contact_point_id = p_sr_contact_point_id ;