# Payment Voucher ## Payment Voucher's Database

UI Name	Database	Type	Remarks
Date	`cb.DOCDATE`	`Date`	E.g.: `2024-11-15`
Voucher No	`cb.DOCNO`	`String`	E.g.: `PV12345`
Payment To	`cb.DESCRIPTION`	`String`	E.g.: `Vendor ABC`
Payment By	`ga.DESCRIPTION`	`String`	E.g.: `Bank XYZ`
Currency	`cb.CURRENCYCODE`	`String`	E.g.: `USD`
Bank Charge	`cb.BANKCHARGE`	`Number`	E.g.: `10.00`
Cheque No	`cb.CHEQUENUMBER`	`String`	E.g.: `123456`
Project	`cb.PROJECT`	`String`	E.g.: `Project A`
From Doc	`cb.FROMDOC`	`String`	E.g.: `Doc123`
Agent	`cb.AGENT`	`String`	E.g.: `Agent X`
Area	`cb.AREA`	`String`	E.g.: `Area 51`
Cancelled	`cb.CANCELLED`	`Boolean`	E.g.: `true`
Local Net Total	`cb.LOCALDOCAMT`	`Number`	E.g.: `1000.00`
Net Total	`cb.DOCAMT`	`Number`	E.g.: `1000.00`
Seq	`dtl.SEQ`	`Number`	E.g.: `1`
G/L Code	`dtl.CODE`	`String`	E.g.: `GL123`
GL Description	`ga2.DESCRIPTION`	`String`	E.g.: `General Ledger`
Description (Detail)	`dtl.DESCRIPTION`	`String`	E.g.: `Payment for services`
Project (Detail)	`dtl.PROJECT`	`String`	E.g.: `Project B`
Company Name	`dtl.COMPANYNAME`	`String`	E.g.: `Company XYZ`
GST No	`dtl.GSTNO`	`String`	E.g.: `GST123456`
Inv No	`dtl.GST_DOCNO`	`String`	E.g.: `INV12345`
Amount	`dtl.AMOUNT`	`Number`	E.g.: `500.00`
Tax	`dtl.TAX`	`Number`	E.g.: `50.00`
Tax Rate	`dtl.TAXRATE`	`String`	E.g.: `10%`
Tax Inclusive	`dtl.TAXINCLUSIVE`	`Boolean`	E.g.: `true`
Tax Amt	`dtl.TAXAMT`	`Number`	E.g.: `50.00`
Sync From	`'ABC COMPANY'`	`String`	E.g.: `ABC COMPANY`
Last Modified	`a.DOCDATETIME`	`Timestamp`	E.g.: `2024-11-15 07:48:46.563`

## Payment Voucher's Sample Code {% hint style="warning" %} Remember to change to the **correct field** before proceeding to run **ANY** script. {% endhint %} {% tabs %} {% tab title="SQL Script" %} {% hint style="info" %} This is a sample SQL, may not be as same as your database. {% endhint %} ```sql WITH AuditData AS ( SELECT SUBSTRING(a.REF FROM POSITION(', ' IN a.REF) + 2) AS AUDITKEY, a.REF, MAX(a.DOCDATETIME) AS DOCDATETIME FROM AUDIT a WHERE a.REF LIKE 'GL_CB, %' GROUP BY a.REF ) SELECT FIRST 3 cb.DOCDATE docDate, cb.DOCNO voucherNo, cb.DESCRIPTION paymentTo, ga.DESCRIPTION paymentBy, cb.CURRENCYCODE currency, cb.BANKCHARGE bankCharge, cb.CHEQUENUMBER chequeNo, cb.PROJECT project, cb.FROMDOC fromDoc, cb.AGENT agent, cb.AREA area, cb.CANCELLED cancelled, cb.LOCALDOCAMT localNetTotal, cb.DOCAMT netTotalRm, dtl.SEQ seq, dtl.CODE glCode, ga2.DESCRIPTION glDescription, dtl.DESCRIPTION dtlDescription, dtl.PROJECT dtlProject, dtl.COMPANYNAME companyName, dtl.GSTNO gstNo, dtl.GST_DOCNO invoiceNo, dtl.AMOUNT amount, dtl.TAX tax, dtl.TAXRATE taxRate, dtl.TAXINCLUSIVE taxInclusive, dtl.TAXAMT taxAmount, '{YOUR_COMPANY}' syncFrom, a.DOCDATETIME lastModified FROM AuditData a JOIN GL_CB cb ON cb.DOCKEY = a.AUDITKEY JOIN GL_CBDTL dtl ON cb.DOCKEY = dtl.DOCKEY JOIN GL_ACC ga ON cb.PAYMENTMETHOD = ga.CODE JOIN GL_ACC ga2 ON ga2.CODE = dtl.CODE WHERE cb.DOCTYPE = 'PV' AND a.DOCDATETIME > TIMESTAMP '{LAST_MODIFIED_DATE_TIME}'; ``` {% endtab %} {% tab title="jobs.json" %} {% hint style="warning" %} In order to sync, you must include **`LastModified`** in **`SELECT`** {% endhint %} ```json "SQLSync": [ { "name": "Payment Voucher", "enabled": true, "server": { "db": "{DATABASE_FILENAME}", "dcf": "C:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF", "username": "ADMIN", "password": "ADMIN" }, "from": "sqlaccounting", "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}", "sql": "WITH AuditData AS (\nSELECT SUBSTRING(a.REF FROM POSITION(', ' IN a.REF) + 2) AS AUDITKEY, a.REF, MAX(a.DOCDATETIME) AS DOCDATETIME \nFROM AUDIT a \nWHERE a.REF LIKE 'GL_CB, %' \nGROUP BY a.REF\n)\nSELECT cb.DOCDATE docDate, cb.DOCNO voucherNo, cb.DESCRIPTION paymentTo, ga.DESCRIPTION paymentBy, cb.CURRENCYCODE currency, cb.BANKCHARGE bankCharge, cb.CHEQUENUMBER chequeNo, cb.PROJECT project, cb.FROMDOC fromDoc, cb.AGENT agent, cb.AREA area, cb.CANCELLED cancelled, cb.LOCALDOCAMT localNetTotal, cb.DOCAMT netTotalRm, dtl.SEQ seq, dtl.CODE glCode, ga2.DESCRIPTION glDescription, dtl.DESCRIPTION dtlDescription, dtl.PROJECT dtlProject, dtl.COMPANYNAME companyName, dtl.GSTNO gstNo, dtl.GST_DOCNO invoiceNo, dtl.AMOUNT amount, dtl.TAX tax, dtl.TAXRATE taxRate, dtl.TAXINCLUSIVE taxInclusive, dtl.TAXAMT taxAmount, '{YOUR_COMPANY}' syncFrom, a.DOCDATETIME lastModified \nFROM AuditData a \nJOIN GL_CB cb ON cb.DOCKEY = a.AUDITKEY \nJOIN GL_CBDTL dtl ON cb.DOCKEY = dtl.DOCKEY \nJOIN GL_ACC ga ON cb.PAYMENTMETHOD = ga.CODE \nJOIN GL_ACC ga2 ON ga2.CODE = dtl.CODE \nWHERE cb.DOCTYPE = 'PV' AND a.DOCDATETIME > @date", "lastSync": "2025-01-01T00:00:00.000" } ] ``` {% endtab %} {% tab title="Logic Script" %} {% hint style="info" %} To find `{fieldName}`, you are required to have access to **Alpha** or use **Network** in Developer Tools `[F12]`. {% endhint %} {% code fullWidth="true" %} ```python #debug('test', inputs) moduleName = "Payment Voucher" inistateKeyField = "DocumentId" keyField = 'VOUCHERNO' modifiedDateField = 'LastModified' fields = [['DocumentId', 'Doc'],['LastModified', 'LastModified']] inistateFields = [] tableField = 'Items' tableColumns = ["GLCODE", "GLDESCRIPTION", "DTLDESCRIPTION", "DTLPROJECT", "COMPANYNAME", "GSTNO", "INVOICENO", "AMOUNT", "TAX", "TAXRATE", "TAXINCLUSIVE", "TAXAMOUNT"] def getFlatten(list, table): details = [dict(Line=y, Document=x) for x in list for y in x[table]] return details def createLine(x): item = JObject() main = JObject.FromObject(x[0]) for field in main: if not tableColumns.Contains(field.Name): item[field.Name] = main[field.Name] item['items'] = JArray() for itemRow in x: rowJSON = JObject() for field in tableColumns: rowJSON[field] = itemRow[field] item['items'].Add(rowJSON) return item result = list(inputs["list"]).GroupBy(lambda x: x[keyField].ToString()).Select(lambda x: createLine(x)).ToList() for item in result: entry = Module(moduleName).find({ 'Dynamic.Extension.{fieldName}': item["VOUCHERNO"] }) form = Module(moduleName).execute("sync", entry) form['Transaction Date'] = item["DOCDATE"] form['Voucher No'] = item["VOUCHERNO"] form['Payment To'] = item["PAYMENTTO"] form['Payment By'] = item["PAYMENTBY"] form['Currency'] = item["CURRENCY"] form['Bank Charge'] = item["BANKCHARGE"] form['Cheque No'] = item["CHEQUENO"] form['Project'] = item["PROJECT"] form['From Doc'] = item["FROMDOC"] form['Agent'] = item["AGENT"] form['Area'] = item["AREA"] if item["CANCELLED"] == 'T': form['Cancelled'] = True else: form['Cancelled'] = False form['Local Net Total'] = item["LOCALNETTOTAL"] form['Net Total (RM)'] = item["NETTOTALRM"] form['Sync From'] = item["SYNCFROM"] form['Sync Datetime'] = DateTime.Now # handle table lines = list(item['items']).ToList().OrderBy(lambda x: Convert.ToInt32(x['SEQ'])).ToList() while len(form['Details']) > len(lines) : last = form['Details'].Last() formLine = form.remove('Details', last) for line in lines: # create if none if len(form['Details']) <= lines.IndexOf(line): formLine = form.add('Details') else : formLine = form['Details'][lines.IndexOf(line)] # assign value to table row formLine['G/L Code'] = line["GLCODE"] formLine['GL Description'] = line["GLDESCRIPTION"] formLine['Description'] = line["DTLDESCRIPTION"] formLine['Project'] = line["DTLPROJECT"] formLine['Company Name'] = line["COMPANYNAME"] formLine['GST No'] = line["GSTNO"] formLine['Inv No'] = line["INVOICENO"] formLine['Amount'] = line["AMOUNT"] formLine['Tax'] = line["TAX"] formLine['Tax Rate (%)'] = line["TAXRATE"] formLine['Tax Inclusive'] = line["TAXINCLUSIVE"] formLine['Tax Amt'] = line["TAXAMOUNT"] debug('form', form) Module(moduleName).submit(entry, form) debug('result', result) ``` {% endcode %} {% endtab %} {% endtabs %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://inistate.gitbook.io/home/advanced/integration/accounting-2/payment-voucher.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.