COBISOFT Documentation

User Tools

Site Tools

You are here: start » COBI.wms » Management Database
cobi.wms:management_database

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
cobi.wms:management_database [2023/06/28 15:37] – [Optional columns] tkammercobi.wms:management_database [2025/09/18 10:16] (current) – [Importing licenses] thuth
Line 74: Line 74:
 The ''Profile'' column is used to enable customer-specific specializations in the app and should be left empty unless instructed. The ''Profile'' column is used to enable customer-specific specializations in the app and should be left empty unless instructed.
  
-The ''PrintService'' column can be used to centrally define the address of the [[Print Service|COBI.wms Print Service]].  If not defined here, it has to be set on each Android device in the [[Print Settings]] screen of COBI.wms.+The ''PrintService'' column can be used to centrally define the address of the [[Print Service|COBI.wms Print Service]].  If not defined here, it has to be set on each Android device in the [[Print Settings]] screen of COBI.wms.  When using the standard port of the COBI.wms Print Service, you should only enter the host name or IP address in this column.  When using a non-standard port number, you can specify it by entering the value ''HOST:PORT'' in this column where ''HOST'' is the host name or IP address and ''PORT'' is the TCP port number. 
  
 ===== Devices and Users ===== ===== Devices and Users =====
Line 139: Line 140:
  
 ==== Separate login per user ==== ==== Separate login per user ====
- 
-Note: This feature requires the app to be connected via Service Layer. 
  
 You can specify a separate SAP Business One login for each COBI.wms user or device.  This way, the Change Log in SAP Business One can correctly display which COBI.wms user or device booked or updated a document. You can specify a separate SAP Business One login for each COBI.wms user or device.  This way, the Change Log in SAP Business One can correctly display which COBI.wms user or device booked or updated a document.
  
-To make use of this feature, first make sure that the Users table in the **COBI.wms Management Database** contains the ''apiUser'' and ''apiPass'' columns.  If they are missing, add them like this: +Specify the SAP Business One login for a COBI.wms user by executing the following SQL command on the Management Database:
- +
-<code sql> +
--- MS SQL Server ----------- +
-use cobiwms; +
-alter table users +
-add apiUser nvarchar(255), +
-    apiPass nvarchar(255); +
-drop view cwms__version; +
----------------------------- +
- +
--- SAP HANA  --------------- +
-set schema cobiwms; +
-alter table users +
-add ( apiUser nvarchar(255), +
-      apiPass nvarchar(255) ); +
-drop view cwms__version; +
----------------------------- +
-</code> +
- +
-(The "drop view" at the end will make the app re-generate some stored procedures on next login.) +
- +
-After that, you can overwrite the SAP Business One login for a COBI.wms user the following way:+
  
 <code sql> <code sql>
Line 195: Line 172:
 Once you've updated the ''users'' table with these commands, just restart the COBI.wms Android app and the change will take effect.  You can make a test booking with the app and check the Change Log in SAP Business One to make sure that it worked. Once you've updated the ''users'' table with these commands, just restart the COBI.wms Android app and the change will take effect.  You can make a test booking with the app and check the Change Log in SAP Business One to make sure that it worked.
  
-**WARNING:** When you save the SAP Business One user's password in the ''apiPass'' field as shown above, the password will be seen in plain text in the management database.  (This should generally not be an issue because untrusted persons should not have access to your database server.)+**WARNING:** When you save the SAP Business One user's password in the ''apiPass'' field as shown above, the password will be seen in plain text in the management database, just like the ''apiPassword'' column of the ''companies'' table.  This should generally not be an issue because untrusted persons should not have access to your database server.  However, if this poses an issue for you, see the next section. 
 + 
 +==== Avoiding plaintext passwords in the database ==== 
 + 
 +Normally, the password of an SAP Business One user has to be specified either in the ''apiPassword'' column of the ''companies'' table, or in the ''apiPass'' column of the ''users'' table. 
 + 
 +(Technical details: It makes no sense to encrypt these columns, because the key to decrypt them would need to be deployed as part of the app, such that anyone could access it by analyzing the Android app.  We cannot store a hashed value either, because the app needs to forward the plaintext password to Service Layer when logging in to SAP Business One.) 
 + 
 +To avoid this issue, the following strategy can be used: 
 + 
 +  - Leave the ''apiPassword'' and ''apiPass'' columns in the database empty 
 +  - Create COBI.wms users with the same username and password as SAP Business One users 
 + 
 +When you enter a username & password in the COBI.wms login screen, the app first uses these to perform a COBI.wms user login.  It then tries to find a username & password for Service Layer by checking the ''apiPassword'' and ''apiPass'' columns mentioned above.  However, if these columns are empty, the app will simply take the username & password that were entered for the COBI.wms user login, and try to use these for the Service Layer login as well.  So, if the username & password of the COBI.wms user is the same as an SAP Business One user, it will work. 
 + 
 +(Technical details: The password of a COBI.wms user is //not// stored in plaintext in the database, only a secure hash value of it is stored, since it doesn't need to be forwarded anywhere.)
  
  
Line 215: Line 207:
 insert into licenses (license) values ('LICENSE_2'); insert into licenses (license) values ('LICENSE_2');
 insert into licenses (license) values ('LICENSE_3'); insert into licenses (license) values ('LICENSE_3');
 +</code>
 +
 +The licenses table also has an optional ''notes'' column which you can use for notes about the license.  For example, if you have both regular COBI.wms licenses as well as COBI.ppc licenses in the same database, you can differentiate them through these notes.  Or, when importing test licenses that are only valid for a limited time, you can enter this as a note.  Examples:
 +
 +<code sql>
 +insert into licenses (license, notes) values ('LICENSE_1', 'WMS');
 +insert into licenses (license, notes) values ('LICENSE_2', 'PPC');
 +insert into licenses (license, notes) values ('LICENSE_3', 'PPC, valid until November 2023');
 </code> </code>
  
Line 285: Line 285:
   * ''PDN'': Receipt   * ''PDN'': Receipt
   * ''PKL'': Picking   * ''PKL'': Picking
 +  * ''RDR'': Sales order
   * ''DLN'': Delivery   * ''DLN'': Delivery
   * ''RPD'': Purchase return   * ''RPD'': Purchase return
Line 291: Line 292:
   * ''IPN'': Receipt from production   * ''IPN'': Receipt from production
   * ''PRQ'': Purchase request   * ''PRQ'': Purchase request
-  * ''POR'': Purchase order+  * ''POR'': Purchase Order
   * ''ITM'': Wares list   * ''ITM'': Wares list
   * ''INC'': Inventory counting   * ''INC'': Inventory counting
   * ''WTQ'': Inventory transfer request   * ''WTQ'': Inventory transfer request
   * ''PRINT'': Label printing   * ''PRINT'': Label printing
 +  * ''ITEM'': Item info
  
 For your convenience, here is a template for calling the ''setUserPermission'' procedure once for every module, which you can copy & paste into SQL Server Management Studio or HANA Studio: For your convenience, here is a template for calling the ''setUserPermission'' procedure once for every module, which you can copy & paste into SQL Server Management Studio or HANA Studio:
Line 306: Line 308:
 exec setUserPermission 'username', 'PDN', 1; exec setUserPermission 'username', 'PDN', 1;
 exec setUserPermission 'username', 'PKL', 1; exec setUserPermission 'username', 'PKL', 1;
 +exec setUserPermission 'username', 'RDR', 1;
 exec setUserPermission 'username', 'DLN', 1; exec setUserPermission 'username', 'DLN', 1;
 exec setUserPermission 'username', 'RPD', 1; exec setUserPermission 'username', 'RPD', 1;
Line 317: Line 320:
 exec setUserPermission 'username', 'WTQ', 1; exec setUserPermission 'username', 'WTQ', 1;
 exec setUserPermission 'username', 'PRINT', 1; exec setUserPermission 'username', 'PRINT', 1;
 +exec setUserPermission 'username', 'ITEM', 1;
  
 -- SAP HANA -- SAP HANA
Line 324: Line 328:
 call setUserPermission('username', 'PDN', 1); call setUserPermission('username', 'PDN', 1);
 call setUserPermission('username', 'PKL', 1); call setUserPermission('username', 'PKL', 1);
 +call setUserPermission('username', 'RDR', 1);
 call setUserPermission('username', 'DLN', 1); call setUserPermission('username', 'DLN', 1);
 call setUserPermission('username', 'RPD', 1); call setUserPermission('username', 'RPD', 1);
Line 335: Line 340:
 call setUserPermission('username', 'WTQ', 1); call setUserPermission('username', 'WTQ', 1);
 call setUserPermission('username', 'PRINT', 1); call setUserPermission('username', 'PRINT', 1);
 +call setUserPermission('username', 'ITEM', 1);
 </code> </code>
  
Line 348: Line 354:
 exec setDevicePermission deviceID, 'PDN', 1; exec setDevicePermission deviceID, 'PDN', 1;
 exec setDevicePermission deviceID, 'PKL', 1; exec setDevicePermission deviceID, 'PKL', 1;
 +exec setDevicePermission deviceID, 'RDR', 1;
 exec setDevicePermission deviceID, 'DLN', 1; exec setDevicePermission deviceID, 'DLN', 1;
 exec setDevicePermission deviceID, 'RPD', 1; exec setDevicePermission deviceID, 'RPD', 1;
Line 359: Line 366:
 exec setDevicePermission deviceID, 'WTQ', 1; exec setDevicePermission deviceID, 'WTQ', 1;
 exec setDevicePermission deviceID, 'PRINT', 1; exec setDevicePermission deviceID, 'PRINT', 1;
 +exec setDevicePermission deviceID, 'ITEM', 1;
  
 -- SAP HANA -- SAP HANA
Line 366: Line 374:
 call setDevicePermission(deviceID, 'PDN', 1); call setDevicePermission(deviceID, 'PDN', 1);
 call setDevicePermission(deviceID, 'PKL', 1); call setDevicePermission(deviceID, 'PKL', 1);
 +call setDevicePermission(deviceID, 'RDR', 1);
 call setDevicePermission(deviceID, 'DLN', 1); call setDevicePermission(deviceID, 'DLN', 1);
 call setDevicePermission(deviceID, 'RPD', 1); call setDevicePermission(deviceID, 'RPD', 1);
Line 377: Line 386:
 call setDevicePermission(deviceID, 'WTQ', 1); call setDevicePermission(deviceID, 'WTQ', 1);
 call setDevicePermission(deviceID, 'PRINT', 1); call setDevicePermission(deviceID, 'PRINT', 1);
 +call setDevicePermission(deviceID, 'ITEM', 1);
 </code> </code>
  
 Change ''deviceID'' to the correct device ID number via search & replace, and switch the 1 at the end to a 0 for the modules to disable. Change ''deviceID'' to the correct device ID number via search & replace, and switch the 1 at the end to a 0 for the modules to disable.
cobi.wms/management_database.1687959454.txt.gz · Last modified: by tkammer
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki