Good Receive

Good Receive's Database

UI Name	Database	Type	Remarks
G/R No	Doc	String	Avoid using the same G/R No to prevent overwrite. E.g.: GR-001234
G/R Key	DocKey	String	E.g.: 271800
Last Modified	LastModified	String	E.g.: 2024-12-20T08:57:37.557
Creditor	CreditorCode	String	Creditor must match existing creditor code, and they must be active state. E.g.: 400-R002
Name	CreditorName	String	E.g.: BLUE SOFA PLT
Supplier DO No	SupplierDONo	String	E.g.: DO-100355
Address [Line 1]	InvAddr1	String	E.g.: NO 64, JALAN KP5
Address [Line 2]	InvAddr2	String	E.g.: KAWASAN INDUSTRIAL KOTA PUTERI
Address [Line 3]	InvAddr3	String	E.g.: 48200, BATU ARANG SELANGOR
Address [Line 4]	InvAddr4	String	E.g.: BLOCK B, UNIT 12
Date	DocDate	String	E.g.: 2024-12-20T00:00:00
Branch	BranchCode	String	E.g.: BR001
Display Term	DisplayTerm	String	E.g.: NET 60
Purchase Location	PurchaseLocation	String	Must have existing Purchase Location in the accounting system. E.g.: PJ
Purchase Agent	PurchaseAgent	String	E.g.: AGENT001
Ship Via	ShipVia	String	E.g.: DHL
Ship Info	ShipInfo	String	E.g.: EXPRESS
Total (ex)	TotalExTax	Number	E.g.: 5000
Inclusive	InclusiveTax	String	E.g.: T
Taxable Amount	TaxableAmt	Number	E.g.: 4500
Tax	Tax	Number	E.g.: 500
Currency	CurrencyCode	String	E.g.: USD
Rate	CurrencyRate	Number	E.g.: 1.2
Local Total	LocalNetTotal	Number	E.g.: 6000
Total	NetTotal	Number	E.g.: 5500
Seq	Seq	Number	E.g.: 17
Item Code	ItemCode	String	E.g.: RDS-5528 SOFA
Description	Description	String	E.g.: RDS CLOUD SOFA 5528
Description 2	Desc2	String	E.g.: L Shape(30", 40") / Col: Beetex HIVE GD 2035 - 05# Gold
Delivery Date	DeliveryDate	String	E.g.: 2024-12-21T00:00:00
Location	Location	String	E.g.: PJ
UOM	UOM	String	E.g.: PCS
Quantity	Qty	Number	Value must be number, and value cannot be negative. E.g.: 2
Unit Price	UnitPrice	Number	Value must be number, and value cannot be negative. E.g.: 2500
Discount	Discount	String	E.g.: 10%
C/N Amt.	CNAmt	String	E.g.: 100
Subtotal	SubTotal	Number	E.g.: 4500
Subtotal (ex)	SubTotalExTax	Number	E.g.: 4000
Detail Tax	DtlTax	Number	E.g.: 500
Ref Doc No	FromDocNo	String	E.g.: PO-001638
UDF Desc 2	UDF_Desc2	String	E.g.: Special Instructions

Good Receive's Sample Code

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

SQL Script

This is a sample SQL, may not be as same as your database.

SELECT TOP (10)
    Doc AS Document,
    DocKey AS DocumentKey,
    LastModified,
    CreditorCode,
    CreditorName,
    SupplierDONo,
    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,
    CNAmt,
    SubTotal,
    SubTotalExTax,
    DtlTax,
    FromDocNo,
    UDF_Desc2,
    LastModified
FROM GoodReceives
WHERE LastModified > "{LAST_MODIFIED_DATE_TIME}";

jobs.json

In order to sync, you must include LastModified in SELECT

"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
  }
]

Logic Script

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

#debug('test', inputs)

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','CNAmt','SubTotal','SubTotalExTax','DtlTax','FromDocNo','UDF_Desc2']

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

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('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-G/R NO}'] = item['Doc']
    form['{YOUR-ADDRESS1}'] = item['InvAddr1']
    form['{YOUR-ADDRESS2}'] = item['InvAddr2']
    form['{YOUR-ADDRESS3}'] = item['InvAddr3']
    form['{YOUR-ADDRESS4}'] = item['InvAddr4']
    form['{YOUR-SUPPLIER D/O NO}'] = item['SupplierDONo']
    form['{YOUR-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']
    debug('Tax', item)
    # handle table
    lines = list(item['items']).ToList().OrderBy(lambda x: Convert.ToInt32(x['Seq'])).ToList()
    
    while len(form['{YOUR-GOODS INFORMATION}']) > len(lines) :
        last = form['{YOUR-GOODS INFORMATION}'].Last()
        productAddToSync(last['Item Code'])
        formLine = form.remove('{YOUR-GOODS INFORMATION}',last)
    
    for line in lines:
        # create if none
        if len(form['{YOUR-GOODS INFORMATION}']) <= lines.IndexOf(line):
            formLine = form.add('{YOUR-GOODS INFORMATION}')
        else :
            formLine = form['{YOUR-GOODS INFORMATION}'][lines.IndexOf(line)]
            
        # assign value to table row
        product = Module('Product').find({ 'Dynamic.Extension.{fieldName}': 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-C/N AMT.}'] = line['CNAmt']
        formLine['{YOUR-SUBTOTAL}'] = line['SubTotal']
        formLine['{YOUR-TAX}'] = line['DtlTax']
        formLine['{YOUR-TOTAL (EX)}'] = line['SubTotalExTax']
        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 DOC. NO.}'] = line['FromDocNo']
        formLine['{YOUR-DESC2}'] = line['UDF_Desc2']
        
    # debug('form', form)
    Module('{YOUR-MODULE-NAME}').submit(entry, form)
    
    after = Module('{YOUR-MODULE-NAME}').find({ 'Dynamic.Extension.{fieldName}': item['Doc'] })
    if after is not None:
        #Get Distinct PO number in GRN
        withPO = filter(lambda x: ('PO' in x['From Doc. No.']), after['{YOUR-GOODS INFORMATION}'])
        sortByPO = sorted(withPO, key=lambda x: x['From Doc. No.'])
        groupByPO = [dict(Key=ckey, List=list(cgroup)) for ckey, cgroup in groupby(sortByPO, lambda x: x['From Doc. No.'])]
    
        for po in groupByPO:
            #Get All Good Receive realated to the PO 
            grn = Module('{YOUR-MODULE-NAME}').findAll({'Dynamic.Extension.{fieldName}': po['Key']})
            items = getFlatten(grn, '{YOUR-GOODS INFORMATION}')
            sortByItem = sorted(items, key=lambda x: x['Line']['Item Code'])
            groupByItem = [dict(Key=ckey, List=list(cgroup)) for ckey, cgroup in groupby(sortByItem, lambda x: {"Item Code" : x['Line']['Item Code'], "PONumber" : x['Line']['From Doc. No.'] })]
            
            # debug('po', po['Key'])
            p = Module('{YOUR-MODULE-NAME}').find({'$and': [{'Dynamic.Extension.{fieldName}': po['Key']}]} )
            if p is not None:
                form2 = Module('{YOUR-MODULE-NAME}').execute('Receive', p)
                u = 0
                sortPOItem = sorted(form2['Main'], key=lambda x: x['Item Code'])
                groupPOItem = [dict(Key=ckey, List=list(cgroup)) for ckey, cgroup in groupby(sortPOItem, lambda x: x['Item Code'])]
                for r in groupPOItem:
                    goodReceives = next((x for x in groupByItem if x['Key']['Item Code'] == r['Key'] and x['Key']['PONumber'] == po['Key']), None)
                    if goodReceives is not None:
                        totalReceived = sum(x['Line']['Qty'] for x in goodReceives['List'])
                        if totalReceived != sum(x['Received'] for x in r['List']):
                            qty = totalReceived
                            for l in r['List']:
                                if qty > 0:
                                    if qty >= l['Qty']:
                                        l['Received'] = l['Qty']
                                        qty = qty - l['Qty']
                                    else:
                                        l['Received'] = qty
                                        qty = 0
                            u = 1
                    else:
                        for l in r['List']:
                            l['Received'] = 0
                        u = 1
                if u == 1:
                    result2 = Module('{YOUR-MODULE-NAME}').submit(p, 'Receive', form2)
                    # debug('result2', result2)
                    debug('Impacted PO', po['Key'])

    
debug('result', result)