KBA-01183: Extensions to Document Save Transaction; SaveCustomDocExtn Rules

Question:

How can I update other SQL data when a Spitfire document is saved?
Can I inject complicated validations and business logic into the sfPMS data layer?

Answer:

Use the SaveCustomDocExtn rule group, described below.


Overview:

The SaveCustomDocExtn rules allow specific stored procedures to be run during the document save transaction. These advanced rules require TSQL programming. In addition, these rules have no effect unless the DocTypeConfig | CustomSaveExtenstions rule is checked (see KBA-01154).

Note: Except for the On Final rule, if your stored procedure raises an exception, the entire document save transaction is rolled back and the exception text is displayed, making it possible to perform extensive validations.   We recommend you first consider using either the UI Configuration ‘required when’ feature or a Document Event Workflow Script. Warning: use of this rule group can have unintended and serious consequences!

The SaveCustomDocExtn rule group is found in the Rules Maintenance tool on the System Admin Dashboard. Each rule in the group consists of a rule name, a filter value (which can be blank) and a result value.

Informing the User

If your procedure raises an exception it will be display to the user.  Your message can include some simple HTML markup.  For example, this snippet prevents two documents from having the same date:

-- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
DECLARE @ALTDMK UniqueIdentifier 
SET @ALTDMK = (SELECT top 1 xh.DocMasterKey
  FROM dbo.xsfDocHeader xh 
    JOIN dbo.v_DocState ds on ds.DocMasterKey = xh.DocMasterKey  and ds.DocState <> 'C'
  WHERE DATEDIFF( day, xh.DocDate ,@DocDate ) = 0
    and xh.DocMasterKey <> @pDocKey
    and xh.DocTypeKey = @ThisDTK
  ORDER BY DocNo ) 
IF @ALTDMK is not null BEGIN 
   SET @MSG = 'A <a title="Open" class="sfDocHL" data-key="' 
       + cast(@AltDMK as varchar(44)) 
       + '" data-permits="0" href="javascript:$PopDoc();">Doc with this date</a> already exists' 
   RAISERROR (@MSG, 16, 1)
END
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 

The result is a message with an HTML anchor. The anchor carries a class indicating a document hyperlink is to be applied and a data attribute with the key of the document is to be opened.

The system will display a dialog similar to this

Save Extension Msg Dialog


Filter Value:

Any Doc type

The SaveCustomDocExtn rules accept a Doc type (as defined in the Doc Types tool and appearing on the filter drop-down) as the filter value.


Result Value:

Stored Procedure

The SaveCustomDocExtn rules accept the name of any stored procedure as the result value. The procedure must be owned by ‘DBO’. Consult the Developer’s Primer for details on the parameters passed to the stored procedure: @pDocKey, @pDocRevKey and @pUserKey.


Rules:

OnApproveInitial

Specifies (in the result value) the stored procedure to be used at the beginning of the save transaction, when the new document state is changing and will be Approved.

OnApproveWrapup

Specifies (in the result value) the stored procedure to be at the end of the save transaction when the new document state is changing and will be Approved.  Note: full coverage of every save requires OnWrapup, OnNewWrapup (below), OnPendingWrapup (below), OnApproveWrapup and OnCloseWrapup (below) to be used in combination.

OnApproveWritten

Specifies (in the result value) the stored procedure that should be used near the end of the save transaction when the new document state is changing and will be Approved, after row data has been updated, but before any other side effects or postings.

OnCloseWrapup

Specifies (in the result value) the stored procedure that should be used at the end of the save transaction when the new document state is Closed (but not Approved).   Note: full coverage of every save requires OnWrapup, OnNewWrapup (below), OnPendingWrapup (below), OnApproveWrapup (above) to be used in combination.

OnFinal

Specifies (in the result value) the stored procedure to be used to send data after the save transaction. The extension will have access to audit data using a fourth required parameter (@pAuditWhen) but cannot roll back the save because it has already been committed.  Likewise, since the extension is fired so late, any errors from this procedure (such as a missing parameter) will only appear in the web application log, not on the document window.

OnInitial

Specifies (in the result value) the stored procedure to be used at the start of the save transaction for previously saved documents.

OnNewWrapup

Specifies (in the result value) the stored procedure to be used at the end of the save transaction for new documents.

OnPendingInitial

Specifies (in the result value) the stored procedure to be used at the beginning of the save transaction, when the new document state is Pending/Committed.

OnPendingWrapup

Specifies (in the result value) the stored procedure to be used at the end of the save transaction, when the new document is Pending/Committed.  Note: full coverage of every save requires OnNewWrapup (below), OnWrapup, OnApproveWrapup and OnCloseWrapup (above) to be used in combination.

OnPendingWritten

Specifies (in the result value) the stored procedure to be used near the end of the save transaction, after row data has been updated, when the new document state is changing and will be Pending/Committed.

OnWrapup

Specifies (in the result value) the stored procedure to be used at the end of the save transaction for previously saved documents.  Note: full coverage of every save requires OnNewWrapup, OnPendingWrapup, OnApproveWrapup and OnCloseWrapup and OnWrapup all to be used in combination.


Additional Comments:

For information about the UI Configuration tool or the Rules Maintenance tool, see the Focus on System Administration guide.


KBA-01183; Last updated: September 22, 2017 at 11:54 am; 
Keywords:  save extension, rules