APPS.CSM_CURRENCY_EVENT_PKG SQL Statements

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

Line: 18

  Makes corresponding entries in to SDQ for all deletes, updates and inserts
  Refreshes for all the users
*/
procedure Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
                       p_message OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;

Line: 25

l_updates_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 26

l_inserts_cur CSM_UTIL_PKG.Changed_Records_Cur_Type;

Line: 29

l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line: 35

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

Line: 41

CURSOR l_deletes_csr(p_last_upd_date date)
IS
SELECT acc.access_id, curr.currency_code, curr_tl.language
FROM fnd_currencies curr,
     fnd_currencies_tl curr_tl,
     csm_currencies_acc acc
WHERE curr.currency_code = curr_tl.currency_code
AND   curr.currency_code = acc.currency_code
AND  ((SYSDATE not BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate))
      OR curr.enabled_flag <> 'Y')
AND ((curr.creation_date < p_last_upd_date AND curr.last_update_date > p_last_upd_date)
     OR (curr_tl.creation_date < p_last_upd_date AND curr_tl.last_update_date > p_last_upd_date)
    );

Line: 56

select csm_currencies_acc_s.NEXTVAL  from dual;

Line: 59

l_curr_last_update_date fnd_currencies.LAST_UPDATE_DATE%TYPE;

Line: 60

l_curr_tl_last_update_date fnd_currencies.LAST_UPDATE_DATE%TYPE;

Line: 61

l_max_update_date fnd_currencies.LAST_UPDATE_DATE%TYPE;

Line: 74

  FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 83

  /****** DELETES  **********/
  --open the cursor
   open l_deletes_csr(l_prog_update_date);

Line: 88

     FETCH l_deletes_csr INTO l_access_id, l_currency_code, l_language;

Line: 89

     EXIT WHEN l_deletes_csr%NOTFOUND;

Line: 110

     DELETE FROM CSM_CURRENCIES_ACC
       WHERE ACCESS_ID = l_access_id;

Line: 116

   close l_deletes_csr;

Line: 118

  /******* UPDATES **********/
  --generate sql for updates
  l_dsql :=
     'select acc.access_id, curr_tl.language, curr.last_update_date, curr_tl.last_update_date
      from csm_currencies_acc acc,
           fnd_currencies curr,
           fnd_currencies_tl curr_tl
      where acc.currency_code = curr.currency_code
      and curr_tl.currency_code = curr.currency_code
      AND  SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
      AND curr.enabled_flag = ''Y''
      AND (curr.last_update_date > :1
            or curr_tl.last_update_date > :2
           )';

Line: 139

   open l_updates_cur for l_dsql USING l_prog_update_date, l_prog_update_date;

Line: 142

     FETCH l_updates_cur INTO l_access_id, l_language, l_curr_last_update_date, l_curr_tl_last_update_date;

Line: 143

     EXIT WHEN l_updates_cur%NOTFOUND;

Line: 162

     IF (l_curr_last_update_date > l_curr_tl_last_update_date) THEN
       l_max_update_date := l_curr_last_update_date;

Line: 165

       l_max_update_date := l_curr_tl_last_update_date;

Line: 169

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

Line: 176

   close l_updates_cur;

Line: 178

  /****** INSERTS  **********/
  --generate sql for inserts

  l_dsql :=
      'SELECT curr.currency_code, curr.last_update_date
       FROM fnd_currencies curr
       WHERE  SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
       AND curr.enabled_flag = ''Y''
       AND NOT EXISTS
          (SELECT 1
           FROM CSM_CURRENCIES_ACC acc
           WHERE acc.currency_code = curr.currency_code
           )';

Line: 193

   open l_inserts_cur for l_dsql;

Line: 196

     FETCH l_inserts_cur INTO l_currency_code, l_curr_last_update_date;

Line: 197

     EXIT WHEN l_inserts_cur%NOTFOUND;

Line: 212

     INSERT INTO csm_currencies_acc(currency_code, access_id, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
       LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
     VALUES (l_currency_code, l_access_id, fnd_global.user_id, sysdate, fnd_global.user_id,
             l_curr_last_update_date, fnd_global.login_id);

Line: 219

   close l_inserts_cur;

Line: 222

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

Line: 246

l_prog_update_date asg_pub_item.last_run_date%TYPE;

Line: 258

SELECT nvl(last_run_date, (sysdate - 365*50))
FROM asg_pub_item
WHERE name = p_pub_item
AND pub_name = 'SERVICEP';

Line: 265

SELECT curr.currency_code, curr_tl.language, curr.last_update_date
FROM fnd_currencies curr,
     fnd_currencies_tl curr_tl
WHERE curr.currency_code = curr_tl.currency_code
AND  SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
AND curr.enabled_flag = 'Y'
AND (curr.creation_date > p_last_upd_date
      OR curr_tl.creation_date > p_last_upd_date);

Line: 276

SELECT curr.currency_code, curr_tl.language
FROM fnd_currencies curr,
     fnd_currencies_tl curr_tl
WHERE curr.currency_code = curr_tl.currency_code
AND  SYSDATE BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate)
AND curr.enabled_flag = 'Y'
AND ((curr.creation_date < p_last_upd_date AND curr.last_update_date > p_last_upd_date)
     OR (curr_tl.creation_date < p_last_upd_date AND curr_tl.last_update_date > p_last_upd_date)
    );

Line: 288

SELECT curr.currency_code, curr_tl.language
FROM fnd_currencies curr,
     fnd_currencies_tl curr_tl
WHERE curr.currency_code = curr_tl.currency_code
AND  ((SYSDATE not BETWEEN nvl(curr.start_date_active, sysdate) AND nvl(curr.end_date_active, sysdate))
      OR curr.enabled_flag <> 'Y')
AND ((curr.creation_date < p_last_upd_date AND curr.last_update_date > p_last_upd_date)
     OR (curr_tl.creation_date < p_last_upd_date AND curr_tl.last_update_date > p_last_upd_date)
    );

Line: 301

 FETCH l_last_run_date_csr INTO l_prog_update_date;

Line: 308

  FOR r_currency_ins_rec IN l_currency_ins_csr(l_prog_update_date) LOOP

     --get the users with this language
     l_tl_omfs_palm_resource_list := l_null_resource_list;

Line: 331

  FOR r_currency_upd_rec IN l_currency_upd_csr(l_prog_update_date) LOOP
     --get the users with this language
     l_tl_omfs_palm_resource_list := l_null_resource_list;

Line: 354

  FOR r_currency_del_rec IN l_currency_del_csr(l_prog_update_date) LOOP
     --get the users with this language
     l_tl_omfs_palm_resource_list := l_null_resource_list;

Line: 377

  UPDATE asg_pub_item
  SET last_run_date = l_run_date
  WHERE name = l_pub_item
  AND pub_name = 'SERVICEP';