APPS.CSM_SERVICE_HISTORY_EVENT_PKG SQL Statements

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

Line: 23

PROCEDURE DELETE_HISTORY_SR_RECORD( p_incident_id NUMBER
                                  , p_history_id  NUMBER
                                  , p_user_id NUMBER )
IS
l_sqlerrno VARCHAR2(20);

Line: 33

   SELECT TASK_ASSIGNMENT_ID
   FROM JTF_TASKS_B              tk
   ,    JTF_TASK_ASSIGNMENTS ta
   ,    JTF_TASK_TYPES_B       tt
   ,    JTF_TASK_STATUSES_B    tkst
   ,    JTF_TASK_STATUSES_B    tast
   WHERE  tk.SOURCE_OBJECT_ID = b_incident_id
    AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
    AND tk.TASK_ID = ta.TASK_ID
   AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
   AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
   AND (tkst.CLOSED_FLAG = 'Y'
    OR tkst.COMPLETED_FLAG = 'Y')
   AND nvl(tkst.CANCELLED_FLAG,'N') <> 'Y'
   AND nvl(tkst.REJECTED_FLAG,'N')  <> 'Y'
   AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
   AND tt.RULE = 'DISPATCH'
   AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
   AND (tast.CLOSED_FLAG = 'Y'
    OR tast.COMPLETED_FLAG = 'Y')
   AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
   AND nvl(tast.REJECTED_FLAG,'N')  <> 'Y' ;

Line: 58

  CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECORD',
                         'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECOR',FND_LOG.LEVEL_PROCEDURE);

Line: 72

    CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECORD',
                         'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECOR',FND_LOG.LEVEL_EXCEPTION);

Line: 79

    l_error_msg := l_error_msg ||'- Exception in  DELETE_HISTORY_SR_RECORD for incident_id:' || p_incident_id
                    || ' and user_id: ' || p_user_id  || ':' || l_sqlerrno || ':' || l_sqlerrmsg;

Line: 81

    CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY_SR_RECORD',FND_LOG.LEVEL_EXCEPTION);

Line: 83

END DELETE_HISTORY_SR_RECORD;

Line: 98

   SELECT TASK_ASSIGNMENT_ID
   FROM JTF_TASKS_B              tk
   ,    JTF_TASK_ASSIGNMENTS ta
   ,    JTF_TASK_TYPES_B       tt
   ,    JTF_TASK_STATUSES_B    tkst
   ,    JTF_TASK_STATUSES_B    tast
   WHERE  tk.SOURCE_OBJECT_ID = b_hist_incident_id
    AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
    AND tk.TASK_ID = ta.TASK_ID
   AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
   AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
   AND (tkst.CLOSED_FLAG = 'Y'
    OR tkst.COMPLETED_FLAG = 'Y')
   AND nvl(tkst.CANCELLED_FLAG,'N') <> 'Y'
   AND nvl(tkst.REJECTED_FLAG,'N')  <> 'Y'
   AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
   AND tt.RULE = 'DISPATCH'
   AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
   AND (tast.CLOSED_FLAG = 'Y'
    OR tast.COMPLETED_FLAG = 'Y')
   AND nvl(tast.CANCELLED_FLAG,'N') <> 'Y'
   AND nvl(tast.REJECTED_FLAG,'N')  <> 'Y'
   AND NOT EXISTS
   (SELECT 1
    FROM csm_service_history_acc acc
    WHERE acc.user_id = b_user_id
    AND acc.incident_id = b_incident_id
    AND acc.history_incident_id = b_hist_incident_id
    );

Line: 169

SELECT CUSTOMER_PRODUCT_ID
,      INSTALL_SITE_ID
,      CUSTOMER_ID
,      INCIDENT_LOCATION_ID
FROM CS_INCIDENTS_ALL_B
WHERE INCIDENT_ID = b_incident_id;

Line: 181

SELECT MAX(tk.SCHEDULED_END_DATE ) AS "TASK_TIME"
FROM JTF_TASKS_B tk
,    JTF_TASK_ASSIGNMENTS ta
,    JTF_RS_RESOURCE_EXTNS rs
WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND   tk.SOURCE_OBJECT_ID = b_incident_id
AND   tk.TASK_ID = ta.TASK_ID
AND   ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND   ta.RESOURCE_ID = rs.resource_id
AND   rs.user_id = b_user_id ;

Line: 197

SELECT DISTINCT inc.INCIDENT_ID
,               inc.CLOSE_DATE
FROM CS_INCIDENTS_ALL_B       inc
,    JTF_TASKS_B              tk
,    JTF_TASK_ASSIGNMENTS ta
,    CS_INCIDENT_STATUSES_B   ists
,    JTF_TASK_TYPES_B       tt
,    JTF_TASK_STATUSES_B    tkst
,    JTF_TASK_STATUSES_B    tast
WHERE inc.CLOSE_DATE <= b_max_date
AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
--AND inc.install_site_id IS NOT NULL
AND ists.CLOSE_FLAG = 'Y'
AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.TASK_ID = ta.TASK_ID
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
AND (tkst.CLOSED_FLAG = 'Y' OR tkst.COMPLETED_FLAG = 'Y')
AND NVL(tkst.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(tkst.REJECTED_FLAG,'N')  <> 'Y'
AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
AND tt.RULE = 'DISPATCH'
AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
AND (tast.CLOSED_FLAG = 'Y' OR tast.COMPLETED_FLAG = 'Y')
AND NVL(tast.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(tast.REJECTED_FLAG,'N')  <> 'Y'
AND inc.CUSTOMER_PRODUCT_ID = b_customer_product_id
ORDER BY inc.CLOSE_DATE DESC;

Line: 231

SELECT DISTINCT inc.INCIDENT_ID
,               inc.CLOSE_DATE
FROM CS_INCIDENTS_ALL_B       inc
,    JTF_TASKS_B              tk
,    JTF_TASK_ASSIGNMENTS ta
,    CS_INCIDENT_STATUSES_B   ists
,    JTF_TASK_TYPES_B       tt
,    JTF_TASK_STATUSES_B    tkst
,    JTF_TASK_STATUSES_B    tast
WHERE inc.CLOSE_DATE <= b_max_date
AND inc.INCIDENT_ID = tk.SOURCE_OBJECT_ID
AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
--AND inc.install_site_id IS NOT NULL
AND ists.CLOSE_FLAG = 'Y'
AND tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND tk.TASK_ID = ta.TASK_ID
AND ta.ASSIGNEE_ROLE = 'ASSIGNEE'
AND tk.TASK_STATUS_ID = tkst.TASK_STATUS_ID
AND (tkst.CLOSED_FLAG = 'Y' OR tkst.COMPLETED_FLAG = 'Y')
AND NVL(tkst.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(tkst.REJECTED_FLAG,'N')  <> 'Y'
AND tk.TASK_TYPE_ID = tt.TASK_TYPE_ID
AND tt.RULE = 'DISPATCH'
AND ta.ASSIGNMENT_STATUS_ID = tast.TASK_STATUS_ID
AND (tast.CLOSED_FLAG = 'Y' OR tast.COMPLETED_FLAG = 'Y')
AND NVL(tkst.CANCELLED_FLAG,'N') <> 'Y'
AND NVL(tkst.REJECTED_FLAG,'N')  <> 'Y'
AND inc.CUSTOMER_ID = b_customer_id
AND inc.INCIDENT_LOCATION_ID = b_INCIDENT_LOCATION_ID
ORDER BY inc.CLOSE_DATE DESC;

Line: 266

SELECT HISTORY_INCIDENT_ID
FROM   CSM_SERVICE_HISTORY_ACC
WHERE  INCIDENT_ID = b_incident_id
AND    USER_ID = b_user_id;

Line: 290

/*      IF IT MATCHES RECORD DOES NOT NEED TO BE INSERTED AND THE PLSQL RECORD SHOULD BE DELETED*/
/*      AT THE END PUSH A DELETE FOR THE REMAINING RECORDS AS THEY ARE NO LONGER NEEDED*/
 OPEN c_history( l_incident_id, l_user_id );

Line: 320

	          /*Record does exist do not insert but remove reference from list*/
              CSM_UTIL_PKG.LOG('Already replicated, deleting from PLSQL table',
                          'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);

Line: 323

	          l_history_table.DELETE(i);

Line: 329

          /*Record does not yet exists so insert*/
          CSM_UTIL_PKG.LOG('Record not replicated yet; push it to client(s)',

Line: 338

        /*Record does not yet exists so insert*/
        CREATE_HISTORY_SR_RECORD( p_incident_id => l_incident_id
                                , p_history_id  => r_get_cp_history.incident_id
                                , p_user_id => l_user_id
  			        , p_closed_date => r_get_cp_history.close_date );

Line: 349

    /*Push delete to history records that are no longer history record*/
    IF l_history_table.COUNT > 0 THEN
      FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
        IF l_history_table.EXISTS(i) THEN
          DELETE_HISTORY_SR_RECORD( l_incident_id, l_history_table(i), l_user_id );

Line: 377

	            /*Record does exist do not insert but remove reference from list*/
                 CSM_UTIL_PKG.LOG('Already replicated, deleting from PLSQL table',
                         'CSM_SERVICE_HISTORY_EVENT_PKG.CALCULATE_HISTORY',FND_LOG.LEVEL_PROCEDURE);

Line: 380

	             l_history_table.DELETE(i);

Line: 386

          /*Record does not yet exists so insert*/
          CSM_UTIL_PKG.LOG('Record not replicated yet; push it to client(s)',

Line: 405

    /*Push delete to history records that are no longer history record*/
    IF l_history_table.COUNT > 0 THEN
      FOR i IN l_history_table.FIRST .. l_history_table.LAST LOOP
        IF l_history_table.EXISTS(i) THEN
          DELETE_HISTORY_SR_RECORD( l_incident_id, l_history_table(i), l_user_id );

Line: 439

SELECT acc.INCIDENT_ID
,      acc.USER_ID
FROM CSM_INCIDENTS_ALL_ACC acc
,    CS_INCIDENTS_ALL_B inc
,    CS_INCIDENT_STATUSES_b cis
,    JTF_TASKS_B tsk
,    JTF_TASK_ASSIGNMENTS ta
,    JTF_RS_RESOURCE_EXTNS rs
WHERE tsk.SOURCE_OBJECT_ID = inc.INCIDENT_ID
AND   tsk.SOURCE_OBJECT_TYPE_CODE = 'SR'
AND   tsk.TASK_ID = ta.TASK_ID
AND   ta.RESOURCE_ID = rs.RESOURCE_ID
AND   rs.USER_ID = acc.USER_ID
AND   inc.INCIDENT_ID = acc.INCIDENT_ID
AND   inc.incident_status_id = cis.incident_status_id
AND   NVL(cis.close_flag,'N') <> 'Y'
AND   tsk.SCHEDULED_END_DATE >= TRUNC(SYSDATE)
AND   NVL(inc.CLOSE_DATE, SYSDATE ) >= SYSDATE;

Line: 462

SELECT /*+INDEX (hacc CSM_SERVICE_HISTORY_ACC_U1)*/ user_id,
       incident_id,
       history_incident_id
FROM   csm_service_history_acc hacc
WHERE NOT EXISTS
 (SELECT 'X'
  FROM csm_incidents_all_acc acc
  WHERE acc.user_id = hacc.user_id
  AND acc.incident_id = hacc.incident_id);

Line: 474

SELECT 1
FROM jtm_con_request_data
WHERE product_code = 'CSM'
AND package_name = 'CSM_SERVICE_HISTORY_EVENT_PKG'
AND procedure_name = 'CONCURRENT_HISTORY'
FOR UPDATE OF last_run_date NOWAIT ;

Line: 504

    DELETE_HISTORY_SR_RECORD(p_incident_id=>r_purge_SR_history.incident_id,
                             p_history_id=>r_purge_SR_history.history_incident_id,
                             p_user_id=>r_purge_SR_history.user_id);

Line: 513

     UPDATE jtm_con_request_data
     SET last_run_date = l_last_run_date
     WHERE CURRENT OF l_upd_last_run_date_csr;

Line: 551

   CSM_ACC_PKG.Insert_Acc
   ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
    ,P_ACC_TABLE_NAME         => g_acc_table_name
    ,P_SEQ_NAME               => g_seq_name
    ,P_PK1_NAME               => g_pk1_name
    ,P_PK1_NUM_VALUE          => p_parent_incident_id
    ,P_PK2_NAME               => g_pk2_name
    ,P_PK2_NUM_VALUE          => p_incident_id
    ,p_USER_ID                => p_user_id
    );

Line: 575

PROCEDURE DELETE_HISTORY(p_task_assignment_id IN NUMBER,
                         p_incident_id IN NUMBER,
                         p_user_id IN NUMBER)
IS
l_sqlerrno VARCHAR2(20);

Line: 585

   SELECT HISTORY_INCIDENT_ID
   FROM   CSM_SERVICE_HISTORY_ACC
   WHERE  INCIDENT_ID = b_incident_id
   AND    USER_ID = b_user_id
   AND NOT EXISTS (SELECT 'X'
                   FROM CSM_TASK_ASSIGNMENTS_ACC ACC,
                        JTF_TASK_ASSIGNMENTS ASG,
                        JTF_TASKS_B TASK
                   WHERE ACC.TASK_ASSIGNMENT_ID = ASG.TASK_ASSIGNMENT_ID
                     AND ASG.TASK_ID = TASK.TASK_ID
                     AND TASK.SOURCE_OBJECT_TYPE_CODE = 'SR'
                     AND TASK.SOURCE_OBJECT_ID = b_incident_id
                     AND ACC.USER_ID = b_user_id
                     AND ACC.TASK_ASSIGNMENT_ID <> b_task_assignment_id);

Line: 601

   CSM_UTIL_PKG.LOG('Entering DELETE_HISTORY for incident_id: ' || p_incident_id
                     || ' and task_assignment_id:' || p_task_assignment_id,
                         'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY',FND_LOG.LEVEL_PROCEDURE);

Line: 606

    DELETE_HISTORY_SR_RECORD( p_incident_id, r_history.HISTORY_INCIDENT_ID, p_user_id );

Line: 609

   CSM_UTIL_PKG.LOG('Leaving DELETE_HISTORY for incident_id: ' || p_incident_id
                     || ' and task_assignment_id:' || p_task_assignment_id,
                         'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY',FND_LOG.LEVEL_PROCEDURE);

Line: 616

        l_error_msg := ' Exception in  DELETE_HISTORY for incident_id: ' || p_incident_id
                     || ' and task_assignment_id:' || p_task_assignment_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;

Line: 618

        CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SERVICE_HISTORY_EVENT_PKG.DELETE_HISTORY',FND_LOG.LEVEL_EXCEPTION);

Line: 620

END DELETE_HISTORY;

Line: 636

   CSM_ACC_PKG.Delete_Acc
    ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
     ,P_ACC_TABLE_NAME         => g_acc_table_name
     ,P_PK1_NAME               => g_pk1_name
     ,P_PK1_NUM_VALUE          => p_parent_incident_id
     ,P_PK2_NAME               => g_pk2_name
     ,P_PK2_NUM_VALUE          => p_incident_id
     ,p_USER_ID                => p_user_id
    );

Line: 685

SELECT ciab.incident_id
     , ciia.user_id
     , ciia.instance_id
FROM   cs_incidents_all_b ciab
     , cs_incident_statuses_b cisb
     , csm_item_instances_acc ciia
     , jtf_tasks_b jtb
WHERE  ciab.customer_product_id      = ciia.instance_id
AND    ciab.incident_status_id       = cisb.incident_status_id
AND    cisb.close_flag               = 'Y'
AND    ciab.incident_id              = jtb.source_object_id
AND    jtb.source_object_type_code   = 'SR'
and    jtb.scheduled_start_date > (sysdate - NVL(fnd_profile.value_specific('CSF_M_HISTORY'),100))
AND    ciia.user_id IN ( SELECT cpa.user_id
                         FROM   csm_party_assignment cpa
                         WHERE  cpa.deleted_flag ='N'
                       )
AND    NOT EXISTS      ( SELECT 1
                         FROM   csm_service_history_acc csha
                         WHERE  csha.user_id = ciia.user_id
                         AND    csha.history_incident_id = ciab.incident_id
                       );

Line: 714

SELECT csha.history_incident_id
     , csha.user_id
     , csha.instance_id
FROM   csm_service_history_acc csha
WHERE  csha.incident_id=1
AND    NOT EXISTS      ( SELECT 1
                         FROM   csm_item_instances_acc ciia
                         WHERE  ciia.user_id     = csha.user_id
                         AND    ciia.instance_id = csha.instance_id
                       );

Line: 743

         l_instance_id_tbl.DELETE;

Line: 749

         l_incident_id_tbl.DELETE;

Line: 755

         l_user_id_tbl.DELETE;

Line: 768

                CSM_ACC_PKG.Insert_Acc
                  ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
                   ,P_ACC_TABLE_NAME         => g_acc_table_name
                   ,P_SEQ_NAME               => g_seq_name
                   ,P_PK1_NAME               => g_pk1_name
                   ,P_PK1_NUM_VALUE          => l_parent_incident_id
                   ,P_PK2_NAME               => g_pk2_name
                   ,P_PK2_NUM_VALUE          => l_incident_id_tbl(i)
                   ,P_PK3_NAME               => g_pk3_name
                   ,P_PK3_NUM_VALUE          => l_instance_id_tbl(i)
                   ,p_USER_ID                => l_user_id_tbl(i)
                  );

Line: 787

               CSM_ACC_PKG.Insert_Acc
	        ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
	         ,P_ACC_TABLE_NAME         => g_incidents_acc_table_name
	         ,P_SEQ_NAME               => g_incidents_seq_name
	         ,P_PK1_NAME               => g_incidents_pk1_name
	         ,P_PK1_NUM_VALUE          => l_incident_id_tbl(i)
	         ,P_USER_ID                => l_user_id_tbl(i)
                );

Line: 814

           l_instance_id_tbl.DELETE;

Line: 820

           l_incident_id_tbl.DELETE;

Line: 826

           l_user_id_tbl.DELETE;

Line: 839

                  CSM_ACC_PKG.Delete_Acc
                    ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
                     ,P_ACC_TABLE_NAME         => g_acc_table_name
                     ,P_PK1_NAME               => g_pk1_name
                     ,P_PK1_NUM_VALUE          => l_parent_incident_id
                     ,P_PK2_NAME               => g_pk2_name
                     ,P_PK2_NUM_VALUE          => l_incident_id_tbl(i)
                     ,P_PK3_NAME               => g_pk3_name
                     ,P_PK3_NUM_VALUE          => l_instance_id_tbl(i)
                     ,p_USER_ID                => l_user_id_tbl(i)
                    );

Line: 855

	        --call the CSM_ACC_PKG to Delete from CSM_INCIDENTS_ALL_ACC table

	          CSM_ACC_PKG.Delete_Acc
	      	    ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
	      	     ,P_ACC_TABLE_NAME         => g_incidents_acc_table_name
	      	     ,P_PK1_NAME               => g_incidents_pk1_name
	      	     ,P_PK1_NUM_VALUE          => l_incident_id_tbl(i)
	      	     ,P_USER_ID                => l_user_id_tbl(i)
	            );