Wiki

Scale Your Enterprise

User Tools

Site Tools

Trace:
versago:record_submission_report_configuration

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
versago:record_submission_report_configuration [2019/06/04 14:23]
runger [Database Trigger for Processing] 		versago:record_submission_report_configuration [2019/11/13 13:45] (current)
Line 11: Line 11:
 The following documents contain additional information that may be helpful with understanding these processes. The following documents contain additional information that may be helpful with understanding these processes.
  
-  * Versago – Administration Console +  * [[versago:administration_console|Introduction (Admin Console)]] 
-  * Versago – Payment Processing Configuration Guide +  * [[versago:payment_configuration_and_reports|Introduction (Payments)]] 
-  * Versago – Reports Configuration Guide+  * [[versago:report_configuration|Introduction (Reports Configuration)]]
  
 ====== Special Terminology ====== ====== Special Terminology ======
Line 21: Line 21:
 ====== Before You Begin ====== ====== Before You Begin ======
  
-Review the [[versago:report_configuration|Reports Configuration]] page. The basic process through the “Display” step is the same for record selection reports. Details of the basic setup are not included in this document.+Review the **//[[versago:report_configuration|Reports Configuration]]//** page. The basic process through the “Display” step is the same for record selection reports. Details of the basic setup are not included in this document.
  
 ====== To Begin ====== ====== To Begin ======
Line 50: Line 50:
 The “Record Submission” page is used to configure the report for record selection. When the “Enable Record Submission” checkbox is selected, additional configuration items are displayed. The “Record Submission” page is used to configure the report for record selection. When the “Enable Record Submission” checkbox is selected, additional configuration items are displayed.
  
-{{:versago/recordsub2.png?384x109}} +{{  :versago/recordsub2.png?384x109  }} 
  
 Also select the “Process Payments” option checkbox if this report will be used for accepting and processing credit card and eCheck payments. Also select the “Process Payments” option checkbox if this report will be used for accepting and processing credit card and eCheck payments.
Line 56: Line 56:
 Configuration of Record Submission reports for payments is described in the documents //Versago – Payments Setup//. Configuration of Record Submission reports for payments is described in the documents //Versago – Payments Setup//.
  
-See the [[#record-submission-setup|Record Submission Setup]] section for detailed information on configuring the record submission information.+See the //**[[#record-submission-setup|Record Submission Setup]]**// section for detailed information on configuring the record submission information.
  
 ===== Calendar tab ===== ===== Calendar tab =====
Line 82: Line 82:
 The Selection Screen setup defines what the users sees on the Report page where they select the items for submission. Click the [**View Example**] button to see a generic version of a record selection screen. The Selection Screen setup defines what the users sees on the Report page where they select the items for submission. Click the [**View Example**] button to see a generic version of a record selection screen.
  
-{{:versago/recordsub3.png?700}}+{{  :versago/recordsub3.png?700  }}
  
-  - The two checkboxes are required for payment processing+  - The two check-boxes are required for payment processing
     - The “Editable field” selection causes a field with the current value (the open balance of an invoice, for example) to be displayed on the selection page and allows the user to change the value (to pay less than the balance, for example).     - The “Editable field” selection causes a field with the current value (the open balance of an invoice, for example) to be displayed on the selection page and allows the user to change the value (to pay less than the balance, for example).
     - The “Show and submit …” selection causes the values the user enters in the editable field for the selected records to be summed, the sum displayed at the bottom of that column and submitted to the next step in the process which is the confirmation screen.     - The “Show and submit …” selection causes the values the user enters in the editable field for the selected records to be summed, the sum displayed at the bottom of that column and submitted to the next step in the process which is the confirmation screen.
Line 92: Line 92:
 Here is an example of how the selection page might look, followed by the confirmation page. Here is an example of how the selection page might look, followed by the confirmation page.
  
-{{:versago/recordsub4.png?600}}+{{  :versago/recordsub4.png?600  }}
  
-{{:versago/recordsub5.png?384x177}}+{{  :versago/recordsub5.png?384x177  }}
  
 ===== Confirmation Screen ===== ===== Confirmation Screen =====
Line 100: Line 100:
 The Confirmation Screen setup defines what the users sees on the page where they review the items selected for submission. This setup is required for payment processing. Click the [**View Example**] button to see a generic version of a confirmation screen. The Confirmation Screen setup defines what the users sees on the page where they review the items selected for submission. This setup is required for payment processing. Click the [**View Example**] button to see a generic version of a confirmation screen.
  
-{{:versago/recordsub6.png?600}}+{{  :versago/recordsub6.png?600  }}
  
   - Check the “Include a Confirmation Screen …” checkbox to provide a screen with a summary of their selections.   - Check the “Include a Confirmation Screen …” checkbox to provide a screen with a summary of their selections.
Line 115: Line 115:
 The final piece of the setup process is to define where the information selected by the user will be stored in the database. The final piece of the setup process is to define where the information selected by the user will be stored in the database.
  
-{{:versago/recordsub7.png?384x91}}+{{  :versago/recordsub7.png?384x91  }}
  
   - Select a database from the “Database” drop-down list.   - Select a database from the “Database” drop-down list.
Line 127: Line 127:
 ====== Submission Database Table Structure Details ====== ====== Submission Database Table Structure Details ======
  
-As noted earlier, a table to hold the information related to the selected records is stored in a table in the specified database. All record submission tables have the same basic structure as described below.+As noted earlier, a table to hold the information related to the selected records is stored in a table in the specified database. All record submission tables have the same basic structure as described **//[[multi_select_table_structure|here]]//**.
  
-^Column Name                                       ^Type and Size^Notes                 ^ +====== Back-End Processing ======
-|User columns from initial setup appear first      |             |                      | +
-|vgo_PaymentDate                                   |DateTime     |For payment processing| +
-|vgo_PaymentDetails                                |nvarchar(100)|For payment processing| +
-|vgo_CC_RespCode                                   |nvarchar(50) |For payment processing| +
-|vgo_TransID                                       |nvarchar(50) |For payment processing| +
-|vgo_PaymentType                                   |Int          |For payment processing| +
-|vgo_CreatedBy                                     |nvarchar(50) |Creation information  | +
-|vgo_CreateDate                                    |DateTime     |Creation information  | +
-|vgo_CreatedById                                   |Int          |Creation information  | +
-|vgo_PostingStatus                                 |Int          |Processing control    | +
-|vgo_PostingDate                                   |DateTime     |Processing control    | +
-|vgo_PostingId                                     |Int          |Processing control    | +
-|vgo_BatchId                                       |Int          |Processing control    | +
-|User columns added after initial setup appear last|             |                      | +
-|                                                  |             |                      | +
- +
-  - **Payment Processing** items are used by the internal payment process. These values are only populated for payment transaction. +
-  - **Creation Information** items are populated for all transactions. +
-  - **vgo_CreatedBy**: Versago user ID (email) that created the transaction. +
-  - **vgo_CreateDate**: Date and time the transaction was created. +
-  - **vgo_CreatedById**: Versago internal user ID number. +
-  - **Processing Control** items can be used to manage back-end processing using tools such as Bizweaver. +
-  - **vgo_PostingStatus**: Typically used to indicate if a record has been processed by the back-end. +
-  - **vgo_PostingDate**: Typically used to capture when a record has been processed by the back-end. +
-  - **vgo_PostingId**: Typically used to capture the ID number of the record created during back-end processing. +
-  - **vgo_BatchId**: A sequential number added by the application when a set of records is added to the database. This value can be used to process a group of records together during back-end processing. +
- +
- +
-====== Database Trigger for Processing ======+
  
 In a Versago environment, back-end processing is typically done using Bizweaver. The information here refers to Bizweaver but other process flow tools might be used as well. In a Versago environment, back-end processing is typically done using Bizweaver. The information here refers to Bizweaver but other process flow tools might be used as well.
Line 164: Line 135:
 Most record submission reports will have an associated Bizweaver workflow to handle additional processing. For payments this might be creating SAP Business One Incoming Payment transactions. Or it might be updating the status on a group of orders. Most record submission reports will have an associated Bizweaver workflow to handle additional processing. For payments this might be creating SAP Business One Incoming Payment transactions. Or it might be updating the status on a group of orders.
  
-There are two ways to initiate this processing. One is to have the Bizweaver workflow set on a schedule so that it executes periodically during the day. Second is to initiate the workflow when the submission is saved to the database. For the second option a database trigger is used. +There are two ways to initiate this processing. The most basic approach is to have the Bizweaver workflow set on a schedule so that it executes periodically during the day.
- +
-The following discussion assumes that you have some knowledge about database triggers and have access to the SQL Server tools needed to install them. +
- +
-===== Multi-Select Records Processing ===== +
-A basic database trigger to initiate Bizweaver processing for records selection will look like this. +
- +
-<code sql multiSelect_trigger_sql.txt> +
-CREATE TRIGGER [dbo].[TriggerName] +
-ON [dbo].[TableName] +
-AFTER INSERT +
-AS +
-BEGIN +
--- SET NOCOUNT ON added to prevent extra result sets from +
--- interfering with SELECT statements. +
-SET NOCOUNT ON; +
--- +
-declare @vgo_BatchID int, @object int, @url varchar(5000), @settingsDB nvarchar(50), @TargetTableName nvarchar(50), @BWServerName nvarchar(50), @BWServerPort nvarchar(50), @WorkflowID nvarchar(5), +
-@pArgVariableName nvarchar(50) +
--- +
-select @vgo_BatchID = vgo_BatchID from inserted +
-select @BWServerName = 'ServerName' +
-select @BWServerPort = 'ServerPort' +
-select @WorkflowID = 'WorkflowID' +
-select @pArgVariableName = 'pArgVariableName' +
--- +
--- Remember that the URL may require https:// and the port will be 443 if you are using SSL. +
-select @url = 'http://' + @BWServerName + ':' + @BWServerPort + '/BWService/api/workflow/InvokeWorkFlow?pTaskID=' + @WorkflowID + '&pStartAfter=0&pArguments={' + @pArgVariableName + ':' + cast(@vgo_BatchID as varchar) + '}' +
-exec sp_OACreate 'MSXML2.XMLHTTP', @object out; +
-exec sp_oamethod @object, 'open', null, 'get', @url +
-exec sp_oamethod @object, 'send'select @url +
---select @responsetext as [Response] +
--- select @url +
-exec sp_oadestroy @object +
-END +
-</code> +
- +
-The values in red are entered manually when the trigger is created. Your Bizweaver administrator can provide this information for you. +
- +
-===== Payments Processing ===== +
-A basic database trigger to initiate Bizweaver processing for payments will look like this. +
- +
-<code sql payments_trigger_sql.txt> +
-CREATE TRIGGER [dbo].[twbs_tr_VersagoPayments] +
-ON [dbo].[Versago_Payments] +
-AFTER INSERT +
-AS +
-BEGIN +
--- SET NOCOUNT ON added to prevent extra result sets from +
--- interfering with SELECT statements. +
-SET NOCOUNT ON; +
--- +
-DECLARE +
-@vgo_BatchID INT, +
-@object INT, +
-@url VARCHAR(5000), +
-@bw_url nvarchar(100), +
-@bw_WorkflowID int, +
-@bw_port int, +
-@pArgVariableName nvarchar(50) +
--- +
-select +
-t0.BW_URL, t0.BW_Port, t0.BW_WorkflowID +
-from vgoCommon_20190604A..twbs_vgo_payment_InfoMapping t0 +
--- +
-select @vgo_batchid = (select vgo_batchid from inserted) +
-select @bw_url = (select t0.bw_url from vgoCommon_20190604A..twbs_vgo_payment_InfoMapping t0) +
-select @bw_port = (select t0.bw_port from vgoCommon_20190604A..twbs_vgo_payment_InfoMapping t0) +
-select @bw_Workflowid = (select t0.BW_WorkflowID from vgoCommon_20190604A..twbs_vgo_payment_InfoMapping t0)  +
--- +
-SELECT @url = @bw_url + '/BWService/api/workflow/InvokeWorkFlow?pTaskID=' + cast(@bw_WorkflowID as varchar) + '&pStartAfter=0&pArguments={' + @pArgVariableName + ':' + CAST(@vgo_BatchID AS VARCHAR) + '}' +
-EXEC sp_OACreate 'MSXML2.XMLHTTP', @object OUT; +
-EXEC sp_oamethod @object, 'open', NULL, 'get', @url +
-EXEC sp_oamethod @object, 'send'SELECT @url +
---select @responsetext as [Response] +
--- select @url +
-EXEC sp_oadestroy @object +
-END +
-</code> +
- +
-The following video illustrates this process.+
  
-{{ :versago:versago_db_trigger_demo.mp4?800 |DB Trigger Demo}}.+The more sophisticated approach is to initiate the workflow when the submission is saved to the database. For the second option a database trigger is used.
  
-It is also possible to make this trigger more flexible by creating a Versago form to hold the information, which is then used when the trigger is executed. Contact your Third Wave consultant or Third Wave support if you have questions about this approach.+The SQL database first needs to be initialized to allow triggered (immediate) processing to occur.  Database triggers are then created to initiate process.
  
 +<WRAP center round important 90%>
 +Descriptions of the initialization process and examples of various triggers are found **//[[bw:call_bizweaver_web_service_from_sql_server|here]]//**.  This information assumes that you have some knowledge about database triggers and have access to the SQL Server tools needed to install them.
 +</WRAP>
versago/record_submission_report_configuration.1559672632.txt.gz · Last modified: 2019/06/04 14:23 by runger

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial 4.0 International
CC Attribution-Noncommercial 4.0 International Valid HTML5 Valid CSS Driven by DokuWiki