eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

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


SELECT  INSTANCE_ID, INVENTORY_ITEM_ID, LAST_VLD_ORGANIZATION_ID,
        OWNER_PARTY_ID, INSTALL_LOCATION_ID, OWNER_PARTY_ACCOUNT_ID,
        SERIAL_NUMBER,  INVENTORY_REVISION,
        DECODE(INSTALL_LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS INSTALL_LOCATION_TYPE_CODE,
        LOCATION_ID, DECODE(LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS LOCATION_TYPE_CODE
FROM    CSI_ITEM_INSTANCES
WHERE   INSTANCE_NUMBER  = c_instance_number;

Line: 68


SELECT  INSTANCE_ID, INVENTORY_ITEM_ID, LAST_VLD_ORGANIZATION_ID,
        OWNER_PARTY_ID, INSTALL_LOCATION_ID, OWNER_PARTY_ACCOUNT_ID,
        SERIAL_NUMBER,  INVENTORY_REVISION,
        DECODE(INSTALL_LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS INSTALL_LOCATION_TYPE_CODE,
        LOCATION_ID, DECODE(LOCATION_TYPE_CODE,'HZ_PARTY_SITES','HZ_PARTY_SITE','HZ_LOCATIONS','HZ_LOCATION', NULL) AS LOCATION_TYPE_CODE
FROM    CSI_ITEM_INSTANCES
WHERE   SERIAL_NUMBER  = c_serial_number;

Line: 79


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

Line: 84


/* 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: 94


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


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


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


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


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

Line: 444


                         'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EVENT);

Line: 446


  /* 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: 474


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


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


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

Line: 489


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


END APPLY_INSERT;

Line: 500


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


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

Line: 527


  r_last_update_date     c_last_update_date%ROWTYPE;

Line: 543


  l_last_updated_by       NUMBER;

Line: 553


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


    open c_last_update_date(b_incident_id => p_record.incident_id);

Line: 560


    fetch c_last_update_date into r_last_update_date;

Line: 561


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


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

Line: 567


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


                         'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);

Line: 575


    close c_last_update_date;

Line: 585


  l_last_updated_by := p_record.LAST_UPDATED_BY;

Line: 644


  l_sr_rec.last_update_program_code := 'CSM_UPSYNC_WRAPPER';

Line: 736


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

Line: 802


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


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


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


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


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

Line: 847


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


END APPLY_UPDATE;

Line: 874


    APPLY_INSERT
      (
        p_record,
        p_error_msg,
        x_return_status
      );

Line: 882


    APPLY_UPDATE
      (
       p_record,
       p_error_msg,
       x_return_status
     );

Line: 890


        ( '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: 935


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


      /*** 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: 980


      /*** 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: 989


      /*** 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: 1003


          /*** 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: 1085


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 ;