APPS.CSM_BUS_PROCESS_TXNS_EVENT_PKG SQL Statements

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

Line: 19

  backend table for deletes, updates and inserts.
  Refreshes for all the users
  Also adds corresponding entries in to SDQ
*/
PROCEDURE Refresh_Acc(p_status OUT NOCOPY VARCHAR2,
                       p_message OUT NOCOPY VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;

Line: 27

l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 28

l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 31

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 37

SELECT nvl(last_run_date, (sysdate - 365*50) )
FROM jtm_con_request_data
WHERE package_name = 'CSM_BUS_PROCESS_TXNS_EVENT_PKG'
AND procedure_name = 'REFRESH_ACC';

Line: 42

CURSOR l_deletes_cur
IS
    SELECT acc.access_id
       FROM CSM_BUS_PROCESS_TXNS_ACC acc
       WHERE NOT EXISTS
         (SELECT bpt.BUSINESS_PROCESS_ID
          FROM CS_BUS_PROCESS_TXNS bpt,
               CS_BUSINESS_PROCESSES bpr
          WHERE bpt.transaction_type_id = acc.transaction_type_id
            AND bpt.business_process_id = acc.business_process_id
            AND bpr.business_process_id = bpt.business_process_id
            AND bpr.field_service_flag = 'Y');

Line: 56

select csm_bus_processes_acc_s.nextval  FROM dual;

Line: 60

l_bpr_last_update_date CS_BUSINESS_PROCESSES.LAST_UPDATE_DATE%TYPE;

Line: 61

l_bpt_last_update_date CS_BUS_PROCESS_TXNS.LAST_UPDATE_DATE%TYPE;

Line: 62

l_max_update_date CSM_BUS_PROCESS_TXNS_ACC.LAST_UPDATE_DATE%TYPE;

Line: 72

  FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 81

  /****** DELETES  **********/
  --Process deletes only if particular access_id is passed
  --open the cursor
   open l_deletes_cur;

Line: 87

     FETCH l_deletes_cur INTO l_access_id;

Line: 88

     EXIT WHEN l_deletes_cur%NOTFOUND;

Line: 100

     DELETE FROM CSM_BUS_PROCESS_TXNS_ACC
       WHERE ACCESS_ID = l_access_id;

Line: 106

   close l_deletes_cur;

Line: 108

  /******* UPDATES **********/
  --generate sql for updates
  l_dsql :=
    'SELECT acc.access_id, bpt.last_update_date, bpr.last_update_date
       FROM CS_BUS_PROCESS_TXNS bpt,
            CS_BUSINESS_PROCESSES bpr,
            CSM_BUS_PROCESS_TXNS_ACC acc
       WHERE bpr.business_process_id = bpt.business_process_id
         AND bpt.transaction_type_id = acc.transaction_type_id
         AND bpt.business_process_id = acc.business_process_id
         AND (bpt.last_update_date > :1
             or bpr.last_update_date > :2
           )';

Line: 128

   open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date;

Line: 131

     FETCH l_updates_cur INTO l_access_id, l_bpt_last_update_date, l_bpr_last_update_date;

Line: 132

     EXIT WHEN l_updates_cur%NOTFOUND;

Line: 143

     IF (l_bpt_last_update_date > l_bpr_last_update_date) THEN
       l_max_update_date := l_bpt_last_update_date;

Line: 146

       l_max_update_date := l_bpr_last_update_date;

Line: 150

     UPDATE CSM_BUS_PROCESS_TXNS_ACC
       SET LAST_UPDATE_DATE = l_run_date -- l_max_update_date
       WHERE ACCESS_ID = l_access_id;

Line: 157

   close l_updates_cur;

Line: 159

  /****** INSERTS  **********/
  --generate sql for inserts
  l_dsql :=
    'SELECT bpt.business_process_id, bpt.transaction_type_id, bpt.last_update_date, bpr.last_update_date
    FROM CS_BUS_PROCESS_TXNS bpt,
         CS_BUSINESS_PROCESSES bpr
    WHERE bpr.business_process_id = bpt.business_process_id
      AND bpr.field_service_flag = ''Y''
      AND NOT EXISTS
          (SELECT access_id
           FROM CSM_BUS_PROCESS_TXNS_ACC acc
           WHERE bpt.transaction_type_id = acc.transaction_type_id
             AND bpt.business_process_id = acc.business_process_id )';

Line: 181

   open l_inserts_cur for l_dsql;

Line: 184

     FETCH l_inserts_cur INTO l_business_process_id, l_transaction_type_id, l_bpt_last_update_date, l_bpr_last_update_date;

Line: 185

     EXIT WHEN l_inserts_cur%NOTFOUND;

Line: 204

     IF (l_bpt_last_update_date > l_bpr_last_update_date) THEN
       l_max_update_date := l_bpt_last_update_date;

Line: 207

       l_max_update_date := l_bpr_last_update_date;

Line: 211

     INSERT INTO CSM_BUS_PROCESS_TXNS_ACC (ACCESS_ID, BUSINESS_PROCESS_ID,
       TRANSACTION_TYPE_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
       LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
     VALUES (l_access_id, l_business_process_id,
       l_transaction_type_id, fnd_global.user_id, sysdate, fnd_global.user_id,
       l_run_date, fnd_global.user_id);

Line: 221

   close l_inserts_cur;

Line: 224

   UPDATE jtm_con_request_data
   SET last_run_date = l_run_date
   WHERE package_name = 'CSM_BUS_PROCESS_TXNS_EVENT_PKG'
     AND procedure_name = 'REFRESH_ACC';