APPS.CSM_SERVICE_REQUESTS_PKG SQL Statements

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

Line: 12

/* Select all inq records */
CURSOR c_incident( b_user_name VARCHAR2, b_tranid NUMBER) is
  SELECT *
  FROM  CSM_INCIDENTS_ALL_INQ
  WHERE tranid$$ = b_tranid
  AND   clid$$cs = b_user_name;

Line: 19

/* Select all contact records for incident from inq table */
CURSOR c_contact( b_incident_id NUMBER, b_tranid NUMBER, b_user_name VARCHAR2 ) IS
  SELECT *
  FROM  CSF_M_SR_CONTACTS_INQ
  WHERE INCIDENT_ID = b_incident_id
  AND   TRANID$$ = b_tranid
  AND   clid$$cs = b_user_name;

Line: 30

SELECT 	APP_ID
FROM 	asg_user
WHERE 	user_id = l_userid;

Line: 36

SELECT 	RESPONSIBILITY_ID
FROM 	asg_user
WHERE 	user_id = l_userid;

Line: 43

SELECT 	1
FROM 	mtl_system_items_b
WHERE 	inventory_item_id = p_inventory_item_id
AND 	organization_id = p_organization_id;

Line: 48

/* Cursor to select party Type */
CURSOR c_party  ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE )
IS
SELECT PARTY_TYPE
FROM   HZ_PARTIES
WHERE  party_id = b_customer_id;

Line: 56

  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

  /* Bug 3917132
     Cursor to get the Bill to Address */
  CURSOR c_bill_to_site_id
    ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
    ) IS
  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: 86

  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: 100

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

Line: 108

  SELECT install_location_id
  FROM 	 csi_item_instances
  WHERE  instance_id = p_customer_product_id
  AND 	 install_location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS');

Line: 148

  CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT for incident_id ' || p_record.incident_id ,
                         'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);

Line: 278

     SELECT master_organization_id
     INTO l_org_id
     FROM mtl_parameters
     WHERE organization_id = l_sr_rec.inventory_org_id;

Line: 336

                         'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EVENT);

Line: 338

  /* Calling CS API for actual insert */
  CS_ServiceRequest_PUB.Create_ServiceRequest
    ( p_api_version          => 4.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_user_id              => l_created_by
    , p_org_id               => p_record.org_id
    , p_request_id           => p_record.incident_id
    , p_request_number       => p_record.incident_number
    , p_service_request_rec  => l_sr_rec
    , p_notes                => l_notes_tab
    , p_contacts             => l_contacts_tab
    , p_resp_id		         => l_resp_id
    , p_default_contract_sla_ind => 'Y'
    , p_auto_generate_tasks      => l_auto_generate_task
    , p_auto_assign 		 => 'Y'
    , x_sr_create_out_rec	 => l_sr_out_rec
    );

Line: 366

    CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
               || ' ROOT ERROR: CS_ServiceRequest_PUB.Create_ServiceRequest ' || sqlerrm
               || ' for incident_id ' || p_record.incident_id,'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);

Line: 372

  CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT for incident_id ' || p_record.incident_id ,
                         'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);

Line: 376

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

Line: 381

     CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm
               || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);

Line: 389

END APPLY_INSERT;

Line: 392

  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 to select object_version_number */
  cursor c_ovn
    ( b_incident_id number
    )
  is
    select incident_id
    ,      object_version_number
    from   cs_incidents
    where  incident_id = b_incident_id;

Line: 412

/* Cursor to select last_update_date */
  cursor c_last_update_date
     ( b_incident_id NUMBER
	 )
  is
    SELECT LAST_UPDATE_DATE,
           LAST_UPDATED_BY
	from CS_INCIDENTS_ALL_B
	where incident_id = b_incident_id;

Line: 425

  SELECT install_location_id
  FROM csi_item_instances
  WHERE instance_id = p_customer_product_id
  AND install_location_type_code = 'HZ_PARTY_SITES';

Line: 433

  r_last_update_date     c_last_update_date%ROWTYPE;

Line: 450

  l_last_updated_by       NUMBER;

Line: 455

  CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE for incident_id ' || p_record.incident_id ,
                         'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 461

    open c_last_update_date(b_incident_id => p_record.incident_id);

Line: 462

    fetch c_last_update_date into r_last_update_date;

Line: 463

    if c_last_update_date%found then
      if r_last_update_date.last_update_date <> p_record.server_last_update_date and r_last_update_date.last_updated_by <> asg_base.get_user_id(p_record.clid$$cs) then
               close c_last_update_date;

Line: 467

               p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE: Incident_id = '
               || p_record.incident_id;

Line: 469

               csm_util_pkg.log('UPWARD SYNC CONFLICT: CLIENT LOST: CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE: Incident_id = '
               || p_record.incident_id,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);

Line: 475

                         'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 477

    close c_last_update_date;

Line: 487

  l_last_updated_by := p_record.LAST_UPDATED_BY;

Line: 572

     SELECT master_organization_id
     INTO l_org_id
     FROM mtl_parameters
     WHERE organization_id = l_sr_rec.inventory_org_id;

Line: 657

  l_sr_rec.last_update_program_code := 'CSM_UPSYNC_WRAPPER';

Line: 663

  CSM_UTIL_PKG.LOG('Before calling CS_ServiceRequest_PUB.Update_ServiceRequest for ' || p_record.incident_id ,
                         'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EVENT);

Line: 667

  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_last_updated_by
    , 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		          => l_resp_id
    , p_default_contract_sla_ind  => 'Y'
    , x_workflow_process_id   => l_workflow_id
    , x_interaction_id        => l_interaction_id
    );

Line: 692

    CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
               || ' ROOT ERROR: CS_ServiceRequest_PUB.Update_ServiceRequest ' || sqlerrm
               || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);

Line: 699

  CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE for incident_id ' || p_record.incident_id ,
                         'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 703

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

Line: 708

     CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE: ' || sqlerrm
               || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION);

Line: 712

END APPLY_UPDATE;

Line: 735

    APPLY_INSERT
      (
        p_record,
        p_error_msg,
        x_return_status
      );

Line: 743

    APPLY_UPDATE
      (
       p_record,
       p_error_msg,
       x_return_status
     );

Line: 751

        ( 'Delete is not supported for this entity'
      || ' for Incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);

Line: 796

  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: 829

      /*** Yes -> delete record from inqueue ***/
      CSM_UTIL_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: 841

      /*** was delete successful? ***/
      IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
       /*** no -> rollback ***/
          CSM_UTIL_PKG.LOG
          ( 'Deleting from inqueue failed, rolling back to savepoint'
      || ' for incident_id ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here

Line: 850

      /*** Yes -> Delete contact recs */
      FOR r_contacts IN c_contact( r_incident.incident_id, p_tranid, p_user_name ) LOOP
        /* Delete matching contact record(s) */
        CSM_UTIL_PKG.DELETE_RECORD
          (
            p_user_name,
            p_tranid,
            r_contacts.seqno$$,
            r_contacts.SR_CONTACT_POINT_ID,
            g_object_name,
            'CSF_M_SR_CONTACTS',
            l_error_msg,
            l_process_status
          );

Line: 864

          /*** was delete successful? ***/
          IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
          /*** no -> rollback ***/
              CSM_UTIL_PKG.LOG
              ( 'Deleting from inqueue failed, rolling back to savepoint'
                || ' for incident_id ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here

Line: 946

SELECT b.last_updated_by
FROM CS_INCIDENTS_ALL_B b,
     CSM_INCIDENTS_ALL_INQ a
WHERE a.clid$$cs = l_user_name
AND tranid$$ = l_tran_id
AND seqno$$ = l_sequence
AND a.incident_id = b.incident_id ;