Solved

What GI Table and Object Can I find Allocation in Sales Order


Userlevel 4
Badge

Hi All,

I am wondering where can I find the field for Allocation for GI in Sales Order screen? (Please see below highlighted area in screen shot):

I am trying to create a GI that show how many are allocated for an item in Sales Order. I tried using SOLineSplit (which is Line Detail in SO screen) but it gave me the amount I should allocate and not what is currently allocated. I have try to check for the element but it just show me SOLine when I click on it.

Kind regards,

Calvin

icon

Best answer by Gabriel Michaud 23 August 2022, 15:43

View original

14 replies

Badge +16

Hi, I think Quantity Available may be a calculated field versus a stored field.  Perhaps look at the Inventory Balance report to see how the figures are calculated:

 

Userlevel 6
Badge +2

@Calvin Within Grids, click on the column header with inspect element to include the data field.

 

Userlevel 4
Badge

Hi @DConcannon,

The issue is the field I am trying to look is not located within the Grid. On my screen shot earlier I was already using Inspected Elements to research on the field (highlighted in yellow), unfortunately it does not provide any useful information. 

Hi @laura01,

Thank you for your suggestion, I will take a deeper dive as the table “INAvailabilityScheme” seems to be a bit hard for me to configurate into my report than I would like to. Hopefully I am able to build the GI so it would work the way I like to. 

Kind regards,

Calvin

Userlevel 7
Badge +10

The information on quantity already allocated for a line can be found in SOLineSplit - IsAllocated is set to TRUE (1).

If you want to get information for a given item, irrespective of the sales order, you would find that in INSiteStatus. QtySOShipping shows the total allocated for an item (whether it’s coming from an unconfirmed shipment or a sales order line that was set to Allocated=1)

Userlevel 4
Badge

 

Hi @Gabriel Michaud,

Thank you for your suggestion. It make sense to filter the items based on active allocation on the items. Currently I have used IIF formula in Result Grid to select the right allocated quantity for the item. 

=IIF([SOLineSplit.IsAllocated]=True,[SOLineSplit.Qty],null)

I did run into a two parts issue with this formula:
1 - For items that have more than 1 line in Line Detail Screen (one active allocation and one non active allocation) (see screen shot 1 and 2 for item AX002 ), how would I write the formula in order to have it show what is allocated and not the blank space

WHILE

2 - It will still show 0 for items that have only 1 line for non allocated quantity ( see SM3000TA3 screen shot 1 and 3)

Ultimately, I do not want the system to show the same item more than once when it is just a 0 for the non allocated line. 

Screen shot 1

Screen shot 2

Screen Shot 3

Thank you for the suggestions. 

Kind regards,

Calvin 

 

Userlevel 7
Badge +10

Calvin,

Would adding a GROUP BY in your Generic Inquiry work? I’d try grouping by SOLine.InventoryID or SOLine.LineNbr if you want individual SOLines to be reported separately.

Regards,

Userlevel 4
Badge

Hi @Gabriel Michaud,

Thank you for the suggestion. I try to group it with Inventory ID or LineNbr, but the issue will be that it group up and won’t show any other order that in in the GI. 

How would I group the QTY within an order while have the list to show other orders and items? I have attached a screen shot and the XML of the GI. 

If anyone know of a solution please let me know.

Thank you,

Calvin

Userlevel 4
Badge

Sorry here is the XML file. 

Calvin

Userlevel 7
Badge +10

Hi Calvin,

Sorry for the late response. I did look at the inquiry and have identified one problem -- the join between SOLine and INSiteStatusSummary should also include SOLine.SiteID=INSiteStatusSummary.SiteID:

If you have inventory in more than one site, this would have potentially multiplied the results.

To correctly report on allocated total, we need to have the necessary groupings added to the Grouping tab:

 

 

Finally, note the aggregate function is set to MAX for INSiteStatusSummary, otherwise it will also multiply the numbers by the number of splits on a given line.

Also, do you think it would make more sense to use QtyHardAvail instead of QtyAvail? QtyAvail takes into account the current (and all the other) orders. QtyHardAvail shows you what’s really available for allocation right now. I tried it to test and my changes and it seems more logical to me.

In this example, I have 13 items on order, 8 of which are allocated. There are 292 items available for allocation/shipment (300 total, but 8 already allocated to this order)

 

 

Userlevel 4
Badge

Hi @Gabriel Michaud,

Thank you for the follow up. That was detail info and I will work it into the GI and see how it fit!

Kind regards,

Calvin

Userlevel 3
Badge

Hi @Gabriel Michaud 

Thank you for working on this solution with @Calvin .  Do you by chance of the XML of the GI you helped him with on this post?  The attached GI’s on here now appear to be expired and can’t download. 

Thanks!

Userlevel 7
Badge +10

@nathankeating I just checked the and the link is still working. The file is stored on the community itself (hosted by insided.com), at first I thought it was an old SharePoint link, but you should be able to download it.

Userlevel 7
Badge

Hi @nathankeating - in case you’re wondering how to download and save:

 

Userlevel 3
Badge

@Gabriel Michaud @Chris Hackett 

Thank you guys!  I was not aware that I could download and save it that way.  Thank you for that tip, and thanks again for creating this GI.  

Reply


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