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

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

Line: 17


  SELECT resource_id
  FROM   ASG_USER
  WHERE  user_id = c_user_id
  AND    Enabled ='Y';

Line: 69


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

Line: 151


  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_seq_name               in VARCHAR2
  , p_user_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: 191


  /*** insert new ACC record for current resource ***/
  l_access_id := Get_Acc_Id
                 ( p_acc_table_name => p_acc_table_name
                 , p_user_id        => l_user_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_pk3_name
                 , p_pk3_num_value  => p_pk3_num_value
                 , p_pk3_char_value => p_pk3_char_value
   	             , p_pk3_date_value => p_pk3_date_value);

Line: 210


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

Line: 219


    CSM_UTIL_PKG.LOG( l_stmt, 'CSM_ACC_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 224


  /*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: 234


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

Line: 250


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

Line: 278


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

Line: 303


           || fnd_global.local_chr(10) || l_error_msg, 'CSM_ACC_PKG.INSERT_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 333


  CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.INSERT_ACC' || sqlerrm, 'CSM_ACC_PKG.INSERT_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 337


END Insert_Acc;

Line: 341


 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_user_id                in NUMBER   DEFAULT NULL
  ,p_operator               in VARCHAR2 DEFAULT '='
)
IS
  l_stmt               VARCHAR2(4000);

Line: 414


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

Line: 434


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

Line: 455


      ':P4 = ' || l_user_id, 'CSM_ACC_PKG.DELETE_ACC',FND_LOG.LEVEL_PROCEDURE);

Line: 469


  /*** 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 ***
      --get the resource id
    OPEN  c_asg_user (l_tab_mobile_user_id(i));

Line: 488


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

Line: 517


  /* 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: 552


  CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.DELETE_ACC' || sqlerrm, 'CSM_ACC_PKG.DELETE_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 555


END Delete_Acc;

Line: 558


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

Line: 586


  CSM_UTIL_PKG.LOG( 'Exception occurred in CSM_ACC_PKG.UPDATE_ACC' || sqlerrm, 'CSM_ACC_PKG.UPDATE_ACC',FND_LOG.LEVEL_EXCEPTION);

Line: 590


END Update_Acc;