APPS.CSL_SERVICE_REQUESTS_PKG SQL Statements

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

Line: 12

  SELECT *
  FROM  CSL_CS_INCIDENTS_ALL_VL_inq
  WHERE tranid$$ = b_tranid
  AND   clid$$cs = b_user_name;

Line: 18

  SELECT *
  FROM  CSL_CS_HZ_SR_CONTACT_PTS_INQ
  WHERE INCIDENT_ID = b_incident_id
  AND   TRANID$$ = b_tranid
  AND   clid$$cs = b_user_name;

Line: 25

  This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
         (
           p_record        IN c_incident%ROWTYPE,
           p_error_msg     OUT NOCOPY    VARCHAR2,
           x_return_status IN OUT NOCOPY VARCHAR2
         ) IS

  -- Commented Out for Sql Repository Performance Fix

  /* CURSOR c_org_rel_contacts
    ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
    ) IS
    SELECT b.party_id
    FROM   CSC_HZ_PARTIES_V  b
      WHERE  b.object_id = b_customer_id
--      AND    b.party_id = p_customer_contact_id
      AND    b.sub_status  =  'A'
      AND    b.obj_status  = 'A'
      AND    b.relation in ('CONTACT_OF','EMPLOYEE_OF')
      AND    b.party_type = 'PARTY_RELATIONSHIP'
--      AND    b.obj_party_type = 'ORGANIZATION' -- can be both org and person caller type
      AND    rownum <= 1;

Line: 53

    SELECT b.subject_id
    FROM   CSC_HZ_PARTIES_V  b
    WHERE  b.object_id = b_customer_id
--    AND    b.subject_id = p_customer_contact_id
    AND    b.sub_status  =  'A'
    AND    b.obj_status  =  'A'
    AND    rownum <= 1
    UNION
    SELECT PARTY_ID
    FROM HZ_PARTIES
    WHERE party_id = b_customer_id
    AND status ='A';  */

Line: 70

    SELECT b.party_id FROM HZ_RELATIONSHIPS  b
      WHERE  b.object_id = b_customer_id
      AND    object_table_name = 'HZ_PARTIES'
      AND    status  =  'A'
      AND    b.relationship_code in ('CONTACT_OF','EMPLOYEE_OF')
      AND    rownum <= 1;

Line: 81

    SELECT b.subject_id FROM HZ_RELATIONSHIPS  b
    WHERE  b.object_id = b_customer_id
      AND    object_table_name = 'HZ_PARTIES'
      AND    status  =  'A'
      AND    rownum <= 1;

Line: 91

    SELECT PARTY_ID FROM HZ_PARTIES
    WHERE party_id = b_customer_id AND status = 'A';

Line: 100

  SELECT use.party_site_use_id FROM
    hz_party_sites site, hz_party_site_uses use
  WHERE site.party_site_id = use.party_site_id
        AND site.status= 'A'
	AND use.site_use_type= 'BILL_TO'
        AND use.primary_per_type = 'Y'
	AND use.status = 'A'
        AND site.party_id = b_customer_id
        AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
            AND (NVL(use.end_date, SYSDATE));

Line: 116

  SELECT use.party_site_use_id FROM
    hz_party_sites site, hz_party_site_uses use
  WHERE site.party_site_id = use.party_site_id
        AND site.status= 'A'
	AND use.site_use_type= 'SHIP_TO'
        AND use.primary_per_type = 'Y'
	AND use.status = 'A'
        AND site.party_id = b_customer_id
        AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
            AND (NVL(use.end_date, SYSDATE));

Line: 132

  SELECT custa.cust_account_id FROM hz_cust_accounts custa
    WHERE custa.status = 'A'
    AND custa.party_id = b_customer_id;

Line: 167

    , v_message     => 'Entering ' || g_object_name || '.APPLY_INSERT'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 397

    , v_message     => 'Leaving ' || g_object_name || '.APPLY_INSERT'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 406

    , v_message     => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 410

  fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);

Line: 420

    , v_message     => 'Leaving ' || g_object_name || '.APPLY_INSERT'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 425

END APPLY_INSERT;

Line: 428

  This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
***/
PROCEDURE APPLY_UPDATE
         (
           p_record        IN c_incident%ROWTYPE,
           p_error_msg     OUT NOCOPY    VARCHAR2,
           x_return_status IN OUT NOCOPY VARCHAR2
         ) IS
  cursor c_ovn
    ( b_incident_id number
    )
  is
    select incident_id
    ,      object_version_number
    from   cs_incidents
    where  incident_id = b_incident_id;

Line: 447

  cursor c_last_update_date
     ( b_incident_id NUMBER
	 )
  is
    SELECT LAST_UPDATE_DATE
	from CS_INCIDENTS_ALL_B
	where incident_id = b_incident_id;

Line: 455

  r_last_update_date     c_last_update_date%ROWTYPE;

Line: 461

    SELECT CREDIT_CARD_NUMBER, CREDIT_CARD_TYPE_CODE,
           CREDIT_CARD_EXPIRATION_DATE, CREDIT_CARD_HOLDER_FNAME,
	   CREDIT_CARD_HOLDER_MNAME, CREDIT_CARD_HOLDER_LNAME, CREDIT_CARD_ID
    FROM CS_INCIDENTS_ALL_B
    WHERE incident_id = b_incident_id;

Line: 488

    , v_message     => 'Entering ' || g_object_name || '.APPLY_UPDATE'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 496

    open c_last_update_date(b_incident_id => p_record.incident_id);

Line: 497

    fetch c_last_update_date into r_last_update_date;

Line: 498

    if c_last_update_date%found then
      if r_last_update_date.last_update_date <> p_record.last_update_date then
        close c_last_update_date;

Line: 518

          , v_message     => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
          , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 534

    close c_last_update_date;

Line: 573

/* Update of contract line must be removed see bug 2610677
l_sr_rec.CONTRACT_SERVICE_ID := p_record.CONTRACT_SERVICE_ID; */

Line: 654

  l_sr_rec.last_update_program_code := 'CSL_LAPTOP';

Line: 659

  CS_ServiceRequest_PUB.Update_ServiceRequest
    ( p_api_version           => 3.0
    , p_init_msg_list         => FND_API.G_TRUE
    , p_commit                => FND_API.G_TRUE
    , x_return_status         => x_return_status
    , x_msg_count             => l_msg_count
    , x_msg_data              => l_msg_data
    , p_request_id            => p_record.incident_id
    , p_object_version_number => l_ovn
    , p_last_updated_by       => l_user_id
    , p_last_update_date      => sysdate
    , p_service_request_rec   => l_sr_rec
    , p_notes                 => l_notes_tab
    , p_contacts              => l_contacts_tab
    , p_resp_id              => to_number(fnd_profile.value('CSL_SR_CREATE_RESP'))
    , x_workflow_process_id   => l_workflow_id
    , x_interaction_id        => l_interaction_id
    );

Line: 688

       , v_message     => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
       , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 698

    , v_message     => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 707

    , v_message     => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 711

  fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);

Line: 721

    , v_message     => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 726

END APPLY_UPDATE;

Line: 763

    APPLY_INSERT
      (
        p_record,
        p_error_msg,
        x_return_status
      );

Line: 771

    APPLY_UPDATE
      (
       p_record,
       p_error_msg,
       x_return_status
     );

Line: 783

        , v_message     => 'Delete is not supported for this entity'
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 868

  a fast sync. This procedure will insert the data that came from mobile into the backend tables using
  public APIs.
***/
PROCEDURE APPLY_CLIENT_CHANGES
         (
           p_user_name     IN VARCHAR2,
           p_tranid        IN NUMBER,
           p_debug_level   IN NUMBER,
           x_return_status IN OUT NOCOPY VARCHAR2
         ) IS

  l_process_status VARCHAR2(1);

Line: 906

      /*** Yes -> delete record from inqueue ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( v_object_id   => r_incident.incident_id
        , v_object_name => g_object_name
        , v_message     => 'Record successfully processed, deleting from inqueue'
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 915

      CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
        (
          p_user_name,
          p_tranid,
          r_incident.seqno$$,
          r_incident.incident_id,
          g_object_name,
          g_pub_name,
          l_error_msg,
          l_process_status
        );

Line: 926

      /*** was delete successful? ***/
      IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
        IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
          jtm_message_log_pkg.Log_Msg
          ( v_object_id   => r_incident.incident_id
          , v_object_name => g_object_name
          , v_message     => 'Deleting from inqueue failed'
          , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 939

        /* Delete matching contact record(s) */
        CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
          (
            p_user_name,
            p_tranid,
            r_contacts.seqno$$,
            r_contacts.SR_CONTACT_POINT_ID,
            g_object_name,
            'CSL_CS_HZ_SR_CONTACT_PTS',
            l_error_msg,
            l_process_status
          );

Line: 951

          /*** was delete successful? ***/
          IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
            IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
              jtm_message_log_pkg.Log_Msg
               ( v_object_id   => r_contacts.SR_CONTACT_POINT_ID
               , v_object_name => g_object_name
               , v_message     => 'Deleting from inqueue failed'
               , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);