Finalising the SQL script

Last modified

Overview

Once the SP has been amended and checked, an additional segment may be added to the script.  This enables the script to determine

  • how and where the pdf may be run from, and
  • which types of job the pdf will be available in

The various options are shown below.

Finalising the SP script.

1.    The listing below provides an example of the MT import and Report definitions:

2.    This section creates the definition for the MT:

EXEC gensp_MergeTemplateImport
     @MTID = 315041
   , @MTLocation = 3
   , @MTCat = 70000
   , @MTDesc = 'Form 4.20 - Statement of Companys Affairs'
   , @Extension = 'pdf'
   , @DeleteExisting = 1
   , @MTPrecDesc ='<root>
                           <sql>pdfsp_Form_4_20_statement_of_companys_affairs
                                   <parameters>
                                   </parameters>
                           </sql>
                   </root>'
   , @MTFlags = 524288
   , @ReportID = 315041
   , @MTReportCategoryID = 51000
   , @SystemFlags = 53

The various settings, and how you get to them, are described below.

3.    This script will identify the Merge Template ID, Merge Template category and other variables required for the Merge template import routine:

The results from this script are effectively as set in Manage Document Templates.

Script variable

Table field

 

@MTID

MergeTemplateID

The MT reference identity, obtained from Listing x

@MTLocation

LocationID

The location of the Practice:

    Australia = 1

    UK = 3

    All locations = 99

@MTCat

MergeTemplateCategoryID

The category for the MT, or where the MT is shown on the folder pane in Manage Document Templates.

For PDF MTs, this will always be 70000.

@MTDesc

Description

The MT’s file name, excluding the file extension.

@Extension

Extension

The file extension, in this case, pdf (without the preceding dot).

@MTPrecDesc

PrecidentActionDescription

This is the SQL script passed to the application when the MT is being prepared.

See section 4 below.

@MTFlags

Flags

The value here depends on the various options set in Manage Document Templates. 

See section 5 below.

@ReportID

MergeTemplateID

The MT reference identity, this time for creating a report reference to the MT.  See listing

@MTReportCategoryID

ReportCategoryID

The Report Category indicates where MT will be shown on the Reports and Forms menu.

@SystemFlags

SystemFlags

<internal>

4.    For the @MTPrecDesc section, the following parameters are used:

Custom Field Type

Data Type

Example XML text

Options:

36

<parameter prompt="Were you appointed over the whole or part of the company''s property?" datatype="36" sqlparameter="@How_Appointed" options="whole|part"/>

SQL Script:

 

<not implemented>

Date:

7

<parameter prompt="Please enter the VAT input from date" datatype="7" sqlparameter="@FromDate"/>

Number / Text:

8

<parameter prompt="Enter the address of the scheme Trustee or Manager, excl postcode" datatype="8" sqlparameter="@TrusteeAddress"/>

Multiple lines of text:

9

<parameter prompt="Enter the short particulars from the charge document" datatype="9" sqlparameter="@ShortParticulars"/>

The @MTFlags parameter relates to the various settings in the ‘User Prompts’ settings of ‘Manage Document Templates’ and probably easiest set manually then recovering the settings using Listing xx above.

Note

To obtain the value for @MTFlags manually, the following values for the various User Prompts selection values are used:

User Prompt

Value

Meeting

16834

Dividend or Distribution

32768

Appointee

65536

One or More Signatories

131072

One Signatory only

524288

Retired Appointee

1048576

Contribution

33554432

Asset

67108864

So, if the ‘Meeting’ and ‘One Signatory Only’ are selected, the value for @MTFlags will be:

            Meeting                         16384

            One Signatory Only         524288

            @MTFlags value       540672

Similarly, selections for ‘Dividend’, ‘One or More Signatories’ and  ‘Assets’ will result in a value for @MTFlags of 67272704 (32768 + 131072 + 67108864).

However, you may not select ‘Appointee’ and ‘Retired Appointee’ together, for obvious reasons.

5.    The value for @ReportID is generally the same as for the MT ID, but there is no requirement for it to be the same.

If this is a new pdf, you should probably overwrite the old pdf, so a search for the old pdf, which can be found using listing xx below, substituting the required pdf’s name for that shown in the listing:

If the pdf is new, a new ReportID should be assigned.  Confirm the next free row with the following listing:

Note

The value of ReportID should be in the same range as similar pdf forms – between 300000 and 500000.

6.    The value for @MTReportCategoryID  can be obtained from the following listing:

7.    The following sections define the job types in which the MT and Report is displayed.  The MT job type is defined here …

DELETE FROM MergeTemplateAdminType 
WHERE MergeTemplateID = 315041

INSERT INTO MergeTemplateAdminType (MergeTemplateID,AdminTypeID, TS )
VALUES (315041, 4100, NEWID())

INSERT INTO MergeTemplateAdminType (MergeTemplateID, AdminTypeID, TS)
VALUES (315041, 4105, NEWID())

… and the Report job type is defined here:

DELETE FROM ReportAdminType
WHERE ReportID = 315041

INSERT INTO ReportAdminType (ReportID, AdminTypeID, TS)
VALUES (315041, 4100, NEWID())

INSERT INTO ReportAdminType (ReportID, AdminTypeID, TS)
VALUES (315041, 4105, NEWID())

The MergeTemplateID and the ReportID are as identified in previous sections.  The job type can be identified using the following script:

Note

The values of -1 (all job types), -2 (all corporate jobs) and -3 (all personal jobs) may also be used.

Related

Page statistics
321 view(s) and 7 edit(s)
Social share
Share this page?

Tags

CCH Insolvency

Comments

Attachments

 

 | Cookie Policy | Copyright | Privacy Policy Terms of Use | Contact Us |