# Purchase Order ## Purchase Order's Database

UI Name	Database	Type	Remarks
P/O No	`Doc`	`String`	Avoid using the same `P/O No` to prevent overwrite. E.g.: `PO-123456`
P/O Key	`DocKey`	`Number`	E.g.: `123456`
Last Modified	`LastModified`	`String`	E.g.: `2024-12-19T08:57:37.557`
Creditor	`CreditorCode`	`String`	`Creditor` must match existing creditor code, and they must be active state. E.g.: `400-C001`
Name	`CreditorName`	`String`	E.g.: `ABC SUPPLIER`
Supplier DO No	`SupplierDONo`	`String`	E.g.: `DO-654321`
Address [Line 1]	`InvAddr1`	`String`	E.g.: `123 INDUSTRIAL PARK`
Address [Line 2]	`InvAddr2`	`String`	E.g.: `ZONE 4`
Address [Line 3]	`InvAddr3`	`String`	E.g.: `CITY, STATE 12345`
Address [Line 4]	`InvAddr4`	`String`	E.g.: `null`
Date	`DocDate`	`String`	E.g.: `2024-12-19T00:00:00`
Branch	`BranchCode`	`String`	E.g.: `BR001`
Credit Term	`DisplayTerm`	`String`	E.g.: `NET 30`
Purchase Location	`PurchaseLocation`	`String`	Must have existing `Purchase Location` in the accounting system. E.g.: `NY`
Purchase Agent	`PurchaseAgent`	`String`	E.g.: `JOHN SMITH`
Ship Via	`ShipVia`	`String`	E.g.: `AIR`
Ship Info	`ShipInfo`	`String`	E.g.: `EXPRESS`
Total (ex)	`TotalExTax`	`Number`	E.g.: `1000`
Inclusive	`InclusiveTax`	`String`	E.g.: `T`
Taxable Amount	`TaxableAmt`	`Number`	E.g.: `100`
Tax	`Tax`	`Number`	E.g.: `10`
Currency	`CurrencyCode`	`String`	E.g.: `USD`
Rate	`CurrencyRate`	`Number`	E.g.: `1.0`
Local Total	`LocalNetTotal`	`Number`	E.g.: `1100`
Total	`NetTotal`	`Number`	E.g.: `1100`
Sequence	`Seq`	`Number`	E.g.: `1`
Item Code	`ItemCode`	`String`	E.g.: `ITEM-001`
Description	`Description`	`String`	E.g.: `ITEM DESCRIPTION`
Description 2	`Desc2`	`String`	E.g.: `ADDITIONAL INFO`
Delivery Date	`DeliveryDate`	`String`	E.g.: `2024-12-20T00:00:00`
Location	`Location`	`String`	E.g.: `WAREHOUSE 1`
UOM	`UOM`	`String`	E.g.: `PCS`
Quantity	`Qty`	`Number`	Value must be number, and value cannot be negative. E.g.: `10`
Unit Price	`UnitPrice`	`Number`	Value must be number, and value cannot be negative. E.g.: `100`
Discount	`Discount`	`String`	E.g.: `10%`
C/N Amt.	`CNAmt`	`String`	E.g.: `50`
Subtotal	`SubTotal`	`Number`	E.g.: `900`
Subtotal (ex)	`SubTotalExTax`	`Number`	E.g.: `800`
Detail Tax	`DtlTax`	`Number`	E.g.: `80`
Ref Document No	`FromDocNo`	`String`	E.g.: `DOC-001`
UDF Description 2	`UDF_Desc2`	`String`	E.g.: `CUSTOM FIELD`

## Purchase Order's Sample Code {% hint style="warning" %} Remember to change to the **correct field** before proceeding to run **ANY** script. {% endhint %} {% tabs %} {% tab title="SQL Script" %} {% hint style="info" %} This is a sample SQL, may not be as same as your database. {% endhint %} ```sql SELECT TOP (10) Doc AS Document, DocKey AS DocumentKey, LastModified, CreditorCode, CreditorName, InvAddr1, InvAddr2, InvAddr3, InvAddr4, DocDate, BranchCode, DisplayTerm, PurchaseLocation, PurchaseAgent, ShipVia, ShipInfo, TotalExTax, InclusiveTax, TaxableAmt, Tax, CurrencyCode, CurrencyRate, LocalNetTotal, NetTotal, Seq, ItemCode, Description, Desc2, DeliveryDate, Location, UOM, Qty, UnitPrice, Discount, SubTotal, SubTotalExTax, DtlTax, FromSODocList, FurtherDescription, LastModified FROM PurchaseOrders WHERE LastModified > "{LAST_MODIFIED_DATE_TIME}"; ``` {% endtab %} {% tab title="jobs.json" %} {% hint style="warning" %} In order to sync, you must include **`LastModified`** in **`SELECT`** {% endhint %} ```json "SQLSync": [ { "name": "AutoCount to Inistate", "enabled": false, "connectionString": "Data Source={SERVER};Initial Catalog={DATABASE};User Id={DB_USERNAME};Password={DB_PASSWORD};", "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}", "sql": "SELECT AccNo, CompanyName, Address1, Address2, Address3, Address4, Phone1, DisplayTerm, EmailAddress, SalesAgent, LastModified \nFROM Debtor \nWHERE LastModified > @date", "lastSync": "{LAST-SYNC-DATE-TIME}", "start": null } ] ``` {% endtab %} {% 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 #debug('test', inputs) def getFlatten(list, table): details = [dict(Line=y, Document=x) for x in list for y in x[table]] return details moduleName = '{YOUR-MODULE-NAME}' inistateKeyField = 'DocumentId' keyField = 'Doc' modifiedDateField = 'LastModified' fields = [['DocumentId', 'Doc'],['LastModified', 'LastModified']] inistateFields = [] tableField = 'Items' tableColumns = ['Seq','ItemCode','Description','Desc2','DeliveryDate','Location','UOM','Qty','UnitPrice','Discount','SubTotal','SubTotalExTax','DtlTax','FromSODocList','FurtherDescription'] 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 def productAddToSync(product): if product is not None: form = Module('{YOUR-MODULE-NAME}').execute('Set To Sync', product) form['To Sync'] = True Module('{YOUR-MODULE-NAME}').submit(product, 'Set To Sync', form) result = list(inputs["list"]).GroupBy(lambda x: x[keyField].ToString()).Select(lambda x: createLine(x)).ToList() for item in result: entry = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.{fieldName}': item['Doc'] }) # debug('test', item['Doc']) # debug('valid', entry['SyncDateTime']) # debug('valid time', item['LastModified']) form = Module('{YOUR-MODULE-NAME}').execute('sync', entry) form['{YOUR-CREDITOR}'] = item['CreditorCode'] form['{YOUR-NAME}'] = item['CreditorName'] form['{YOUR-ADDRESS1}'] = item['InvAddr1'] form['{YOUR-ADDRESS2}'] = item['InvAddr2'] form['{YOUR-ADDRESS3}'] = item['InvAddr3'] form['{YOUR-ADDRESS4}'] = item['InvAddr4'] form['{YOUR-P/O NO}'] = item['Doc'] form['{YOUR-DOC-DATE}'] = item['DocDate'] form['{YOUR-BRANCH}'] = item['BranchCode'] form['{YOUR-CREDIT TERM}'] = item['DisplayTerm'] form['{YOUR-PURCHASE LOCATION}'] = item['PurchaseLocation'] form['{YOUR-PURCHASE AGENT}'] = item['PurchaseAgent'] form['{YOUR-SHIP VIA}'] = item['ShipVia'] form['{YOUR-SHIP INFO}'] = item['ShipInfo'] form['{YOUR-SUBTOTAL (EX)}'] = item['TotalExTax'] form['{YOUR-INCLUSIVE}'] = True if item['InclusiveTax'] != "F" else False form['{YOUR-TAXABLE AMOUNT}'] = item['TaxableAmt'] form['{YOUR-TAX}'] = item['Tax'] form['{YOUR-CURRENCY}'] = item['CurrencyCode'] form['{YOUR-RATE}'] = item['CurrencyRate'] form['{YOUR-LOCAL TOTAL}'] = item['LocalNetTotal'] form['{YOUR-TOTAL}'] = item['NetTotal'] # handle table lines = list(item['items']).ToList().OrderBy(lambda x: Convert.ToInt32(x['Seq'])).ToList() text = '' while len(form['Main']) > len(lines) : last = form['Main'].Last() productAddToSync(last['Item Code']) formLine = form.remove('Main',last) for line in lines: # create if none if len(form['Main']) <= lines.IndexOf(line): formLine = form.add('Main') else : formLine = form['Main'][lines.IndexOf(line)] # assign value to table row product = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.pIoowv': str(line['ItemCode']) }) if product is not None: if formLine['{YOUR-ITEM CODE}'].Id > 0 and formLine['{YOUR-ITEM CODE}'] != str(line['ItemCode']) and entry is not None : productAddToSync(formLine['{YOUR-ITEM CODE}']) formLine['{YOUR-ITEM CODE}'] = product formLine['{YOUR-DESCRIPTION}'] = line['Description'] formLine['{YOUR-DESCRIPTION 2}'] = line['Desc2'] formLine['{YOUR-DELIVERY DATE}'] = line['DeliveryDate'] formLine['{YOUR-LOCATION}'] = line['Location'] formLine['{YOUR-UOM}'] = line['UOM'] formLine['{YOUR-QTY}'] = line['Qty'] formLine['{YOUR-UNIT PRICE}'] = line['UnitPrice'] formLine['{YOUR-DISCOUNT}'] = line['Discount'] formLine['{YOUR-SUBTOTAL}'] = line['SubTotal'] formLine['{YOUR-TOTAL (INC)}'] = Convert.ToInt32(line['SubTotalExTax'] if line['SubTotalExTax'] != None else 0) + Convert.ToInt32(line['DtlTax'] if line['DtlTax'] != None else 0) #no field in Autocount record total include tax, need add 2 value formLine['{YOUR-FROM S/O DOC. NO.}'] = line['FromSODocList'] # formLine['Further Description'] = line['FurtherDescription'] #ignore first, take time to handle the image # debug('form', form) Module('{YOUR-MODULE-NAME}').submit(entry, form) # after = Module('Purchase Order').find({ 'Dynamic.Extension.zHLOEu': item['Doc'] }) # if after is not None: # for item in after['Main']: # if item['Item Code'].Id > 0: # form2 = Module('Product').execute('Update Stocks', item['Item Code']) # result2 = Module('Product').submit(item['Item Code'], 'Update Stocks', form2) debug('result', result) ``` {% endcode %} {% endtab %} {% endtabs %} --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://inistate.gitbook.io/home/advanced/integration/accounting-2/purchase-order.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.