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.JTM_HOOK_UTIL_PKG SQL Statements

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

Line: 47


 SELECT null
    FROM  asg_user               au
    ,     asg_user_pub_resps     aupr
    WHERE au.user_name   = aupr.user_name
    AND   aupr.pub_name  = 'SERVICEL'
    AND   au.enabled 	 = 'Y'
    AND   au.resource_id = b_resource_id;

Line: 76


    SELECT resource_id
    FROM   jtf_rs_resource_extns rre
    ,      fnd_user              usr
    WHERE  usr.user_id = rre.user_id
    AND    usr.user_name = b_client_name;

Line: 94


  SELECT user_id
  FROM fnd_user
  WHERE user_name = b_client_name;

Line: 148


  l_stmt := 'SELECT ACCESS_ID FROM ' || p_acc_table_name ||
            ' WHERE RESOURCE_ID = :1' ||
            ' AND ' || p_pk1_name || ' = ' || l_pk1_string;

Line: 275


  l_stmt := 'SELECT RESOURCE_ID, ACCESS_ID FROM ' || p_acc_table_name ||
            ' WHERE ' || p_pk1_name || ' = ' || l_pk1_string;

Line: 363


  Procedure that inserts a record into any ACC table
***/
PROCEDURE INSERT_ACC
  ( p_publication_item_names in t_publication_item_list
  , p_acc_table_name         in VARCHAR2
  , p_resource_id            in NUMBER
  , p_pk1_name               in VARCHAR2
  , p_pk1_num_value          in NUMBER   DEFAULT NULL
  , p_pk1_char_value         in VARCHAR2 DEFAULT NULL
  , p_pk1_date_value         in DATE     DEFAULT NULL
  , p_pk2_name               in VARCHAR2 DEFAULT NULL
  , p_pk2_num_value          in NUMBER   DEFAULT NULL
  , p_pk2_char_value         in VARCHAR2 DEFAULT NULL
  , p_pk2_date_value         in DATE     DEFAULT NULL
  , p_pk3_name               in VARCHAR2 DEFAULT NULL
  , p_pk3_num_value          in NUMBER   DEFAULT NULL
  , p_pk3_char_value         in VARCHAR2 DEFAULT NULL
  , p_pk3_date_value         in DATE     DEFAULT NULL
 )
IS
  l_stmt           VARCHAR2(2000);

Line: 395


  /*** insert new ACC record for current resource ***/
  l_access_id := Get_Acc_Id
                 ( p_acc_table_name => p_acc_table_name
                 , p_resource_id    => p_resource_id
                 , p_pk1_name       => p_pk1_name
                 , p_pk1_num_value  => p_pk1_num_value
                 , p_pk1_char_value => p_pk1_char_value
                 , p_pk1_date_value => p_pk1_date_value
                 , p_pk2_name       => p_pk2_name
                 , p_pk2_num_value  => p_pk2_num_value
                 , p_pk2_char_value => p_pk2_char_value
	             , p_pk2_date_value => p_pk2_date_value
                 , p_pk3_name       => p_pk2_name
                 , p_pk3_num_value  => p_pk2_num_value
                 , p_pk3_char_value => p_pk2_char_value
 	             , p_pk3_date_value => p_pk2_date_value);

Line: 414


    l_stmt := 'UPDATE '||p_acc_table_name||
              ' SET COUNTER = COUNTER + 1'||
	      ', LAST_UPDATE_DATE = SYSDATE '||
	      ', LAST_UPDATED_BY = 1 '||
              ' WHERE ACCESS_ID = :1 ';

Line: 424


         , 'JTM_HOOK_UTIL_PKG.Insert_Acc executing:' || fnd_global.local_chr(10) || l_stmt
         , JTM_HOOK_UTIL_PKG.g_debug_level_full
         , 'jtm_message_log_pkg');

Line: 431


  /*Record does not exists so do the insert*/
  /* Check how many PK there are and transfer values */
    IF p_pk1_date_value IS null THEN
      l_pk1_value := NVL( TO_CHAR(p_pk1_num_value ), p_pk1_char_value );

Line: 441


    l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
              ' CREATION_DATE, CREATED_BY, COUNTER, RESOURCE_ID, ' || p_pk1_name || ') ' ||
              'VALUES (JTM_ACC_TABLE_S.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, :1, ' || l_pk1_string ||
              ') RETURNING ACCESS_ID INTO :3';

Line: 456


      l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
                ' CREATION_DATE, CREATED_BY, COUNTER, RESOURCE_ID, ' ||
	 p_pk1_name ||', '|| p_pk2_name || ') ' ||
                'VALUES (JTM_ACC_TABLE_S.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, :1, ' ||
	 l_pk1_string || ', ' || l_pk2_string || ' ) RETURNING '||  'ACCESS_ID INTO :3';

Line: 483


        l_stmt := 'INSERT INTO ' || p_acc_table_name || ' (ACCESS_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,'||
                  ' CREATION_DATE, CREATED_BY, COUNTER, RESOURCE_ID, ' ||
                  p_pk1_name ||', '|| p_pk2_name ||', '|| p_pk3_name || ') ' ||
                  'VALUES (JTM_ACC_TABLE_S.NEXTVAL, SYSDATE, 1, SYSDATE, 1, 1, :1, ' ||
	 l_pk1_string || ', ' || l_pk2_string || ', ' || l_pk3_string || ' ) RETURNING '||
                  'ACCESS_ID INTO :3';

Line: 509


         , 'JTM_HOOK_UTIL_PKG.Insert_Acc executing:' || fnd_global.local_chr(10) ||
           l_stmt || fnd_global.local_chr(10) || ':1 = ' || p_resource_id
           || fnd_global.local_chr(10) || l_error_msg
         , JTM_HOOK_UTIL_PKG.g_debug_level_full
         , 'jtm_message_log_pkg');

Line: 533


END Insert_Acc;

Line: 536


PROCEDURE Update_Acc
 ( p_publication_item_names in t_publication_item_list
  ,p_acc_table_name         in VARCHAR2
  ,p_resource_id            in NUMBER
  ,p_access_id              in NUMBER
 )
IS
 l_rc BOOLEAN;

Line: 549


END Update_Acc;

Line: 552


 Procedure that deletes record(s) from any ACC table
 If p_resource_id is NULL, all ACC records that match the PK values are deleted.
 If p_resource_id is specified and p_operator='=' the ACC record is only deleted for that specific resource.
 If p_resource_id is specified and p_operator='<>' all ACC records with resource_id<>p_resource_id are deleted
***/
PROCEDURE Delete_Acc
 ( p_publication_item_names in t_publication_item_list
  ,p_acc_table_name         in VARCHAR2
  ,p_pk1_name               in VARCHAR2
  ,p_pk1_num_value          in NUMBER   DEFAULT NULL
  ,p_pk1_char_value         in VARCHAR2 DEFAULT NULL
  , p_pk1_date_value        in DATE     DEFAULT NULL
  , p_pk2_name              in VARCHAR2 DEFAULT NULL
  , p_pk2_num_value         in NUMBER   DEFAULT NULL
  , p_pk2_char_value        in VARCHAR2 DEFAULT NULL
  , p_pk2_date_value        in DATE     DEFAULT NULL
  , p_pk3_name              in VARCHAR2 DEFAULT NULL
  , p_pk3_num_value         in NUMBER   DEFAULT NULL
  , p_pk3_char_value        in VARCHAR2 DEFAULT NULL
  , p_pk3_date_value        in DATE     DEFAULT NULL
  ,p_resource_id            in NUMBER   DEFAULT NULL
  ,p_operator               in VARCHAR2 DEFAULT NULL
)
IS
  l_stmt               VARCHAR2(4000);

Line: 627


  l_stmt := 'SELECT RESOURCE_ID, ACCESS_ID FROM ' || p_acc_table_name ||
            ' WHERE COUNTER = 1 AND ' || p_pk1_name || ' = ' || l_pk1_string;

Line: 647


    /*** no -> delete all ACC records ***/
    dbms_sql.parse( l_cursor, l_stmt, dbms_sql.v7);

Line: 668


     , 'JTM_HOOK_UTIL_PKG.Delete_Acc executing:' || fnd_global.local_chr(10) ||
      l_stmt || fnd_global.local_chr(10) ||
      ':P1 = ' || l_pk1_value || fnd_global.local_chr(10) ||
      ':P2 = ' || l_pk2_value || fnd_global.local_chr(10) ||
      ':P3 = ' || l_pk3_value || fnd_global.local_chr(10) ||
      ':P4 = ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.g_debug_level_full
    , 'jtm_message_log_pkg');

Line: 689


  /*** were any records deleted? ***/
  IF l_tab_mobile_user_id.COUNT > 0 THEN
    /*** yes -> loop over arrays containing mobile_user_id and access_id and notify oLite ***/
    FOR i IN l_tab_mobile_user_id.FIRST .. l_tab_mobile_user_id.LAST LOOP
      -- notify oLite of deletion ***
      FOR j IN 1 .. p_publication_item_names.LAST LOOP
        l_rc := asg_download.markDirty( p_publication_item_names(j), l_tab_access_id(i)
                                      , l_tab_mobile_user_id(i), 'D', sysdate );

Line: 701


  /*Perform the actual delete*/
  l_stmt := 'DELETE '||p_acc_table_name||
            ' WHERE COUNTER = 1'||
	    ' AND '||p_pk1_name||' = ' || l_pk1_string;

Line: 719


     , 'JTM_HOOK_UTIL_PKG.Delete_Acc executing :' || fnd_global.local_chr(10) ||
      l_stmt || fnd_global.local_chr(10) ||
      ':P1 = ' || l_pk1_value || fnd_global.local_chr(10) ||
      ':P2 = ' || l_pk2_value || fnd_global.local_chr(10) ||
      ':P3 = ' || l_pk3_value || fnd_global.local_chr(10) ||
      ':P4 = ' || p_resource_id
     , JTM_HOOK_UTIL_PKG.g_debug_level_full
     , 'jtm_message_log_pkg');

Line: 743


  /* Now we have deleted all records we have to decrease the counter of the remaining records*/
  l_stmt := 'UPDATE '||p_acc_table_name||
            ' SET COUNTER = COUNTER - 1'||
	    ', LAST_UPDATE_DATE = SYSDATE'||
	    ', LAST_UPDATED_BY = 1'||
            ' WHERE COUNTER >= 2 AND '||p_pk1_name||' = ' || l_pk1_string;

Line: 763


    , 'JTM_HOOK_UTIL_PKG.Delete_Acc executing:' || fnd_global.local_chr(10) || l_stmt
    , JTM_HOOK_UTIL_PKG.g_debug_level_full
    , 'jtm_message_log_pkg');

Line: 787


END Delete_Acc;

Line: 790


PROCEDURE DELETE_ACC_FOR_RESOURCE
( p_acc_table_name IN VARCHAR2
, p_resource_id IN NUMBER
) IS
 l_stmt    VARCHAR2(1000);

Line: 796


  l_stmt := 'DELETE ' || p_acc_table_name ||' WHERE RESOURCE_ID = :P1';

Line: 802


    , 'JTM_HOOK_UTIL_PKG.Delete_Acc_4Res executing:' || fnd_global.local_chr(10) ||
      l_stmt || fnd_global.local_chr(10)||'P1 = '||p_resource_id
    , JTM_HOOK_UTIL_PKG.g_debug_level_full
    , 'jtm_message_log_pkg');

Line: 828


  SELECT val.profile_option_value
    FROM fnd_profile_options       opt,
         fnd_profile_option_values val
   WHERE NVL(opt.start_date_active, SYSDATE) <= SYSDATE
     AND NVL(opt.end_date_active,   SYSDATE) >= SYSDATE
     AND opt.profile_option_name = b_profile_option_name
     AND opt.application_id      = val.application_id
     AND opt.profile_option_id   = val.profile_option_id
     AND ( ( val.level_id    = G_SITE_LEVEL_ID    AND
             val.level_value = b_site_level_value
           ) OR
           ( val.level_id    = G_APPL_LEVEL_ID    AND
             val.level_value = b_appl_level_value
           ) OR
           ( val.level_id    = G_RESP_LEVEL_ID    AND
             val.level_value = b_resp_level_value
           ) OR
           ( val.level_id    = G_USER_LEVEL_ID    AND
             val.level_value = b_user_level_value
           )
         )
         ORDER BY val.level_id DESC;