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

Remember to change to the correct field before proceeding to run ANY script.

This is a sample SQL, may not be as same as your database.

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}';

In order to sync, you must include LastModified in SELECT

"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"
    }
]

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

#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)

PreviousGood Receive NextCustomer Invoice

Last updated 1 month ago