Document Generation »
Sample Contract Letter To Lessee »
Sample Invoice »
Sample invoice for all leases on Portfolio 2 (dxGeneration) »
Return to Overviews
Document Generation
Sample Invoice
The following example shows how to create a document template for the invoices due and how to generate that document by using information from multiple tables of the database. It also shows how to use the content selector, table select, and XPath expression to merge the data into the document template from the database, the construction of the XPath based on the XML root and child elements, and how the child elements of the XML can be categorized based on the SQL select statements.
- Create the text form of the document in MS Word or in a similar application that allows you to save in ".docx" format. Following is a sample document for invoices of a lease.
- Determine the text from your document that will contain information from the database, such as lease number, lessee address, invoice numbers, and other details of the invoice.
- Identify the database table and column names for the text identified in the previous step. Refer to Database Reference for a detailed reference of LeasePak database table and column names. The following table lists the database table and column names for the information identified in the previous step.
Sr #. Text/Content Database Table Database Column Name 1 General Electric Company Lessee (ral) nam_long_s 2 GE LIGHTING Lessee (ral) add_s 3 Nela Park Lessee (ral) add2_s 4 Cleveland, OH 44112 Lessee (ral) cty_s, st_s, zip_s 5 JOHN SMITH Lessee (ral) short_cont_nam_s 6 41 Lease (rls) lse_s 7 154, 191 Accounts Receivable (rar) invo_s 8 3/1/1989
4/1/1989Accounts Receivable (rar) d_pmtdu_s 9 678
1260Accounts Receivable (rar) (tamt_b_d - tamt_p_d) as amt_due_d To calculate totals and to count the number of outstanding invoices user must set parameter name to calculate and merge the calculated value. In this example, the total number of outstanding invoices will be calculated by using the COUNT function of SQL via SQL select statement in the next steps of this document. Whereas to calculate the total outstanding amount the SUM function of the SQL select statement will be used to sum up the outstanding invoice amounts. These column names and values does not exist in the database. 10 2 Count 11 1938 tot_amt_due_d -
Identify which tracking module you want to use your document template with. Document generation works with specific LeasePak tracking modules only. Each tracking module works with a different component of LeasePak: Application, Lease, Lessee, and Asset. The following table explains the parameters that work with each of the tracking module fields:
Tracking Modules LeasePak Component Database Table.Column name Application Application rap.app_s Collection Lessee/Lease ral.les_s/rls.lse_s Customer Summary,
Insurance,
Maturing Lease,
Recovery,
Lease SummaryLease rls.lse_s Collateral Asset req.unit_s
The selected tracking module component will become the base parameter for document generation. The selected document template can be used to generate document from Collection, Customer Service, Insurance, Maturing Lease, Recovery, and Lease Summary tracking modules because it is based on a lease number. Therefore, the lease number will be used as a base parameter to retrieve other associated fields. The document template can also be used by dxGeneration to generate documents in a batch. The document generation screen captures a list of created documents with parameters to pass to the document generation merge engine by using the identified parameter value.
In this invoice document, 'lease' is a base parameter that will be used to retrieve associated lessee address information and invoice details.
Base parameter = Lease (lse_s)
Use the selected base parameter to retrieve the associated set of parameters identified in the step 3. These identified set of parameters will be used in the SQL select statements with the help of {arg.lse_s}. - Write an SQL select statement by using the identified database column names and base parameter to retrieve the information from database. Document Generation requires a minimum of two SQL select statements to retrieve a record from database, the ROOT level SQL, and SQL at level 1. Users can add more SQLs according to the fields required from database. The column names of the relevant SQL select statement will be passed as parameters to the document generation merge engine to retrieve the associated records. The following table lists the tracking modules and SQLs that build the parameter list and the parameters to use in the ROOT level SQL and other SQLs.
Tracking Module Base Parameter Database Table.Column name SQLs that builds parameter list Parameter available to use in ROOT and Other SQLs Application Application rap.app_s SELECT * FROM RAP
WHERE APP_S = '<selected application number>,The parameter would be:
• {arg.app_s}
• {arg.les_s}
• {arg.dlr_s}
• {arg.d_com_s} …Collection Lessee/Lease ral.les_s/rls.lse_s SELECT * FROM RAL
OR
WHERE LES_S = '<selected lessee number>'
SELECT * FROM RLS
WHERE LSE_S = '<selected lease number>'If lessee is selected then the parameters can be:
• {arg.les_s}
• {arg.cus_s}
• {arg.def_por_s}
• {arg.st_s}
If lease is selected then the parameters can be:
• {arg.lse_s}
• {arg.les_s}
• {arg.dlr_s}
• {arg.d_com_s} …Customer Summary
Insurance
Maturing Lease
Recovery
Lease SummaryLease rls.lse_s SELECT * FROM RLS
WHERE LSE_S = ‘<selected lease number>’• {arg.lse_s}
• {arg.les_s}
• {arg.dlr_s}
• {arg.d_com_s} …Collateral Asset req.unit_s SELECT * FROM REQ
WHERE UNIT_S = ‘<selected asset number>’• {arg.lse_s}
• {arg.unit_s}
• {arg.dlr_s}
• {arg.d_acq_s}
• {arg.cls_s} …
- ROOT Level SQL: The first SQL must be at ROOT level. Use the identified base parameter in the argument of the ROOT level SQL. In this document, the identified base parameter is a lease number. Use the database column name of lease number in the argument of ROOT level SQL. The identified base parameter can be accessed with the help of special syntax in the ROOT level SQL and other SQLs. In this invoice document the ROOT level SQL would be as follows:
SELECT lse_s, les_s
FROM rls
WHERE lse_s = '{ar.lse_s}'
{} = The '{' and '}' brackets are used to identify the SQL parameters at run time.
arg = The arg is used to access the parameter passed in by the tracking modules at run time.
The results of the ROOT SQL will be used in the next SQLs. - Second SQL at Level 1: Write an SQL select statement by using the identified database fields in step 3 to retrieve information from the database. Any of the column names from the ROOT Level SQL can be used in the WHERE clause in this SQL statement. These column names or result values will be identified in the SQL WHERE clause by using the curly brackets { }. This is the second SQL at level 1, which retrieves the lessee associated to the lease. The second SQL at level 1 would be as follows:
SELECT lse_s
FROM rls
WHERE lse_s = '{lse_s}'
- Third SQL at Level 1: Write an SQL select statement by using the identified database fields in step 3 to retrieve information from the database. This is the third SQL at level 1, which retrieves the lessee address information associated to the lease. The third SQL at level 1 would be as follows:
SELECT les_s, short_cont_nam_s, nam_long_s, add_s, add2_s, cty_s, st_s, zip_s
FROM ral
WHERE les_s = '{les_s}'
- Fourth SQL at Level 1: The fourth SQL at level 1 retrieves the invoice detail information. The fourth SQL at level 1 would be as follows:
SELECT invo_s, d_pmtdu_s, (tamt_b_d - tamt_p_d) as amt_due_d
FROM rar
WHERE lse_s = '{lse_s}'
- Fifth SQL at Level 1: The fifth SQL at level 1 is calculating the totals of the required fields, which can have more than one record. In this example, the number of outstanding invoices and payment amount due is calculated as
<count>and<total>. The fifth SQL at level 1 would be as follows:
SELECT COUNT(*) as count, SUM(tamt_b_d - tamt_p_d) AS tot_amt_due_d
FROM rar
WHERE lse_s = '{lse_s}'
- Relationship of ROOT level and SQLs at Level 1: The ROOT level SQL is the SQL statement with the base parameter, which validates the use of the base parameter. The SQLs at level 1 retrieves the relevant record associated with the base parameter. In this document, the base parameter is a lease number that is retrieving the associated lessee address information, and invoice details of that lease. It also includes the total outstanding number of invoices and total payment amount due for that lease.
- ROOT Level SQL: The first SQL must be at ROOT level. Use the identified base parameter in the argument of the ROOT level SQL. In this document, the identified base parameter is a lease number. Use the database column name of lease number in the argument of ROOT level SQL. The identified base parameter can be accessed with the help of special syntax in the ROOT level SQL and other SQLs. In this invoice document the ROOT level SQL would be as follows:
- Use U0736 to add a document
- Use U0736→Data Definition SQL screen to add SQL select statements. Use the SQL select statements identified in the previous steps.
- Use U0736→Data Definition Testing screen to test the SQL select statements from the previous step. Use the base parameter defined in the ROOT level SQL.
- Use U0736 "Create XML File" to determine the XML element names. The XML element names will assist you in creating an XPath expression for your document.
In the above XML, the ROOT level SQL is creating <data> element only. By default, the root element name will be <data> in the XML file. The ROOT level SQL does not create any other XML element. The column names of second, third, fourth, and fifth SQLs at Level 1 will be used to create child elements of the
<data>element. In this XML, the second SQL is retrieving the lessee associated to the selected lease number. The third SQL at level is retrieving the address information of the lessee. The fourth SQL at level 1 is retrieving invoice details of the lease and are grouped as<lease_invoices>. A lease can have multiple invoices therefore; the result record of invoices is grouped into the<lease_invoices>. Each invoice detail is named as<invoice_info>. The fifth SQL at level 1 is counting the number of outstanding invoices as<count>and sum of the total amount due as<tot_amt_due_d>. This SQL statement is setting the XML parameter names in fifth SQL at level 1 because it is calculating the result records retrieved from fourth SQL at level 1.
- Use U0736→Data Definition SQL screen to add SQL select statements. Use the SQL select statements identified in the previous steps.
- Map the SQL select statement column names into the XML elements.
The database column names used in the SQL select statements will become the XML element names. The following table explains the XML elements derived from the SQL statements.
SQL Statement SQL Column Names XML Element Names SELECT lse_s, les_s
FROM rls
WHERE lse_s = '{ar.lse_s}'
SELECT lse_s
FROM rls
WHERE lse_s = '{lse_s}'
SELECT les_s, short_cont_nam_s, nam_long_s, add_s,
add2_s, cty_s, st_s, zip_s
FROM ral
WHERE les_s = '{les_s}'
SELECT invo_s, d_pmtdu_s, (tamt_b_d - tamt_p_d) as amt_due_d
FROM rar
WHERE lse_s = '{lse_s}'
SELECT COUNT(*) as count, SUM(tamt_b_d - tamt_p_d) AS tot_amt_due_d
FROM rar
WHERE lse_s = '{lse_s}'<?xml version="1.0"?> <data> rls.lse_s <lse_s>41</lse_s> ral.les_s <les_s>32</les_s> ral.short_cont_nam_s <short_cont_nam_s>JOHN SMITH</short_cont_nam_s> ral.nam_long_s <nam_long_s>General Electric Company</nam_long_s> ral.add_s <add_s>GE LIGHTING</add_s> ral.add2_s <add2_s>Nela Park</add2_s> ral.cty_s <cty_s>Cleveland</cty_s> ral.st_s <st_s>OH</st_s> ral.zip_s <zip_s>44112</zip_s> <lease_invoices count="2"> <invoice_info> rar.invo_s <invo_s>154</invo_s> rar.d_pmtdu_s <d_pmtdu_s>3/1/1989</d_pmtdu_s> rar.(tamt_b_d - tamt_p_d) as amt_due_d <amt_due_d>678</amt_due_d> </invoice_info> <invoice_info> rar.invo_s <invo_s>191</invo_s> rar.d_pmtdu_s <d_pmtdu_s>4/1/1989</d_pmtdu_s> rar.(tamt_b_d - tamt_p_d) as amt_due_d <amt_due_d>1260</amt_due_d> </invoice_info> </lease_invoices> <totals> count <count>2</count> tot_amt_due_d <tot_amt_due_d>1938</tot_amt_due_d> </totals> </data> - Use the XML elements to create an XPath expression.
Use XML document structured elements to define the XPath in your document. The following table explains how the XML elements are used to create XPath expression for the document template.
XML XPath Expression <?xml version="1.0"?> <data> <lse_s>41</lse_s> /data/lse_s <les_s>32</les_s> /data/les_s <short_cont_nam_s>JOHN SMITH</short_cont_nam_s> /data/short_cont_nam_s <nam_long_s>General Electric Company</nam_long_s> /data/nam_long_s <add_s>GE LIGHTING</add_s> /data/add_s <add2_s>Nela Park</add2_s> /data/add2_s <cty_s>Cleveland</cty_s> /data/cty_s <st_s>OH</st_s> /data/st_s <zip_s>44112</zip_s> /data/zip_s <lease_invoices count="2"> <invoice_info> <invo_s>154</invo_s> ./invo_s <d_pmtdu_s>3/1/1989</d_pmtdu_s> ./d_pmtdu_s <amt_due_d>678</amt_due_d> ./amt_due_d </invoice_info> <invoice_info> <invo_s>191</invo_s> ./invo_s <d_pmtdu_s>4/1/1989</d_pmtdu_s> ./d_pmtdu_s <amt_due_d>1260</amt_due_d> ./amt_due_d </invoice_info> </lease_invoices> <totals> <count>2</count> /data/total/count <tot_amt_due_d>1938</tot_amt_due_d> /data/totals/tot_amt_due_d </totals> </data> - Use the identified XPath expressions to create content selectors for your document. Below is an example of a content selector with XPath expression.
<# <Content Select="/data/les_s" /> #>
In this example, the ROOT level SQL, which always creates data element at root level by default, is created as<data>. Users can skip this in XPath.
<# <Table Select="/data/lease_invoices/invoice_info" /> #>
This is the collection of records to access the information by the defined relative XPath for table.
./invo_s,./d_pmtdu_s,./amt_due_d
In order to print data in each table cell, use relative XPath for the required element. The above XPath merges the data retrieved by the table select XPath.
/data/totals/count
/data/totals/tot_amt_due_d
The elements <count> and <tot_amt_due_d> does not exist in database. Users will need to define the parameter names as per the requirements of the document template. These two elements are calculating the totals of outstanding invoices and total amount due to be paid. Users must define the element names in the relevant SQL select statement by using the SQL function for final calculations.
The following table explains how the identified XPath expression can be used in the content selector root <data> element and table select for multiple records in a table as <lease_invoices> and <invoice_info> elements. The <totals> element is the child element of <data> element.
Sr #. Text/Content Database Table.Column Name Content Selector/XPath Expression 1 General Electric Company ral.nam_long_s <# <Content Select="/data/nam_long_s" /> #> 2 GE LIGHTING ral.add_s <# <Content Select="/data/add_s" /> #> 3 Nela Park ral.add2_s <# <Content Select="/data/add2_s" /> #> 4 Cleveland, OH 44112 ral.cty_s , ral.st_s , ral.zip_s <# <Content Select="/data/cty_s" /> #> ,
<# <Content Select="/data/st_s" /> #>,
<# <Content Select="/data/zip_s" /> #>,5 JOHN SMITH ral.short_cont_nam_s <# <Content Select="/data/short_cont_nam_s" /> #> 6 41 rls.lse_s <# <Content Select="/data/lse_s" /> #> This XPath will be used to gather collection of records. <# <Table Select="/data/lease_invoices/invoice_info" /> #> 7 154, 191 rar.invo_s ./invo_s 8 3/1/1989
4/1/1989rar.d_pmtdu_s ./d_pmtdu_s 9 678
1260rar.(tamt_b_d - tamt_p_d) as amt_due_d ./amt_due_d For totals 10 2 /data/totals/count 11 1938 /data/totals/total_amt_due_d - Replace the identified text from step (2) with the XPath and content selector in the appropriate location of your document template.
This is how the document will look after placing the content and table selector with the XPath expressions.
- Save your document template as .docx type on your PC.
- Use U0712 Custom General→Document Generation Customizations option to set the directory path for your created document template. U0736 requires this directory path and file name to generate a document.
- Use U0736 Document to generate a document. The first two screens require the same data as set in step (6). Refer to U0736 Document for more information.
The SQLs are retrieving records based on lease number. In this example when user enters a lease number, Document Generation looks for the associated lessee address information from ral table. The second SQL at level 1 is retrieving the lessee number attached to lease. The third SQL at level 1 is retrieving the address information of lessee from ral table. Whereas the fourth SQL at level is retrieving the invoices of the lease including the invoice number, due date, and payment amount due from rar table. To calculate totals, the fifth SQL at level 1 is calculating the total number of outstanding invoices and payment amount due.
Reference Guide |
Table of Contents
© 2025 NETSOL Technologies. All rights reserved.
The information contained in this document is the sole property of NETSOL Technologies, Inc. Use of the information contained herein is restricted. Conditions of use are subject to change without notice. NETSOL Technologies, Inc. assumes no liability for any inaccuracy that may appear in this document nor does it have the obligation to update any of the information contained herein; the contents of this document do not constitute a promise or warranty. The software described in this document is furnished under license and may be used or copied only in accordance with the terms of said license. Unauthorized use, alteration, or reproduction of this document without the written consent of NETSOL Technologies, Inc. is prohibited.
The information contained in this document is the sole property of NETSOL Technologies, Inc. Use of the information contained herein is restricted. Conditions of use are subject to change without notice. NETSOL Technologies, Inc. assumes no liability for any inaccuracy that may appear in this document nor does it have the obligation to update any of the information contained herein; the contents of this document do not constitute a promise or warranty. The software described in this document is furnished under license and may be used or copied only in accordance with the terms of said license. Unauthorized use, alteration, or reproduction of this document without the written consent of NETSOL Technologies, Inc. is prohibited.