Sales Order

Sales Order's Database

UI Name	Database	Type	Remarks
S/O No	docNo	String	Avoid using the same S/O No to prevent overwrite. E.g.: SO-123456
S/O Key	docKey	Number	E.g.: 123456
Debtor	debtorCode	String	Debtor must match existing debtor code, and they must be active state. E.g.: 400-D001
Name	debtorName	String	E.g.: JOHN DOE
Address [Line 1]	invAddr1	String	E.g.: 123 MAIN ST,
Address [Line 2]	invAddr2	String	E.g.: APT 4B,
Address [Line 3]	invAddr3	String	E.g.: NEW YORK, NY 10001
Delivery Address [Line 1]	DeliverAddr1	String	E.g.: 456 SECOND ST,
Delivery Address [Line 2]	DeliverAddr2	String	E.g.: SUITE 5A,
Delivery Address [Line 3]	DeliverAddr3	String	E.g.: LOS ANGELES, CA 90001
Sales Location	salesLocation	String	Must have existing Sales Location in the accounting system. E.g.: HQ
Date	docDate	Date	E.g.: 2024-08-12T00:00:00+08:00
Credit Term	creditTerm	String	Credit Term must match the same in the accounting system. E.g.: C.O.D.
Agent	agent	String	E.g.: JANE SMITH
Ref	ref	String	E.g.: REF-123456
Remark 2	Remark2	String	E.g.: PENDING
Multi Pricing	multiPricing	String	E.g.: Price 1
Description	description	String	E.g.: ORDER DESCRIPTION
Expiry Date	SalesExemptionExpiryDate	Date	E.g.: 2024-10-17T00:00:00+08:00
Phone	phone1	String	E.g.: [phone]
Fax	fax1	String	E.g.: +0987654321
Inclusive	inclusiveTax	Boolean	E.g.: true
Credit Term	displayTerm	String	Credit Term must match the same in the accounting system. E.g.: C.O.D.
UDF	udf	Object	Refer Below
Table Item	details	Array	Refer Below

Sales Order's UDF

UI Name	Database	Type	Remarks
Venue	VENUE	String	E.g.: CONFERENCE CENTER
Gmail	Gmail	String	E.g.: [e-mail]
IC	IC	String	E.g.: S1234567D
Balance	BALANCE	Number	E.g.: 1000
Payment	PAYEMENT	String	E.g.: CREDIT CARD
Branding	BRANDING	String	E.g.: BRAND X
Note	Note	String	E.g.: SPECIAL INSTRUCTIONS
PDate	PDate	Date	E.g.: 2024-10-05T00:00:00+08:00

Sales Order's Table Item

UI Name	Database	Type	Remarks
Item No	ItemCode	String	E.g.: ITEM-123456
Description	description	String	E.g.: ITEM DESCRIPTION
Description 2	desc2	String	E.g.: ADDITIONAL INFO
Delivery Date	deliveryDate	Date	E.g.: 2024-10-17T00:00:00+08:00
Location	location	String	E.g.: WAREHOUSE 1
Quantity	qty	Number	Value must be number, and value cannot be negative. E.g.: 10
UOM	uom	String	E.g.: PCS
Unit Price	unitPrice	Number	Value must be number, and value cannot be negative. E.g.: 100
Subtotal	subtotal	Number	Value must be number, and value cannot be negative. E.g.: 1000
Total	total	Number	Value must be number, and value cannot be negative. E.g.: 1100
UDF	udf	Object	E.g.: "POSTTOPO": "T"
Description 2	description2	String	E.g.: ADDITIONAL DESCRIPTION

Stock Transfer's Sample Code

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

Logic Script

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

import clr

clr.AddReference("Newtonsoft.Json")
clr.AddReference("System.Net")
clr.AddReference("System.IO")

from System.Text import *
from Newtonsoft.Json import *
from Newtonsoft.Json.Linq import *
from System import *

clr.AddReference("System.Drawing")
from System.Drawing import Bitmap, Image
from System.Drawing.Imaging import ImageFormat
from System.IO import MemoryStream, StreamWriter
from System.Net import WebClient
import binascii
from System import BitConverter

# search
documentList = Module("{YOUR-MODULE-NAME}").findAll(
    {
        "$or": [
            {
                "Audit.UpdatedDate": {"$gt": Inputs["lastSync"]},
                "Audit.UpdatedBy": {"$ne": "External"},
                "Dynamic.Extension.{fieldName}": {"$in": [None, ""]},
            },
            {
                "Audit.UpdatedDate": {"$gt": Inputs["lastSync"]},
                "Dynamic.Extension.{fieldName}": True,
                "Dynamic.Extension.{fieldName}": {"$in": [None, ""]},
            },
        ]
    }
)
debug("documentList", documentList)

resultList = []

# added
def prepareRow(data, x):
    row = JObject()
    # row["ItemCode"] = x['Model']
    row["ItemCode"] = (
        str(x["{YOUR-ITEM-MODEL}"])[:30] if x["{YOUR-ITEM-ID}"] > 0 else None
    )
    row["description"] = x["{YOUR-ITEM-DESCRIPTION}"]
    row["desc2"] = (
        str(x["{YOUR-REMARKS}"])[:100] if x["{YOUR-REMARKS}"] is not None else None
    )
    row["deliveryDate"] = x["{YOUR-DELIVERY-DATE}"]
    row["location"] = data["{YOUR-WAREHOUSE-NAME}"]
    row["qty"] = x["{YOUR-QUANTITY}"]
    row["uom"] = x["{YOUR-ITEM-UOM}"] if x["{YOUR-ITEM-ID}"] > 0 else None
    row["unitPrice"] = x["{YOUR-UNIT-PRICE}"]
    row["subtotal"] = x["{YOUR-AMOUNT}"]
    row["total"] = x["{YOUR-AMOUNT}"]
    row["taxAdjustment"] = None
    row["udf"] = JObject()
    row["udf"]["POSTTOPO"] = x["{YOUR-POST-TO-PO}"]

    row["description2"] = x["{YOUR-REMARKS}"]

    if x["{YOUR-TAX-CODE-ID}"] > 0:
        row["taxType"] = x["{YOUR-TAX-CODE}"]["{YOUR-CODE}"]
        row["taxRate"] = x["{YOUR-TAX-CODE}"]["{YOUR-RATE}"].ToString()
    row["deliveryDate"] = x["{YOUR-DELIVERY-DATE}"]

    if (
        x["{YOUR-PHOTO}"] is not None
        and x["{YOUR-PHOTO}"]["{YOUR-PATH}"] is not None
        and x["{YOUR-PATH}"] != ""
    ):
        url = FilePathTranslator.GetPath(str(x["{YOUR-PHOTO}"]["{YOUR-PATH}"]))
        # debug('path', url)
        result = image_to_hex(url)
        # debug('pic', result)
        row["furtherDescription"] = result
    return row


def preparePayload(data):
    payloadData = JObject()
    payloadData["type"] = "SalesOrder"
    payloadData["cancelled"] = False
    payloadData["payload"] = JObject()
    payloadData["payload"]["docNo"] = data["{YOUR-DOCUMENT-ID}"]
    if data["{YOUR-CODE}"] != None and data["{YOUR-CODE}"] != 0:
        payloadData["payload"]["docKey"] = data["{YOUR-CODE}"]
    payloadData["payload"]["debtorCode"] = data["{YOUR-DEBTOR-CODE}"]
    payloadData["payload"]["debtorName"] = data["{YOUR-NAME}"]

    if data["{YOUR-ADDRESS}"] is not None:
        lines = data["{YOUR-ADDRESS}"].split("\n")
        for i, line in enumerate(lines):
            if i < 4:
                line = str(line.rstrip("\r"))[:40] if line is not None else ""
                payloadData["payload"]["invAddr{0}".format(i + 1)] = line
                payloadData["payload"]["DeliverAddr{0}".format(i + 1)] = line

    payloadData["payload"]["salesLocation"] = data["{YOUR-WAREHOUSE-NAME}"]
    # payloadData["payload"]["docNo"] = None if data['{YOUR-DOCUMENT-ID}'] == "" or data['{YOUR-DOCUMENT-ID}'] is None else data['{YOUR-DOCUMENT-ID}']
    payloadData["payload"]["docDate"] = data["{YOUR-ORDER-DATE}"]
    payloadData["payload"]["creditTerm"] = data["{YOUR-CREDIT-TERM}"]
    payloadData["payload"]["agent"] = data["{YOUR-SALESPERSON-CODE}"]
    payloadData["payload"]["ref"] = data["{YOUR-REFERENCE}"]
    payloadData["payload"]["Remark2"] = data["{YOUR-STATUS-2}"]

    payloadData["payload"]["udf"] = JObject()
    payloadData["payload"]["udf"]["VENUE"] = data["{YOUR-VENUE-NAME}"]
    payloadData["payload"]["udf"]["Gmail"] = data["{YOUR-EMAIL}"]
    payloadData["payload"]["udf"]["IC"] = data["{YOUR-IC-NO}"]
    payloadData["payload"]["udf"]["BALANCE"] = data["{YOUR-BALANCE}"]
    payloadData["payload"]["udf"]["PAYEMENT"] = (
        str(data["{YOUR-DEPOSIT-REFERENCE-NO}"])[:50]
        if data["{YOUR-DEPOSIT-REFERENCE-NO}"] is not None
        else None
    )

    payloadData["payload"]["udf"]["BRANDING"] = data["{YOUR-BRANDING}"]
    if data["{YOUR-BRANDING}"] is None or data["{YOUR-BRANDING}"] == "":
        payloadData["payload"]["udf"]["BRANDING"] = "NONE"

    payloadData["payload"]["udf"]["Note"] = (
        str(data["{YOUR-REMARKS}"])[:200]
        if data["{YOUR-REMARKS}"] is not None
        else None
    )
    payloadData["payload"]["udf"]["PDate"] = data["{YOUR-PROCESSING-DATE}"]

    if data["{YOUR-DELIVERY-DATE}"] is not None:
        payloadData["payload"]["SalesExemptionExpiryDate"] = data[
            "{YOUR-DELIVERY-DATE}"
        ]

    payloadData["payload"]["multiPricing"] = "{YOUR-MULTI-PRICING}"
    payloadData["payload"]["description"] = "{YOUR-DESCRIPTION}"
    payloadData["payload"]["phone1"] = data["{YOUR-CONTACT-NO}"]
    payloadData["payload"]["fax1"] = data["{YOUR-CONTACT-NO-2}"]

    # payloadData["payload"]["currencyRate"] = data['Exchange Rate'] // not use
    payloadData["payload"]["inclusiveTax"] = True
    payloadData["payload"]["displayTerm"] = data["{YOUR-CREDIT-TERM}"]
    payloadData["payload"]["details"] = JArray.FromObject(
        data["{YOUR-ITEMS}"].Select(lambda x: prepareRow(data, x))
    )
    payloadData["LastModified"] = data["{YOUR-UPDATED-DATE}"]
    return payloadData


# payload = JObject.FromObject(payloadData)
# data['Code'] = result['docKey']
# data['Sales Order No'] = result['docNo']

# end of added
for entry in documentList:
    payloadData = preparePayload(entry)
    resultList.append(payloadData)
    # debug('payloadData', payloadData)

debug("done", resultList)

# debug('resultList','done')
# test = JArray.FromObject(resultList)
# debug('here', 'here4')
outputs["results"] = JArray.FromObject(resultList)
# debug('result', resultList)

SQL ACCOUNTING integration - sales order

example script	purpose
payloadData["type"] = "SL_SO"	to let the system know you integration with Sales Order
payloadData["payload"]["DOCNO"] = data["SO Number"]	change ["DOCNO"]. You can refer below table

UI	mapping field	type	remarks
Customer	CODE	text	map to customer (need to make sure the customer code exist in SQL)
Fax (Delivery part)	DFAX1	text
Attention (Delivery part)	DATTENTION	text
Terms	TERMS	text	map to payment term (need to make sure the payment term code exist in SQL)
Description	DESCRIPTION	text
Ref 1	DOCREF1	text
S/O No	DOCNO	text
Date	DOCDATE	date
Ext. No	DOCNOEX	text

SQL ACCOUNTING integration - sales order line

example script	purpose
payloadData["payload"]["cdsDocDetail"] = JArray.FromObject(data['Details'].Select(lambda x: prepareRow(x)))	["cdsDocDetail"] mean map to line

UI	mapping field	type
More Description	DESCRIPTION3	text
Remark 1	REMARK1	text
UOM	UOM	text
U/Price	UNITPRICE	number
Qty	QTY	number
Disc	DISC	number