Customer Invoice

Customer Invoice's Database

UI Name

Database

Type

Remarks

Doc Date

iv.DOCDATE

Date

E.g.: 2024-11-15

Inv No

iv.DOCNO

String

E.g.: INV12345

Currency Code

iv.CURRENCYCODE

String

E.g.: USD

Agent

iv.AGENT

String

E.g.: Agent X

Terms

iv.TERMS

String

E.g.: Net 30

Ext. No

iv.DOCNOEX

String

E.g.: EXT123

Customer Code

iv.CODE

String

E.g.: CUST123

Area

iv.AREA

String

E.g.: Area 51

Bill To

ga.DESCRIPTION

String

E.g.: Customer ABC

Invoice Description

iv.DESCRIPTION

String

E.g.: Payment for services

Cancelled

iv.CANCELLED

Boolean

E.g.: true

Local Net Total

iv.LOCALDOCAMT

Number

E.g.: 1000.00

Net Total

iv.DOCAMT

Number

E.g.: 1000.00

Seq

dtl.SEQ

Number

E.g.: 1

Sales A/C

dtl.ACCOUNT

String

E.g.: SA123

Description (Detail)

dtl.DESCRIPTION

String

E.g.: Payment for services

Project (Detail)

dtl.PROJECT

String

E.g.: Project B

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 Amount

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

Customer Invoice'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 'AR_IV, %'
    GROUP BY a.REF
)
SELECT FIRST 3
    iv.DOCDATE docDate,
    iv.DOCNO docNo,
    iv.CURRENCYCODE currencyCode,
    iv.AGENT agent,
    iv.TERMS terms,
    iv.DOCNOEX docNoExt,
    iv.CODE customerCode,
    iv.AREA area,
    ga.DESCRIPTION billTo,
    iv.DESCRIPTION invoiceDescription,
    iv.CANCELLED cancelled,
    iv.LOCALDOCAMT localNetTotal,
    iv.DOCAMT netTotalRm,
    dtl.SEQ seq,
    dtl.ACCOUNT salesAccount,
    dtl.DESCRIPTION dtlDescription,
    dtl.PROJECT project,
    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 AR_IV iv ON a.AUDITKEY = iv.DOCKEY
JOIN AR_IVDTL dtl ON iv.DOCKEY = dtl.DOCKEY
JOIN GL_ACC ga ON iv.CODE = ga.CODE
WHERE a.DOCDATETIME > TIMESTAMP '{LAST_MODIFIED_DATE_TIME}'

In order to sync, you must include LastModified in SELECT

"SQLSync": [
  {
    "name": "Customer Invoice",
    "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 'AR_IV, %' \nGROUP BY a.REF) \nSELECT iv.DOCDATE docDate, iv.DOCNO docNo, iv.CURRENCYCODE currencyCode, iv.AGENT agent, iv.TERMS terms, iv.DOCNOEX docNoExt, iv.CODE customerCode, iv.AREA area, ga.DESCRIPTION billTo, iv.DESCRIPTION invoiceDescription, iv.CANCELLED cancelled, iv.LOCALDOCAMT localNetTotal, iv.DOCAMT netTotalRm, dtl.SEQ seq, dtl.ACCOUNT salesAccount, dtl.DESCRIPTION dtlDescription, dtl.PROJECT project, 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 AR_IV iv ON a.AUDITKEY = iv.DOCKEY \nJOIN AR_IVDTL dtl ON iv.DOCKEY = dtl.DOCKEY \nJOIN GL_ACC ga ON iv.CODE = ga.CODE \nWHERE 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 = 'Customer Invoice'
inistateKeyField = 'DocumentId'
keyField = 'DOCNO'
modifiedDateField = 'LastModified'
fields = [['DocumentId', 'Doc'],['LastModified', 'LASTMODIFIED']]
inistateFields = []
tableField = 'Items'
tableColumns = ['SALESACCOUNT','DTLDESCRIPTION','PROJECT','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['DOCNO'] })
    
    form = Module('Customer Invoice').execute('sync', entry)
    form['Transaction Date'] = item['DOCDATE']
    form['Inv No'] = item['DOCNO']
    form['Currency'] = item['CURRENCYCODE']
    form['Agent'] = item['AGENT']
    form['Terms'] = item['TERMS']
    form['Ext No'] = item['DOCNOEXT']
    form['Customer Code'] = item['CUSTOMERCODE']
    form['Area'] = item['AREA']
    form['Bill To'] = item['BILLTO']
    form['Invoice Description'] = item['INVOICEDESCRIPTION']
    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)]
        
        formLine['Sales A/C'] = line['SALESACCOUNT']
        formLine['Description'] = line['DTLDESCRIPTION']
        formLine['Project'] = line['PROJECT']
        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)

PreviousPayment Voucher NextCustomer Payment

Last updated 2 days ago