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_TXN_BILL_TYPES_EVENT_PKG SQL Statements

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

Line: 7


  backend table for deletes, updates and inserts.
  Refreshes for all the users
  Also adds corresponding entries in to SDQ

  MODIFICATION HOSTORY:
  10/06/02 ANURAG added check before inserting into csm_txn_billing_types_acc
      for already existing primary key value
*/

procedure Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
                       p_message OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

Line: 20


l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 21


l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 24


l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 42


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

Line: 47


CURSOR l_deletes_cur
IS
SELECT acc.access_id
FROM CSM_TXN_BILLING_TYPES_ACC acc
WHERE NOT EXISTS
     (select tbt.TXN_BILLING_TYPE_ID
        from cs_txn_billing_types tbt,
             cs_transaction_types_b ttb,
             cs_business_processes bpr,
             cs_bus_process_txns bpt,
             CS_BILLING_TYPE_CATEGORIES cbtc
       where acc.txn_billing_type_id = tbt.txn_billing_type_id
         AND acc.business_process_id = bpt.business_process_id
         AND tbt.transaction_type_id = ttb.transaction_type_id
         AND SYSDATE BETWEEN nvl(tbt.start_date_active, SYSDATE) AND nvl(tbt.end_date_active, SYSDATE)
         AND SYSDATE BETWEEN nvl(ttb.start_date_active, SYSDATE) AND nvl(ttb.end_date_active, SYSDATE)
         AND SYSDATE BETWEEN nvl(bpr.start_date_active, SYSDATE) AND nvl(bpr.end_date_active, SYSDATE)
         AND SYSDATE BETWEEN nvl(bpt.start_date_active, SYSDATE) AND nvl(bpt.end_date_active, SYSDATE)
         AND tbt.billing_type = cbtc.billing_type
         AND cbtc.billing_category IN ( 'L', 'E', 'M' )
         AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
         AND bpt.transaction_type_id = tbt.transaction_type_id
         AND bpr.business_process_id = bpt.business_process_id
         AND bpr.field_service_flag = 'Y'
      );

Line: 77


SELECT tt_tl.LANGUAGE
FROM cs_transaction_types_tl tt_tl
WHERE tt_tl.transaction_type_id = p_transaction_type_id;

Line: 84


select 1
from csm_txn_billing_types_acc
where txn_billing_type_id = p_txn_billing_type_id
and business_process_id = p_business_process_id;

Line: 93


  FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 102


  /****** DELETES  **********/
  --open the cursor
   open l_deletes_cur;

Line: 107


     FETCH l_deletes_cur INTO l_access_id;

Line: 108


     EXIT WHEN l_deletes_cur%NOTFOUND;

Line: 120


     DELETE FROM CSM_TXN_BILLING_TYPES_ACC
       WHERE ACCESS_ID = l_access_id;

Line: 125


   close l_deletes_cur;

Line: 127


  /******* UPDATES **********/
  --generate sql for updates
  l_dsql := 'select acc.access_id, tt_tl.language
             from csm_txn_billing_types_acc acc,
                  cs_txn_billing_types tbt,
                  cs_transaction_types_b ttb,
                  cs_transaction_types_tl tt_tl,
                  cs_business_processes bpr,
                  cs_bus_process_txns bpt,
                  CS_BILLING_TYPE_CATEGORIES cbtc
            where acc.txn_billing_type_id = tbt.txn_billing_type_id
              AND acc.business_process_id = bpt.business_process_id
              AND tbt.transaction_type_id = ttb.transaction_type_id
              AND tbt.billing_type = cbtc.billing_type
              AND cbtc.billing_category IN ( ''L'', ''E'', ''M'' )
              AND tt_tl.transaction_type_id = tbt.transaction_type_id
              AND bpt.transaction_type_id = tbt.transaction_type_id
              AND SYSDATE BETWEEN nvl(bpt.start_date_active, SYSDATE) AND nvl(bpt.end_date_active, SYSDATE)
              AND bpr.business_process_id = bpt.business_process_id
              AND SYSDATE BETWEEN nvl(bpr.start_date_active, SYSDATE) AND nvl(bpr.end_date_active, SYSDATE)
              AND bpr.field_service_flag = ''Y''
              AND (tbt.last_update_date > :1
                 or ttb.last_update_date > :2
                 or tt_tl.last_update_date > :3
                 or bpr.last_update_date > :4
                 or bpt.last_update_date > :5
                 or cbtc.last_update_date > :6
               )';

Line: 158


   open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date, l_prog_update_date;

Line: 161


     FETCH l_updates_cur INTO l_access_id, l_language;

Line: 162


     EXIT WHEN l_updates_cur%NOTFOUND;

Line: 181


     UPDATE csm_txn_billing_types_acc
       SET LAST_UPDATE_DATE = l_run_date
       WHERE ACCESS_ID = l_access_id;

Line: 188


   close l_updates_cur;

Line: 190


  /****** INSERTS  **********/
  --generate sql for inserts
  l_dsql := 'select tbt.TXN_BILLING_TYPE_ID, bpt.business_process_id, tbt.transaction_type_id
        from cs_txn_billing_types tbt,
             cs_transaction_types_b ttb,
             cs_business_processes bpr,
             cs_bus_process_txns bpt,
             CS_BILLING_TYPE_CATEGORIES cbtc
       where tbt.transaction_type_id = ttb.transaction_type_id
         AND SYSDATE BETWEEN nvl(tbt.start_date_active, SYSDATE) AND nvl(tbt.end_date_active, SYSDATE)
         AND SYSDATE BETWEEN nvl(ttb.start_date_active, SYSDATE) AND nvl(ttb.end_date_active, SYSDATE)
         AND SYSDATE BETWEEN nvl(bpr.start_date_active, SYSDATE) AND nvl(bpr.end_date_active, SYSDATE)
         AND SYSDATE BETWEEN nvl(bpt.start_date_active, SYSDATE) AND nvl(bpt.end_date_active, SYSDATE)
         AND tbt.billing_type = cbtc.billing_type
         AND cbtc.billing_category IN ( ''L'', ''E'', ''M'' )
         AND SYSDATE BETWEEN nvl(cbtc.start_date_active, SYSDATE) AND nvl(cbtc.end_date_active, SYSDATE)
         AND bpt.transaction_type_id = tbt.transaction_type_id
         AND bpr.business_process_id = bpt.business_process_id
         AND bpr.field_service_flag = ''Y''
         AND NOT EXISTS
         (select 1
          from csm_txn_billing_types_acc acc
          where acc.txn_billing_type_id = tbt.txn_billing_type_id
          and   acc.business_process_id = bpt.business_process_id
         )';

Line: 217


   open l_inserts_cur for l_dsql;

Line: 220


     FETCH l_inserts_cur INTO l_txn_billing_type_id, l_business_process_id, l_transaction_type_id;

Line: 221


     EXIT WHEN l_inserts_cur%NOTFOUND;

Line: 224


     select csm_txn_billing_types_acc_s.NEXTVAL into l_access_id from dual;

Line: 251


          INSERT INTO csm_txn_billing_types_acc(access_id, txn_billing_type_id, business_process_id, CREATED_BY,
                       CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
          VALUES (l_access_id,l_txn_billing_type_id, l_business_process_id, fnd_global.user_id, l_run_date,
                fnd_global.user_id, l_run_date, fnd_global.login_id);

Line: 261


   close l_inserts_cur;

Line: 264


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