APPS.JTM_CON_QUERY_REQUEST_PKG SQL Statements

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

Line: 11

  SELECT con_query_id
  ,      acc_table_name
  ,      con_query
  ,      last_run_date
 FROM   jtm_con_query_request_data
 WHERE EXECUTE_FLAG='Y'
 ORDER BY execution_order;

Line: 22

 SELECT distinct pubitm.primary_key_column
  FROM   asg_pub_item               pubitm
  , 	 jtm_pub_acc		pubacc
  WHERE  pubacc.publication_item_name = pubitm.name
  AND    pubacc.con_query_id = b_con_query_id
  AND    pubacc.execute_flag = 'Y'
  AND    pubitm.status = 'Y'
  AND    pubitm.enabled = 'Y';

Line: 35

	SELECT au.resource_id
    FROM   asg_user           au
    ,      asg_user_pub_resps aupr
    ,      asg_pub_item       api
    WHERE  au.user_name  = aupr.user_name
    AND    aupr.pub_name = api.pub_name
    AND    api.name 	 = b_pub_item_name
    AND    au.enabled  	 = 'Y'
	AND    api.ENABLED 	 = 'Y';

Line: 59

    select creation_date, application_id,
           base_application_id, descriptive_flexfield_name
    from JTM_FND_DESCR_FLEXS_ACC;

Line: 65

   select access_id
   from   JTM_FND_DESC_FLEX_CONTEXT_ACC
   where  creation_date < p_creation_date
   and    APPLICATION_ID = p_appl_id
   and    DESCRIPTIVE_FLEXFIELD_NAME = p_dff_name;

Line: 73

   select access_id
   from   JTM_FND_DESC_FLEX_COL_USG_ACC
   where  creation_date < p_creation_date
   and    APPLICATION_ID = p_appl_id
   and    DESCRIPTIVE_FLEXFIELD_NAME = p_dff_name;

Line: 81

   select access_id
   from   JTM_FND_FLEX_VALUES_ACC
   where  creation_date < p_creation_date
   AND flex_value_id IN
  (SELECT V.flex_value_id
  FROM fnd_descr_flex_column_usages bas, FND_FLEX_VALUES V
  WHERE bas.application_id = p_appl_id
  AND bas.descriptive_flexfield_name = p_dff_name
  AND bas.FLEX_VALUE_SET_ID = V.FLEX_VALUE_SET_ID
  );

Line: 94

   select access_id
   from jtm_fnd_flex_value_sets_acc
   where  creation_date < p_creation_date
   and flex_value_set_id IN
  (SELECT FLEX_VALUE_SET_ID
  FROM fnd_descr_flex_column_usages bas
  WHERE bas.application_id = p_appl_id
  AND bas.descriptive_flexfield_name = p_dff_name
  );

Line: 127

   select  u.user_id BULK COLLECT INTO l_csl_tab_user
   from    asg_user u, asg_user_Pub_resps r
   where   u.user_name = r.user_name
   and     r.pub_name = 'JTM';

Line: 141

   select  u.user_id BULK COLLECT INTO l_csm_tab_user
   from    asg_user u, asg_user_Pub_resps r
   where   u.user_name = r.user_name
   and     r.pub_name = 'JTM_HANDHELD';

Line: 190

                  Update JTM_FND_DESC_FLEX_CONTEXT_ACC
                  set creation_date = c_seed_date.creation_date
                  where access_id = c_old_context_acc.access_id;

Line: 259

                   Update JTM_FND_DESC_FLEX_COL_USG_ACC
                   set creation_date = c_seed_date.creation_date
                   where access_id = c_old_col_usg_acc.access_id;

Line: 328

                   Update JTM_FND_FLEX_VALUES_ACC
                   set creation_date = c_seed_date.creation_date
                   where access_id = c_old_value_acc.access_id;

Line: 397

                   Update JTM_FND_FLEX_VALUE_SETS_ACC
                   set creation_date = c_seed_date.creation_date
                   where access_id = c_old_value_set_acc.access_id;

Line: 452

   SELECT category
   INTO l_category
   FROM   jtm_con_request_data
   WHERE upper(package_name) = 'JTM_CON_QUERY_REQUEST_PKG'
   AND upper(procedure_name) = 'RUN_QUERY_REQUESTS';

Line: 519

	SELECT publication_item_name BULK COLLECT INTO l_publication_item_name
	FROM JTM_PUB_ACC WHERE CON_QUERY_ID = p_con_query_id;

Line: 589

  JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
  	(v_package_name => NULL
	,v_procedure_name => NULL
	,v_con_query_id => r_query_request.con_query_id
    ,v_query_stmt => G_CATEGORY
    ,v_start_time => l_query_start
    ,v_end_time => NULL
    ,v_status => 'Running'
    ,v_message => 'Processing for table ' ||r_query_request.acc_table_name
    ,x_log_id => l_start_log_id
    ,x_status => l_status
    ,x_msg_data => l_message);

Line: 657

    /***  PROCESS UPDATES  ***/

    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      JTM_message_log_pkg.Log_Msg
      ( v_object_id   => r_query_request.acc_table_name
      , v_object_name => G_PACKAGE_NAME
      , v_message     => 'Processing UPDATES'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      , v_module      => 'jtm_message_log_pkg');

Line: 670

        /*** Yes -> Get access_id of records that were updated since last_run_date  ***/
        l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
          ' ACC WHERE (' || l_primary_key || ') IN (SELECT ' ||
          l_primary_key || ' FROM (' || r_query_request.con_query || ') B ' ||
          'WHERE B.LAST_UPDATE_DATE >= :last_run_date)';

Line: 688

        l_tab_access_id.DELETE;

Line: 704

          , v_message     => 'Pushing ' || l_tab_access_id.COUNT || ' updated record(s) to subscribed resources.'
          , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
          , v_module      => 'jtm_message_log_pkg');

Line: 719

    END IF; -- process UPDATES

Line: 721

    /***  PROCESS INSERTS ***/
    /***
      Insert new records to in ACC with COUNTER = 0.
      Then select all ACCESS_IDs from ACC where COUNTER = 0.
      Then update COUNTER to 1.
      This is a workaround for the fact that INSERT INTO with subquery cannot be used
      in combination with RETURNING and we need the ACCESS_IDs to push the records to the
      mobile users.
    ***/

    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      JTM_message_log_pkg.Log_Msg
      ( v_object_id   => r_query_request.acc_table_name
      , v_object_name => G_PACKAGE_NAME
      , v_message     => 'Processing INSERTS'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      , v_module      => 'jtm_message_log_pkg');

Line: 742

           'INSERT INTO JTM_FND_PROF_OPTIONS_VAL_ACC ' ||
           '(APPLICATION_ID,LEVEL_ID,LEVEL_VALUE, ' ||
           'LEVEL_VALUE_APPLICATION_ID,PROFILE_OPTION_ID, ' ||
           'ACCESS_ID, COUNTER, LAST_UPDATE_DATE, LAST_UPDATED_BY, ' ||
           'CREATION_DATE, CREATED_BY) ' ||
           'SELECT V.APPLICATION_ID,V.LEVEL_ID,V.LEVEL_VALUE, ' ||
           'NVL(V.LEVEL_VALUE_APPLICATION_ID, -1), ' ||
           'V.PROFILE_OPTION_ID, ' ||
           'JTM_ACC_TABLE_S.NEXTVAL, 0, SYSDATE, 1, SYSDATE, 1 ' ||
           'FROM FND_PROFILE_OPTION_VALUES V, ' ||
           '     JTM_FND_PROF_OPTIONS_VAL_ACC ACC ' ||
           'WHERE V.APPLICATION_ID IN ' ||
           ' (0,170,178,222,401,513,523,544,690,697,868,874,689,883) ' ||
           'AND V.APPLICATION_ID = ACC.APPLICATION_ID(+) ' ||
           'AND V.LEVEL_ID = ACC.LEVEL_ID(+) ' ||
           'AND V.LEVEL_VALUE = ACC.LEVEL_VALUE(+) ' ||
           'AND NVL(V.LEVEL_VALUE_APPLICATION_ID, -1) = ' ||
           '    ACC.LEVEL_VALUE_APPLICATION_ID(+) ' ||
           'AND V.PROFILE_OPTION_ID = ACC.PROFILE_OPTION_ID(+) ' ||
           'AND ACC.APPLICATION_ID IS NULL';

Line: 763

        l_dynamic_stmt := 'INSERT INTO ' || r_query_request.acc_table_name ||
          '(' || l_original_primary_key  || ', ACCESS_ID, COUNTER,' ||
          ' LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY) ' ||
          'SELECT ' || l_primary_key || ', JTM_ACC_TABLE_S.NEXTVAL, 0, SYSDATE, 1, SYSDATE, 1' ||
          ' FROM (' || r_query_request.con_query || ') WHERE' ||
          ' (' || l_primary_key || ') NOT IN ' ||
          '(SELECT ' || l_primary_key || ' FROM ' || r_query_request.acc_table_name || ')';

Line: 782

    /*** Retrieve ACCESS_IDs for any inserted records ***/
    l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
          ' WHERE COUNTER = 0';

Line: 796

    l_tab_access_id.DELETE;

Line: 812

        , v_message     => 'Pushing ' || l_tab_access_id.COUNT || ' inserted record(s) to subscribed resources.'
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
        , v_module      => 'jtm_message_log_pkg');

Line: 829

      l_dynamic_stmt := 'UPDATE ' || r_query_request.acc_table_name || ' SET COUNTER=1 WHERE COUNTER=0';

Line: 839

    END IF; -- process INSERTS

Line: 842

    /*** PROCESS DELETES ***/
    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      JTM_message_log_pkg.Log_Msg
      ( v_object_id   => r_query_request.acc_table_name
      , v_object_name => G_PACKAGE_NAME
      , v_message     => 'Processing DELETES'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      , v_module      => 'jtm_message_log_pkg');

Line: 854

           'SELECT acc.access_id ' ||
           'FROM jtm_fnd_prof_options_val_acc acc, ' ||
           '(SELECT application_id, level_id, level_value, ' ||
           'nvl(level_value_application_id, -1) ' ||
           'as level_value_application_id, profile_option_id ' ||
           'FROM fnd_profile_option_values ' ||
           'WHERE application_id IN ' ||
           '(0,170,178,222,401,513,523,544,690,697,868,874,689,883) ) B ' ||
           'WHERE acc.application_id = b.application_id(+) ' ||
           'and  acc.level_id = b.level_id(+) ' ||
           'and  acc.level_value = b.level_value(+) ' ||
           'and  acc.level_value_application_id =  ' ||
              ' b.level_value_application_id(+) ' ||
           'and  acc.profile_option_id = b.profile_option_id(+) ' ||
           'and  b.application_id is null ';

Line: 870

       l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
           ' WHERE (' || l_primary_key || ') NOT IN (SELECT ' ||
           l_primary_key || ' FROM (' || r_query_request.con_query || '))';

Line: 878

    l_tab_access_id.DELETE;

Line: 894

          , v_message     => 'Pushing ' || l_tab_access_id.COUNT || ' deleted record(s) to subscribed resources.'
          , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
          , v_module      => 'jtm_message_log_pkg');

Line: 910

        /* Delete record from acc table. */
        l_dynamic_stmt := 'DELETE ' || r_query_request.acc_table_name ||
             ' WHERE access_id= :1';

Line: 927

    END IF; -- process DELETES

Line: 934

  JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
      (v_log_id =>l_start_log_id
      ,v_query_stmt => G_Category
      ,v_start_time => l_query_start
      ,v_end_time   => sysdate
      ,v_status     => p_status
      ,v_message    => p_message
      ,x_status     => l_status
      ,x_msg_data   => l_message);

Line: 968

    JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
      (v_log_id =>l_start_log_id
      ,v_query_stmt => G_Category
      ,v_start_time => l_query_start
      ,v_end_time   => sysdate
      ,v_status     => p_status
      ,v_message    => p_message
      ,x_status     => l_status
      ,x_msg_data   => l_message);

Line: 979

/* PWU: The version 2 of the process request. It handle the last_update_date
   gracely, even the base table last update date is wrong.
   New requirement: All the query statement should include the last_update_date
   in the select clause */
PROCEDURE Process_Request_v2
   (r_query_request c_query_requests%ROWTYPE
   ,p_status out nocopy varchar2
   ,p_message out nocopy varchar2) IS

  l_query_start  date;

Line: 1004

  l_update_count       NUMBER;

Line: 1007

  update_cursor      RefCurType;

Line: 1014

  JTM_MESSAGE_LOG_PKG.INSERT_CONC_STATUS_LOG
  	(v_package_name => NULL
	,v_procedure_name => NULL
	,v_con_query_id => r_query_request.con_query_id
    ,v_query_stmt => G_CATEGORY
    ,v_start_time => l_query_start
    ,v_end_time => NULL
    ,v_status => 'Running'
    ,v_message => 'Processing for table ' ||r_query_request.acc_table_name
    ,x_log_id => l_start_log_id
    ,x_status => l_status
    ,x_msg_data => l_message);

Line: 1071

/*************************** PROCESS UPDATES ***************************/

    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      JTM_message_log_pkg.Log_Msg
      ( v_object_id   => r_query_request.acc_table_name
      , v_object_name => G_PACKAGE_NAME
      , v_message     => 'Processing UPDATES'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      , v_module      => 'jtm_message_log_pkg');

Line: 1085

      l_dynamic_stmt := 'SELECT ACC.ACCESS_ID, B.LAST_UPDATE_DATE FROM ' ||
           r_query_request.acc_table_name||' ACC, ('|| r_query_request.con_query||') B ' ||
           'WHERE B.LAST_UPDATE_DATE <> ACC.LAST_UPDATE_DATE ' ||
           'AND ' || GET_CONDITION(l_primary_key,'ACC','B');

Line: 1100

      l_update_count  := 0;

Line: 1101

      OPEN update_cursor for l_dynamic_stmt;

Line: 1103

          FETCH  update_cursor INTO	m_tab_access_id(l_update_count+1), l_lud;

Line: 1104

          EXIT WHEN update_cursor%NOTFOUND;

Line: 1105

          EXECUTE IMMEDIATE 'Update ' || r_query_request.acc_table_name ||
                            ' set last_update_date = :d where access_id = :a'
                            using l_lud, m_tab_access_id(l_update_count+1);

Line: 1108

          l_update_count := l_update_count + 1;

Line: 1110

      CLOSE update_cursor;

Line: 1112

      IF l_update_count > 0 THEN
        -- 1 or more acc rows retrieved -> push to resources
        IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
          JTM_message_log_pkg.Log_Msg
          ( v_object_id   => r_query_request.acc_table_name
          , v_object_name => G_PACKAGE_NAME
          , v_message     => 'Pushing ' || l_update_count || ' updated record(s) to subscribed resources.'
          , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
          , v_module      => 'jtm_message_log_pkg');

Line: 1130

    END IF; -- process UPDATES

Line: 1132

/*************************** 2. PROCESS INSERTS ***************************/
    /***
      Insert new records to in ACC with COUNTER = 0.
      Then select all ACCESS_IDs from ACC where COUNTER = 0.
      Then update COUNTER to 1.
      This is a workaround for the fact that INSERT INTO with subquery cannot be used
      in combination with RETURNING and we need the ACCESS_IDs to push the records to the
      mobile users.
    ***/

    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      JTM_message_log_pkg.Log_Msg
      ( v_object_id   => r_query_request.acc_table_name
      , v_object_name => G_PACKAGE_NAME
      , v_message     => 'Processing INSERTS'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      , v_module      => 'jtm_message_log_pkg');

Line: 1151

    l_dynamic_stmt := 'INSERT INTO ' || r_query_request.acc_table_name ||
      '(' || l_primary_key  || ', ACCESS_ID, COUNTER,' ||
      ' LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY) ' ||
      'SELECT ' || l_primary_key || ', JTM_ACC_TABLE_S.NEXTVAL, 0, LAST_UPDATE_DATE, 1, sysdate, 1' ||
      ' FROM (' || r_query_request.con_query || ') WHERE' ||
      ' (' || l_primary_key || ') NOT IN ' ||
      '(SELECT ' || l_primary_key || ' FROM ' || r_query_request.acc_table_name || ')';

Line: 1170

    /*** Retrieve ACCESS_IDs for any inserted records ***/
    l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
          ' WHERE COUNTER = 0';

Line: 1184

    l_tab_access_id.DELETE;

Line: 1200

        , v_message     => 'Pushing ' || l_tab_access_id.COUNT || ' inserted record(s) to subscribed resources.'
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
        , v_module      => 'jtm_message_log_pkg');

Line: 1219

      l_dynamic_stmt := 'UPDATE ' || r_query_request.acc_table_name || ' SET COUNTER=1 WHERE COUNTER=0';

Line: 1229

    END IF; -- process INSERTS

Line: 1231

/*************************** 3. PROCESS DELETES ***************************/
    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      JTM_message_log_pkg.Log_Msg
      ( v_object_id   => r_query_request.acc_table_name
      , v_object_name => G_PACKAGE_NAME
      , v_message     => 'Processing DELETES'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
      , v_module      => 'jtm_message_log_pkg');

Line: 1244

      l_dynamic_stmt := 'SELECT ACCESS_ID FROM ' || r_query_request.acc_table_name ||
        ' WHERE (' || l_primary_key || ') NOT IN (SELECT ' ||
        l_primary_key || ' FROM (' || r_query_request.con_query || '))';

Line: 1251

      l_tab_access_id.DELETE;

Line: 1267

          , v_message     => 'Pushing ' || l_tab_access_id.COUNT || ' deleted record(s) to subscribed resources.'
          , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
          , v_module      => 'jtm_message_log_pkg');

Line: 1286

      /* Delete record from acc table. */
      l_dynamic_stmt := 'DELETE ' || r_query_request.acc_table_name ||
        ' WHERE (' || l_primary_key || ') NOT IN (SELECT ' ||
        l_primary_key || ' FROM (' || r_query_request.con_query || '))';

Line: 1305

/*************************** 3. PROCESS DELETES DONE***************************/

  p_message := 'Successfully processing with query id  ' ||
                r_query_request.con_query_id;

Line: 1312

  JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
      (v_log_id =>l_start_log_id
      ,v_query_stmt => G_Category
      ,v_start_time => l_query_start
      ,v_end_time   => sysdate
      ,v_status     => p_status
      ,v_message    => p_message
      ,x_status     => l_status
      ,x_msg_data   => l_message);

Line: 1348

    JTM_MESSAGE_LOG_PKG.UPDATE_CONC_STATUS_LOG
      (v_log_id =>l_start_log_id
      ,v_query_stmt => G_Category
      ,v_start_time => l_query_start
      ,v_end_time   => sysdate
      ,v_status     => p_status
      ,v_message    => p_message
      ,x_status     => l_status
      ,x_msg_data   => l_message);

Line: 1401

     UPDATE jtm_con_query_request_data
     SET    LAST_TXC_START =  sysdate,
            LAST_TXC_END = null,
            STATUS = 'Running',
            COMPLETION_TEXT = 'Processing the query with id '
                    || r_query_request.con_query_id
     WHERE  con_query_id = r_query_request.con_query_id;

Line: 1454

         UPDATE jtm_con_query_request_data
         SET    LAST_TXC_END = sysdate,
                STATUS = l_status,
                COMPLETION_TEXT = l_message
         WHERE  con_query_id = r_query_request.con_query_id;

Line: 1460

         UPDATE jtm_con_query_request_data
         SET    LAST_TXC_END = sysdate,
                last_run_date = last_txc_start,
                STATUS = l_status,
                COMPLETION_TEXT = l_message
         WHERE  con_query_id = r_query_request.con_query_id;

Line: 1474

  UPDATE JTM_CON_REQUEST_DATA SET LAST_RUN_DATE = SYSDATE
  WHERE PRODUCT_CODE = 'JTM'
  AND   PACKAGE_NAME = G_PACKAGE_NAME
  AND   UPPER(PROCEDURE_NAME) = L_API_NAME;