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.

Inistate To SQL Accounting

jobs.json

In order to sync, you must include LastModified in SELECT

"SQLSync": [
    {
      "name": "SyncInvoice",
      "enabled": true,
      "server": {
        "db": "{DATABASE_FILENAME}",
        "dcf": "C:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF",
        "username": "ADMIN",
        "password": "ADMIN"
      },
      "to": "sqlaccounting",
      "debug": false,
      "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
      "errorHook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
      "lastSync": "2025-10-08T12:07:08.302+08:00",
      "interval": 5,
      "start": null
    }
]

Logic Script

SQL Accounting to Inistate

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

jobs.json

Logic Script