Non-Stock Kit Stock Availability - Acumatica Generic Inquiries & Pivot Tables

RE: Non-Stock Kit Stock Availability

cwomack — Fri, 02 Dec 2022 18:11:54 +0000

@busyfritz This was perfect. I added the component table and then added it as a group by and this was exactly what the client wanted to see. I appreciate your work on this.

RE: Non-Stock Kit Stock Availability

Wyatt.ERP — Mon, 29 Nov 2021 22:00:33 +0000

Hello. I have gotten something like this working using a Cross Join. Usually you want to avoid Cross joins because they can be pretty destructive, but they do have their uses. In this case, you can start with INSite CROSS APPLY InventoryItem. The results here being a list of every inventory item and every warehouse. LEFT JOIN in INSiteStatus ON INSiteStatus.SiteID = INSIte.SiteID AND INSiteStatus.InventoryID = InventoryItem.InventoryID and use ISNULL(,0) to get the Qtys you need.

Your conditions are super important here, because you want to target as few InventoryItem/Site records as possible. If you can add in parameters to only look at one Site or one Kit at a time, you should do that.

I'd love to see replenishment Qtys worked into here!

RE: Non-Stock Kit Stock Availability

NickSM — Tue, 02 Nov 2021 07:56:27 +0000

Hi @busyfritz,

I'll check this out in the morning and I can see the joins in the xml, so that might help. I can also see the IIF field for Availability which I added in mine, but not as complex. Thanks for the answer though as it is always nice to find out.

Regards

Nick SM

RE: Non-Stock Kit Stock Availability

NickSM — Tue, 02 Nov 2021 07:53:00 +0000

Hi @brandi-buff,

Thanks for your reply. I had managed to replicate this query in MySQL using the left (or right) joins based on the Tables. I think GIs should be able to do this, so I will take a look at @busyfritz answer and loop back around to the BQL if not.

I think also that you may have added a link to a Customer site or were you trying to show the wiki article?

Regards

Nick SM

RE: Non-Stock Kit Stock Availability

busyfritz — Thu, 28 Oct 2021 19:32:25 +0000

Here's a GI that will do this, but they don't include SO booked. That is a feature that would need to be added:

2576



  
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  
  
    
      

        

        

      
      
        

      
      
      

      

        

        

      
      
      

        

      
      
        

          

            

          
          
        
        
          

          

        
        
      
      
        

      
      
        

        

          

          

            

          
          
          

        
        
          

        
        
      
      
        

        

          

          

            

          
          
          

        
        
          

        
        
      
      
        

        

          

          

            

          
          
          

        
        
          

        
        
      
      
    
    
      

    
    
    

    
      
        
        
        
        
        
        
          
        
        
          
          
        
        
        
          
            
          
        
        
          
          
        
        
          
            
          
        
        
          
            
            
          
          
          
          
        
        
        
        
        
        
          
            
              
            
          
        
        
      
    
    
      
        
      
    
    
      
      
    
    
      
    
   
 

RE: Non-Stock Kit Stock Availability
Brandi Buff — Thu, 28 Oct 2021 17:59:43 +0000
This has to do with how databases join tables for queries.  If you use an inner join, it will only return results if they exist in both tables.  If you use a left or right join, it will return all records from the left or right table and only those from the other table where there is a match.  Where there is no match, it would return a null.
It sounds like you want the database to return the complete list of warehouses, the complete list of kits and the count of where those two intersect showing a 0 if there are none.
In traditional relational databases, I would do this with more than one query where the first query is an input in the second.  The first would be a crossjoin query across warehouses & kits to get the complete cross product of both (call this output1).  I would then create a query that totals kits per warehouse (call this output2).  Lastly, I would create a query of output1 & output2 where I join them on the warehouse ID and kit ID.  I would then have a function on the total iif(isnull(total),0,total)
I don't think you can do this with a generic inquiry.  But I looks like you might be able to do this with BQL - https://ggc.acumatica.com/(W(1))/Help?CompanyID=GGC&ScreenId=ShowWiki&pageid=13001ef0-91f0-4094-b924-d1114c3e8e41
 

RE: Non-Stock Kit Stock Availability
busyfritz — Mon, 25 Oct 2021 11:54:52 +0000
Did you ever get an answer to this? I needing the same thing.

Non-Stock Kit Stock Availability
NickSM — Thu, 20 May 2021 05:49:53 +0000
Hi,
I've been trying to build a GI to bring through component availability, which in turn lets them see what stock out they have. I've found that if a stock location hasn't had any stock, then the item won't show.
2365
It seems simple as I'm joining the INKitSpecStkDet and INSiteStatus. The problem is when there isn't a line in the INSiteStatus for the Component Line. In the screenshot, you can see that only 6 lines appear for one location whereas there are 7 lines for the Kit Spec. I even tried to add a Warehouse Detail for the missing location, but that doesn't work for me. Lastly, I added the INSiteStatusSummary to see if that helped, as it is the main table for the Warehouse Details tab. This didn't help either.
Any suggestions? I can provide the code and xml file if needed.