GI to Exclude SO from list where item meets criteria

  • 13 July 2022
  • 5 replies

Userlevel 4
Badge +1
  • Jr Varsity III
  • 92 replies

I have a GI where I need to include certain Sales Orders AND exclude orders from that subset where they have 1 particular NONSTOCK inventory item "COLOFEE"

Hoping someone can help with the funky GI conditions functions:





I can do this in sql using the Having clause:

Select c.OrderNbr
from soorder C, Soline A, Inventoryitem B
where c.OrderNbr=a.OrderNbr
and A.inventoryid=B.inventoryid
and B.inventorycd = 'colofee'
group by C.OrderNbr
having sum(case when b.inventoryCd = 'colofee' then 1 else 0 end) = 0

help is appreciated!



Best answer by DanielByrum 13 July 2022, 20:01

View original

5 replies

Userlevel 7
Badge +17

Hi @K2a  Please share the XML format of the GI here?

Userlevel 4
Badge +1

A bit more background: 

Colorado just implemented a 0.27 retail sales tax on anything delivered by vehicle in the state.   So when our Shopify orders originate with a ship-to in CO they capture this 0.27 fee.   However the commerce connector does NOT bring this over into ACU.  Since we are using Avalara (and so is Shopify) we’ve been instructed to create a $0 non-stock item and add it to the SO and it will create the tax line.  

I’m trying to automate adding the non-stock line to the SO.  I have an import scenario based on this GI (XML Attached) that updates the SO perfectly.  The challenge is this GI still recognizes the SO as needing the the non-stock item after it has been added because of my criteria (state = CO, status = Open). 

I need to evaluate ALL the rows in a Sales Order and only surface the ones that meet my criteria and DO NOT have the Non-Stock item (COLOFEE) attached! (i.e. the “having” clause in sql)

I heard that grouping in GI’s doesn’t work very well so you will see an aggregate function where I am trying to add up a field to get a filtering criteria...=iif([InventoryItem.StkItem]=1,0,1)

Any ideas are appreciated!

we are on 2021R1.

Userlevel 2

Have you tried adding a UDF that the import scenario can ‘mark’ the order as updated? Then filter the GI on that UDF. We use something similar to mark orders as printed/purchased.

Userlevel 4
Badge +1

@DanielByrum that’s an interesting idea...I will look at that.

Userlevel 4
Badge +1

@DanielByrum that definitely seems to work.  I’ll check to see if we’re up for doing that in our PROD enviro.



About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved