How to create a GI to show Qty on Hand and Sold Qty
Hi Team,
We have a requirement where we have to create a GI and have to show Qty on Hand across all warehouses and Sold Qty (Qty across all released Invoices) for stock items. We created a GI and for all new products it is working fine but for existing products the values are incorrect. I am attaching the created GI, can you please review and suggest the best way possible to achieve it.
Thank you!
Page 1 / 1
Hi @santhu3242
Please find the attached GI which pulls sold and Onhand qty which helps to fulfil your requirement or compare the GI with your GI.
Thank you @manikantad18 for the response, we verified with suggested input and getting the data as expected
Now the details are showing based on individual warehouse but we need to show one line for one product and have to show sum of Qty on Hand across all warehouses and sum of all sold qty across all warehouse and released invoices.
Can you suggest to achieve this and help with that please.
@manikantad18 - Can you help me understand what might be happening here
I’m trying to use your GI to pull in total sales + usage in assembly during a given time period and then compare that to our total OH + total on PO. I’ve given the GI parameters for the date range using INItemSiteHistDay.SDate and warehouse from the INSiteStatus.SiteID, which all seems to work great
When I try to create calculations using values from the INItemSiteHistDay table it doesn’t seem to be pulling in the sum value and instead relies on the first value it’s seeing
Below you see the Sales from INItemSiteHistDay.qtySales as 45 which is correct per the Inventory Transaction History on the side panel (30 +10 + 5) and you see the INItemSiteHistDay.qtyAssemblyIn correctly at 52
However - Total Sold is being calculated as =(nINItemSiteHistDay.QtySales]+ eINItemSiteHistDay.QtyDropShipSales])-.INItemSiteHistDay.QtyCreditMemos] which in this case should equal 45 since there are no drop shipments or credit memos. The inquiry is returning the first value in the history (in this case 30).
Total Used - =(tINItemSiteHistDay.QtySales]+mINItemSiteHistDay.QtyDropShipSales]+tINItemSiteHistDay.QtyAssemblyOut])-sINItemSiteHistDay.QtyCreditMemos]
is trying to add in QtyAssemblyOut to the sales usage - which in this case should be 97 but it is only returning the first value in Assembly Out (52)
It gets even worse when I try to subtract Total Used from the Qty On Hand and then add back in Total on PO