# 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.: `+1234567890`
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.: `example@gmail.com`
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 {% hint style="warning" %} Remember to change to the **correct field** before proceeding to run **ANY** script. {% endhint %} {% tabs %} {% 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 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) ``` {% endcode %} {% endtab %} {% endtabs %} **SQL ACCOUNTING integration - sales order**

example script purpose

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

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 |